[HN Gopher] The surprising impact of medium-size texts on Postgr...
___________________________________________________________________
The surprising impact of medium-size texts on PostgreSQL
performance (2020)
Author : skadamat
Score : 81 points
Date : 2023-08-30 13:42 UTC (9 hours ago)
(HTM) web link (hakibenita.com)
(TXT) w3m dump (hakibenita.com)
| hankchinaski wrote:
| somewhat 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 wrote:
| If 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)
| derefr wrote:
| Is 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 wrote:
| No. Nor have I seen any other DBMS offer such a feature.
|
| Your application-based approach is the way you have to do it.
| ddorian43 wrote:
| Columar stores should do this at a minimum (inside 1 block
| though, so, say, in 100k rows block)
| mgkimsal wrote:
| Benefits 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 wrote:
| Perhaps space-efficient "tagged" data.
| derefr wrote:
| Our "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/) -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.")
| indigo945 wrote:
| Well, hashes aren't strings, they are binary blobs often
| represented as a hex string. Storing them as bytea may give
| better performance than dropping them all into a humongous
| table, even though it wastes slightly more disk space (if
| values indeed repeat that often).
| derefr wrote:
| I'm not sure you read what I wrote correctly. The
| _technique_ is called "string interning" regardless of
| what exactly you're interning. In our case, we have a
| table assigning IDs to 32-byte bytea values.
|
| (Also, to be pedantic, a Postgres BYTEA _is_ a string; it
| 's just what a programming language would call a "raw
| string" -- i.e. a string of bytes. Postgres TEXT,
| meanwhile, is a string _of characters_ , required to be
| valid in a given character encoding. The PG TEXT and
| BYTEA types are the same ADT with the same set of
| applicable operations. Of course, this belies the name
| "BYTEA" [i.e. "byte array"] -- it's definitely _not_ an
| array ADT of any kind. If it were, then you 'd be able to
| use PG's array operations on it!)
| paulddraper wrote:
| See also https://news.ycombinator.com/item?id=24836979
| dang wrote:
| Thanks! Macroexpanded:
|
| _The surprising impact of medium-size texts on PostgreSQL
| performance_ - https://news.ycombinator.com/item?id=24836979 -
| Oct 2020 (16 comments)
| pjungwir wrote:
| I 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.
| [deleted]
| h1fra wrote:
| Interesting 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)
| hackyhacky wrote:
| tl;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 wrote:
| ...for some definition of faster
| junon wrote:
| Objectively slower than large/small strings. I don't think it
| requires that much nuance to understand the point.
| paulddraper wrote:
| *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 wrote:
| This 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 wrote:
| In 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.
| MobiusHorizons wrote:
| Only 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 wrote:
| Or when you use the index, but still have to do a lot of
| IO.
| jabart wrote:
| Microsoft 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.
| [deleted]
| cmrdporcupine wrote:
| 'tis why some newer DB storage systems support variable sized
| pages.
|
| https://db.in.tum.de/~freitag/papers/p29-neumann-cidr20.pdf
| [deleted]
| archit3cture wrote:
| This 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.
| [deleted]
| high_priest wrote:
| Looks like they are hand drawn
| johndough wrote:
| The tables look like they have been created with
| 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
| haki wrote:
| I used excalidraw.com
| cxcorp wrote:
| It 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).
| throwmeaway2232 wrote:
| I 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.
___________________________________________________________________
(page generated 2023-08-30 23:02 UTC)