Menu
I have an autovacuum process running on pg_toast:
select query, from pg_stat_activity where query like '%autov%';
"autovacuum: VACUUM pg_toast.pg_toast_15404513 "
How do I find out what table/index/whatever this pg_toast pertains to? Or is the autovacuum working on something else?
729
The Autovacuum Daemon. PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands. When enabled, autovacuum checks for tables that have had a large number of inserted, updated or deleted tuples.
Controls whether the server should run the autovacuum launcher daemon. This is on by default; however, track_counts must also be enabled for autovacuum to work. This parameter can only be set in the postgresql.
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.
Autovacuum does take a lock on the table, but it is a weak lock which does not interfere with normal operations (SELECT, UPDATE, DELETE) but will interfere with things like adding indexes, or truncating the table.
You can check the associated toast table in pg_class using the following query: The table name will be pg_toast_$ (OID) where oid is the toast table oid, reltoastrelid of original table. The toast table is in pg_toast schema, so to query you need to use: The above query will print the toasted chunk_id, chunk_seq, chunk_data, if present.
The AUTOVACUUM section in the postgresql.conf file. autovacuum: It is set to ‘on’ by default so it may not be declared exclusively in the shell or terminal. autovacuum_naptime: This parameter is set to 1min or 60s which indicates the duration between consecutive autovacuum calls or wakeups.
TOAST This section provides an overview of TOAST (The Oversized-Attribute Storage Technique). PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly.
If any of the columns of a table are TOAST -able, the table will have an associated TOAST table, whose OID is stored in the table's pg_class. reltoastrelid entry. On-disk TOAST ed values are kept in the TOAST table, as described in more detail below.
Here's a shorter way:
select 15404513::regclass;
where 15404513 is pg_toast_ suffix.
59
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
