MAIN, Uncompressed?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

MAIN, Uncompressed?

Simon Riggs
It looks like we need a new Column Storage option for MAIN, Uncompressed.

We have these Column Storage options
Plain - inline only, uncompressed
Main - inline until external as last resort, compressible
External - external, uncompressed
Extended - external, compressible

So there is no option for Main, but not compressible...

With reference to code... there seems to be no way to skip step 3

/* ----------
 * Compress and/or save external until data fits into target length
 *
 *  1: Inline compress attributes with attstorage 'x', and store very
 *     large attributes with attstorage 'x' or 'e' external immediately
 *  2: Store attributes with attstorage 'x' or 'e' external
 *  3: Inline compress attributes with attstorage 'm'
 *  4: Store attributes with attstorage 'm' external
 * ----------
 */

Not sure what to call this new option? MAINU?

Objections?

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Tom Lane-2
Simon Riggs <[hidden email]> writes:
> It looks like we need a new Column Storage option for MAIN, Uncompressed.
> We have these Column Storage options
> Plain - inline only, uncompressed
> Main - inline until external as last resort, compressible
> External - external, uncompressed
> Extended - external, compressible

> So there is no option for Main, but not compressible...

Doesn't Plain serve the purpose?

In point of fact, though, "never inline and never compress" is not really
a useful option, as it can be more easily read as "fail on wide values".

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Simon Riggs
On 25 August 2017 at 12:24, Tom Lane <[hidden email]> wrote:

> Simon Riggs <[hidden email]> writes:
>> It looks like we need a new Column Storage option for MAIN, Uncompressed.
>> We have these Column Storage options
>> Plain - inline only, uncompressed
>> Main - inline until external as last resort, compressible
>> External - external, uncompressed
>> Extended - external, compressible
>
>> So there is no option for Main, but not compressible...
>
> Doesn't Plain serve the purpose?

No, because that just gives an error if you try to insert a large column value.

> In point of fact, though, "never inline and never compress" is not really
> a useful option, as it can be more easily read as "fail on wide values".

Agreed, but that is not what I am proposing.

Main is roughly what is wanted, yet it always tries to compress. If
you already know that won't be useful it should be possible to turn
compression off.

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Tom Lane-2
Simon Riggs <[hidden email]> writes:
> Main is roughly what is wanted, yet it always tries to compress. If
> you already know that won't be useful it should be possible to turn
> compression off.

If you know compression isn't useful, but you don't want to fail on
wide values, then "external" should serve the purpose.

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Simon Riggs
On 25 August 2017 at 13:21, Tom Lane <[hidden email]> wrote:
> Simon Riggs <[hidden email]> writes:
>> Main is roughly what is wanted, yet it always tries to compress. If
>> you already know that won't be useful it should be possible to turn
>> compression off.
>
> If you know compression isn't useful, but you don't want to fail on
> wide values, then "external" should serve the purpose.

Well, almost. External toasts at 2048-ish bytes whereas Main toasts at
8160 bytes.

The rows are typically near 4kB long, so if marked External they would
always be toasted.

It's desirable to have the full row in the heap block, rather than
have to access heap-toastindex-toastblocks in all cases.

The data is also incompressible, so Main just wastes time on insert.

Hence, we have a missing option.

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Tom Lane-2
Simon Riggs <[hidden email]> writes:
> On 25 August 2017 at 13:21, Tom Lane <[hidden email]> wrote:
>> If you know compression isn't useful, but you don't want to fail on
>> wide values, then "external" should serve the purpose.

> Well, almost. External toasts at 2048-ish bytes whereas Main toasts at
> 8160 bytes.
> The rows are typically near 4kB long, so if marked External they would
> always be toasted.
> It's desirable to have the full row in the heap block, rather than
> have to access heap-toastindex-toastblocks in all cases.
> The data is also incompressible, so Main just wastes time on insert.
> Hence, we have a missing option.

Maybe, but the use case seems mighty narrow.

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Simon Riggs
On 25 August 2017 at 14:08, Tom Lane <[hidden email]> wrote:

> Simon Riggs <[hidden email]> writes:
>> On 25 August 2017 at 13:21, Tom Lane <[hidden email]> wrote:
>>> If you know compression isn't useful, but you don't want to fail on
>>> wide values, then "external" should serve the purpose.
>
>> Well, almost. External toasts at 2048-ish bytes whereas Main toasts at
>> 8160 bytes.
>> The rows are typically near 4kB long, so if marked External they would
>> always be toasted.
>> It's desirable to have the full row in the heap block, rather than
>> have to access heap-toastindex-toastblocks in all cases.
>> The data is also incompressible, so Main just wastes time on insert.
>> Hence, we have a missing option.
>
> Maybe, but the use case seems mighty narrow.

JSON blobs between 2kB and 8160 bytes are very common.

String length is maybe a poisson distribution, definitely not uniform.

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Greg Stark
On 25 August 2017 at 19:59, Simon Riggs <[hidden email]> wrote:
>
> On 25 August 2017 at 14:08, Tom Lane <[hidden email]> wrote:
>
> > Maybe, but the use case seems mighty narrow.
>
> JSON blobs between 2kB and 8160 bytes are very common.
>
> String length is maybe a poisson distribution, definitely not uniform.


But JSON blobs should be highly compressible. Even jsonb will be quite
compressible.

That said I always found remembering the mapping from these names to
various behaviours to be quite hard to use. I would have found it far
more useful to have two separate properties I could set "compress" and
"external" or perhaps even more useful would be to set some kind of
guideline size threshold for each (and perhaps a second size compress
threshold and external threshold for the whole tuple).

I think this is a particularly old piece of code and we're lucky the
default heuristics have served well for all this time because I doubt
many people fiddle with these storage attributes. The time may have
come to come up with a better UI for the storage attributes because
people are doing new things (like json) and wanting more control over
this heuristic.

For what it's worth I think a good start would be to give people more
visibility into what the tuptoaster heuristic is actually doing to
their data and that will encourage people to give feedback about when
they're surprised and are frustrated by the existing UI.


--
greg


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Tom Lane-2
Greg Stark <[hidden email]> writes:
> I think this is a particularly old piece of code and we're lucky the
> default heuristics have served well for all this time because I doubt
> many people fiddle with these storage attributes. The time may have
> come to come up with a better UI for the storage attributes because
> people are doing new things (like json) and wanting more control over
> this heuristic.

Yeah, I could get behind a basic rethinking of the tuptoaster control
knobs.  I'm just not in love with Simon's specific proposal, especially
not if we can't think of a better name for it than "MAINU".

> For what it's worth I think a good start would be to give people more
> visibility into what the tuptoaster heuristic is actually doing to
> their data and that will encourage people to give feedback about when
> they're surprised and are frustrated by the existing UI.

Hm, what might that look like exactly?  More pgstattuple functionality
perhaps?

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Simon Riggs
On 25 August 2017 at 20:53, Tom Lane <[hidden email]> wrote:

> Greg Stark <[hidden email]> writes:
>> I think this is a particularly old piece of code and we're lucky the
>> default heuristics have served well for all this time because I doubt
>> many people fiddle with these storage attributes. The time may have
>> come to come up with a better UI for the storage attributes because
>> people are doing new things (like json) and wanting more control over
>> this heuristic.
>
> Yeah, I could get behind a basic rethinking of the tuptoaster control
> knobs.  I'm just not in love with Simon's specific proposal, especially
> not if we can't think of a better name for it than "MAINU".

Extended/External would be just fine if you could set the toast
target, so I think a better suggestion would be to make "toast_target"
a per-attribute option .

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Mark Kirkwood-2
On 26/08/17 12:18, Simon Riggs wrote:

> On 25 August 2017 at 20:53, Tom Lane <[hidden email]> wrote:
>> Greg Stark <[hidden email]> writes:
>>> I think this is a particularly old piece of code and we're lucky the
>>> default heuristics have served well for all this time because I doubt
>>> many people fiddle with these storage attributes. The time may have
>>> come to come up with a better UI for the storage attributes because
>>> people are doing new things (like json) and wanting more control over
>>> this heuristic.
>> Yeah, I could get behind a basic rethinking of the tuptoaster control
>> knobs.  I'm just not in love with Simon's specific proposal, especially
>> not if we can't think of a better name for it than "MAINU".
> Extended/External would be just fine if you could set the toast
> target, so I think a better suggestion would be to make "toast_target"
> a per-attribute option .
>

+1, have thought about this myself previously....thank you for bringing
it up!

regards

Mark


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Simon Riggs
On 26 August 2017 at 05:40, Mark Kirkwood <[hidden email]> wrote:

> On 26/08/17 12:18, Simon Riggs wrote:
>
>> On 25 August 2017 at 20:53, Tom Lane <[hidden email]> wrote:
>>>
>>> Greg Stark <[hidden email]> writes:
>>>>
>>>> I think this is a particularly old piece of code and we're lucky the
>>>> default heuristics have served well for all this time because I doubt
>>>> many people fiddle with these storage attributes. The time may have
>>>> come to come up with a better UI for the storage attributes because
>>>> people are doing new things (like json) and wanting more control over
>>>> this heuristic.
>>>
>>> Yeah, I could get behind a basic rethinking of the tuptoaster control
>>> knobs.  I'm just not in love with Simon's specific proposal, especially
>>> not if we can't think of a better name for it than "MAINU".
>>
>> Extended/External would be just fine if you could set the toast
>> target, so I think a better suggestion would be to make "toast_target"
>> a per-attribute option .
>>
>
> +1, have thought about this myself previously....thank you for bringing it
> up!
OK, so table-level option for "toast_tuple_target", not attribute-level option

The attached patch and test shows this concept is useful and doesn't
affect existing data.

For 4x 4000 byte rows:
* by default we use 1 heap block and 3 toast blocks
* toast_tuple_target=4080 uses 2 heap blocks and 0 toast blocks

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

toast_tuple_target.v1.patch (11K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Simon Riggs
On 29 August 2017 at 07:58, Simon Riggs <[hidden email]> wrote:

> On 26 August 2017 at 05:40, Mark Kirkwood <[hidden email]> wrote:
>> On 26/08/17 12:18, Simon Riggs wrote:
>>
>>> On 25 August 2017 at 20:53, Tom Lane <[hidden email]> wrote:
>>>>
>>>> Greg Stark <[hidden email]> writes:
>>>>>
>>>>> I think this is a particularly old piece of code and we're lucky the
>>>>> default heuristics have served well for all this time because I doubt
>>>>> many people fiddle with these storage attributes. The time may have
>>>>> come to come up with a better UI for the storage attributes because
>>>>> people are doing new things (like json) and wanting more control over
>>>>> this heuristic.
>>>>
>>>> Yeah, I could get behind a basic rethinking of the tuptoaster control
>>>> knobs.  I'm just not in love with Simon's specific proposal, especially
>>>> not if we can't think of a better name for it than "MAINU".
>>>
>>> Extended/External would be just fine if you could set the toast
>>> target, so I think a better suggestion would be to make "toast_target"
>>> a per-attribute option .
>>>
>>
>> +1, have thought about this myself previously....thank you for bringing it
>> up!
>
> OK, so table-level option for "toast_tuple_target", not attribute-level option
>
> The attached patch and test shows this concept is useful and doesn't
> affect existing data.
>
> For 4x 4000 byte rows:
> * by default we use 1 heap block and 3 toast blocks
> * toast_tuple_target=4080 uses 2 heap blocks and 0 toast blocks

New patch, v2, since one line in the docs failed to apply because of
recent changes.

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

toast_tuple_target.v2.patch (11K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: MAIN, Uncompressed?

Andrew Dunstan-8


On 09/12/2017 12:11 PM, Simon Riggs wrote:

>
>> OK, so table-level option for "toast_tuple_target", not attribute-level option
>>
>> The attached patch and test shows this concept is useful and doesn't
>> affect existing data.
>>
>> For 4x 4000 byte rows:
>> * by default we use 1 heap block and 3 toast blocks
>> * toast_tuple_target=4080 uses 2 heap blocks and 0 toast blocks
>
> New patch, v2, since one line in the docs failed to apply because of
> recent changes.
>


This has bitrotted ever so slightly. I fixed that and took the
opportunity to edit the docs text slightly to improve the clarity a bit.
Revised patch attached - adjust to your taste.

The code is extremely simple and small and straightforward, and the
tests are good.

Marking as Ready for Committer,

cheers

andrew

--
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


toast_tuple_target.v3.patch (8K) Download Attachment
Previous Thread Next Thread