A PostgreSQL extension that streams table writes directly into an Apache Paimon lakehouse warehouse — no Kafka, no Debezium, no separate CDC pipeline required.
The extension implements a custom Table Access Method (TAM) that intercepts every DML and DDL operation on tagged tables and forwards them to a built-in background worker. The background worker writes Parquet data files and Paimon manifests to local disk, then promotes them to S3 or HDFS. The analytical layer sees a continuously updated, queryable Paimon table with data freshness measured in seconds.
- Use cases
- Architecture
- Design decisions
- Repository layout
- Requirements
- Building
- Installation
- Configuration
- Quick start
- Java reader and compaction
- Performance
- Limitations
Direct lakehouse ingestion without a streaming stack. The classic analytical pipeline (PostgreSQL → Debezium → Kafka → Flink → lakehouse) crosses four ownership boundaries, each with its own SLA, on-call rotation, and approval process for schema changes. postgres-paimon collapses that chain: the OLTP team writes to PostgreSQL and the lakehouse gets updated within seconds, with no intermediate infrastructure to operate.
Low-latency analytical freshness. Batch ETL runs on schedules measured in hours. CDC-to-streaming pipelines deliver seconds of latency but require dedicated streaming infrastructure. postgres-paimon achieves seconds-level freshness using only PostgreSQL and object storage.
Schema-safe analytical delivery. When the OLTP team changes a table schema, postgres-paimon propagates the change to the Paimon schema atomically. Destructive operations that cannot be reconciled in the analytical layer (primary key changes, non-nullable column additions without defaults) are blocked at the source before they execute, turning production incidents into design-time errors.
Reducing organizational coordination overhead. Because the extension runs inside PostgreSQL, the OLTP team owns the entire delivery path. Schema changes, table additions, and truncations are handled automatically. There is no intermediate team to notify, no schema registry to update, no connector mapping to fix.
┌──────────────────────────────────────────────────────┐
│ PostgreSQL backend │
│ │
│ INSERT / UPDATE / DELETE │
│ │ │
│ ▼ │
│ paimon_heap TAM ──► heap AM (standard storage) │
│ │ │
│ └──► row serialised into per-txn buffer │
│ │ │
│ XACT_COMMIT callback │
│ │ │
│ ▼ │
│ shared-memory ring buffer (32 MB) │
└──────────────────────────────────┬───────────────────┘
│ LWLock write
┌──────────────────────────────────▼───────────────────┐
│ paimon_bgworker (PostgreSQL bgworker) │
│ │
│ ring read ──► PaimonTableWriter │
│ │ │
│ ├──► Parquet row groups │
│ ├──► Paimon manifest files │
│ └──► Paimon snapshot │
│ │ │
│ promote to warehouse │
│ (local / S3 / HDFS) │
└──────────────────────────────────────────────────────┘
DDL propagation is handled by a ProcessUtility hook. CREATE TABLE bootstraps the Paimon schema; ALTER TABLE evolves it; DROP TABLE and TRUNCATE are forwarded to the bgworker before execution so catalog state is still available. Primary key changes are blocked with an error.
Schema evolution is encoded natively in the Paimon schema format. Each ALTER TABLE operation writes a new schema-N file and atomically updates LATEST. The bgworker reads the current schema version before writing each row group, so the Parquet files are always schema-aligned.
Paimon CDC ingestion mode is used throughout. The bgworker writes append-only CDC event files (row_kind column: +I, -U, +U, -D). Merge-on-read and compaction are delegated to the analytical cluster (Spark, Flink, or the bundled compaction job), which has the compute headroom for it. This keeps the bgworker's write path simple and non-blocking.
WAL-based CDC (Debezium, pgoutput) reads the write-ahead log from outside PostgreSQL. It consumes a replication slot (which blocks WAL cleanup if the consumer lags), requires a network-accessible replication connection, and sees PostgreSQL's internal storage format rather than typed row data.
The TAM API intercepts at the executor level, after type coercion and constraint checking, with full access to the typed TupleTableSlot. There is no replication slot, no extra network connection, and no WAL parsing. The cost is that the TAM runs inside the backend process and must be fast — which the ring-buffer design ensures.
An earlier design used a Unix domain socket between backends and a standalone daemon process. The socket ACK round-trip added latency to every transaction commit proportional to the number of tables written. The ring buffer replaces this with a single LWLock acquisition per commit. The write is non-blocking: if the ring is full the batch is dropped and a counter is incremented (visible via paimon_heap.dropped_msgs). This keeps the OLTP latency floor predictable even under heavy analytical write load.
Running the writer as a PostgreSQL BackgroundWorker ties its lifecycle to the PostgreSQL instance. It starts automatically on server start, is restarted on crash, and has access to shared memory without an IPC setup. The tradeoff is that it competes with backends for CPU and I/O on the same host.
The bgworker writes CDC event rows rather than materialised snapshots. Compaction — merging +I/-U/+U/-D sequences into a clean primary-key table — is deferred to the analytical cluster. This makes the bgworker's write path append-only (no read-modify-write), which keeps write amplification low and allows the bgworker to use simple sequential I/O.
Changing a table's primary key after data has been written would require rewriting the entire Paimon table. The extension blocks ALTER TABLE ... ADD/DROP PRIMARY KEY and ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY with an error at the ProcessUtility level. The table must be recreated to change its primary key.
extension/ C++ PostgreSQL extension (TAM + bgworker)
paimon_heap.cpp Extension entry point, TAM registration, GUC definitions
paimon_ddl.cpp ProcessUtility hook — DDL capture and forwarding
paimon_client.* Per-backend row accumulator, ring-buffer producer
paimon_ring.* Shared-memory ring buffer (producer + consumer API)
paimon_shmem.* Shared-memory segment allocation
paimon_bgworker.* Background worker — ring consumer, Parquet writer driver
paimon_schema.* Paimon schema-N / LATEST management
paimon_manifest.* Paimon manifest and snapshot file writer
paimon_parquet.* Arrow/Parquet row-group writer
paimon_snapshot.* Paimon snapshot JSON writer
paimon_avro.* Avro schema encoding (manifest schema embedded in ORC/Parquet metadata)
paimon_table_writer.* Top-level writer: dispatches to schema/manifest/parquet/snapshot
paimon_offload_s3.* S3 upload (AWS SDK for C++)
paimon_offload_hdfs.* HDFS upload (libhdfs3)
paimon_event.h Wire protocol between TAM and bgworker
type_map.h PaimonTypeCode → Arrow/Parquet type mapping
type_map_pg.h PostgreSQL OID → PaimonTypeCode mapping
paimon_heap--1.0.sql Extension SQL: CREATE ACCESS METHOD
paimon_heap.control Extension control file
java/ Paimon compatibility tools (not release artifacts — testing only)
src/test/java/pro/surpin/pgpaimon/
PaimonRead.java CLI reader — print schema and row count
PaimonCompactionJob.java Spark-based compaction job (fat jar)
TpccCompare.java TPC-C consistency checker (PostgreSQL vs Paimon)
PaimonFeatureTest.java DML + schema evolution integration tests
PaimonDdlTest.java DDL propagation tests
PaimonTypeTest.java Type mapping tests
PaimonOffloadTest.java S3 / HDFS offload tests (embedded MiniDFS)
PaimonAdvancedTest.java Advanced scenarios (concurrent writers, large batches)
PaimonE2ETest.java End-to-end: PostgreSQL → Paimon → Spark compaction
| PG version | Status | Notes |
|---|---|---|
| 14 | ❌ | shmem_request_hook absent |
| 15 | ❌ | WaitEventExtensionNew absent |
| 16 | ✅ | Minimum supported |
| 17 | ✅ | Supported |
| 18 | ✅ | Recommended — release binaries target PG 18 |
| 19 | 🔬 | Development target (currently beta) |
| Dependency | Version | Notes |
|---|---|---|
| PostgreSQL | 16–18 | See compatibility table above |
| Apache Arrow C++ | 18+ | libarrow, libparquet |
| AWS SDK for C++ | 1.11+ | S3 offload; aws-cpp-sdk-s3, aws-cpp-sdk-core |
| libhdfs3 | any | HDFS offload |
| libkrb5 | any | Kerberos for HDFS |
| C++ compiler | C++17 | GCC 11+ or Clang 14+ |
Arrow can be installed from the Apache Arrow apt repository.
AWS SDK and libhdfs3 must be built from source or provided pre-built.
The Java tools are used for Paimon compatibility testing only — verifying that
files written by paimon_heap are readable by the upstream Paimon Java API, and for
running integration tests against a live PostgreSQL instance. They are not release
artifacts and are not required to run the extension.
| Dependency | Version |
|---|---|
| JDK | 17+ |
| Maven | 3.8+ |
| Apache Paimon | 1.4.1 |
| Apache Spark | 3.5 (compaction/e2e profiles) |
| Apache Hadoop | 3.3.4 |
# Paths — override as needed
export PG_CONFIG=/usr/local/pgsql/bin/pg_config
export AWS_SDK_ROOT=/opt/aws-sdk-cpp
export HDFS3_ROOT=/usr/local
cd extension
make
sudo make installThe Makefile respects PG_CONFIG, AWS_SDK_ROOT, and HDFS3_ROOT as environment variables with the above defaults.
A reproducible build environment is provided for local development and CI:
docker build -f docker/Dockerfile -t postgres-paimon-build .
docker run --rm -v "$PWD":/src -w /src/extension postgres-paimon-build makeThese jars are for testing only — not required to run the extension.
cd java
# Paimon reader (schema + row count)
mvn package
# Named profiles — integration and compatibility tests
mvn package -P feature-test # DML + schema evolution tests
mvn package -P ddl-test # DDL propagation tests
mvn package -P type-test # Type mapping tests
mvn package -P offload-test # S3/HDFS offload tests
mvn package -P advanced-test # Advanced concurrency tests
mvn package -P e2e-test # End-to-end (requires Spark)
mvn package -P compaction # Spark compaction jobAdd to postgresql.conf:
shared_preload_libraries = 'paimon_heap'
paimon_heap.warehouse = '/data/paimon-warehouse'Then in psql:
CREATE EXTENSION paimon_heap;This registers the paimon_heap access method. New tables must opt in explicitly:
CREATE TABLE orders (...) USING paimon_heap;To stream all new tables automatically without specifying USING paimon_heap on every CREATE TABLE, set the database default:
ALTER DATABASE mydb SET default_table_access_method = 'paimon_heap';After reconnecting, plain CREATE TABLE will use paimon_heap. Existing tables are unaffected — the setting only applies to tables created after the change.
To revert:
ALTER DATABASE mydb RESET default_table_access_method;To set it only for a specific role rather than the whole database:
ALTER ROLE analyst IN DATABASE mydb SET default_table_access_method = 'paimon_heap';All GUCs except ring_size_mb are PGC_SIGHUP — they take effect after SELECT pg_reload_conf() without a server restart.
| GUC | Default | Description |
|---|---|---|
paimon_heap.warehouse |
/tmp/paimon-warehouse |
Final warehouse root directory |
paimon_heap.staging_dir |
same as warehouse | Local staging directory for in-progress Parquet files |
paimon_heap.ring_size_mb |
32 |
Shared-memory ring buffer size in MB (PGC_POSTMASTER — requires restart) |
| GUC | Default | Description |
|---|---|---|
paimon_heap.flush_txns |
100 |
Flush after this many committed transactions; 0 = flush every transaction |
paimon_heap.flush_secs |
30 |
Flush after this many seconds regardless of transaction count |
paimon_heap.min_rows |
0 |
Minimum rows per Parquet file; 0 = no minimum |
| GUC | Default | Description |
|---|---|---|
paimon_heap.s3_bucket |
'' |
S3 bucket name; empty disables S3 offload |
paimon_heap.s3_prefix |
'' |
Key prefix inside the bucket |
paimon_heap.s3_region |
'' |
AWS region; empty = SDK credential chain |
paimon_heap.s3_endpoint |
'' |
Custom endpoint for MinIO, Ceph, etc. |
| GUC | Default | Description |
|---|---|---|
paimon_heap.hdfs_namenode |
'' |
Namenode host:port; empty disables HDFS offload |
paimon_heap.hdfs_path |
'' |
Destination path on HDFS |
paimon_heap.hdfs_user |
'' |
HDFS user |
paimon_heap.hdfs_use_https |
false |
Use HTTPS for HDFS connection |
paimon_heap.hdfs_conf_dir |
'' |
Path to Hadoop config directory |
paimon_heap.hdfs_krb_principal |
'' |
Kerberos principal |
paimon_heap.hdfs_krb_keytab |
'' |
Kerberos keytab file |
-- postgresql.conf already has shared_preload_libraries = 'paimon_heap'
-- and paimon_heap.warehouse = '/tmp/paimon-warehouse'
CREATE EXTENSION paimon_heap;
-- Create a table — paimon_heap is now the default AM for this database
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer TEXT,
amount NUMERIC(12,2),
created_at TIMESTAMPTZ DEFAULT now()
);
-- DML is mirrored to the warehouse automatically
INSERT INTO orders VALUES (1, 'Alice', 99.99, now());
UPDATE orders SET amount = 149.99 WHERE id = 1;
DELETE FROM orders WHERE id = 1;
-- Schema evolution propagates automatically
ALTER TABLE orders ADD COLUMN status TEXT;After a few seconds (one flush_secs cycle), Parquet files appear under /tmp/paimon-warehouse/orders/. Read them with Spark, Flink, Trino, or any Parquet-aware tool.
# Inspect with the Paimon Java reader (compatibility test tool — optional)
# Build first: cd java && mvn package
java -jar java/target/paimon-reader.jar /tmp/paimon-warehouse ordersThese tools verify that files written by paimon_heap are readable by the upstream
Paimon Java API. They are not release artifacts — the extension itself requires no
JVM at runtime. Build them with cd java && mvn package [-P <profile>].
java -jar java/target/paimon-reader.jar [warehouse] [table]Prints the table schema and total row count using the Paimon Java API directly. Useful for confirming that Parquet + manifest files written by the extension are structurally valid.
The extension writes CDC event files (insert/update-before/update-after/delete). Without compaction, reads must merge all CDC events on every query. The compaction job produces a clean deduplicated snapshot:
java -jar java/target/paimon-compaction.jar [warehouse] [table]Requires Spark 3.5 on the classpath (or the fat jar built with -P compaction).
The test programs are entry-point JARs that connect to a running PostgreSQL instance with the extension installed:
# DML + schema evolution
java -jar java/target/paimon-feature-test.jar [warehouse] [jdbc-url]
# End-to-end with Spark compaction
java -jar java/target/paimon-e2e-test.jar [warehouse] [jdbc-url]Default JDBC URL: jdbc:postgresql://localhost:5434/testdb?user=ubuntu&password=
Benchmarks run automatically on every release via the Benchmark workflow.
Hardware: GitHub Actions ubuntu-latest runner — AMD EPYC 9V74 · 4 vCPU · 16 GB RAM
Software: PostgreSQL 18.4, pgbench (bundled), Apache Arrow 18 / Parquet, AWS SDK for C++ 1.11
Schema: (id BIGINT, k INT, v TEXT) — one INSERT per transaction, random k
pgbench duration: 30 seconds per scenario
Flush tuning: flush_interval_txns = 100, flush_interval_secs = 10 (realistic production defaults)
Four engine configurations are compared:
| Configuration | Description |
|---|---|
| heap (sync) | Standard PostgreSQL heap, synchronous_commit = on (default). Each commit waits for WAL to reach disk. |
| heap (async) | Standard PostgreSQL heap, synchronous_commit = off. WAL is flushed asynchronously (~200 ms delay). Upper bound on single-node INSERT throughput. |
| paimon_heap | paimon_heap with wal_level = off (default). Rows are serialised into the ring buffer at commit; WAL durability is not guaranteed on crash. |
| paimon_heap + WAL | paimon_heap with wal_level = local. A custom WAL record containing all commit's ring messages is inserted at XACT_EVENT_PRE_COMMIT. Because this fires before RecordTransactionCommit(), the record is flushed by the same WAL fsync that makes the commit durable — no extra I/O. On crash, the startup process replays the WAL record into a recovery journal; the bgworker consumes the journal on next start and delivers the rows to Parquet normally. |
| Engine | 1 client | 4 clients | 8 clients |
|---|---|---|---|
| heap (sync) | 6,387 | 15,726 | 23,196 |
| heap (async) | 17,655 | 54,450 | 57,347 |
paimon_heap (wal_level=off) |
5,899 | 13,549 | 18,267 |
paimon_heap (wal_level=local) |
5,738 | 13,378 | 19,179 |
paimon_heap overhead vs heap (sync):
| Clients | wal_level=off |
wal_level=local |
|---|---|---|
| 1 | −8% | −10% |
| 4 | −14% | −15% |
| 8 | −21% | −17% |
wal_level=local is within ~2% of wal_level=off across all concurrency levels. The WAL record is absorbed into the commit's existing fsync, so enabling crash-safe delivery costs almost nothing in throughput. The remaining 10–17% gap vs plain heap (sync) is the cost of row serialisation, the XLogInsert buffer copy, and the ring buffer write — all in-memory operations.
| Metric | Value |
|---|---|
| INSERT time (PostgreSQL COMMIT) | 310 ms |
| End-to-end time (COMMIT → S3 flush visible) | 1,361 ms |
| INSERT throughput | 322,581 rows/s |
| End-to-end throughput | 73,475 rows/s |
| Parquet file size | 4,468 KB |
The 1,051 ms gap between COMMIT and S3 visibility is the bgworker's Parquet write + upload time. The PostgreSQL client is unblocked immediately after COMMIT.
| TPS | Ring-buffer drops |
|---|---|
| 20,475 | 4,823 |
At 16 flat-out concurrent inserters the ring fills faster than the bgworker drains it, producing drops. Increase paimon_heap.ring_size_mb and/or reduce flush_interval_txns to eliminate drops in production workloads with many concurrent writers.
When the warehouse lives on the same disk as PostgreSQL data files, the bgworker's sequential Parquet writes can interfere with OLTP random I/O at high scale factors. Mitigations:
- Dedicated disk for the warehouse — point
paimon_heap.staging_dirat a separate device - Rate-limit the bgworker — increase
flush_interval_txns/flush_interval_secsto reduce I/O burst frequency - Increase ring buffer size — larger
ring_size_mballows bigger batches and fewer flush cycles
- PostgreSQL 16+ only. The extension requires
WaitEventExtensionNew(PG 16) andshmem_request_hook(PG 15). PG 14 and 15 are not supported. Release binaries target PG 18. - Primary key is immutable. Changing a table's primary key after data has been written is not supported. Recreate the table.
- Non-nullable column additions are blocked.
ALTER TABLE ... ADD COLUMN col TYPE NOT NULLwithout a default is rejected because it cannot be represented in the existing Paimon data files. - Ring buffer drops under sustained overload. If the OLTP commit rate exceeds the bgworker's write throughput for long enough to fill the 32 MB ring, DML batches are silently dropped. The drop count is exposed via a GUC counter. Size the ring and tune flush parameters for the expected workload.
- No replication. The extension writes to the local warehouse only. HA/replication of the warehouse is the responsibility of the storage layer (S3 versioning, HDFS replication).
- Avro schema metadata. Manifest files embed schema using a custom Avro-compatible encoding compatible with Paimon 1.4+.
Apache License 2.0 — see LICENSE.