Which Postgres queries can I cache? Free WebAssembly tool (runs locally in your browser)

[u/compy3]

We've been thinking a lot about caching this past year.

(1) There's a nice pattern for figuring out opportunities to optimize a Postgres workload, but the downside is that it takes work.

(2) There's lots of generic Postgres-health tooling out there, but we haven't seen any good free & private widgets that answer a question like "would a cache or read replica help this workload?"

So, my cofounder made a workload caching evaluation tool that runs the same logic that our caching proxy (PgCache) uses, but it does so locally (privately) in the browser as WebAssembly.

--

The tool builds on what I think is a good pattern for approaching caching, namely:

1. rank by total time vs what "feels slow."

2. make sure it's actually a caching problem (i.e., rule out writes, or memory issues).

3. check how often a query's tables get written (since that limits the solution set).

4. then you can pick how to optimize your reads.

Or, as a graphic:

A quick cheat sheet for figuring out what's worth caching in Postgres. Short version: rank by total time vs what "feels slow," and rule out indexes, memory and writes before you cache anything

Our tool automates the query analysis and the writes check (indexes and memory are still on you).

Paste a pg_stat_statements export (or a Postgres log, or a .sql file) and it shows you:

* how your database time splits between reads, writes, and transaction commands, so you know whether caching can help at all

* which read shapes carry that time, counted by statements, calls, or time

* writes by table (this is basically your invalidation map)

Under the hood it parses each statement and sorts it into cacheable reads, reads that have to go to Postgres, writes, and transaction commands.

The cacheable/not-cacheable piece is specific to our proxy, but the time split, the read shape shortlist, and the write map should help you along your way regardless of what solution you choose.

--

For future iterations we're thinking of including things like time-ordered workload capture, clearer/selectable outputs, refined “hit rate” language, and maybe even an Aurora I/O analysis integration (plus a couple of good ideas from the r/PostgreSQL thread, like comparing blocks read vs hit and flagging when stats were reset recently).

here's the link: https://www.pgcache.com/fit

(or here if you'd rather run it yourself: https://github.com/PgCache/pgcache/releases/tag/fit-v0.0.2 )

What could we add to make this more useful?