OrioleDB is a PostgreSQL storage engine extension.
It supports data storage in S3. Its MVCC uses UNDO logs to hold older row versions, reducing bloat in primary storage, and 64-bit transaction IDs avoid traditional XID wraparound. See the upstream design for implementation details.
Pigsty supports OrioleDB for PostgreSQL 16, 17, and 18. The current PG18 package baseline is OrioleDB 1.10.3, based on PostgreSQL 18.6.
You can run OrioleDB as an RDS with Pigsty.
Current packages support PostgreSQL 16, 17, and 18 on supported Linux platforms.
Set pg_mode to oriole to use the /usr/oriole-$v installation path.
The orioledb package alias resolves to versioned kernel packages, such as orioledb-16, orioledb-17, and orioledb-18.
The current Pigsty package line is OrioleDB 1.8 beta16.
curl -fsSL https://repo.pigsty.io/get | bash;cd ~/pigsty;./configure -c fork/oriole # use the OrioleDB template./deploy.yml # Install Pigsty with OrioleDB
For production deployment, change the password parameters in pigsty.yml before you run the install playbook.
Configuration
pg-meta:hosts:10.10.10.10:{pg_seq:1, pg_role:primary }vars:pg_cluster:pg-metapg_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'}pg_crontab:['00 01 * * * /pg/bin/pg-backup full']# Full backup at 1 AM daily# OrioleDB specific settingspg_mode:oriole # oriole compatibility modepg_version:18# OrioleDB supports PG 16, 17, and 18pg_packages:[orioledb, pgsql-common ] # Install OrioleDB kernelpg_libs:'orioledb, pg_stat_statements, auto_explain'# Load OrioleDB extension
Usage
To use OrioleDB, install the orioledb package alias.
Pigsty uses pg_version to select the PG16, PG17, or PG18 OrioleDB kernel package.
Initialize TPC-B-like tables with pgbench using 100 warehouses:
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 meta
Next, rebuild these tables with the orioledb storage engine and compare performance:
-- Create OrioleDB tables
CREATETABLEpgbench_accounts_o(LIKEpgbench_accountsINCLUDINGALL)USINGorioledb;CREATETABLEpgbench_branches_o(LIKEpgbench_branchesINCLUDINGALL)USINGorioledb;CREATETABLEpgbench_history_o(LIKEpgbench_historyINCLUDINGALL)USINGorioledb;CREATETABLEpgbench_tellers_o(LIKEpgbench_tellersINCLUDINGALL)USINGorioledb;-- Copy data from regular tables to OrioleDB tables
INSERTINTOpgbench_accounts_oSELECT*FROMpgbench_accounts;INSERTINTOpgbench_branches_oSELECT*FROMpgbench_branches;INSERTINTOpgbench_history_oSELECT*FROMpgbench_history;INSERTINTOpgbench_tellers_oSELECT*FROMpgbench_tellers;-- Drop original tables and rename OrioleDB tables
DROPTABLEpgbench_accounts,pgbench_branches,pgbench_history,pgbench_tellers;ALTERTABLEpgbench_accounts_oRENAMETOpgbench_accounts;ALTERTABLEpgbench_branches_oRENAMETOpgbench_branches;ALTERTABLEpgbench_history_oRENAMETOpgbench_history;ALTERTABLEpgbench_tellers_oRENAMETOpgbench_tellers;
Key Features
No XID Wraparound: Eliminates transaction ID wraparound maintenance
No Table Bloat: Advanced storage management prevents table bloat
Cloud Storage: Native support for S3-compatible object storage
OLTP Optimized: Designed for transactional workloads
Improved Performance: Better space utilization and query performance
Note: Currently in Beta stage - thoroughly evaluate before production use.
Available Extensions
The table lists selected non-Contrib extensions for reference. Query the target instance to confirm actual availability and versions.