pg_functor

pg_functor is PostgreSQL extension that maintains local readonly columnar replicas of PostgreSQL tables. Rows are copied into Parquet as transactions commit then queried through foreign tables with DataFusion

It is intended for analytical queries that benefit from columnar reads without exporting data to a separate service or converting the source table during every SELECT

pg_functor is partially adopted from initial implementation of proprietary storage engine developed by me at ByteField Software in 2025

How it works

1. create_replica creates logical replication slot and exports consistent source snapshot
2. Background worker writes the snapshot and later inserts to durable Parquet segments
3. Read-only foreign table exposes the replica under the requested schema and name
4. PostgreSQL plans the SQL query. Safe projections, filters and aggregates are pushed into DataFusion/Parquet

Requirements

• PostgreSQL 16, 17, or 18 with server development files
• Rust 1.96 or newer
• cargo-pgrx 0.19.1
• A PostgreSQL superuser for server configuration and CREATE EXTENSION

PostgreSQL must have logical WAL enabled and enough worker and replication-slot capacity for intended replica count:

wal_level = logical
shared_preload_libraries = 'pg_functor' # add to any existing entries
max_replication_slots = 16              # size for your workload
max_worker_processes = 16               # leave capacity for other workers

shared_preload_libraries is recommended so workers recover automatically after PostgreSQL restart. Creating or rebuilding a replica can start its database worker dynamically, but changing wal_level or preload settings requires server restart.

Installation

Install matching pgrx CLI and register your PostgreSQL installation once:

cargo install cargo-pgrx --version 0.19.1 --locked
cargo pgrx init --pg18=/path/to/pg_config

Use --pg16, --pg17 or --pg18 to match your server. Build and install extension from this repo:

cargo pgrx install --release --pg-config /path/to/pg_config

Restart PostgreSQL after applying server settings, enable extension in each database that will contain replicas:

CREATE EXTENSION pg_functor;

Usage

Assume public.events is the source table. Caller must own it and have CREATE on the target schema:

CREATE SCHEMA IF NOT EXISTS analytics;

SELECT pgfunctor.create_replica(
    source        => 'public.events'::regclass,
    target_schema => 'analytics',
    target_name   => 'events',
    options       => '{"compression":"zstd"}'::jsonb
);

Creation is asynchronous. Check readiness and lag through the status view:

SELECT
    replica,
    status,
    lag_bytes,
    rows,
    compressed_bytes,
    last_error
FROM pgfunctor.replica_status
WHERE replica = 'analytics.events'::regclass;

Once status = 'ready' just query your replica:

SELECT event_type, count(*)
FROM analytics.events
WHERE occurred_at >= current_date - interval '30 days'
GROUP BY event_type
ORDER BY count(*) DESC;

Waiting for writes

Capture LSN after source transaction commits then wait for replica to reach it:

INSERT INTO public.events (event_type, occurred_at)
VALUES ('signup', clock_timestamp());

SELECT pgfunctor.wait_until(
    'analytics.events'::regclass,
    pg_current_wal_flush_lsn()::text,
    5000 -- timeout in milliseconds
);

Lifecycle operations

SELECT pgfunctor.pause('analytics.events'::regclass);
SELECT pgfunctor.resume('analytics.events'::regclass);

-- Required after UPDATE, DELETE or incompatible source schema change
SELECT pgfunctor.rebuild('analytics.events'::regclass);

-- Removes the target, replication slot, metadata and managed files asynchronously
SELECT pgfunctor.drop_replica('analytics.events'::regclass);

Replica targets are readonly. Grant access to the target independently from source. Source row-level security policies are not copied

Developer guide

Select one PostgreSQL feature per build:

cargo check --no-default-features --features pg18
cargo fmt --all --check
cargo clippy --all-targets --no-default-features --features pg18
cargo pgrx test pg18

Run end-to-end suite against installed PostgreSQL 18 extension:

cargo pgrx install --release --pg-config /path/to/pg_config
python3 tests/e2e.py --pg-config /path/to/pg_config

End-to-end suite creates and removes isolated temporary PostgreSQL cluster. Use --keep-data when debugging a failure

TODO

• Add benchmarks
• Comparisons and prior art

License

Project is licensed under MIT License