Zero-ETL search: query and index PostgreSQL and ClickHouse in place
SereneDB queries PostgreSQL and ClickHouse where they live, with no pipeline that copies them first. CREATE SERVER names a remote database once; its tables then join like local ones, with supported filters pushed into the source. A view and one inverted index add BM25 search: the postings live in SereneDB, and every other column is read from the source by key.
curl https://install.serenedb.com | shWhat is zero ETL? Reading the source instead of shipping a copy
Zero ETL means using data where it already lives, without a pipeline that extracts it, reshapes it and loads a copy into a second system first. A copy is only as fresh as its last successful run. A federated query reads the source when it runs, and a search index over a remote table keeps only postings and reads every other column from the source by key. Change the source below and see what each road returns; every result here ran on SereneDB 26.11.2 against PostgreSQL.
Copy
nightly jobSELECT title, price_cents, stock FROM products_copy WHERE description ILIKE '%waterproof%' ORDER BY sku;
- Dry bag$2940 left
- Rain jacket$1896 left
- Trail runner$12914 left
Federated query
reads the sourceSELECT title, price_cents, stock FROM shop.public.products WHERE description ILIKE '%waterproof%' ORDER BY sku;
- Dry bag$2940 left
- Rain jacket$1896 left
- Trail runner$12914 left
Search in place
products_idxSELECT title, price_cents, stock FROM products_idx WHERE description @@ 'waterproof' ORDER BY BM25(products_idx.tableoid) DESC;
- Dry bag$2940 left
- Rain jacket$1896 left
- Trail runner$12914 left
How a federated query works: two servers, one view, one index
A federated query is one SQL statement over tables in more than one database. CREATE SERVER makes a PostgreSQL or ClickHouse database a catalog you address as server.schema.table; supported filters are translated into each source’s own query, and the query logs show what each source received. An index over a view keeps postings and keys in SereneDB and reads everything else from the source. Hover a stage, or a part of the catalog.
The database you already run
CREATE SERVER connects before it saves anything, so a wrong host or password fails at once and leaves no catalog row. The server is guarded by the USAGE privilege, and its tables are reached through PostgreSQL’s own catalog, so nothing is declared table by table.
Events in ClickHouse
clickhouse_fdw talks to ClickHouse over its native protocol, on port 9000. An equality filter on a ClickHouse column reaches ClickHouse as a WHERE, and an index over a ClickHouse table keys on the table’s primary key, which is its ORDER BY key unless set separately.
Servers, a view and an index in one namespace
A server’s tables are addressed as server.schema.table. The index over the view stores postings, the key and the INCLUDE columns; every other column is read from PostgreSQL when a query asks for it. Build a search index on someone else’s database walks through the keys and the lookups.
One statement, two databases
Remote tables join like local ones. The query logs show what each source received: PostgreSQL the brand filter and the four columns the query reads, ClickHouse the two columns of pageviews. The join and the GROUP BY ran in SereneDB.
Matches are re-read by key
The index answers the match and returns the sku of each row; SereneDB then looks those skus up in PostgreSQL, in batches, for the columns the query needs, so a price changed a second ago shows. Key the index on the primary key: PostgreSQL gives an updated row a new ctid.
Postings are a snapshot
Rows inserted in PostgreSQL become searchable when the index is built again. CREATE OR REPLACE INDEX rebuilds it from the remote table and swaps it in when it commits; until then, other sessions keep using the old index.
Search joined with remote orders
A phrase match on the index joins remote orders in the same statement: the postings answer the phrase, PostgreSQL returns the order rows.
What lives where
postings here · rows therepostgres_fdw for PostgreSQL, clickhouse_fdw for ClickHouse. Parquet, CSV and Iceberg files in object storage have their own page: search over a data lake.
A PostgreSQL view keys on ctid unless key_columns names a column; a ClickHouse table keys on its primary key.
WITH (key_columns = 'sku')Columns read by key are current on every query; rows added since the last build become searchable after the next one.
A role queries a server only with USAGE on it, and ATTACH … READ_ONLY refuses writes.
GRANT USAGE ON FOREIGN SERVER shop TO analyst;Postgres federated query and search, in five steps
One CREATE SERVER makes the tables queryable; a view and one CREATE INDEX make them searchable. The rows stay in PostgreSQL. Tested with SereneDB 26.11.2 against PostgreSQL 17.11 on 2026-11-03: every statement and result below ran from an empty database.
CREATE SERVER shop FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'pg.internal', port '5432', database 'shop', user 'reader', password 'secret');
SELECT p.sku, p.title, sum(o.qty) AS sold FROM shop.public.products AS p JOIN shop.public.orders AS o ON o.sku = p.sku WHERE p.brand = 'Northpeak' GROUP BY p.sku, p.title ORDER BY sold DESC, p.sku;
CREATE TEXT SEARCH DICTIONARY en AS split_text(case := 'lower') | stem_words('en_US.UTF-8') WITH (frequency, position, norm); CREATE VIEW products_v AS SELECT sku, title, description, brand, price_cents, stock FROM shop.public.products; CREATE INDEX products_idx ON products_v USING inverted (sku, title en, description en) INCLUDE (brand) WITH (key_columns = 'sku');
SELECT sku, title, brand, price_cents, stock FROM products_idx WHERE description @@ 'waterproof' ORDER BY BM25(products_idx.tableoid) DESC, sku LIMIT 10;
CREATE OR REPLACE INDEX products_idx ON products_v USING inverted (sku, title en, description en) INCLUDE (brand) WITH (key_columns = 'sku');
What teams build on zero-ETL search
Search over the database you already run
A product catalog in PostgreSQL gets BM25 ranking and phrase search, joined with live orders in the same statement, without a pipeline that ships rows to a separate search engine and keeps them in sync.
Postgres full-text search →SELECT i.sku, i.title, sum(o.qty) AS sold FROM products_idx AS i JOIN shop.public.orders AS o ON o.sku = i.sku WHERE i.description @@ ts_phrase('running shoe') GROUP BY i.sku, i.title ORDER BY sold DESC, i.sku;
| sku | title | sold |
|---|---|---|
| TR-1 | Trail runner | 6 |
| RS-4 | Road shoe | 2 |
Entities in PostgreSQL, events in ClickHouse
Products live in PostgreSQL and page views in ClickHouse. One statement joins them, and an index over a ClickHouse table adds BM25 search to the reviews stored there, joined back to PostgreSQL.
Real-time analytics →SELECT p.sku, p.title, sum(v.views) AS views, p.stock FROM shop.public.products AS p JOIN analytics.events.pageviews AS v ON v.sku = p.sku WHERE p.brand = 'Northpeak' GROUP BY p.sku, p.title, p.stock ORDER BY views DESC, p.sku;
| sku | title | views | stock |
|---|---|---|---|
| TR-1 | Trail runner | 800 | 13 |
| RJ-2 | Rain jacket | 210 | 6 |
| HB-3 | Hiking boot | 75 | 3 |
A database another team runs
Other roles query a server only after GRANT USAGE, and ATTACH … READ_ONLY opens a PostgreSQL database for reading only: an INSERT through it is refused. The credentials stay in the server definition, which only superusers can read.
Database for AI agents →GRANT USAGE ON FOREIGN SERVER shop TO analyst; ATTACH 'host=pg.internal port=5432 dbname=shop user=reader password=secret' AS shop_ro (TYPE postgres, READ_ONLY);
Zero ETL is using data where it already lives, without a pipeline that extracts it, reshapes it and loads a copy into another system first. In SereneDB that means naming a database with CREATE SERVER or ATTACH, querying its tables in place, and building search indexes over them while the rows stay at the source.
ETL copies rows into a second system on a schedule, so the copy is only as fresh as its last successful run. Zero ETL drops the copy: the query reads the source, so an UPDATE in PostgreSQL shows in the next SELECT. ELT only changes the order, loading first and transforming in the warehouse; the copy stays.
AWS uses the name for integrations that copy data from an operational database such as Aurora, RDS or DynamoDB into Amazon Redshift, first in full and then as it changes; AWS builds and runs the pipeline, so you don’t. SereneDB reaches the same goal without moving the data: CREATE SERVER queries the database in place, and when a local table is wanted, a subscription to a Postgres publication keeps it current.
A federated query is one SQL statement over tables that live in different databases, answered without loading them into one place first. In SereneDB a remote database is a catalog addressed as server.schema.table, so a federated query is an ordinary JOIN, and supported filters are translated into each source’s own query.
No. Federated search sends one query to several search engines and merges their result lists. Here one inverted index in SereneDB covers the remote rows and ranks them with BM25; PostgreSQL or ClickHouse store the rows and return the columns a query asks for, by key.
Name the database with CREATE SERVER … FOREIGN DATA WRAPPER postgres_fdw, create a view over server.schema.table, and build CREATE INDEX … USING inverted on the view, with a text search dictionary on the text columns. Key it on the primary key with key_columns, and rebuild it with CREATE OR REPLACE INDEX when new rows should become searchable. The external databases docs cover the options.
Yes. With a postgres_fdw server and a clickhouse_fdw server, shop.public.products joins analytics.events.pageviews in one SELECT. In the test, PostgreSQL received the brand filter, ClickHouse returned the sku and views columns, and the join and the GROUP BY ran in SereneDB. SereneDB vs ClickHouse covers indexing ClickHouse tables in place.
A federated query reads the source when it runs. A search index over a remote table returns current values for every column it reads by key; the set of searchable rows is the one from its last build.
Yes, when a table should sit next to its index: CREATE SUBSCRIPTION subscribes to a Postgres publication, copies the published tables once, then applies every committed INSERT, UPDATE, DELETE and TRUNCATE to local tables of the same name, which you create first. An inverted index on the local table picks the changes up at its next refresh, every second by default.
Only for work a query does not do: cleansing, reshaping data into a model you want stored, or keeping history the source discards. That step can be SQL over the remote tables, such as CREATE TABLE … AS SELECT from a server. Zero ETL removes the copies that exist only so that another engine can read the data.
Get started with SereneDB
Name your database with CREATE SERVER, then join it, aggregate it, or put a BM25 index over it while the rows stay where they are.