Re: Pluggable toaster

Hi hackers!

Pluggable TOAST API with catalog control table PG_TOASTREL - pre-patch.

Pluggable TOAST - TOAST API rework - introduce PG_TOASTREL catalog
relation containing TOAST dependencies. NOTE: here is a pre-patch, not
a final version, just to introduce another approach to a Pluggable TOAST
idea, it needs some cleanup, tests rework and some improvements, so
the main
goal of this message is to introduce this different approach. This is
the
last patch and it is installed on top of older TOAST API patches, so
here
are 3 patches attached:

0001_toaster_interface_v24.patch.gz
This patch introduces new custom TOAST pointer, Pluggable TOAST API and
Toaster support functions - cache, lookup, and new attribute
'atttoaster'
in PG_ATTRIBUTE table which stores Toaster OID;

0002_toaster_default_v24.patch.gz
Here the default TOAST mechanics is routed via TOAST API, but still
using
varatt_external TOAST Pointer - so this step does not change overall
TOAST
mechanics unless you plug in some custom Toaster;

0003_pg_toastrel_table_v24.patch.gz
Here Pluggable TOAST is reworked not to modify PG_ATTRIBUTE, instead
this
patch introduces new catalog table PG_TOASTREL with its support
functions.

Motivation: PG_ATTRIBUTE is already the largest catalog table. We try
to avoid modification of existing catalog tables, and previous solution
had several problems:
1) New field in PG_ATTRIBUTE;
2) No opportunity to save all Toaster assignment history;
3) No opportunity to have multi-TOAST tables assigned to a relation or
an attribute;
4) Toaster cannot be dropped - to drop Toaster we need to scan all
tables
with TOASTable columns.

Instead of extending PG_ATTRIBUTE with ATTTOASTER attribute, we decided
to store all Table-Toaster relations in a new catalog table PG_TOASTREL.
This cancels the necessity to modify catalog table PG_ATTRIBUTE, allows
to store
full history of Toasters assignments, and allows to drop unused Toasters
from system.

Toasters are assigned to a table column. ALTER TABLE ... SET TOASTER
command
creates a new row in PG_TOASTREL. To distinguish sequential assignments,
PG_TOASTREL has special attribute - 'version'. With each new assignment
its 'version' attribute is increased, and the row with the biggest
'version'
is the current Toaster for a column.

This approach allows to provide different behavior, even for a single
table
we can have one TOAST table for the whole relation (as it is in current
TOAST
mechanics), or we can have separate TOAST relation(s) for each TOASTable
column - this requires a slight modification if current approach. The
latter
also allows simple invariant of column-oriented storage.

Also, this approach makes PG_ATTRIBUTE attribute RELTOASTRELID obsolete
-
current mechanics allows only 1 TOAST table for relation, which limits
greatly TOAST capabilities - because all TOASTed columns are stored in
this
table, which in its turn limits overall base relation capacity.

In future, this approach allows us to have a kind of near infinite TOAST
storage, with ability to store large values (larger than 512 Mbytes),
auto-creation of TOAST table only when the first value is actually
TOASTed,
and much more.

The approach, along with the TOAST API itself, introduces the catalog
table
PG_TOASTREL with a set of support functions.

PG_TOASTREL definition:

postgres(at)postgres=# \d+ pg_toastrel;
Table
"pg_catalog.pg_toastrel"
Column | Type | Collation | Nullable | Default | Storage |
Toaster | Compression | Stats target | Description

-------------+----------+-----------+----------+---------+---------+---------+-------------+--------------+-------------
oid | oid | | not null | | plain |
| | |
toasteroid | oid | | not null | | plain |
| | |
relid | oid | | not null | | plain |
| | |
toastentid | oid | | not null | | plain |
| | |
attnum | smallint | | not null | | plain |
| | |
version | smallint | | not null | | plain |
| | |
relname | name | | not null | | plain |
| | |
toastentname | name | | not null | | plain |
| | |
flag | "char" | | not null | | plain |
| | |
toastoptions | "char" | | not null | | plain |
| | |
Indexes:
"pg_toastrel_oid_index" PRIMARY KEY, btree (oid)
"pg_toastrel_name_index" UNIQUE CONSTRAINT, btree (toasteroid, relid,
version, attnum)
"pg_toastrel_rel_index" btree (relid, attnum)
"pg_toastrel_tsr_index" btree (toasteroid)
Access method: heap
(This is not a final definition)

Where:
oid - PG_TOASTREL record ID
toasteroid - Toaster OID from PG_TOASTER
relid - base relation OID
toastentid - TOAST entity OID (not necessary to be a table)
attnum - TOASTable attribute index in base relation
version - Toaster assignment version - sequence of assignments
relname - base relation name (optional)
toastentname - TOAST entity name (optional)
flag - special field to mark rows, currently only the value 'x' is used
to mark unused rows

PG_TOASTREL unique key consists of:
toasteroid, relid, attnum, version

All currently assigned Toasters are additionally stored in cache for
fast access. When new row is being TOASTed - Toaster, relation Oid,
TOAST relation Oid, column index are added into Toastrel Cache for fast
access.

Create table, change Toaster, change column type were changed to
add new rows in PG_TOASTREL, to use this table and cache instead
of altering pg_attribute with new column. For table creation from
scratch when no TOAST tables were created is used special condition
with version=0.

DROP TABLE drops rows in PG_TOASTREL for this table. This allows to -
DROP TOASTER command added. When no rows with the according Toaster are
present in PG_TOASTREL - it is considered unused and thus could be
safely
dropped from the system.

Default toaster 'deftoaster' (reference TOAST mechanics) cannot be
dropped.

Working branch:
https://github.com/postgrespro/postgres/tree/toastapi_with_ctl

Would be glad to get any proposals and objections.

--
Regards,
Nikita Malakhov
Postgres Professional
https://postgrespro.ru/