Re: Optimize external TOAST storage

On Thu, Mar 17, 2022 at 01:04:17PM -0400, Robert Haas wrote:
> Right, so perhaps the ultimate thing here would be a more fine-grained
> knob than SET STORAGE EXTERNAL -- something that allows you to specify
> that you want to compress only when it really helps. While some people
> might find that useful, I think the current patch is less ambitious,
> and I think that's OK. It just wants to save something in the cases
> where it's basically free. Unfortunately we've learned that it's never
> *entirely* free because making the last TOAST chunk longer can always
> cost you something, even if it gets longer by only 1 byte. But for
> larger values it's hard for that to be significant.

I guess I think we should be slightly more ambitious. One idea could be to
create a default_toast_compression_ratio GUC with a default of 0.95. This
means that, by default, a compressed attribute must be 0.95x or less of the
size of the uncompressed attribute to be stored compressed. Like
default_toast_compression, this could also be overridden at the column
level with something like

ALTER TABLE mytbl ALTER mycol SET COMPRESSION lz4 RATIO 0.9;

If the current "basically free" patch is intended for v15, then maybe all
this extra configurability stuff could wait for a bit, especially if we can
demonstrate a decent read performance boost with a more conservative
setting. However, I don't see anything terribly complicated about the
proposed configurability changes (assuming my proposal makes some amount of
sense to you and others).

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com