PostgreSQL CDC to a Redis-compatible cache: lag, throughput and recovery measured
We streamed a PostgreSQL table into KiviDB with Data integration and timed it: a committed change reached KiviDB in 4.8 ms at the median, bursts moved at about 2,600 rows a second, and every...
Caching rows from PostgreSQL usually means writing to both, or expiring and refetching. Data integration instead reads PostgreSQL's change stream and keeps a KiviDB copy up to date. We streamed a 50,000-row table and timed every step from the database's own region, then fixed what the study showed was slow.
Measured in the same region
- 4.8 ms: from commit to KiviDB, median 300 single-row updates, each timed from its COMMIT returning to the new value being readable. 95th percentile 5.2 ms.
- 2,600: rows a second in a burst 10,000 inserts, as one transaction or as 100 commits of 100 rows, from the first write to the last row readable.
- 1,000 / 1,000: sampled rows identical Every field of 1,000 random rows matched, and both sides held 60,000 rows after inserts, updates and 10,000 deletes.
Source: PostgreSQL 16 in AWS eu-central-1, TLS required. Target: a free-tier KiviDB Cloud database in the same region. The clock: one task in that region, reading both.
How we ran it
- The source PostgreSQL 16.4 with logical replication on, in eu-central-1, reachable only from KiviDB Cloud's addresses, TLS required. An orders table of 50,000 rows: id, customer, status, total, item count and a timestamp, 4.5 MB.
- The pipeline One Data integration pipeline: public.orders to one hash per row under order:{id}. It snapshots the table first, then streams changes from a replication slot of its own.
- One change at a time 300 times: update one row's status, and from the moment COMMIT returns, read the key in KiviDB every millisecond until the new value appears.
- Bursts and deletes 10,000 inserts as 100 commits of 100 rows, then 10,000 in a single transaction, then a single DELETE of 10,000 rows; each timed until its last row arrived or left.
- Agreement 1,000 random rows compared field by field, and the row counts of both sides compared, after everything else had run.
The first run was slow; the second was not
The first measurements showed every row waiting for the previous one's reply. We changed the pipeline to send a transaction's writes together, deployed it, and measured again. Rows per second, end to end.
| Before the change | After | |
|---|---|---|
| Burst, 100 commits of 100 rows | 606 | 2,594 |
| Burst, one transaction of 10,000 | 611 | 2,674 |
A single change was already fast (4.8 ms before and after); waiting on every row only cost time when many arrived at once.
Seconds to catch up
The initial copy of 50,000 rows, and a DELETE of 10,000, before and after the change. Lower is better.
| Before the change | After | |
|---|---|---|
| Snapshot of 50,000 rows | ~95 s | ~14 s |
| Delete of 10,000 rows | 14.3 s | 0.77 s |
Snapshot times are read from the pipeline's progress, which it reports every few seconds, so they are approximate; the delete times are measured directly.
Three findings worth keeping
- Ordered does not mean one at a time Changes must land in commit order, and the first version kept that order by waiting for each write. Writes on one connection already arrive in order, so a transaction's writes can all be in flight and still be confirmed before its position is saved.
- A full target stops, then resumes In the first run the free database ran out of memory mid-burst. The pipeline stopped with an error, and once there was room it carried on from its saved position and ended with an exact match. Its error now says the database is full, not just that a transaction was discarded.
- No dual writes The application writes only to PostgreSQL. Inserts, updates and deletes reach KiviDB on their own, in order, within milliseconds, so a cache never holds a row the database has already changed for long.
Every figure in one table
| Measure | Before the change | After |
|---|---|---|
| Single-row change, median | 4.8 ms | 4.8 ms |
| Single-row change, 95th percentile | 6.5 ms | 5.2 ms |
| 10,000 rows, 100 commits | 606 rows/s | 2,594 rows/s |
| 10,000 rows, one transaction | 611 rows/s | 2,674 rows/s |
| Delete of 10,000 rows | 14.3 s | 0.77 s |
| Snapshot of 50,000 rows | ~95 s | ~14 s |
| Sampled rows identical | 1,000 / 1,000 | 1,000 / 1,000 |
Each change is timed from its COMMIT returning, by polling KiviDB once a millisecond; one KiviDB round trip took 1.6 ms, so very small lags are rounded up to it.
Questions
Was this a production-sized database?
No. The target was a free-tier database on a shared host with 100 MB of memory, and the source a small PostgreSQL container. A dedicated database would have more room; the timings would differ but not the method.
Which sources does Data integration read?
PostgreSQL through logical replication and MySQL through its binary log. Each table maps to a key template, as a hash or a JSON document, with optional column lists, renames and a time to live.
What does a pipeline cost?
$0.04 an hour while it runs, plus $0.10 per GB moved past the first 10 GB each month. A pipeline needs credit to start and stops when the organization runs out.
What happens if the pipeline stops?
It keeps its replication slot and its position. Starting it again carries on from where it stopped; a deleted pipeline drops its slot so PostgreSQL stops keeping WAL for it.
Other case studies
- RAG retrieval on SciFact: Keyword, vector and hybrid search scored on 300 scientific claims.
- Semantic cache on Banking77: 13,083 support questions: model calls saved against wrong answers.
- Agent memory on LoCoMo: 1,540 questions from retrieved memories against the whole history.
Keep a table current in KiviDB
Create a database, add a pipeline from PostgreSQL or MySQL, and read rows as keys a few milliseconds after they commit.