# pgcel
A PostgreSQL extension that implements Google's Common Expression Language (CEL) for use in database schemas.
This extension uses:
- [cel-rust](https://github.com/cel-rust/cel-rust) - A Rust implementation of CEL
- [pgrx](https://github.com/pgcentralfoundation/pgrx) - A framework for building PostgreSQL extensions in Rust
## Features
- **`celprogram` type**: A custom PostgreSQL type that stores CEL source for reuse
- **`cel_compile`**: Compile CEL expression strings into `celprogram` values
- **`cel_eval`**: Evaluate programs against JSON context objects, returning boolean results
- **`cel_eval_json`**: Evaluate programs and return results as JSONB (for non-boolean expressions)
- **In-memory caching**: Reuses compiled executables within each backend; shares validation hashes across backends
## Installation
### Prerequisites
- Rust (>= 2021 edition)
- PostgreSQL supported by [`pgrx`](https://github.com/pgcentralfoundation/pgrx) and >= 14
- [cargo-pgrx](https://github.com/pgcentralfoundation/pgrx)
### Build & Install
For PostgreSQL 14 (use the matching version and `pg_config` for another PostgreSQL release):
```bash
cargo install cargo-pgrx --version "0.12.9" --locked
cargo pgrx init --pg14=$(which pg_config)
cd pgcel
cargo pgrx install --release
```
## Usage
### Basic Example
```sql
CREATE EXTENSION pgcel;
CREATE TABLE example (
id int PRIMARY KEY,
program celprogram NOT NULL
);
INSERT INTO example (id, program) VALUES (1, cel_compile('ctx.a.startsWith("hello")'));
INSERT INTO example (id, program) VALUES (2, cel_compile('ctx.a.startsWith("world")'));
SELECT *
FROM example e
WHERE cel_eval(e.program, '{ "a": "hello world!" }');
-- Returns the first row we inserted, but not the other one.
```
### Available Functions
#### `cel_compile(expression text) → celprogram`
Compiles a CEL expression string into a `celprogram`.
```sql
SELECT cel_compile('x > 10 && y < 20');
```
#### `cel_eval(program celprogram, context jsonb) → boolean`
Evaluates a CEL program against a JSON context. The context is available as `ctx` in the expression.
```sql
SELECT cel_eval(
cel_compile('ctx.value > 10'),
'{"value": 42}'
);
-- Returns: true
```
#### `cel_eval_json(program celprogram, context jsonb) → jsonb`
Evaluates a CEL program and returns the result as JSONB. Useful for expressions that return non-boolean values.
```sql
SELECT cel_eval_json(
cel_compile('ctx.a + ctx.b'),
'{"a": 1, "b": 2}'
);
-- Returns: 3
```
## CEL Expression Examples
CEL supports a rich expression language:
```sql
-- String operations
SELECT cel_compile('ctx.name.startsWith("John")');
SELECT cel_compile('ctx.email.contains("@")');
SELECT cel_compile('ctx.text.size() > 100');
-- Numeric comparisons
SELECT cel_compile('ctx.age >= 18 && ctx.age <= 65');
SELECT cel_compile('ctx.price * ctx.quantity > 1000');
-- List operations
SELECT cel_compile('ctx.roles.exists(r, r == "admin")');
SELECT cel_compile('"premium" in ctx.features');
SELECT cel_compile('ctx.items.size() > 0');
-- Conditional expressions
SELECT cel_compile('ctx.status == "active" ? ctx.score : 0');
-- Boolean logic
SELECT cel_compile('ctx.enabled && (ctx.verified || ctx.admin)');
```
## Architecture
`celprogram` persists the expression source. At runtime the extension uses three caches:
1. **Backend-local executable LRU**: Reuses compiled programs by source until evicted.
2. **Backend-local validation hashes**: Avoids revalidating recently seen sources in the same backend.
3. **Shared validation hashes**: A fixed-size, 1,024-entry `PgLwLock` map records validated source hashes across backends; it does not hold executable programs.
Compiled executables cannot be shared across PostgreSQL processes; a validation-hash hit in one backend still requires it to compile if it lacks the local executable.
### Cache configuration
`pgcel.program_cache_size` controls the number of compiled programs retained by each PostgreSQL backend. It defaults to `1024`, accepts values from `1` to `65536`, and can be set globally or per session:
```sql
SET pgcel.program_cache_size = 4096;
```
## Benchmark results
Run on an M2 Macbook Air, 8GB of RAM, MacOS Tahoe 26.2, using PostgreSQL 16 with `pgcel.program_cache_size = 1024`:
| Benchmark | Latency | Throughput |
| --- | ---: | ---: |
| `cel_compile` (simple) | 0.682 ms | 1,467 ops/sec |
| `cel_compile` (complex) | 0.709 ms | 1,411 ops/sec |
| `cel_eval` (one cached program, 10,000 contexts) | 3.0 µs | 331,642 ops/sec |
| `cel_eval_json` (cached arithmetic) | 3.8 µs | 260,213 ops/sec |
| CEL interpreter evaluation (simple / medium / complex) | 1.50 / 1.71 / 1.98 µs | — |
| CEL interpreter, 1,000-context batch | 1.60 ms | 624k eval/sec |
`bench.rs` measurements exercise `cel-interpreter` directly; the SQL measurements include the PostgreSQL extension path and report single-backend throughput, not concurrent aggregate capacity or tail latency. The SQL stress script now pairs programs with compatible contexts; its full suite passed against a disposable PostgreSQL 16 cluster. The figures above are from an earlier local run, not the disposable verification run.
`bench/run_benchmarks.sh` defaults to the `pg14` feature; set `PG_VERSION=pg16` (or another supported feature) to select a matching pgrx build and managed server. SQL benchmarks use libpq connection variables such as `PGHOST` and `PGPORT`; when a connection target is specified, the runner will not start a managed server. Run SQL suites only against a dedicated benchmark database.
To regenerate local logs, run `PG_VERSION=pg16 bench/run_benchmarks.sh` against a dedicated benchmark database. It writes `bench/criterion_results.txt`, `bench/sql_results.txt`, and `bench/concurrent_results_*.txt`; run `psql -v ON_ERROR_STOP=1 -f bench/join_benchmark.sql > bench/join_results.txt 2>&1` separately for the large JOIN workload, also against a dedicated database. The SQL scripts recreate only their own `pgcel_stress_bench` or `pgcel_join_bench` schema (erasing prior results there), never drop the extension, and leave the schemas for inspection. Remove them explicitly with `DROP SCHEMA pgcel_stress_bench CASCADE;` and `DROP SCHEMA pgcel_join_bench CASCADE;` when finished. These logs are ignored and are not reproducible snapshots; record the PostgreSQL version, hardware, cache setting, and build mode when comparing runs. Use `RUN_SQL=false RUN_CONCURRENT=false` for a Criterion-only run. `Cargo.lock` is retained to pin Rust dependencies.
## Development
### Building
```bash
cargo build --release --features pg14
```
### Running Tests
```bash
cargo pgrx test pg14 # Adjust for your PG version
```
With `pgcel` installed and preloaded on a test server, run `bash tests/session_contracts.sh` to check SQL source round-tripping, invalid input, the cache GUC, and separate backend sessions. It uses the current libpq connection settings and does not create database objects.
## License
MIT License