OrioleDB
Next Gen OLTP engine for PostgreSQL
OrioleDB is a PostgreSQL storage engine extension that claims to deliver 4x OLTP performance without the xid wraparound & table bloat, and “cloud native” (data on s3) capabilities.
The latest version of OrioleDB is based on a Patched PostgreSQL 17.0 with an additional extension
You can run OrioleDB as RDS with pigsty, it is compatible with PG 17 and available on all supported Linux platforms.
Get Started
Follow the Pigsty standard installation and use the oriole config template.
./configure -c oriole # Use the OrioleDB configuration template
./install.yml # Install Pigsty with OrioleDBFor production deployments, make sure to modify the password parameters in the pigsty.yml config before running the install playbook.
Configuration
pg-meta:
hosts:
10.10.10.10: { pg_seq: 1, pg_role: primary }
vars:
pg_cluster: pg-meta
pg_users:
- {name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [dbrole_admin] ,comment: pigsty admin user }
- {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
pg_databases:
- {name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty], extensions: [orioledb]}
pg_hba_rules:
- {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}
node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am
# OrioleDB Ad Hoc Settings
pg_mode: oriole # oriole compatible mode
pg_packages: [ orioledb, pgsql-common ] # install OrioleDB kernel
pg_libs: 'orioledb, pg_stat_statements, auto_explain' # Load OrioleDB ExtensionUsage
To use OrioleDB, you need to install the orioledb_17 and oriolepg_17 packages (currently only available as RPMs).
Initialize TPC-B-like tables with 100 warehouses using pgbench:
pgbench -is 100 meta
pgbench -nv -P1 -c10 -S -T1000 meta
pgbench -nv -P1 -c50 -S -T1000 meta
pgbench -nv -P1 -c10 -T1000 meta
pgbench -nv -P1 -c50 -T1000 metaNext, you can rebuild these tables using the orioledb storage engine and observe the performance differences:
-- Create OrioleDB tables
CREATE TABLE pgbench_accounts_o (LIKE pgbench_accounts INCLUDING ALL) USING orioledb;
CREATE TABLE pgbench_branches_o (LIKE pgbench_branches INCLUDING ALL) USING orioledb;
CREATE TABLE pgbench_history_o (LIKE pgbench_history INCLUDING ALL) USING orioledb;
CREATE TABLE pgbench_tellers_o (LIKE pgbench_tellers INCLUDING ALL) USING orioledb;
-- Copy data from regular tables to OrioleDB tables
INSERT INTO pgbench_accounts_o SELECT * FROM pgbench_accounts;
INSERT INTO pgbench_branches_o SELECT * FROM pgbench_branches;
INSERT INTO pgbench_history_o SELECT * FROM pgbench_history;
INSERT INTO pgbench_tellers_o SELECT * FROM pgbench_tellers;
-- Drop original tables and rename OrioleDB tables
DROP TABLE pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers;
ALTER TABLE pgbench_accounts_o RENAME TO pgbench_accounts;
ALTER TABLE pgbench_branches_o RENAME TO pgbench_branches;
ALTER TABLE pgbench_history_o RENAME TO pgbench_history;
ALTER TABLE pgbench_tellers_o RENAME TO pgbench_tellers;