PG Phriday: Burnt TOAST
www.pgedge.com
Shaun Thomas on how PostgreSQL TOAST stores large columns, when it speeds up scans and updates, and where splitting a table or switching to lz4 helps - with benchmarks.
  • lobsters__zie@crustaceanson.theshelf.topM
    1·
    3 days ago

    One reason PostgreSQL is awesome is because when your queries start getting slow, throwing hardware at the problem solves it 99% of the time. Which is exactly what I would have done here first, added more resources for PG to play with. It sounds like they are running it on some super constrained hardware. I assume for editorial/blog purposes and not for real out in production.

    We have an event log like this in a database, we call it the audit_log and we use JSON, not XML. That said, we currently have around 100M rows and we don’t have speed or timeout issues and we are very lazy and don’t optimize anything we don’t have to. Our pg_relation_size() is currently 56GB for audit_log. Of course our entire DB fits in memory, not just this one audit_log table.

    That said, thanks for doing the hard work of tracking down the PG optimization fun, great read!

    score 1 · canonical

    • lobsters__fanf@crustaceanson.theshelf.topOPM
      1·
      4 days ago

      Yeah I wondered the same when reading it. I gathered that problems arise when there isn’t a clear idea in the schema or the queries of when the large TOASTy columns are needed, so they get pulled in unnecessarily. If the TOASTy column is moved to a separate table, such that queries must do a join to touch the TOAST, then it’s less likely for a query to accidentally pull in large amounts of wasted data. (Heh, I guess an ORM could easily undermine this trick.)

      In the past I was planning a schema design in which I made a mistake very similar to this; sadly it never got to production so I didn’t get the opportunity to learn from my own fuckup, but it’s good to belatedly learn from someone else’s experience.

      score 1 · canonical