Search code examples
postgresqllimittoastoid

What is the size limit of a TOAST table in PostgreSQL? 4 billion rows o 4 billions values of chunk_id?

in the wiki for PostgreSQL related to TOASTed tables (https://wiki.postgresql.org/wiki/TOAST) says that:

"You cannot have more than 2^32 (4 billion) out-of-line values in a single table, because there would have to be duplicated OIDs in its TOAST table."

What does it mean?

1) the TOAST table cannot have more than 4 billion rows? or

2) the TOAST table cannot have more than 4 billion distinct values of OIDs (values for column chunk_id)?

We have a toast table with 3.2 billion rows and wondering if we are close to the limits.

Thanks in advance

Solution

  • TOAST tables are defined like this:

    \d pg_toast.pg_toast_59238
TOAST table "pg_toast.pg_toast_59238"
   Column   |  Type   
------------+---------
 chunk_id   | oid
 chunk_seq  | integer
 chunk_data | bytea

    Here chunk_id is the identifier of a single toasted value, and chunk_seq is the index for the parts into which the toasted value has been split.

    Since there are only around 4 billion different unsigned 4-byte integers, and that is what the data type oid is, there can be only 4 billion toasted data in each database table.

    However, not each entry in a table gets toasted: only when the size of a table row exceeds 2000 bytes after compression, values are stored out of line.

    You can fund the TOAST table for your table:

    SELECT reltoastrelid
FROM pg_class
WHERE relname = 'mytable';

    Then you can find how many toasted entries there are:

    SELECT count(DISTINCT chunk_id)
FROM pg_toast.pg_toast_12345;

    Warning: That is an expensive query.