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.
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)
There is reindexdb and vacuumdb in bin directory of postgresql instance. They cover 99% of maintenance use-cases when run from cron, mainly those mentioned in comments. For general purpose workload i find default settings reasonable.
How would I know when to run these? Or if I need them?
I know I can do research, get training, etc... but I have never done maintenance like this with MySQL/MariaDB, so it's the unknown unknowns that worry me.
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-...
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.
That makes sense. Never had to get into a layered bouncer setup, but I can imagine how that would happen.
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…
I don't know the author, but when I read an article by someone who obviously knows something about Postgres (knowing something about Postgres myself), I feel it's much more likely that they made a slight language error.
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. 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.
The encoding stinks, but it seems "fixed". (utf8mb4) I just migrated a few hundred databases between servers with the old dbs not having proper/modern encoding, and it was a bit messy to clean up.
Date format... not sure, I just use datetime now, timezones I manage in app (never trusted db engine for that...) Maybe I avoided those by accident with unix time until I moved to datetime.
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…
I don't know the author, but when I read an article by someone who obviously knows something about Postgres (knowing something about Postgres myself), I feel it's much more likely that they made a slight language error.
I would tend to agree with that. The problem becomes how to come to the conclusion that he "obviously knows something about Postgres".
My skimming of the first few paragraphs was trying to do just that and my conclusion seems to have been the opposite of everyone else :)
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…
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.