We have a pretty big postgres 14 database with timescaledb installed. Recently we had some issues with MXID and XID wraparound so we started to monitor these as well as autovacuum. What we noticed is that with our 1000/s transaction rate we have to vacuum the whole database every 2 days to prevent XID wraparound. However, what is not clear is why do we have to vacuum old partitions (chunks in timescale) which are not touched sometimes for years. I see similar patterns for many of these tables, basically all but one pages are frozen. Could this one page contain some metadata about the table that cannot be cleaned up?

automatic aggressive vacuum to prevent wraparound of table "chdb._timescaledb_internal._hyper_161_172231_chunk": index scans: 0
        pages: 0 removed, 8052 remain, 0 skipped due to pins, 8051 skipped frozen
        tuples: 0 removed, 302204 remain, 0 are dead but not yet removable, oldest xmin: 987543035
        index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed

However, when i manually vacuum this partition i always see a few nonremovable rows which i guess is responsible for that page being unfrozen. Is there a way to inspect these rows? Or actually postgres has to vacuum each table every 2 billion transactions?

chdb=# VACUUM (INDEX_CLEANUP FALSE, FREEZE, VERBOSE) _timescaledb_internal._hyper_161_172231_chunk;
INFO:  aggressively vacuuming "_timescaledb_internal._hyper_161_172231_chunk"
INFO:  table "_hyper_161_172231_chunk": found 0 removable, 4 nonremovable row versions in 1 out of 8052 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 990878593
Skipped 0 pages due to buffer pins, 8051 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  aggressively vacuuming "pg_toast.pg_toast_1865219981"
INFO:  table "pg_toast_1865219981": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 990878593
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

**Edit** I also just found out that some tables that are truncated (we cannot drop them) are still being autovacuumed although it has 0 rows. Autovacuum happened after the table was already truncated and nothing inserted to it since. The XID age (age(c.relfrozenxid)) is still growing after manually doing vacuum freeze on the table.

We have PostgreSQL 9.6 databases and a dailytask running a pg_dump for all databases, though these backups are getting "large" at this point. My database was 900MB, then I tried clearing it by deleting old history which is not necessary anymore and after that ran VACUUM FULL. The statistics in pgAdmin say that the database is now only 30MB. When I run the pg_dump command manually through a Command prompt, it creates a file of 22MB. When I run my daily task (windows task scheduler) it still creates a backup file of 1GB. What am I missing at this point?

I'm using postgresql 9.5 and partman for partitioning tables. there is this table that has 30 partitions and volume of one of them are too higher than others. so I need to full vacuum this partition but I don't want to lock the main table. is it possible?

I want to create a stored procedure to trigger vacuum, I know there are many ways to automate vacuum, but I would like to use the stored procedure (Postgresql 9.6). The problem Im facing is, I can't run the vacuum inside the BEGIN/END so there is a workaround, we can use set autocommit=on. But when I add this autocommit inside the stored procedure, its throwing error. ### Sample SP:

create procedure test(table_list varchar(1000))
LANGUAGE plpgsql 
AS 
$$ 
DECLARE 
*** some values ***
BEGIN 
EXECUTE 'set autocommit=on';
*** Rest of the command to get the table names one by one and vacuum in a loop***
END;
$$

Is this possible? Or any alternate way?

In my app for Postgresql I have a table and my app inserts rows to that table. It could be 2-3 rows, also it could be 100-200k rows. How to do that auto vacuum runs for that table as soon as possible? I played with **autovacuum_analyze_scale_factor**, **autovacuum_analyze_threshold** and other parameters for this table but didn't get good results. Does anybody know how to do that?

Sqlite, contrary most SQL-compatible DB engine, is working directly on files instead over network sockets. It is also not very good in concurrency, beside that deletion operators are mostly advisory (i.e. do not free the places of the deleted entities). That results that an sqlite database needs regularly be VACUUM-ed, at least those with regular modify or delete operations. Vacuum works by rebuilding the database file, without the free spaces in it. Needless to say, it is absolutely non-concurrent with anything, making it to a costly operation - we have downtime for the vacuum. *However, I think there should exist some indicator which compares the size of the database file to the size of the actual data in it.* Using this indicator, it could be said, do we need a vacuum-ing or not. Does it exist?

I am working with a PostgreSQL table with a large TEXT field, which is theoretically updated on a regular basis. I have thought about storing the data directly in the filesystem, but with TOAST, the data is already being stored off-page and compressed in the database so I figured I would keep things simple and just use the database storage. To give some context, I am indexing RSS feeds. I will have a script run every 24h that pulls the RSS feeds and potentially updates the table. This can lead to a lot of dead tuples, and thus lots of space being used on disk. Of course, autovacuum will take care of this eventually but it has the potential to be a lot of data (many GB) and I want to make sure I know what will happen when I am doing lots of updates on this very large table. One solution I have is to only update the TEXT field (storing the RSS data) if there are certain substantial changes to the feed, e.g. a new post on the website. This means that I could avoid doing the UPDATE unless I *really* have to. However, I still want to update the table (to keep track of when I most recently did an HTTP request). This will create a dead tuple with the old version of the row's data. What will happen to the TEXT data, if it isn't actually changed? Will the UPDATE also duplicate the TEXT data, when it creates a dead tuple? Or will the TEXT data be left untouched, because it wasn't changed and it is stored off-page?

I have a single table with an approximate size of 85gb and roughly around 120 million of rows (in a PostgreSQL (12.8) database). I need to update all 120 something million rows with an update query to get rid of certain characters existing in all rows of a column by using the function of regexp_replace. And another constraint I have is that I don't have much disk space (around 20 gb), considering the necessity of duplication of the table while running the update operation. But this is a secondary issue for me. For the time being, the main problem is the slowness of this operation during the run and **more importantly** increasing size of table once the run has been completed. This increase in the size leaves no other option for me other than running a **full vacuum** on the table, which is something I cannot do due to the aforementioned disk space limitations. I should also add that this table is indexed (with different methods including btree) and also has a primary key. I also tried to create cluster on the index but nothing has changed. In order to reduce the run time I selected only 20 million rows from the main table and tried to work on that one, however that also didn't help. Also related to the **full vacuum** necessity I mentioned above, even if I try to update the table by applying a where condition to update only a smaller portion at each run, that also causes an growth in size, which means that I have to run full vacuum afterwards, as normal vacuum does not help reducing the size back to what it was before the run.

I have had some bad historical usage on my Aurora PostgreSQL instance. Namely, connections that create a lot of temporary objects and some very long running transactions (which limits autovacuum). Now I have some very, very bloat pg_catalog indices. For example: pg_attribute has only 34k records but *pg_attribute_relid_attnam_index and pg_attribute_relid_attnum_index are 630MB and 450MB!* In total I have gigabytes of (bloated) system indices on system tables. However, I cannot REINDEX or VACUUM FULL these tables due to permissions. Is this situation correctable in any way? Or is my RDS managed database permanently hosed and I need to migrate to a new one.

We are running: user@primary/client-n:~$ psql -d database -c "SELECT version();" version --------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 10.7 (Ubuntu 10.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit (1 row) on: user@primary/client-n:~$ lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 16.04.6 LTS Release: 16.04 Codename: xenial and have a setup with one primary and two streaming replication clients configured with: user@client-n:~$ psql -d postgres -c "SELECT name, setting FROM pg_settings WHERE name IN ( 'hot_standby', 'hot_standby_feedback', 'max_standby_streaming_delay' );" name | setting ----------------------------+--------- hot_standby | on hot_standby_feedback | on max_standby_streaming_delay | 150000 (3 rows) We have one database only (other than the standard ones) and one table in the database. Approximately 3 to 4 times per day we run into a special case autovacuum situation which is described in the documentation as: > [...] it (autovacuum) will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained We are monitoring pg_locks and have been able to observe the autovacuum daemon taking the table-level lock ACCESS EXCLUSIVE which in turn leads to a whole host of blocked processes on the clients as illustrated with the log entries below: Primary: ... 2019-06-04 05:59:29.154 BST [8998-1] LOG: automatic vacuum of table "database.schema.table": index scans: 1 ... Client 1: ... 2019-06-04 05:59:03.660 BST [21167-858] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG: process 21167 still waiting for AccessShareLock on relation 16390 of database 16388 after 1000.222 ms 2019-06-04 05:59:03.660 BST [21167-859] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL: Process holding the lock: 2741. Wait queue: 21167, 1215, 26415. 2019-06-04 05:59:03.660 BST [21167-860] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT: SELECT ... 2019-06-04 05:59:03.730 BST [1215-51] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG: process 1215 still waiting for AccessShareLock on relation 16390 of database 16388 after 1000.188 ms at character 15 2019-06-04 05:59:03.730 BST [1215-52] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL: Process holding the lock: 2741. Wait queue: 21167, 1215, 26415. 2019-06-04 05:59:03.730 BST [1215-53] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT: SELECT ... ... 2019-06-04 05:59:19.975 BST [22242-4569] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG: process 22242 still waiting for AccessShareLock on relation 16390 of database 16388 after 1000.281 ms at character 15 2019-06-04 05:59:19.975 BST [22242-4570] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL: Process holding the lock: 2741. Wait queue: 21167, 1215, 26415, 2423, 1289, 24009, 22441, 2640, 1843, 1056, 23336, 28060, 1860, 1134, 19419, 14649, 2721, 29540, 20138, 22242. 2019-06-04 05:59:19.975 BST [22242-4571] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT: SELECT... ... And the process holding the lock: postgres=# SELECT pid, backend_type, wait_event_type, wait_event FROM pg_stat_activity WHERE pid = 2741; pid | backend_type | wait_event_type | wait_event ------+--------------+-----------------+---------------- 2741 | startup | Activity | RecoveryWalAll (1 row) Client 2: ... 2019-06-04 06:00:08.964 BST [16153-1] [PostgreSQL JDBC Driver@ip_address(port):role@database] | FATAL: terminating connection due to conflict with recovery 2019-06-04 06:00:08.964 BST [16153-2] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL: User was holding a relation lock for too long. 2019-06-04 06:00:08.964 BST [16153-3] [PostgreSQL JDBC Driver@ip_address(port):role@database] | HINT: In a moment you should be able to reconnect to the database and repeat your command. 2019-06-04 06:00:09.964 BST [5747-537] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG: process 5747 still waiting for AccessShareLock on relation 16390 of database 16388 after 1000.248 ms 2019-06-04 06:00:09.964 BST [5747-538] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL: Process holding the lock: 12709. Wait queue: 5747, 19765, 16036, 14617, 12280, 14513, 14728, 15398, 27611, 14542, 15948, 23398, 5853, 5098, 4324, 10760, 23480, 30192, 15300, 16228. 2019-06-04 06:00:09.964 BST [5747-539] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT: SELECT ... 2019-06-04 06:00:09.975 BST [19765-6847] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG: process 19765 still waiting for AccessShareLock on relation 16390 of database 16388 after 1000.180 ms 2019-06-04 06:00:09.975 BST [19765-6848] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL: Process holding the lock: 12709. Wait queue: 5747, 19765, 16036, 14617, 12280, 14513, 14728, 15398, 27611, 14542, 15948, 23398, 5853, 5098, 4324, 10760, 23480, 30192, 15300, 16228. 2019-06-04 06:00:09.975 BST [19765-6849] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT: SELECT ... ... 2019-06-04 06:01:25.487 BST [15873-1] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG: process 15873 still waiting for AccessShareLock on relation 16390 of database 16388 after 1000.218 ms at character 15 2019-06-04 06:01:25.487 BST [15873-2] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL: Process holding the lock: 12709. Wait queue: 5747, 19765, 16036, 14617, 12280, 14513, 14728, 15398, 27611, 14542, 15948, 23398, 5853, 5098, 4324, 10760, 23480, 30192, 15300, 16228, 16127, 16285, 15873. 2019-06-04 06:01:25.487 BST [15873-3] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT: SELECT ... ... 2019-06-04 06:01:29.160 BST [16127-6] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG: process 16127 acquired AccessShareLock on relation 16390 of database 16388 after 8560.748 ms at character 15 2019-06-04 06:01:29.160 BST [16127-7] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT: SELECT ... ... And the process holding the lock, again: postgres=# SELECT pid, backend_type, wait_event_type, wait_event FROM pg_stat_activity WHERE pid = 2741; pid | backend_type | wait_event_type | wait_event ------+--------------+-----------------+---------------- 12709 | startup | Activity | RecoveryWalAll (1 row) The blocked queries on the clients lead to API latency of between 10 to 20 seconds and occasionally, a high number of 5xx responses. Out SRE team has been tasked with bringing the API latency during these incidents down and we are looking for ways of resolving this what we understand to be a very niche situation. We are currently experimenting with recovery_min_apply_delay = 120s on client 1 (hence the later log entries) so that both clients do not lock up at the same time. This has somewhat reduced he number of erroneous responses and lowered the latency spikes a bit. We are unsure about how to go about resolving this issue fully, and in fact, whether it is possible to do so. We would appreciate your advice. We have found this related post , but sadly, it hasn't been resolved either.

I am working with a PostgreSQL database and need some advice on when to run VACUUM ANALYZE on my tables. 1. I am attaching a very large table tmp_details (50 million records) as a partition to an existing table details (500 million records). The details table is actively used for reporting, but I am not performing any other write operations on it. Given the PostgreSQL documentation (https://www.postgresql.org/docs/current/populate.html) suggests running VACUUM ANALYZE after loading large amounts of data: - Is it necessary to run VACUUM ANALYZE on the details table after attaching tmp_details to it? - If yes, should I vacuum the entire details table or just the newly attached partition? 2. I am also creating a new table notreconciled (10 million records) from tmp_details. The notreconciled table will be used both for reporting and as a partition later. Should I run VACUUM ANALYZE on the notreconciled table after creating it? Any insights or best practices would be greatly appreciated!

I have a heavily used table (with around 5 million rows) in a PostgreSQL database in which I want to drop a column and reclaim the space the column used. The docs suggest doing a table rewriting ALTER TABLE to force the space back, but that is not safe to run while the table is used and would cause downtime. Are there any practical options that don't require any downtime? I tried to run the pgcompact tool, but that did not change anything.

I have a table in my database that used to have data long time ago, but now data have been deleted and is empty and no new data are inserted on it. Once a day I run vacuum analyze for several tables and for this specific one I noticed that it takes much time. Below is the verbose output 2024-10-03 07:12:22,217 INFO Vacuum/Analyze output: [INFO: vacuuming "public.activity_by_cgi_rat" INFO: index "activity_sec_by_cgi_rat_pk" now contains 0 row versions in 10940559 pages DETAIL: 0 index row versions were removed. 10940553 index pages have been deleted, 10940553 are currently reusable. CPU: user: 18.87 s, system: 52.19 s, elapsed: 2627.28 s. INFO: "activity_by_cgi_rat": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1176039973 There were 0 unused item identifiers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 18.87 s, system: 52.19 s, elapsed: 2627.28 s. INFO: vacuuming "pg_toast.pg_toast_21943" INFO: index "pg_toast_21943_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.03 s. INFO: "pg_toast_21943": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1176065166 There were 0 unused item identifiers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.06 s. INFO: analyzing "public.activity_by_cgi_rat" INFO: "activity_by_cgi_rat": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows ] 2024-10-03 07:12:22,218 INFO msgtype=PERF component=analytics.rdb operation=db_expire.vacuum_db_table elapsed_sec=2627.60 db_table=activity_by_cgi_rat Almost 1h for an empty is much time isn't it? Why is this happening? I notice this line in the verbose output: INFO: index "activity_sec_by_cgi_rat_pk" now contains 0 row versions in 10940559 pages which looks suspicious: Why in an empty table so much pages? --- EDIT --- I read in docs the following: In PostgreSQL 7.4 and later, index pages that have become completely empty are reclaimed for re-use. There is still a possibility for inefficient use of space: if all but a few index keys on a page have been deleted, the page remains allocated. So a usage pattern in which all but a few keys in each range are eventually deleted will see poor use of space. For such usage patterns, periodic reindexing is recommended. Is it possible that I have to reindex the table?

I am trying to get rid of unused indexes. The index usage is very much depends on the output of pg_stat_user_indexes.idx_scan (correct me if i am wrong). Just wondering what are the impacts of autovacuum or vacuum and auto analyze or analyze on the index usage counters. Will it reset the counters pg_stat_user_indexes.idx_scan to zero every time the autovacuum or vacuum and auto analyze or analyze completed? I am looking at the potential mistakenly removal useful indexes after the usage counters are set to zeros by unknown processes. Is there other process can potentially change the counters apart by calling pg_stat_database, pg_stat_reset_single_table_counters, rebuild index, removal, recreating index, change the column definitions? I hope some of your smart brains can shine some light on this. Thank ahead.

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is that I'm not sure what will happen if I run VACUUM FULL and there is not enough free space to make a copy of the table. I understand that VACUUM FULL should not be used but I figured it was the best option in this scenario. Any ideas would be appreciated. I'm using PostgreSQL 9.0.6

I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows:

autovacuum_vacuum_cost_delay   = 0     #Turn off cost based vacuum
autovacuum_vacuum_cost_limit   = 10000 #Max value
autovacuum_vacuum_threshold    = 50    #Default value
autovacuum_vacuum_scale_factor = 0.2   #Default value

I notice that the auto vacuum only kicks in when the database is not under load, so I get into situations where there are far more dead tuples than live tuples. See the attached screenshot for an example. One of the tables has 23 live tuples but 16845 dead tuples awaiting vacuum. That's insane! Auto vacuum kicks in when the test run finishes and the database server is idle, which is not what I want as I would like auto vacuum to kick in whenever the number of dead tuples exceeds 20% live tuples + 50, as the database has been configured. Auto vacuum when the server is idle is useless to me, as the production server is expected to hit 1000s of updates / sec for a sustained period which is why I need auto vacuum to run even when the server is under load. Is there anything that I'm missing? How do I force auto vacuum to run while the server is under heavy load? **Update** Could this be a locking issue? The tables in question are summary tables which are populated via an after insert trigger. These tables are locked in SHARE ROW EXCLUSIVE mode to prevent concurrent writes to the same row.

We have a multi-tenant SaaS app. We had been using a shared DB for all our tenants with TenantId as foreign key in all the tables. Everything had been working fine, till our business required DB (or Schema) per Tenant architecture. We upgraded our backend (ASP.NET Core 8) to handle this architecture and ambitiously migrated all our tenants to a separate DB on a cluster. We have around 1000 DBs on the cluster and since our migration life has been very difficult. Where with the same amount of data and load even 32 GB 4 CPU r7g.xl instance seemed over-provisioned, now even 16 CPU 128 GB r7g.4xl AWS instance sometimes seems underprovisoned. Out of 1000 DBs more than half won't even have more than 100 MB data. Few can have data in few GBs. Since we had been using AWS DMS (serverless) also the replication had been inactive for some time. So came the warnings in vacuum after some time: WARNING: oldest xmin is far in the past When we checked all the possible reasons, it turned out replication slots are holding the xmin. Here is what the following query returns:

SELECT c.relnamespace::regnamespace as schema_name, c.relname as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
2^31-1000000-greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as remaining
FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm') ORDER BY 4;

Result: The value is increasing, we tried deleting the replication slots two times, and each time it caused a downtime and cluster couldn't recover and we had to resort to creating a new cluster from backup. So the moment we delete the inactive replication slots this happens: The insert/update queries which otherwise run fine, suddenly just after deleting the replicating slots start showing LW locks. Here is how it looks like now (slots not deleted): We have come to believe it is a mistake to have more than 300 DBs per cluster. So we will be using multiple clusters with around 300 DBs per cluster. But what should we do now, and why deleting the replication slots triggers these locks?

I think my question is if I drop constraints and or indices can that space be used by a vacuum full? However I don't really know enough to be sure that's the right question. I have a Heroku managed DB, it has allocated storage of 768GB I am using ~500GB(there's a big full vacuum running it'll be clearer after that) Leaving ~268GB available (edit: vacuum is done I actually have 289GB free) There is this other table that is getting a diet, it has: * pg_relation_size = 274GB * pg_table_size = 283GB * pg_indexes_size = 74GB * pg_total_relation_size = 357GB I'm going to drop the column that is producing most of the toast (moving the data to S3) and two of the FK's which show in \d as having indicies and constraints. I'm looking at how can I full vacuum this thing afterward. (I will do regular vacuum first) My understanding of full vacuum is basically it makes a whole other copy. If my changes get the pg_total_relation_size under the ~268GB free space then presumably I'm good. But that seems unlikely. So I'm wondering if the full vacuum can use any of the space that gets marked as reusable by the regular vacuum, if so there might be enough room anyway. So will the vacuum be able to make use of the space freed up by any of the above? Is there anywhere else I should look for freeing up some space? Also is there anyway to know in advance if I'm going to have enough space for the full vacuum? Of course my fall back plan is to eat the time and cost for moving the whole thing to a bigger database plan, vacuuming it and then moving it back. edit: what if I drop all the indicies and constraints, full vacuum and then add them back? then the "copy" doesn't need space for those btw I'm able to shut down all access to this table for a day or two if need be, hence my ability to full vacuum it

We have time series monitoring on both pg_stat_database as well as pg_stat_statements. Recently We have been observing temp file usage in our DB during certain period. But for the same period we are not able to find queries that in total equates to total usage from pg_stat_database. We have seen somewhere maintenance operations like vacuuming and analyse can cause temp file usage, but for the period we couldn't find such activities as well. Is there any other internal operations which can cause temp file usage?

We run a "manual" VACUUM ANALYZE VERBOSE on some of our larger tables after we do major DELETE/INSERT changes to them. This seems to work without issue although sometimes a table's VACUUM job will run for hours (see this post for similar issues and reasoning). On doing more research I found that we have large tables with a large number of dead tuples even after running VACUUM. For example, here are some of the stats produced from the query in this response . -[ RECORD 50 ]--+--------------------------- relname | example_a last_vacuum | 2014-09-23 01:43 last_autovacuum | 2014-08-01 01:19 n_tup | 199,169,568 dead_tup | 111,048,906 av_threshold | 39,833,964 expect_av | * -[ RECORD 51 ]--+--------------------------- relname | example_b last_vacuum | 2014-09-23 01:48 last_autovacuum | 2014-08-30 12:40 n_tup | 216,596,624 dead_tup | 117,224,220 av_threshold | 43,319,375 expect_av | * -[ RECORD 52 ]--+--------------------------- relname | example_c last_vacuum | 2014-09-23 01:55 last_autovacuum | 2014-09-23 18:25 n_tup | 309,831,136 dead_tup | 125,047,233 av_threshold | 61,966,277 expect_av | * The last field states that these (and most tables) would meet the threshold for autovacuum. However, having just run VACUUM ANALYZE VEBOSE on each of those tables, shouldn't the dead tuple count be 0 (or close to 0, not 125M of 300M)? The documentation states: > VACUUM reclaims storage occupied by dead tuples. Does this mean our VACUUM is not working? ---------- **UPDATE** Per request in repsonse here are some logs from the VERBOSE jobs: INFO: vacuuming "public.example_1" INFO: scanned index "idx_example_1_on_gp_id_and_dd_id" to remove 378386 row versions DETAIL: CPU 1.83s/3.42u sec elapsed 23.01 sec. INFO: scanned index "index_example_1_on_q_id" to remove 378386 row versions DETAIL: CPU 2.10s/3.91u sec elapsed 18.92 sec. INFO: "example_1": removed 378386 row versions in 7085 pages DETAIL: CPU 0.09s/0.05u sec elapsed 0.19 sec. INFO: index "idx_example_1_on_gp_id_and_dd_id" now contains 30347438 row versions in 291065 pages DETAIL: 378386 index row versions were removed. 165587 index pages have been deleted, 164287 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "index_example_1_on_q_id" now contains 30347438 row versions in 333287 pages DETAIL: 378386 index row versions were removed. 152773 index pages have been deleted, 152757 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "example_1": found 1773 removable, 401984 nonremovable row versions in 14438 out of 1493006 pages DETAIL: 0 dead row versions cannot be removed yet. There were 10567 unused item pointers. 0 pages are entirely empty. CPU 4.26s/7.51u sec elapsed 46.10 sec. INFO: vacuuming "pg_toast.pg_toast_17917" INFO: index "pg_toast_17917_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_17917": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.example_1" INFO: "example_1": scanned 30000 of 1493006 pages, containing 611502 live rows and 0 dead rows; 30000 rows in sample, 40563141 estimated total rows This table now shows 0 dead tuples in the stats. Most of the tables are much lower dead tuples this morning so either our VACUUM or autovacuum is working. We do have a handful of tables that output nothing and yet still show dead tuples: -[ RECORD 49 ]--+--------------------------- relname | example_2 last_vacuum | 2014-09-23 02:23 last_autovacuum | 2014-09-02 14:30 n_tup | 117,914,944 dead_tup | 34,507,388 av_threshold | 23,583,039 expect_av | * A couple times I have seen in the logs where the indexes will get checked over and over again. This seems to correspond to long-running VACUUM jobs. Any idea why? Is this just working around record locking (I don't think any writes were happening during this jobs run.) INFO: vacuuming "public.example_2" ... INFO: scanned index "index_example_2_on_gsg_id_and_dd_id" to remove 2795959 row versions DETAIL: CPU 3.88s/16.54u sec elapsed 23.09 sec. INFO: scanned index "index_example_2_on_q_id" to remove 2795959 row versions DETAIL: CPU 6.74s/21.13u sec elapsed 84.64 sec. INFO: "example_2": removed 2795959 row versions in 48214 pages DETAIL: CPU 0.71s/0.32u sec elapsed 33.65 sec. INFO: scanned index "index_example_2_on_gsg_id_and_dd_id" to remove 2591011 row versions DETAIL: CPU 2.84s/16.11u sec elapsed 19.28 sec. INFO: scanned index "index_example_2_on_q_id" to remove 2591011 row versions DETAIL: CPU 5.46s/22.70u sec elapsed 130.57 sec. INFO: "example_2": removed 2591011 row versions in 45539 pages DETAIL: CPU 0.67s/0.38u sec elapsed 15.16 sec. INFO: index "index_example_2_on_gsg_id_and_dd_id" now contains 123807784 row versions in 1560915 pages DETAIL: 108836958 index row versions were removed. 1100790 index pages have been deleted, 718471 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.25 sec. INFO: index "index_example_2_on_q_id" now contains 123807784 row versions in 1886087 pages DETAIL: 110336259 index row versions were removed. 1058063 index pages have been deleted, 266983 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.07 sec. INFO: "example_2": found 124808 removable, 1355901 nonremovable row versions in 2086343 out of 6966379 pages DETAIL: 0 dead row versions cannot be removed yet. There were 7858495 unused item pointers. 0 pages are entirely empty. CPU 595.49s/2130.13u sec elapsed 5656.34 sec. INFO: vacuuming "pg_toast.pg_toast_18079" INFO: index "pg_toast_18079_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_18079": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.example_2" INFO: "example_2": scanned 30000 of 6966379 pages, containing 528443 live rows and 522 dead rows; 30000 rows in sample, 152953760 estimated total rows