toastinfo

Overview

Version

Build

You can build the RPM packages for toastinfo using pig build:

pig build pkg toastinfo         # build RPM packages

Install

You can install toastinfo directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install toastinfo;          # Install for current active PG version

pig ext install -y toastinfo -v 18  # PG 18
pig ext install -y toastinfo -v 17  # PG 17
pig ext install -y toastinfo -v 16  # PG 16
pig ext install -y toastinfo -v 15  # PG 15
pig ext install -y toastinfo -v 14  # PG 14

dnf install -y toastinfo_18       # PG 18
dnf install -y toastinfo_17       # PG 17
dnf install -y toastinfo_16       # PG 16
dnf install -y toastinfo_15       # PG 15
dnf install -y toastinfo_14       # PG 14

apt install -y postgresql-18-toastinfo   # PG 18
apt install -y postgresql-17-toastinfo   # PG 17
apt install -y postgresql-16-toastinfo   # PG 16
apt install -y postgresql-15-toastinfo   # PG 15
apt install -y postgresql-14-toastinfo   # PG 14

Create Extension:

CREATE EXTENSION toastinfo;

Usage

Sources: upstream README, upstream tags.

toastinfo exposes how PostgreSQL stores variable-length (varlena) values, including inline, compressed, and out-of-line TOAST storage forms.

Functions

pg_toastinfo(anyelement) describes the storage form of a datum:

CREATE EXTENSION toastinfo;

SELECT a, length(b), pg_column_size(b), pg_toastinfo(b), pg_toastpointer(b)
FROM t;

Possible storage forms include:

• null, for NULL values.
• ordinary, for non-varlena data types.
• short inline varlena, up to 126 bytes with a 1-byte header.
• long inline varlena, uncompressed, up to 1 GiB with a 4-byte header.
• long inline varlena, compressed (pglz|lz4).
• toasted varlena, uncompressed.
• toasted varlena, compressed (pglz|lz4).

Compressed varlenas show the compression method on PostgreSQL 14+.

pg_toastpointer(anyelement) returns the TOAST table chunk_id OID for out-of-line toasted values, or NULL for non-toasted input:

SELECT pg_toastpointer(large_column)
FROM my_table;

Storage Example

CREATE TABLE t (a text, b text);

ALTER TABLE t ALTER COLUMN b SET STORAGE EXTERNAL;
INSERT INTO t VALUES ('external-10000', repeat('x', 10000));

ALTER TABLE t ALTER COLUMN b SET STORAGE EXTENDED;
INSERT INTO t VALUES ('extended-1000000', repeat('x', 1000000));

ALTER TABLE t ALTER COLUMN b SET COMPRESSION lz4;
INSERT INTO t VALUES ('extended-lz4', repeat('x', 1000000));

SELECT a, pg_column_size(b), pg_toastinfo(b), pg_toastpointer(b)
FROM t;

Caveats

• Pigsty metadata carries toastinfo 1.6 for PostgreSQL 14-18, with Pigsty RPMs and PGDG DEBs.
• The upstream GitHub README documents the same SQL surface, but the public GitHub tags visible during review stop at v1.5; no upstream 1.6 changelog was found in that repository.
• pg_toastpointer is meaningful only for out-of-line toasted data; ordinary, inline, or NULL values return NULL.