Mark Stosberg <mark@xxxxxxxxxxxxxxx> writes: > I recently set up partitioning on a table that sees heavy insert > traffic. There are never updates or deletes, we just drop the partitions > later. > It's my understanding that bloat can only appear through updates or > deletes, but these partitions are reported to have significant bloat in > them. Where else can this come from and how I can I reduce it? > I'm using a "bloat" view with 9.0.3 which might the same as this one: > https://wiki.postgresql.org/wiki/Show_database_bloat > This shows that each index on the table has over a gig of bloat: > select distinct(iname), wastedbytes, wastedsize from bloat where > wastedbytes > 0 order by wastedbytes DESC limit 20; > And this query also shows the same amount of bloat in the table itself: > select distinct(tablename), wastedbytes, wastedsize from bloat where > wastedbytes > 0 order by wastedbytes DESC limit 10; It's hard to evaluate that without knowing what the actual table/index sizes are, or IOW what is the reported bloat on a percentage basis? The view you mention isn't tremendously accurate --- AFAICS it isn't accounting for alignment padding between fields, page headers, and some other things. And it will consider the unused space on a page to be "bloat" even if it's too small to fit another tuple. So expecting the number to be zero is hopelessly optimistic. Also, indexes generally don't even try to pack pages completely full, so a larger percentage of unused space is to be expected for them. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
