Does TOAST really compress the complete row?
I am confused about one claim in this blog post: https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-binary-objects
All columns that come after data > 2000 bytes participate in The
Large Attribute Strorage Technique (TOAST). This storage is for the
row, not the column. Your id column comes as the last column in the
table? Whoopsie, your primary key just got shoved into blob storage
I always was under the impression that TOASTing only happens on column level, not on row level.
The manual does not mention anything about the whole row being TOASTed if one column exceeds the threshold.
Can someone clarify please?
Thomas
Thomas Kellerer <shammat@gmx.net> writes:
I am confused about one claim in this blog post: https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-binary-objects
All columns that come after data > 2000 bytes participate in The
Large Attribute Strorage Technique (TOAST). This storage is for the
row, not the column. Your id column comes as the last column in the
table? Whoopsie, your primary key just got shoved into blob storage
I always was under the impression that TOASTing only happens on column level, not on row level.
You're right, and the quoted text is wrong. Not only does TOAST compress
fields not whole rows, but it selectively targets wider fields first.
If your pkey is getting toasted, you should likely rethink your choice
of pkey. (Or, possibly, you just have so many fields there's no choice
but to compress all of them. Then it might be time for a table redesign.)
The decision-making about this is concentrated in
heap_toast_insert_or_update, which can be seen here:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/heaptoast.c
regards, tom lane
Another thing that was said I wasn't aware of and have not been able to
find any evidence to support:
Show quoted text
10. Blobs don’t participate in Logical replication.
On 7/2/20 4:29 PM, Adam Brusselback wrote:
Another thing that was said I wasn't aware of and have not been able to
find any evidence to support:
https://www.postgresql.org/docs/12/logical-replication-restrictions.html
"Large objects (see Chapter 34) are not replicated. There is no
workaround for that, other than storing data in normal tables."
Of course that does not apply to bytea:
https://www.postgresql.org/docs/12/datatype-binary.html
10. Blobs don’t participate in Logical replication.
--
Adrian Klaver
adrian.klaver@aklaver.com
https://www.postgresql.org/docs/12/logical-replication-restrictions.html
"Large objects (see Chapter 34) are not replicated. There is no
workaround for that, other than storing data in normal tables."
Of course that does not apply to bytea:
That makes sense now, I was reading that section as if it were talking
about bytea, not LO.
Thanks for pointing that out!
- Adam