Date: Fri, 24 Nov 2017 12:38:00 +0300
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>
Subject: Re: [HACKERS] Custom compression methods
Message-ID: <20171124123800.034c9208@wp.localdomain>
In-Reply-To: <57daf28d-ed76-c364-a9ca-65d0ff71a36f@2ndquadrant.com>
References: <20170907194236.4cefce96@wp.localdomain>
	<20170912175505.4afa11fd@wp.localdomain>
	<20171102152836.60c041e4@wp.localdomain>
	<20171114162356.52e3d388@wp.localdomain>
	<62e46a47-08a8-6e06-5e4f-6f52e0a13202@2ndquadrant.com>
	<20171121174717.69ecd8f4@wp.localdomain>
	<bfb3d34e-ba74-02cb-ca2a-43344866d9fd@2ndquadrant.com>
	<20171123123849.5a686d7b@wp.localdomain>
	<57daf28d-ed76-c364-a9ca-65d0ff71a36f@2ndquadrant.com>
Organization: Postgres Professional
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Precedence: bulk

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