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
Date: Fri, 24 Nov 2017 12:38:00 +0300
Message-ID: <20171124123800.034c9208@wp.localdomain> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: i.kurbangaliev@postgrespro.ru, tomas.vondra@2ndquadrant.com, i.musin@postgrespro.ru
Subject: Re: [HACKERS] Custom compression methods
In-Reply-To: <20171124123800.034c9208@wp.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox