Postgresql – Disk space requirement for REINDEX in Postgresql

disk-spaceindexpostgresqlvacuum

There is a server with postres 13 running out of disk space.

It contains a table metrics that occupies 90% of the entire database.
I have deleted old records in this table.
Due to this table is used in production, I cannot run "VACUUM FULL metrics".
Since I can't afford to block it.

After deleting old records I want to REINDEX CONCURRENTLY bloated indexes to free space.
But first of all I would like to know how much space is needed to reindex them. If the server runs out of space Postgres will crash.

So, is it possible to calculate a space requirement for REINDEX in Postgresql?

Best Answer

Up to three times than the size of new index:

index structure itself
WAL records during create index
temporary files while sorting index values

I have deleted old records in this table

It is possible to reclaim space using the pgcompacttable tool. pgcompacttable was designed specifically for compressing tables when there is not enough space for a copy of the table.

During an update or insert, postgresql will write the new version of the tuple to a free space at the beginning of the table. Therefore, if we somehow update all tuples at the end of the table, the subsequent vacuum will find only empty pages at the end of the table without actual (live) data and will return those pages to the file system. So the size of the table on disk will be reduced. pgcompacttable performs exacly such updates + vacuum.

Related Solutions

PostgreSQL – Does It Efficiently Reuse Space from Deleted Rows?

VACUUM rewrites the entire block, efficiently packing the remaining rows and leaving a single contiguous block of free space (though this space isn't zeroed and the physical disk file might contain the remnants of deleted rows which of course are in no way visible to the database user).

test schema:

--#psql postgres postgres
select oid from pg_database where datname='postgres';
/*
  oid
-------
 12035
*/
create schema stack;
set search_path=stack;
create table foo(bar text);
insert into stack.foo(bar) values('row 1');
insert into stack.foo(bar) values('row 2');
checkpoint;
select * from foo;
/*
  bar
-------
 row 1
 row 2
*/
select relfilenode
from pg_class c join pg_namespace n on n.oid=c.relnamespace
where nspname='stack' and relname='foo';
/*
 relfilenode
-------------
      446488
*/

physical contents of table backing file:

xxd -a /var/lib/postgresql/9.3/main/base/12035/446488

0000000: 3800 0000 f093 9d83 0000 0000 2000 c01f  8........... ...
0000010: 0020 0420 0000 0000 e09f 3c00 c09f 3c00  . . ......<...<.
0000020: 0000 0000 0000 0000 0000 0000 0000 0000  ................
*
0001fc0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001fd0: 0200 0100 0208 1800 0d72 6f77 2032 0000  .........row 2.. <---- this is row 2
0001fe0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001ff0: 0100 0100 0208 1800 0d72 6f77 2031 0000  .........row 1..

delete and vacuum:

delete from foo where bar='row 1';
vacuum stack.foo;
checkpoint;

physical contents of table backing file:

xxd -a /var/lib/postgresql/9.3/main/base/12035/446488

0000000: 3800 0000 e8b5 9e83 0000 0500 2000 e01f  8........... ...
0000010: 0020 0420 0000 0000 0000 0000 e09f 3c00  . . ..........<.
0000020: 0000 0000 0000 0000 0000 0000 0000 0000  ................
*
0001fc0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001fd0: 0200 0100 0209 1800 0d72 6f77 2032 0000  .........row 2.. <---- this is free space
0001fe0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001ff0: 0200 0100 0209 1800 0d72 6f77 2032 0000  .........row 2..

insert new row:

insert into stack.foo(bar) values('row 3');
checkpoint;

final physical contents of table backing file:

xxd -a /var/lib/postgresql/9.3/main/base/12035/446488

0000000: 3800 0000 c8ec 9e83 0000 0100 2000 c01f  8........... ...
0000010: 0020 0420 0000 0000 c09f 3c00 e09f 3c00  . . ......<...<.
0000020: 0000 0000 0000 0000 0000 0000 0000 0000  ................
*
0001fc0: 2476 4c14 0000 0000 0000 0000 0000 0000  $vL.............
0001fd0: 0100 0100 0208 1800 0d72 6f77 2033 0000  .........row 3.. <---- this is row 3
0001fe0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001ff0: 0200 0100 0209 1800 0d72 6f77 2032 0000  .........row 2..

clean up:

drop schema stack cascade;

Sql-server – Extracted data WAY bigger than deficit left from deleted rows

For the first thing I'd control the FillFactor for both indexes (you said your tables have PK defined on them, so I assume the tables are clustered).

It could be set to 50% in the first table or to smth like that, even if I do not see much sense in setting FF to smth different from 100 for an index on identity column.

In this case when you rebuilt the first table even if it should compact the table it could instead leave free space according to FF defined.

.................

The second option is your XML that did not release the space.

After you rebuilt your table all the index pages and data pages with in-row or row-overflow data are full again (of course if the FF was not changed to some lower value than it was before)

But the LOB pages were not compacted by rebuild. In this case you can try ALTER INDEX..REORGANIZE to compact your XML

Best Answer

Related Solutions

PostgreSQL – Does It Efficiently Reuse Space from Deleted Rows?

Sql-server – Extracted data WAY bigger than deficit left from deleted rows

Related Question