Thread: Can't we give better table bloat stats easily?
Everywhere I've worked I've seen people struggle with table bloat. It's hard to even measure how much of it you have or where, let alone actually fix it.
If you search online you'll find dozens of different queries estimating how much empty space are in your tables and indexes based on pg_stats statistics and suppositions about header lengths and padding and plugging them into formulas of varying credibility.
But isn't this all just silliiness these days? We could actually sum up the space recorded in the fsm and get a much more trustworthy number in milliseconds.
I rigged up a quick proof of concept and the code seems super simple and quick. There's one or two tables where the number is a bit suspect and there's no fsm if vacuum hasn't run but that seems pretty small potatoes for such a huge help in reducing user pain.
Hi, On 2019-08-16 20:39:21 -0400, Greg Stark wrote: > But isn't this all just silliiness these days? We could actually sum up the > space recorded in the fsm and get a much more trustworthy number in > milliseconds. You mean like pgstattuple_approx()? https://www.postgresql.org/docs/current/pgstattuple.html Or something different? > I rigged up a quick proof of concept and the code seems super simple and > quick. There's one or two tables where the number is a bit suspect and > there's no fsm if vacuum hasn't run but that seems pretty small potatoes > for such a huge help in reducing user pain. Hard to comment on what you propose, without more details. But note that you can't just look at the FSM, because in a lot of workloads it is often hugely out of date. And fairly obviously it doesn't provide you with information about how much space is currently occupied by dead tuples. What pgstattuple_approx does is to use the FSM for blocks that are all-visible, and look at the page otherwise. Greetings, Andres Freund
On Sat, Aug 17, 2019 at 9:59 AM Andres Freund <andres@anarazel.de> wrote: > > Hi, > > On 2019-08-16 20:39:21 -0400, Greg Stark wrote: > > But isn't this all just silliiness these days? We could actually sum up the > > space recorded in the fsm and get a much more trustworthy number in > > milliseconds. > > You mean like pgstattuple_approx()? > > https://www.postgresql.org/docs/current/pgstattuple.html > > Or something different? > > > I rigged up a quick proof of concept and the code seems super simple and > > quick. There's one or two tables where the number is a bit suspect and > > there's no fsm if vacuum hasn't run but that seems pretty small potatoes > > for such a huge help in reducing user pain. > > Hard to comment on what you propose, without more details. But note that > you can't just look at the FSM, because in a lot of workloads it is > often hugely out of date. And fairly obviously it doesn't provide you > with information about how much space is currently occupied by dead > tuples. What pgstattuple_approx does is to use the FSM for blocks that > are all-visible, and look at the page otherwise. > It's just an idea but we could have pgstattuple_approx use sample scan to estimate the table bloat more faster. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Fri, Aug 16, 2019 at 8:39 PM Greg Stark <stark@mit.edu> wrote:
Everywhere I've worked I've seen people struggle with table bloat. It's hard to even measure how much of it you have or where, let alone actually fix it.If you search online you'll find dozens of different queries estimating how much empty space are in your tables and indexes based on pg_stats statistics and suppositions about header lengths and padding and plugging them into formulas of varying credibility.
There is not much we can do to suppress bad advice that people post on their own blogs. If wiki.postgresql.org is hosting bad advice, by all means we should fix that.
But isn't this all just silliiness these days? We could actually sum up the space recorded in the fsm and get a much more trustworthy number in milliseconds.
If you have bloat problems, then you probably have vacuuming problems. If you have vacuuming problems, how much can you trust fsm anyway?
Cheers,
Jeff