Jennifer Spencer <jenniferm411@hotmail.com> 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