Post

Incidents are no fun - but I think I’ve learned more about Postgres and query optimizations in the past 3 weeks than the rest of my career combined.

Partitions are great until they’re not - beware the LWLocks

ST_Intersects(geometry, geometry) will gladly give you a distance of 0 if you ask for it … after checking every possible point combination?!

If you don’t order ST_DWithin by distance - you’re gonna have a bad time.

Beware analyze! If your data is non-homogeneous then you’re doing a coin flip on how good your pg_stats will be. ALTER TABLE foo ALTER COLUMN field SET STATISTICS {between 100 (default) and 10k}. You can trade analyze runtime (defaults to fast) for substantially larger sample sizes for your stats

Pretty stoked out outcomes from this (and continuing efforts!). CPU on our read replica down from an average of 50% to sub 10%. P95 (read only) query times down from ~150ms to 0.9ms (!!!). Query timeouts clamped down from 10s to 2s.