11–20 of 87 posts
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.
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.
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.
> 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…
> 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…
Doesnt Aurora effectively have a PG bouncer in front?
[0] https://aws.amazon.com/blogs/database/set-up-highly-availabl...
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)
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…
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)
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)
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.