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

51–60 of 87 posts

Re: Tips for a Healthier Postgres Database

#51

Post by forinti »

eric4smith wrote: post #43

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.

Re: Tips for a Healthier Postgres Database

#52

Post by forinti »

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.

Newer versions of MySQL are better, but it wasn't long ago that it didn't even check column types or constraints.

I would not choose MySQL over Postgresql because of this, never mind all the other features.

Re: Tips for a Healthier Postgres Database

#54

Post by gcau »

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…

How are you measuring your db load?

Re: Tips for a Healthier Postgres Database

#55

Post by stepbeek »

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.

Killer feature for me was transactional schema changes. We use flyway for db migrations which works fine but a failed migration is left in a half completed state in MySQL - Postgres can execute the migration in a transaction that rolls back on failure.

Re: Tips for a Healthier Postgres Database

#56

Post by GordonS »

eric4smith wrote: post #43

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…

I've done the same, using pg_cron to execute `VACUUM ANALYZE` - works just fine.

Re: Tips for a Healthier Postgres Database

#57

Post by ezekiel68 »

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 always get flamed when I mention this (I don't mind it, btw) but...

I find it pretty interesting that for the DB write-intensive portion[0] of the TechEmpower web framework benchmarks, you must go further than entry 150 (sorted by most requests-per-second) to find MySQL used, compared to PostgreSQL or Mongo. For the DB read-intensive portion[1], only 15 of the top 100 use MySQL (the rest in that bracket use PostgreSQL), and the first entry there comes in at number 44. Important: if you examine the entries, you'll find that some of the frameworks have multiple listings for different configurations, including the use of MySQL vs PostgreSQL (in other words: same framework, but only the DB is different, in the same test).

[0] https://www.techempower.com/benchmarks/#section=data-r20&hw=...

[1] https://www.techempower.com/benchmarks/#section=data-r20&hw=...

Re: Tips for a Healthier Postgres Database

#58

Post by GordonS »

nicoburns wrote: post #8
zmmmmm wrote: post #5

I would have said, check your shared buffers memory and concurrency are configured right for the environment you are running on. The defaults are wrong for most production environments and the right values are highly server dependent. If you haven't specifically tuned them for your production server, chances are they are wrong and you are leaving substantial memory and / or concurrency on the table.

How would one go about determining the right values for these settings?

I don't have links right now, but if you search for something like "online postgres config tool", you should find two different pages that do the same thing - you enter some data about your environment, and it spits out recommended settings.

Re: Tips for a Healthier Postgres Database

#59

Post by hnarn »

gcau wrote: post #54
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…

How are you measuring your db load?

I just assumed they were talking about regular sysload

Re: Tips for a Healthier Postgres Database

#60

Post by tpetry »

mulmboy wrote: post #36
jdreaver wrote: post #14

> If you see idle is above 20 it's recommended to explore using PgBouncer. Exploring pgbouncer when you have lots of idle connections is a great tip, but 20 idle connections feels _extremely_ low to me. I've seen postgres databases on AWS Aurora serving over 13,000 transactions per second with hundreds of idle connections (because of client side pooling with a few dozen backend clients) just fine. In fact, around tha…

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.
Post reply on HN