The surprising impact of medium-size texts on PostgreSQL performance
1–10 of 17 posts
Re: The surprising impact of medium-size texts on PostgreSQL performance
#2Very interesting! I've spend many moons optimizing postgresql configs and queries, never ran across this before :)
Pretty sure I have some targets to try this on even
Re: The surprising impact of medium-size texts on PostgreSQL performance
#3There can be significant impact from off-page types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), and the deprecated TEXT/NTEXT/BINARY) in SQL Server, and presumably other DBMs, too.
Though one key difference is that SQL Server doesn't compress the off-page parts as this article states postgres does. In fact even if you have the table set to compress using either row or page compression option, off-page data is not compressed.
Re: The surprising impact of medium-size texts on PostgreSQL performance
#4This is why most databases have separate char/varchar and text types: char and varchar are stored inline, and text is stored externally.
PostgreSQL doesn't do this, there is no difference between any of the character types, they are all stored the same way, and the type only serves to validate the data. See: https://www.postgresql.org/docs/current/datatype-character.h...
MySQL even has TINYTEXT for when you want to store small strings outside the table (for performance). With tinytext only a single byte is stored in the table, and the rest externally, so I/O is reduced when doing a full table scan, if you don't need to read the tinytext column.
Re: The surprising impact of medium-size texts on PostgreSQL performance
#5There can be significant impact from off-page types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), and the deprecated TEXT/NTEXT/BINARY) in SQL Server, and presumably other DBMs, too. Though one key difference is that SQL Server doesn't compress the off-page parts as this article states postgres does. In fact even if you have the table set to compress using either row or page compression option, off-page data is not c…
The way PG handles this is better than SQL Server was back when I used that primarily (2008r2). The compression is not as amazing as it could be, but it is quite helpful regardless. It'll be nice when the work on the "pluggable compression" [1] (or similar) is committed, allowing the use of better compression algorithms like zstd.
[1]. https://www.postgresql.eu/events/pgconfeu2019/sessions/sessi...
Re: The surprising impact of medium-size texts on PostgreSQL performance
#6The article only briefly touches on this, but you can control various aspects of how postgres decides to store your value using `ALTER TABLE ... SET STORAGE` including the ability to make it inline, uncompressed, compressed, or TOASTED and also to change the threshold parameters to when postgres decides it for you. The documentation is about a 1/3 of the way down this page:
https://www.postgresql.org/docs/13/sql-altertable.html
This is useful if you have, for example, short character codes coming from some external system. The default will be EXTENDED (external, compressed) but in order to make the absolutely smallest stored tuple possible, if you know it's a short or fixed length, you can go MAIN (internal, compressed) or PLAIN (internal, uncompressed).
Re: The surprising impact of medium-size texts on PostgreSQL performance
#7I've mentioned this story here before, but one of the most surprising performance gains I saw was by eliminating TOAST look ups. If I recall correctly, each time you use the `->>` operator on a TOASTed JSONb column, the column will be deTOASTed. That means if you write a query like:
SELECT x ->> 'field1', x ->> 'field2', x ->> 'field3'
FROM table
My first attempt to fix this was to read the field in one query and use a subselect to pull out the individual fields. This attempt was thwarted by the Postgres optimizer which inlined the subquery and still resulted in deTOASTing the field multiple times.
After a discussion with the Postgres IRC, RhodiumToad pointed out that if I add OFFSET 0 to the end of the subquery, that will prevent Postgres from inlining it. After retrying that, I saw an order of magnitude improvement due to eliminating the redundant work.
Re: The surprising impact of medium-size texts on PostgreSQL performance
#8I've mentioned this story here before, but one of the most surprising performance gains I saw was by eliminating TOAST look ups. If I recall correctly, each time you use the `->>` operator on a TOASTed JSONb column, the column will be deTOASTed. That means if you write a query like: SELECT x ->> 'field1', x ->> 'field2', x ->> 'field3' FROM table and x is TOASTed, Postgres will deTOAST x three different times. This m…
This sounds like an interesting optimization possibility. Do you know will this be patched?
Re: The surprising impact of medium-size texts on PostgreSQL performance
#9I've mentioned this story here before, but one of the most surprising performance gains I saw was by eliminating TOAST look ups. If I recall correctly, each time you use the `->>` operator on a TOASTed JSONb column, the column will be deTOASTed. That means if you write a query like: SELECT x ->> 'field1', x ->> 'field2', x ->> 'field3' FROM table and x is TOASTed, Postgres will deTOAST x three different times. This m…
How do you find out that the optimiser inlined the subquery? Is this visible in the explain output?
Re: The surprising impact of medium-size texts on PostgreSQL performance
#10I've mentioned this story here before, but one of the most surprising performance gains I saw was by eliminating TOAST look ups. If I recall correctly, each time you use the `->>` operator on a TOASTed JSONb column, the column will be deTOASTed. That means if you write a query like: SELECT x ->> 'field1', x ->> 'field2', x ->> 'field3' FROM table and x is TOASTed, Postgres will deTOAST x three different times. This m…
How do you find out that the optimiser inlined the subquery? Is this visible in the explain output?
IIRC, EXPLAIN VERBOSE will show you the columns being selected by each step of the plan. The inlining showed up there.