Start with the show database bloat sample query on the PostgreSQL wiki if you're investigating possible table/index bloat issues.
Also check whether autovacuum is enabled. Some people misguidedly turn it down or off because they see it creating load; they should actually be turning it up in these situations.
Answer from Craig Ringer on Stack ExchangeStart with the show database bloat sample query on the PostgreSQL wiki if you're investigating possible table/index bloat issues.
Also check whether autovacuum is enabled. Some people misguidedly turn it down or off because they see it creating load; they should actually be turning it up in these situations.
If you are administrator/developer of the database, some symptoms are when you have huge (>1m rows) tables with temp data that are regularly deleted (like deleting the entries for the last month) and the database/table size does not change.
I'm using these queries to check the table size:
/***********************
* DB schema sizes
*************************/
SELECT pg_size_pretty(sum(pg_relation_size(pg_class.oid))::bigint), nspname,
CASE pg_class.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 't' THEN 'toast' ELSE pg_class.relkind::text END
FROM pg_class
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
GROUP BY pg_class.relkind, nspname
ORDER BY sum(pg_relation_size(pg_class.oid)) DESC;
/***********************
* DB table sizes
*************************/
SELECT pg_size_pretty(pg_relation_size(pg_class.oid)), pg_class.relname, pg_namespace.nspname,
CASE pg_class.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 't' THEN 'TOAST' ELSE pg_class.relkind::text END
FROM pg_class
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
ORDER BY pg_relation_size(pg_class.oid) DESC;
This is not 100% relevant to your question but can give you a start.
postgresql - How to find tables that require VACUUM FULL? - Stack Overflow
PostgreSQL - Query to determine which tables are currently being vacuumed? - Stack Overflow
Is it common to need to do regular full vacuum on tables?
optimization - how to automatically determine which tables need a vacuum / reindex in postgresql - Stack Overflow
You can use a lateral join to the function call, from a list of tables.
pg_stat_user_tables provides a handy list of tables, but you might want something else.
select schemaname, relname, pgstattuple.* from pg_stat_user_tables, pgstattuple(relid) order by dead_tuple_percent desc;
But, it is doubtful this is useful. For one thing, dead_tuple_percent is adequately dealt with by a regular (non-FULL) vacuum, so sorting on it with the intention of driving VACUUM FULL doesn't make sense. It might more sense to use free_space or free_percent. But even that is questionable. Why squeeze out the freespace, if you are just going to create it again soon anyway? There is no point in doing that unless the bloat is actually a problem, and until you think you fixed the problem which created the bloat in the first place, which there is no automated tool for.
You can use pg_stat_user_tables instead:
select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
from pg_stat_user_tables
order by n_dead_tup desc;
This problem can easily be solved with system catalogs. I suggest to join on pg_locks since autovacuum acquires a ShareUpdateExclusiveLock lock on the table it is working on, to avoid some manual parsing of the query from pg_stat_activity.
The following query lists the tables being auto-vacuumed, solving the pg_toast reference if a toast table is being vacuumed, as explained in Postgres pg_toast in autovacuum - which table? question linked to by @Zeki.
SELECT n.nspname || '.' || c.relname
FROM pg_namespace n, pg_stat_activity a, pg_locks l, pg_class c
WHERE
a.query LIKE 'autovacuum: %'
AND l.pid = a.pid
AND l.mode = 'ShareUpdateExclusiveLock'
AND (c.oid = l.relation OR c.reltoastrelid = l.relation)
AND n.oid = c.relnamespace
AND n.nspname <> 'pg_toast';
Please note that while pg_stat_activity and pg_locks catalogs are shared across databases, this query will only list the tables being auto-vacuumed in the current database as pg_relation is not a shared catalog.
In instead of finding if the tables are being vacuumed turn auto vacuum off for the involved tables:
alter table table_name_pattern
set (
autovacuum_enabled = false,
toast.autovacuum_enabled = false
);
table pattern is a glob pattern like tbl*. At the end of the query turn auto vacuum back on
alter table table_name_pattern
set (
autovacuum_enabled = true,
toast.autovacuum_enabled = true
);
Edit in response to the commentaries:
The query to find if the involved tables are being vacuumed is unnecessary and useless. If it is known that one or more of the involved tables are being vacuumed what is it supposed to be done? Wait and keep repeating the inquiring query until none is being vacuumed? And when none is then start the long query just to discover after a while that auto vacuum was just kicked off again? There is no point. Why not just turn auto vacuum off and avoid all the hassle?
There is no ethical superiority in doing it the hard way, especially if the hard way is going to give worse results than the simple one. Simpler code is simpler to use and understand but it is not necessarily easier to build. Many times it is the opposite, requiring more intellectual effort or preparedness then the complex one.
If the autovacuum setting is altered inside a transaction and that transaction is rolled back the setting will be back to whatever it was before the transaction start
drop table if exists t;
create table t (id int);
begin;
alter table t
set (
autovacuum_enabled = false,
toast.autovacuum_enabled = false
);
\d+ t
Table "public.t"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | | plain | |
Has OIDs: no
Options: autovacuum_enabled=false
rollback;
\d+ t
Table "public.t"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | | plain | |
Has OIDs: no
But that setting inside the transaction will not be seen outside of the transaction so I guess autovacuum will still run. If this is true than the setting must be done outside of the transaction and controlled by a job that will turn it back regardless of what happens with the long running query.
After an incident just a week ago with a large table that refused to use an index (full vacuum solved that), ran into a problem where a 130k row table was having drastically different responses based on the value of the query against an indexed column (as long as 2 seconds depending on the value, vs. 002 for others). This time we did a full vacuum on every table and performance is better through.
And now I'm hearing this may be commonly necessary. Is that true? The standard vacuum doesn't help but full locks the table so everything needs to be shut down. Just seems odd to me that a database should require this kind maintenance
It sounds like you are trying to re-invent auto-vacuum. Any reason you can't just enable that and let it's do it's job?
For the actual information you want, look at pg_stat_all_tables and pg_stat_all_indexes.
For a good example of how to use the data in it, look at the source for auto-vacuum. It doesn't query the views directly, but it uses that information.
I think you really should consider auto-vacuum.
However, if i did understood right your needs, that's what i'll do:
- For every table (how many tables do you have?) define the criterias;
For example, talbe 'foo' need to be reindex every X new records and vacuum every X update, delete or insert
- Write out your own application to do that.
Every day it check the tables status, save it in a log (to compare the rows difference over the time), and then reindex/vacuum the tables whose match yours criterias.
Sounds a little hacking, but i think is a good way to do an custom-autovacuum-with-custom-'triggers'-criteria
PostgreSQL 9.3
Determine if Autovacuum is Running
This is specific to Postgres 9.3 on UNIX. For Windows, see this question.
Query Postgres System Table
SELECT
schemaname, relname,
last_vacuum, last_autovacuum,
vacuum_count, autovacuum_count -- not available on 9.0 and earlier
FROM pg_stat_user_tables;
Grep System Process Status
$ ps -axww | grep autovacuum
24352 ?? Ss 1:05.33 postgres: autovacuum launcher process (postgres)
Grep Postgres Log
# grep autovacuum /var/log/postgresql
LOG: autovacuum launcher started
LOG: autovacuum launcher shutting down
If you want to know more about the autovacuum activity, set log_min_messages to DEBUG1..DEBUG5. The SQL command VACUUM VERBOSE will output information at log level INFO.
Regarding the Autovacuum Daemon, the Posgres docs state:
In the default configuration, autovacuuming is enabled and the related configuration parameters are appropriately set.
See Also:
- http://www.postgresql.org/docs/current/static/routine-vacuuming.html
- http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html
I'm using:
select count(*) from pg_stat_activity where query like 'autovacuum:%';
in collectd to know how many autovacuum are running concurrently.
You may need to create a security function like this:
CREATE OR REPLACE FUNCTION public.pg_autovacuum_count() RETURNS bigint
AS 'select count(*) from pg_stat_activity where query like ''autovacuum:%'';'
LANGUAGE SQL
STABLE
SECURITY DEFINER;
and call that from collectd.
In earlier Postgres, "query" was "current_query" so change it according to what works.