From: | Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com> |
---|---|
To: | Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: A separate table level option to control compression |
Date: | 2019-03-12 12:38:02 |
Message-ID: | d15d15d8-8780-024c-8fc0-c33e8432ab7a@2ndQuadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 3/11/19 2:23 AM, Masahiko Sawada wrote:
> On Wed, Feb 6, 2019 at 4:32 PM Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:
>> Hello,
>>
>> Currently either the table level option `toast_tuple_target` or the compile time default `TOAST_TUPLE_TARGET` is used to decide whether a new tuple should be compressed or not. While this works reasonably well for most situations, at times the user may not want to pay the overhead of toasting, yet take benefits of inline compression.
>>
>> I would like to propose a new table level option, compress_tuple_target, which can be set independently of toast_tuple_target, and is checked while deciding whether to compress the new tuple or not.
>>
>> For example,
>>
>> CREATE TABLE compresstest250 (a int, b text) WITH (compress_tuple_target = 250);
>> CREATE TABLE compresstest2040 (a int, b text) WITH (compress_tuple_target = 2040);
>>
>> -- shouldn't get compressed nor toasted
>> INSERT INTO compresstest250 VALUES (1, repeat('1234567890',20));
>>
>> -- should get compressed, but not toasted
>> INSERT INTO compresstest250 VALUES (2, repeat('1234567890',30));
>>
>> -- shouldn't get compressed nor toasted
>> INSERT INTO compresstest2040 VALUES (1, repeat('1234567890',20));
>> INSERT INTO compresstest2040 VALUES (2, repeat('1234567890',30));
>>
>> Without this patch, the second INSERT will not compress the tuple since its length is less than the toast threshold. With the patch and after setting table level option, one can compress such tuples.
>>
>> The attached patch implements this idea.
>>
> I like this idea.
>
> The patch seems to need update the part describing on-disk toast
> storage in storage.sgml.
>
Yeah. Meanwhile, here's a rebased version of the patch to keep the cfbot
happy.
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachment | Content-Type | Size |
---|---|---|
compress_tuple_target-v2.patch | text/x-patch | 14.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2019-03-12 12:44:03 | Re: Offline enabling/disabling of data checksums |
Previous Message | Hugh Ranalli | 2019-03-12 12:30:51 | Re: Unaccent extension python script Issue in Windows |