HTAP for PostgreSQL
Reports and scans over whole tables run on a columnar copy that DBTrail keeps about a second behind your database. You connect to it with psql or your reporting tool, so those queries stop loading your PostgreSQL.
Heavy reads on a columnar copy, for PostgreSQL 15 to 18, Amazon RDS for PostgreSQL and Aurora PostgreSQL. DBTrail reads your changes through logical replication with the built-in pgoutput plugin, and installs nothing in your PostgreSQL.
Works with
Tables of 2 and 10 million rows, with 400 transactions a second written to them. Answers read the changes captured since the last snapshot. How current an answer is
Run them on DBTrail's port instead. The copy is columnar, so a scan over a whole table reads only the columns it needs.
Totals and rankings over whole tables.
SELECT warehouse, sum(amount) FROM order_lines GROUP BY warehouse;
Trends that read most of a table, without pulling it through your shared buffers.
SELECT date_trunc('month', created_at), count(*) FROM orders WHERE created_at >= '2023-01-01' GROUP BY 1;
Page 5,000 of a listing, read on the copy instead of your primary.
SELECT * FROM events ORDER BY id LIMIT 20 OFFSET 100000;
Every statement on port 5433 is checked before it runs. What a comparison against a real PostgreSQL found answering the same runs on the copy. The rest runs on your PostgreSQL, read-only, as a role you create; without that role, the copy answers everything. A statement the copy might answer differently goes to your PostgreSQL, even if that takes longer.
SELECT warehouse, sum(amount) FROM order_lines GROUP BY 1DBTrailanswers the sameSELECT * FROM customers WHERE id = 42PostgreSQLthe copy has no indexSELECT avg(amount) FROM ordersPostgreSQLnumeric keeps more digitsSELECT date_trunc('month', created_at), count(*) FROM orders GROUP BY 1DBTrailanswers the sameBEGIN; SELECT qty FROM stock WHERE sku = 'A-17'PostgreSQLinside a transactionUPDATE orders SET status = 'paid' WHERE id = 4827refusedthe port only readsThe list of what the copy may answer comes from running the same statements on PostgreSQL and on its copy. avg of a numeric, round of a double, regular expressions and every function nobody measured go to your PostgreSQL.
A statement that is not a read is refused before it is sent. The connection to your PostgreSQL is opened read-only. The role it connects as is one you create, with SELECT only.
psql and the usual drivers, with SCRAM-SHA-256 and TLS, prepared statements, and the catalog queries a reporting tool sends to list tables and columns.
DBTrail follows your WAL as a logical replication client, so there is no export to schedule. Fresh mode lays the changes captured since the last snapshot over the copy.
DBTrail checks these when it starts. When a required one is missing it stops, with the statement to run. It creates its replication slot and nothing else.
wal_level = logicalServer-wide, and it takes a restart.REPLICATIONFor the replication stream. A second role, with SELECT only, if you want statements forwarded.REPLICA IDENTITY FULL on each captured tableSo every UPDATE and DELETE carries the whole old row. It makes them write more WAL.max_slot_wal_keep_size setStrongly recommended. Unlimited, a stalled slot keeps WAL until the disk fills, and DBTrail warns while it is.DEFERRABLEThe copy is kept by key. A table with a deferrable one is refused, with the statement that fixes it.The exact SQL, and RDS and Aurora: PostgreSQL-side setup
DBTrail keeps the before and after of every row it captures. See a row as it was this morning, or get the SQL that puts back the rows a bad UPDATE or DELETE touched, including rows removed by ON DELETE CASCADE. You review it and run it.
-- the row as it was this morning SELECT * FROM _flashback.orders AS OF '2026-11-18 09:00:00' WHERE id = 4827; -- the SQL that puts it back UPDATE orders SET status = 'paid' WHERE id = 4827;
at, by or pivot, written without quotes, is a syntax error on the copy. "at" answers as PostgreSQL does.All of it: Limitations
Open source under Apache-2.0, for any use.
One command installs it with Docker. Add your PostgreSQL in the web interface, turn on the port, and connect.
psql "host=127.0.0.1 port=5433 dbname=shop user=app sslmode=require"
PostgreSQL guide
The port, the login, TLS, the forwarding role, and what your reporting tool sees when it lists your tables.
CREATE ROLE app_ro LOGIN PASSWORD '...';
Query with a PostgreSQL client