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.

If you lateral join on a subquery depending on a WHERE filter - then you better hoist that subquery out or Postgres will just read out every possible value

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.