Search over Iceberg: insert → searchable
An Iceberg table in your own object storage is the source of truth; SereneDB keeps a derived search index over it. Any engine — Spark, BigQuery, Flink, SereneDB itself — writes rows to the table; one REINDEX makes everything committed before it searchable, atomically. Full-text (BM25), vector similarity and hybrid queries then run against the index in one SQL statement.
What lives where:
- The Iceberg table (your bucket, your catalog) holds the rows — text, metadata, embeddings — as plain Parquet. It stays fully yours: every engine can read and write it, and
REINDEXpicks up foreign commits, updates and deletes the same way. - SereneDB holds only the derived index (term dictionaries, vector structures, indexed columns) on local disk. There is no second copy of the corpus — lose the node, rebuild the index from the table. Columns you did not index are still selectable: they are materialized from the source Parquet at query time, only for the rows a query matched.
How REINDEX refreshes
REINDEX INDEX <name> runs one pass: it compares the current source state against what the index holds, applies the difference, and publishes atomically — readers see the previous complete state or the new one, never a partial index. Iceberg tables and file globs refresh incrementally (only the difference is indexed); other sources rebuild in full — the per-source table in the reference has the details.
The rest of this page builds both delta roads from zero — first the Iceberg pipeline, then the same lifecycle over a plain directory of files.
Connect the object store
The warehouse data lives in object storage; give SereneDB credentials for it. A PERSISTENT SECRET survives restarts:
CREATE PERSISTENT SECRET chunks_store ( TYPE S3, KEY_ID '⟨access key⟩', SECRET '⟨secret key⟩', REGION '⟨us-east-1⟩', SCOPE 's3://⟨bucket⟩/⟨warehouse prefix⟩/');Attach the catalog
CREATE SERVER attaches the Iceberg REST catalog as a database. The server row persists in the SereneDB catalog and re-attaches at boot, so everything built on it survives restarts unattended. max_table_staleness bounds how old a cached table version may be served between refreshes:
CREATE SERVER chunks_catalog FOREIGN DATA WRAPPER iceberg_fdw OPTIONS ( warehouse '⟨warehouse⟩', endpoint '⟨https://your-rest-catalog/iceberg/v1/restcatalog⟩', authorization_type '⟨oauth2⟩', token '⟨...⟩', max_table_staleness '10 minutes');Create the table
Skip this if the table already exists — the point of the pattern is that any engine may own it. Created from SereneDB it is a regular Iceberg table like any other:
CREATE SCHEMA chunks_catalog.docs;
CREATE TABLE chunks_catalog.docs.chunks ( entity_id INTEGER, source_name TEXT, page_number INTEGER, uri TEXT, body TEXT, emb FLOAT[]);Index it
Expose the table through a view (casting the embedding list to a fixed-size vector), then index the view: filter columns as plain terms, the text column through an analyzer for BM25, the vector column with IVF for similarity search. See Indexes over views for everything the index can do:
CREATE TEXT SEARCH DICTIONARY en ( template = 'text', locale = 'en_US.UTF-8', stemming = true, frequency = true, position = true);
CREATE VIEW chunks_v AS SELECT entity_id, source_name, page_number, uri, body, emb::FLOAT[3072] AS emb FROM chunks_catalog.docs.chunks;
CREATE INDEX chunks_idx ON chunks_v USING inverted( entity_id, source_name, page_number, body en, emb ivf (metric = 'cosine'));The freshness barrier
Writers commit batches to the table; REINDEX INDEX runs one pass and returns only when everything committed before it is searchable. That makes freshness a one-statement barrier in any pipeline — load the data, run REINDEX, start the consumers:
INSERT INTO chunks_catalog.docs.chunks VALUES (...);
REINDEX INDEX chunks_idx;-- returns only when everything committed before it is searchablecount 3Everything from the batch is now in the index:
SELECT entity_id, source_name, page_number, bodyFROM chunks_idxORDER BY entity_id, source_name, page_number; entity_id | source_name | page_number | body-----------+-------------+-------------+----------------------------------------------- 7 | report.pdf | 1 | carbon emissions grow with scope 3 accounting 7 | report.pdf | 2 | renewable capacity doubles under the new plan 8 | notes.pdf | 1 | scope 3 emissions of the supply chainNew data keeps arriving
Each commit adds Parquet files to the table; the next pass indexes only those — the three rows already indexed above are not re-read:
INSERT INTO chunks_catalog.docs.chunks VALUES (...);
REINDEX INDEX chunks_idx;count 2SELECT entity_id, source_name, page_number, bodyFROM chunks_idxORDER BY entity_id, source_name, page_number; entity_id | source_name | page_number | body-----------+-------------+-------------+----------------------------------------------- 7 | report.pdf | 1 | carbon emissions grow with scope 3 accounting 7 | report.pdf | 2 | renewable capacity doubles under the new plan 8 | notes.pdf | 1 | scope 3 emissions of the supply chain 8 | notes.pdf | 2 | audit trail for renewable certificates 9 | memo.pdf | 1 | carbon capture pilots start next quarterTo run the pass on a schedule instead of by hand, set the reindex_interval index option — the loop survives server restarts.
Query
Vector similarity, scoped to a filter — everything below answers from the index:
SELECT source_name, page_number, bodyFROM chunks_idxWHERE entity_id = ⟨tenant⟩ORDER BY emb <=> ⟨query vector⟩ LIMIT 5; source_name | page_number | body-------------+-------------+----------------------------------------------- report.pdf | 1 | carbon emissions grow with scope 3 accountingHybrid — full-text filter plus semantic ranking in one statement:
SELECT source_name, page_number, bodyFROM chunks_idxWHERE entity_id = ⟨tenant⟩ AND body @@ ts_phrase('scope 3')ORDER BY emb <=> ⟨query vector⟩ LIMIT 5; source_name | page_number | body-------------+-------------+----------------------------------------------- report.pdf | 1 | carbon emissions grow with scope 3 accountingSelecting a column the index does not hold (uri here) still works: it is fetched from the source Parquet at query time, only for the matched rows:
SELECT source_name, uri, bodyFROM chunks_idxWHERE entity_id = ⟨tenant⟩ AND body @@ ts_phrase('carbon emissions')LIMIT 5; source_name | uri | body-------------+-------------------------+----------------------------------------------- report.pdf | s3://docs/report.pdf#p1 | carbon emissions grow with scope 3 accountingUpdates
Rewrite rows in the table — from SereneDB or any other engine — and the next pass reindexes just the affected files:
UPDATE chunks_catalog.docs.chunksSET body = '⟨new text⟩'WHERE source_name = '⟨name⟩' AND page_number = ⟨n⟩;
REINDEX INDEX chunks_idx;The search sees the new text, not the old:
SELECT entity_id, source_name, page_number, bodyFROM chunks_idxWHERE body @@ ts_phrase('carbon capture')ORDER BY entity_id, source_name, page_number; entity_id | source_name | page_number | body-----------+-------------+-------------+-------------------------------------------- 9 | memo.pdf | 1 | carbon capture pilots delayed to next yearDeletes
Deletes are the same story. Drop one document:
DELETE FROM chunks_catalog.docs.chunks WHERE source_name = '⟨name⟩';
REINDEX INDEX chunks_idx;Its rows are gone; everything else never left the index:
SELECT entity_id, source_name, page_number, bodyFROM chunks_idxORDER BY entity_id, source_name, page_number; entity_id | source_name | page_number | body-----------+-------------+-------------+----------------------------------------------- 7 | report.pdf | 1 | carbon emissions grow with scope 3 accounting 7 | report.pdf | 2 | renewable capacity doubles under the new plan 9 | memo.pdf | 1 | carbon capture pilots delayed to next yearThe same over plain files
No catalog at all — a directory of Parquet (or CSV, JSON) files behaves the same way, locally or on S3. Index a view over a glob:
COPY (SELECT 1 AS id, 'pallets arrive at the northern dock' AS body) TO '⟨dir⟩/batch1.parquet' (FORMAT parquet);
CREATE VIEW events_v AS SELECT * FROM read_parquet('⟨dir⟩/*.parquet');
CREATE INDEX events_idx ON events_v USING inverted(id, body en);count 1A new file lands in the directory — the pass picks up just that file:
COPY (SELECT 2 AS id, 'customs cleared the second shipment' AS body) TO '⟨dir⟩/batch2.parquet' (FORMAT parquet);
REINDEX INDEX events_idx;count 1SELECT id, body FROM events_idx ORDER BY id; id | body----+------------------------------------- 1 | pallets arrive at the northern dock 2 | customs cleared the second shipmentA file disappears — the next pass drops its rows:
rm ⟨dir⟩/batch1.parquetREINDEX INDEX events_idx;SELECT id, body FROM events_idx ORDER BY id;
DROP VIEW events_v CASCADE; id | body----+------------------------------------- 2 | customs cleared the second shipment