Jennifer Spencer <jenniferm411@xxxxxxxxxxx> wrote: > We do mostly inserts, no updates and very few deletes. We drop > entire tables but don't delete often. We have very long rows, > though. Do you think the above is a situation likely to create > extreme bloat? No. Only deletes and updates can cause dead rows, and if you use regular vacuums with reasonable frequency (including just having a reasonably configured autovacuum) you won't get extreme bloat from even those -- provided you avoid a few pitfalls. (1) Avoid long-running transactions to the degree that you can. Vacuum must leave a dead row if there's any transaction that might still be able to see it. (2) If you're running a version prior to 8.4, make sure your Free Space Manager settings are adequate. (A VACUUM VERBOSE at the database level will report where you're at in that regard in the last few lines.) (3) Avoid updating too many rows in a single database transaction. (A series of smaller updates can allow autovacuum to reclaim dead row space from earlier updates to use for the new rows generated by later updates.) > My Sybase experience with extreme bloat was that it was caused by a > three-field clustered index in a very long short-row table over time > (~a year). This job doesn't use clustered indexes. The issues are entirely different in PostgreSQL. Clustered indexes don't mean remotely the same thing. (In PostgreSQL a CLUSTER operation rewrites the table, putting the rows in the order of the specified index, but there is no effort to maintain that sequence after that point. An index flagged for "cluster" in PostgreSQL is merely the default index to use if you ask to cluster a table without specifying an index.) Sybase doesn't (or didn't last I used it) use MVCC, so an UPDATE modified the row in place, protecting the operation with blocking locks; it wasn't the DELETE/INSERT pair that it is in PostgreSQL. > I thought we had to do vacuum full to avoid transaction ID > wraparound/reset issues? We do have a lot of transactions, a whole > lot. Protecting against that requires vacuum to *freeze* the tuples, which does not require VACUUM FULL. Don't confuse VACUUM FULL with VACUUM FREEZE or a with a VACUUM of the entire database. Autovacuum can normally cover your needs for tuple freezing without explicitly running anything to do so. > Are you saying that most admins avoid VACUUM FULL as much as > possible? Yes. We tried it a few times early on and discovered that aggressive maintenance is unnecessary if you do a good job with your regular maintenance, and that if you have room for a second copy of a table, CLUSTER is almost always a better option than VACUUM FULL. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
