Postgres Pro
Downloads
Home   >   mailing lists

Re: Deleting older versions in unique indexes to avoid page splits - Mailing list pgsql-hackers

From Victor Yegorov
Subject Re: Deleting older versions in unique indexes to avoid page splits
Date
Msg-id CAGnEbogATZS1mWMVX8FzZHMXzuDEcb10AnVwwhCtXtiBpg3XLQ@mail.gmail.com
Whole thread Raw
In response to Re: Deleting older versions in unique indexes to avoid page splits  (Victor Yegorov <vyegorov@gmail.com>)
List pgsql-hackers
Tree view
пн, 4 янв. 2021 г. в 17:28, Victor Yegorov <vyegorov@gmail.com>:
I created another setup (see attached testcases). Basically, I emulated queue operations(INSERT at the end and DELETE 

Sorry, hit Send too early.

So, I emulated queue operations(INSERT at the end and DELETE from the head). And also made 5-minute transactions
appear in the background for the whole duration of the test. 3 pgbench were run in parallel on a scale 3000 bench database
with modifications (attached).

Master
------

        relname        |   nrows   | blk_before | mb_before | blk_after | mb_after |  diff
-----------------------+-----------+------------+-----------+-----------+----------+--------
 pgbench_accounts      | 300000000 |    4918033 |   38422.1 |   5065575 |  39574.8 |  +3.0%
 accounts_mtime        | 300000000 |    1155119 |    9024.4 |   1287656 |  10059.8 | +11.5%
 fiver                 | 300000000 |     427039 |    3336.2 |    567755 |   4435.6 | +33.0%
 pgbench_accounts_pkey | 300000000 |     822573 |    6426.4 |   1033344 |   8073.0 | +25.6%
 score                 | 300000000 |     284022 |    2218.9 |    458502 |   3582.0 | +61.4%
 tenner                | 300000000 |     346050 |    2703.5 |    417985 |   3265.5 | +20.8%
(6 rows)

DB size: 65.2..72.3 (+7.1GB / +10.9%)
TPS: 2297 / 495

Patched
------
        relname        |   nrows   | blk_before | mb_before | blk_after | mb_after |  diff
-----------------------+-----------+------------+-----------+-----------+----------+--------
 pgbench_accounts      | 300000000 |    4918033 |   38422.1 |   5067500 |  39589.8 |  +3.0%
 accounts_mtime        | 300000000 |    1155119 |    9024.4 |   1283441 |  10026.9 | +11.1%
 fiver                 | 300000000 |     427039 |    3336.2 |    429101 |   3352.4 |  +0.5%
 pgbench_accounts_pkey | 300000000 |     822573 |    6426.4 |    826056 |   6453.6 |  +0.4%
 score                 | 300000000 |     284022 |    2218.9 |    285465 |   2230.2 |  +0.5%
 tenner                | 300000000 |     346050 |    2703.5 |    347695 |   2716.4 |  +0.5%
(6 rows)

DB size: 65.2..67.5 (+2.3GB / +3.5%)
TPS: 2216 / 492

As you can see, TPS are very much similar, but the fact that we have no bloat for the patched version makes me very happy!

On the graphs, you can clearly see extra write activity performed by the backedns of the patched version.
 

--
Victor Yegorov
Attachment

pgsql-hackers by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: pg_waldump/heapdesc.c and struct field names
Next
From: Thomas Munro
Date:
Subject: Re: Context diffs