iiinova — where your digital self keeps evolving

tempest98.bsky.social @tempest98.bsky.social · 18/08/2026

Giving a talk about PgCache this Wednesday. First talk, excited! - hopefully it goes well

tempest98.bsky.social @tempest98.bsky.social · 18/05/2026

Hi @kartoonistkelly.bsky.social, there is not one currently. I've chatted previously with my co-founder (@compy3.bsky.social) about setting one up though. Most likely it would end up on Discord.

000

Reposted by @tempest98.bsky.social

PgCache 🐘🦀 @pgcache.bsky.social · 05/05/2026

Stop scaling Postgres with brute force. Read replicas copy 100% of your data for a fraction of queries...and add real ops overhead. PgCache: cache the hot 20% that matters. #PostgreSQL #SRE

231

Reposted by @tempest98.bsky.social

PgCache 🐘🦀 @pgcache.bsky.social · 29/04/2026

Release notes for PgCache/0.4.9: 0.4.9 - 2026-04-24 Materialized query results Pre-PG18 search_path change tracking Default admission_threshold changed from 2 to 1 New mv_size_ratio setting (default 10)

101

Reposted by @tempest98.bsky.social

PgCache 🐘🦀 @pgcache.bsky.social · 14/04/2026

"Our query cache failed with parameterized queries because the fingerprints went wrong.” Heard that last week. It's why we're building PgCache: to support the features that matter in production, like prepared statements and partitions. Looking for 1–2 more teams to pilot (DM me)

001

tempest98.bsky.social @tempest98.bsky.social · 06/12/2025

I also worked on two bits of functionality - adding indexes in the cache database by querying and reproducing the indexes defined in origin. Long term, maybe something more sophisticated will be better, but this is good for now. - start tracking estimated cache size for cached queries

000

tempest98.bsky.social @tempest98.bsky.social · 06/12/2025

For the ones in the main code, I found out I was using a lot of length checks and then index slicing to get the data to operate on. The idioms to get the data directly, e.g if let and slice patterns generally made the code much nicer. So now I'm wondering what other clippy warnings I should enable?

100

tempest98.bsky.social @tempest98.bsky.social · 06/12/2025

Been a productive day and a half. Last night, I refactored and split up one of the larger files in the project. This morning, cleaned up all the clippy warnings, including a couple of hundred about index_slicing. Although, a good chunk of those were in tests, where I'm happy to allow index_slicing

100

tempest98.bsky.social @tempest98.bsky.social · 05/12/2025

This particular case was simply returning all columns from the cache as text format, and now they are properly typed. I had to tweak tokio-postgres because for some reason they decided that SimpleColumn should drop all information but the column name.

000

tempest98.bsky.social @tempest98.bsky.social · 05/12/2025

I've also reached the point where I've worked on the project long enough that early decisions made for expediency now need to be revisited. I didn't know enough at the time to know what really needed to be done, so now coming back around to those decisions actually feels kind of good.

100

tempest98.bsky.social @tempest98.bsky.social · 05/12/2025

Well, the metrics have been working as intended, showing where the fallback behavior of PgCache was masking code not working as expected. This led to adding support for binary parameters in the extended query protocol implementation. The initial implementation only worked for the text format.

000

tempest98.bsky.social @tempest98.bsky.social · 13/11/2025

I've already fixed a couple of bugs where the cache was behaving in unexpected ways and the failover to origin was masking the problems. I'm really excited to have much more robust tests going forward :-)

000

tempest98.bsky.social @tempest98.bsky.social · 13/11/2025

Now with the metrics added, counts for cache hits and misses can be checked at the end of the test and the test will fail if the counts are not as expected. I've added checks to a couple of tests so far, and I'd love to say no bugs were found. On the other hand, the metrics are working as expected

100

tempest98.bsky.social @tempest98.bsky.social · 13/11/2025

The way the cache is designed, if there are any errors trying to process a cache hit then the cache fails over by forwarding the query to origin. Or if the tests expects there to be a cache hit and it is a miss, there is no way to tell just by looking at the query results.

100

tempest98.bsky.social @tempest98.bsky.social · 13/11/2025

Made some good progress over the last week. Added some basic metrics on cache behavior - e.g. counts of cache hits and misses. Having metrics for users of the cache will be important, but the main motivation was to use these metrics in integration tests to verify internal cache behavior.

100

tempest98.bsky.social @tempest98.bsky.social · 05/11/2025

And the most recent work has been to added basic support for the extended query protocol. This ended up being tricky as there are multiple phases of the protocol that have to be coordinated between the cache and origin. The current code is basic and can be improved, but it works :-)

000

tempest98.bsky.social @tempest98.bsky.social · 05/11/2025

That set of changes eliminated almost the entire overhead of the proxy. To eliminate the rest I'm going to have to write my own postgres network protocol handling instead of relying on rust-postgres.

100

tempest98.bsky.social @tempest98.bsky.social · 05/11/2025

After that, I did some performance optimization. Main culprit was the naive code I initially wrote would write data back to the client for every row, which got expensive handling hundreds of thousands of rows. Now the code accumulates rows into a 64kB buffer and writes that when it is full.

100

tempest98.bsky.social @tempest98.bsky.social · 05/11/2025

Next big piece of work was adding a resolution step to handle table and column aliases. The output is a ResolvedAST structure where all the naming is explicit. This enabled adding constant and constraint propagation to make cache invalidation more precise.

100

tempest98.bsky.social @tempest98.bsky.social · 05/11/2025

Trying to embrace type-driven design, I dded a CacheableQuery struct for, well, queries that can be cached. It helped centralize the logic for deciding what can be cached and allowed a bunch of code to be simplified. I'm generally happy with the outcome.

110

tempest98.bsky.social @tempest98.bsky.social · 05/11/2025

I also moved the creation of the cache thread into the proxy thread, which enables the proxy thread to recreate the cache if a failure is detected.

100

tempest98.bsky.social @tempest98.bsky.social · 05/11/2025

Did a bunch of refactoring and cleanup after that, including working on tests so that I can run multiple integration tests in parallel. Key to that was randomizing port assignments so that the multiple tests wouldn't conflict with each other. The pgtemp crate has been super useful for this.

100

tempest98.bsky.social @tempest98.bsky.social · 05/11/2025

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.

100

tempest98.bsky.social @tempest98.bsky.social · 05/11/2025

Oh hey, I actually finished the article. PG 18 came out while I was working on the article, so I retested with 18. The #postgres folks added another optimization from the original article, support for removing unneeded self joins. medium.com/@tempest_619...

medium.com

PostgreSQL query optimizations

I came across this article a while ago that looks at a number of query optimizations databases can make to optimize queries before…

000

tempest98.bsky.social @tempest98.bsky.social · 18/10/2025

After accepting that fact, I got back to working, but haven't been posting about it. So now I have a bunch of posting to catch up on.

000

tempest98.bsky.social @tempest98.bsky.social · 18/10/2025

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.

100

tempest98.bsky.social @tempest98.bsky.social · 28/08/2025

Another one is being able to rename columns when using a table alias, e.g "... FROM users AS blah(foo, bar) ..." will rename the first two columns of table users. I've never used and don't recall having come across it before.

000

tempest98.bsky.social @tempest98.bsky.social · 28/08/2025

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.

100

tempest98.bsky.social @tempest98.bsky.social · 26/08/2025

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.

blog.jooq.org

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…

100

tempest98.bsky.social @tempest98.bsky.social · 26/08/2025

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.

000

tempest98.bsky.social @tempest98.bsky.social · 18/08/2025

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.

000

tempest98.bsky.social @tempest98.bsky.social · 14/08/2025

I've done this 3 or 4 times, and it has been nice. Previously, I might have done all the work in a single commit or put off the refactoring until the main work was done, or forever :-) The simplicity of #jujutsu makes it easy to try out different workflows.

000

tempest98.bsky.social @tempest98.bsky.social · 14/08/2025

The workflow I'm using is whenever I decide to refactor something is to create a revision before the main one with 𝚓𝚓 𝚗𝚎𝚠 –𝙱 @ possibly squash some work I've already done into the revision and then complete the changes. After returning to the main revision, there may be some conflicts to resolve.

100

tempest98.bsky.social @tempest98.bsky.social · 14/08/2025

What I'm enjoying is how #jujutsu makes it easy to keep the main revision focused on the join support and to create separate revision for refactors that are useful but not directly related to the main revision.

100

tempest98.bsky.social @tempest98.bsky.social · 14/08/2025

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.

100

tempest98.bsky.social @tempest98.bsky.social · 09/08/2025

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.

000

tempest98.bsky.social @tempest98.bsky.social · 09/08/2025

I also updated the code to skip the cache and forward all messages to the origin when in a transaction. The ended up being straightforward to add, which is always nice when that happens. And I was able to do some minor cleanup and fixing up tests.

000

tempest98.bsky.social @tempest98.bsky.social · 09/08/2025

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.

100

tempest98.bsky.social @tempest98.bsky.social · 07/08/2025

I imagine this would be a rather rare edge case, but it is still rather unsatisfying.

000

tempest98.bsky.social @tempest98.bsky.social · 07/08/2025

The cache will detect the schema change when a relation message is received and invalidate the relevant queries. There's an interesting edge case where if the new table has columns with the same names and types in the same order, then it won't be detected as having changed.

100

tempest98.bsky.social @tempest98.bsky.social · 07/08/2025

This means there is an arbitrary time between the schema change and when that change appears on the stream. Not having timely schema changes in the stream is a bit disappointing, I'm definitely not the first one to feel this way though.

100

tempest98.bsky.social @tempest98.bsky.social · 07/08/2025

The replication stream doesn't provide enough information to compute the change. Also relation messages are not sent when the table has been changed, but only when there is an insert, update, or delete run against the table.

100

tempest98.bsky.social @tempest98.bsky.social · 07/08/2025

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.

100

tempest98.bsky.social @tempest98.bsky.social · 05/08/2025

Added support for reading settings from a TOML file. 😌

010

tempest98.bsky.social @tempest98.bsky.social · 05/08/2025

The new structure is incomplete and only supports what I need so far. It's been much simpler to use, and a nice bonus has been clones of it are much cheaper.

000

tempest98.bsky.social @tempest98.bsky.social · 05/08/2025

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.

100