HTAP for PostgreSQL

Heavy PostgreSQL reports,
on a copy of your tables.

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.

$ curl -fsSL https://raw.githubusercontent.com/dbtrail/dbtrail/main/install.sh | sh
Apache-2.0

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

Measured

1.2 seconds behind your PostgreSQL at the median, never more than 3.2.

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

15 to 18
PostgreSQL versions tested in CI
0
extensions, plugins or triggers in your server
1
replication slot, the only thing DBTrail creates

The reads that keep your PostgreSQL busy.

Run them on DBTrail's port instead. The copy is columnar, so a scan over a whole table reads only the columns it needs.

Reports

Totals and rankings over whole tables.

SELECT warehouse, sum(amount)
FROM order_lines
GROUP BY warehouse;

Years of history

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;

Deep pages

Page 5,000 of a listing, read on the copy instead of your primary.

SELECT * FROM events
ORDER BY id
LIMIT 20 OFFSET 100000;

Each statement goes to the copy only if the copy answers it as PostgreSQL would.

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.

Port 5433copy about a second behind
  • SELECT warehouse, sum(amount) FROM order_lines GROUP BY 1DBTrailanswers the same
  • SELECT * FROM customers WHERE id = 42PostgreSQLthe copy has no index
  • SELECT avg(amount) FROM ordersPostgreSQLnumeric keeps more digits
  • SELECT date_trunc('month', created_at), count(*) FROM orders GROUP BY 1DBTrailanswers the same
  • BEGIN; SELECT qty FROM stock WHERE sku = 'A-17'PostgreSQLinside a transaction
  • UPDATE orders SET status = 'paid' WHERE id = 4827refusedthe port only reads

How the list was made

The 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.

Only reads reach 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.

Your client and tools

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.

It follows your WAL

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.

What it needs from your PostgreSQL.

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.

The exact SQL, and RDS and Aurora: PostgreSQL-side setup

And when a change goes wrong.

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.

Recovery and time travel
-- 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;

What to know before you rely on it.

What it is tested on
End to end against PostgreSQL 15 to 18 in CI, and on RDS and Aurora. Read the limitations before you point it at production.
The copy is behind your PostgreSQL
About a second in fresh mode, until the next snapshot otherwise. A read that must see a write made a moment ago belongs on your PostgreSQL.
The port only reads
Writes are refused. Your application keeps writing to your PostgreSQL, and nothing fails over to DBTrail. Keep your replicas and your backups.
Its index runs in its own database
DBTrail keeps the history it captures in a MySQL 8.4 that the Docker install runs for you in a container of its own, or in one you bring. You run it, as you run DBTrail: disk, backups and upgrades.
Big transactions arrive whole
PostgreSQL sends a transaction once it commits, so one that changed millions of rows leaves the copy behind until it has arrived.
A few names need quotes on the copy
A column named at, by or pivot, written without quotes, is a syntax error on the copy. "at" answers as PostgreSQL does.

All of it: Limitations

Start on one machine.

Open source under Apache-2.0, for any use.

Try it

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

Connect a client

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