pg_orca

Overview

PG18 only; use session_preload_libraries=pg_orca for automatic planner hook loading.

Version

Build

You can build the RPM / DEB packages for pg_orca using pig build:

pig build pkg pg_orca         # build RPM / DEB packages

Install

You can install pg_orca directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_orca;          # Install for current active PG version

pig ext install -y pg_orca -v 18  # PG 18

dnf install -y pg_orca_18       # PG 18

apt install -y postgresql-18-pg-orca   # PG 18

Preload:

shared_preload_libraries = 'pg_orca';

Create Extension:

CREATE EXTENSION pg_orca;

Usage

Sources: pgorca README, entrypoint/GUC source, control file.

pg_orca plugs the ORCA cost-based optimizer from the Greenplum/Apache Cloudberry lineage into a standard PostgreSQL 18 server. The upstream README describes this project as PostgreSQL 18-only, and the local package metadata also marks it for PG18 only.

Enable ORCA For A Session

CREATE EXTENSION loads the library in the current session, so the pg_orca.* GUCs and planner hook are available immediately:

CREATE EXTENSION pg_orca;

SET pg_orca.enable_orca = on;

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42
  AND created_at >= now() - interval '30 days';

If ORCA cannot handle a query, the README says it falls back to the standard PostgreSQL planner automatically. Turn on fallback logging while validating a workload:

SET pg_orca.trace_fallback = on;

Preload For New Connections

For automatic planner-hook loading in later sessions, upstream recommends session_preload_libraries, not shared_preload_libraries:

ALTER DATABASE mydb SET session_preload_libraries = 'pg_orca';
ALTER DATABASE mydb SET pg_orca.enable_orca = on;

Existing sessions are unaffected until they reconnect. If another session preload library is already configured, include both values explicitly:

ALTER DATABASE mydb
SET session_preload_libraries = 'pg_orca,pg_stat_statements';

Role-local and cluster-wide scopes are also valid:

ALTER ROLE bench SET session_preload_libraries = 'pg_orca';

ALTER SYSTEM SET session_preload_libraries = 'pg_orca';
SELECT pg_reload_conf();

Tuning And Diagnostics

The README documents these main GUCs:

• pg_orca.enable_orca: enable ORCA; default off.
• pg_orca.trace_fallback: log fallback to the standard planner; default off.
• optimizer_segments: segment count for costing; default 1.
• optimizer_sort_factor: sort cost scaling; default 1.0.
• optimizer_metadata_caching: cache relation metadata; default on.
• optimizer_mdcache_size: metadata cache size in KB; default 16384.
• optimizer_search_strategy_path: optional custom search strategy XML path.

The entrypoint source also defines additional ORCA tuning and debug GUCs such as optimizer_join_order, pg_orca.join_order_dynamic_threshold, pg_orca.cost_model, and optimizer_print_*. Treat those as workload/debug knobs and validate plans before keeping them in a persistent database setting.

Caveats

• PostgreSQL 18 only.
• Use session_preload_libraries = 'pg_orca' for automatic loading in new sessions.
• ORCA is disabled by default after loading; set pg_orca.enable_orca = on.
• Fallback to the PostgreSQL planner is expected for unsupported queries; enable pg_orca.trace_fallback when checking coverage.