Burnt TOAST
14 points by fanf
14 points by fanf
Several million rows is pretty small. And I'm not sure why the big text in each row was a problem at this size either?
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.
Key quote for some of the problems they were encountering:
“Just specify the column names!” you might say, and I agree. If only it were that easy to modify ORM-generated queries and five years of accumulated hand-written ones.
This is a pretty good argument for having and using a (decent) ORM. A large fraction of the post could be solved by a decent ORM that doesn't do SELECT *, and/or provides a mechanism to control which columns are loaded e.g. defer in Django. You can put the defer() call on the model's base manager and have it applied by default.
It could also be solved by some mechanism to analyse and clean up hand-written queries to remove the SELECT *. I guess I assumed that people who write a lot of queries by hand would have tools for this, but maybe they don't exist.
A lot of people write SELECT * in the first place because writing out all the columns is a pain, which is also what ORMs (decent ones) solve.