9 ms·
The surprising impact of medium-size texts on PostgreSQL performance (2020)
- hackyhacky 3y agotl;dr: PostreSQL stores small values directly in the table, but has to store large values outside of the table by itself, and the table merely contains a reference to the external storage. In either case, the table itself is small, and performance is fast. However, if you store data that is just under the size threshold for moving outside of the table, the table gets large, and performance degrades. Thus, the tl;dr of the the tl;dr: small and large TEXT values are faster than medium-sized TEXT values.
- paulddraper 3y ago...for some definition of faster
- junon 3y agoObjectively slower than large/small strings. I don't think it requires that much nuance to understand the point.
- MobiusHorizons 3y agoOnly faster for queries that don’t care about the value of the text, and do a full table scan instead of reading from the index
- troupo 3y agoOr when you use the index, but still have to do a lot of IO.
- paulddraper 3y ago*IF* your workload ignores the value of the string. That's the nuance. From the article: > We executed a query against all three tables to search for a string within the text value...This time, the results are more consistent with what we would expect. [Large is slower than medium.] --- I.E. This isn't a "bug" ... this is a performance tradeoff. Transitioning values from inline to a separate table has performance positives and negatives. This article highlights a performance positive from TOAST once the toast_tuple_target boundary is crossed.
- derefr 3y agoThis is a somewhat-artificial case, though. In most cases I've seen in practice, even when you're using the value of the string, you're also filtering by some non-text columns, where the compactness of the TOASTed table allows you to do the filtering step on the left side of the implicit TOAST-join much faster (and keep more of the left table hot in memory); so that even despite needing to implicitly join in the TOAST records, you're only joining them in for the filtered rowset — usually a win overall.
- paulddraper 3y agoIn most cases I've seen in practice, you're querying against an index. This drastically reduces the impact of unused fields compared to a full table scan.
- deleted 3y ago[deleted]
- deleted 3y ago[deleted]
- cmrdporcupine 3y ago'tis why some newer DB storage systems support variable sized pages. https://db.in.tum.de/~freitag/papers/p29-neumann-cidr20.pdf https://db.in.tum.de/~freitag/papers/p29-neumann-cidr20.pdf
- jabart 3y agoMicrosoft SQL Server does this as well with [n]varchar(max) as any size that exceeds a data pages 8k size goes off to a special different data page. tl;dr; CPU cache sizes are small and text is large. Using numeric fields will always be faster. "hackyhacky" is equal to 1.25 Int64s.
- archit3cture 3y agoThis comment does not address the content of the article. I would be interested to know what tool is used to generate the svg table figures.
- high_priest 3y agoLooks like they are hand drawn
- johndough 3y agoThe tables look like they have been created with https://excalidraw.com/ https://excalidraw.com/ with "sloppyness" option set to high and pointy corners activated. EDIT: Now confirmed by author https://news.ycombinator.com/item?id=37323895 https://news.ycombinator.com/item?id=37323895
- haki 3y agoI used excalidraw.com
- cxcorp 3y agoIt looks similar to the style you get from draw.io if you choose "Sketch" as the style for an object (add box -> "Style" from right sidebar -> Sketch).
- deleted 3y ago[deleted]
- pjungwir 3y agoI remember when working with Oracle 20 years ago the DBA liked to make sidecar tables to hold the big columns, so they were out of the way of most queries. It was awkward but I guess it was worth it. One time after starting to use Postgres I almost did the same thing, but then I realized the database was already doing it for me. TOAST really is the greatest thing since sliced bread! If you made it to the end of the article, you saw the author recommends to consider making these sidecar tables by hand if you have medium-size texts. I think I would try very hard to tune TOAST before going that far. Also note that changing the table storage parameters doesn't automatically re-write the existing data. You need to re-store the data to make that happen. I believe `UPDATE t SET foo = foo` would do it, but I haven't tested. The 8kB page size also matters when you use Postgres as a vector database, e.g. with pgvector. Jonathan Katz gave a great lightning talk about this a few months ago at PGCon. Vectors these days can have a lot of parameters, so they are big. But since the whole point is to query against the vector, you don't really want it TOASTed. I believe he has been working on some tricks to solve that problem.
- paulddraper 3y agoSee also https://news.ycombinator.com/item?id=24836979 https://news.ycombinator.com/item?id=24836979
- dang 3y agoThanks! Macroexpanded: The surprising impact of medium-size texts on PostgreSQL performance - https://news.ycombinator.com/item?id=24836979 https://news.ycombinator.com/item?id=24836979 - Oct 2020 (16 comments)
- derefr 3y agoIs anyone here aware of a low-level approach (like TOAST is low-level) for "string interning" in Postgres tables? Not quite like the PG enum type, as there is an unbounded set of values, potentially millions of such. But each value may also have potentially millions of duplicates (usually with a power-law distribution), with the size of the text well-worth deduplicating. We currently do string-interning at the application level, by: 1. creating an explicit table of strings to join with, with an auto-increment pkey and a unique index on the string; 2. "registering" newly-discovered strings in the data, by inserting into this strings table with ON CONFLICT DO NOTHING; 3. then querying back for the string IDs, loading these into a local in-memory cache, and using the cache to map strings to their string IDs; 4. then actually inserting the data, where all strings have been mapped to string IDs. It really feels like something PG should be doing itself (like TOAST!) But so far I haven't seen any extension that attempts to solve this problem.
- paulddraper 3y agoNo. Nor have I seen any other DBMS offer such a feature. Your application-based approach is the way you have to do it.
- ddorian43 3y agoColumar stores should do this at a minimum (inside 1 block though, so, say, in 100k rows block)
- mgkimsal 3y agoBenefits are... ? For text searching? I was getting shades of Lucene reading what you were describing, but I may be way off of what your intent was.
- paulddraper 3y agoPerhaps space-efficient "tagged" data.
- derefr 3y agoOur "text" isn't actually text, but rather 32-byte bytea values (SHA256 hashes). (This is just a very close analogy, not what we're actually doing:) imagine a data warehouse for relationally querying IPLD (https://ipld.io/ https://ipld.io/) -hypermedia-document-shaped data, where every piece of data has embedded "foreign keys" to other data, that come in the form of content hashes (really IPFS CIDs, but you can strip that down to just a hash for internal use.) An average modelled-document-type table will have 3-12 of these foreign keys as toplevel columns; and these columns will have various indices on them, to allow the various tables of this data to be efficiently joined through these keys. And there will be billions of records in each of these modelled-document-type tables. We still need the content hashes — we can't just swap them out for BIGSERIAL values at ingest time and drop the original content-hash values — because people query the data by content-hash, and also expect content-hash references to other documents to appear in query results. Throwing all the content hashes into a big "interned strings" table, and then replacing them with content-hash-IDs in the regular tables, saves tons of space (and thereby makes much more of the regular tables fit into memory at once.) And as we can join content-hash-ID foreign-keys to content-hash-ID primary-keys, we only ever need to involve the "interned strings" table 1. as an initial lookup to find the first document to work with, and 2. to translate any content-hash-IDs in the result back to content hashes. (If you think about it carefully, while "string interning" might be the strategy used, the problem is more like "keeping a bijective mapping between large expensive globally-unique public identifiers, and cheap small per-shard-unique private identifiers, where translation occurs between global and local identifiers at the system API boundary.")
- hankchinaski 3y agosomewhat related is the use and abuse of jsonb columns in postgres, these also get stored in toast tables and cause a lot of performance issues as opposed to when being stored in actual tables and columns (not json)
- haki 3y agoIf you store large jsonb values in the database, storing them out of line may be a good thing - for example, you can query metadata much faster. Also, worth mentioning that jsonb is only benefitial when you plan on indexing/manipulating the data in the database. Otherwise, using json type may be a better choice (smaller size, stored as text, parsed on demand)
- throwmeaway2232 3y agoI was pretty confused by the index performance when getting ~50% of the table. I think what's happening is that since the tuples in the medium table are larger, less are stored on each page. Just looking at the sizes of the tables: medium: 977 MB Large: 25 MB we can assume that the medium query had to access 39.08 ( 977 / 25 ) * more pages. medium time: 3198.539 large time: 85.74 3198.539 / 85.74 = 37 which seems to track. What was confusing me is that I thought that the io would take longer for the large text objects because there's more of it. I think that the reason why this isn't the case is because TOAST is probably optimised to make fewer calls to io, but they tend to be much larger. it's not necessarily the size of the data, but rather how many syscalls you have to make to request the data. pretty unsure about this, but it seems to make sense now.
- deleted 3y ago[deleted]
- h1fra 3y agoInteresting I wasn't aware of this system to side-car content. I will definitely check. But it makes storing long logs and content in DB an even better choice. (unless you need a CDN ofc)