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

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