OrioleDB (OLTP)
Module:
OrioleDB is a PostgreSQL storage engine extension that delivers 4x OLTP performance without 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 additional extension
Currently, Pigsty has OrioleDB RDS support on EL 8/9 systems. Support for Debian/Ubuntu systems will be available in future releases.
Get Started
Follow the Pigsty standard installation and use the oriole config template.
curl -fsSL https://repo.pigsty.cc/get | bash; cd ~/pigsty ./bootstrap # Prepare Pigsty dependencies ./configure -c oriole # Use the OrioleDB configuration template ./install.yml # Install Pigsty with OrioleDB For production deployments, make sure to modify the password parameters in the pigsty.yml config before running the install playbook.
Configuration
all: children: pg-orio: vars: pg_databases: - {name: meta ,extensions: [orioledb]} vars: pg_mode: oriole pg_version: 17 pg_packages: [ orioledb, pgsql-common ] pg_libs: 'orioledb.so, pg_stat_statements, auto_explain' repo_extra_packages: [ orioledb ] Usage
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 meta Next, 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; Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.