Zero-ETL Search with SereneDB
Zero ETL means using data without building an ETL pipeline that copies it first. SereneDB queries PostgreSQL and ClickHouse tables where they live and indexes them for search while the rows stay remote.
One CREATE SERVER per database, and its tables are server.schema.table in a query: joined with each other, with local tables, or behind a full-text index.
What Is Zero ETL?
Zero ETL is data integration without an ETL pipeline in between. ETL stands for extract, transform, load: a job that copies transactional data from the systems that write it into a data warehouse or a search cluster, on a schedule somebody has to own. There are two ways to remove that job.
The first is managed replication: AWS's zero-ETL integrations replicate Aurora, RDS, DynamoDB and other sources into Amazon Redshift after an initial load, and DynamoDB and DocumentDB into OpenSearch Service for search; AWS runs the pipeline. The copy still exists; you stop maintaining it. The second is what AWS's own definition calls “querying across data silos without the need for data movement”: the federated query. What is a federated query? One SQL statement over tables in more than one database, with supported filters sent down to each source so it returns fewer rows. SereneDB does the second and adds search: an index over a remote table whose rows stay in the source.
replicate source ──copy──▶ warehouse ◀── SELECT
in place source ◀──────── SELECT ── serenedb
index: postings + keys + INCLUDEWhy Zero ETL: Every Copy Is a Pipeline to Run
The cost of ETL is rarely the first load. It is every run after it.
The pipeline outlives its author
Every copy has a job behind it: extract, reshape, load, retry, alert. A column is renamed at the source, the job fails overnight, and the copy is only as fresh as the last run that succeeded.
Search is one more copy
Full-text search usually means another system with its own ingest: rows shipped from PostgreSQL to a search cluster, ids shipped back, and application code joining the two result sets.
Query and index where the rows live
A federated query reads PostgreSQL and ClickHouse at query time. An index over a remote table keeps postings, plus any INCLUDE columns, in SereneDB and fetches every other value from the source by key.
How SereneDB Does Zero ETL: Federated Query Plus Search
SereneDB works as a federated query engine over the databases you already run: a foreign server turns each one into a catalog, and the rest is SQL.
Remote tables via CREATE SERVER
A foreign server names a remote database and connects before anything is saved. It survives restarts and is guarded by USAGE. For databases, two wrappers are documented, postgres_fdw and clickhouse_fdw; ATTACH is the per-session alternative for PostgreSQL.
CREATE SERVER shop FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'pg.internal', port '5432', database 'shop',
user 'reader', password 'secret');
CREATE SERVER analytics FOREIGN DATA WRAPPER clickhouse_fdw
OPTIONS (host 'clickhouse.internal', port '9000', database 'events');
GRANT USAGE ON FOREIGN SERVER shop TO analyst;One statement, two engines
Remote tables join like local ones. Equality, IN and other supported predicates are translated into the remote query, so PostgreSQL filters its own rows before any of them leave it.
SELECT p.sku, p.title, sum(v.views) AS views
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
ORDER BY views DESC;An index over a remote table
Index a view over the remote table and a phrase match joins remote orders in the same statement. Matches are re-read from the source by key: the ctid for PostgreSQL, the primary key for ClickHouse, or key_columns.
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;Step-by-Step: Zero-ETL Search over Postgres in 5 Minutes
Name the PostgreSQL database
It connects before it saves anything, so a wrong host or password fails here, not on the first query.
CREATE SERVER shop FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'pg.internal', port '5432', database 'shop',
user 'reader', password 'secret');Query it where it lives
Two remote tables joined and aggregated in one statement; the brand filter runs inside PostgreSQL.
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;Index a view over the remote table
Key on sku, the primary key: PostgreSQL moves an updated row to a new ctid, and the index finds a match again only by the key it stored.
CREATE TEXT SEARCH DICTIONARY en AS
split_text(case := 'lower') | stem_words('en_US.UTF-8')
WITH (frequency, position);
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');Search it
brand comes from the index; price_cents and stock are read from PostgreSQL by sku for the matches only, so a price changed a second ago shows.
SELECT sku, title, brand, price_cents, stock
FROM products_idx
WHERE description @@ 'waterproof'
ORDER BY BM25(products_idx.tableoid) DESC, sku
LIMIT 10;Refresh when products change
Postings are a snapshot. REINDEX rebuilds them in full, re-reading the remote table, and publishes atomically; it needs MAINTAIN on the view. reindex_interval runs the same pass on a loop as the index owner, so size the interval to the table.
-- one pass, on demand
REINDEX INDEX products_idx;
-- or every five minutes, in the background
ALTER INDEX products_idx SET (reindex_interval = 300000);Use Cases for Zero-ETL Search
Files and Iceberg tables in object storage are the other half of it: see search over a data lake.
Search over an operational database
A product catalog in PostgreSQL gets BM25 ranking and phrase search without a search cluster or a pipeline that ships rows to one. Walkthrough: a search index on someone else's database.
PostgreSQL entities, ClickHouse events
Products in PostgreSQL, clicks in ClickHouse: one statement joins them, and an index over either adds full-text search.
A database another team runs
Other roles query a server only with USAGE on it, and ATTACH … READ_ONLY opens a PostgreSQL database for reading only. Any PostgreSQL-compatible database attaches the same way.
Zero-ETL Limitations in SereneDB
Querying in place moves work to query time, and it stops at these edges.
PostgreSQL, ClickHouse, DuckDB
For databases, CREATE SERVER documents postgres_fdw and clickhouse_fdw, and ATTACH adds PostgreSQL databases and DuckDB files for a session. MySQL, SQL Server, Snowflake and SaaS APIs are not covered by the docs, so plan on a copy for those.
Snapshots, not change capture
AWS pitches its integrations for near-real-time analytics. SereneDB does not replicate: a federated query reads current rows, but an index over a remote table rebuilds in full on every pass, and rows added between passes are not yet searchable.
Not PostgreSQL's FDW feature
No CREATE FOREIGN TABLE, no USER MAPPING (one connection identity per server), no custom wrappers. Writes through a server are documented for PostgreSQL.
Frequently Asked Questions
Using data without an ETL pipeline that copies it first. Managed replication, as in AWS's Redshift integrations, hides the pipeline; a federated query removes it by reading each source at query time. SereneDB does the second, plus search indexes over remote tables.
One SQL statement over tables in several databases. In SereneDB each remote database is a server you address as server.schema.table, so a PostgreSQL to ClickHouse join is an ordinary JOIN, with supported filters pushed down to each source.
No. Federated search sends one query to several search engines and merges their result lists. SereneDB builds one inverted index over the remote rows and ranks them with BM25 itself; the remote databases store the rows and return them by key.
Name the database with CREATE SERVER, or ATTACH a Postgres database for a session. Create a view over server.schema.table, build CREATE INDEX … USING inverted on the view, and refresh it with REINDEX or reindex_interval.
Create a server, or ATTACH, per database and name each table by its catalog: shop.public.products joins analytics.events.pageviews and any local table. One transaction may write to only one attached database.
Database sources are PostgreSQL and ClickHouse via CREATE SERVER, PostgreSQL and DuckDB via ATTACH; indexes over remote tables are snapshots rebuilt in full on each refresh; no foreign tables or user mappings. A ClickHouse table without primary-key metadata needs key_columns, or it returns only indexed and INCLUDE columns.
Yes, for reshaping, cleansing, and history the source does not keep: those need a pipeline or a table of their own. Zero ETL removes the copies that exist only so that another engine can read the data.
Get Started with SereneDB
One CREATE SERVER makes your PostgreSQL tables queryable; a view and one CREATE INDEX make them searchable. The rows stay in PostgreSQL.