To recap the main goals for Direct Toast
Why do this
-----------
# performance
- 5% - 25% for in-memory vector queries without
indexes for vector sizes where indexing isn't
possible
- 2x for simple in-memory queries where fetching
fetching toast is a significant part of the work
- 1.5x to 100x for cases where toast index does
not fit in memory the worst case would be a
full table with an out-of-order index that
doesn't fit in memory where each toasted field
causes an extra disk access for index.
Assuming 1 ms for that the extra disk access
it adds 46 days to the full scan.
I have not seen a dump or copy operation that long,
but I have seen one taking over a week.
Chris Travers had some anecdotal evidence of
a case where sequential scans degraded to
single-digit rows per second rows per second
on a database with large external SCSI arrays
# no running out of OIDs at 4B
- added bonus - not slowing down finding scarce
free oids when close to 4B
# space savings
- if your toasted fields are small
the index space usage can make up a significant
portion of the data size
Migration
---------
As direct toast just adds one more VARATT pointer type
and does not change anything else it is fully backwards
compatible. The toast table format change is also backwards
compatible as it adds a field at the end of the tuple
VACUUM FULL needs to be modified to refuse to work on toast tables.
CLUSTER already refuses as now the toast index is partial
VACUUM FULL and CLUSTER need to get an option to simultaneously
also rewrite toast table in main table row order.
