CODEWITHSUNDEEP
postgresqlvacuum
Roman Ganoshenko
Roman Ganoshenko

Reputation: 55

How to do full vacuum on table with lot of dead rows w/o disk space?

Will try to describe the problem:

We have a table "stories", current statistics:

total_relation_size: 188 GB

indexes only size: 52 GB

n_tup_del: 274299085

n_live_tup: 368846049

We currently have ~6 gigabytes left on our SSD.

Now we mounted another PC with ~190 gigabytes available, it's available as /dump folder.

How can we free up space?

Make pg_dump this table, then truncate it, and then restore? How safely is it?

Or we can somehow allow "vacuum full" to use mounted device (/dump) space to do? We understand that vacuum full will lock access.

Thanks in advance!

Upvotes: 2

Views: 431

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246338

A Windows "share" is not safe storage for a database.

First, perform a backup.

Ignoring foreign key constraints, you could proceed as follows:

  • Create a tablespace dump with location /dump.

  • Create an empty copy there:

    CREATE TABLE copy (LIKE largetable) TABLESPACE dump;

  • Run

    INSERT INTO copy SELECT * FROM largetable;

  • Drop the large table.

  • Move the new table to the default tablespace:

    ALTER TABLE copy SET TABLESPACE pg_default;

ALTER TABLE copy RENAME TO largetable;

  • Create indexes and constraints as appropriate.

  • Clean up:

    DROP TABLESPACE dump;

Upvotes: 1

Related Questions