2 ms·
Excellent article - it reminds me of this comment: https://news.ycombinator.com/item?id=11322244 https://news.ycombinator.com/item?id=11322244 In Oracle you c
by chris_wot 11y ago
Excellent article - it reminds me of this comment:
https://news.ycombinator.com/item?id=11322244 https://news.ycombinator.com/item?id=11322244
In Oracle you can set the amount of MVCC storage, can any experienced PostgreSQL DBAs confirm if bloating is a big issue they must deal with?
- pgaddict 11y agoWe do provide PostgreSQL support/DBA for a lot of customers, and while I don't have any detailed stats I probably can make some general conclusions ... I'd say that for most deployments bloat it's not a big issue. But of course, once in a while a customer gets bitten by it and we have to intervene. There's a variety of reasons for that: 1. disabled autovacuum Hey, we've disabled autovacuum because we can't risk it affecting production! Hey, we do know our workload patterns much better! Hey! 2. default autovacuum configuration (or bad tuning) The defaults are very conservative, and generally require tuning (cranking up) on large databases. But people tend to do exactly the opposite, driven by the misconception that it will make autovacuum less intrusive. 3. doing things that are known to break things Like, keeping transactions open for a long time. 4. pathological workload patterns There are a few workload patterns that result in bloat (particularly in indexes), and autovacuum can't really fix that. For example large bulk deletes may cause this. This is probably the one thing that can't be fixed by configuration changes, etc.
- anarazel 11y agoRe 4) Bulk deletes causing index fragmentation isn't really related to MVCC tho; and it's certainly something non mvcc engines also have troubles with.
- ivoras 11y agoIt certainly exists, and in unexpected places, like system catalogues. It's especially visible if a (wrong) mgmt decision has been made to switch off autovacuum - then everything tends to blow up, and fast. In short: explicit bloat management and autovacuum tuning are a necessity for all high transaction rate workloads in PostgreSQL, and the stats need to be monitored closely to detect workload changes.