0

We are running a PostgreSQL database on Windows Server. The database version is 9.4.5 (it's been running stable for a long time now so we aren't updating it).

Some of our tables have millions of rows, and they are running fine.

What causes problems is a tiny little table that is only ~2000 rows. This table is used to store current status reported from remote devices. These devices are sending their updated status every few seconds, so there are quite a few updates in that database each second. Nothing is being removed though, the rows are kept intact they are just being updated every time a device reports new status.

Some time ago I have noticed huge performance drop on our Apache server that uses this database. What I saw was that the status table was >3GB in size while having only 2000 rows. I read a lot on the internet, tried normal VACUUMs but they didn't do anything.

I finally went for VACUUM FULL as I saw that it is safe to use it on versions 9.0 and above. That really helped, the table size dropped just to a few KBs!

However, the problem is happening periodically every few days, and now when I try to run VACUUM FULL it takes a very long time, and our whole service is unusable during that time.

What could I do to prevent this from happening? Autovacuum is turned on, but it seems to do nothing in this particular case.

Output of VACUUM VERBOSE:

username=# vacuum verbose status;
INFO:  vacuuming "public.status"
INFO:  index "status_pkey" now contains 3295731 row versions in 11704 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.01u sec elapsed 0.14 sec.
INFO:  "status": found 0 removable, 4472204 nonremovable row versions in 400557 out of 400558 pages
DETAIL:  4470176 dead row versions cannot be removed yet.
There were 23081 unused item pointers.
0 pages are entirely empty.
CPU 2.87s/0.70u sec elapsed 3.64 sec.
INFO:  vacuuming "pg_toast.pg_toast_16753"
INFO:  index "pg_toast_16753_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_16753": 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.
VACUUM

As you can see, there are 0 removable row versions, despite the table has only 2039 physical rows.

SELECT n_live_tup, n_dead_tup
from pg_stat_user_tables
where relname = 'status';

 n_live_tup | n_dead_tup
------------+------------
       2039 |    4473416

At that moment,

SELECT pg_size_pretty(pg_total_relation_size('status'))

reported a size of 3221 MB, for only 2039 rows in the table. That is hilarious.

0

Something is keeping autovacuum from removing those dead tuples.

These can be only three things that keep VACUUM from doing its job:

  1. A long running concurrent transaction. Check with

    SELECT pid, datname, usename, state, backend_xmin
    FROM pg_stat_activity
    WHERE backend_xmin IS NOT NULL
    ORDER BY age(backend_xmin) DESC;
    
  2. An abandoned replication slot. Check with

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;
    
  3. An orphaned prepared transaction. Check with

    SELECT gid, prepared, owner, database, transaction AS xmin
    FROM pg_prepared_xacts
    ORDER BY age(transaction) DESC;
    

But all that would also block VACUUM (FULL) from shrinking the table. So there must be something extra you are doing when you run VACUUM (FULL).

You have autovacuum enabled, right?

This is what you should do:

  1. Remove whatever is blocking VACUUM.

  2. Make sure autovacuum runs fast enough on that table:

    ALTER TABLE status SET (autovacuum_vacuum_cost_delay = 0);
    

If the data modifications are caused by UPDATE you could also try to get HOT updates. These remove the need for VACUUM. A prerequisite is that the updated column or columns are not indexed.

  • You're right! Something was blocking the VACUUM from running. I shutdown everything that used the database and then it worked, normal vacuum removed all the dead rows. I don't know what though, I ran your queries but the 1st one doesn't return me anything that uses this one table (only other queries that disappear over time), second and third don't return any results. – AJ Cole Oct 10 at 12:05
  • 1
    Then number 1 is your problem. It does not matter if the long running concurrent transaction uses the table or not, its mere existence blocks VACUUM: the transaction could use the table later on and need old row versions. Sessions that are idle in transaction are evil and your problem. Make sure that no transaction stays in that state, if necessary by using idle_in_transaction_sesion_timeout. – Laurenz Albe Oct 10 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.