61–70 of 87 posts
Some schema changing statements drop the statistics. We learned this the hard way. We altered some integer columns to bigint. That cleared the statistics for those column and caused terrible query plans. An ANALYZE fixes this, but it took us a few days to notice.
Earlier quoted context omitted.
Isn't AWS Aurora PostgreSQL a completely different (but compatible) product to PostgreSQL? If so it would make sense that its ability to handle many connections is just an implementation detail
Aurora forked PostgreSQL very early on (before 9.6) and has been improved independent and a lot has been changed. They are effectively different databases now.
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...
AWS has "only" replaced the storage layer.
Earlier quoted context omitted.
Isn't AWS Aurora PostgreSQL a completely different (but compatible) product to PostgreSQL? If so it would make sense that its ability to handle many connections is just an implementation detail
Aurora forked PostgreSQL very early on (before 9.6) and has been improved independent and a lot has been changed. They are effectively different databases now.
The biggest Postgres related lesson I learnt recently is the importance of running ANALYZE and having up to date table statistics. In our case it was the difference between queries taking several seconds to run and taking We get much higher traffic during the daytime, so we now have a cronjob that runs VACUUM ANALYZE each night. We also have some small metadata tables that are very update heavy as we sync these from…
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 performa…
Why isn't that so?
REINDEX concurrently made a huge difference for us. We have a main database that started at version 9.6 and was upgraded along the way. The largest table is huge (billions of rows, TB’s of diskspace) and gets a lot of deletes and updates. Vacuums could no longer finish on that table (we killed it after ~90 days). Reindex (+ vacuum with skip-indexes) dropped our db load from 60 to 20 and fixed the autovacuums, which n…
Earlier quoted context omitted.
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 performa…
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?
Running these manual commands is just shifting the config tuning for large databases to periodic „manual“ operations.
Earlier quoted context omitted.
Aurora forked PostgreSQL very early on (before 9.6) and has been improved independent and a lot has been changed. They are effectively different databases now.
That's not true at all. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide... AWS has "only" replaced the storage layer.
Weekly VACUUM ANALYZE on some of the busiest write tables made me go from sleepless nights to bliss. And every 6 months we noticed certain queries getting slower and slower. That's because the amount of data growth means we have to have a different approach to indexing or the way how we access it. So our queries change a few times a year. Otherwise, on a 650 Gigabyte database, there is remarkably little maintenance n…
My bad luck is knowing Postgresql. It brings me great suffering when working with Oracle which is unnecessarily complicated and excessively buggy. You don't need support to work with Postgresql. Everything is simple and works as documented.
After having worked with PostgreSQL, MySQL and Oracle I cannot understand why anyone would pick Oracle. Its advantages can't be worth the hassle even if we ignore the license fee.
Earlier quoted context omitted.
Aurora forked PostgreSQL very early on (before 9.6) and has been improved independent and a lot has been changed. They are effectively different databases now.
Do you know of any credible source for Amazon forking postgresql? I did some googling and can certainly find lots of people saying it, but nothing authoritative from Amazon themselves.
For MySQL any of the big users like facebook & co. Are running (heavy) forked mysql versions with changes they need.