# OrioleDB

> Next Gen OLTP engine for PostgreSQL
---

[OrioleDB](https://orioledb.com/) 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](https://github.com/orioledb/postgres) with an additional [extension](https://github.com/orioledb/orioledb)

You can run OrioleDB as RDS with pigsty, it is compatible with PG 17 and available on all supported Linux platforms.
The latest version is beta12 over patchset 17_11.


------

## Get Started

Follow the Pigsty [**standard installation**](/docs/install/) and use the [`oriole`](https://github.com/pgsty/pigsty/blob/v3.7.0/conf/oriole.yml) config template.

```bash
curl -fsSL https://repo.pigsty.io/get | bash -s v3.7.0; cd ~/pigsty;
./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

```yaml
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 Extension
```


------

## 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`:

```bash
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:

```sql
-- 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;
```