Making Postgres better with OrioleDB

#postgres #database #storageengines

One of the biggest advantages Postgres has over lots of other DBMSs out there is the fact that it allows external improvements or new features, without requiring any interactions with the core codebase, via extensions. There are many extensions each with a unique purpose(I have also written two trivial extensions pg_wal_ext and pg_table_bloat )

One extension that especially stands out is OrioleDB. It provides an alternative storage engine to Postgres. Postgres comes with only one storage engine based on the heap. This is different from MySQL which comes with several storage engines: innodb, myissam etc.

Postgres heap based storage engine works well in most cases but it also presents a number of issues, some of which are: bloat introduced by how updates are handled, the need for garbage collection(vacuum), transaction wraparound

OrioleDB comes with the promise of solving the issues presented by the Postgres heap. I tested it out and the results were pretty good. I'll describe the process I used and the results below.

First you need a patched up version of Postgres: 16(tag:patches16_33) or 17(tag:patches17_5). The steps are available on github

The test is for a read only workload on 16 tables each with 25M records(approx 94GB for each setup). I used a server with 64GB RAM, 20cores and 500GB NVME SSD(ext4 filesystem). I use my setup scripts to build and install Postgres. The benchmarking tool used is sysbench. The OrioleDB used is built from source from the main branch commit 0c484c4.

The install steps for OrioleDB:

# Install dependencies
apt update
apt install python3 python3-dev python3-pip python3-setuptools python3-testresources libzstd1 libzstd-dev libssl-dev libcurl4-openssl-dev

git clone https://github.com/orioledb/orioledb
cd orioledb
git reset --hard 0c484c4 # optional
make USE_PGXS=1 ORIOLEDB_PATCHSET_VERSION=5
echo "shared_preload_libraries = 'orioledb.so'" >> /usr/local/pgsql/data/postgresql.conf

The buffer pool for both setups is set to 16GB i.e shared_buffers for heap and orioledb.main_buffers for Orioledb.

To run the actual tests, I used https://github.com/misachi/sysbench-graphing-tests. Once everything has been setup, running ./run.threads <run number> pgsql should execute the tests. The script ensures the database is warmed up before running the tests.

I did 2 test runs for each setup: 2 runs of Postgres heap and 2 runs of Postgres with OrioleDB extension. The results are as shown below

Runs labelled out/res2 and out/res3 show results for running normal Postgres with heap based storage engine while res4 and res5 show results for Postgres with OrioleDB.

The impressive bit, OrioleDB is able to outperform Postgres heap while using less CPU and memory resources. This can be attributed partly to OrioleDB's lock-free page reads thus reduced contention.

Memory usage(MB) for Postgres heap