Re: zheap: a new storage format for PostgreSQL - Mailing list pgsql-hackers

From Amit Kapila
Subject Re: zheap: a new storage format for PostgreSQL
Date
Msg-id CAA4eK1K-1zuAnbUnM6xtXtTGQ2uBZhcu6O34B7kYboVZk536nw@mail.gmail.com
Whole thread Raw
In response to Re: zheap: a new storage format for PostgreSQL  (Daniel Westermann <daniel.westermann@dbi-services.com>)
List pgsql-hackers
On Mon, Nov 19, 2018 at 6:36 PM Daniel Westermann
<daniel.westermann@dbi-services.com> wrote:
>
> >Yes, we need to connect to the database for performing rollback
> >actions.  Once the rollback for that database is over, undo apply
> >worker will exit and you should be able to drop the database.
>
> Thank you, Amit.
> Can you have a look at this one?
>
> create table t1 ( a text ) partition by list (a);
> create table t1_1 PARTITION of t1 (a) for values in ('a');
> create table t1_2 PARTITION of t1 (a) for values in ('b');
> create table t1_3 PARTITION of t1 (a) for values in ('c');
> create table t1_4 PARTITION of t1 (a) default;
>
> postgres=# \d+ t1
>                                    Table "public.t1"
>  Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description
> --------+------+-----------+----------+---------+----------+--------------+-------------
>  a      | text |           |          |         | extended |              |
> Partition key: LIST (a)
> Partitions: t1_1 FOR VALUES IN ('a'),
>             t1_2 FOR VALUES IN ('b'),
>             t1_3 FOR VALUES IN ('c'),
>             t1_4 DEFAULT
> Options: storage_engine=zheap
>
>
> insert into t1 select 'a' from generate_series ( 1, 1000000 );
> insert into t1 select 'b' from generate_series ( 1, 1000000 );
> insert into t1 select 'c' from generate_series ( 1, 1000000 );
>
> postgres=# begin;
> BEGIN
> postgres=# update t1 set a = 'd' where a = 'a';
> UPDATE 1000000
> postgres=# rollback;
> ROLLBACK
>

Here, you are doing a big rollback, so I guess it will be pushed to
background unless you increase the value of 'rollback_overflow_size'.
You can confirm that by checking if any undo apply worker is active
and rollback finishes immediately.

> postgres=# select * from t1 where a = 'd';
> postgres=# select * from t1 where a = 'd';
> postgres=# select * from t1 where a = 'd';
>
> The selects at the end take seconds
>

I think what is happening is as rollback is still in progress, the
scan needs to fetch the data from undo and it will be slow.

> and a lot of checkpoints are happening.
>

It is because Rollbacks also write WAL and you are doing a big
Rollback which will lead to re-write of the entire table.

I guess if you allow rollback to complete before issuing a select, you
will see better results.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: More issues with pg_verify_checksums and checksum verificationin base backups
Next
From: Amit Kapila
Date:
Subject: Re: New function pg_stat_statements_reset_query() to reset statisticsof a specific query