[nijave]

LLMs are an interesting call out. I've been trying with the idea of a query optimizer agent that analyzes PG slow query/auto explain logs.

I've only tried Claude Opus but it does a pretty good job interpreting the plan. It's also really powerful being able to bring in telemetry context and code to help make a more balanced tradeoff with usage metrics (much easier to callout anomolies, edge cases, and non performance sensitive contexts)

That said fixing the queries which usually originate from Django ORM is a bit hit or miss. Been multiple times the LLM wants to rewrite to something similar but not identical or wants to make large, structural changes instead of focusing on low hanging surgical improvements

Once it was even able to pull PG mailing lists to figure out I was hitting a planner predicate comparison limitation where switching from "bool = false" to "not bool" or maybe the other way around led to a massive 100x+ improvement (the planner was skipping using an index because the predicate in the query didn't "match" the predicate in the partial index despite the conditions being logically equivalent)

Would have taken days or weeks for me to figure that out on my own

[Atotalnoob]

You can tell the LLM to optimize the Django ORM. With ORMs, large structural changes are sometimes needed for small query text changes due to how ORMs generate the SQL.

I’m a SWE and at my previous job it fell to me to optimize some queries that used EF Core ORM. I gave opus a local db with a small export of anonymized prod data, had it generate a billion rows with similar cardinality.

Then I told opus how to get the raw SQL and told it to write unit tests with various optimizations.

It got the query down from 5+ minutes to a few seconds. I verified the final SQL and ORM structure.

All it needed was to modify some indices and fix up the ORM to properly generate the lateral join.

However, to get the join working properly required a full restructure of the ORM query.

[nijave]

>You can tell the LLM to optimize the Django ORM

I have and have come to the conclusion it's too risky. On too many attempts it hallucinates correctness and subtly breaks edge cases

>large structural changes are sometimes needed for small query text changes due to how ORMs generate the SQL

Sometimes that's true but a lot of times you can get 90% the benefit with significantly lower risk with a much smaller targeted changes.

It can check correctness against real data but I've still hit problems where you can just look at the rewrite and identify behavior differences that don't necessarily show up in 1 off execution comparison unless you brute force your way through tons of test data.

A lot of the cases I'm running into are somewhat older code (5+ years) with poor test coverage that few people understand or remember. Some of these probably have legitimate bugs but it's risky assuming and safer to ensure an optimization is identical.

[hylaride]

Having a good DB monitoring setup can also help a lot. We (over)pay for datadog's Database monitoring APM, but it is decent in letting you see how queries are run and change over time. It also checks query plans and lets you know when too many sequential scans are being performed, as well as indexes that are no longer used (in large DBs removing them can alleviate a lot of write IOPs).

It's not perfect and still requires thought and understanding (it'll recommend removing unused indexes on the primary keys, for example), but SOMETHING needs to continuously monitor anything but the smallest, simplest relational DBs.

[nijave]

Yeah slowly building up to that, we noped out of DD APM because leadership does not like seeing the $ amount of the bill (despite it being a small fraction of AWS and consisting of 10s of services in 1 platform)

[hylaride]

FWIW, there are alternatives: https://github.com/dhamaniasad/awesome-postgres#optimization

[nijave]

I'll have to take another look--last time all the free stuff was pretty underwhelming and all the commercial stuff... wasn't free...

So far I've had the best luck with pg_stat_statements + auto explain + Claude Code to go digging through those.