It seems to me that there's something major missing from Postgres if you need to manually run something daily or else the queries can go from 100ms to "several seconds."

If this is such a standard requirement and the statistics are so vital to performance, why isn't something built-in to the engine to keep these up-to-date without a user intervention?

I don't have to run "ANALYZE" on DynamoDB daily to ensure performance doesn't tank.

This is usually done automatically in Postgres in the background by the Autovacuum process. For high volumes of traffic or certain usage patterns you might have to tune the Autovacuum settings, or it might not be able to keep up with the more conservative default settings.

One issue is that there is some counter-intuitive behaviour here, if you see the Autovacuum taking significant resources, the worst thing you can do is to let it run less often. You actually need to make it more aggressive in that kind of situation, and/or fix your usage pattern or add more resources.

If a manual ANALYZE is necessary, this can often indicate a misconfiguration of Postgres, e.g. someone reducing the Autovacuum frequency or disabling it entirely. Postgres also got a lot better at this, so it also matters how old your Postgres version is.

Agreed, I never faced these issues as our database is relatively small but it's weird to see so many manual commands used for maintenance when these could be built-in PG ongoing process.

Why isn't that so?

It is built-in. The autovacuum process is doing this. When you have a large database you may need to tune some config parameters, as the default conservative setting for low/mid databases are naturally different.

Running these manual commands is just shifting the config tuning for large databases to periodic „manual“ operations.

+1 to both of these approaches. Maybe once per year we would have a query plan regress because of out of date column statistics. This happened on some pathological cases like tables that had old data evicted frequently. Once we committed to running VACUUM ANALYZE nightly on a cron, we never saw query plans regress out of of the blue again.

ANALYZE is the first thing I run when looking at a problematic slow query, if you aren't aware of it, you could spend hours trying to figure out why the planner was doing A when it should be doing B.

Have you looked into tuning autovacuum? Autovacuum should handle it and even it does not you should look at why it failed to run often enough.

We could. But seeing as we have pretty much no query volume overnight it seemed simpler just to use the hammer and vacuum everything overnight.

> so we now have a cronjob that runs VACUUM ANALYZE each night.

Doesn't autovacuum typically handle this automatically?

It should, but it may need to be tuned to run aggressively enough, which can be tricky. Sometimes it's easier to just schedule a manual job that runs during your off hours. There are some interesting discussions on the Postgres development list on improving VACUUM performance [1] and other improvements to the overall MVCC mechanism to avoid the need for VACUUM in the first place if possible [2]. Postgres, like any complex system, does have pathological cases. It helps to keep an eye on performance and learn the system as your database grows. But it's good to see the community is thinking about how to improve the situation.

[1]: https://www.postgresql.org/message-id/flat/CA%2BTgmoZgapzekb... [2]: https://www.postgresql.org/message-id/flat/CAH2-Wz%3DsSvMX5H...