Trying out OrioleDB

I am curious about OrioleDB given my unsuccessful attempts to resolve some Postgres perf issues for the Insert Benchmark via tuning. 

The first step is to compile and install their Postgres fork from source.

  git clone https://github.com/orioledb/postgres.git oriole_pg

  cd oriole_pg
  git checkout patches16_18
  bash mk.pg.o3_native_lto ~/d/pg16oriole
  make install
  cd contrib/pgstattuple
  make install

  

The nexts step is to compile and install the OrioleDB extension from source. It has to find pg_config so I updated my PATH for that.

  PATH=$PATH:~/d/pg16oriole
  make USE_PGXS=1
  make USE_PGXS=1 install

  # installcheck requires testgres

  pip3 install testgres
  make USE_PGXS=1 installcheck

There is a test failure during make installcheck (see issue 275) but that doesn't matter to me today. I am not trying to use this in production.

The next step is to modify my Postgres init scripts so I can use OrioleDB. I lack many basic PG admin skills and stumbled my way through the locales issue. I hope OrioleDB updates their README to explain that.

  cd ~/d/pg16oriole

  # Add this to the Postgres configuration file
  # shared_preload_libraries = 'orioledb.so'

  # Add this to my init scripts. 
  #    First make the test database use the C locale because OrioleDB

  #        tables must use ICO, C or POSIX locales.

  #    For this to work I must use template0.
  bin/createdb ib -l "C" -T template0

  # Add this to my init scripts.

  bin/psql ib -c 'create extension orioledb'

  # Finally initialize Postgres
  bash ${insert-init-script-here}

The final step is to modify my benchmark clients to create OrioleDB tables and that is done by appending using orioledb to the end of the CREATE TABLE statements.

Configuration

The next step is to tune some of the OrioleDB options based on the advice here.

I am using this on a small (8 cores, 16G RAM) server.