pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
surprised to find bloat in insert-only table 4+ messages / 2 participants [nested] [flat]
* surprised to find bloat in insert-only table @ 2011-06-03 15:10 Mark Stosberg <mark@summersault.com> 0 siblings, 1 reply; 4+ messages in thread From: Mark Stosberg @ 2011-06-03 15:10 UTC (permalink / raw) To: pgsql-admin 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; Mark ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: surprised to find bloat in insert-only table @ 2011-06-03 16:02 Tom Lane <tgl@sss.pgh.pa.us> parent: Mark Stosberg <mark@summersault.com> 0 siblings, 1 reply; 4+ messages in thread From: Tom Lane @ 2011-06-03 16:02 UTC (permalink / raw) To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-admin Mark Stosberg <mark@summersault.com> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: surprised to find bloat in insert-only table @ 2011-06-03 17:03 Mark Stosberg <mark@summersault.com> parent: Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 4+ messages in thread From: Mark Stosberg @ 2011-06-03 17:03 UTC (permalink / raw) To: pgsql-admin Thanks for reply, Tom. > 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 table size is reported as: 4036 MB according to: select pg_size_pretty(pg_table_size('table_name')); Compared to 1669 MB reported as table bloat in the 'bloat' view. So, the bloat is about 40% of the total size. For an index, it's 410 MB of bloat, vs 1669 MB for an index size. One thing that looks suspicious is that the exact same number of bytes is being report for the table as well as each index. > 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. Thanks for the clarifications! In summary, it sounds like there is some bloat that I need to live with, but at least with partitioning the amount of bloat will stay relatively constant. Mark ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: surprised to find bloat in insert-only table @ 2011-06-03 17:39 Tom Lane <tgl@sss.pgh.pa.us> parent: Mark Stosberg <mark@summersault.com> 0 siblings, 0 replies; 4+ messages in thread From: Tom Lane @ 2011-06-03 17:39 UTC (permalink / raw) To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-admin Mark Stosberg <mark@summersault.com> writes: > Compared to 1669 MB reported as table bloat in the 'bloat' view. So, > the bloat is about 40% of the total size. > For an index, it's 410 MB of bloat, vs 1669 MB for an index size. Hm ... 40% unused space wouldn't be surprising at all for an index. The traditional rule of thumb for a b-tree index is that the steady state fill factor is about 2/3rds. You can do better for an index that is loaded in increasing order (eg, an index on a serial or timestamp column typically has a higher fill factor) but indexes on columns that are more random are not going to see that. > One thing that looks suspicious is that the exact same number of bytes > is being report for the table as well as each index. Um. In that case I'd say your view is flat out wrong... regards, tom lane ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-06-03 17:39 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-06-03 15:10 surprised to find bloat in insert-only table Mark Stosberg <mark@summersault.com> 2011-06-03 16:02 ` Tom Lane <tgl@sss.pgh.pa.us> 2011-06-03 17:03 ` Mark Stosberg <mark@summersault.com> 2011-06-03 17:39 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox