9 ms·

Tips for a Healthier Postgres Database

323 points by craigkerstiens 5y ago

jaytaylor 5y ago
I kind of wish the title was "Healthier and Happier". Despite this calamitous oversight (wink), articles such as this are a great source to be able to draw on others' experience and reap the benefits of others hindsight without experiencing the outages or time consuming problems yourself.
- craigkerstiens 5y ago
  :) Love this.
- qwesda 5y ago
  I would have opted for "Fitter, happier, more productive"
tyingq 5y ago
"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.
- craigkerstiens 5y ago
  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.
  - uhoh-itsmaciek 5y ago
    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.)
    - piaste 5y ago
      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.
      - uhoh-itsmaciek 5y ago
        Due to some Postgres limitations, a query can run even after the connection drops (the backend process only checks if there's still a client to send results to after query completion). I think I saw some work being done on this in 14 or the upcoming 15 (i.e., to re-check periodically during query processing), but a lot of Postgres versions are still affected. I worked on the Heroku Data team and we regularly saw queries running for days. I think starting with an aggressive timeout to fail fast where you can't afford a slow query anyway, and then make exceptions where it makes sense, is easier to work with than tracking down problem cases and putting in limits after they cause issues.
tharkun__ 5y ago
There seems to be a common lifecycle of indexes within applications. First you start off with almost none, maybe a few on primary keys Not to be rude or anything but I hope he doesn't mean this. Every single postgres primary key (and unique constraint) automatically gets an index. That's how the unique constraint is implemented. Primary keys being naturally unique.
- whakim 5y ago
  I read this as "maybe a few (which are the ones automatically created on primary keys)", as opposed to redundant indices created on primary keys.
  - tharkun__ 5y ago
    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.
    - JohnBooty 5y ago
      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.
      - tharkun__ 5y ago
        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.
zmmmmm 5y ago
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.
- nicoburns 5y ago
  How would one go about determining the right values for these settings?
  - zmmmmm 5y ago
    Looks like crunchdata has blog posts for that too! https://blog.crunchydata.com/blog/optimize-postgresql-server-performance https://blog.crunchydata.com/blog/optimize-postgresql-server...
  - GordonS 5y ago
    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.
czhu12 5y ago
If you are using rails, pghero is a great gem to get better visibility on your Postgres performance https://github.com/ankane/pghero https://github.com/ankane/pghero
nicoburns 5y ago
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 <100ms. 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 another data store, replacing all rows each time. We now run VACUUM FULL on these after each sync (this locks the table but is fast (20-40ms) on such small tables) to avoid bloating them over time.
- jdreaver 5y ago
  +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.
- skunkworker 5y ago
  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.
- chkal 5y ago
  > so we now have a cronjob that runs VACUUM ANALYZE each night. Doesn't autovacuum typically handle this automatically?
  - uhoh-itsmaciek 5y ago
    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%2BTgmoZgapzekbTqdBrcH8O8Yifi10_nB7uWLB8ajAhGL21M6A%40mail.gmail.com https://www.postgresql.org/message-id/flat/CA%2BTgmoZgapzekb... [2]: https://www.postgresql.org/message-id/flat/CAH2-Wz%3DsSvMX5HWo8d%3DDFznid0BiTySCm-zhHUsnC4UP-OaXbA%40mail.gmail.com#48c8b20719ea035f90006f6cfeaa6b93 https://www.postgresql.org/message-id/flat/CAH2-Wz%3DsSvMX5H...
jdreaver 5y ago
> 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 that scale is when we switched _from_ pgbouncer to client side pooling to simplify our architecture, and we noticed no degradation in any major metrics.
- parhamn 5y ago
  Doesnt Aurora effectively have a PG bouncer in front?
  - jdreaver 5y ago
    Nope. There is an AWS blog post [0] from September 2021 about setting up pgbouncer in front of Aurora Postgres, and that blog post references the AWS RDS Proxy service [1], but Aurora doesn't have pgbouncer in front by default. For what it's worth, we also handled hundreds of idle connections just fine on vanilla RDS postgres. [0] https://aws.amazon.com/blogs/database/set-up-highly-available-pgbouncer-and-haproxy-with-amazon-aurora-postgresql-readers/ https://aws.amazon.com/blogs/database/set-up-highly-availabl... [1] https://aws.amazon.com/rds/proxy/ https://aws.amazon.com/rds/proxy/
- mulmboy 5y ago
  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
  - tpetry 5y ago
    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.
RobertRoberts 5y ago
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)
- 33degrees 5y ago
  I’m definitely not an expert and I haven’t found running postgres any more difficult than mysql. There are some differences that take a bit of getting used to, though.
- status_quo69 5y ago
  Like a lot of other complex systems, there's a lot of tweaking you can do in the config, but generally postgres works out of the box pretty well. There's a few gotchas on setup (postgres has a very conservative default configuration for things like memory usage and parallelism), but it's still pretty good. I'd say the biggest footgun is really not knowing about the process per connection limitation, which is why the article mentions pg bouncer. Everything else in the article is pretty geared towards setting your db up for monitoring so you can head off issues.
- dano 5y ago
  I converted a large MySQL DB to PostgreSQL in 2004 and never looked back. Think about how many improvements have occurred in Pg releases since that time and the availability of information. It took my team about six months to do the conversion as there were any number of type conversion problems due to programming assumptions and loose typing in MySQL at that time. There's also an excellent support community on IRC at Freenode (you can google it) and they are very helpful to people of any level of experience. Optimizations as presented by many are not needed right up front and you'll learn them as you go along. Good luck!
- yjftsjthsd-h 5y ago
  > 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. Has MySQL gotten... a lot better, or are you just used to its quirks? I feel like the UTF encoding and ... date format? timezones? are/were all really big footguns.
minaguib 5y ago
+1 Ran into a pathological case where a query that usually took 10-20ms would sometimes, semi-randomly, take > 15 minutes (while the corresponding PG process server-side is running at 100% CPU) Explain showed a dramatic query plan change, which led us towards bad table statistics.
12907835202 5y ago
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.
- brightball 5y ago
  Honestly, after using PG for a couple of years it’s more than a little painful to go back. You don’t realize how limited you are until you really get into all the additional capabilities that PG brings…and then have them unavailable.
- nijave 5y ago
  I think Postgres tends to have a bigger SQL dialect and it's faster on the same hardware (afaik, based on light testing) MySQL clustering and operations tooling seems much better. No vacuums to worry about
- fiddlerwoaroof 5y ago
  This former MySQL dev says: > MySQL is a pretty poor database, and you should strongly consider using Postgres instead. https://web.archive.org/web/20220104181634/https://blog.sesse.net/blog/tech/2021-12-05-16-41_leaving_mysql.html https://web.archive.org/web/20220104181634/https://blog.sess...
- mnahkies 5y ago
  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
- forinti 5y ago
  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.
- 5y ago
brightball 5y ago
All good tips! I never understood why pgbouncer wasn’t just included by default. Additionally, the linked article about checking for unused indexes is really helpful IMO. https://blog.crunchydata.com/blog/cleaning-up-your-postgres-database https://blog.crunchydata.com/blog/cleaning-up-your-postgres-...
- saurik 5y ago
  It would be awkward if pgbouncer were "included" in postgres as the topology of bouncers you have might involve multiple layers running on different computers to minimize things like connection setup latency.
  - brightball 5y ago
    That makes sense. Never had to get into a layered bouncer setup, but I can imagine how that would happen.
- osigurdson 5y ago
  Note that LISTEN/NOTIFY only works with pgbouncer session pooling.
cloudyglider 5y ago
> 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?
- osigurdson 5y ago
  It was improved in 14 but may not eliminate the need for pgbouncer depending on the use case. https://pganalyze.com/blog/postgres-14-performance-monitoring https://pganalyze.com/blog/postgres-14-performance-monitorin...
- andruby 5y ago
  There is still value in constraining the maximum number of concurrent queries. That’s PgBouncers main task.
- jeltz 5y ago
  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.
rc_mob 5y ago
Does pg_stat_statements slow down my database?
- sgt 5y ago
  If it does, it's miniscule. I have been running it for years on fairly busy databases without any issues. The value provided is huge.
- jeltz 5y ago
  Yes, but of I recall correctly not by much. On most workloads it should not be noticeable at all.
deleted 5y ago
[deleted]
eric4smith 5y ago
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.
- forinti 5y ago
  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.
  - jeltz 5y ago
    The buggyness of Oracle surprised me. I expected complicated (but underestimated how complicated they make things) but what I did not expect was the bugs. 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.
  - deleted 5y ago
    [deleted]
- GordonS 5y ago
  I've done the same, using pg_cron to execute `VACUUM ANALYZE` - works just fine.
andruby 5y ago
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.
- gcau 5y ago
  How are you measuring your db load?
  - hnarn 5y ago
    I just assumed they were talking about regular sysload
    - andruby 5y ago
      Correct. Just unix load (ie concurrent busy processes)
- moltar 5y ago
  Have you tried pg_repack? We had some great success with it on large tables with lots of bloat
  - andruby 5y ago
    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.
andruby 5y ago
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.
- tpetry 5y ago
  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.
  - andruby 5y ago
    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.
SergeAx 5y ago
How is it normal to (seemingly) mandatory use third party connection management proxy for production environment? My primary database these days is MongoDB, so I am a bit distanced from RDBMs now.
buro9 5y ago
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/#/ 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 https://github.com/prometheus-community/postgres_exporter and alerts https://awesome-prometheus-alerts.grep.to/rules#postgresql https://awesome-prometheus-alerts.grep.to/rules#postgresql . There are a few Grafana dashboards available for the prometheus exporter, start with those.

I kind of wish the title was "Healthier and Happier". Despite this calamitous oversight (wink), articles such as this are a great source to be able to draw on others' experience and reap the benefits of others hindsight without experiencing the outages or time consuming problems yourself.

craigkerstiens 5y ago
:) Love this.
qwesda 5y ago
I would have opted for "Fitter, happier, more productive"

tyingq 5y ago

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

craigkerstiens 5y ago
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.
- uhoh-itsmaciek 5y ago
  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.)
  - piaste 5y ago
    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.
    - uhoh-itsmaciek 5y ago
      Due to some Postgres limitations, a query can run even after the connection drops (the backend process only checks if there's still a client to send results to after query completion). I think I saw some work being done on this in 14 or the upcoming 15 (i.e., to re-check periodically during query processing), but a lot of Postgres versions are still affected. I worked on the Heroku Data team and we regularly saw queries running for days. I think starting with an aggressive timeout to fail fast where you can't afford a slow query anyway, and then make exceptions where it makes sense, is easier to work with than tracking down problem cases and putting in limits after they cause issues.

tharkun__ 5y ago

There seems to be a common lifecycle of indexes within applications. First you start off with almost none, maybe a few on primary keys Not to be rude or anything but I hope he doesn't mean this. Every single postgres primary key (and unique constraint) automatically gets an index. That's how the unique constraint is implemented. Primary keys being naturally unique.

whakim 5y ago
I read this as "maybe a few (which are the ones automatically created on primary keys)", as opposed to redundant indices created on primary keys.
- tharkun__ 5y ago
  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.
  - JohnBooty 5y ago
    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.
    - tharkun__ 5y ago
      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.

zmmmmm 5y ago

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.

nicoburns 5y ago
How would one go about determining the right values for these settings?
- zmmmmm 5y ago
  Looks like crunchdata has blog posts for that too! https://blog.crunchydata.com/blog/optimize-postgresql-server-performance https://blog.crunchydata.com/blog/optimize-postgresql-server...
- GordonS 5y ago
  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.

czhu12 5y ago

If you are using rails, pghero is a great gem to get better visibility on your Postgres performance https://github.com/ankane/pghero https://github.com/ankane/pghero

nicoburns 5y ago

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 <100ms. 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 another data store, replacing all rows each time. We now run VACUUM FULL on these after each sync (this locks the table but is fast (20-40ms) on such small tables) to avoid bloating them over time.

jdreaver 5y ago
+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.
skunkworker 5y ago
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.
chkal 5y ago
> so we now have a cronjob that runs VACUUM ANALYZE each night. Doesn't autovacuum typically handle this automatically?
- uhoh-itsmaciek 5y ago
  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%2BTgmoZgapzekbTqdBrcH8O8Yifi10_nB7uWLB8ajAhGL21M6A%40mail.gmail.com https://www.postgresql.org/message-id/flat/CA%2BTgmoZgapzekb... [2]: https://www.postgresql.org/message-id/flat/CAH2-Wz%3DsSvMX5HWo8d%3DDFznid0BiTySCm-zhHUsnC4UP-OaXbA%40mail.gmail.com#48c8b20719ea035f90006f6cfeaa6b93 https://www.postgresql.org/message-id/flat/CAH2-Wz%3DsSvMX5H...

jdreaver 5y ago

> 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 that scale is when we switched _from_ pgbouncer to client side pooling to simplify our architecture, and we noticed no degradation in any major metrics.

parhamn 5y ago
Doesnt Aurora effectively have a PG bouncer in front?
- jdreaver 5y ago
  Nope. There is an AWS blog post [0] from September 2021 about setting up pgbouncer in front of Aurora Postgres, and that blog post references the AWS RDS Proxy service [1], but Aurora doesn't have pgbouncer in front by default. For what it's worth, we also handled hundreds of idle connections just fine on vanilla RDS postgres. [0] https://aws.amazon.com/blogs/database/set-up-highly-available-pgbouncer-and-haproxy-with-amazon-aurora-postgresql-readers/ https://aws.amazon.com/blogs/database/set-up-highly-availabl... [1] https://aws.amazon.com/rds/proxy/ https://aws.amazon.com/rds/proxy/
mulmboy 5y ago
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
- tpetry 5y ago
  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.

RobertRoberts 5y ago

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)

33degrees 5y ago
I’m definitely not an expert and I haven’t found running postgres any more difficult than mysql. There are some differences that take a bit of getting used to, though.
status_quo69 5y ago
Like a lot of other complex systems, there's a lot of tweaking you can do in the config, but generally postgres works out of the box pretty well. There's a few gotchas on setup (postgres has a very conservative default configuration for things like memory usage and parallelism), but it's still pretty good. I'd say the biggest footgun is really not knowing about the process per connection limitation, which is why the article mentions pg bouncer. Everything else in the article is pretty geared towards setting your db up for monitoring so you can head off issues.
dano 5y ago
I converted a large MySQL DB to PostgreSQL in 2004 and never looked back. Think about how many improvements have occurred in Pg releases since that time and the availability of information. It took my team about six months to do the conversion as there were any number of type conversion problems due to programming assumptions and loose typing in MySQL at that time. There's also an excellent support community on IRC at Freenode (you can google it) and they are very helpful to people of any level of experience. Optimizations as presented by many are not needed right up front and you'll learn them as you go along. Good luck!
yjftsjthsd-h 5y ago
> 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. Has MySQL gotten... a lot better, or are you just used to its quirks? I feel like the UTF encoding and ... date format? timezones? are/were all really big footguns.

minaguib 5y ago

+1 Ran into a pathological case where a query that usually took 10-20ms would sometimes, semi-randomly, take > 15 minutes (while the corresponding PG process server-side is running at 100% CPU) Explain showed a dramatic query plan change, which led us towards bad table statistics.

12907835202 5y ago

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.

brightball 5y ago
Honestly, after using PG for a couple of years it’s more than a little painful to go back. You don’t realize how limited you are until you really get into all the additional capabilities that PG brings…and then have them unavailable.
nijave 5y ago
I think Postgres tends to have a bigger SQL dialect and it's faster on the same hardware (afaik, based on light testing) MySQL clustering and operations tooling seems much better. No vacuums to worry about
fiddlerwoaroof 5y ago
This former MySQL dev says: > MySQL is a pretty poor database, and you should strongly consider using Postgres instead. https://web.archive.org/web/20220104181634/https://blog.sesse.net/blog/tech/2021-12-05-16-41_leaving_mysql.html https://web.archive.org/web/20220104181634/https://blog.sess...
mnahkies 5y ago
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
forinti 5y ago
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.
5y ago

brightball 5y ago

All good tips! I never understood why pgbouncer wasn’t just included by default. Additionally, the linked article about checking for unused indexes is really helpful IMO. https://blog.crunchydata.com/blog/cleaning-up-your-postgres-database https://blog.crunchydata.com/blog/cleaning-up-your-postgres-...

saurik 5y ago
It would be awkward if pgbouncer were "included" in postgres as the topology of bouncers you have might involve multiple layers running on different computers to minimize things like connection setup latency.
- brightball 5y ago
  That makes sense. Never had to get into a layered bouncer setup, but I can imagine how that would happen.
osigurdson 5y ago
Note that LISTEN/NOTIFY only works with pgbouncer session pooling.

cloudyglider 5y ago

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

osigurdson 5y ago
It was improved in 14 but may not eliminate the need for pgbouncer depending on the use case. https://pganalyze.com/blog/postgres-14-performance-monitoring https://pganalyze.com/blog/postgres-14-performance-monitorin...
andruby 5y ago
There is still value in constraining the maximum number of concurrent queries. That’s PgBouncers main task.
jeltz 5y ago
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.

rc_mob 5y ago

Does pg_stat_statements slow down my database?

sgt 5y ago
If it does, it's miniscule. I have been running it for years on fairly busy databases without any issues. The value provided is huge.
jeltz 5y ago
Yes, but of I recall correctly not by much. On most workloads it should not be noticeable at all.

deleted 5y ago

[deleted]

eric4smith 5y ago

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.

forinti 5y ago
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.
- jeltz 5y ago
  The buggyness of Oracle surprised me. I expected complicated (but underestimated how complicated they make things) but what I did not expect was the bugs. 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.
- deleted 5y ago
  [deleted]
GordonS 5y ago
I've done the same, using pg_cron to execute `VACUUM ANALYZE` - works just fine.

andruby 5y ago

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.

gcau 5y ago
How are you measuring your db load?
- hnarn 5y ago
  I just assumed they were talking about regular sysload
  - andruby 5y ago
    Correct. Just unix load (ie concurrent busy processes)
moltar 5y ago
Have you tried pg_repack? We had some great success with it on large tables with lots of bloat
- andruby 5y ago
  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.

andruby 5y ago

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.

tpetry 5y ago
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.
- andruby 5y ago
  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.

SergeAx 5y ago

How is it normal to (seemingly) mandatory use third party connection management proxy for production environment? My primary database these days is MongoDB, so I am a bit distanced from RDBMs now.

buro9 5y ago

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/#/ 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 https://github.com/prometheus-community/postgres_exporter and alerts https://awesome-prometheus-alerts.grep.to/rules#postgresql https://awesome-prometheus-alerts.grep.to/rules#postgresql . There are a few Grafana dashboards available for the prometheus exporter, start with those.