Does pg_stat_statements slow down my database?
Tips for a Healthier Postgres Database
31–40 of 87 posts
Re: Tips for a Healthier Postgres Database
#32[deleted]
Re: Tips for a Healthier Postgres Database
#33The 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…
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.
Re: Tips for a Healthier Postgres Database
#34I 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.
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.
Re: Tips for a Healthier Postgres Database
#35All 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-...
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.
Re: Tips for a Healthier Postgres Database
#36> 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
Re: Tips for a Healthier Postgres Database
#37> 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?
There is still value in constraining the maximum number of concurrent queries. That’s PgBouncers main task.
Re: Tips for a Healthier Postgres Database
#38> 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 was improved in 14 but may not eliminate the need for pgbouncer depending on the use case.
https://pganalyze.com/blog/postgres-14-performance-monitorin...
Re: Tips for a Healthier Postgres Database
#39All 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-...
Note that LISTEN/NOTIFY only works with pgbouncer session pooling.
Re: Tips for a Healthier Postgres Database
#40Does pg_stat_statements slow down my database?
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.