4

I'm not that experienced with postgresql.

Did a simple command with 

DELETE FROM table_name where some_condition;

There are thousands of rows involved. But after that command actually, the disk space got even smaller.

Any Idea what is going wrong? I enabaled autovacuum = on and also tried to execute 'VACUUM FULL;' but that consumed my whole disk space.

What I want to do is pretty simple. Delete rows and get the space. There is a lot of space involved and not that much space left on the machine. Is there a way to do it?

MichaelRazum
  • 789
  • 1
  • 10
  • 26

2 Answers2

6

autovacuum should be running in any modern Postgres database unless you know exactly what you are doing. But autovacuum is not going to shrink tables except for rare corner cases. It schedules periodic VACUUM and ANALYZE jobs, but never VACUUM FULL, which acquires an exclusive lock on processed tables and indexes. VACUUM helps to avoid table and index bloat, though, by cleaning up dead tuples (along with various other good deeds) and thereby making space available to future write operations on the same object - not returning space to the OS, yet.

You write:

tried to execute 'VACUUM FULL;' but that consumed my whole disk space.

You mean temporarily? Because that's what VACUUM FULL does. But when it's done, tables and associated indexes are reduced to their minimum size - which is not generally desirable, unless you urgently need the disk space or rows are never updated.

So VACUUM FULL is the right tool for you, unless you are out of disk space already - which should best not occur to begin with. So you need to create some wiggle room to let it work its magic.

There is also the community tool pg_repack, which can do the same as VACUUM FULL without exclusive locks. But it also needs some free disk space to work. Related answer with more details:

To delete all rows of a table and immediately release disk space, use TRUNCATE. It effectively writes a new file on disk and deletes the old. That's also why it typically can't be used with concurrent load on a DB.

TRUNCATE table_name;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
3

VACUUM FULL works by creating a new version of each table and copying all the data. An old table isn't deleted until the new table is complete, so a VACUUM FULL operation will temporarily increase disk space while it is processing. See this note in the PostgreSQL documentation (emphasis mine):

Tip: Plain VACUUM may not be satisfactory when a table contains large numbers of dead row versions as a result of massive update or delete activity. If you have such a table and you need to reclaim the excess disk space it occupies, you will need to use VACUUM FULL, or alternatively CLUSTER or one of the table-rewriting variants of ALTER TABLE. These commands rewrite an entire new copy of the table and build new indexes for it. All these options require exclusive lock. Note that they also temporarily use extra disk space approximately equal to the size of the table, since the old copies of the table and indexes can't be released until the new ones are complete.

A regular VACUUM, on the other hand, just prunes deleted rows and index entries from the database file.

You may have more success if you first run a regular VACUUM and then try running a VACUUM FULL. You may also have more success if you try running VACUUM FULL on specific tables. If you run them in the right order, you might be able to recover enough disk space that the remaining tables in the database can run VACUUM FULL. Otherwise, you will need to find some way of increasing the available disk space so VACUUM FULL can complete.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • Is there some workaround, or what is the reason that after a delete per sql the disk space isn't released? As I understood I need to have at least as much space as the database consumes if I want to run this command. Does it make sense to chunk the deletes statements, let's say in 1000 portions and then run VACUUM? – MichaelRazum Dec 16 '17 at 13:09
  • Disk space isn't immediately released by design. The same page I linked above says, "This approach is necessary to gain the benefits of multiversion concurrency control (MVCC, see Chapter 13): the row version must not be deleted while it is still potentially visible to other transactions." – Bacon Bits Dec 16 '17 at 13:12
  • 1
    Only `VACUUM FULL` requires disk space. `VACUUM` does not require disk space. I would guess that batch deletes followed by periodic `VACUUM`s would not help in your current situation, but maybe there's something I'm missing. I'm not a big expert on PostgreSQL. – Bacon Bits Dec 16 '17 at 13:17
  • There is an [autovacuum daemon](https://www.postgresql.org/docs/9.2/static/routine-vacuuming.html#AUTOVACUUM) that you can enable in PostgreSQL, which periodically will clean up stale deleted rows and indexes. It's recommended that you use it. I don't know if it's enabled by default or not. – Bacon Bits Dec 16 '17 at 13:17
  • Ok I understand. So I think if that is enabled I just have to wait. Is there a way to find out if that daemon is running and what it is doing right now ? – MichaelRazum Dec 16 '17 at 13:43
  • 1
    `SHOW autovacuum;` will tell you if it's running. I don't remember if it logs activity to a text file. You can see when a table was last touched by querying `SELECT * FROM "pg_catalog"."pg_stat_all_tables"`. There's columns like `last_autovacuum`, `last_autoanalyze`, `autovacuum_count`, etc. – Bacon Bits Dec 16 '17 at 13:54