PostgreSQL Speed up VACUUM — Part 2

(TOAST impact on VACUUM)

In part-1, we explored various database parameters, table-level configurations, and potential future enhancements to optimize VACUUM performance. Let’s delve deeper into this topic and examine how the underlying table storage structure influences VACUUM efficiency.

This post primarily focuses on PostgreSQL’s TOAST (The Oversized-Attribute Storage Technique). If you’re unfamiliar with TOAST, I recommend reviewing the official PostgreSQL documentation for a comprehensive overview. In essence, my understanding of this feature is as follows: “divide a single large row into multiple chunked rows and store them in multiple tables (main table and a TOAST table) rather than storing in a single table. Then also have a Foreign Key kind of relationship to join two tables data together to produce a combine tuple”.

So let’s quickly create test datasets and observe how storage parameters can make a difference on vacuum job.

###Table1 with extended storage (out of line storage)
create table table_extended (id int, age int, name text);
ALTER TABLE table_extended SET (
autovacuum_enabled = false, toast.autovacuum_enabled = false);
###Table2 with main storage (in line storage)
create table table_main (id int, age int, name text);
alter table table_main alter column name set storage main;
ALTER TABLE table_main SET (
autovacuum_enabled = false, toast.autovacuum_enabled = false);

I have loaded both the table with a wider “name” column data so TOAST feature will get triggered to decide compression/out of line storage.

The TOAST management code is triggered only when a row value to be stored in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB).

###Loaded same amount of data in both the tables and then checked ##the size
SELECT
    c1.relname,
    pg_size_pretty(pg_relation_size(c1.relname::regclass)) AS size,
    c2.relname AS toast_relname,
    pg_size_pretty(pg_relation_size(('pg_toast.' || c2.relname)::regclass)) AS toast_size
FROM
    pg_class c1
    JOIN pg_class c2 ON c1.reltoastrelid = c2.oid
WHERE
    c1.relname LIKE 'table_%'
    AND c1.relkind = 'r';
relname     |  size   |  toast_relname   | toast_size
----------------+---------+------------------+------------
 table_extended | 29 MB   | pg_toast_2116928 | 1302 MB
 table_main     | 1302 MB | pg_toast_2116934 | 0 bytes

So looking at table size above, we see tables are of similar size but the difference here is out of line storage for table_extended and in line storage for table_main.

Now let’s update these tables and make everyone 5 years older.

###cleaned up database and OS cache before executing UPDATEs
postgres=# update table_extended set age=age+5;
UPDATE 500000
Time: 2061.278 ms (00:02.061)
postgres=# update table_main set age=age+5;
UPDATE 500000
Time: 80259.084 ms (01:20.259)
relname     |  size   |  toast_relname   | toast_size
----------------+---------+------------------+------------
 table_extended | 57 MB   | pg_toast_2616940 | 1302 MB
 table_main     | 2604 MB | pg_toast_2616946 | 0 bytes

Since our updates only affect the age column and not the TOASTed name column, all modifications in table_extended are confined to the main table. The TOAST table remains untouched because a new tuple version is created solely for the id and age columns. Conversely, table_main doubles in size because it generates a new version of the entire tuple, including id, age, and name. This difference in storage behavior naturally leads to a noticeable disparity in VACUUM runtime, as expected based on the resulting table sizes. UPDATE query execution time difference is also there which is obvious looking at the resultant table size. We can also see the number of dead tuples for the main tables and toast tables in the pg_stat_all_tables.

During an UPDATE operation, values of unchanged fields are normally preserved as-is; so an UPDATE of a row with out-of-line values incurs no TOAST costs if none of the out-of-line values change.

Now I have run vacuum on both the tables to cleanup dead tuples and see a clear difference in the run time which also make sense because of difference in main table size.

###cleaned up DB and OS cache
postgres=# vacuum table_extended;
VACUUM
Time: 14500.174 ms (00:14.500)
postgres=# vacuum table_main;
VACUUM
Time: 42836.927 ms (00:42.837)

It’s crucial to understand that regular VACUUM operations target both the main table and the TOAST table, depending on the presence of dead tuples in either or both. However, the main table and the TOAST table maintain separate visibility information (xmin, xmax, and visibility map). Consequently, anti-wraparound VACUUM needs to freeze rows in both storage areas of a single table, potentially increasing the overall workload.

I initiated a thread in hacker emailing to understand the need for a separate visibility info for a TOAST table and there are nice reasons for having that.

Summary

While the previous examples illustrated the extremes of best-case and worst-case scenarios. It often employs compression and utilizes out-of-line storage for wider columns, optimizing space usage effectively. However, understanding these storage nuances empowers you to exert greater control in specific edge cases where fine-tuning might be necessary.

There are two ways to control the in line/out of line (TOAST) storage:

1. alter the columns to set appropriate storage
  
alter table table_extended alter column name set storage   extended/main/plain/external;
2. set parameter toast_tuple_target
    
create table table_main (id int, age int, name text) WITH   (toast_tuple_target=128);

So is it that simple to have a TOAST table every time (out of line storage)? Apart from VACUUM job considerations there are other considerations having a TOAST table storage along with the main table.

When selecting only a few columns that reside entirely within the main table, the TOAST approach can offer significant performance advantages. Since the query only needs to scan the necessary columns in the main table, the wider rows stored in the TOAST table remain untouched, resulting in faster retrieval times.

ID, AGE --> Main Table, NAME --> TOAST TABLE
SELECT ID,AGE from <TABLE>;

Conversely, when retrieving all columns from a table with TOASTed attributes, there might be a performance overhead. This is because the database needs to join the main table with the TOAST table to reconstruct the complete tuples before returning the results.

ID, AGE --> Main Table, NAME --> TOAST TABLE
SELECT ID,AGE,NAME from <TABLE>;

Parallel Vacuum Job:

In Postgres13 Index vacuum/cleanup happens in parallel manner and that is with one worker per Index.

During my exploration of TOAST tables, I discovered that PostgreSQL allows independent VACUUM operations for the main table and its associated TOAST table. Autovacuum seems to trigger these jobs based on individual table statistics, such as dead tuple thresholds and transaction ID age. However, when manually executing VACUUM on a table, it processes the main table first and then the TOAST table sequentially.

So why not run parallel vacuum on main and TOAST table in case of manual vacuuming.

relname     |  size   |  toast_relname   | toast_size
----------------+---------+------------------+------------
 table_extended | 1235 MB | pg_toast_2616940 | 3986 MB
relname     | n_live_tup | n_dead_tup
----------------+------------+------------
 table_extended |     503443 |   21005650
relname      | n_live_tup | n_dead_tup
------------------+------------+------------
 pg_toast_2616940 |    1000000 |    2061080

Note that TOAST table row count can be different compared to main table as a wider row is split into multiple rows in TOAST table.

Session1: first run vacuum on TOAST table.
vacuum (verbose,freeze) pg_toast.pg_toast_2616940;
Session2: Then run vacuum on main table.
vacuum (verbose, freeze, PROCESS_TOAST false) table_extended;