Re: zheap: a new storage format for PostgreSQL

From: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
To: Daniel Westermann <daniel(dot)westermann(at)dbi-services(dot)com>
Cc: Adam Brusselback <adambrusselback(at)gmail(dot)com>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: Re: zheap: a new storage format for PostgreSQL
Date: 2018-11-20 03:02:13
Message-ID: CAA4eK1K-1zuAnbUnM6xtXtTGQ2uBZhcu6O34B7kYboVZk536nw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Nov 19, 2018 at 6:36 PM Daniel Westermann
<daniel(dot)westermann(at)dbi-services(dot)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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2018-11-20 03:06:00 Re: New function pg_stat_statements_reset_query() to reset statistics of a specific query
Previous Message Andres Freund 2018-11-20 02:32:18 Re: More issues with pg_verify_checksums and checksum verification in base backups