@tempest98.bsky.social
Working on a cache for PostgreSQL: https://github.com/tempest98/pgcache
How is my last post 18 days ago, now there is more posting to catch up on....
let's summarize what I've done since my last substantive post:
I did complete the sql join work, so the cache now supports simple equi-joins, and using CDC keeps the cache up to date or invalidates queries as necessary.
let's summarize what I've done since my last substantive post:
I did complete the sql join work, so the cache now supports simple equi-joins, and using CDC keeps the cache up to date or invalidates queries as necessary.
November 5, 2025 at 10:29 PM
How is my last post 18 days ago, now there is more posting to catch up on....
let's summarize what I've done since my last substantive post:
I did complete the sql join work, so the cache now supports simple equi-joins, and using CDC keeps the cache up to date or invalidates queries as necessary.
let's summarize what I've done since my last substantive post:
I did complete the sql join work, so the cache now supports simple equi-joins, and using CDC keeps the cache up to date or invalidates queries as necessary.
Oy, well, it's been a while since I last posted. I ran into some technical difficulties with my code that were disappointing and got de-motivated for a couple of weeks. I was hoping the cache would never have to invalidate anything, but that isn't going to be realistic.
October 18, 2025 at 5:49 PM
Oy, well, it's been a while since I last posted. I ran into some technical difficulties with my code that were disappointing and got de-motivated for a couple of weeks. I was hoping the cache would never have to invalidate anything, but that isn't going to be realistic.
I mostly have subquery support working and have moved on to supporting VALUES clauses, and learning a bunch about SQL I didn't know, or at least the dialect #postgres uses. The surprising one being that you don't have to have any columns in the SELECT target list, so "SELECT FROM ..." is valid.
August 28, 2025 at 12:54 AM
I mostly have subquery support working and have moved on to supporting VALUES clauses, and learning a bunch about SQL I didn't know, or at least the dialect #postgres uses. The surprising one being that you don't have to have any columns in the SELECT target list, so "SELECT FROM ..." is valid.
I've also been working on a small blog post updating this cool article for PG 17: blog.jooq.org/10-cool-sql-...
Spoiler: only two optimizations have been added since the tests done against PG 9.6, and even then one of the changes is only partial.
Spoiler: only two optimizations have been added since the tests done against PG 9.6, and even then one of the changes is only partial.
10 Cool SQL Optimisations That do not Depend on the Cost Model
Cost Based Optimisation is the de-facto standard way to optimise SQL queries in most modern databases. It is the reason why it is really really hard to implement a complex, hand-written algorithm i…
blog.jooq.org
August 26, 2025 at 2:20 AM
I've also been working on a small blog post updating this cool article for PG 17: blog.jooq.org/10-cool-sql-...
Spoiler: only two optimizations have been added since the tests done against PG 9.6, and even then one of the changes is only partial.
Spoiler: only two optimizations have been added since the tests done against PG 9.6, and even then one of the changes is only partial.
The path for updating the CDC flows for handling a simple join has become twistier than expected.
I'm setting off on the path to add support for subqueries to the AST structure I've been using. I knew I would need to do this eventually, but I thought eventually would be much later.
I'm setting off on the path to add support for subqueries to the AST structure I've been using. I knew I would need to do this eventually, but I thought eventually would be much later.
August 26, 2025 at 2:16 AM
The path for updating the CDC flows for handling a simple join has become twistier than expected.
I'm setting off on the path to add support for subqueries to the AST structure I've been using. I knew I would need to do this eventually, but I thought eventually would be much later.
I'm setting off on the path to add support for subqueries to the AST structure I've been using. I knew I would need to do this eventually, but I thought eventually would be much later.
I have my first join query returning results from the cache. It's been a bunch of work updating code flows that were assuming a single table to work with and the code is not pretty. Next step is updating the CDC related flows, and after that, a bunch of cleanup.
August 17, 2025 at 11:07 PM
I have my first join query returning results from the cache. It's been a bunch of work updating code flows that were assuming a single table to work with and the code is not pretty. Next step is updating the CDC related flows, and after that, a bunch of cleanup.
I've started work to bring support for simple joins to the cache. This ended up entailing a fair amount of refactoring, some expected and some not so much.
August 13, 2025 at 11:44 PM
I've started work to bring support for simple joins to the cache. This ended up entailing a fair amount of refactoring, some expected and some not so much.
I removed a hardcoded reference to the "public" schema in the code. Attempting to handle schemas properly, but I'm sure I'll have to go through and make another pass at getting it right.
The hardcoded name was bothering me, so happy to have taken this step.
The hardcoded name was bothering me, so happy to have taken this step.
August 9, 2025 at 8:48 PM
I removed a hardcoded reference to the "public" schema in the code. Attempting to handle schemas properly, but I'm sure I'll have to go through and make another pass at getting it right.
The hardcoded name was bothering me, so happy to have taken this step.
The hardcoded name was bothering me, so happy to have taken this step.
I got in some quick wins today - added handling for truncate logical replication messages. The only message I still have left to handle is the type definition message, which I haven't even thought about where to start for those yet.
August 8, 2025 at 11:05 PM
I got in some quick wins today - added handling for truncate logical replication messages. The only message I still have left to handle is the type definition message, which I haven't even thought about where to start for those yet.
Worked on handling relation messages from the #PostgreSQL logical replication stream. At first I thought I'd be able to compare the existing table to the table defined in the replication stream, and then realized that makes no sense.
August 7, 2025 at 2:33 AM
Worked on handling relation messages from the #PostgreSQL logical replication stream. At first I thought I'd be able to compare the existing table to the table defined in the replication stream, and then realized that makes no sense.
Added support for reading settings from a TOML file. 😌
August 5, 2025 at 2:53 AM
Added support for reading settings from a TOML file. 😌
Finally created a GitHub repo for the project, no license yet: github.com/tempest98/pg...
GitHub - tempest98/pgcache: PostgreSQL proxy for query aware caching, using CDC for cache maintenance.
PostgreSQL proxy for query aware caching, using CDC for cache maintenance. - tempest98/pgcache
github.com
August 5, 2025 at 2:41 AM
Finally created a GitHub repo for the project, no license yet: github.com/tempest98/pg...
I've found the ParseResult structure returned from pg_query::parse() to be fairly tedious to navigate and use. I've now created my own structures, the top level called SqlQuery, and a function to convert a ParseResult to a SqlQuery.
August 5, 2025 at 2:33 AM
I've found the ParseResult structure returned from pg_query::parse() to be fairly tedious to navigate and use. I've now created my own structures, the top level called SqlQuery, and a function to convert a ParseResult to a SqlQuery.
I haven't posted in a while because I moved back to LA from Munich.
I was able to get some work done last week, which was mainly finding a bunch of mistakes and kind of dumb things I did. So productive week because the code is now in a much better place.
I was able to get some work done last week, which was mainly finding a bunch of mistakes and kind of dumb things I did. So productive week because the code is now in a much better place.
July 30, 2025 at 7:13 PM
I haven't posted in a while because I moved back to LA from Munich.
I was able to get some work done last week, which was mainly finding a bunch of mistakes and kind of dumb things I did. So productive week because the code is now in a much better place.
I was able to get some work done last week, which was mainly finding a bunch of mistakes and kind of dumb things I did. So productive week because the code is now in a much better place.
I started using the iddqd crate, it is perfect for a couple of use cases I have in the code.
docs.rs/iddqd/latest...
docs.rs/iddqd/latest...
iddqd - Rust
Maps where keys are borrowed from values.
docs.rs
July 12, 2025 at 8:17 AM
I started using the iddqd crate, it is perfect for a couple of use cases I have in the code.
docs.rs/iddqd/latest...
docs.rs/iddqd/latest...
I got the logical replication stream handling working, the current code is now functionally back to parity with a PoC I had written first.
The architecture and the code are on a much better foundation. I'm still figuring out a good design for the threads handling the cache.
The architecture and the code are on a much better foundation. I'm still figuring out a good design for the threads handling the cache.
July 12, 2025 at 8:10 AM
I got the logical replication stream handling working, the current code is now functionally back to parity with a PoC I had written first.
The architecture and the code are on a much better foundation. I'm still figuring out a good design for the threads handling the cache.
The architecture and the code are on a much better foundation. I'm still figuring out a good design for the threads handling the cache.
The caching part of the cache is now caching data. In implementing it I've been trying to come up with a threading architecture that makes sense. I must have rewritten the same chunk of code 3 or 4 times to come up with a design that works and that I like. Although, I don't really like it yet.
July 11, 2025 at 11:03 AM
The caching part of the cache is now caching data. In implementing it I've been trying to come up with a threading architecture that makes sense. I must have rewritten the same chunk of code 3 or 4 times to come up with a design that works and that I like. Although, I don't really like it yet.
I started working on the caching part of the cache. Still no caching happening though. I added in the check if the query meets the criteria for caching, in which case the query is sent to the cache handling thread, the query is run, and results are returned.
July 7, 2025 at 12:22 PM
I started working on the caching part of the cache. Still no caching happening though. I added in the check if the query meets the criteria for caching, in which case the query is sent to the cache handling thread, the query is run, and results are returned.
a couple of updates - the first test is committed, an integration test that runs a few queries to verify the proxy is working. Learned about the CARGO_BIN_EXE_<name> environment variable to launch the executable application to be able to test against it.
pgtemp - Rust
pgtemp is a Rust library and cli tool that allows you to easily create temporary PostgreSQL servers for testing without using Docker.
docs.rs
July 3, 2025 at 1:03 PM
a couple of updates - the first test is committed, an integration test that runs a few queries to verify the proxy is working. Learned about the CARGO_BIN_EXE_<name> environment variable to launch the executable application to be able to test against it.
I started using Error Set for errors. Still not sure the best way to structure errors, so this seems like a good start. I'm using one error set per module, which might not be the intended usage. It is working for now, we'll see how it evolves.
docs.rs/error_set/la...
docs.rs/error_set/la...
error_set - Rust
Error Set
docs.rs
July 1, 2025 at 11:41 AM
I started using Error Set for errors. Still not sure the best way to structure errors, so this seems like a good start. I'm using one error set per module, which might not be the intended usage. It is working for now, we'll see how it evolves.
docs.rs/error_set/la...
docs.rs/error_set/la...
Managed to get md5 passthrough auth to work, and scram authentication came along for the ride. Excited to have it working.
Next up, figuring out how to get tests in place and to work on error handling. and after that, maybe get to start working on the actual caching part of things.
Next up, figuring out how to get tests in place and to work on error handling. and after that, maybe get to start working on the actual caching part of things.
June 28, 2025 at 4:06 PM
Managed to get md5 passthrough auth to work, and scram authentication came along for the ride. Excited to have it working.
Next up, figuring out how to get tests in place and to work on error handling. and after that, maybe get to start working on the actual caching part of things.
Next up, figuring out how to get tests in place and to work on error handling. and after that, maybe get to start working on the actual caching part of things.
tokio stream merge worked quite nicely. back to having the proxying working again (with only trust authentication), the foundation to inspect and act on the messages is now in place.
The postgres startup and authentication flow is a little tricky, will start by trying to get md5 auth to work.
The postgres startup and authentication flow is a little tricky, will start by trying to get md5 auth to work.
June 28, 2025 at 11:39 AM
tokio stream merge worked quite nicely. back to having the proxying working again (with only trust authentication), the foundation to inspect and act on the messages is now in place.
The postgres startup and authentication flow is a little tricky, will start by trying to get md5 auth to work.
The postgres startup and authentication flow is a little tricky, will start by trying to get md5 auth to work.
After getting the raw proxying working, I thought modifying it to inspect and act on the actual messages would be fairly straightforward. Spoiler: it was not.
Spent a couple of days bagging my head on async, select!, and mutability; and wandering down few dead ends.
Spent a couple of days bagging my head on async, select!, and mutability; and wandering down few dead ends.
June 27, 2025 at 11:15 AM
After getting the raw proxying working, I thought modifying it to inspect and act on the actual messages would be fairly straightforward. Spoiler: it was not.
Spent a couple of days bagging my head on async, select!, and mutability; and wandering down few dead ends.
Spent a couple of days bagging my head on async, select!, and mutability; and wandering down few dead ends.
Now I'm wishing for a spawn_local_scoped(), looks like that won't be in the cards. Here's a good article about why not: without.boats/blog/the-sco...
and a GitHub issue about it that has some good discussion: github.com/tokio-rs/tok...
and a GitHub issue about it that has some good discussion: github.com/tokio-rs/tok...
The Scoped Task trilemma
without.boats
June 23, 2025 at 8:39 AM
Now I'm wishing for a spawn_local_scoped(), looks like that won't be in the cards. Here's a good article about why not: without.boats/blog/the-sco...
and a GitHub issue about it that has some good discussion: github.com/tokio-rs/tok...
and a GitHub issue about it that has some good discussion: github.com/tokio-rs/tok...
I have the raw proxying of bytes working, which turns out to be simple in Rust and Tokio. It took me a little bit to find my way there. I can start working on interpreting the stream of data as part of the Postgres protocol. I'll need to improve the error handling eventually as well.
June 23, 2025 at 8:36 AM
I have the raw proxying of bytes working, which turns out to be simple in Rust and Tokio. It took me a little bit to find my way there. I can start working on interpreting the stream of data as part of the Postgres protocol. I'll need to improve the error handling eventually as well.