Force TOAST to be used by the PostgreSQL table

[chrisdone]

While researching TOAST I found this very interesting article: The Surprising Impact of Medium-Size Texts on PostgreSQL Performance

Screenshot:

This includes when indexes are used.

The takeaway is:

• If you have lots of small fields <2KB, then pulling rows from your table will be fast and should not use TOAST.
• If you have lots of big fields, TOAST will perform much better when selecting specific rows from tables.
• If you have a mixture of small and big fields, you're better off configuring the tuple setting to force PG to TOAST strings more aggressively:

toast_tuple_target is a storage parameter that controls the minimum tuple length after which PostgreSQL tries to move long values to TOAST. The default is 2K, but it can be decreased to a minimum of 128 bytes. The lower the target, the more chances are for a medium size string to be move out-of-line to the TOAST table.

In the case of Casa, we have a mixture of large and small fields. Hypothetically we could achieve a performance boost by configuring this and pushing out all our blobs into TOAST. Casa regularly performs queries of the form select content from blob where id in (x, y, z, ...). But we'd need to run some SQL queries to group by content size to see what the size distributions are like.

If we get some query time metrics from Prometheus, that could be a way to measure the improvement. Could make a decent blog post.