Background: Table Bloat in Postgres

Postgres stores tables in files in the operating system’s file system.

Whenever records are deleted or updated and their old version is not visible to concurrent transactions any more, those records become dead tuples. An asynchronous background process (autovacuum) will then eliminate those by marking the space they occupied as free space: space that Postgres can recycle for future writes.

However, free space isn’t automatically returned to the file system. If for some reason a write operation caused lots of space to become free in a table, from the file system’s point of view Postgres is hogging space that doesn’t hold data: the table is bloated. This isn’t necessarily a problem. But, sometimes one wishes to compact the table, be it to release the space, be it to shrink the table for faster scans. In that case, there are commands the DBA might run in a scheduled maintenance operation to rebuild the table (vacuum full for Postgres < 19 and repack for Postgres >= 19).

This brings us to a common question. Is a given table bloated? How much space could I reclaim by repacking it?

To answer that question, Postgres has a built-in-extension (→ pgstattuple). The problem is, this is very slow for large tables. It basically needs to scan the whole table (or large parts of it).

You will find a large number of queries on the world wide web that work around this problem. These queries inspect the catalog to compute how many bytes are needed to store a record and fetch statistics of column size used by the query planner. Then they compare the results to the actual table size and hence estimate table bloat.

This works reasonably well for most cases, but, I always found it somewhat unsatisfactory that we don’t measure this more directly. After all Postgres must know where and how much free space there is, otherwise it wouldn’t be able to recycle it automatically, right?

Indeed Postgres keeps a → Free Space Map for each table. As it turns out, there is a built-in extension (→ pg_freespacemap) that allows querying the free space directly. The way this works is that you query pg_freespace() passing a table name and a page number (Postgres stores data organized into 8k pages). The function will return the number of bytes that are considered free space inside the given page.

I’ve written a function that uses this approach. As there might be millions of pages in large tables, it just samples a subset of the pages, making it very fast (few milliseconds) even for huge tables.

Chris’ fancy table bloat estimate function

create or replace function estimate_bloat(table_name text) returns numeric as
$$
  -- Chris' fancy table bloat estimate function v. 0.1

  declare
    s                   text;
    t                   text;

    tstats              record;

    page_cnt            bigint;
    sample_size         bigint;
    samples             bigint[];
    r                   bigint;

    fs                  numeric;
    total_fs            numeric;
    avg_fs              numeric;
    ret_dead_tuple_pct  numeric;
    ret_free_space_pct  numeric;

  begin
    s = split_part(table_name, '.', 1);
    t = split_part(table_name, '.', 2);

    select relid, last_vacuum, last_autovacuum into tstats
      from pg_stat_user_tables where schemaname = s and relname = t;

    select relpages into page_cnt from pg_class where oid = tstats.relid;

    if page_cnt = 0 or (tstats.last_vacuum is null and tstats.last_autovacuum is null) then
      return null;
    end if;

    samples = '{}';
    if page_cnt <= 20 then
      sample_size = page_cnt;
      for j in 0 .. sample_size - 1 loop
        samples = samples || j;
      end loop;
    else
      sample_size = ceil(384.16 / (1 + 383.16 / page_cnt));       -- Cochran's sample size
      for j in page_cnt - 1 - sample_size .. page_cnt - 2 loop    -- Floyd's sample selection
        r = floor(random() * (j+1))::bigint;
        if r = any(samples) then
          samples = samples || j;
        else
          samples = samples || r;
        end if;
      end loop;
    end if;

    total_fs = 0;
    for j in 1..sample_size loop
      execute 'select pg_freespace(''' || table_name || ''', ' || samples[j] || ')' into fs;
      total_fs = total_fs + fs;
    end loop;
    avg_fs = total_fs / sample_size;
    return round((100.0 * page_cnt * avg_fs / pg_relation_size(table_name, 'main')), 0);

  end;
$$ language 'plpgsql';

One important caveat: the function needs the built-in extension pg_freespacemap and can only be executed by roles with the pg_stat_scan_tables grant:

create extension pg_freespacemap;
grant pg_stat_scan_tables to <your_role>;

Admittedly, this makes it less practical than the pure catalog-based queries for situations where one does not have permissions to create the extension or assign that powerful grant to a role.

The function returns an estimate of the percentage of free space in a given table.

Some tests

First create a helper function that returns a random text string of the given length:

create or replace function random_text(len int) returns text as
$$
  declare
    ret text;
  begin
    ret = '';
    for i in 1 .. len loop
      ret = ret || chr(floor(random() * 96.0)::int + 32);
    end loop;
    return ret;
  end;
$$ language 'plpgsql';

Then create a random table with 10k records and delete 25% of them:

begin;

create table public.testtable1 with (autovacuum_enabled = false) as
  select x::numeric,
         random() as r,
         random() > 0.5 as b,
         case when random() > 0.5 then null::text else 'test'::text end as t1,
         random_text(floor(random() * 80)::int) as t2
  from generate_series(1, 10000) as x;

  delete from public.testtable1 where x % 4 = 0;

commit;

-- make sure PGSTAT_MIN_INTERVAL (1s) passed after the commit
-- for the statistics about dead tuples to be up to date
select pg_sleep(1.1); 

I’ve disabled autovacuum on the table (that’s not something you should normally do), because I also want to observe vacuum clean up the dead tuples.

So, with autovacuum disabled, we can see the dead tuples from the table stats:

select n_live_tup, n_dead_tup from pg_stat_user_tables where relname = 'testtable1';

 n_live_tup | n_dead_tup 
------------+------------
       7500 |       2500
(1 row)

After a vacuum, dead tuples will be gone:

vacuum public.testtable1;
select n_live_tup, n_dead_tup from pg_stat_user_tables where relname = 'testtable1';

 n_live_tup | n_dead_tup 
------------+------------
       7500 |          0

This way, the table is “bloated”, with an expected 25% of free space. Here, the bloat estimation function comes into play:

select * from estimate_bloat('public.testtable1');

In my example run this gave an estimate of 24%. Not a bad estimate!

The truth can be obtained with the “officially” measured space:

select free_percent from pgstattuple('public.testtable1');

that gives a value of 24.19%.

As a final test, let’s actually repack the table and see what happens. pg_relation_size() can be used to show the space used by the in-table data (just the data, not other resources, not out-of-table data and not any indexes):

select pg_relation_size('public.testtable1');

In the current run, this gives 925696 bytes.

Now let’s repack the table and run it again:

vacuum full public.testtable1;
select pg_relation_size('public.testtable1');

The new value is 696320 bytes. That means the percentage of space effectively released to the filesystem was 100 * (925696-696320) / 925696 = 24.8%. That’s quite in line with the expected value and the estimate.

One cool feature of Postgres is that it automatically compresses and stores larger records out of table (see → TOAST).

So what happens then? Let’s find out with another table that has larger records in the t2 field, say, between 10k and 15k characters:

begin;

drop table if exists public.testtable2;

create table public.testtable2 with (autovacuum_enabled = false) as
    select x::numeric,
           random() as r,
           random() > 0.5 as b,
           case when random() > 0.5 then null::text else 'test'::text end as t1,
           random_text(floor(random() * 10000 + 5000)::int) as t2
    from generate_series(1, 10000) as x;

    delete from public.testtable2 where x % 4 = 0;

commit;

select pg_sleep(1.1);  -- make sure PGSTAT_MIN_INTERVAL (1s) passed after the commit
                       -- for the statistics about dead tuples to be up to date

vacuum public.testtable2;

Let’s get the estimate:

select * from estimate_bloat('public.testtable2');

The result is, at first glance surprisingly, still in the right ballpark: 23%!

Technically Postgres stored the data in two tables here: In this run, 688128 bytes are in the actual table and 111861760 bytes in the TOAST table. The estimation function didn’t bother to look at the TOAST table at all! The idea is the statistics will still work out, if 25% of records have been freed, regardless of which table we sample.

After repacking the table, I get 516096 and 83959808 bytes. So the freed space in this run is 100 * (688128+111861760-516096-83959808)/(688128+111861760) = 24.9%, in line with the expectation, and the estimate of 23% wasn’t too bad.

The trick lies in the statistics: if there were a correlation between which records were deleted and how large they were, the estimation would be off.