Why are pg_toast tables REINDEXED when doing a REINDEX DATABASE?

Question

As part of an upgrade I decided to REINDEX my database using REINDEX (VERBOSE, CONCURRENTLY) DATABASE <databasename>.

The log shows:

INFO: index "datastore.ds_sgmt_vg_partner_por_idx_a" was reindexed
INFO: index "datastore.ds_sgmt_vg_partner_por_idx_sc" was reindexed
INFO: index "pg_toast.pg_toast_2418155_index" was reindexed
INFO: table "datastore.ds_sgmt_vg_partner_portal_users" was reindexed
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s.

I expected line 1,2 & 4, but was a bit surpised by line 3. I'll have to admit that I do not exactly know what pg_toast does (or where the name comes from).

Is it a good thing that these indexes are rebuilt too?

Is it a lucky good thing that I decided to try REINDEX DATABASE in stead of REINDEXING each individual INDEX?

Answer 1

So much for our LLMs.... :

A pg_toast index is a type of index in PostgreSQL that is used for storing large or out-of-line data associated with a table. It is part of the TOAST (The Oversized-Attribute Storage Technique) mechanism in PostgreSQL, which allows for efficient storage and retrieval of large data values that exceed the maximum size of a database page.

When a column in a table contains large data values, such as text, binary, or large objects (BLOBs), PostgreSQL automatically stores these values in a separate TOAST table. The TOAST table is associated with the original table and is used to store the large data values in smaller chunks or "toast chunks". The pg_toast index is used to efficiently locate and retrieve these toast chunks when needed.

The pg_toast index is created automatically by PostgreSQL when a table with large data values is created. It is used internally by the database system and is not directly accessible or modifiable by users.

It's worth noting that the size of the pg_toast table and index can grow significantly if a table contains a large amount of data or if there are many large data values stored in the table. In such cases, it may be necessary to manage the size of the pg_toast table and index to optimize database performance and storage utilization.