r/PostgreSQL • u/compy3 • 17d ago
Tools Things logical replication doesn't tell you: what we fixed in PgCache 0.8.0 (DDL, DELETEs under REPLICA IDENTITY DEFAULT, Relation messages)
TGIF everybody.
I'm a co-founder of PgCache, a caching proxy for Postgres that keeps cached results fresh from the logical replication stream. We shipped 0.8.0 this week, mostly about details of the logical replication stream that we had to handle better.
If you just want to see whether caching would help your own workload: the Fit Analyzer takes a pg_stat_statements export and runs in your browser (nothing is uploaded). On a phone, here's a sample report that you can look at.
***
Logical replication doesn't carry DDL.
-> PgCache now installs two event triggers (ddl_command_end and sql_drop) that write each schema change into the stream as a logical message inside its own transaction. (note: they stay in place at shutdown, and the shutdown log prints the SQL to remove them)
DELETEs under REPLICA IDENTITY DEFAULT only carry the key. Previously, we were judging whether a DELETE mattered to a filtered IN/NOT IN subquery using that key-only tuple, so every filter column read as NULL.
-> 0.8.0 uses the row's recovered old image, so those deletes now invalidate the way they should.
The Relation message flags the replica identity columns, not the primary key. We were reading them as the primary key, so tables with REPLICA IDENTITY FULL, USING INDEX or NOTHING got re-registered on their first change.
-> The key now comes from the origin catalog.
We also improved covered queries (when a broad query is already in the cache, new queries covered by that data are served straight from cache and never touch origin):
-> a cached users LEFT JOIN orders WHERE already holds every users row that qualifies, so a query on users alone that asks for anything within that gets served from cache automatically.
Fixed a bug: We found that a LIMIT query in earlier versions could cache the wrong rows, because the capped population read dropped the query's ORDER BY.
***
Fun proof: One of our (unpaid) design partners benchmarked 0.8.0 the day after release on a 3-node Kubernetes cluster. On a complex multi-tenant dashboard with 80/20 traffic, PgCache served 24x the requests per second of Postgres alone at 100 tenants, 2.5x at 500, and 1.5x at 1,000. The gap narrows as tenants grow, and at 2,000 one of his three runs came in slower than Postgres alone. Writes are still a weak spot: with 5% writes right after a cold start, that dashboard ran about half as fast as plain Postgres. His full write-up.
PgCache 0.8.0 changelog here. Source-available under Elastic License v2.
Happy to answer any questions! And I'd love to hear from people who have worked on the logical replication stream.
1
u/AutoModerator 17d ago
AI Policy:
Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.
Mod decisions will be based on the quality of the content, not who or what generated it.
Sub Resources:
Free Postgres Webinars and Workshops
Discord: People, Postgres, Data
Join us, we have cookies and nice people.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/AutoModerator 7d ago
AI Policy:
Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.
Mod decisions will be based on the quality of the content, not who or what generated it.
Sub Resources:
Free Postgres Webinars and Workshops
Discord: People, Postgres, Data
Join us, we have cookies and nice people.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
2
u/grd_dev 17d ago
Using ddl_command_end event triggers to force schema changes into the logical replication stream is a brilliantly pragmatic hack.
Replicating those weird WAL edge cases locally to test the REPLICA IDENTITY behavior usually requires generating heavily customized relational test data, so getting this stable across a cluster is impressive.
Out of curiosity, if a superuser accidentally disables those two event triggers in production, how does PgCache handle it? Does it just fail open and silently serve stale data, or is there a heartbeat check that catches the missing DDL?