Custom compression methods

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

Re: Custom compression methods

Oleg Bartunov-2
On Sat, Dec 2, 2017 at 6:04 PM, Tomas Vondra
<[hidden email]> wrote:

> On 12/01/2017 10:52 PM, Andres Freund wrote:
>> On 2017-12-01 16:14:58 -0500, Robert Haas wrote:
>>> Honestly, if we can give everybody a 4% space reduction by
>>> switching to lz4, I think that's totally worth doing -- but let's
>>> not make people choose it, let's make it the default going forward,
>>> and keep pglz support around so we don't break pg_upgrade
>>> compatibility (and so people can continue to choose it if for some
>>> reason it works better in their use case). That kind of improvement
>>> is nothing special in a specific workload, but TOAST is a pretty
>>> general-purpose mechanism. I have become, through a few bitter
>>> experiences, a strong believer in the value of trying to reduce our
>>> on-disk footprint, and knocking 4% off the size of every TOAST
>>> table in the world does not sound worthless to me -- even though
>>> context-aware compression can doubtless do a lot better.
>>
>> +1. It's also a lot faster, and I've seen way way to many workloads
>> with 50%+ time spent in pglz.
>>
>
> TBH the 4% figure is something I mostly made up (I'm fake news!). On the
> mailing list archive (which I believe is pretty compressible) I observed
> something like 2.5% size reduction with lz4 compared to pglz, at least
> with the compression levels I've used ...
Nikita Glukhove tested compression on real  json data:

Delicious bookmarks (js):

json 1322MB
jsonb 1369MB
jsonbc 931MB 1.5x
jsonb+lz4d 404MB 3.4x

Citus customer reviews (jr):

json 1391MB
jsonb 1574MB
jsonbc 622MB 2.5x
jsonb+lz4d 601MB 2.5x

I also attached a plot with wired tiger size (zstd compression) in Mongodb.
Nikita has more numbers about compression.


>
> Other algorithms (e.g. zstd) got significantly better compression (25%)
> compared to pglz, but in exchange for longer compression. I'm sure we
> could lower compression level to make it faster, but that will of course
> hurt the compression ratio.
>
> I don't think switching to a different compression algorithm is a way
> forward - it was proposed and explored repeatedly in the past, and every
> time it failed for a number of reasons, most of which are still valid.
>
>
> Firstly, it's going to be quite hard (or perhaps impossible) to find an
> algorithm that is "universally better" than pglz. Some algorithms do
> work better for text documents, some for binary blobs, etc. I don't
> think there's a win-win option.
>
> Sure, there are workloads where pglz performs poorly (I've seen such
> cases too), but IMHO that's more an argument for the custom compression
> method approach. pglz gives you good default compression in most cases,
> and you can change it for columns where it matters, and where a
> different space/time trade-off makes sense.
>
>
> Secondly, all the previous attempts ran into some legal issues, i.e.
> licensing and/or patents. Maybe the situation changed since then (no
> idea, haven't looked into that), but in the past the "pluggable"
> approach was proposed as a way to address this.
I don't think so. Pluggable means that now we have more data types,
which don't fit to
the old compression scheme of TOAST and we need better flexibility. I
see in future we
could avoid decompression of the whole toast just to get on key from
document, so we
first slice data and compress each slice separately.

>
>
> regards
>
> --
> Tomas Vondra                  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Screen Shot 2017-12-03 at 11.46.14.png (250K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Ildus Kurbangaliev
In reply to this post by Tomas Vondra-4
On Fri, 1 Dec 2017 21:47:43 +0100
Tomas Vondra <[hidden email]> wrote:
 
>
> +1 to do the rewrite, just like for other similar ALTER TABLE commands

Ok. What about the following syntax:

ALTER COLUMN DROP COMPRESSION - removes compression from the column
with the rewrite and removes related compression options, so the user
can drop compression method.

ALTER COLUMN SET COMPRESSION NONE for the cases when
the users want to just disable compression for future tuples. After
that they can keep compressed tuples, or in the case when they have a
large table they can decompress tuples partially using e.g. UPDATE,
and then use ALTER COLUMN DROP COMPRESSION which will be much faster
then.

ALTER COLUMN SET COMPRESSION <cm> WITH <cmoptions> will change
compression for new tuples but will not touch old ones. If the users
want the recompression they can use DROP/SET COMPRESSION combination.

I don't think that SET COMPRESSION with the rewrite of the whole table
will be useful enough on any somewhat big tables and same time big
tables is where the user needs compression the most.

I understand that ALTER with the rewrite sounds logical and much easier
to implement (and it doesn't require Oids in tuples), but it could be
unusable.

--
----
Regards,
Ildus Kurbangaliev

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Robert Haas
On Wed, Dec 6, 2017 at 10:07 AM, Ildus Kurbangaliev
<[hidden email]> wrote:

> On Fri, 1 Dec 2017 21:47:43 +0100
> Tomas Vondra <[hidden email]> wrote:
>> +1 to do the rewrite, just like for other similar ALTER TABLE commands
>
> Ok. What about the following syntax:
>
> ALTER COLUMN DROP COMPRESSION - removes compression from the column
> with the rewrite and removes related compression options, so the user
> can drop compression method.
>
> ALTER COLUMN SET COMPRESSION NONE for the cases when
> the users want to just disable compression for future tuples. After
> that they can keep compressed tuples, or in the case when they have a
> large table they can decompress tuples partially using e.g. UPDATE,
> and then use ALTER COLUMN DROP COMPRESSION which will be much faster
> then.
>
> ALTER COLUMN SET COMPRESSION <cm> WITH <cmoptions> will change
> compression for new tuples but will not touch old ones. If the users
> want the recompression they can use DROP/SET COMPRESSION combination.
>
> I don't think that SET COMPRESSION with the rewrite of the whole table
> will be useful enough on any somewhat big tables and same time big
> tables is where the user needs compression the most.
>
> I understand that ALTER with the rewrite sounds logical and much easier
> to implement (and it doesn't require Oids in tuples), but it could be
> unusable.

The problem with this is that old compression methods can still be
floating around in the table even after you have done SET COMPRESSION
to something else.  The table still needs to have a dependency on the
old compression method, because otherwise you might think it's safe to
drop the old one when it really is not.  Furthermore, if you do a
pg_upgrade, you've got to preserve that dependency, which means it
would have to show up in a pg_dump --binary-upgrade someplace.  It's
not obvious how any of that would work with this syntax.

Maybe a better idea is ALTER COLUMN SET COMPRESSION x1, x2, x3 ...
meaning that x1 is the default for new tuples but x2, x3, etc. are
still allowed if present.  If you issue a command that only adds
things to the list, no table rewrite happens, but if you remove
anything, then it does.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Ildus Kurbangaliev
On Fri, 8 Dec 2017 15:12:42 -0500
Robert Haas <[hidden email]> wrote:

>
> Maybe a better idea is ALTER COLUMN SET COMPRESSION x1, x2, x3 ...
> meaning that x1 is the default for new tuples but x2, x3, etc. are
> still allowed if present.  If you issue a command that only adds
> things to the list, no table rewrite happens, but if you remove
> anything, then it does.
>

I like this idea, but maybe it should be something like ALTER COLUMN
SET COMPRESSION x1 [ PRESERVE x2, x3 ]? 'PRESERVE' is already used in
syntax and this syntax will show better which one is current and which
ones should be kept.

--
----
Regards,
Ildus Kurbangaliev

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Robert Haas
On Mon, Dec 11, 2017 at 7:55 AM, Ildus Kurbangaliev
<[hidden email]> wrote:

> On Fri, 8 Dec 2017 15:12:42 -0500
> Robert Haas <[hidden email]> wrote:
>> Maybe a better idea is ALTER COLUMN SET COMPRESSION x1, x2, x3 ...
>> meaning that x1 is the default for new tuples but x2, x3, etc. are
>> still allowed if present.  If you issue a command that only adds
>> things to the list, no table rewrite happens, but if you remove
>> anything, then it does.
>
> I like this idea, but maybe it should be something like ALTER COLUMN
> SET COMPRESSION x1 [ PRESERVE x2, x3 ]? 'PRESERVE' is already used in
> syntax and this syntax will show better which one is current and which
> ones should be kept.

Sure, that works.  And I think pglz should exist in the catalog as a
predefined, undroppable compression algorithm.  So the default for
each column initially is:

SET COMPRESSION pglz

And if you want to rewrite the table with your awesome custom thing, you can do

SET COMPRESSION awesome

But if you want to just use the awesome custom thing for new rows, you can do

SET COMPRESSION awesome PRESERVE pglz

Then we can get all the dependencies right, pg_upgrade works, users
have total control of rewrite behavior, and everything is great.  :-)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Alexander Korotkov
On Mon, Dec 11, 2017 at 8:25 PM, Robert Haas <[hidden email]> wrote:
On Mon, Dec 11, 2017 at 7:55 AM, Ildus Kurbangaliev
<[hidden email]> wrote:
> On Fri, 8 Dec 2017 15:12:42 -0500
> Robert Haas <[hidden email]> wrote:
>> Maybe a better idea is ALTER COLUMN SET COMPRESSION x1, x2, x3 ...
>> meaning that x1 is the default for new tuples but x2, x3, etc. are
>> still allowed if present.  If you issue a command that only adds
>> things to the list, no table rewrite happens, but if you remove
>> anything, then it does.
>
> I like this idea, but maybe it should be something like ALTER COLUMN
> SET COMPRESSION x1 [ PRESERVE x2, x3 ]? 'PRESERVE' is already used in
> syntax and this syntax will show better which one is current and which
> ones should be kept.

Sure, that works.  And I think pglz should exist in the catalog as a
predefined, undroppable compression algorithm.  So the default for
each column initially is:

SET COMPRESSION pglz

And if you want to rewrite the table with your awesome custom thing, you can do

SET COMPRESSION awesome

But if you want to just use the awesome custom thing for new rows, you can do

SET COMPRESSION awesome PRESERVE pglz

Then we can get all the dependencies right, pg_upgrade works, users
have total control of rewrite behavior, and everything is great.  :-)

Looks good.

Thus, in your example if user would like to further change awesome compression for evenbetter compression, she should write.

SET COMPRESSION evenbetter PRESERVE pglz, awesome; -- full list of previous compression methods

I wonder what should we do if user specifies only part of previous compression methods?  For instance, pglz is specified but awesome is missing.

SET COMPRESSION evenbetter PRESERVE pglz; -- awesome is missing

I think we should trigger an error in this case.  Because query is specified in form that is assuming to work without table rewrite, but we're unable to do this without table rewrite.

I also think that we need some way to change compression method for multiple columns in a single table rewrite.  Because it would be way more efficient than rewriting table for each of columns.  So as an alternative of

ALTER TABLE tbl ALTER COLUMN c1 SET COMPRESSION awesome; -- first table rewrite
ALTER TABLE tbl ALTER COLUMN c2 SET COMPRESSION awesome; -- second table rewrite

we could also provide

ALTER TABLE tbl ALTER COLUMN c1 SET COMPRESSION awesome PRESERVE pglz; -- no rewrite
ALTER TABLE tbl ALTER COLUMN c2 SET COMPRESSION awesome PRESERVE pglz; -- no rewrite
VACUUM FULL tbl RESET COMPRESSION PRESERVE c1, c2; -- rewrite with recompression of c1 and c2 and removing depedencies

?

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Robert Haas
On Mon, Dec 11, 2017 at 12:41 PM, Alexander Korotkov
<[hidden email]> wrote:
> Thus, in your example if user would like to further change awesome
> compression for evenbetter compression, she should write.
>
> SET COMPRESSION evenbetter PRESERVE pglz, awesome; -- full list of previous
> compression methods

Right.

> I wonder what should we do if user specifies only part of previous
> compression methods?  For instance, pglz is specified but awesome is
> missing.
>
> SET COMPRESSION evenbetter PRESERVE pglz; -- awesome is missing
>
> I think we should trigger an error in this case.  Because query is specified
> in form that is assuming to work without table rewrite, but we're unable to
> do this without table rewrite.

I think that should just rewrite the table in that case.  PRESERVE
should specify the things that are allowed to be preserved -- its mere
presence should not be read to preclude a rewrite.  And it's
completely reasonable for someone to want to do this, if they are
thinking about de-installing awesome.

> I also think that we need some way to change compression method for multiple
> columns in a single table rewrite.  Because it would be way more efficient
> than rewriting table for each of columns.  So as an alternative of
>
> ALTER TABLE tbl ALTER COLUMN c1 SET COMPRESSION awesome; -- first table
> rewrite
> ALTER TABLE tbl ALTER COLUMN c2 SET COMPRESSION awesome; -- second table
> rewrite
>
> we could also provide
>
> ALTER TABLE tbl ALTER COLUMN c1 SET COMPRESSION awesome PRESERVE pglz; -- no
> rewrite
> ALTER TABLE tbl ALTER COLUMN c2 SET COMPRESSION awesome PRESERVE pglz; -- no
> rewrite
> VACUUM FULL tbl RESET COMPRESSION PRESERVE c1, c2; -- rewrite with
> recompression of c1 and c2 and removing depedencies
>
> ?

Hmm.  ALTER TABLE allows multi comma-separated subcommands, so I don't
think we need to drag VACUUM into this.  The user can just say:

ALTER TABLE tbl ALTER COLUMN c1 SET COMPRESSION awesome, ALTER COLUMN
c2 SET COMPRESSION awesome;

If this is properly integrated into tablecmds.c, that should cause a
single rewrite affecting both columns.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Alexander Korotkov
On Mon, Dec 11, 2017 at 8:46 PM, Robert Haas <[hidden email]> wrote:
On Mon, Dec 11, 2017 at 12:41 PM, Alexander Korotkov
<[hidden email]> wrote:
> Thus, in your example if user would like to further change awesome
> compression for evenbetter compression, she should write.
>
> SET COMPRESSION evenbetter PRESERVE pglz, awesome; -- full list of previous
> compression methods

Right.

> I wonder what should we do if user specifies only part of previous
> compression methods?  For instance, pglz is specified but awesome is
> missing.
>
> SET COMPRESSION evenbetter PRESERVE pglz; -- awesome is missing
>
> I think we should trigger an error in this case.  Because query is specified
> in form that is assuming to work without table rewrite, but we're unable to
> do this without table rewrite.

I think that should just rewrite the table in that case.  PRESERVE
should specify the things that are allowed to be preserved -- its mere
presence should not be read to preclude a rewrite.  And it's
completely reasonable for someone to want to do this, if they are
thinking about de-installing awesome.
 
OK, but NOTICE that presumably unexpected table rewrite takes place could be still useful.

Also we probably should add some view that will expose compression methods whose are currently preserved for columns.  So that user can correctly construct SET COMPRESSION query that doesn't rewrites table without digging into internals (like directly querying pg_depend).

> I also think that we need some way to change compression method for multiple
> columns in a single table rewrite.  Because it would be way more efficient
> than rewriting table for each of columns.  So as an alternative of
>
> ALTER TABLE tbl ALTER COLUMN c1 SET COMPRESSION awesome; -- first table
> rewrite
> ALTER TABLE tbl ALTER COLUMN c2 SET COMPRESSION awesome; -- second table
> rewrite
>
> we could also provide
>
> ALTER TABLE tbl ALTER COLUMN c1 SET COMPRESSION awesome PRESERVE pglz; -- no
> rewrite
> ALTER TABLE tbl ALTER COLUMN c2 SET COMPRESSION awesome PRESERVE pglz; -- no
> rewrite
> VACUUM FULL tbl RESET COMPRESSION PRESERVE c1, c2; -- rewrite with
> recompression of c1 and c2 and removing depedencies
>
> ?

Hmm.  ALTER TABLE allows multi comma-separated subcommands, so I don't
think we need to drag VACUUM into this.  The user can just say:

ALTER TABLE tbl ALTER COLUMN c1 SET COMPRESSION awesome, ALTER COLUMN
c2 SET COMPRESSION awesome;

If this is properly integrated into tablecmds.c, that should cause a
single rewrite affecting both columns.

OK.  Sorry, I didn't notice we can use multiple subcommands for ALTER TABLE in this case...

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Tomas Vondra-4
In reply to this post by Ildus Kurbangaliev
Hi,

I see there's an ongoing discussion about the syntax and ALTER TABLE
behavior when changing a compression method for a column. So the patch
seems to be on the way to be ready in the January CF, I guess.

But let me play the devil's advocate for a while and question the
usefulness of this approach to compression. Some of the questions were
mentioned in the thread before, but I don't think they got the attention
they deserve.

FWIW I don't know the answers, but I think it's important to ask them.
Also, apologies if this post looks to be against the patch - that's part
of the "devil's advocate" thing.


The main question I'm asking myself is what use cases the patch
addresses, and whether there is a better way to do that. I see about
three main use-cases:

1) Replacing the algorithm used to compress all varlena types (in a way
that makes it transparent for the data type code).

2) Custom datatype-aware compression (e.g. the tsvector).

3) Custom datatype-aware compression with additional column-specific
metadata (e.g. the jsonb with external dictionary).

Now, let's discuss those use cases one by one, and see if there are
simpler (or better in some way) solutions ...


Replacing the algorithm used to compress all varlena values (in a way
that makes it transparent for the data type code).
----------------------------------------------------------------------

While pglz served us well over time, it was repeatedly mentioned that in
some cases it becomes the bottleneck. So supporting other state of the
art compression algorithms seems like a good idea, and this patch is one
way to do that.

But perhaps we should simply make it an initdb option (in which case the
whole cluster would simply use e.g. lz4 instead of pglz)?

That seems like a much simpler approach - it would only require some
./configure options to add --with-lz4 (and other compression libraries),
an initdb option to pick compression algorithm, and probably noting the
choice in cluster controldata.

No dependencies tracking, no ALTER TABLE issues, etc.

Of course, it would not allow using different compression algorithms for
different columns (although it might perhaps allow different compression
level, to some extent).

Conclusion: If we want to offer a simple cluster-wide pglz alternative,
perhaps this patch is not the right way to do that.


Custom datatype-aware compression (e.g. the tsvector)
----------------------------------------------------------------------

Exploiting knowledge of the internal data type structure is a promising
way to improve compression ratio and/or performance.

The obvious question of course is why shouldn't this be done by the data
type code directly, which would also allow additional benefits like
operating directly on the compressed values.

Another thing is that if the datatype representation changes in some
way, the compression method has to change too. So it's tightly coupled
to the datatype anyway.

This does not really require any new infrastructure, all the pieces are
already there.

In some cases that may not be quite possible - the datatype may not be
flexible enough to support alternative (compressed) representation, e.g.
because there are no bits available for "compressed" flag, etc.

Conclusion: IMHO if we want to exploit the knowledge of the data type
internal structure, perhaps doing that in the datatype code directly
would be a better choice.


Custom datatype-aware compression with additional column-specific
metadata (e.g. the jsonb with external dictionary).
----------------------------------------------------------------------

Exploiting redundancy in multiple values in the same column (instead of
compressing them independently) is another attractive way to help the
compression. It is inherently datatype-aware, but currently can't be
implemented directly in datatype code as there's no concept of
column-specific storage (e.g. to store dictionary shared by all values
in a particular column).

I believe any patch addressing this use case would have to introduce
such column-specific storage, and any solution doing that would probably
need to introduce the same catalogs, etc.

The obvious disadvantage of course is that we need to decompress the
varlena value before doing pretty much anything with it, because the
datatype is not aware of the compression.

So I wonder if the patch should instead provide infrastructure for doing
that in the datatype code directly.

The other question is if the patch should introduce some infrastructure
for handling the column context (e.g. column dictionary). Right now,
whoever implements the compression has to implement this bit too.



regards

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

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Ildus Kurbangaliev
On Mon, 11 Dec 2017 20:53:29 +0100
Tomas Vondra <[hidden email]> wrote:

> But let me play the devil's advocate for a while and question the
> usefulness of this approach to compression. Some of the questions were
> mentioned in the thread before, but I don't think they got the
> attention they deserve.

Hi. I will try to explain why this approach could be better than others.

>
>
> Replacing the algorithm used to compress all varlena values (in a way
> that makes it transparent for the data type code).
> ----------------------------------------------------------------------
>
> While pglz served us well over time, it was repeatedly mentioned that
> in some cases it becomes the bottleneck. So supporting other state of
> the art compression algorithms seems like a good idea, and this patch
> is one way to do that.
>
> But perhaps we should simply make it an initdb option (in which case
> the whole cluster would simply use e.g. lz4 instead of pglz)?
>
> That seems like a much simpler approach - it would only require some
> ./configure options to add --with-lz4 (and other compression
> libraries), an initdb option to pick compression algorithm, and
> probably noting the choice in cluster controldata.

Replacing pglz for all varlena values wasn't the goal of the patch, but
it's possible to do with it and I think that's good. And as Robert
mentioned pglz could appear as builtin undroppable compresssion method
so the others could be added too. And in the future it can open the
ways to specify compression for specific database or cluster.

>
> Custom datatype-aware compression (e.g. the tsvector)
> ----------------------------------------------------------------------
>
> Exploiting knowledge of the internal data type structure is a
> promising way to improve compression ratio and/or performance.
>
> The obvious question of course is why shouldn't this be done by the
> data type code directly, which would also allow additional benefits
> like operating directly on the compressed values.
>
> Another thing is that if the datatype representation changes in some
> way, the compression method has to change too. So it's tightly coupled
> to the datatype anyway.
>
> This does not really require any new infrastructure, all the pieces
> are already there.
>
> In some cases that may not be quite possible - the datatype may not be
> flexible enough to support alternative (compressed) representation,
> e.g. because there are no bits available for "compressed" flag, etc.
>
> Conclusion: IMHO if we want to exploit the knowledge of the data type
> internal structure, perhaps doing that in the datatype code directly
> would be a better choice.

It could be, but let's imagine there will be internal compression for
tsvector. It means that tsvector has two formats now and minus one bit
somewhere in the header. After a while we found a better compression
but we can't add it because there is already one and it's not good to
have three different formats for one type. Or, the compression methods
were implemented and we decided to use dictionaries for tsvector (if
the user going to store limited number of words). But it will mean that
tsvector will go two compression stages (for its internal and for
dictionaries).

>
>
> Custom datatype-aware compression with additional column-specific
> metadata (e.g. the jsonb with external dictionary).
> ----------------------------------------------------------------------
>
> Exploiting redundancy in multiple values in the same column (instead
> of compressing them independently) is another attractive way to help
> the compression. It is inherently datatype-aware, but currently can't
> be implemented directly in datatype code as there's no concept of
> column-specific storage (e.g. to store dictionary shared by all values
> in a particular column).
>
> I believe any patch addressing this use case would have to introduce
> such column-specific storage, and any solution doing that would
> probably need to introduce the same catalogs, etc.
>
> The obvious disadvantage of course is that we need to decompress the
> varlena value before doing pretty much anything with it, because the
> datatype is not aware of the compression.
>
> So I wonder if the patch should instead provide infrastructure for
> doing that in the datatype code directly.
>
> The other question is if the patch should introduce some
> infrastructure for handling the column context (e.g. column
> dictionary). Right now, whoever implements the compression has to
> implement this bit too.

Column specific storage sounds optional to me. For example compressing
timestamp[] using some delta compression will not require it.

--
----
Regards,
Ildus Kurbangaliev

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Alexander Korotkov
Hi!

Let me add my two cents too.

On Tue, Dec 12, 2017 at 2:41 PM, Ildus Kurbangaliev <[hidden email]> wrote:
On Mon, 11 Dec 2017 20:53:29 +0100 Tomas Vondra <[hidden email]> wrote:
> Replacing the algorithm used to compress all varlena values (in a way
> that makes it transparent for the data type code).
> ----------------------------------------------------------------------
>
> While pglz served us well over time, it was repeatedly mentioned that
> in some cases it becomes the bottleneck. So supporting other state of
> the art compression algorithms seems like a good idea, and this patch
> is one way to do that.
>
> But perhaps we should simply make it an initdb option (in which case
> the whole cluster would simply use e.g. lz4 instead of pglz)?
>
> That seems like a much simpler approach - it would only require some
> ./configure options to add --with-lz4 (and other compression
> libraries), an initdb option to pick compression algorithm, and
> probably noting the choice in cluster controldata.

Replacing pglz for all varlena values wasn't the goal of the patch, but
it's possible to do with it and I think that's good. And as Robert
mentioned pglz could appear as builtin undroppable compresssion method
so the others could be added too. And in the future it can open the
ways to specify compression for specific database or cluster.
 
Yes, usage of custom compression methods to replace generic non type-specific compression method is really not the primary goal of this patch.  However, I would consider that as useful side effect.  However, even in this case I see some advantages of custom compression methods over initdb option.

1) In order to support alternative compression methods in initdb, we have to provide builtin support for them.  Then we immediately run into dependencies/incompatible-licenses problem.  Also, we tie appearance of new compression methods to our release cycle.  In real life, flexibility means a lot.  Giving users freedom to experiment with various compression libraries without having to recompile PostgreSQL core is great.
2) It's not necessary that users would be satisfied with applying single compression method to the whole database cluster.  Various columns may have different data distributions with different workloads.  Optimal compression type for one column is not necessary optimal for another column.
3) Possibility to change compression method on the fly without re-initdb is very good too.

> Custom datatype-aware compression (e.g. the tsvector)
> ----------------------------------------------------------------------
>
> Exploiting knowledge of the internal data type structure is a
> promising way to improve compression ratio and/or performance.
>
> The obvious question of course is why shouldn't this be done by the
> data type code directly, which would also allow additional benefits
> like operating directly on the compressed values.
>
> Another thing is that if the datatype representation changes in some
> way, the compression method has to change too. So it's tightly coupled
> to the datatype anyway.
>
> This does not really require any new infrastructure, all the pieces
> are already there.
>
> In some cases that may not be quite possible - the datatype may not be
> flexible enough to support alternative (compressed) representation,
> e.g. because there are no bits available for "compressed" flag, etc.
>
> Conclusion: IMHO if we want to exploit the knowledge of the data type
> internal structure, perhaps doing that in the datatype code directly
> would be a better choice.

It could be, but let's imagine there will be internal compression for
tsvector. It means that tsvector has two formats now and minus one bit
somewhere in the header. After a while we found a better compression
but we can't add it because there is already one and it's not good to
have three different formats for one type. Or, the compression methods
were implemented and we decided to use dictionaries for tsvector (if
the user going to store limited number of words). But it will mean that
tsvector will go two compression stages (for its internal and for
dictionaries).

I would like to add that even for single datatype various compression methods may have different tradeoffs.  For instance, one compression method can have better compression ratio, but another one have faster decompression.  And it's OK for user to choose different compression methods for different columns.

Depending extensions on datatype internal representation doesn't seem evil for me.  We already have bunch of extension depending on much more deeper guts of PostgreSQL.  On major release of PostgreSQL, extensions must adopt the changes, that is the rule.  And note, the datatype internal representation alters relatively rare in comparison with other internals, because it's related to on-disk format and ability to pg_upgrade.

> Custom datatype-aware compression with additional column-specific
> metadata (e.g. the jsonb with external dictionary).
> ----------------------------------------------------------------------
>
> Exploiting redundancy in multiple values in the same column (instead
> of compressing them independently) is another attractive way to help
> the compression. It is inherently datatype-aware, but currently can't
> be implemented directly in datatype code as there's no concept of
> column-specific storage (e.g. to store dictionary shared by all values
> in a particular column).
>
> I believe any patch addressing this use case would have to introduce
> such column-specific storage, and any solution doing that would
> probably need to introduce the same catalogs, etc.
>
> The obvious disadvantage of course is that we need to decompress the
> varlena value before doing pretty much anything with it, because the
> datatype is not aware of the compression.
>
> So I wonder if the patch should instead provide infrastructure for
> doing that in the datatype code directly.
>
> The other question is if the patch should introduce some
> infrastructure for handling the column context (e.g. column
> dictionary). Right now, whoever implements the compression has to
> implement this bit too.

Column specific storage sounds optional to me. For example compressing
timestamp[] using some delta compression will not require it.

It's also could be useful to have custom compression method with fixed (not dynamically complemented) dictionary.  See [1] for example what other databases do.  We may specify fixed dictionary directly in the compression method options, I see no problems.  We may also compress that way not only jsonb or other special data types, but also natural language texts.  Using fixed dictionaries for natural language we can effectively compress short texts, when lz and other generic compression methods don't have enough of information to effectively train per-value dictionary.

For sure, further work to improve infrastructure is required including per-column storage for dictionary and tighter integration between compression method and datatype.  However, we are typically deal with such complex tasks in step-by-step approach.  And I'm not convinced that custom compression methods are bad for the first step in this direction.  For me they look clear and already very useful in this shape.


------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Oleg Bartunov-2
On Tue, Dec 12, 2017 at 6:07 PM, Alexander Korotkov
<[hidden email]> wrote:

> Hi!
>
> Let me add my two cents too.
>
> On Tue, Dec 12, 2017 at 2:41 PM, Ildus Kurbangaliev
> <[hidden email]> wrote:
>>
>> On Mon, 11 Dec 2017 20:53:29 +0100 Tomas Vondra
>> <[hidden email]> wrote:
>> > Replacing the algorithm used to compress all varlena values (in a way
>> > that makes it transparent for the data type code).
>> > ----------------------------------------------------------------------
>> >
>> > While pglz served us well over time, it was repeatedly mentioned that
>> > in some cases it becomes the bottleneck. So supporting other state of
>> > the art compression algorithms seems like a good idea, and this patch
>> > is one way to do that.
>> >
>> > But perhaps we should simply make it an initdb option (in which case
>> > the whole cluster would simply use e.g. lz4 instead of pglz)?
>> >
>> > That seems like a much simpler approach - it would only require some
>> > ./configure options to add --with-lz4 (and other compression
>> > libraries), an initdb option to pick compression algorithm, and
>> > probably noting the choice in cluster controldata.
>>
>> Replacing pglz for all varlena values wasn't the goal of the patch, but
>> it's possible to do with it and I think that's good. And as Robert
>> mentioned pglz could appear as builtin undroppable compresssion method
>> so the others could be added too. And in the future it can open the
>> ways to specify compression for specific database or cluster.
>
>
> Yes, usage of custom compression methods to replace generic non
> type-specific compression method is really not the primary goal of this
> patch.  However, I would consider that as useful side effect.  However, even
> in this case I see some advantages of custom compression methods over initdb
> option.
>
> 1) In order to support alternative compression methods in initdb, we have to
> provide builtin support for them.  Then we immediately run into
> dependencies/incompatible-licenses problem.  Also, we tie appearance of new
> compression methods to our release cycle.  In real life, flexibility means a
> lot.  Giving users freedom to experiment with various compression libraries
> without having to recompile PostgreSQL core is great.
> 2) It's not necessary that users would be satisfied with applying single
> compression method to the whole database cluster.  Various columns may have
> different data distributions with different workloads.  Optimal compression
> type for one column is not necessary optimal for another column.
> 3) Possibility to change compression method on the fly without re-initdb is
> very good too.

I consider custom compression as the way to custom TOAST. For example,
to optimal access
parts of very long document we need to compress slices of document.
Currently, long jsonb
document get compressed and then sliced and that killed all benefits
of binary jsonb.  Also, we are
thinking about "lazy" access to the parts of jsonb from pl's, which is
currently awfully unefficient.

>
>> > Custom datatype-aware compression (e.g. the tsvector)
>> > ----------------------------------------------------------------------
>> >
>> > Exploiting knowledge of the internal data type structure is a
>> > promising way to improve compression ratio and/or performance.
>> >
>> > The obvious question of course is why shouldn't this be done by the
>> > data type code directly, which would also allow additional benefits
>> > like operating directly on the compressed values.
>> >
>> > Another thing is that if the datatype representation changes in some
>> > way, the compression method has to change too. So it's tightly coupled
>> > to the datatype anyway.
>> >
>> > This does not really require any new infrastructure, all the pieces
>> > are already there.
>> >
>> > In some cases that may not be quite possible - the datatype may not be
>> > flexible enough to support alternative (compressed) representation,
>> > e.g. because there are no bits available for "compressed" flag, etc.
>> >
>> > Conclusion: IMHO if we want to exploit the knowledge of the data type
>> > internal structure, perhaps doing that in the datatype code directly
>> > would be a better choice.
>>
>> It could be, but let's imagine there will be internal compression for
>> tsvector. It means that tsvector has two formats now and minus one bit
>> somewhere in the header. After a while we found a better compression
>> but we can't add it because there is already one and it's not good to
>> have three different formats for one type. Or, the compression methods
>> were implemented and we decided to use dictionaries for tsvector (if
>> the user going to store limited number of words). But it will mean that
>> tsvector will go two compression stages (for its internal and for
>> dictionaries).
>
>
> I would like to add that even for single datatype various compression
> methods may have different tradeoffs.  For instance, one compression method
> can have better compression ratio, but another one have faster
> decompression.  And it's OK for user to choose different compression methods
> for different columns.
>
> Depending extensions on datatype internal representation doesn't seem evil
> for me.  We already have bunch of extension depending on much more deeper
> guts of PostgreSQL.  On major release of PostgreSQL, extensions must adopt
> the changes, that is the rule.  And note, the datatype internal
> representation alters relatively rare in comparison with other internals,
> because it's related to on-disk format and ability to pg_upgrade.
>
>> > Custom datatype-aware compression with additional column-specific
>> > metadata (e.g. the jsonb with external dictionary).
>> > ----------------------------------------------------------------------
>> >
>> > Exploiting redundancy in multiple values in the same column (instead
>> > of compressing them independently) is another attractive way to help
>> > the compression. It is inherently datatype-aware, but currently can't
>> > be implemented directly in datatype code as there's no concept of
>> > column-specific storage (e.g. to store dictionary shared by all values
>> > in a particular column).
>> >
>> > I believe any patch addressing this use case would have to introduce
>> > such column-specific storage, and any solution doing that would
>> > probably need to introduce the same catalogs, etc.
>> >
>> > The obvious disadvantage of course is that we need to decompress the
>> > varlena value before doing pretty much anything with it, because the
>> > datatype is not aware of the compression.
>> >
>> > So I wonder if the patch should instead provide infrastructure for
>> > doing that in the datatype code directly.
>> >
>> > The other question is if the patch should introduce some
>> > infrastructure for handling the column context (e.g. column
>> > dictionary). Right now, whoever implements the compression has to
>> > implement this bit too.
>>
>> Column specific storage sounds optional to me. For example compressing
>> timestamp[] using some delta compression will not require it.
>
>
> It's also could be useful to have custom compression method with fixed (not
> dynamically complemented) dictionary.  See [1] for example what other
> databases do.  We may specify fixed dictionary directly in the compression
> method options, I see no problems.  We may also compress that way not only
> jsonb or other special data types, but also natural language texts.  Using
> fixed dictionaries for natural language we can effectively compress short
> texts, when lz and other generic compression methods don't have enough of
> information to effectively train per-value dictionary.
>
> For sure, further work to improve infrastructure is required including
> per-column storage for dictionary and tighter integration between
> compression method and datatype.  However, we are typically deal with such
> complex tasks in step-by-step approach.  And I'm not convinced that custom
> compression methods are bad for the first step in this direction.  For me
> they look clear and already very useful in this shape.

+1

>
> 1.
> https://www.percona.com/doc/percona-server/LATEST/flexibility/compressed_columns.html
>
> ------
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Robert Haas
In reply to this post by Alexander Korotkov
On Mon, Dec 11, 2017 at 1:06 PM, Alexander Korotkov
<[hidden email]> wrote:
> OK, but NOTICE that presumably unexpected table rewrite takes place could be
> still useful.

I'm not going to complain too much about that, but I think that's
mostly a failure of expectation rather than a real problem.  If the
documentation says what the user should expect, and they expect
something else, tough luck for them.

> Also we probably should add some view that will expose compression methods
> whose are currently preserved for columns.  So that user can correctly
> construct SET COMPRESSION query that doesn't rewrites table without digging
> into internals (like directly querying pg_depend).

Yes.  I wonder if \d or \d+ can show it somehow.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Robert Haas
In reply to this post by Tomas Vondra-4
On Mon, Dec 11, 2017 at 2:53 PM, Tomas Vondra
<[hidden email]> wrote:
> But let me play the devil's advocate for a while and question the
> usefulness of this approach to compression. Some of the questions were
> mentioned in the thread before, but I don't think they got the attention
> they deserve.

Sure, thanks for chiming in.  I think it is good to make sure we are
discussing this stuff.

> But perhaps we should simply make it an initdb option (in which case the
> whole cluster would simply use e.g. lz4 instead of pglz)?
>
> That seems like a much simpler approach - it would only require some
> ./configure options to add --with-lz4 (and other compression libraries),
> an initdb option to pick compression algorithm, and probably noting the
> choice in cluster controldata.
>
> No dependencies tracking, no ALTER TABLE issues, etc.
>
> Of course, it would not allow using different compression algorithms for
> different columns (although it might perhaps allow different compression
> level, to some extent).
>
> Conclusion: If we want to offer a simple cluster-wide pglz alternative,
> perhaps this patch is not the right way to do that.

I actually disagree with your conclusion here.   I mean, if you do it
that way, then it has the same problem as checksums: changing
compression algorithms requires a full dump-and-reload of the
database, which makes it more or less a non-starter for large
databases.  On the other hand, with the infrastructure provided by
this patch, we can have a default_compression_method GUC that will be
set to 'pglz' initially.  If the user changes it to 'lz4', or we ship
a new release where the new default is 'lz4', then new tables created
will use that new setting, but the existing stuff keeps working.  If
you want to upgrade your existing tables to use lz4 rather than pglz,
you can change the compression option for those columns to COMPRESS
lz4 PRESERVE pglz if you want to do it incrementally or just COMPRESS
lz4 to force a rewrite of an individual table.  That's really
powerful, and I think users will like it a lot.

In short, your approach, while perhaps a little simpler to code, seems
like it is fraught with operational problems which this design avoids.

> Custom datatype-aware compression (e.g. the tsvector)
> ----------------------------------------------------------------------
>
> Exploiting knowledge of the internal data type structure is a promising
> way to improve compression ratio and/or performance.
>
> The obvious question of course is why shouldn't this be done by the data
> type code directly, which would also allow additional benefits like
> operating directly on the compressed values.
>
> Another thing is that if the datatype representation changes in some
> way, the compression method has to change too. So it's tightly coupled
> to the datatype anyway.
>
> This does not really require any new infrastructure, all the pieces are
> already there.
>
> In some cases that may not be quite possible - the datatype may not be
> flexible enough to support alternative (compressed) representation, e.g.
> because there are no bits available for "compressed" flag, etc.
>
> Conclusion: IMHO if we want to exploit the knowledge of the data type
> internal structure, perhaps doing that in the datatype code directly
> would be a better choice.

I definitely think there's a place for compression built right into
the data type.  I'm still happy about commit
145343534c153d1e6c3cff1fa1855787684d9a38 -- although really, more
needs to be done there.  But that type of improvement and what is
proposed here are basically orthogonal.  Having either one is good;
having both is better.

I think there may also be a place for declaring that a particular data
type has a "privileged" type of TOAST compression; if you use that
kind of compression for that data type, the data type will do smart
things, and if not, it will have to decompress in more cases.  But I
think this infrastructure makes that kind of thing easier, not harder.

> Custom datatype-aware compression with additional column-specific
> metadata (e.g. the jsonb with external dictionary).
> ----------------------------------------------------------------------
>
> Exploiting redundancy in multiple values in the same column (instead of
> compressing them independently) is another attractive way to help the
> compression. It is inherently datatype-aware, but currently can't be
> implemented directly in datatype code as there's no concept of
> column-specific storage (e.g. to store dictionary shared by all values
> in a particular column).
>
> I believe any patch addressing this use case would have to introduce
> such column-specific storage, and any solution doing that would probably
> need to introduce the same catalogs, etc.
>
> The obvious disadvantage of course is that we need to decompress the
> varlena value before doing pretty much anything with it, because the
> datatype is not aware of the compression.
>
> So I wonder if the patch should instead provide infrastructure for doing
> that in the datatype code directly.
>
> The other question is if the patch should introduce some infrastructure
> for handling the column context (e.g. column dictionary). Right now,
> whoever implements the compression has to implement this bit too.

I agree that having a place to store a per-column compression
dictionary would be awesome, but I think that could be added later on
top of this infrastructure.  For example, suppose we stored each
per-column compression dictionary in a separate file and provided some
infrastructure for WAL-logging changes to the file on a logical basis
and checkpointing those updates.  Then we wouldn't be tied to the
MVCC/transactional issues which storing the blobs in a table would
have, which seems like a big win.  Of course, it also creates a lot of
little tiny files inside a directory that already tends to have too
many files, but maybe with some more work we can figure out a way
around that problem.  Here again, it seems to me that the proposed
design is going more in the right direction than the wrong direction:
if some day we have per-column dictionaries, they will need to be tied
to specific compression methods on specific columns.  If we already
have that concept, extending it to do something new is easier than if
we have to create it from scratch.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Tomas Vondra-4


On 12/12/2017 10:33 PM, Robert Haas wrote:

> On Mon, Dec 11, 2017 at 2:53 PM, Tomas Vondra
> <[hidden email]> wrote:
>> But let me play the devil's advocate for a while and question the
>> usefulness of this approach to compression. Some of the questions were
>> mentioned in the thread before, but I don't think they got the attention
>> they deserve.
>
> Sure, thanks for chiming in.  I think it is good to make sure we are
> discussing this stuff.
>
>> But perhaps we should simply make it an initdb option (in which case the
>> whole cluster would simply use e.g. lz4 instead of pglz)?
>>
>> That seems like a much simpler approach - it would only require some
>> ./configure options to add --with-lz4 (and other compression libraries),
>> an initdb option to pick compression algorithm, and probably noting the
>> choice in cluster controldata.
>>
>> No dependencies tracking, no ALTER TABLE issues, etc.
>>
>> Of course, it would not allow using different compression algorithms for
>> different columns (although it might perhaps allow different compression
>> level, to some extent).
>>
>> Conclusion: If we want to offer a simple cluster-wide pglz alternative,
>> perhaps this patch is not the right way to do that.
>
> I actually disagree with your conclusion here.   I mean, if you do it
> that way, then it has the same problem as checksums: changing
> compression algorithms requires a full dump-and-reload of the
> database, which makes it more or less a non-starter for large
> databases.  On the other hand, with the infrastructure provided by
> this patch, we can have a default_compression_method GUC that will be
> set to 'pglz' initially.  If the user changes it to 'lz4', or we ship
> a new release where the new default is 'lz4', then new tables created
> will use that new setting, but the existing stuff keeps working.  If
> you want to upgrade your existing tables to use lz4 rather than pglz,
> you can change the compression option for those columns to COMPRESS
> lz4 PRESERVE pglz if you want to do it incrementally or just COMPRESS
> lz4 to force a rewrite of an individual table.  That's really
> powerful, and I think users will like it a lot.
>
> In short, your approach, while perhaps a little simpler to code, seems
> like it is fraught with operational problems which this design avoids.
>

I agree the checksum-like limitations are annoying and make it
impossible to change the compression algorithm after the cluster is
initialized (although I recall a discussion about addressing that).

So yeah, if such flexibility is considered valuable/important, then the
patch is a better solution.

>> Custom datatype-aware compression (e.g. the tsvector)
>> ----------------------------------------------------------------------
>>
>> Exploiting knowledge of the internal data type structure is a promising
>> way to improve compression ratio and/or performance.
>>
>> The obvious question of course is why shouldn't this be done by the data
>> type code directly, which would also allow additional benefits like
>> operating directly on the compressed values.
>>
>> Another thing is that if the datatype representation changes in some
>> way, the compression method has to change too. So it's tightly coupled
>> to the datatype anyway.
>>
>> This does not really require any new infrastructure, all the pieces are
>> already there.
>>
>> In some cases that may not be quite possible - the datatype may not be
>> flexible enough to support alternative (compressed) representation, e.g.
>> because there are no bits available for "compressed" flag, etc.
>>
>> Conclusion: IMHO if we want to exploit the knowledge of the data type
>> internal structure, perhaps doing that in the datatype code directly
>> would be a better choice.
>
> I definitely think there's a place for compression built right into
> the data type.  I'm still happy about commit
> 145343534c153d1e6c3cff1fa1855787684d9a38 -- although really, more
> needs to be done there.  But that type of improvement and what is
> proposed here are basically orthogonal.  Having either one is good;
> having both is better.
>

Why orthogonal?

For example, why couldn't (or shouldn't) the tsvector compression be
done by tsvector code itself? Why should we be doing that at the varlena
level (so that the tsvector code does not even know about it)?

For example we could make the datatype EXTERNAL and do the compression
on our own, using a custom algorithm. Of course, that would require
datatype-specific implementation, but tsvector_compress does that too.

It seems to me the main reason is that tsvector actually does not allow
us to do that, as there's no good way to distinguish the different
internal format (e.g. by storing a flag or format version in some sort
of header, etc.).

> I think there may also be a place for declaring that a particular data
> type has a "privileged" type of TOAST compression; if you use that
> kind of compression for that data type, the data type will do smart
> things, and if not, it will have to decompress in more cases.  But I
> think this infrastructure makes that kind of thing easier, not harder.
>

I don't quite understand how that would be done. Isn't TOAST meant to be
entirely transparent for the datatypes? I can imagine custom TOAST
compression (which is pretty much what the patch does, after all), but I
don't see how the datatype could do anything smart about it, because it
has no idea which particular compression was used. And considering the
OIDs of the compression methods do change, I'm not sure that's fixable.

>> Custom datatype-aware compression with additional column-specific
>> metadata (e.g. the jsonb with external dictionary).
>> ----------------------------------------------------------------------
>>
>> Exploiting redundancy in multiple values in the same column (instead of
>> compressing them independently) is another attractive way to help the
>> compression. It is inherently datatype-aware, but currently can't be
>> implemented directly in datatype code as there's no concept of
>> column-specific storage (e.g. to store dictionary shared by all values
>> in a particular column).
>>
>> I believe any patch addressing this use case would have to introduce
>> such column-specific storage, and any solution doing that would probably
>> need to introduce the same catalogs, etc.
>>
>> The obvious disadvantage of course is that we need to decompress the
>> varlena value before doing pretty much anything with it, because the
>> datatype is not aware of the compression.
>>
>> So I wonder if the patch should instead provide infrastructure for doing
>> that in the datatype code directly.
>>
>> The other question is if the patch should introduce some infrastructure
>> for handling the column context (e.g. column dictionary). Right now,
>> whoever implements the compression has to implement this bit too.
>
> I agree that having a place to store a per-column compression
> dictionary would be awesome, but I think that could be added later on
> top of this infrastructure.  For example, suppose we stored each
> per-column compression dictionary in a separate file and provided some
> infrastructure for WAL-logging changes to the file on a logical basis
> and checkpointing those updates.  Then we wouldn't be tied to the
> MVCC/transactional issues which storing the blobs in a table would
> have, which seems like a big win.  Of course, it also creates a lot of
> little tiny files inside a directory that already tends to have too
> many files, but maybe with some more work we can figure out a way
> around that problem.  Here again, it seems to me that the proposed
> design is going more in the right direction than the wrong direction:
> if some day we have per-column dictionaries, they will need to be tied
> to specific compression methods on specific columns.  If we already
> have that concept, extending it to do something new is easier than if
> we have to create it from scratch.
>

Well, it wasn't my goal to suddenly widen the scope of the patch and
require it adds all these pieces. My intent was more to point to pieces
that need to be filled in the future.

regards

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

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Chapman Flack
In reply to this post by Robert Haas
On 12/12/2017 04:33 PM, Robert Haas wrote:

> you want to upgrade your existing tables to use lz4 rather than pglz,
> you can change the compression option for those columns to COMPRESS
> lz4 PRESERVE pglz if you want to do it incrementally or just COMPRESS

This is a thread I've only been following peripherally, so forgive
a question that's probably covered somewhere upthread: how will this
be done? Surely not with compression-type bits in each tuple? By
remembering a txid where the compression was changed, and the former
algorithm for older txids?

-Chap

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Robert Haas
In reply to this post by Tomas Vondra-4
On Tue, Dec 12, 2017 at 5:07 PM, Tomas Vondra
<[hidden email]> wrote:
>> I definitely think there's a place for compression built right into
>> the data type.  I'm still happy about commit
>> 145343534c153d1e6c3cff1fa1855787684d9a38 -- although really, more
>> needs to be done there.  But that type of improvement and what is
>> proposed here are basically orthogonal.  Having either one is good;
>> having both is better.
>>
> Why orthogonal?

I mean, they are different things.  Data types are already free to
invent more compact representations, and that does not preclude
applying pglz to the result.

> For example, why couldn't (or shouldn't) the tsvector compression be
> done by tsvector code itself? Why should we be doing that at the varlena
> level (so that the tsvector code does not even know about it)?

We could do that, but then:

1. The compression algorithm would be hard-coded into the system
rather than changeable.  Pluggability has some value.

2. If several data types can benefit from a similar approach, it has
to be separately implemented for each one.

3. Compression is only applied to large-ish values.  If you are just
making the data type representation more compact, you probably want to
apply the new representation to all values.  If you are compressing in
the sense that the original data gets smaller but harder to interpret,
then you probably only want to apply the technique where the value is
already pretty wide, and maybe respect the user's configured storage
attributes.  TOAST knows about some of that kind of stuff.

> It seems to me the main reason is that tsvector actually does not allow
> us to do that, as there's no good way to distinguish the different
> internal format (e.g. by storing a flag or format version in some sort
> of header, etc.).

That is also a potential problem, although I suspect it is possible to
work around it somehow for most data types.  It might be annoying,
though.

>> I think there may also be a place for declaring that a particular data
>> type has a "privileged" type of TOAST compression; if you use that
>> kind of compression for that data type, the data type will do smart
>> things, and if not, it will have to decompress in more cases.  But I
>> think this infrastructure makes that kind of thing easier, not harder.
>
> I don't quite understand how that would be done. Isn't TOAST meant to be
> entirely transparent for the datatypes? I can imagine custom TOAST
> compression (which is pretty much what the patch does, after all), but I
> don't see how the datatype could do anything smart about it, because it
> has no idea which particular compression was used. And considering the
> OIDs of the compression methods do change, I'm not sure that's fixable.

I don't think TOAST needs to be entirely transparent for the
datatypes.  We've already dipped our toe in the water by allowing some
operations on "short" varlenas, and there's really nothing to prevent
a given datatype from going further.  The OID problem you mentioned
would presumably be solved by hard-coding the OIDs for any built-in,
privileged compression methods.

> Well, it wasn't my goal to suddenly widen the scope of the patch and
> require it adds all these pieces. My intent was more to point to pieces
> that need to be filled in the future.

Sure, that's fine.  I'm not worked up about this, just explaining why
it seems reasonably well-designed to me.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Tomas Vondra-4


On 12/13/2017 01:54 AM, Robert Haas wrote:

> On Tue, Dec 12, 2017 at 5:07 PM, Tomas Vondra
> <[hidden email]> wrote:
>>> I definitely think there's a place for compression built right into
>>> the data type.  I'm still happy about commit
>>> 145343534c153d1e6c3cff1fa1855787684d9a38 -- although really, more
>>> needs to be done there.  But that type of improvement and what is
>>> proposed here are basically orthogonal.  Having either one is good;
>>> having both is better.
>>>
>> Why orthogonal?
>
> I mean, they are different things.  Data types are already free to
> invent more compact representations, and that does not preclude
> applying pglz to the result.
>
>> For example, why couldn't (or shouldn't) the tsvector compression be
>> done by tsvector code itself? Why should we be doing that at the varlena
>> level (so that the tsvector code does not even know about it)?
>
> We could do that, but then:
>
> 1. The compression algorithm would be hard-coded into the system
> rather than changeable.  Pluggability has some value.
>

Sure. I agree extensibility of pretty much all parts is a significant
asset of the project.

> 2. If several data types can benefit from a similar approach, it has
> to be separately implemented for each one.
>

I don't think the current solution improves that, though. If you want to
exploit internal features of individual data types, it pretty much
requires code customized to every such data type.

For example you can't take the tsvector compression and just slap it on
tsquery, because it relies on knowledge of internal tsvector structure.
So you need separate implementations anyway.

> 3. Compression is only applied to large-ish values.  If you are just
> making the data type representation more compact, you probably want to
> apply the new representation to all values.  If you are compressing in
> the sense that the original data gets smaller but harder to interpret,
> then you probably only want to apply the technique where the value is
> already pretty wide, and maybe respect the user's configured storage
> attributes.  TOAST knows about some of that kind of stuff.
>

Good point. One such parameter that I really miss is compression level.
I can imagine tuning it through CREATE COMPRESSION METHOD, but it does
not seem quite possible with compression happening in a datatype.

>> It seems to me the main reason is that tsvector actually does not allow
>> us to do that, as there's no good way to distinguish the different
>> internal format (e.g. by storing a flag or format version in some sort
>> of header, etc.).
>
> That is also a potential problem, although I suspect it is possible to
> work around it somehow for most data types.  It might be annoying,
> though.
>
>>> I think there may also be a place for declaring that a particular data
>>> type has a "privileged" type of TOAST compression; if you use that
>>> kind of compression for that data type, the data type will do smart
>>> things, and if not, it will have to decompress in more cases.  But I
>>> think this infrastructure makes that kind of thing easier, not harder.
>>
>> I don't quite understand how that would be done. Isn't TOAST meant to be
>> entirely transparent for the datatypes? I can imagine custom TOAST
>> compression (which is pretty much what the patch does, after all), but I
>> don't see how the datatype could do anything smart about it, because it
>> has no idea which particular compression was used. And considering the
>> OIDs of the compression methods do change, I'm not sure that's fixable.
>
> I don't think TOAST needs to be entirely transparent for the
> datatypes.  We've already dipped our toe in the water by allowing some
> operations on "short" varlenas, and there's really nothing to prevent
> a given datatype from going further.  The OID problem you mentioned
> would presumably be solved by hard-coding the OIDs for any built-in,
> privileged compression methods.
>

Stupid question, but what do you mean by "short" varlenas?


regards

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

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Ildus Kurbangaliev
In reply to this post by Robert Haas
On Tue, 12 Dec 2017 15:52:01 -0500
Robert Haas <[hidden email]> wrote:

>
> Yes.  I wonder if \d or \d+ can show it somehow.
>

Yes, in current version of the patch, \d+ shows current compression.
It can be extended to show a list of current compression methods.

Since we agreed on ALTER syntax, i want to clear things about CREATE.
Should it be CREATE ACCESS METHOD .. TYPE СOMPRESSION or CREATE
COMPRESSION METHOD? I like the access method approach, and it
simplifies the code, but I'm just not sure a compression is an access
method or not.

Current implementation
----------------------

To avoid extra patches I also want to clear things about current
implementation. Right now there are two tables, "pg_compression" and
"pg_compression_opt". When compression method is linked to a column it
creates a record in pg_compression_opt. This record's Oid is stored in
the varlena. These Oids kept in first column so I can move them in
pg_upgrade but in all other aspects they behave like usual Oids. Also
it's easy to restore them.

Compression options linked to a specific column. When tuple is
moved between relations it will be decompressed.

Also in current implementation SET COMPRESSION contains WITH syntax
which is used to provide extra options to compression method.

What could be changed
---------------------

As Alvaro mentioned COMPRESSION METHOD is practically an access method,
so it could be created as CREATE ACCESS METHOD .. TYPE COMPRESSION.
This approach simplifies the patch and "pg_compression" table could be
removed. So compression method is created with something like:

CREATE ACCESS METHOD .. TYPE COMPRESSION HANDLER
awesome_compression_handler;

Syntax of SET COMPRESSION changes to SET COMPRESSION .. PRESERVE which
is useful to control rewrites and for pg_upgrade to make dependencies
between moved compression options and compression methods from pg_am
table.

Default compression is always pglz and if users want to change they run:

ALTER COLUMN <col> SET COMPRESSION awesome PRESERVE pglz;

Without PRESERVE it will rewrite the whole relation using new
compression. Also the rewrite removes all unlisted compression options
so their compresssion methods could be safely dropped.

"pg_compression_opt" table could be renamed to "pg_compression", and
compression options will be stored there.

I'd like to keep extra compression options, for example pglz can be
configured with them. Syntax would be slightly changed:

SET COMPRESSION pglz WITH (min_comp_rate=25) PRESERVE awesome;

Setting the same compression method with different options will create
new compression options record for future tuples but will not
rewrite table.

--
----
Regards,
Ildus Kurbangaliev

Reply | Threaded
Open this post in threaded view
|

Re: Custom compression methods

Álvaro Herrera
In reply to this post by Tomas Vondra-4
Tomas Vondra wrote:

> On 12/13/2017 01:54 AM, Robert Haas wrote:

> > 3. Compression is only applied to large-ish values.  If you are just
> > making the data type representation more compact, you probably want to
> > apply the new representation to all values.  If you are compressing in
> > the sense that the original data gets smaller but harder to interpret,
> > then you probably only want to apply the technique where the value is
> > already pretty wide, and maybe respect the user's configured storage
> > attributes.  TOAST knows about some of that kind of stuff.
>
> Good point. One such parameter that I really miss is compression level.
> I can imagine tuning it through CREATE COMPRESSION METHOD, but it does
> not seem quite possible with compression happening in a datatype.

Hmm, actually isn't that the sort of thing that you would tweak using a
column-level option instead of a compression method?
  ALTER TABLE ALTER COLUMN SET (compression_level=123)
The only thing we need for this is to make tuptoaster.c aware of the
need to check for a parameter.

> > I don't think TOAST needs to be entirely transparent for the
> > datatypes.  We've already dipped our toe in the water by allowing some
> > operations on "short" varlenas, and there's really nothing to prevent
> > a given datatype from going further.  The OID problem you mentioned
> > would presumably be solved by hard-coding the OIDs for any built-in,
> > privileged compression methods.
>
> Stupid question, but what do you mean by "short" varlenas?

Those are varlenas with 1-byte header rather than the standard 4-byte
header.

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

12345678