Indexing Views

An inverted index can be built over a view, not just a base table. This is how SereneDB searches data it does not own a primary copy of — most importantly external files (Parquet, CSV, JSON on disk or S3) exposed through a view.

A view has no primary key of its own, so two things have to be resolved: a row identity (so each indexed document can be located again) and, when a query needs a column the index does not hold, a way to fetch that column on demand. SereneDB handles both through a fast-path source.

Diagramflowchart LR

Counts, relevance scores and the columns the index stores come straight from the frozen index. It stores INCLUDEd columns, included() expressions, vector columns, and geo columns whose dictionary keeps the source value: encode_geopoint, or encode_geojson with its default source coding. Every other column, indexed text, numeric and temporal columns too, is fetched live from the source by row identity at query time. That split — stored columns frozen, the rest live — is the whole model.

Tip

A view-backed index stores a frozen snapshot of the postings taken at CREATE INDEX time — it does not track later changes to the source. Column values, however, are read live from the source when a query materializes them (see Materializing real columns).

All examples below use this setup — a base table and a view over it:

SQL

CREATE TABLE base_docs (id INTEGER PRIMARY KEY, body VARCHAR, extra VARCHAR);
CREATE TEXT SEARCH DICTIONARY view_en AS    split_text(case := 'lower') | normalize_tokens('en_US.UTF-8', accent := false)    WITH (frequency, position);
INSERT INTO base_docs VALUES    (1, 'quick brown fox', 'alpha'),    (2, 'lazy dog sleeps', 'beta'),    (3, 'quick red fox', 'gamma');
CREATE VIEW v_docs AS SELECT id, body, extra FROM base_docs;
CREATE INDEX v_docs_idx ON v_docs USING inverted (id, body view_en) INCLUDE (id);

Row identity

How the index derives a stable per-row identity depends on the view body:

View body	Row identity	Can be overridden
SELECT … FROM base_table	The hidden row id	—
SELECT … FROM read_parquet('file')	The reader's file_row_number	—
read_csv / read_json single file	The byte offset within the file	—
Reader over a glob ('…/*.parquet')	(file_index, position) pair	—
Attached DuckDB / Iceberg table	The source row id / (file_index, row)	—
Attached PostgreSQL table	The remote ctid	WITH (key_columns = '…')
Attached ClickHouse table	The engine's primary-key columns	WITH (key_columns = '…')
Generic body (inline VALUES, UNION ALL, joins)	None; see Generic views	—

Row identity is always derived automatically — there is no pk index option. The only sources that accept an explicit key are attached external databases, via key_columns.

Fast-path sources

A fast-path source is a view body SereneDB recognizes well enough to both derive the row identity and re-read columns by it later. The recognized sources are: SereneDB base tables, read_parquet / read_csv / read_json (and their *_auto / parquet_scan / read_ndjson variants), read_json_objects / read_json_objects_auto / read_ndjson_objects, read_text, read_duckdb, Iceberg tables, attached DuckDB tables and attached PostgreSQL and ClickHouse tables.

The view body may shape the source and still qualify as fast-path:

• a column subset, reordering or renaming (SELECT body, id FROM …, SELECT a AS x FROM …);
• a cast on a column (SELECT id::BIGINT, body FROM …);
• a WHERE / ORDER BY / LIMIT in the view body (the index then captures only the rows the view emits).

An indexed expression over source columns (upper(body), (json ->> 'b')) goes in the index definition, over a plain view.

A body with no fast-path leaf — inline VALUES, a UNION ALL, a join — is a generic view: it indexes normally but supports only the non-materializing queries below.

Keeping a view on the fast path

Besides losing materialization, a generic view's index is rebuilt in full on every refresh — each REINDEX and each reindex_interval tick, whether or not the source changed — even over files or Iceberg, which otherwise refresh incrementally. These make a view generic:

In the view body	Write instead
An expression in the select list: SELECT id, upper(body) AS body FROM …	A plain view, with the expression in the index: USING inverted((upper(body)) en)
A qualified column: SELECT t.id, t.body FROM … AS t	Bare column names: SELECT id, body FROM …
A join, a subquery, UNION ALL or inline VALUES	One table or one reader call per view
GROUP BY, HAVING, DISTINCT, QUALIFY, SAMPLE or a WITH clause	A plain view; aggregate or deduplicate in the query
A reader over a list of files: read_parquet(['a.parquet', 'b.parquet'])	One glob: read_parquet('data/*.parquet')
Compressed JSON: a .gz / .zst path, or compression = 'gzip' / 'zstd'	Uncompressed files, or INCLUDE the columns you return

Two constructs keep the fast path but turn every refresh that finds a change into a full rebuild: a LIMIT in the view body, and a reader's union_by_name option.

Referencing the source

Names in a view body are resolved each time the view is used — first in the view's own schema, then along the search_path of the session running the query. Write a table that lives outside the view's schema in full: a short name makes the view resolve differently, or not at all, depending on who queries it.

Attention

Write an Iceberg table by its full path, server.namespace.table. Any part you leave out has to be looked up in the catalog: SereneDB asks for the table in each Iceberg namespace the name could live in until it finds it — one catalog request for the namespace that holds it, plus a wasted one for every namespace tried before it. These lookups happen when each query is planned, even a query answered entirely from the index, whenever the catalog's cached answer is older than max_table_staleness (so on every query if it is not set). With the full path the location is already known, and a query answered from the index makes no catalog request at all.

• Iceberg — attach the REST catalog with CREATE SERVER … FOREIGN DATA WRAPPER iceberg_fdw and use the full path:

  sql

  CREATE SERVER lake FOREIGN DATA WRAPPER iceberg_fdw OPTIONS (
      warehouse '⟨warehouse⟩',
      uri '⟨https://your-rest-catalog/iceberg/v1/restcatalog⟩',
      authorization_type '⟨oauth2⟩', token '⟨...⟩',
      max_table_staleness '10 minutes');
  
  CREATE VIEW events_v AS SELECT id, body FROM lake.analytics.events;

  Prefer the catalog to iceberg_scan('⟨path⟩'). A metadata-file path pins the view to that one snapshot, so a refresh never sees newer commits; a table-directory path needs a version-hint.text file, which catalog-managed tables usually do not have. The catalog always knows the current snapshot, and every refresh loads it.

• Parquet, CSV, JSON and text files — give one path, and use a glob for a dataset that grows: a refresh then re-reads only the files that appeared, changed or disappeared.

• Attached DuckDB — attach a database file: a table in an in-memory attached database is not a fast-path source.

• Attached PostgreSQL and ClickHouse — matches are re-fetched by key; see the default key and when to override it.

What runs without materialization

These queries are answered entirely from the index, never touching the source — so they work on every view shape, including generic views:

• COUNT(*) / COUNT(1), with or without a @@ filter;
• full-text @@ filters and secondary scalar filters (=, <, BETWEEN, IN) that get pushed into the index scan;
• relevance scores — BM25, TFIDF — and the index tableoid;
• ts_offsets;
• projections of the columns the index stores — INCLUDEd, vector and source-coded geo columns — and aggregates over them; an indexed text, numeric or temporal column is returned from the index only when it is INCLUDEd as well, as id is in the setup above.

SQL

SELECT count(*) FROM v_docs_idx WHERE body @@ ts_phrase('quick');

 count
-------
     2

SQL

SELECT id FROM v_docs_idxWHERE body @@ ts_phrase('quick')ORDER BY BM25(v_docs_idx.tableoid) DESC, id;

 id
----
  1
  3

Materializing real columns

Selecting a real source column that the index doesn't store, an indexed text, numeric or temporal column too, materializes it: the index hands back the row identities for the matches, and SereneDB re-reads those columns from the source through the fast-path lookup. The column values are not stored in the index — they are fetched live at query time:

SQL

SELECT id, extra FROM v_docs_idx WHERE body @@ ts_phrase('quick') ORDER BY id;

 id | extra
----+-------
  1 | alpha
  3 | gamma

Because the read is live, if the underlying source changed after the index was built, materialized values reflect the current source: rows deleted from the source are left out of the result, and edited content shows its new value (or raises an error if a file became unreadable). Counts and scores, by contrast, still reflect the frozen build-time snapshot.

With ORDER BY … LIMIT k, USING SAMPLE k ROWS, or a LIMIT k that follows a search condition, a WHERE filter or an OFFSET, the index picks the k rows before their real columns are read, and only those rows are read from the source, for k up to late_materialization_lookup_max_rows (2048 by default). The plan reads the source with a LOOKUP_JOIN whose right side is an INDEX_SOURCE, keyed by the row identity the index stores; its Key names that identity as the source does (rowid, file_row_number, file_index, file_row_number, file_offset, ctid, or the key columns), or, when the query returns only columns the source provides and orders and filters by indexed columns, with the same LOOKUP_JOIN that reads the index (Inspecting the query plan). A query can then return fewer than k rows when some of the picked rows were deleted from the source after the index was built; refreshing the index brings it back in line with the source.

Columns a file reader adds on its own materialize too, with the values the view gives them: a hive partition column (hive_partitioning) comes from the path of the row's file, filename is that path, and file_row_number is the position the reader reports for the row. Filters on these columns work like filters on any other materialized column.

Generic views

A generic view (no fast-path source) still indexes and answers the non-materializing queries, with no extra options:

SQL

CREATE VIEW u_docs AS    SELECT 1 AS id, 'quick brown fox' AS body    UNION ALL SELECT 2, 'lazy dog sleeps';
CREATE INDEX u_docs_idx ON u_docs USING inverted (id, body view_en);
SELECT count(*) FROM u_docs_idx WHERE body @@ ts_phrase('quick');

 count
-------
     1

Its index keeps no row identity, so only the columns it stores can be read back: INCLUDEd, vector and source-coded geo columns. Selecting any other column, even an indexed text, numeric or temporal one, raises an error:

SQL

SELECT id, body FROM u_docs_idx WHERE body @@ ts_phrase('quick');

db error: ERROR: materialising real columns from this view-backed inverted index is not yet supported -- view body must be a simple `SELECT * FROM <reader>(literal_args)` over a recognised fast-path source (read_parquet/csv/json/...)

INCLUDE columns

INCLUDEd columns on a view are stored in the index's columnstore, so they are returned without materializing the source — the same as on a base table. Use INCLUDE for columns you frequently return but never search, to avoid the per-row source lookup. A query that returns only columns the index stores, counts and scores never touches the source at all: over a catalog-attached Iceberg table named in full it does not even contact the catalog, so its latency does not depend on the server's max_table_staleness.

Refreshing the index

The postings are a snapshot; the source moves on. REINDEX INDEX <name> runs one refresh pass: it compares the current source state against what the index holds, applies the difference incrementally when the source supports it and falls back to a full rebuild when it does not, then publishes the result atomically — readers see either the previous complete state or the new one, never a partial index. (REINDEX INDEX CONCURRENTLY is accepted too: the pass never blocks readers either way.)

A pass always compares against the source's current committed state — for a catalog-attached Iceberg table it forces a fresh table load even inside the server's max_table_staleness window. That is what makes REINDEX a freshness barrier: when it returns, everything committed before it is searchable. It requires the MAINTAIN privilege on the view (the same class as VACUUM).

A pass reads the source in a transaction of its own, not the caller's. Inside an explicit transaction it still sees every commit made before it started, and it never sees the caller's own uncommitted writes to the source. The index itself must be committed: an index created in the same, still-open transaction cannot be refreshed yet.

Only one pass runs per index at a time. A REINDEX that finds another pass running — manual or automatic — waits for it to finish and then runs its own pass against the source as it is at that moment. While it waits, pg_stat_progress_create_index shows it with command = 'REINDEX' and phase waiting for running reindex; it can be cancelled during the wait. If the source changes while a pass is running, the pass is repeated automatically.

SQL

COPY (SELECT 1 AS id, 'error timeout connecting to shard' AS message      UNION ALL SELECT 2, 'checkpoint completed')TO '${__TEST_DIR__}/app_logs_1.parquet' (FORMAT PARQUET);
CREATE VIEW app_logs AS    SELECT * FROM read_parquet('${__TEST_DIR__}/app_logs_*.parquet');
CREATE INDEX app_logs_idx ON app_logs USING inverted (id, message view_en);

count 2

Add a file behind the view's glob and refresh:

SQL

COPY (SELECT 3 AS id, 'error disk full on node-7' AS message)TO '${__TEST_DIR__}/app_logs_2.parquet' (FORMAT PARQUET);
REINDEX INDEX app_logs_idx;
SELECT count(*) FROM app_logs_idx WHERE message @@ ts_phrase('error');

count 1

 count
-------
     2

This is the building block of a full pipeline: rows land in a table any engine writes to, one REINDEX between load and query makes them searchable. The Search over Iceberg cookbook builds it from zero — catalog, index, freshness barrier, hybrid queries.

What a pass detects — and how much work it does — depends on the source:

Source	What a pass detects	Work done
Iceberg table	the diff between the indexed snapshot and the table's current one, including row-level deletes	delta — only the difference is indexed
File glob (Parquet/CSV/JSON, local or S3)	files that appeared, changed or disappeared	delta — unchanged files are not re-read
Everything else (base tables, attached databases, generic views)	any change	full rebuild each pass

Two caveats:

• If an Iceberg table's indexed snapshot has left the table's history (rollback, snapshot expiration), the pass falls back to a full rebuild — no sequence comparison can see deletes that were undone.
• An index that cannot re-derive row identity takes the rebuild road regardless of source: one built WITH (store_pk = 'none'), or over a view whose body caps rows with LIMIT.

Automatic refresh

The reindex_interval index option (milliseconds, 0 = off) runs the same pass periodically. It is shorthand for a job: a non-zero value creates a job named after the index with an AFTER <interval> schedule, so a pass starts that long after the previous one finished. Without the option, CREATE INDEX takes the session's reindex_interval setting, 0 by default. Retune it live with ALTER INDEX ... SET, where 0 drops the job. ALTER INDEX ... RESET (reindex_interval) applies the session setting again, so it drops the job only while that setting is 0:

SQL

CREATE INDEX app_logs_auto_idx ON app_logs USING inverted (id, message view_en)WITH (reindex_interval = 5000);
SELECT job_name, schedule FROM sdb_jobs() WHERE database_name = current_database() AND job_name = 'app_logs_auto_idx';
ALTER INDEX app_logs_auto_idx SET (reindex_interval = 60000);
SELECT schedule FROM sdb_jobs() WHERE database_name = current_database() AND job_name = 'app_logs_auto_idx';
ALTER INDEX app_logs_auto_idx SET (reindex_interval = 0);
SELECT count(*) AS jobs FROM sdb_jobs() WHERE database_name = current_database() AND job_name = 'app_logs_auto_idx';

 job_name          | schedule
-------------------+---------------------------
 app_logs_auto_idx | AFTER INTERVAL '00:00:05'

 schedule
---------------------------
 AFTER INTERVAL '00:01:00'

 jobs
------
    0

The job appears in sdb_jobs() with its run counters and last error, and the usual job statements apply to it: ALTER JOB ... SUSPEND pauses the refresh, and EXECUTE JOB runs one pass right away. The interval lives only in the job, so the index's reloptions do not list it. The job is owned by the index, the way a sequence is owned by a column: it is dropped together with the index, and DROP JOB drops it alone, which turns the refresh off and keeps the index, as ALTER INDEX ... SET (reindex_interval = 0) does. It targets the index by its internal id, so renaming the index keeps it working, but the job keeps its name: after ALTER INDEX ... RENAME, sdb_jobs(), ALTER JOB and EXECUTE JOB know it by the old one. Creating the job, at CREATE INDEX or at the ALTER INDEX ... SET that turns the refresh on, fails if a job with the same name already exists in the schema.

SQL

ALTER INDEX app_logs_auto_idx SET (reindex_interval = 60000);
ALTER INDEX app_logs_auto_idx RENAME TO app_logs_search_idx;
SELECT job_name, schedule FROM sdb_jobs() WHERE database_name = current_database() AND job_name LIKE 'app_logs%';
ALTER INDEX app_logs_search_idx RESET (reindex_interval);
SELECT count(*) AS jobs FROM sdb_jobs() WHERE database_name = current_database() AND job_name LIKE 'app_logs%';
ALTER INDEX app_logs_search_idx SET (reindex_interval = 60000);
DROP JOB app_logs_search_idx;
SELECT    (SELECT count(*) FROM sdb_jobs() WHERE database_name = current_database() AND job_name LIKE 'app_logs%') AS jobs,    (SELECT count(*) FROM duckdb_indexes() WHERE database_name = current_database() AND index_name = 'app_logs_search_idx') AS indexes;

 job_name          | schedule
-------------------+---------------------------
 app_logs_auto_idx | AFTER INTERVAL '00:01:00'

 jobs
------
    0

 jobs | indexes
------+---------
    0 |       1

The job is persisted with the index, so the refresh survives server restarts. A failed pass (source unreachable, empty glob) leaves the index serving its last published state and is recorded as the job's last error; the next pass retries. The pass runs in its own session and reads global settings — apply options the source needs with SET GLOBAL; a manual REINDEX uses the calling session's settings. It runs as the job's owner, which is the owner of the indexed view at the time the job is created: a later ALTER VIEW ... OWNER TO does not change it, and that role cannot be dropped while the job exists.

Snapshot and isolation

Between refreshes a view-backed index is a static snapshot: its postings are captured at CREATE INDEX (or the last refresh) and do not track source changes live — there is no background DML tracking as there is for base tables. A reader transaction keeps a consistent view of the index even if the underlying view is dropped concurrently.

See also

• Indexing External Data — Parquet/CSV/JSON on disk or S3, and attached PostgreSQL/ClickHouse
• Inverted Index — creating an index · Full-Text Search
• CREATE INDEX … USING inverted