Horton

Re: [HACKERS] Custom compression methods

Ildus Kurbangaliev <i.kurbangaliev@postgrespro.ru>

View thread View on postgr.es
From: Ildus Kurbangaliev <i.kurbangaliev@postgrespro.ru>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org, Ildar Musin <i.musin@postgrespro.ru>
Date: 2017-11-24T09:38:00Z
Lists: pgsql-hackers 
On Thu, 23 Nov 2017 21:54:32 +0100
Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
 
> 
> Hmm, this seems to have fixed it, but only in one direction. Consider
> this:
> 
>     create table t_pglz (v text);
>     create table t_lz4 (v text compressed lz4);
> 
>     insert into t_pglz select repeat(md5(i::text),300)
>     from generate_series(1,100000) s(i);
> 
>     insert into t_lz4 select repeat(md5(i::text),300)
>     from generate_series(1,100000) s(i);
> 
>     \d+
> 
>      Schema |  Name  | Type  | Owner | Size  | Description
>     --------+--------+-------+-------+-------+-------------
>      public | t_lz4  | table | user  | 12 MB |
>      public | t_pglz | table | user  | 18 MB |
>     (2 rows)
> 
>     truncate t_pglz;
>     insert into t_pglz select * from t_lz4;
> 
>     \d+
> 
>      Schema |  Name  | Type  | Owner | Size  | Description
>     --------+--------+-------+-------+-------+-------------
>      public | t_lz4  | table | user  | 12 MB |
>      public | t_pglz | table | user  | 18 MB |
>     (2 rows)
> 
> which is fine. But in the other direction, this happens
> 
>     truncate t_lz4;
>     insert into t_lz4 select * from t_pglz;
> 
>      \d+
>                        List of relations
>      Schema |  Name  | Type  | Owner | Size  | Description
>     --------+--------+-------+-------+-------+-------------
>      public | t_lz4  | table | user  | 18 MB |
>      public | t_pglz | table | user  | 18 MB |
>     (2 rows)
> 
> which means the data is still pglz-compressed. That's rather strange,
> I guess, and it should compress the data using the compression method
> set for the target table instead.

That's actually an interesting issue. It happens because if tuple fits
to page then postgres just moves it as is. I've just added
recompression if it has custom compressed datums to keep dependencies
right. But look:

  create table t1(a text);
  create table t2(a text);
  alter table t2 alter column a set storage external;
  insert into t1 select repeat(md5(i::text),300) from
    generate_series(1,100000) s(i);
  \d+

                      List of relations 
   Schema | Name | Type  | Owner |    Size    | Description 
  --------+------+-------+-------+------------+-------------
   public | t1   | table | ildus | 18 MB      | 
   public | t2   | table | ildus | 8192 bytes | 
  (2 rows)

  insert into t2 select * from t1;

  \d+

                    List of relations
   Schema | Name | Type  | Owner | Size  | Description 
  --------+------+-------+-------+-------+-------------
   public | t1   | table | ildus | 18 MB | 
   public | t2   | table | ildus | 18 MB | 
  (2 rows)

That means compressed datums now in the column with storage specified as
external. I'm not sure that's a bug or a feature. Lets insert them
usual way:

  delete from t2;
  insert into t2 select repeat(md5(i::text),300) from
    generate_series(1,100000) s(i);
  \d+

                     List of relations
   Schema | Name | Type  | Owner |  Size   | Description 
  --------+------+-------+-------+---------+-------------
   public | t1   | table | ildus | 18 MB   | 
   public | t2   | table | ildus | 1011 MB | 

Maybe there should be more common solution like comparison of attribute
properties?

-- 
---
Ildus Kurbangaliev
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

Commits

  1. docs: Update TOAST storage docs for configurable compression.

  2. Further tweaking of pg_dump's handling of default_toast_compression.

  3. Fix interaction of TOAST compression with expression indexes.

  4. Tidy up more loose ends related to configurable TOAST compression.

  5. Short-circuit slice requests that are for more than the object's size.

  6. Mostly-cosmetic adjustments of TOAST-related macros.

  7. Remove useless configure probe for <lz4/lz4.h>.

  8. Error on invalid TOAST compression in CREATE or ALTER TABLE.

  9. docs: Fix omissions related to configurable TOAST compression.

  10. More code cleanup for configurable TOAST compression.

  11. Bring configure support for LZ4 up to snuff.

  12. Make compression.sql regression test independent of default.

  13. Use valid compression method in brin_form_tuple

  14. Fix up pg_dump's handling of per-attribute compression options.

  15. Allow configurable LZ4 TOAST compression.

  16. Fix inconsistencies in the code

  17. Mostly-cosmetic improvements in memory chunk header alignment coding.

  18. Allow numeric to use a more compact, 2-byte header in many cases.