PostgreSQL directory very full

postgresql-8.4

In the /var/lib/pgsql/data/base Postgres that in version 8.4 and it was identified that the error single bank is with 37G which is absurd, is not already realized vacuum full declined nothing and also I put the autovacuum as off in postgresql.conf. the only thing I can do is back up and after that uninstall and reinstall the bank to resolve palliative-mind.

Can someone help me?

I am using Centos 6.7 and I know the Bank that's full …

Best Answer

Vacuum (even FULL) isn't the most efficient when it comes to reclaiming space from bloated indexes. And in PostgreSQL 8.x it was even more the issue. I do remember reading about tests where dump, drop and restore might be indeed faster and gets better results than vacuum full. And this is what I would suggest if possible.

There is another option you might try first though, quoting PostgreSQL 8.4 documentation:

The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM would. The FULL option does not shrink indexes; a periodic REINDEX is still recommended. In fact, it is often faster to drop all indexes, VACUUM FULL, and recreate the indexes.

Related Solutions

Postgresql – Degrading PostgreSQL 8.4 write performance

Assuming it's not a VACUUM problem, it sounds like you've got an unindexed FOREIGN KEY. During the bulk load, do you see an increase in CPU or disk IO?

For giggles, can you please post the output from a VACUUM VERBOSE ANALYZE table_name? An EXPLAIN ANALYZE INSERT ... may also be useful along with as much of the relevant schema.

Postgresql – TOAST Table Growth Out of Control – FULLVAC Does Nothing

This:

INFO: "pg_toast_16874": found 22483 removable, 10475318 nonremovable row versions in 10448587 pages 22483 removable, 10475318 nonremovable row versions in 10448587 pages

suggests that the underlying issue is that something can still "see" those rows so they can't be removed.

The candidates for that are:

Lost prepared transactions. Check pg_catalog.pg_prepared_xacts; it should be empty. Also run SHOW max_prepared_transactions; it should report zero.
Long-running sessions with an open, idle transaction. In PostgreSQL 8.4 and above this should only be an issue for SERIALIZABLE transactions. Check pg_catalog.pg_stat_activity for <IDLE> in transaction sessions.

Most likely you have a client that's failing to commit or rollback transactions during long idle periods.

If this doesn't turn out to be it, the next thing I'd check would be to do a sum of the octet_size of each column of the table of interest. Compare that to the pg_relation_size of the table and its TOAST side-table. If there's a big difference then the space consumed is likely by no longer visible rows and you probably do have table bloat issues. If they're quite similar, you can start narrowing down where the space use is by summing up the octet sizes per column, getting the top 'n' values, etc.

Best Answer

Related Solutions

Postgresql – Degrading PostgreSQL 8.4 write performance

Postgresql – TOAST Table Growth Out of Control – FULLVAC Does Nothing

Related Question