postr

postr is a PostgreSQL extension built with pgrx that lets you run Ruby inside PostgreSQL.

It is currently an untrusted, superuser-only prototype. It is not a sandbox and should not be treated like a safe multi-tenant procedural language runtime.

At a glance:

• PostgreSQL loads postr and registers a native LANGUAGE ruby
• SQL functions execute Ruby through a def call(...) entrypoint
• Ruby code can query PostgreSQL through the Postr helper module
• external apps can call those functions over the normal PostgreSQL wire protocol

Why it exists:

postr is for cases where you want Ruby business logic, triggers, and set-returning functions to live next to PostgreSQL data, but still be callable from ordinary SQL or any external client that can speak the PostgreSQL protocol.

If you are evaluating the repo, start here:

• read the showcase example: examples/11-order-decision-engine.sql
• run the app-side client: examples/12-pg-order-decision-engine.rb
• skim the cookbook index: examples/README.md
• read the security model before treating this like a production PL: Security

This first cut is intentionally narrow:

• It uses pgrx for the extension surface, catalog access, and the procedural language handler.
• It embeds Ruby into the PostgreSQL backend process with magnus.
• It supports native LANGUAGE ruby functions.
• It maps PostgreSQL values to native Ruby objects for the main scalar, array, composite, network, and built-in range families used by LANGUAGE ruby calls.
• It is a prototype, not a trusted procedural language implementation.

Status

This repo now contains:

• a pgrx extension crate layout
• an embedded Ruby VM initialized lazily inside each PostgreSQL backend process
• a native ruby procedural language registered by the extension
• a Ruby-first def call(...) execution model for native LANGUAGE ruby functions
• DO LANGUAGE ruby inline block support
• native RETURNS trigger support for row and statement triggers
• native RETURNS SETOF support for iterable and yield-driven result production
• native record argument support for named composites and anonymous ROW(...) values
• native RETURNS record and RETURNS SETOF record support with call-site row descriptors
• native OUT, INOUT, and TABLE signature support
• native VARIADIC argument support with Ruby array binding
• native scalar, array, composite, network, and built-in range argument/return mapping for LANGUAGE ruby
• cleaner Ruby exception formatting with stable postr://... source labels
• a backend-local cache of compiled Ruby callables and dispatch shapes for native LANGUAGE ruby functions
• a Ruby-visible Postr prelude for SQL, messaging, quoting, and subtransaction helpers
• a curated Ruby require policy backed by PostgreSQL settings
• a backend-local cache of the canonical Ruby security policy so unchanged GUC state avoids full runtime-policy rebuilds on every call
• constrained vendored gem loading through an explicit gem-home allowlist
• one native execution model centered on embedded LANGUAGE ruby
• a repeatable pgbench harness for comparing SQL, plpgsql, and native Ruby call overhead
• an explicit benchmark methodology for release-mode local performance checks
• representative text, JSON, and array benchmark scenarios for profiling Ruby-heavy work

What it does not contain yet:

• pseudotype function signatures other than trigger and record
• parallel-worker hardening, planner benchmarking, or sandboxing
• a multi-platform release matrix beyond the current PostgreSQL 17 Linux package job

Other pseudotypes, including polymorphic any* types and unsupported output-column shapes like nested record, are rejected explicitly during validation.

Local requirements

You need:

• Rust
• Ruby with headers and libraries available for embedding
• cargo-pgrx
• a PostgreSQL installation initialized for pgrx

cargo clippy in this crate treats clippy::cargo, clippy::pedantic, and clippy::nursery as part of the baseline lint set. The repo also exposes cargo lint as the standard strict lint command for the pg17 target.

GitHub Actions enforces that same standard on every push and pull request by running cargo lint, cargo test --lib, and cargo pgrx test pg17 against PostgreSQL 17.

Tagged releases on GitHub also run cargo pgrx package, extract the produced artifact, smoke-test that packaged install into a fresh PostgreSQL 17 cluster, and then attach the verified PostgreSQL 17 Linux artifact.

Backend-facing behavior, including the Postr prelude, is exercised with cargo pgrx test pg17. Plain cargo test --lib keeps covering the non-backend unit-test surface on macOS without requiring a live PostgreSQL backend during process load.

magnus officially documents support for Ruby 3.0 through 3.4. On this machine, this extension still compiled against Ruby 4.0.2 on March 28, 2026, but that should be treated as local validation rather than an upstream compatibility guarantee.

Setup

Install cargo-pgrx and initialize PostgreSQL:

cargo install --locked cargo-pgrx --version 0.17.0
cargo pgrx init

Then run the extension:

cargo pgrx run

Inside psql:

CREATE EXTENSION postr;

Anonymous Ruby blocks work too:

DO $ruby$
  Postr.notice("hello from inline ruby")
$ruby$ LANGUAGE ruby;

Curated Requires

Native LANGUAGE ruby functions and DO LANGUAGE ruby blocks can use plain Ruby require, but postr only allows it through an explicit PostgreSQL-backed policy:

• postr.extra_load_paths adds absolute directories to the curated Ruby load path
• postr.allowed_requires lists the top-level feature names Ruby code may require
• postr.gem_home points at a vendored gem home
• postr.allowed_gems lists the gem names whose lib/ paths may be exposed from that gem home

Example session-level setup:

SET postr.extra_load_paths = '/opt/postr/ruby';
SET postr.allowed_requires = 'postr_helpers';
SET postr.gem_home = '/opt/postr/gems';
SET postr.allowed_gems = 'postr_echo';

Then Ruby code can load approved code normally:

CREATE FUNCTION public.ruby_require_demo(name text)
RETURNS text
LANGUAGE ruby
AS $ruby$
  require "postr_helpers"

  def call(name:)
    PostrHelpers.greet(name)
  end
$ruby$;

Top-level require calls outside postr.allowed_requires are rejected. Kernel.load is blocked for user code, and vendored gems stay unavailable until both postr.gem_home and postr.allowed_gems are configured.

The policy follows normal PostgreSQL GUC semantics:

• SET LOCAL only affects Ruby calls inside the current transaction
• RESET removes the configured load surface again, including vendored gem access when postr.gem_home is cleared
• repeated Ruby calls with the same canonical policy reuse a backend-local fast path instead of rebuilding the Ruby runtime policy from scratch each time

Docker

For a disposable local PostgreSQL 17 environment with postr preinstalled:

docker build -f Dockerfile.local -t postr-local .
docker run --rm \
  --name postr-local \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=postr \
  -p 5432:5432 \
  postr-local

On first boot, the container runs docker/initdb/010-postr.sql and creates the extension automatically in the postr database.

Connect with:

psql postgresql://postgres:postgres@localhost:5432/postr

Packaged Artifacts

The release job publishes a packaged install tree built by cargo pgrx package.

To produce and smoke-test the same shape locally:

cargo pgrx package --pg-config "$(pg_config)" --out-dir dist/postr-pg17
bash ./scripts/smoke_test_packaged_artifact.sh \
  --package-root dist/postr-pg17 \
  --pg-config "$(pg_config)"

The smoke test copies the packaged postr files into the PostgreSQL installation pointed to by pg_config, starts a temporary cluster, creates the extension, and verifies that a native LANGUAGE ruby function runs successfully.

Example

For a runnable cookbook beyond the short examples below, see examples/README.md. Those .sql files are also executed by the standard cargo pgrx test pg17 regression suite. The most “Ruby-first” cookbook entries are 08-rules-engine.sql and 09-ruby-first.sql. The fullest end-to-end showcase is 11-order-decision-engine.sql, with the companion app-side client at 12-pg-order-decision-engine.rb. For an app-side Ruby client, see 10-pg-client.rb, which demonstrates Ruby app -> PostgreSQL -> embedded Ruby with the pg gem.

Create a native Ruby function:

CREATE FUNCTION public.ruby_greet(name text, excited boolean)
RETURNS text
LANGUAGE ruby
AS $ruby$
  def call(name:, excited:)
    message = "hello, #{name}"
    excited ? "#{message}!" : message
  end
$ruby$;

Call it like a normal PostgreSQL function:

SELECT public.ruby_greet('postgres', true);

The same flow from an external Ruby app looks like:

gem install pg
DATABASE_URL=postgresql://postgres:postgres@127.0.0.1:5432/postr \
  ruby examples/10-pg-client.rb

That script connects with PG.connect, creates a native LANGUAGE ruby function, and calls it over the normal PostgreSQL wire protocol.

If you want one demo that shows why this library exists, run the order-decision showcase:

DATABASE_URL=postgresql://postgres:postgres@127.0.0.1:5432/postr \
  ruby examples/12-pg-order-decision-engine.rb

That path exercises Ruby triggers, JSONB and array mapping, curated require, transactional writes, relational rule loading, audit rows, and a SETOF explainer, all from an external Ruby app.

Arguments are exposed in three ways inside Ruby:

• args is a hash-like object keyed by PostgreSQL argument name
• argv is an array in call order
• valid PostgreSQL argument names are also bound as Ruby locals

Native LANGUAGE ruby functions should define def call(...). That gives you normal Ruby methods, helpers, and constants while still exposing args and argv inside the function object.

Function bodies can also define nested modules and additional constants around def call(...), so Ruby helpers can stay local to the PostgreSQL function body instead of being flattened into one method.

Inside native Ruby functions, Postr exposes a small standard library:

• Postr.exec(sql, *params) runs SQL and returns the processed row count
• Postr.select(sql, *params) returns an array of row hashes, or yields each row to a block and returns nil
• Postr.first(sql, *params) returns the first row hash or nil
• Postr.value(sql, *params) returns the first column of the first row or nil
• Postr.param(value, type) and Postr.arg(value, type) wrap an explicit PostgreSQL type for ambiguous cases such as empty arrays, typed strings, composite values, and typed nil
• Postr.notice(message) / Postr.warn(message) emit PostgreSQL server messages
• Postr.quote_ident(name) quotes an identifier the PostgreSQL way
• Postr.transaction { ... } runs the block inside an internal PostgreSQL subtransaction and returns the block result on success

Postr.transaction is savepoint-like, not a top-level commit. If the block raises, writes inside that block are rolled back and the error is re-raised into Ruby.

The query helpers take Ruby splat params, so the common path stays terse:

Postr.exec(
  "INSERT INTO postr_people(name, age, born_on) VALUES ($1, $2, $3)",
  "Ada",
  37,
  Date.new(1815, 12, 10)
)

count = Postr.value("SELECT count(*) FROM postr_people WHERE age >= $1", 18)
row = Postr.first("SELECT name, age FROM postr_people WHERE id = $1", Postr.param("1", "uuid"))

Native RETURNS trigger functions use the same def call(...) model. Inside a trigger body:

• trigger is a Ruby object with name, op, when, level, table_name, table_schema, argv, row?, statement?, before?, after?, instead_of?, insert?, update?, delete?, and truncate?
• new and old expose row hashes when the trigger event makes them available
• trigger arguments from CREATE TRIGGER ... EXECUTE PROCEDURE ... are exposed through both trigger.argv and argv

Trigger return behavior:

• nil keeps PostgreSQL's default row/statement result
• false or :skip skips the row in row-level triggers
• a row hash modifies the row returned by a row-level trigger

Example trigger:

CREATE FUNCTION public.ruby_fill_slug()
RETURNS trigger
LANGUAGE ruby
AS $ruby$
  def call(trigger:, new:)
    return new unless trigger.before? && trigger.insert?

    new["slug"] = "#{new["name"].downcase}-#{trigger.argv[0]}"
    new
  end
$ruby$;

CREATE TRIGGER ruby_fill_slug
BEFORE INSERT ON public.people
FOR EACH ROW
EXECUTE PROCEDURE public.ruby_fill_slug('friend');

Native RETURNS SETOF functions also use def call(...). They can either return an iterable object or yield rows one at a time:

• scalar SETOF functions should return or yield scalar Ruby values
• SETOF some_composite_type functions should return or yield row hashes keyed by column name
• a single composite row should be wrapped in an array or produced with yield

Example SETOF function:

CREATE FUNCTION public.ruby_series(limit_count integer)
RETURNS SETOF integer
LANGUAGE ruby
AS $ruby$
  def call(limit_count:)
    1.upto(limit_count) { |value| yield value }
    nil
  end
$ruby$;

When Ruby raises, postr now reports the exception using stable postr://... source labels and filters out the internal dispatch frames, so the first backtrace lines point at the Ruby body you wrote instead of the embedding glue.

Planner Declarations

LANGUAGE ruby functions can use normal PostgreSQL planner declarations, and postr preserves them in pg_proc:

• volatility declarations like IMMUTABLE, STABLE, and VOLATILE
• STRICT
• COST
• ROWS for SETOF functions

Use them deliberately:

• only mark a Ruby function IMMUTABLE when it depends solely on its SQL inputs and not on SQL access, time, randomness, filesystem state, or Ruby load-time side effects
• use STRICT when null inputs should short-circuit before Ruby runs
• raise COST for expensive Ruby work so the planner stops treating it like a cheap scalar helper
• set realistic ROWS for SETOF functions so row-count estimates are not fiction

postr currently requires PARALLEL UNSAFE for native Ruby functions. PARALLEL SAFE and PARALLEL RESTRICTED are rejected during validation until the embedded runtime is explicitly hardened for parallel-worker execution.

Example:

CREATE FUNCTION public.ruby_series(limit_count integer)
RETURNS SETOF integer
LANGUAGE ruby
STABLE
STRICT
PARALLEL UNSAFE
COST 23
ROWS 7
AS $ruby$
  def call(limit_count:)
    1.upto(limit_count) { |value| yield value }
    nil
  end
$ruby$;

Current argument mapping for native LANGUAGE ruby functions:

• boolean, integer, and float types become Ruby true / false, Integer, and Float
• numeric becomes Ruby BigDecimal
• date becomes Ruby Date
• timestamp and timestamptz become Ruby Time
• time and timetz become Ruby Time
• interval becomes Postr::Interval
• uuid becomes Postr::Uuid
• inet and cidr become Postr::Network
• built-in single-range types like int4range, int8range, numrange, daterange, tsrange, and tstzrange become Postr::Range
• enum values become Ruby Symbol
• bytea becomes a binary Ruby String
• json / jsonb become Ruby Hash / Array / scalar values
• SQL arrays become nested Ruby Array values with nil preserved for SQL NULL
• composite row types become Ruby Hash values keyed by column name
• domain-wrapped scalars, arrays, and composites follow the same native mapping as their base types
• rarer families such as multiranges, money, geometric types, xml, and internal/catalog scalar types still arrive as strings via PostgreSQL text output functions by design

Current return mapping for native LANGUAGE ruby functions:

• scalar and domain return types are parsed through PostgreSQL input functions
• bytea return types expect a Ruby String and preserve raw bytes
• enum return types accept Ruby Symbol or string-like values
• uuid return types accept Postr::Uuid or string-like values
• interval return types accept Postr::Interval or string-like values PostgreSQL can parse
• inet and cidr return types accept Postr::Network or string-like values PostgreSQL can parse
• built-in single-range return types accept Postr::Range or string-like values PostgreSQL can parse
• json / jsonb return Ruby Hash / Array / scalar values directly
• array return types expect Ruby Array values and preserve nested dimensions and nil
• composite return types expect Ruby row hashes keyed by column name
• void is supported
• SETOF scalar return types materialize one row per yielded or iterated Ruby value
• SETOF array return types materialize one array datum per yielded or iterated Ruby array
• SETOF composite return types materialize one row per yielded or iterated Ruby hash
• record returns must still be called with a concrete row shape, and domain failures are reported against the declared domain type
• rarer scalar families that stay string-backed on input also round-trip through PostgreSQL text parsing on return

Return jsonb directly:

CREATE FUNCTION public.ruby_profile(name text, age integer)
RETURNS jsonb
LANGUAGE ruby
AS $ruby$
  def call(name:, age:)
    {
      name: name,
      age: age,
      tags: ["ruby", "postgres"]
    }
  end
$ruby$;

Security

This extension executes Ruby inside the PostgreSQL backend process. That means:

• the extension must be built against a compatible Ruby installation
• Ruby code runs with the operating-system privileges of the PostgreSQL server process
• this should be treated as superuser-only and unsafe for multi-tenant use
• postr is not a sandbox; the require policy is a load-surface control, not a privilege boundary

Current load-surface model:

• top-level Ruby require is limited to logical feature names listed in postr.allowed_requires
• approved local code must live under absolute directories listed in postr.extra_load_paths
• vendored gems must live under postr.gem_home and be named in postr.allowed_gems
• Kernel.load is blocked for user code so ad hoc file loads do not bypass the curated require path
• nested requires from an already-approved feature still work, so standard libraries and allowed gems can load their own internals

This is a better foundation than subprocess execution, but it is still not a full production language runtime. The remaining work is mostly about hot-path performance measurement, tightening runtime and deployment behavior, and being explicit about the security model.

Packaging

For a local installation package directory, run:

cargo pgrx package --pg-config /path/to/pg_config --out-dir dist/postr-pg17

That produces the filesystem layout PostgreSQL package managers expect. The tag-driven GitHub release workflow packages the same layout into a .tar.gz artifact for PostgreSQL 17 on Linux.

The backend test suite now includes a real upgrade regression: it stages a historical 0.0.1 install fixture for pg_test, creates a throwaway database, runs CREATE EXTENSION postr VERSION '0.0.1', upgrades with ALTER EXTENSION postr UPDATE TO '0.1.0', and then verifies that native LANGUAGE ruby still works after the upgrade.

Benchmarking

Benchmark the native call path against SQL and plpgsql baselines with scripts/benchmark_native_calls.sh.

The standard local benchmarking procedure is documented in bench/METHODOLOGY.md.

Use a release-built server when measuring. With local psql and pgbench installed:

cargo pgrx run pg17 --release

DATABASE_URL=postgresql://postgres@localhost:<pgrx-port>/postgres \
  ./scripts/benchmark_native_calls.sh

The harness installs benchmark helpers from bench/setup.sql for four scenarios across SQL, plpgsql, and Ruby:

• identity
• text_pipeline
• json_project
• array_reduce

Useful environment variables:

• WORKLOAD=all|sql|plpgsql|ruby
• SCENARIO=all|identity|text|json|array
• CLIENTS, JOBS, DURATION
• RUNS, WARMUP_RUNS
• MODE=simple|prepared|extended

If you are using the disposable Docker flow and do not have local PostgreSQL client tools installed, the same harness can run inside the container:

docker build -f Dockerfile.local -t postr-local .
docker run --rm \
  --name postr-local \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=postr \
  -p 5432:5432 \
  postr-local

BENCH_CONTAINER=postr-local PGDATABASE=postr \
  ./scripts/benchmark_native_calls.sh

To record a pinned machine-local baseline artifact instead of reading raw terminal output, use capture_local_benchmark_baseline.sh. A captured example from this machine lives at local-docker-2026-03-28.md.

That captured artifact records medians of about:

• identity: SQL 100k TPS, plpgsql 85k TPS, Ruby 127k TPS
• text pipeline: SQL 154k TPS, plpgsql 179k TPS, Ruby 318k TPS
• JSON project: SQL 97k TPS, plpgsql 105k TPS, Ruby 17k TPS
• array reduce: SQL 295k TPS, plpgsql 269k TPS, Ruby 255k TPS

Those numbers are useful because they separate “Ruby call overhead” from “Ruby plus native type mapping work”. The JSON scenario is currently the clearest heavy-path canary.

The pinned local artifact from this same day still shows enough run-to-run noise that it should not be treated as a project-wide baseline. It is a captured local reference point, not a replacement for a quieter dedicated benchmark host.

Development layout

The crate is split by responsibility:

• src/lib.rs wires PostgreSQL entry points and registers the native ruby language
• src/language.rs handles native LANGUAGE ruby loading and validation
• src/postr_prelude.rs defines the Ruby-visible Postr helper module
• src/type_mapping.rs maps PostgreSQL datums to Ruby values and back
• src/runtime.rs owns embedded Ruby initialization and Ruby helper calls
• src/pg_support.rs contains PostgreSQL FFI and datum helpers