cancel
Showing results for 
Search instead for 
Did you mean: 

Deep dive Postgres at the #pgconfeu conference

Level 2 Adventurer

Today I followed many good technical sessions at the European Postgres conference. The Postgres conferences are really technical oriented, you will find no marketing sessions there and you learn a lot of things.


As promised yesterday, I wrote today my first blog about the new Postgres storage engine ZHEAP/UNDO, which is a very interesting feature, with very interesting results.

Before you continue to read this blog, if you didn’t read my blog from yesterday, read it first :-) link

 

First test: table creation

 

We create 2 tables, one with the default Postgres storage engine HEAP, and one with the new storage engine ZHEAP.

 

1
2
3
4
5
6
7
8
PSQL> create table heap2 as select a.*, md5(a::varchar), now() from generate_series(1,5000000) a;
  
SELECT 5000000
Time: 12819.369 ms (00:12.819)
 
PSQL> create table zheap2  with (storage_engine='zheap') as select a.*, md5(a::varchar), now() from generate_series(1,5000000) a;
SELECT 5000000
Time: 19155.004 ms (00:19.155)

 

You noticed, that with Postgres you can choose your storage engine at table level :-). The table creation with ZHEAP is slower but is normal because now we have to create the UNDO segment also.

 

Second test: Size of both tables

 

Before to start the tests we will check the size of the HEAP and ZHEAP tables, as announced yesterday the HEAP table should be smaller, because we have less header information.

 

1
2
3
4
5
6
7
8
PSQL>  select pg_size_pretty(pg_relation_size('heap2'));
 pg_size_pretty
----------------
 365 MB
PSQL> select pg_size_pretty(pg_relation_size('zheap2'));
 pg_size_pretty
----------------
 289 MB

 

The ZHEAP tables is smaller, it exactly what Amit explain us yesterday because the block header with ZHEAP is smaller. If you want to learn more read his presentation from yesterday. Again the link is on my blog from yesterday.

 

Third test: Update on the table

 

To get the bloat effect on the HEAP table, we will now update the full table and see what happen.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
PSQL> update heap2 set a=a+12222222;
UPDATE 5000000
Time: 19834.911 ms (00:19.835)
 
PSQL> update zheap2 set a=a+12222222;
UPDATE 5000000
Time: 26956.043 ms (00:26.956)
 
PSQL> select pg_size_pretty(pg_relation_size('zheap2'));
 pg_size_pretty
----------------
 289 MB
PSQL> vacuum heap2;
PSQL> select pg_size_pretty(pg_relation_size('heap2'));
 pg_size_pretty
----------------
 730 MB

 

The same as for the creation the update is a bit longer, but the update with ZHEAP write many information into the log file. We should test again this update with disabling the writing of information into the log file about creating undo segment.
But as you can see, the most important information here is that the table don’t bloat as the HEAP table, now the HEAP table is 2 times bigger despite I executed a VACUUM.

 

Fourth test: test of the ROLLBACK

 

To test the ROLLBACK we have to open first a transaction with BEGIN;

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
PSQL>  begin;
BEGIN
PSQL>* update heap2 set a=a+12222222;
UPDATE 5000000
Time: 22071.462 ms (00:22.071)
PSQL> * rollback;
ROLLBACK
Time: 1.437 ms
 
PSQL> begin;
BEGIN
PSQL> * update zheap2 set a=a+12222222;
UPDATE 5000000
Time: 28210.845 ms (00:28.211)
PSQL> * rollback;
ROLLBACK
Time: 0.567 ms

 

This is the part where I’m the most surprised, the ROLLBACK for ZHEAP is so fast as for HEAP, I can’t explain that. I will leave my colleague Daniel Westermann making deeper tests :-). Because with ZHEAP he has to apply the undo blocks, where HEAP tables only mark the transactions as aborted.

 

Fifth tests: Check of the query performances

 

For this test we have to first flush the filesystem cache and to restart the database, to be sure that nothing is cached.

 

1
2
3
4
5
6
7
8
9
10
postgres@dbi-pg-tun:/home/postgres/zheap/ [ZHEAP] pgstop
waiting for server to shut down.... done
server stopped
 
postgres@dbi-pg-tun:/home/postgres/ [ZHEAP] sudo sync
postgres@dbi-pg-tun:/home/postgres/ [ZHEAP] sudo echo 3 > /proc/sys/vm/drop_caches
 
postgres@dbi-pg-tun:/home/postgres/zheap/ [ZHEAP] pgstart
waiting for server to start.... done
server started

 

Now we are ready for the last test

 

1
2
3
4
5
6
7
8
9
10
11
12
postgres@dbi-pg-tun:/home/postgres/zheap/ [ZHEAP] sqh
PSQL> select count(*) from heap2;
  count 
---------
 5000000
Time: 3444.869 ms (00:03.445)
 
PSQL> select count(*) from zheap2;
  count 
---------
 5000000
Time: 593.894 ms

 

As you can see the query performance are improved significantly for full table scan :-) because the table didn’t bloat as for the HEAP table. For your information, I started additionally 2 times a full update before to restart the database and the HEAP table is now 3 times bigger.

 

1
2
3
4
5
6
7
8
9
10
PSQL> select pg_size_pretty(pg_relation_size('heap2'));
 pg_size_pretty
----------------
 1095 MB
 
Time: 0.508 ms
PSQL> select pg_size_pretty(pg_relation_size('zheap2'));
 pg_size_pretty
----------------
 289 MB

 

Conclusion of these tests

 

  • Postgres allow the usage or not of UNDO’s at the table level
  • We are surprised how fast the ROLLBACK are, but this must be tested again, I don’t understand why
  • Select performance are improved significantly for full table scan:-)
  • The storage will not bloat anymore with ZHEAP
  • Finally, only the updates are a little bit slower

It will be interesting to follow the discussions around this feature on the mailing list.

 

(Article originally published - Wednesday, October 6, 2018, at https://blog.dbi-services.com/deep-dive-postgres-at-the-pgconfeu-conference/)