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 enfine ZHEAP.
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 is normal because now we have to create the UNDO segment also.
Second test : Size of the 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.
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.
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;
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.
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
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 you information I started additionally 2 times a full update before to restart the database and the HEAP table is now 3 times bigger.
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 surprise 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.