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

71–80 of 87 posts

Re: Tips for a Healthier Postgres Database

#71

Post by buro9 »

New server and want a basic config for a production server? (I'm assuming brand new app here, no prior monitoring and knowledge on what to tune.)

Use this to get the values you need https://pgtune.leopard.in.ua/#/ .

This saved me a lot of headaches, and it just gets the server into a good enough state from which you can observe and optimise later.

I'd also add in monitoring early, add a Prometheus exporter https://github.com/prometheus-community/postgres_exporter and alerts https://awesome-prometheus-alerts.grep.to/rules#postgresql . There are a few Grafana dashboards available for the prometheus exporter, start with those.

Re: Tips for a Healthier Postgres Database

#72

Post by jeltz »

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…

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.

Re: Tips for a Healthier Postgres Database

#73

Post by jeltz »

cloudyglider wrote: post #30

> On versions prior to Postgres 14, connections consumed extra overhead leaving idle connections as wasted space. Does this mean PGbouncer is unnecessary for postgres > v14?

It is less necessary but it could still be worth running it since there is still a bit if overhead plus PgBouncer can be used to when doing failovers.

Re: Tips for a Healthier Postgres Database

#75

Post by zedfoxus »

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.

IMO there has to be a solid business case to switch a database layer. If MySQL serves your business well, I’d not switch. I like MySQL and PostgreSQL. I’ve used both. I felt the decision to go one way or the other depended more on how the company could maintain it after all decision makers were no longer there. Where MySQL was chosen, getting tooling and support from Oracle or Percona was seen as the biggest benefit. Where PostgreSQL was chosen, quality optimizer and open source was seen as the biggest benefit.

I’d highly recommend to try PostgreSQL yourself and learn it’s config, permissions, replication, administration and querying capabilities. You’ll appreciate PostgreSQL’s casting with :: for example. If you had to start from scratch, you’ll be better informed about PostgreSQL and can include it in your selection process.

When we used MySQL, we loved the ease of replication and tooling such as Monyog/Webyog/Workbench. When we used PostgreSQL, we loved the query optimizer and JSON functions.

Re: Tips for a Healthier Postgres Database

#76

Post by tharkun__ »

JohnBooty wrote: post #29
tharkun__ wrote: post #7

Earlier quoted context omitted.

I would agree IFF there wasn't the 'maybe'. If that word wasn't there I would read it as having not many tables in the beginning and thus not many primary keys and thus not many primary key indices. 'maybe' changes that dramatically unfortunately.

I don't disagree, but Mr. Kersteins has quite the track record of Postgres excellence, so he gets every benefit of the doubt from me. I feel quite certain he understands how primary keys work! I'm sure this was just a miswording.

Fair enough. I don't know him though (never heard the name) and after skimming through the first paragraphs of each item and reading this I closed the window instantly as for me it invalidated the information where I had no in depth knowledge myself. As in 'how can I trust any of the rest if something so fundamental is off'.

Very unfortunate if what you say is true. I guess I'll give him the benefit of the doubt then and go read the rest.

Re: Tips for a Healthier Postgres Database

#77

Post by andruby »

tpetry wrote: post #61
andruby wrote: post #45

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.

Is there any documentation which statements are doing this. Had watched this in the past too, but can‘t remember which one was responsible for it and whether it changed in the last versions.

I haven’t found any. In our case, we did an alter table .. alter column, which I assumed would he fine. In retrospect, it does make sense that PG recreates that column from scratch and thus it doesn’t keep the statistics.

Since then we typically include an analyze statement whenever we do a large change, or rewrite a lot of rows.

Re: Tips for a Healthier Postgres Database

#78

Post by nicoburns »

jeltz wrote: post #72
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…

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.

Re: Tips for a Healthier Postgres Database

#79

Post by andruby »

moltar wrote: post #66
andruby wrote: post #44

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…

Have you tried pg_repack? We had some great success with it on large tables with lots of bloat

It’ll probably shave 50% off that table’s disk space.

However, we’re actually in the process of sharding the db, and by copying customer by customer we’ll lose the bloat that way. The subsequent shards will be a lot more manageable so we can run pg_repack there with more confidence.

Re: Tips for a Healthier Postgres Database

#80

Post by piaste »

uhoh-itsmaciek wrote: post #47
craigkerstiens wrote: post #9

Earlier quoted context omitted.

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

How often would a runaway query be able to run for days without the connection being dropped? I would expect either the incoming client request or the background job that started the query to have their own timeouts, and to dispose of their connections/transactions when killed. If a request or job does last for hours, it's probably easier to notice compared to a query, due to generally better observability tools.

The only time I've seen a running query lasting for days in prod was when a human database toucher had forgot their DBeaver tabs open after running a complex query with too few filters.

Post reply on HN