Hot answers tagged

72

For the general settings use: select * from pg_settings where name like '%autovacuum%' for table specific settings, check out the column reloptions in pg_class: select relname, reloptions from pg_class You will probably want to join that to pg_namespace to limit this to a specific schema. Where joining needs to happen on the hidden col pg_namespace.oid (...


41

On my PostgreSQL (8.3) I use this trick: I get table's disk size using pg_total_relation_size() - this includes indexes and TOAST size, which is what VACUUM processes. This gives me the idea of how many bytes the VACUUM has to read. I run VACUUM on the table. I find the pid of the VACUUM process (in pg_catalog.pg_stat_activity). In Linux shell I run while ...


41

To return space to the OS, use VACUUM FULL. While being at it, I suppose you run VACUUM FULL ANALYZE. I quote the manual: FULL Selects "full" vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old ...


23

NOTE: I have tested this on 9.1. I have no 9.0 server lying around here. I am preeeettty sure though it will work on 9.0 though. CAUTION (As noted in the comments by @erny): Note that high CPU load due to I/O operations may be expected. You can do this with pretty much no down-time by using a temporary tablespace. The down-time will be in the form of ...


17

The key words here are: "heavily updated" "in the table for 2-3 hours". Point 1. is indication for a lower fill factor, while 2. is the opposite. It helps performance if multiple row versions are stored on the same data page. H.O.T. updates would achieve that. Read here or here. They need some wiggle room on the data page - like dead tuples or space ...


16

Here's a short concise answer. Vacuum full takes out an exclusive lock and rebuilds the table so that it has no empty blocks (we'll pretend fill factor is 100% for now). Vacuum freeze marks a table's contents with a very special transaction timestamp that tells postgres that it does not need to be vacuumed, ever. Next update this frozen id will disappear. ...


15

tl;dr: The first process that reads data after it is committed will set hint bits. That will dirty the page, creating write activity. The other thing VACUUM (but not other commands) does is marks the page as all-visible, if appropriate. VACUUM will eventually have to hit the table to freeze the tuples. The work that needs to be done after an insert isn't ...


12

I agree with ETL that there is no short answer. Size is not the only thing that matters - we run quite large PostgreSQL OLTP Databases (with some tables > 100.000.000 rows) under heavy load and currently we rely on autovacuum only. Yet, two things seem important to me: There seems to be a consensus, that autovacuum should never be switched off, unless you ...


12

ALTER TABLE .. DROP COLUMN ... marks the column as deleted in the system table pg_attribute. The table itself is not otherwise manipulated until rows are rewritten some way or another. The drop itself is very fast, but it does take a brief ACCESS EXCLUSIVE lock. I would not call that "downtime", though. Actually reclaiming disk space is the tricky ...


10

To check what CLUSTER does, I took a table fo mine from an earlier experiment which basically contained the first 10 million positive integers. I already deleted some rows and there is an other column as well but these only affect the actual table size, so it is not that interesting. First, having run VACUUM FULL on the table fka, I took its size: \dt+ ...


10

VACUUM can only remove dead tuples which are long-dead, that is, dead to all possible uses. If you have long-lived transactions, they may prevent the recently-dead tuples from being removed. This is an example of a situation where a long-lived transaction prevented removal: INFO: "pgbench_accounts": found 0 removable, 2999042 nonremovable row versions in ...


10

First things first — 8.4 is no longer supported, so consider upgrading. Autovacuum settings are documented. Let's focus on the settings that affects when autovacuum kicks in. As you might know, this process is responsible for both, vacuuming and analyzing tables. One of the settings affecting ANALYZE frequency is autovacuum_analyze_threshold. As you can ...


10

Work done by an interrupted VACUUM FULL will be entirely lost, as it will simply revert to using the previous version of the table and throw away the in-progress version of the table. Work done by a regular (not-FULL) VACUUM might not be entirely lost. It cleans indexes in batches, and any batches which were fully cleaned will not need to be cleaned again. ...


9

This is very hard to determine. You can tune autovacuuming to be more agressive or to be milder. But when set to mild and it is lagging behind and the base I/O load is too high, it can happen that it never reaches a proper vacuumed state - then you see the process running and running and running. Furthermore, later PostreSQL editions have much improved ...


9

VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old copy until the operation is complete. Usually this should only be used when a ...


9

The statistics are produced from the values in the columns. As these values are not changed by VACUUM FULL or even by VACUUM, a fresh ANALYZE is not specifically required. If the database is in a maintenance window, it's a good opportunity to run it, though. Its execution time is likely to be insignificant compared to VACUUM FULL. By default, a DBA will ...


9

Yes, it is safe. Vacuum full will recreate the tables as new objects and only when they're done they will be put to use. If you cancel it, the new files not yet put to use will be removed and old ones will be kept. Of course if you would cancel it by killing the process handling the vacuum there would be files left, but still the database would be intact.


8

I found this post and this post helpful, but like others have mentioned, it can be difficult to calculate the overall progress of vacuum, since the process involves a few separate operations. I use this query to monitor the progress of vacuum's table scanning, which seems to be the bulk of the work: SELECT heap_blks_scanned/cast(heap_blks_total as numeric)*...


8

Existing "qualify for autovacuum" script is very useful, but (as correctly stated) was missing table specific options. Here is modified version of it that takes those options into account: WITH rel_set AS ( SELECT oid, CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1) ...


8

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 ...


7

To further explain what Jayadevan wrote. The way that Postgres works with transactions, and to keep track of visible data is by comparing internal Transaction IDs. However, since those transactions are a 32-bit integer sooner or later they will wrap around, and therefore the new transaction will look like it was made in the past (and thus be visible in a ...


7

Autovacuum should pretty well cover it, unless you mis-configured something. Other answers cover that already. There is one clearly defined case for manual VACUUM (and more importantly: manual ANALYZE) though: temporary tables, they are not considered by the autovacuum demon. I quote the manual on CREATE TABLE here: The autovacuum daemon cannot access ...


7

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table. That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle ...


7

They are your data. You probably don't want to remove them ;) "nonremovable row versions" includes the current, active row versions containing the data you care about. It also includes row versions made obsolete by a delete or update, but still visible to the snapshot of one or more running transactions. You should only be concerned if there are lots of ...


7

The oldest transactions are some minutes old and haven't modified anything on the table yet. That's not sufficient. I think what is required to mark these rows as dead is that, when these transactions were started, there was no other transaction that had touched these rows (doing an UPDATE or DELETE on them). Updating or deleting a row will keep the ...


6

While I agree that using the auto features is best instead of running it database wide, in most cases per table tuning is necessary. I don't quite agree with the design choice of postgres to tie together vacuum and analyze, I have seen several instances where databases that do a lot of insert/update but little delete never get analyze done and start to ...


6

If you don't have concurrent transactions that would prohibit you from getting an exclusive lock on the table, I would: Select the (relatively few) surviving rows into a temporary table. Make sure you have enough RAM available for the temporary tables (for this session only). Read about temp_buffers in this related answer: Optimizing bulk update performance ...


6

I was able to recreate this. Essentially, when inside of a transaction, In READ COMMITTED the default transaction level: SELECT gets an AccessShareLock VACUUM can clean up dead rows versions pg_stat_activity.backend_xmin IS NULL for the transaction In SERIALIZABLE or REPEATABLE READ transaction levels: SELECT gets an AccessShareLock VACUUM can not clean ...


6

You need the pgstattuple extension to get the amount of free space. So you could run SELECT t.oid::regclass AS table_name, s.table_len AS size, dead_tuple_len + s.approx_free_space AS reclaimable FROM pg_class AS t CROSS JOIN LATERAL pgstattuple_approx(t.oid) AS s WHERE t.relkind = 'r' ORDER BY (s.dead_tuple_len::float8 + s.approx_free_space:...


5

If you use anything but CLUSTER / VACUUM FULL / pg_repack (which all manage locks automatically) you need to make sure there are no concurrent writes to the table. Take an exclusive lock on the table and do everything in a single transaction or, better yet, shut out all connections to avoid concurrent changes. TABLESPACE Yes, your last idea could work. ...


Only top voted, non community-wiki answers of a minimum length are eligible