Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Tips for a Healthier Postgres Database

blog.crunchydata.com

Post reply on HN
Print view View original

41–50 of 87 posts

Re: Tips for a Healthier Postgres Database

#41

Post by chkal »

nicoburns wrote: post #13

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…

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

Doesn't autovacuum typically handle this automatically?

Re: Tips for a Healthier Postgres Database

#42

Post by saberience »

nicoburns wrote: post #13

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 performance doesn't tank.

Re: Tips for a Healthier Postgres Database

#43

Post by eric4smith »

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 needed, except testing restores on daily backups and testing replicas.

Re: Tips for a Healthier Postgres Database

#44

Post by andruby »

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 now take less than a day. The indexes on that table had accumulated a lot of bloat, and I think newer versions also improved the index disk layout.

We now have a monthly cron job to reindex all indexes.

Re: Tips for a Healthier Postgres Database

#45

Post by andruby »

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.

Re: Tips for a Healthier Postgres Database

#46

Post by uhoh-itsmaciek »

chkal wrote: post #41
nicoburns wrote: post #13

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…

> 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...

Re: Tips for a Healthier Postgres Database

#47

Post by uhoh-itsmaciek »

craigkerstiens wrote: post #9
tyingq wrote: post #2

"Set a statement timeout" I think I would recommend setting log_min_duration_statement first and watching the logs for some time before doing that. So that you know what's going to get whacked, have some opportunity to tune it, etc. Edit: It is mentioned, so perhaps just talking about that prior to talking about setting the timeout.

Good catch, will definitely update or perhaps even re-order. I'm not sure either in isolation gives you everything you need, but point noted that some sense of what is getting cancelled is equally important.

I think there's a case to be made that a timeout is more important. With an OLTP workload, it's not hard to imagine a runaway query running for days, consuming I/O bandwidth and silently slowing everything down. A timeout may break some things, but it will do so a lot more clearly. (Of course, both settings are a good idea.)

Re: Tips for a Healthier Postgres Database

#48

Post by rrickardt »

RobertRoberts wrote: post #17

I would love to drop MySQL/MariaDB and go with PostgreSQL, but information like this makes me nervous that I would setup a footgun and be constantly debugging things because I am not a DB admin expert. Anyone care to comment on how PostgreSQL works out of the box without being an expert? (I've run MySQL/MariaDB for almost 20 years and there are very few issues I've been surprised by)

There is reindexdb and vacuumdb in bin directory of postgresql instance. They cover 99% of maintenance use-cases when run from cron, mainly those mentioned in comments. For general purpose workload i find default settings reasonable.

Re: Tips for a Healthier Postgres Database

#49

Post by fabian2k »

saberience wrote: post #42
nicoburns wrote: post #13

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…

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.

Re: Tips for a Healthier Postgres Database

#50

Post by mnahkies »

12907835202 wrote: post #23

As someone who's used MySQL for 17 years and dabbled a little in Mongo is there any reason to try and switch to Postgres? Obviously I love learning new things, but I've just never felt inclined to try it out, whereas I'm always jumping between other languages and frameworks within them. Not sure why that is.

I haven't had a chance to use mysql 8 yet (which I believe narrows the gap between postgres and mysql), but whenever I'm interacting with databases on mysql 5.7 I miss features like common table expressions from postgres
Post reply on HN