This is the multi-page printable view of this section. .
Article
- 1: SOW: Postpartum Care for 100,000 Packages
- 2: How a Packaging Patch Broke Valkey’s Memory Accounting
- 3: Instantly Clone PostgreSQL Databases—No Black Magic Required
- 4: What Is a PostgreSQL Distribution?
- 5: Extensions for Everyone
- 6: 504 Extensions: Expand the PostgreSQL Landscape
- 7: The PostgreSQL Extension Encyclopedia: Bilingual and Ready to Use
- 8: 464 Extensions, Ready Out of the Box: The New PostgreSQL Extension Catalog
- 9: Forging a China-Rooted, Global PostgreSQL Distro
- 10: On Trusting Open-Source Supply Chains
- 11: PG Extension Cloud: Unlocking PostgreSQL’s Entire Ecosystem
- 12: Build and Packaging: An Overlooked but Scarce Skill
- 13: The PostgreSQL 'Supply Cut' and Trust Issues in Software Supply Chain
- 14: PGDG Cuts Off Mirror Sync Channel
- 15: Postgres Extensions Day — See You There!
- 16: Pig, The Postgres Extension Wizard
- 17: The Ideal Way to Deliver PostgreSQL Extensions
Long-form articles about the problems PIG is built to solve: making PostgreSQL extensions discoverable, packaging them as native RPM/DEB artifacts, publishing trustworthy repositories, and exposing the resulting capabilities through a practical CLI.
Versions, counts, and examples in these articles reflect their publication dates. For current usage, see the PIG documentation, extension catalog, and release notes.
Suggested reading paths:
- PIG and the pig family: Meet PIG, instant PostgreSQL cloning, and SOW’s repository state model.
- Extension delivery: the original repository rationale, PG Extension Cloud, the extension encyclopedia, and Extensions for Everyone.
- Packaging and trust: why Linux packaging is scarce, the PGDG mirror incident, Pigsty’s response, and what a Valkey packaging bug teaches us.
- The distribution layer: forging a global PostgreSQL distribution and what a PostgreSQL distribution actually is.
1 - SOW: Postpartum Care for 100,000 Packages
Today, let’s talk about Postpartum Care for Sows—a Chinese meme for the sort of absurdly specialized practical topic nobody expects to discuss. This time, the sow in question is my new open-source project, SOW.
When you maintain a PostgreSQL distribution, compiling the software is rarely the most painful part. The real pain is dealing with everything the build produces.
Pigsty maintains hundreds of components across multiple Linux distributions, CPU architectures, and major PostgreSQL versions. Multiply those combinations out, and the repository ends up with more than 100,000 artifacts: RPMs, DEBs, indexes, signatures, checksums, snapshots, and piles of metadata that exist only to keep package managers happy.
Users see apt install or dnf install. Maintainers see something else entirely. You update one package and must guarantee that the other 99,999 objects were not accidentally deleted. You change one index and must ensure that users around the world never catch the repository halfway through the cutover, with half the old state and half the new. At small scale, these look like scripting problems. At 100,000 artifacts, they abruptly become database, distributed systems, and software supply-chain problems.
That is why I built SOW, a self-contained APT/YUM repository manager written in Go.
If all you need is to turn a directory of RPMs and DEBs into a usable repository, one command is enough:
For a repository you intend to maintain over time, SOW also provides Managed mode. It projects one package payload into multiple distribution views, records desired and built state, creates immutable snapshots, computes exact changesets, and publishes incremental updates to a filesystem or object store.
SOW provides both repository indexing and long-term repository management in one binary.
I built this tool entirely to solve my own problems. But if you also maintain a large software repository spanning multiple Linux distributions, it should help you too—though admittedly, there probably aren’t many of us.
Why SOW?
The name deserves an explanation. We previously built a companion project, Pig—PostgreSQL Install Genius, the package manager for the PostgreSQL ecosystem. If a little Pig installs the packages, then the tool that produces, stores, organizes, and distributes those artifacts naturally has to be its mother: SOW.
It also expands neatly to Software Object Warehouse, a name that fits naturally into the software supply chain. Better yet, the pig metaphor has roots in industrial history. In traditional pig-iron casting, molten iron flowed down a central channel and branched into rows of smaller molds. The cooled ingots were called pigs, while the main runner that fed them was the sow. Seen from above, one large channel feeding rows of ingots looked like a sow nursing her piglets.
Why Build Another Repository Tool?
The immediate trigger was Pigsty’s offline installation. Pigsty first downloads the required RPMs and DEBs, then turns them into a local offline repository. Historically, RPM systems relied on createrepo_c, while Debian and Ubuntu relied on dpkg-dev. The output is only a few XML files, Packages indexes, and compressed metadata, yet preparing the toolchain pulls in hundreds of megabytes of dependencies.
That is cumbersome enough on Linux and even uglier on macOS. You have to start different Linux containers, mount the same directory into each one, run the RPM and DEB tools separately, then copy the results back. Bringing in hundreds of megabytes of tooling and a fleet of containers to generate a few megabytes of metadata is hardly elegant.
In Pigsty 4.5, I removed that clutter. SOW is a self-contained binary. It runs directly on Linux and macOS, understands both RPM and DEB package formats, and implements the APT and DNF repository specifications. It has no daemon and requires no separate language runtime.
But installing fewer tools was only the surface problem. Four deeper problems convinced me to keep building SOW as the repository grew.
First, Hard Links Don’t Help in Object Storage
The same noarch RPM may appear in repositories for several architectures, and the same package may belong to beta, latest, and stable views. On a local disk, hard links let many paths share one inode. Upload them to Cloudflare R2, Alibaba Cloud OSS, or another object store, and every object key incurs real storage and upload costs. Identical content does not make the cloud treat those keys as one object.
Second, 100,000 Files Make Even a Comparison Expensive
A repository update may change only a few dozen packages, yet traditional synchronization tools often have to walk, compare, and verify more than 100,000 files to prove it. A full comparison can easily take more than ten minutes. The actual transfer may take seconds; almost all the time goes into proving that nothing changed.
Third, a Live Repository Must Never Expose a Half-Built State
An RPM repository is more than a directory of .rpm files. Clients read repomd.xml first, then follow it to primary, filelists, and the package payloads. APT has the same constraint: Release, InRelease, Packages, and by-hash files form a strict graph of references.
If you publish them in the wrong order, a client may see a new pointer before the object it references exists. To the maintainer, that is only a few seconds during an upload. To a user who happens to arrive during that window, it is an irreproducible 404, checksum failure, or aborted installation.
Fourth, Directories Have No Versions, but Distributions Need Them
The deeper problem surfaced when I wanted to add repository channels. How do you maintain beta, latest, and stable at the same time? Keep a monthly snapshot? Answer “which packages were added last Wednesday?” Roll back safely? Determine which old objects are no longer referenced by any snapshot and may be deleted?
You can script each requirement in isolation. Put them together, however, and those scripts metastasize into a shadow database with no transactions, schema, or audit trail. There are already tools such as createrepo_c, dpkg-scanpackages, reprepro, and aptly, along with general-purpose synchronization and object-storage tools. But I could not find a lightweight open-source tool that brought RPM and DEB support, a single package pool, immutable snapshots, recoverable publishing, and incremental delivery into one clear model.
Fortunately, building your own tools has never been cheaper.
Two Levels of Complexity, Two Modes
SOW does not assume that every repository needs the same degree of governance. It splits the problem into Plain and Managed modes: small problems stay small, while large ones get the full state machine.
Plain: The Package Directory Is the Truth
Plain mode has one central command:
The RPMs and DEBs in the directory are the sole source of truth. repodata/, Packages, and Packages.gz are projections that can be discarded and rebuilt at any time. SOW scans top-level packages concurrently. Without signing, it opens each package only once, computing its SHA-256, parsing it, and extracting every fact needed for rendering in the same pass before generating metadata for both repository formats.
The output first goes into a private staging area on the same filesystem. SOW validates it with its own parsers. Before replacing public files, it compares the package list and stat snapshots to detect changes during the build. It does not rehash every package at this stage.
Plain mode does not maintain an operation log. Its file replacements are separate operations, so an interruption can leave incomplete indexes. Run the same sow create command again to rebuild them from the package directory. For a live service, build in a separate directory and switch it into service after the command succeeds.
With --pigsty, SOW removes DEB i386 packages and Patroni 3.0.4 packages, then writes a repo_complete marker containing the retained packages’ SHA-256 hashes. It removes an old marker before changing public files and writes the new marker last. Consumers that use this convention wait for the marker before using the repository.
This mode solves Pigsty’s original problem: replace two toolchains and several containers with one small binary, then quickly produce a repository that real APT, DNF, and YUM clients can consume.
Managed: The Repository Is a State Machine, Not a Directory
A long-lived repository cannot look only at “what is in the directory now.” It must also know what you want, what it published successfully last time, and why the two differ.
SOW’s Managed model has four layers:
A Workspace is the configuration and discovery boundary. A Repository is the ownership boundary. A Dist is a named set of RPM or DEB members. An Architecture View is only a rendered result and does not own package payloads. The most important invariant is this: within one Repository, every package has exactly one canonical payload and no duplicates.
A noarch RPM or all DEB can appear in several architecture indexes without copying its payload. Beta and stable can reference the same package object without creating a second cloud object key. APT and DNF repositories can also live under the same directory hierarchy.
The boundary of “store only one copy” must be stated precisely: it is one Repository or one publication prefix, not an entire Workspace, bucket, or the whole world. SOW does not deduplicate implicitly across Repositories, because deduplication must not destroy ownership boundaries. Deleting one repository must never remove a shared object that another repository still needs.
Desired, Built, and Generation
Managed mode divides repository state into three concepts:
| State | Meaning |
|---|---|
| Desired | The membership set requested by configuration and add/remove operations |
| Built | The last public view that was fully rendered, validated, and committed successfully |
| Generation | An immutable manifest of a particular Built state |
This distinction may sound academic. In practice, it exists specifically to handle failure.
Suppose you add 5,000 packages in one operation. Desired has changed, but the build is killed halfway through with SIGKILL. Without this separation, the system is left staring at a directory tree with no idea how far the update got. With it, SOW retains the previous completed Generation and records the interrupted operation. Recovery can determine which work remains and whether to roll back or continue.
A Generation does not copy the entire repository. It stores an immutable manifest, metadata, and a set of package-payload references; many snapshots can reference the same Pool objects. The exact difference between two Generations is a Changeset: which payloads to add, which metadata to replace, which pointers to switch, and which old objects may be deleted after their retention period.
The transfer set comes from comparing two known Generations. SOW still checks the local tree and remote inventory, but it can avoid transferring unchanged objects.
Publish the Referenced Objects Before the Pointers
SOW publishes repository objects in a defined order:
First, place immutable package payloads. Next, write checksum-addressed metadata and by-hash indexes. Then update the client entry points: repomd.xml, Release, and InRelease. Garbage collection removes an old object only after it is no longer needed by retained Generations or publication targets and its retention period has expired.
This order makes the referenced immutable objects available before their new pointers. The whole repository does not switch in one atomic transaction: views, direct APT indexes, and detached signatures change in separate writes. A client can still need to retry during that transition.
On a local POSIX filesystem, this protocol relies on same-filesystem staging, fsync, atomic rename, stable-path locks, and a durable operation log. Before any Managed write command begins its own work, it checks for any unfinished prior operation and recovers it. Recovery decides whether to roll back or roll forward solely from evidence already persisted on disk. If the recorded state conflicts, SOW stops without applying a guessed repair.
Object stores do not support atomic commits across several keys. SOW therefore persists a commit intent first, advances the protocol pointers in deterministic order, and records an Applied Checkpoint for each target. A filesystem target and an R2 target each have their own evidence; success on the former is never mistaken for success on the latter. In SOW 0.3, R2 garbage collection reports candidates and keeps the remote objects.
This is the essential difference between SOW and a single rclone sync command. Moving files is easy. The hard parts are knowing what to transfer, when the operation counts as committed, which direction recovery should take after failure, and what is truly safe to delete.
At 100,000 Objects, Performance Needs Proof
Most of the work in SOW 0.3 was not adding more features. It was making an already sound model work at real repository scale.
The Plain path now reads, hashes, and parses each package in a single pass, using bounded concurrency through --jobs. Identical input produces byte-for-byte identical metadata. When nothing needs updating, SOW returns no-op and does not replace a public inode merely to bump its timestamp.
The Managed path caches parsed “package facts” in SQLite, keyed by their immutable SHA-256 digest. SOW reads each new package to calculate its digest and parse its metadata on ingestion. Later builds load facts in batches and compute the membership projection in memory. A warm build still walks the public namespace, but for unchanged Pool files it checks only the device, inode, size, mtime, and ctime fingerprint instead of reading every payload again. If the fingerprint drifts, SOW falls back to one authoritative SHA-256 pass and repairs the cache automatically. When you need a full cryptographic audit, run sow check explicitly.
Optimizations like these matter only when they show up in the numbers.
In the project benchmark, membership expansion for a Dist with 5,000 objects fell from about 4.1 seconds to 33 milliseconds. At 50,000 objects, the new implementation finishes in under a second, where the old one did not finish in the benchmark. See the v0.3.0 release notes for the benchmark context. Payload promotion now uses bounded, single-writer group commits, capped at 512 objects or 1 GiB per batch. This both reduces fsync storms and prevents file-descriptor use and recovery state from growing without bound as the repository expands.
These numbers are not there to decorate a benchmark slide. They simply demonstrate that once a repository truly holds 100,000 artifacts, “the state model is correct” is only the passing grade. Whether routine small changes remain cheap enough determines whether the tool stays viable over time.
Tearing Down V1 and Rebuilding from a Minimal End-to-End Core
SOW took a while to build. Midway through, I tore it down almost completely and started over.
That original implementation remains archived as v0.1.0. It was ambitious: Git refs managed repository views, a SHA-256 CAS stored artifacts, and the same system handled upstream synchronization, multi-target cloud publishing, verification, repair, garbage collection, a Cloudflare Worker, CDN purges, edge validation, and production migration.
Many of those features worked, and some paths passed acceptance tests against real APT and DNF clients and a non-production R2 environment. But the flaw was equally clear: the repository model, cloud provider, CDN, edge runtime, and migration workflow were too tightly coupled. Proving one feature correct required half of the rest of the system; every small change dragged a long acceptance matrix behind it.
In the end, I shelved it.
I did not abandon the goal, only the route to it. The worst fate for an infrastructure tool is to have a little of everything without any layer that can be explained and verified on its own. So the second version rebuilt the smallest self-contained slices first:
- P0 / Plain Create: one directory in, one working repository out;
- P1 / Managed Control Plane: Workspace, Repository, Dist, Membership, Build, Generation, Check, Changes, and Operation Log;
- More complex synchronization, remote publishing, CDN, and provider control planes went back into separate acceptance queues, one capability at a time.
v0.2.0 established today’s Plain + Managed foundation: a single package pool, metadata views, deterministic builds, locks, logs, crash recovery, Generations, and publication to filesystems and R2.
v0.3.0 introduced no new conceptual layer. Instead, it removed the old V1 runtime, tightened the cloud-transfer boundary, and fixed repeated reads, per-object queries, payload commits, and observability at large scale in both Plain and Managed modes. The current release binary depends only on the new V2 core. The old implementation remains in Git history and the v0.1.0 / v0.2.0 tags as a record of what we learned, not as a second source of truth.
This path looks slower than building everything for one grand reveal, but it is faster. Every layer has an independent contract, explicit failure semantics, and acceptance tests against real clients. The next layer rests on solid ground, not on an increasingly unreadable wish list.
Where SOW Goes Next
SOW 0.3 can create repositories, manage membership and snapshots, calculate changesets, and publish to filesystems and R2. It is still some distance from the complete software-artifact control plane I have in mind.
The roadmap has four main tracks:
- Upstream repository synchronization. Consume upstream APT/YUM indexes directly, verify signatures and digests, fetch only missing artifacts, and bring mirror results into the same Package Object, Membership, and Generation model.
- More complete incremental delivery. Today,
changesand target checkpoints already make changesets explicit and reusable, so SOW can publish only the delta. Next comes support for more object stores and synchronization providers, with large remote inventories, resumable transfers, conditional writes, and safe-deletion evidence forming a reliable end-to-end system. - CDN and cache control. A CDN purge is not merely “call an API.” It must be bound to an exact Generation, cache TTL, receipt, and failure-recovery protocol. V1 proved this path can work, but it will return as an independent, testable module rather than being welded back onto the repository core.
- Version and retention policies. Dist, Generation,
retain, and target can already express beta, latest, stable, and monthly snapshots. Higher-level policy orchestration will eventually make common release cadences possible without hand-wiring them through external scripts.
Most of these capabilities existed in some form in the first version. I will not port the old code wholesale. As with 0.2 and 0.3, I will bring back one sharply bounded, independently testable capability at a time.
No more disappearing to build the grand design in one shot. Ship small, complete systems continuously.
The Pig Family Keeps Growing
SOW is part of Pigsty’s increasingly elaborate porcine universe. The naming scheme keeps getting more ridiculous—and more complete:
- Pigsty: the sty, responsible for installing and managing the PostgreSQL ecosystem;
- SOW: the mother pig, responsible for organizing, building, and publishing software repositories;
- Boar: the male pig, a graphical control plane for Pigsty now under development;
- Silo: the grain bin, responsible for S3-compatible object storage;
- Oink: the sound pigs make, powering the documentation and website framework;
- Snort: the pig rooting around, collecting logs and monitoring metrics.
The names are jokes first, of course. But behind them, a complete chain is taking shape: SOW organizes the artifacts; Silo stores them; Pigsty installs them into running systems; Snort watches those systems; and Oink explains everything. SOW fills the part of that chain that was easiest to overlook.
A software repository looks like a directory that Nginx can serve. But once it carries 100,000 objects across multiple operating systems and architectures for countless users, it is really a headless database. It has objects, relationships, versions, transactions, logs, garbage collection, and commit pointers that absolutely must be correct.
SOW makes those hidden rules explicit. It replaces the hopeful assumption that “the legacy scripts are probably fine” with an engineering contract backed by checks, recovery, and auditability. If all you want is an offline repository, start with one command:
If you also maintain a long-lived software distribution, visit the SOW project site or go straight to the documentation to see what lies beneath.
SOW is licensed under the Apache-2.0 license. The v0.3.0 release described here provides amd64 and arm64 archives for Linux and macOS, plus RPM and DEB packages for Linux. Get the latest release from the download page, or browse the source code.
A hundred thousand packages aren’t frightening. Treating them as nothing more than a hundred thousand files is.
Originally published on vonng.com.
2 - How a Packaging Patch Broke Valkey’s Memory Accounting
I was updating the Redis module in Pigsty recently, adding Valkey as an alternative engine, and found a bug in an inherited packaging patch.
In affected Valkey DEB packages, failed synchronous saves can corrupt used_memory until it reports a value near 2^64.
With maxmemory set, commands subject to its limit then return OOM errors. Restarting the server resets the counter.
There can be plenty of free memory. The log records the save failure, but not a separate memory-accounting error. used_memory_rss stays normal and RSS graphs are flat. The only number moving is used_memory, and nobody alerts on memory usage slowly going down.
A build that applies the same patch and links the bundled private jemalloc can crash on the first failed SAVE. Pristine upstream source does not contain the patch and is unaffected.
The cause is a single line in a packaging patch. Its lineage goes back to 2017, it was fixed once in Redis, came back in Valkey, and eventually got copied into Valkey’s own release pipeline — so all five official DEB packaging lines examined in this investigation—7.2, 8.0, 8.1, 9.0, and 9.1—carried it. The official RPM packaging did not.
The packaging fix merged on August 7, 2026. Here is how it surfaced.
What’s wrong
Valkey logs the working directory on two RDB error paths: failure to open the temporary file and failure to rename it to the final snapshot name. Upstream uses a stack buffer, so there’s nothing to release.
Debian’s packaging patch moves it to the heap:
The motivation is fine: keep a 4 KB array off the stack.
The problem is the last line. get_current_dir_name() allocates through glibc’s malloc(). zfree() is Valkey’s own deallocator. They are not interchangeable.
The usual reaction is “it’s a free, what could go wrong”. But zfree() isn’t a wrapper around free(). It does three things: ask the allocator how big the block is, subtract that from used_memory, and then release it.
Step two is wrong unconditionally. This allocation never went through Valkey’s allocator, so it was never added to used_memory — and used_memory is unsigned, so subtracting past zero wraps it.
Whether steps one and three break depends on which jemalloc the binary was linked against. With system libjemalloc.so, as used by the affected DEB packages, global malloc() and Valkey use the same allocator. The pointer belongs to the right heap, so nothing crashes and you only get the corrupted counter. With the bundled private jemalloc — upstream’s default — it can’t find the pointer in its own bookkeeping and dereferences null:
Look familiar? Same top-of-stack as redis/redis#7927 from 2020 — the same bug, in Redis.
The saving grace is that only synchronous saves in the main process accumulate. BGSAVE and scheduled saves fork, so the damage dies with the child. The real-world trigger is a full disk, wrong permissions, or a read-only filesystem, combined with something calling synchronous SAVE in a loop: a monitoring script, a backup cron, a client that retries.
It waits until you already have a problem, then quietly adds a second one.
How I found it
Not through any clever analysis. Upstream’s own test suite caught it.
While reworking the Valkey DEB packaging for Pigsty, I ran runtest as usual. Three cases in unit/shutdown failed and the server left a crash report.
Our packages use the bundled jemalloc, so we were on the crashing side — louder symptoms than the official packages, and much easier to catch. Following the crash trace up, then opening debian/patches/0003-*.patch, and there it was.
The relevant test, by the way, deliberately creates a directory named dump.rdb to force rename(2) to fail — which is exactly one of the two patched paths.
Once I understood the mechanism, the first thing I did was disbelieve myself: two Valkey versions, three builds each (pristine, patched, patched-and-fixed), against three allocators, on two architectures. The result held.
Then I spent longer than the technical work checking whether someone had already reported it. Worth noting one trap: two of Debian’s search endpoints were broken at the time and returned zero rows for bugs I knew existed. Without a “this query should definitely return something” control, I’d have walked away with a confident false negative.
I found no dedicated Debian bug report for this Valkey defect.
Where the line came from
The patch header goes back further than I expected:
-
2017 — Chris Lamb writes the patch for Debian’s Redis packaging.
-
2020 — a
zfree(cwdp)shows up in Redis’s copy and blows up: redis/redis#7927 and Debian #972683, fixed by switching to libcfree(). -
Debian’s Valkey packaging is derived from the Redis packaging, patch included.
-
In October 2024, a change to fix the leak adds
zfree(cwdp)again. It first ships in Debian Valkey8.0.2+dfsg1-1;8.0.1+dfsg1-1is unaffected. The same mistake, in the same patch lineage, four years apart. -
March 2025 — Valkey maintainer zuiderkwast, reviewing these Debian patches, spots the mismatch immediately:
…the latter uses
malloc()(rather thanzmalloc()) and we later free it usingzfree(). This means it will mess up the memory usage tracking done in zmalloc and zfree. Therefore, we may not want to take this patch, at least not unmodified.Correct diagnosis. He assumed the damage stopped at broken accounting, and the issue lost priority when jemalloc upstream was archived.
-
April 2026 — Valkey builds an automated pipeline covering five version lines, four distributions, and two CPU architectures, and copies Debian’s patch set wholesale. The official
.debs inherit the bug.
The Debian packaging rules leave override_dh_auto_test empty, with the upstream regression-test commands commented out. The pipeline checks installation and service behavior, but those checks do not exercise the failing RDB paths.
Reporting it
Two channels, handled separately.
Debian takes email. No account needed — send a plain text message to [email protected] with pseudo-headers as the first lines of the body:
My first attempt bounced, saying the body didn’t start with Package:, so “your message has been ignored completely”. Which was baffling, because it did:
The answer was in the message-id: it was Apple Mail, which sends rich text by default. BTS only parses plain text, so it never saw the line.
So if you’re filing a Debian bug from a Mac: switch to plain text first (Format → Make Plain Text), and turn off smart quotes, or the quote characters in your patch get mangled and the patch is worthless. Resent, it went through as #1143239.
Upstream took a comment, then a PR. zuiderkwast’s year-old comment was the natural hook, so I picked up from there with what he didn’t have: under the bundled allocator this is a segfault, not drift; the upstream test suite already catches it; and the official .debs have it today.
He asked for a PR on August 3. The change is one line:
zlibc_free() exists in Valkey for exactly this case — it’s defined specifically so callers can reach the real libc free().
Which also explains how zfree got picked in the first place. A plain free(cwdp) does not compile: Valkey deliberately marks free() deprecated and builds with -Werror. One possible explanation is that the compiler rejected free() and the author chose the closest-looking name.
A defensive measure that pushed someone into the hole it was guarding.
Outcome
zuiderkwast approved the same day, with a question: why keep this patch at all? The path is only used in one error message; just go back to the stack buffer.
Fair, and I’d listed dropping it as the alternative in the PR description. Since he was leaning that way, I did it downstream first — removed the patch entirely from Pigsty’s Valkey and Redis packaging, rebuilt, and reported back: the shutdown tests pass, and ordinary paths still appear in the error message. If a path exceeds the stack buffer, upstream reports unknown. The problem goes away along with the patch.
Then it went somewhere I didn’t expect. zuiderkwast turned to the maintainers who built the pipeline:
Why did we copy Debian’s patches?
If some things need to be patched, that’s better fixed upstream in Valkey itself. Bugs in this repo’s patches are harder to spot than bugs in Valkey main repo IMHO.
That last sentence is the best summary of the whole episode.
The PR merged on 2026-08-07. It corrects both deallocation sites in all five packaging lines. The maintainer who built the pipeline replied “Thanks for the notification. Will investigate this!” — a review of the whole patch stack is underway.
As of this article’s publication on August 9, the Debian report had received no maintainer reply. The investigation also found the same defect in bookworm’s Redis 5:7.0.15-1~deb12u7 package. I included that finding in the Valkey report but had not filed it separately.
Takeaway
Nobody involved did anything stupid.
Lamb’s 2017 patch was legitimate packaging hygiene. Whoever added zfree(cwdp) was fixing a real leak and picked the wrong deallocator — pushed there by free() being deliberately deprecated. zuiderkwast spotted the mismatch a year ago and diagnosed it correctly; he just underestimated the consequence. Copying Debian’s patches into the release pipeline was the pragmatic move, and Debian’s packaging quality is famously good.
Every step was reasonable. The result sat quietly across five product lines and four distributions.
Packaging patches are where bugs hide. They can be absent from upstream CI, upstream code review, and the source history users normally inspect. They get copy-pasted across projects (Redis → Valkey) and across organizations (Debian → Valkey’s own pipeline), losing a little context each time.
And the reason this surfaced at all: that test case was sitting there the whole time, creating a directory named dump.rdb to force exactly this failure. The official DEB build had the regression-test commands commented out, so this test did not run there.
Packaging is dull work. Run the tests anyway.
Postscript: a question at PGConf
At PGConf this year I gave a talk called Extension for Everyone. Afterwards Christoph Berg, who maintains the PGDG APT repository, asked me a good question:
How do you actually test all these packages?
Pigsty maintains packaging for a few hundred extensions and components. The packaging repos alone carry over a hundred patches, a good share of them fixing small, fiddly details. One person cannot hand-verify every path in every package. There isn’t enough time.
My answer: beyond whatever tests the package ships with, I have Codex run a smoke pass — model how the software actually gets used in production and try to hit the things that break.
It turns up a lot. This bug is the biggest thing it has found so far.
Which is worth sitting with for a moment: why did this survive so long?
Valkey is not an obscure project anymore; it’s the default Redis replacement in several distributions. Debian is about as mainstream as it gets, with famously good packaging. Valkey’s maintainers are excellent engineers — zuiderkwast diagnosed the mechanism correctly a year ago just by reading the patch. And Redis’s own packaging copies Debian’s patch the same way.
So the missing ingredient was never capability. It was patience.
Spending a full day on a footnote-grade bug in a cold error path — comparing patch states, allocators, versions, and architectures, reproducing the crash thirty times, tracing a patch lineage back a decade, checking five different search endpoints to confirm nobody had reported it — does not pay off on a human time budget. It is too boring.
Which happens to be exactly the kind of work an agent is good at.
End to end on this one: it ran the tests, built the reproduction matrix, wrote the patch, drafted the Debian report, and worded the exchanges with the maintainers on GitHub. My job was assigning the work, pushing back, and making the final calls.
I should be clear that this is not “the AI does everything and I put my feet up”. During this investigation I had a second agent do an adversarial review of my draft at maximum effort, and it pulled out four factual errors in one pass: I had misremembered how the old Debian bug was actually fixed, FLUSHDB doesn’t reach this code path at all, “refuses every write” overstated the impact, and I had missed entirely that bookworm’s Redis is still affected today. Sending any of that to maintainers as-is would have been my embarrassment, not the model’s.
So the value isn’t that the agent gets it right the first time. It’s that the cost of making it disprove itself over and over is low enough to be worth doing — where a human would decide that six builds to chase a statistics counter isn’t worth the afternoon.
The change in pace is startling. Two years ago every step in that loop was mine to do by hand. Now I sit here handing out tasks, asking follow-up questions, and signing off — and the rest of it just runs.
Originally published on vonng.com.
3 - Instantly Clone PostgreSQL Databases—No Black Magic Required
Six months ago, on January 8, 2026, I wrote Git for Data: Instant PostgreSQL Database and Instance Cloning, introducing a new feature in PostgreSQL 18 and Pigsty v4.0: instant database cloning. With filesystem copy-on-write (CoW) and PostgreSQL 18’s new file_copy_method = clone setting, you can clone a huge database in seconds while initially sharing its data blocks.
This is a particularly good fit for AI agents. As I wrote in What Kind of Database Do AI Agents Need?, ultra-low-cost database cloning is critical for counterfactual experiments. So when Pigsty 4.0 shipped, I added this capability to its PostgreSQL database provisioning workflow.
Today I saw an article from Aliyun announcing support for this feature in Alibaba Cloud RDS for PostgreSQL. I couldn’t help laughing: that took them long enough. The feature itself is not complicated. It requires no kernel patch—just enable one setting in PostgreSQL 18 and add a STRATEGY option when creating the database. But simple as it sounds, a robust implementation still has a few edge cases to handle.
A Few Improvements
Previously, cloning a database through Pigsty’s IaC-style workflow was still somewhat cumbersome: first define the clone with another database as its template, then run the database creation workflow.
So for the Pig v1.5 release, I turned database cloning into one simple command: pig pg clone. If you have a database named meta, just run pig pg clone meta, and it automatically creates a clone.
You can, of course, customize its behavior with options—for example, by specifying the branch name. If you do not provide one, Pig generates names sequentially by appending an underscore and a number.
The command automatically detects whether instant cloning is both enabled and supported. At the moment, Pigsty on XFS satisfies those prerequisites. If so, Pig performs an instant clone. If not, it warns you, waits for confirmation, and performs a conventional clone instead. Use -y to skip the confirmation.
As long as the underlying filesystem supports CoW, such as XFS, cloning a database takes essentially constant time—usually a few hundred milliseconds—and initially needs only metadata space. Subsequent writes allocate new space for the modified blocks.
Agent-Native CLI
This command-line tool is built specifically for DBAs and DBA agents. You could already clone a database with an Ansible playbook or Pigsty’s /pg/bin/pg-clone shell script, but neither is as convenient as using the pig CLI directly.
For example, before executing an operation, you can use --plan to print the plan. It tells you what Pig will do and what risks are involved. You can also use -o json or -o yaml to return results in JSON or YAML.
Incidentally, both normal command output and help output are available as text, JSON, or YAML. That is especially useful to agents: they can explore the CLI and retrieve exactly the structured help they need. The pg clone and pg fork commands provide structured results.
I call this design an Agent-Native CLI, and I have written about it before.
Instance-Level Forks
Database cloning is not the only related new feature worth mentioning. In Git for Data: Instant PostgreSQL Database and Instance Cloning, I also covered instant instance-level cloning, which I call a “fork.”
Run pig pg fork dev, and Pig creates an instance named dev from the current instance, assigning it a new random port. This is extremely useful when recovering from an accidental deletion: first create a temporary fork, then quickly run an incremental PITR on the fork to roll back and validate the result. Once you know the recovery is correct, perform it on the main instance.
PITR with pig is now very convenient as well. The example below performs an end-to-end point-in-time recovery to a specific timestamp, coordinating shutdown, restore, and the subsequent startup steps. You can still use pig pgbackrest when you want precise control over every operation.
This release of the pig CLI adds many management features, including operations for PostgreSQL, Patroni, and pgBackRest. They are now all exposed as Agent-Native CLI commands like pg clone and pg fork, making them equally convenient for human DBAs and AI agents. I will write a dedicated article about them in a few days.
Further Reading
- Git for Data: Instant PostgreSQL Database Cloning
- Put Your AI Agent’s State in a Database
- What Kind of Database Do AI Agents Need?
Originally published on vonng.com.
4 - What Is a PostgreSQL Distribution?
People often ask me: what exactly is Pigsty?
My usual answer is: a PostgreSQL distribution.
The next question is usually: what, then, is a “PostgreSQL distribution”?
That is a good question. And the best place to begin is not databases, but operating systems.
I. Start with Linux Distributions
When most people hear “distribution,” they think of Linux distributions: Red Hat, Debian, Ubuntu, SUSE, Arch, and so on. But if Linux already exists, why do we need Linux distributions? What exactly is the relationship between the two?
The distinction is simple: the Linux project develops the kernel; a distribution assembles the operating system around it.
Compile the Linux kernel and you still do not have a usable machine. There is no shell, init system, C standard library, coreutils, package manager, networking toolkit, user space, or security-update policy. The kernel schedules hardware and exposes system calls, but an industrial-scale gulf separates that from “a usable operating system.”
Someone has to bridge that gulf, and there are countless ways to do it. glibc or musl? systemd or OpenRC? apt, dnf, or pacman? A release every six months, or rolling releases? What should the default security policy be? How are packages signed? How are vulnerabilities patched? How long are versions maintained? Which services are enabled by default? The accumulated answers to those questions are what make a distribution.
A distribution, then, does not merely deliver a kernel. It delivers an integrated set of decisions—and the credibility of an organization willing to stand behind those decisions over time.
Nobody says Debian, Red Hat, and Ubuntu are competing with Linus over who writes the better kernel. They compete at a different task: turning a shared kernel into a system that is more reliable, consistent, and easier to ship.
The shared kernel gives distributions a common foundation. Their integration, release, and maintenance work turns it into a usable system.
That is the key to understanding PostgreSQL distributions.
II. Apply the Analogy to PostgreSQL—Carefully
PostgreSQL is often described as the Linux kernel of the database world. But if you map the Linux analogy directly onto PostgreSQL, it breaks almost immediately.
PostgreSQL is not the Linux kernel. Compile PostgreSQL from source, run initdb, and start the server; you have a working database. The SQL engine, transactions, MVCC, WAL, replication protocol, psql, and client libraries are all there.
The official PGDG repositories also ship prebuilt binaries that users can install and start directly.
The Linux kernel is not useful on its own, but the PostgreSQL kernel can run independently. That raises an obvious question: if PostgreSQL already works by itself, what problem is a PG distribution supposed to solve?
A standalone PostgreSQL instance is an excellent database kernel. A production system, however, needs more than “it starts.” If the primary fails, what takes over? Who notices when a backup is corrupt? Can you recover to a point before data was accidentally deleted? How does the connection pool redirect traffic? How are certificates rotated? How are metrics collected and alerts evaluated? How are extension versions managed? How is configuration drift corrected? How do upgrades work? How is a new replica provisioned? After recovery, what closes the loop and returns the system to a healthy steady state?
Neither initdb nor yum install postgresql answers those questions.
That is where a PG distribution earns its keep. It does not turn PostgreSQL into a usable database—PostgreSQL already is one. It integrates the PostgreSQL kernel into a production-ready data service.
III. The Three Layers of a PG Distribution
A serious PG distribution has at least three jobs: selection and integration, build and distribution, and orchestration and control.
All three matter, but their marginal value is not equal. The further down the list you go, the closer you get to the real battleground.
1. Selection and Integration: Make Decisions for the User
Production PostgreSQL is not a bare postgres process. You need backups, high availability, connection pooling, monitoring, logging, alerting, object storage, extensions, an access-control model, and sensible defaults. Every category offers a long list of choices.
For backups, you might use pgBackRest, Barman, WAL-G, pg_basebackup, or a hand-rolled script built on PostgreSQL’s backup primitives.
For high availability, there is Patroni, repmgr, Pacemaker, or even Pgpool-II pressed into service for primary/standby failover. Monitoring might mean Prometheus, VictoriaMetrics, Grafana, Zabbix, or any number of combinations.
This layer tests a distribution author’s judgment, experience, and sense of responsibility. Being opinionated does not mean making arbitrary choices for users. It means having learned from enough failures to know which paths are sound—and which are better.
To be fair, differentiation at this layer is narrowing. Good tools used for long enough tend to produce community consensus: Patroni is increasingly hard to avoid for HA, pgBackRest for backups, and combinations such as Prometheus and Grafana for monitoring. Selection still matters, but “I chose the right components” is no longer much of a moat by itself.
Choosing well is not enough. You also have to deliver those choices reliably.
2. Build and Distribution: A Supply Chain Is Trust, Not a Gimmick
The second layer is build and distribution. It is often underestimated because users see a package name, not the unglamorous work behind it: multiple operating systems, architectures, versions, and extensions; dependency resolution; ABI compatibility; GPG signing; CVE response; repository availability; and version lifecycle management.
PGDG already provides a formidable piece of public infrastructure. Its YUM and APT repositories deliver prebuilt PostgreSQL binaries, more than a hundred extensions, and several critical ecosystem components. It is an excellent commons.
Precisely because that commons is so good, differentiation at the build-and-distribution layer requires something more. Pigsty’s own repository, for example, fills in a large part of the missing extension catalog—another 300 extensions—along with infrastructure packages. It ships native RPM and DEB packages across 16 Linux OS-and-architecture combinations and has been maintained continuously for almost four years.
Long-term credibility in packaging, fast patching, a stable supply chain, and a sustained track record of reliable maintenance do form a barrier—and that barrier compounds over time. But this is a defensive capability. It can make users comfortable basing production systems on your repository, yet by itself it rarely explains why they must choose you.
What truly separates a distribution from an “install some packages” script is the next layer: orchestration and control.
3. Orchestration and Control: Turn Static Packages into a Living System
The hardest part of a distribution is orchestration and control. Judgment about component selection is converging, while build and distribution are largely defensive. Orchestration and control are where implementations are tested against one another in the real world. The challenge fits into one sentence: How do you turn all these static packages into a dynamically running service?
Think of a software repository as a supplier of flour, eggs, and butter. It does not bake them into a cake. Even if every package comes with a detailed recipe—that is, documentation—you are still a long way from a finished cake. And some production systems do not merely need a cake; they need an automated cake factory.
The gap between many established open-source distributions and cloud RDS offerings lies precisely in this last mile. One gives you a collection of installed packages. The other sells a ready-to-use service with automated operations and self-healing. The indispensable step between them is orchestration.
Orchestration is the act of “cooking”: turning something static, like software on a DVD, into a live, dynamic system. It must handle everything the PostgreSQL kernel leaves behind after initdb, and everything a package repository never attempts to manage: which components start in what order and what depends on what; how a failed primary is detected, a new leader elected, traffic redirected, pooler connections reestablished, and a replacement replica provisioned—the entire closed loop of failure recovery; and, most importantly, how the whole system remains at its declared desired state and automatically corrects any drift.
Orchestration and control still require substantial integration. Kubernetes, Patroni, and Ansible provide shared building blocks, but each distribution must connect them into a complete operating procedure.
IV. Two Paths to Orchestration: Kubernetes-Native and Linux-Native
Once orchestration becomes the central problem, the question is: at what layer should the control plane live?
There are two mainstream answers, and they define the two principal tracks for PG distributions today. The essential distinction is where orchestration and control are implemented: one path builds on Kubernetes as a common substrate; the other returns to the Linux operating system and builds upward from there. Neither is universally better. They make different tradeoffs.
The GitHub star counts below are from July 4, 2026.
Path One: Kubernetes-Native
This path treats the database as a first-class citizen on Kubernetes and uses the Operator pattern for orchestration. You submit a declarative custom resource defined by a CRD, and the Operator’s reconciliation loop continuously drives actual cluster state toward the desired state. Provisioning, monitoring, failover, and scaling all happen at the Kubernetes layer.
This is the busiest and most crowded track. Its players include CloudNativePG, led by EDB, with roughly 8,900 stars; the Zalando Postgres Operator, with about 5,200; Crunchy PGO, with about 4,400; KubeBlocks, with about 3,100; and a longer list including StackGres, Kubegres, Tembo, KubeDB, and the Percona Operator.
The advantages are clear: a unified control plane, a declarative API, smooth GitOps integration, and an easy interface for platform teams. For organizations that already treat Kubernetes as their operating system, putting databases on Kubernetes is a natural extension.
The cost is equally clear. You are not merely adding a PG Operator. You are adopting the entire Kubernetes control plane, storage and network abstractions, scheduling model, authorization model, failure model, and cognitive load. The real entry cost is not the Operator itself, but whether your organization has already paid the Kubernetes tuition.
Path Two: Linux-Native
This path does not put the database inside Kubernetes. It returns to the operating system: run directly on Linux, on physical or virtual machines; install RPM or DEB packages; manage services with systemd; and drive administration with Ansible or a similar infrastructure-as-code tool.
The Linux-native open-source projects include: Pigsty, with roughly 5,200 stars; Autobase, with about 4,300; pgEdge, with about 700; and EDB TPA, with about 90. Percona Distribution for PostgreSQL also provides an open-source stack with commercial support. Commercial products include EDB Postgres Advanced Server, CYBERTEC PGEE, and the ClusterControl management platform.
The advantages are fewer infrastructure layers and direct use of familiar Linux tools. DBAs can inspect the processes, files, and services themselves. The cost is that Kubernetes is no longer providing desired-state reconciliation, idempotent execution, failure recovery, or upgrade orchestration. You have to implement those capabilities yourself while also confronting the differences across more than a dozen major versions of mainstream Linux distributions. It is continuous, unglamorous work.
Pigsty’s Choice
Both paths are reasonable. The right one depends on where your team already stands and where it makes sense to place the complexity. Pigsty chose the Linux-native path. I have explained the reasoning at length in Should Databases Be Deployed in Kubernetes? and Is Containerizing Databases a Good Idea?. My view is that this path better fits the nature of databases. It is difficult, but correct.
Among the projects listed here, Pigsty has the most GitHub stars on the Linux-native track and ranks second overall, after CloudNativePG.
In my own queries to mainstream AI models about self-hosting PostgreSQL on Linux, Pigsty often appears among the first recommendations. For a project led by an independent developer and unaffiliated with any cloud vendor, reaching that point has not been easy.
V. One Step Further: A Meta-Distribution
The story could end there. But Pigsty does something else that pushes at the boundary of the term “PG distribution.”
The industry usually assumes that a distribution is built around one fixed kernel. Debian and Red Hat are built around Linux. Traditional PG distributions are built around the vanilla PostgreSQL kernel. A distribution and its kernel are almost inseparable.
The PostgreSQL world, however, contains some unusual variants. OrioleDB replaces the storage engine. Babelfish adds SQL Server protocol compatibility. PolarDB for PostgreSQL implements a RAC-style architecture. IvorySQL supports Oracle syntax. openHalo is MySQL-compatible, while Percona TDE adds transparent encryption. These projects modify the PG kernel. Strictly speaking, they are no longer “pure PostgreSQL,” but distinct species and subspecies within the PG-compatible family. Traditionally, every variant would need to build its own operational stack.
Pigsty takes another approach: extract the orchestration and control foundation, and make the kernel itself a replaceable layer. This is the natural consequence of taking the third layer far enough. Once the control plane is sufficiently flexible and no longer hard-wired to a specific kernel, replacing that kernel becomes a matter of swapping a build artifact and a configuration template. We build binary packages and provide configuration templates for these different PG forks, allowing users to run different kernels on the same foundation. At the time of writing, Pigsty offers more than a dozen kernel and application-stack configurations.
In that sense, Pigsty is no longer merely a “PostgreSQL distribution.” You can use it to derive a distribution of your own: an IvorySQL distribution, a PolarDB distribution, or a TDE distribution. By combining modules, you can even turn it into a distribution for Redis, Etcd, or MinIO; for Prometheus or VictoriaMetrics; or even for Claude Code and Codex.
More precisely, it is a meta-distribution: a distribution for building distributions. A foundation that can be repeatedly tailored, reused, and redistributed is itself a transferable capability. It no longer belongs to one kernel, or to one person.
The following examples use the Pigsty configuration layout from July 2026:
Epilogue: A Distribution Is a Supply Chain of Trust
Return to the original question: what is a PostgreSQL distribution?
At the technical level, it has three core jobs. Selection and integration make the right decisions on your behalf. Build and distribution deliver the artifacts created by those decisions. Orchestration and control turn those static packages into a living, self-healing system.
But the real soul of a distribution lies beneath those three technical layers.
Technology can be copied. Component choices can be imitated, packages can be rebuilt, and anyone willing to invest enough time can reproduce 70 or 80 percent of the orchestration. Two things cannot be copied—and determine whether a distribution can be trusted for the long term: a community that continues to use and maintain it, and the trust that grows from that work over time.
Users do not merely need an answer to “Which package should I install?” They need answers to harder questions. Whose packages do I trust? Whose defaults? Whose extension builds? Whose HA decisions and backup-recovery process? Who patches a CVE promptly? Who will still maintain this path five years from now? When configuration drifts, a failover occurs, a version is upgraded, or data must be restored—at the moments that matter most—who can bring the entire system back under control?
The answers point not to a piece of code, but to the person and community that continue to take responsibility for it. The essence of a distribution is to gather responsibilities scattered across source code, builds, signatures, repositories, extensions, configuration, orchestration, monitoring, upgrades, and disaster recovery into a supply chain of trust that can be verified, reproduced, audited, and relied upon over the long term. Trust accumulates as promises made along that chain are honored again and again. It cannot be bought or copied. Only a community can grow it over time.
Cloud services provide trust too, but they hide the chain inside a black box. You buy managed trust, while surrendering transparency, portability, and ultimate control. You trust the cloud vendor to choose the right components, apply patches, maintain backups, handle failures, and plan upgrades. You also trust it not to box you in on pricing, access, ecosystem control, compliance, or availability. That is still trust. Its price is that you can no longer inspect or take over the chain yourself.
Pigsty takes another path. It does not mystify complexity or outsource responsibility to an invisible control plane. It makes the chain visible, codifies it, signs it, orchestrates it, and returns as much control as possible to the user. Pigsty is therefore not a “tool for installing PostgreSQL,” nor merely a “script for building your own RDS.” It aims to deliver an open PostgreSQL supply chain of trust: from the upstream kernel to extension artifacts, from RPM and DEB repositories to HA orchestration, from monitoring and alerting to backup and recovery, and from a single PG kernel to the entire family of PG-compatible kernels. Every link can be verified, and every link can be brought under the user’s control. Behind it stands a community willing to maintain it for the long term.
What Linux distributions ultimately accumulated was never just the technical ability to integrate a kernel into a system. It was the credibility earned by names such as Debian and Red Hat through decades of simply continuing to be there. Pigsty aims to grow that same kind of trust in the PostgreSQL world—and to keep it open, auditable, and under the user’s control.
A real distribution ultimately delivers not software, but a supply chain of trust that can be audited, reproduced, migrated, and relied upon for the long term—together with a community willing to stand behind it.
No rented cloud. No vendor worship. No putting complexity—or trust—inside anyone else’s black box.
Instead, put the ability to run a first-rate production database service back in the hands of users willing to run it themselves—along with a community willing to support it for the long haul.
Originally published on vonng.com.
5 - Extensions for Everyone
Slide deck: Extensions for Everyone
Part I. Introduction
0. Extensions for Everyone
Hi everyone. This talk is called Extensions for Everyone.
It is about delivering PostgreSQL extensions, and about how a shared delivery layer can benefit users, extension authors, vendors, and PostgreSQL hackers.
1. Who am I
I am Ruohang Feng, author and maintainer of Pigsty, an open-source PostgreSQL distribution.
I also build pgext.cloud, an open-source delivery layer for PostgreSQL extensions.
Over the past two years, I have been cataloging, building, packaging, and testing hundreds of extensions across PostgreSQL versions and Linux platforms. So this talk is not a theory. It is a field report.
2. Extensibility Matters
Extensibility matters. Two years ago, I wrote that PostgreSQL is eating the database world.
The argument was simple: PostgreSQL wins through extensibility. It lets the ecosystem move quickly without forcing every new idea into core. That is the superpower, but it also creates a practical problem.
If PostgreSQL grows through extensions, then extension delivery becomes part of the system.
Extensibility alone is not enough. An extension only matters when it can be found, installed, and trusted.
That is why I began collecting and packaging extensions.
3. Two Years Later
Two years later, I have been building open-source infrastructure for PostgreSQL extensions. It is called pgext.cloud.
Today, it ships across sixteen Linux targets and five active PostgreSQL major versions. Together with PGDG and contrib, the deliverable set is about 511 extensions.
The repository serves roughly one million downloads per month. Several PostgreSQL vendors now deliver their extensions through it. But this talk is not mainly about the repository.
The main point is what we have learned while maintaining this matrix. That is what I want to share today.
4. Who Benefits?
When I say “extensions for everyone”, I mean four groups of people.
First, users and DBAs. They want packages. They do not want to compile code on production servers.
Second, extension authors. They need reach, and they do not want to spend their time on build and delivery work.
Third, vendors. They need reusable components. Rebuilding the same packages again and again is a waste of engineering time.
Fourth, PostgreSQL hackers. They need signals. When compatibility breaks, extensions are often where we see it first.
So this is about a shared delivery layer. Not just for convenience, but also for visibility. Before we talk about delivery, let us look at the ecosystem. We need to understand what we are trying to deliver.
Part II: The Ecosystem Landscape
5. Galaxy
How many PostgreSQL extensions exist? There is a well-known community-maintained GitHub list with more than a thousand entries. The catalog I maintain currently tracks about 1,617 entries.
But this number needs context.
Some projects are active. Some are abandoned. Some are only available on cloud platforms. Some depend on a dedicated PostgreSQL fork. Some are just ideas and examples. So 1,617 does not mean 1,617 installable extensions.
It means the ecosystem boundary is large and messy.
6. GitHub Stars
The first public signal is GitHub stars. Stars do not measure quality. They do not measure production usage. They also undercount projects whose main development happens outside GitHub, such as PostgreSQL and PostGIS.
But stars are still useful. They show attention, reputation, and rough awareness. Familiar names appear near the top: TimescaleDB, pgvector, Citus, pg_search, pgml, pgai, pgmq, and many others.
If we look at the distribution, it is extremely skewed. A few extensions get most of the attention, followed by a very long tail. It is a long-tailed distribution.
7. Star Tiering
If we group extensions by order of magnitude, we get a simple tier model.
I put four widely recognized extensions in tier zero: PostGIS, TimescaleDB, pgvector, and Citus. The remaining tiers use GitHub star counts.
Tier one: 44 extensions, between one thousand and ten thousand stars.
Tier two: about 152 extensions, between one hundred and one thousand stars.
Tier three: about 373 extensions, between ten and one hundred stars.
And then a long tail of around 748 extensions below ten stars.
This is not a quality ranking. Some popular projects are no longer active, such as pgml or zombodb. Some low-star extensions are quite useful.
But the tiers tell us something. The visible ecosystem is much smaller than the discovered one. Adding tiers zero through three gives about 570 prominent or starred extensions, which is close to what is actually deliverable.
8. The Extension Funnel
This gives us a funnel. At the top, there are about 1,600 candidates. If we cut the long tail, the number drops quickly.
In the middle, about 500 are already cataloged, packaged, and delivered.
We can split this by source: about 330 from the Pigsty repository, and 160 from PGDG, with some overlap between the two. At the very bottom, 71 contrib extensions are shipped with Postgres itself.
The point is the shape. Discovery is broad. Delivery is narrower. Usage is narrower again.
9. Dimension Analysis
The catalog also tracks dimensions beyond stars: language, license, category, last release date, repository status, packaging status, PostgreSQL version support, and operating system support. We can browse 32 different dimensions here.
Now let us move from “what exists” to “what can actually be delivered”.
Part III: The Delivery Layer
10. The Status Quo
Packaging PostgreSQL extensions is hard. Not because package formats are mysterious, but because the matrix is large. We are talking about 5 active PostgreSQL major versions times 16 Linux platforms. That is 80 build slots per extension. Only a handful of extensions actually cover all of it.
The PGDG YUM and APT repositories, maintained by Christoph and Devrim, already do foundational work. They carry many of the most important extensions, around 150 packages in total. But there are still gaps: Rust extensions, for example, and some operating system plus PostgreSQL slots that are not filled.
So the complementary repository aims to fill that gap. It adds packages where PGDG coverage is missing, or where the build is too expensive to maintain. In total, that is about 300 additional extension packages.
11. The Trade-Off
There is a real trade-off behind that. C extensions build quickly. Rust extensions do not. One Rust build can take longer than all the C builds combined.
But users still need them. A self-hosted Supabase stack, for example, needs about a dozen extensions, three of them written in Rust. So the question is not whether the work is necessary. The question is where the work should live.
12. Why Linux Native?
Container images reduce part of the matrix. I really admire that. With containers, you only build for 5 PostgreSQL majors times 2 architectures. That is 10 slots per extension, an 8x reduction.
But Linux-native packages are still important. Many users still install Postgres through the native package manager, APT or YUM. And most Postgres Docker images themselves install extensions as Debian packages from the PGDG APT repository.
So the packaging has to be done somewhere.
13. PGEXT.CLOUD
To deliver all these RPM and DEB extension packages, we have built open-source infrastructure around the problem.
It has four parts: a catalog for discovery, a repository for delivery, an optional CLI for easier access, and the build matrix behind them.
The CLI is simple. The repository is useful. But the catalog and the build matrix are where most of the engineering cost lives.
14. Extension Catalog
The catalog is the source of truth. It is not a marketing page. It is a database with structured metadata describing everything about an extension: dimensions, tags, dependencies, availability matrix, and notes on how to install, configure, build, and use it.
This sounds like boring grunt work. But boring metadata is what lets the rest of the system behave predictably. With that data, you can ask Codex to regenerate the extension galaxy in one prompt.
15. Catalog Details
The catalog is part of the delivery path. The website and the CLI tools all use it as the source of truth.
Currently, that metadata is exported as several CSV files and updated regularly. It comes in two versions: a universe version that collects generic metadata for 1,600 extensions, and a detailed version that covers 511 of them.
I would be very happy if this kind of information could one day live on postgresql.org as an official extension directory. For now, it lives on pgext.cloud and GitHub.
16. Catalog Page Views
The catalog website also gives us pageview data. It is not the same as production usage, but it tells us what users are looking at. That can be useful. It tells us which extensions deserve packaging effort first, and which categories are becoming active.
Here is the extension pageview data from the last month.
17. Repository
To deliver these extensions to users, the catalog itself is not sufficient. You also need a repository.
Technically, the repository is an APT and YUM repository with signed Linux-native packages, hosted on Cloudflare with a regional mirror.
This repository aims to enhance the PGDG YUM and APT repositories. It is fully compatible, built under the same conventions, with the package layout users already understand and use.
18. Repo Download Stats
The repository now serves roughly one million RPM and DEB downloads per month.
But these numbers have limits. They do not include the PGDG side. Cloudflare also does not offer detailed access logs outside its enterprise plan, so we are missing a lot of data.
I would really welcome it if the PGDG repository could share access logs, or at least some aggregate statistics. That would be a very useful signal for the extension ecosystem.
19. What We Can Still Infer
Even partial and biased download data is still useful. It can show which PostgreSQL major versions are active. It can show which operating system targets matter. It can show whether a package cell is used enough to justify maintenance.
But be careful. A package with few downloads may still be important. Maybe we need a combined signal: stars, pageviews, availability, build failures, and downloads. Something like a DB-Engines-style score for Postgres extensions.
20. The CLI - PIG
Once we have the catalog and repository, extension delivery is almost solved. You can use the operating system package manager, dnf or apt, to install directly from the PGDG and PGEXT repositories.
We also have a dedicated but purely optional command-line tool called PIG. It is written in Go and distributed as a single executable. The name means “piggyback on the OS package manager”. It resolves extension names to the packages for the selected PostgreSQL version and platform.
The interesting part is that it does not only install. It can also build and deliver binary packages. After setting up the build environment, run pig build pkg pg_search to build pg_search, or select another supported extension such as pg_duckdb.
This matters for supply-chain trust. Users can rebuild everything themselves if they want to.
So that is the delivery layer: catalog, repository, CLI, and the build matrix behind them. On paper, it looks clean. In practice, the matrix is where things get hard.
Part IV: Maintenance in the Wild
21. Dimension Explosion!
In the previous chapter, we talked about the matrix: 80 slots per extension.
But 5 PostgreSQL versions times 16 Linux targets is an oversimplified model. The real picture is messier. There are more factors than rows and columns.
On the operating system side: distribution family, architecture, major version, and sometimes minor version.
On the PostgreSQL side: major version, and sometimes minor version.
On the extension side: extension version, and pgrx version for Rust extensions.
When you multiply all of these together, the combination explodes very quickly.
The rest of this part is about what we learn when that explosion meets reality.
22. PG Minor ABI Break
In November 2024, we hit a concrete case. PostgreSQL 17.1 broke ABI compatibility during a minor upgrade. That broke certain extensions, including TimescaleDB.
In response, some maintainers switched to building for every PostgreSQL minor version. But that creates new problems. If you build for every single minor version, in-place upgrades become much harder.
It is better to treat this as an exceptional case. But when it happens, we have to be ready.
23. OS Minor Break
Sometimes even an operating system minor version will break your build.
For example, EL changed the OpenSSL version from 3.2 to 3.5, and some extensions break at link time.
In response, the PGDG YUM repository recently changed its packaging policy. It now builds per minor version instead of per major version. So we have separate builds for EL 10.0, 10.1, 9.6, and 9.7, instead of just EL 10 and EL 9. That is yet another sub-dimension on the matrix.
24. Rust Problems
Rust extensions are growing. They bring new people and new ideas into the ecosystem. The Rust community uses a framework called pgrx to write them, and that introduces a few new problems.
First, the build cost. Rust builds are slow and disk-hungry. One Rust extension can take longer to build than all the C extensions combined.
Second, pgrx itself has versions: 0.16, 0.17, 0.18, and so on. They are not interchangeable. I have spent a lot of time aligning Rust extensions to specific pgrx versions, but as time goes by, version drift comes back.
So Rust does not just add another language. It adds another compatibility axis.
25. Bulky Extensions
Extensions used to be small, typically a few hundred kilobytes. That is no longer always true.
Some newer extensions, such as pg_search and pg_duckdb, are tens of megabytes. Source archives and build outputs both add up quickly. Across the full matrix, this turns into real storage and bandwidth cost.
26. Naming Conflicts
The matrix is one kind of complexity. Conflicts between extensions are another.
Last year, I talked about Citus and Hydra competing for the same name, columnar. This year, we have a new example: bm25. Three extensions now expose an access method called bm25:
- pg_search from ParadeDB
- pg_textsearch from Timescale
- vchord_bm25 from TensorChord
Unlike Citus and Hydra, you can install these three together. But you cannot create them all in the same database, because the access method name collides.
This is not just a packaging issue. It is an ecosystem metadata problem. If the catalog records not just package names, but also extension objects, libraries, and access methods, authors can check for collisions before release.
27. Library Conflicts
Here is another example. Three DuckDB-based extensions wanted to use the same shared library: libduckdb.
The package manager sees files on disk. PostgreSQL sees shared libraries and control files. The user sees CREATE EXTENSION. All three layers can disagree.
The practical resolution was to mount two of the extensions as sub-extensions under pg_duckdb. It worked, but it took real effort to coordinate and persuade the authors.
The lesson is simple: names are part of compatibility, and names do conflict.
28. API Break
We also fix many extensions that lack active maintenance. The last release date for some of them was years ago, but PostgreSQL major version changes still affect them.
Usually, the original author writes version branches to handle different PostgreSQL majors. If the extension is no longer maintained, a packager has to step in.
We have talked about how this work helps the first three groups: users, authors, and vendors. Can it also be useful to PostgreSQL hackers?
I think build coverage is a useful signal. When a patch breaks N extensions, that number is information. It shows ecosystem impact. This is where delivery infrastructure starts to look like feedback infrastructure.
29. PG 19 Compatibility
Here is a concrete case. I ran the build pipeline against PostgreSQL 19 development snapshots. Around 50 extensions failed to build.
The failures cluster into a small set of categories: real API changes, old assumptions, missing version branches, dependency problems, and packages that were already fragile.
Some PostgreSQL hackers told me last year that this might be useful for patches with broad reach, such as threading work, refactors, and hook changes. If a CI pipeline can run extension builds against a patch series, the result could be useful input during patch review.
I would really like feedback from this room on whether that is worth pursuing.
The goal is not to block progress. The goal is to make ecosystem impact visible earlier.
30. Keeping It Maintainable
A practical question is maintainability. All of this work is done by one person. I run a one-person company and a one-person distribution called Pigsty. I have been doing this for about five years.
It is getting easier these days because of AI tooling. A year ago, every build spec was written by hand. After accumulating enough examples, adding new extensions has become straightforward. Last month, I added 50 new extensions in two days.
My friend Yurii Rashkovskii once described an idea called PGPM: URL in, RPM out. With Codex and Claude Code, that idea is becoming real.
AI also lowers testing cost. We can drive sanity checks from extension documentation and catch behavior regressions earlier.
AI may not be ready to commit Postgres core patches. But it is clearly qualified for this kind of work. I maintain a MinIO fork that fixes CVEs and bugs, almost entirely through Codex and Claude Code. It actually works in production.
This is the only way a 511-extension matrix stays alive with one maintainer.
31. Three Questions
To close, extensions are the collective treasure of the Postgres ecosystem. I hope this work helps users, authors, vendors, and Postgres hackers build a better Postgres.
I want to leave this room with three questions.
First, what catalog metrics would actually be useful? Pageviews, downloads, package availability, build failures, last release date, object conflicts. Which of these should be visible, and which are noise?
Second, can extension build coverage help patch review? Is it useful as an early warning signal for API, ABI, and behavior changes?
Third, should some of this metadata live closer to PostgreSQL community infrastructure? Under postgresql.org, alongside PGDG, or somewhere else?
Extensions are collective infrastructure. Delivery is part of extensibility. If we improve delivery, PostgreSQL’s superpower reaches more people.
32. Thank You
Thank you.
If you have any questions, please contact me.
Vonng [email protected]
Originally published on vonng.com.
6 - 504 Extensions: Expand the PostgreSQL Landscape
A GitHub issue turned into an extension sprint. 32 new additions say a lot about where PostgreSQL is headed.
It Started with a Chemistry Extension
Two days ago, a user opened a GitHub issue: he was using RDKit, the de facto standard library in cheminformatics, to store molecular structures, run substructure searches, and compute similarity inside PostgreSQL. He noticed that the official PGDG package was built without InChI support. After spending a while rebuilding it with the right compile flags, he got it working, but still hoped Pigsty could support it out of the box.
RDKit really is a nasty one. I tried to bring it into the Pigsty extension repo about two years ago, porting it from Debian to EL. The dependency tree was ugly: Boost, Eigen, RapidJSON, Cairo, plus optional modules like InChI and Avalon. Each one came with its own build flags and OS-specific library-version problems. I fought with it for a while, got nowhere, and shelved it.
This time was different. I had coding agents.
Using Codex or Claude Code for this kind of build-system archaeology is almost unfair. Things that used to take endless rounds of trial and error now usually take one or two iterations of prompting and then waiting. This release also fixed the missing InChI support in the PGDG package. In practice it came down to enabling one more build flag and bundling the InChI source. It worked on the first proper pass, and the user was happy.
Honestly, feedback like that is the best part of doing open source.
Strike While the Iron Is Hot
Once I was warmed up, I went after a few other long-standing problem cases.
plv8: PostgreSQL bindings for the V8 engine. It had refused to build on EL10 for a while. This time, after carrying a few patches, I finally got it building reliably.
duckdb_fdw: lets PostgreSQL read and write external DuckDB files. Previously it clashed with DuckDB’s official pg_duckdb extension because both wanted the same shared library name, so I had to hide it temporarily. This time I turned duckdb_fdw into a sub-extension of pg_duckdb, so they share the same libduckdb. The conflict is gone, and both can coexist cleanly again.
At that point I figured: if the toolchain is already hot, why not finish the rest of the worthwhile extensions in the PostgreSQL ecosystem that had been sitting on the backlog? That turned into this release: 32 new additions, 22 updates, and the Pigsty extension repo officially crossing 500, landing at 504 total extensions.
| Category | All | PGDG | PIGSTY | CONTRIB | MISS | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|---|---|---|---|---|
| Total | 504 | 155 | 332 | 71 | 0 | 481 | 488 | 479 | 473 | 457 |
| EL | 499 | 150 | 332 | 71 | 5 | 472 | 482 | 474 | 468 | 452 |
| Debian | 489 | 107 | 311 | 71 | 15 | 466 | 474 | 464 | 458 | 442 |
The catalog combines PostgreSQL’s bundled extensions with packages from PGDG and Pigsty. The repository counts overlap: some extensions are available from more than one source.
To put that in perspective: most managed PostgreSQL cloud RDS expose a few dozen extensions at best. Take Supabase, for example. It looks like a long list, but after you subtract the 35 contrib extensions that come with PostgreSQL, you are left with fewer than 30 third-party extensions.
The New Extensions
This batch is heavy. Broadly, four groups:
Data-domain extensions: make chemical molecules, RDF triples, BSON, Protobuf, recurring schedules, and other complex objects first-class database citizens.
Query extensions: sparse linear algebra and graph algorithms, Datalog-style graph queries, full-text search, hybrid ranking fusion, recursive SQL template engines.
Production engineering extensions: deep observability, exported query telemetry, CDC to MQTT, COPY interception, DDL propagation for logical replication, lightweight distributed locks, soft-alert data quality management.
Developer-experience extensions: session variables, pseudo-autonomous transaction logging, natural-language time parsing.
Together they point to a broader trend: the extension layer is pushing PostgreSQL into the space between an application platform and a data platform. Things that used to require separate services increasingly fit inside a single SQL transaction boundary.
A Tour of the New Additions
This release adds 32 new extensions. The summaries below were compiled with help from Claude, Codex, and Gemini to give readers a quick way to understand what each one does, how it works, and where it fits.
1. rdkit: Cheminformatics Inside PostgreSQL
RDKit is the de facto standard open-source cheminformatics library, started by Greg Landrum (originally at Novartis, now T5 Informatics). Its PostgreSQL cartridge brings molecular storage, substructure search, and similarity computation into a relational database — millions of compounds queryable with plain SQL.
The cartridge adds mol (molecules) and qmol (SMARTS query patterns), plus bfp/sfp fingerprint types. Operators: @> for substructure matching, % for Tanimoto similarity, <%> as a distance operator — all GiST-indexable via fingerprint pre-filtering. Key functions: mol_from_smiles(), morganbv_fp(), tanimoto_sml(). GUCs like rdkit.tanimoto_threshold control match sensitivity.
Using the ChEMBL dataset with 1.87 million compounds as an example:
Use cases center on drug discovery: lead scaffold search across million-scale libraries, SAR analysis via similarity, compound registration with fingerprint dedup, and catalog search over datasets like eMolecules (6M+ compounds).
Settle your index strategy and query templates early — filters that are correct but bypass indexes will be slow. On 1.87M compounds, substructure queries range from ~88 ms to ~1.9 s; with tuning, the cartridge handles 6M+ compounds. BSD licensed. Docker images (mcs07/postgres-rdkit) and conda packages available.
2. provsql: Semiring Provenance for Query Results
ProvSQL, from Pierre Senellart (ENS Paris / INRIA Valda, VLDB 2018), adds (m-)semiring provenance and uncertainty management to PostgreSQL. It tracks which base tuples each query result was derived from, and lets you evaluate that provenance under different algebraic structures: booleans, security levels, counts, or probabilities.
It hooks into query execution and adds a hidden provsql UUID column to each table, pointing into a provenance circuit. Supported SQL is broad: SELECT-FROM-WHERE, JOIN, GROUP BY, DISTINCT, UNION/EXCEPT, aggregates, HAVING, and on PG 14+ also INSERT/DELETE/UPDATE. Core functions: add_provenance(), provenance_evaluate(), formula(), probability_evaluate(). Probability evaluation ranges from naive to Monte Carlo to d-DNNF compilation via external solvers (d4, c2d).
Four typical scenarios: security-label propagation (results inherit the highest source classification), probabilistic databases (base tuples carry confidence scores), data lineage and audit (trace each output row back to sources, optionally export as PROV-XML), and credibility scoring (e.g. weighting witness statements in investigative workflows).
The key property is composability: provenance is not a dead log string but a live object you can keep computing on. Worth enabling on critical paths — core reports, feature pipelines, compliance calculations — not as a blanket switch for the whole database. C/C++ with Boost; provenance circuits live in shared memory. PG 10–18. MIT.
3. onesparse: Billion-Edge Graph Algorithms in SQL
OneSparse wraps SuiteSparse:GraphBLAS to bring high-performance sparse linear algebra into PostgreSQL. Developer Michel Pelletier sits on the GraphBLAS C API committee; advisor Timothy A. Davis is the SuiteSparse author. The premise: represent graphs as sparse matrices and run BFS, PageRank, triangle centrality, and friends via matrix operations — all from SQL.
Types: matrix, vector, scalar, semiring, monoid. Operator @ for matrix multiplication under plus_times semiring. Ships LAGraph algorithms: BFS (level and parent modes), PageRank, triangle centrality, degree centrality, SSSP. Wraps GraphBLAS opaque handles in PostgreSQL’s Expanded Object Header; small graphs (<1 GB) in TOAST, larger ones as Large Objects or files. Built-in JIT with NVIDIA CUDA GPU acceleration.
The published GAP benchmarks include graphs with up to 4.3 billion edges and report BFS throughput above 7 billion edges per second on some inputs. Potential uses include transaction-graph fraud detection, social-network analysis, and Graph RAG. Test data loading, serialization, and query planning with a small representative graph before scaling up.
Requires PG 18 Beta or newer; still alpha. Apache 2.0.
4. pg_datasentinel: Deep Observability for PostgreSQL in the Container Era
pg_datasentinel (Christophe Reveillere / Datasentinel, 1.0 released April 10 2026) fills four gaps in PostgreSQL’s native monitoring, especially for containerized deployments:
- Extended activity monitoring — augments
pg_stat_activitywith per-backend memory usage, live temp-file bytes, and on PG 18+ the current plan ID. - Container resource visibility — CPU quotas, memory limits/usage, and CPU pressure for Docker / Kubernetes / OpenShift / any cgroup environment.
- Transaction wraparound forecasting — tracks XID and MXID burn rate, exposes live ETAs to aggressive vacuum and wraparound limits.
- Log capture views — parses vacuum, analyze, temp-file, and checkpoint events into a shared-memory ring buffer queryable from SQL.
For PostgreSQL on Kubernetes, this gives container-level visibility without a separate monitoring agent. The XID wraparound warning is the standout — wraparound can force-shutdown a database, and having a burn-rate ETA turns firefighting into forecasting. 3-Clause BSD. PG 15+.
5. datasketches: Approximate Analytics at Hundred-Million-Row Scale
Apache DataSketches (Apache Foundation, originally Yahoo/Verizon Media) brings approximate query data structures into SQL. When exact COUNT(DISTINCT), quantiles, or heavy-hitter analysis gets too expensive on large datasets, sketches trade a few percent of accuracy for orders of magnitude in speed and memory.
Seven sketch types: cpc_sketch (compressed probabilistic counting), hll_sketch (HyperLogLog), theta_sketch (distinct counting with set algebra), aod_sketch (tuples), kll_float_sketch/kll_double_sketch (quantiles), req_float_sketch (tail quantiles), frequent_strings_sketch (frequent items). Standard API: *_sketch_build(), *_sketch_union(), *_sketch_get_estimate().
What makes sketches powerful is mergeability: pre-aggregate by dimension slice, union at query time for arbitrary distinct counts. Sublinear memory. Binary format compatible across Java, C++, Python, Rust, and Go.
Use cases: real-time UV counting without storing user IDs, latency distribution (p50/p95/p99 over billions of events), audience overlap via Theta Sketch intersections (“saw ad A and visited site B”). On 100M rows, CPC distinct counting takes ~20 s vs ~2 min for exact COUNT(DISTINCT), with single-digit percent relative error.
6. pghydro: Drainage-Network Analysis from Brazil’s National Water Agency
PgHydro, by Alexandre de Amorim Teixeira (Brazil’s National Water and Sanitation Agency, ANA), is ANA’s official tool for hydrology workflows nationwide. Built on PostGIS, presented at FOSS4G 2022.
It covers the full hydrological network workflow: GIS data import, topological consistency checks, flow direction, Otto Pfafstetter basin coding, upstream/downstream analysis, catchment area, and Strahler stream order. Five sub-extensions: pghydro (core), pgh_raster (DEM), pgh_hgm (hydrogeomorphology), pgh_consistency (validation), pgh_output (export).
Fits national-scale hydrology databases, basin planning, upstream/downstream pollution analysis, and drainage-network validation. Think of it less as “an extension with GIS functions” and more as a domain-specific ETL pipeline living inside the database — raw terrain and river data in PostGIS, processing automated in SQL, recomputation after source updates far more reliable than ad hoc scripts. QGIS plugin PgHydroTools available for visual interaction. Pure PL/pgSQL. GPLv2.
7. pg_stat_ch: PostgreSQL Query Telemetry, Exported to ClickHouse
pg_stat_ch was announced by ClickHouse on February 13, 2026, with Kaushik Iska as the author. Where pg_stat_statements aggregates inside PostgreSQL, pg_stat_ch streams every raw query execution event (45 fields, fixed 4.6 KB each) out to ClickHouse for p50/p95/p99 analysis, top-query ranking, and error analytics.
Pipeline: PG hooks → shared-memory ring buffer → background worker → ClickHouse via native binary protocol with LZ4 compression (statically linked clickhouse-cpp). The 45 fields cover timing, row counts, buffers, WAL, CPU, JIT (PG 15+), parallel workers (PG 18+), client context, and SQLSTATE errors. On queue overflow it drops events and bumps a counter rather than applying backpressure — StatsD philosophy.
On the ClickHouse side it ships four materialized views: events_recent_1h for a rolling one-hour copy, query_stats_5m for five-minute buckets with TDigest quantiles, db_app_user_1m for database/app/user load attribution, and errors_recent for a rolling seven-day error window.
Performance: ~5 μs p99 overhead per query. pgbench at 36.6K TPS / 32 clients captured 7.7M events in 30 s with zero drops and <1% TPS impact. Lock contention minimized in three layers: atomic overflow checks → non-blocking LWLock → per-backend local buffers flushed per transaction (~5x fewer lock acquisitions). A clean division of labor: PostgreSQL for transactions, ClickHouse for telemetry. Far more robust than reconstructing the same picture from log files. PG 16–18. Apache 2.0.
8. pg_rrf: Rank Fusion for Hybrid Search in One Function
pg_rrf (yuiseki, January 2026, Rust/pgrx) packages Reciprocal Rank Fusion (RRF) as a native PostgreSQL function. In hybrid retrieval, different retrievers produce scores on incomparable scales. RRF sidesteps that by using rank positions only:
score(d) = Σ 1 / (k + rank_i(d))
The default k is 60, following Cormack et al., SIGIR 2009.
The extension exposes four functions: rrf(rank_a, rank_b, k) for two-way fusion, rrf3() for three-way fusion, rrfn(ranks[], k) for N-way fusion, and the most useful one in practice, rrf_fuse(ids_a bigint[], ids_b bigint[], k), which takes two ranked ID arrays and returns a fused (id, score) table. It is NULL-safe: an ID that appears in only one list is scored from that list alone.
Replaces 20+ lines of FULL OUTER JOIN / COALESCE / hand-rolled score math with one function call. Good fit for RAG hybrid retrieval, product search, and multi-signal document ranking. Keeping fusion in the database helps when the fused result still needs to join business tables. v0.0.3. MIT.
9. pg_kazsearch: Kazakh Full-Text Search, from Zero to One
pg_kazsearch is the first PostgreSQL full-text-search extension for Kazakh. Kazakh is highly agglutinative — a single word like мектептерімізде stacks plurality, possession, and locative suffixes atop the root мектеп. Existing PG and Elasticsearch analyzers cannot handle this.
Written in Rust/pgrx. Provides kazakh_cfg text-search config and pg_kazsearch_dict. Stemming uses BFS suffix stripping with vowel-harmony validation and a 21,863-root POS-tagged lexicon (Apertium-kaz) to prevent over-stemming. Tunable via ALTER TEXT SEARCH DICTIONARY.
Benchmarks on 2,999 articles: 0.5 ms query latency (2.8x faster than pg_trgm), +25% nDCG@10, +23% Recall@10. Useful for Kazakh news/government-document search, e-commerce, and multilingual systems that need proper search for low-resource languages instead of crude trigram fallback.
10. pg_liquid: Datalog-Style Graph Queries
pg_liquid (Michael Golfi) brings Liquid/Datalog-style declarative graph queries into PostgreSQL. liquid.query(...) lets you declare facts, define rules, and run a terminal query in one call — no separate graph database needed. Rules are scoped to a single invocation. Supports fact assertions, recursive transitive closure, compound queries, and row normalizers.
Also supports ontology predicates (DefPred) and typed compounds (OntologyClaim@(...)), where compounds carry provenance or confidence while rules handle subclass closure. Good fit for knowledge-graph queries, hierarchy traversal (org charts, taxonomy trees), and rule-based business logic. Pure PL/pgSQL, no external dependencies. Early-stage.
11. logical_ddl: Logical Replication, but for DDL Too
PostgreSQL logical replication handles DML only — no DDL. Schema drift breaks replication. logical_ddl (Samed Yildirim) fills that gap with event triggers that intercept DDL, deparse it into a replicated table, and generate equivalent SQL on the subscriber side.
Supported: ALTER TABLE RENAME TO, RENAME COLUMN, ADD COLUMN, ALTER COLUMN TYPE, DROP COLUMN. Built-in types, arrays, composites, domains, and enums work; CREATE TYPE itself is out of scope. logical_ddl.publish_tablelist controls capture per table and per command type.
Useful for automated DDL sync in logical-replication setups, zero-downtime migrations, and multi-datacenter topologies. DDL propagation becomes an auditable data flow rather than a manual side process. MIT. PGXN available. Constraints, indexes, and defaults not yet supported.
12. rdf_fdw: Query the Semantic Web with SQL
rdf_fdw (Jim Jones) is a foreign data wrapper that bridges SQL and the semantic web by querying RDF triple stores via SPARQL endpoints. Adds an rdfnode type for IRIs, language tags, and typed literals. Supports SQL-to-SPARQL pushdown for WHERE/LIMIT/ORDER BY/DISTINCT, plus INSERT/UPDATE/DELETE via SPARQL UPDATE endpoints.
rdf_fdw_clone_table() can batch-clone foreign data into local tables. Watch memory: fetched data is loaded before conversion, so large result sets need effective pushdown. Good for linked-data integration (DBpedia, Wikidata) and using SQL/BI tooling on SPARQL endpoints. MIT. PG 9.5–18.
13. pgbson: A More Exact Binary Document Type than JSONB
pgbson (buzzm, a.k.a. postgresbson) adds a native BSON type to PostgreSQL. BSON provides first-class datetime, decimal128, int32/int64, binary, etc. — types that matter for exact round-tripping across distributed systems. Binary-perfect BSON in, BSON out.
Two access styles. Fast path: dotpath functions like bson_get_string(bson, 'd.recordId'), bson_get_datetime(), bson_get_decimal128() — walk the binary directly, allocate only at the leaf. Slow path: -> / ->> operators that construct intermediate subdocuments at each level. Expression indexes on the function API can yield 10,000x speedups over sequential scan. Also accepts EJSON input.
Use cases: cross-language event pipelines needing exact type preservation, financial data (decimal128), and digital-signature workflows relying on deterministic binary representation. MIT. PG 14–18.
14. pg_when: Describe Time in Natural Language
pg_when (frectonz) parses natural-language time expressions into timestamptz or Unix epochs. when_is(text) returns a normalized timestamp; grammar: date + at + time + in + timezone, defaulting to UTC.
Also: seconds_at(), millis_at(), micros_at(), nanos_at() for Unix epochs at varying precision. A parser, not a scheduler. Fits operator-facing tools that accept human time input, backfill scripts where natural language beats date math, and timezone normalization. MIT.
15. pgmqtt: Push Database Changes Straight to MQTT
pgmqtt (RayElg, Rust) turns PostgreSQL row changes into MQTT messages and maps inbound MQTT messages back into tables. Not a general MQTT client — it wires database CDC to a message broker at the database layer, with SQL-defined topic mappings and payload templates.
Natural fit for IoT: push database state changes to edge devices without middleware, or ingest sensor readings from MQTT directly into tables. Also works for lightweight event-driven systems that want less glue code. Elastic License 2.0.
16. pg_query_rewrite: Transparent SQL Substitution
pg_query_rewrite (Pierre Forstmann) uses the post_parse_analyze_hook to rewrite parsed SQL statements. Rules live in shared memory, matched by exact string equality — whitespace and case both matter.
Statements must match exactly, including whitespace, case, and semicolons. Parameterized statements are unsupported, and each statement is limited to about 32 KB. Reload rules after a restart. This is useful for redirecting fixed SQL during migrations and for query A/B tests. Default max 10 rules. PG 9.5–18.
17. pgclone: Clone Database Objects with One Function Call
pgclone (valehdba, v2.0.0 on PGXN) lets you clone tables, schemas, databases, functions, roles, and privileges from a source instance via SQL functions — no pg_dump/pg_restore or shell scripts needed.
Uses the COPY protocol for fast data movement. Supports async operation with progress tracking, row/column filters, DDL coverage (indexes, constraints, triggers, views, materialized views, sequences), masking, and automatic sensitive-column discovery.
Good for fast dev/test provisioning, sanitized prod-to-staging clones, and cross-database migration — the whole workflow stays inside the database.
18. pgproto: Native Protobuf Support
pgproto (Apaezmx) adds native Protocol Buffers (proto3) storage, query, mutation, and indexing. Register a FileDescriptorSet in pb_schemas, then protobuf columns expose nested fields via path arrays. Operators: -> field navigation, #> nested path, || message merge. Functions: pb_set(), pb_insert(), pb_delete(), pb_to_json().
100K-row benchmark: 16 MB storage (vs 46 MB JSONB, 25 MB relational), 5.9 ms full-document retrieval (vs 33.1 ms relational with multi-table joins). If you want to keep Protobuf for RPC/messaging while making the data indexable inside the database, this delivers. Fits IoT data, microservice event stores, gRPC data layers. PostgreSQL License.
19. pg_fsql: A Recursive SQL Template Engine Driven by JSONB
pg_fsql (yurc) is a recursive SQL template engine driven by JSONB. Templates are organized as dot-path trees; child templates emit fragments or JSON injected into parents. Placeholder syntax: {d[key]} with escaping modes (!r, !j, !i). Command types: exec, ref, if, exec_tpl, map, NULL. Optional SPI plan caching per template. APIs: fsql.run (execute), fsql.render (dry run), fsql.tree, fsql.explain. No superuser needed.
Not “functional SQL” — more a hierarchical template system for generating SQL from JSON request bodies. Reduces conditional branching in the application layer. Fits dynamic reports, ETL orchestration, multi-tenant query generation, and centralized SQL templates stored in tables.
20. pg_dispatch: Async SQL Dispatch on Top of pg_cron
pg_dispatch (Snehil Shah) is an async SQL dispatcher built on pg_cron, TLE-compatible alternative to pg_later. pgdispatch.fire(command) for immediate async execution, pgdispatch.snooze(command, delay) for delayed. The point: get heavy work out of the foreground transaction — if an AFTER INSERT trigger needs something expensive, push it to the background.
Pure PL/pgSQL, runs in sandboxed environments (Supabase, AWS RDS). Requires pg_cron >= 1.5. Good for async side effects in triggers/functions — notifications, background rollups, audit writes that should not block the main transaction.
21. block_copy_command: Security Hardening by Intercepting COPY
block_copy_command (rustwizard, Rust/pgrx) intercepts COPY cluster-wide via ProcessUtility hook. In PCI-DSS or HIPAA environments: block exfiltration via COPY TO, block unauthorized imports via COPY FROM.
Role-based blocklists, directional control (block_to / block_from), COPY ... TO PROGRAM blocked for everyone by default. Blocklist can include superusers. Built-in audit logging.
Useful in multi-tenant or hosted environments, enterprise compliance setups needing centralized audit, and ETL environments where import/export privileges must be tightly separated. The author also maintains a broader command-firewall extension, pg_command_fw.
22. pg_isok: Soft Alerts for Data Quality
pg_isok (Karl O. Pinc, in production for 10+ years) is soft-trigger data integrity management. You write SQL queries that find suspicious data patterns; Isok records, classifies, and defers findings, surfacing only newly introduced problems or changes to previously accepted data — no re-reviewing the same historical anomalies.
Unlike hard constraints, Isok lets questionable data exist while keeping it under review. Workflow: isok_queries and isok_results tables, run_isok_queries to execute checks; results accepted or deferred row by row. Fits messy-data cleanup and business rules too fuzzy for hard constraints that still need human judgment.
23. external_file: Oracle BFILE Semantics for PostgreSQL
external_file (Gilles Darold, HexaCluster Corp) provides Oracle BFILE equivalence. EFILE type references server-side files via directory alias + filename; readEfile(), writeEfile(), copyEfile() for I/O. Built on lo_* large-object machinery with directory-alias and privilege tables controlling access.
Built for Ora2Pg migrations, but also useful for legacy systems with files outside the database and metadata inside, or database-driven batch import/export of external large objects.
24. byteamagic: Detect File Types in bytea
byteamagic (Nico Mandery) wraps libmagic (the library behind Unix file). Two functions: byteamagic_mime(bytea) returns MIME type, byteamagic_text(bytea) returns human-readable description.
If you store BLOBs in tables, this identifies what they actually are from SQL. Good for upload governance, real content-type detection, and historical BLOB cleanup.
25. pg_text_semver: Native Semantic Versioning
pg_text_semver (Rowan Rodrik van der Molen) implements SemVer 2.0.0 as a text domain. Unlike the C-based semver extension, version components have no 32-bit integer limit.
Pure SQL. Supports min/max aggregation and PGXN version-range validation. Useful for extension/package version management, dependency checks, and version analytics.
26. parray_gin: Substring Matching Indexes for text[]
parray_gin (Eugene Seliverstov) adds partial-match operators for text[] columns backed by GIN indexes. Native GIN array operators only do exact element matching; parray_gin adds @@> for matching array elements against LIKE patterns, using pg_trgm trigram decomposition with recheck for false positives.
Useful for tag autocomplete, fuzzy tag search, or any case where array partial matching should hit an index. PG 9.1–18.
27. pg_slug_gen: Cryptographically Secure Timestamp Slugs
pg_slug_gen (Fernando Olle) generates short unique identifiers combining timestamp info with cryptographically secure randomness (pg_strong_random()). Length sets precision: 10 chars (seconds), 13 (milliseconds), 16 (microseconds, default), 19 (nanoseconds).
Not a “slugify the title” URL helper — a short, hard-to-guess public identifier. Good for invite codes, short links, and public resource IDs where exposing auto-increment sequences is undesirable. Much less predictable than base62(sequence).
28. pglock: Lightweight Distributed Locks Inside PostgreSQL
pglock (fraruiz) implements lightweight distributed locks on top of PostgreSQL. Lock table + functions: pglock.lock, pglock.unlock, pglock.ttl, pglock.set_serializable. TTL expiration (default 5 min), optionally reaped by pg_cron. Recommended isolation: SERIALIZABLE.
No Redis or ZooKeeper needed. Fits multi-instance job competition, leader election, idempotent consumers, duplicate-work prevention — lock behavior and business writes stay in the same database. Pure SQL.
29. pg_regresql: Portable Planner Statistics for EXPLAIN Costing
pg_regresql (Radim Marek / boringSQL) solves a specific plan-regression-testing problem: the planner reads real file sizes from disk and scales row counts accordingly, so injected production-sized statistics in pg_class get overridden by your tiny CI dataset’s physical size.
The extension hooks get_relation_info_hook to force the planner to trust pg_class values (relpages, reltuples, relallvisible) instead of physical file sizes. This makes cost estimates portable — compare EXPLAIN output across schema versions, reproduce production plans on a laptop, keep plan baselines stable in CI.
Only affects planner costing, not execution or EXPLAIN ANALYZE actuals. For test/CI only, not production. BSD 2-Clause.
30. pgcalendar: Infinite Projection for Recurring Schedules
pgcalendar (h4kbas) implements a full recurring-event calendar. Events are logical entities; schedules define recurrence (daily/weekly/monthly/yearly); projections generate concrete occurrences; exceptions cancel or reschedule individual instances.
Infinite projection, schedule transitions, and exception handling show up everywhere — rostering, meetings, billing — and become a mess when every application reimplements them. Putting this in the database centralizes permissions, audit, and consistency.
31. pg_variables: Session Variables Faster than Temp Tables
pg_variables (Postgres Professional) adds session-level variables — scalars, arrays, and records — grouped into named packages. By default variables do not roll back; with is_transactional = true they honor ROLLBACK and SAVEPOINTs.
A high-performance temp-table alternative that avoids catalog bloat. Useful for intermediate state in stored procedures/batch jobs, connection-level caching, and as infrastructure for other extensions (pgelog uses it to cache dblink connections).
32. pgelog: Logs That Survive Rollback
pgelog (anfiau) uses dblink to simulate pseudo-autonomous transactions — log records survive even when the calling transaction rolls back. Solves a classic PL/pgSQL problem: logs written inside an EXCEPTION block disappear when the outer transaction aborts. Uses pg_variables to cache dblink connections per session.
On critical paths, losing the diagnostic trail because the business transaction rolled back is exactly the wrong outcome. Also makes staged batch/migration scripts easier to introspect than RAISE NOTICE. Depends on dblink and pg_variables; each session may open an extra connection, so mind max_connections.
Conclusion
These 32 additions trace a few clear lines.
More “professional objects” inside the database. BSON, Protobuf, RDF, recurring schedules, molecules, graphs — the database becomes a queryable store for complex domain objects, with permissions, audit, backup, and transactions already built in. Less data movement, fewer sidecar services.
Query capabilities as composable APIs. RRF fusion, recursive SQL templates, query rewriting, sparse algebra, sketch approximations — more logic expressed in fewer, more stable SQL building blocks, auditable and optimizable.
The extension layer absorbing platform and ops work. Telemetry export (pg_stat_ch), container visibility (pg_datasentinel), security hooks (block_copy_command), soft-alert governance (pg_isok) — capabilities that used to live outside the database are being pulled in.
Deeper vertical penetration. Cheminformatics (rdkit), hydrology (pghydro), Kazakh NLP (pg_kazsearch) — PostgreSQL keeps becoming the computational substrate for more specialized fields.
Cathedrals (Apache Foundation projects) and bazaars (weekend builds) side by side, building the most advanced open-source database ecosystem in the world.
Originally published on vonng.com.
7 - The PostgreSQL Extension Encyclopedia: Bilingual and Ready to Use
Extensions are the soul of PostgreSQL. Without them, PostgreSQL is just a very good relational database. With them, it becomes a platform that can swallow entire categories of database workloads.
The problem is that the extension ecosystem has long been awkward to use. People struggle to find extensions, understand them, and install them. You search GitHub for README files, check PGXN for packages, and then wrestle with OS and PG version compatibility by hand.
So I built something different: an encyclopedia for 464 PostgreSQL extensions, each one with a full profile, plus a real binary repository behind it.
Not just a list
There is no shortage of extension lists on the internet. What is usually missing is operational detail.
On each extension page, you can directly see:
- Basic metadata: version, category, language, license, repository, source download.
- Extension properties: preload requirement, DDL presence, trust, relocatability, target schema.
- Version and packaging data: supported PG majors, RPM/DEB names.
- Platform matrix: which packages exist for which OS and architecture combinations.
- Install commands: ready-to-copy commands for
pig,dnf, andapt. - Relationships: dependencies, conflicts, and related extensions.
We also aggregated 460+ extension docs so people can browse a large portion of the PG extension world in one place.
464 extensions, 16 categories
The catalog is split into 16 major categories. If you have heard that PostgreSQL can behave like a time-series database, vector database, graph database, document store, or even emulate Oracle and SQL Server semantics, this is where you can see which extensions actually make those claims real.
Multiple ways to browse
You can explore the catalog from multiple angles:
By repository origin
Extensions are grouped into PGDG, PIGSTY, and CONTRIB.
By implementation language
You can see how much of the ecosystem is written in C, C++, Rust, Java, Python, SQL, or plain data files.
By license
MIT, Apache 2.0, PostgreSQL, BSD, GPL, AGPL, Timescale License: all of them matter in real-world adoption.
By extension properties
Need shared_preload_libraries? Contains no SQL DDL? Conflicts with something else? Packages multiple extensions together? The directory makes those traits visible.
By platform
At the OS and CPU level, you can see exactly which extensions are available and which are not.
The full stack: directory + repo + package manager
The catalog only makes sense because it sits on top of real infrastructure:
- Directory: what exists, what it does, whether it is available.
- Binary repository: prebuilt RPM/DEB packages distributed through CDN.
pigpackage manager: one command to install across different OS and PG versions.
Together, they turn discovery, evaluation, installation, and use into a single workflow.
A few numbers
Why build this?
At a glance, this looks like a documentation site. In practice, it is infrastructure for the PostgreSQL extension ecosystem.
Too many good extensions die in obscurity because the path from “I heard this exists” to “I installed it successfully” is still too painful. That friction pushes people toward worse alternatives.
My goal is simple: come here, see what exists, pick what you want, copy one command, and use it.
How to use it
If you already know your way around PostgreSQL and just want more packages beyond PGDG, add the Pigsty APT/DNF repository:
If you want the full experience, use the Pigsty PostgreSQL distribution:
Fully open source
The website and the metadata itself are open source. If you want a copy or want to reuse the data, the source lives in pgsty/pgext.
Bonus
The original post also included a related conference poster, so I kept it here as well.
The catalog in numbers
Extensions are the soul of PostgreSQL, and this directory is an index to that soul.
Four hundred and sixty-four extensions. Sixteen categories. Fourteen operating-system and CPU-architecture combinations. Five PG major versions. Bilingual docs, metadata, package links, and install commands in one place.
Originally published in Chinese on vonng.com.
8 - 464 Extensions, Ready Out of the Box: The New PostgreSQL Extension Catalog
Today I put Claude Code to work on another genuinely useful project: a completely new PostgreSQL extension catalog. You can find it at pigsty.io/ext.
This is already the catalog’s fifth incarnation. After a long detour, it has returned to the Hugo + Docsy stack used by the first version and has been folded back into the main Pigsty website. That journey is a story in its own right, which I will save for later. First, let us look at what this version actually does.
Not Just Packages, but Documentation Too
The old extension catalog answered a few basic questions: What is this extension called? Where is its metadata? Where can I download the binary packages? How do I install it with one command? Once it was installed, however, learning how to use it was your problem. Go find the documentation yourself.
This version is different. With AI’s help, we have begun systematically collecting and translating the documentation for all 464 extensions. The goal is to put the essential usage information for every extension in one place.
There are two broad cases:
For heavyweight extensions with enormous documentation sets, we will build dedicated translation sites. Citus, TimescaleDB, and PostGIS are good examples: each has enough documentation to fill a book and deserves its own treatment.
For most lightweight extensions, things are much simpler: their entire documentation set is often a single README. Take pgvector, one of the hottest vector extensions in the PostgreSQL ecosystem—its documentation fits on one page.
The same is true of pg_repack, an indispensable operations tool for removing table bloat online: its documentation is also a single Markdown page.
Our job is to collect all those READMEs and embed them in each extension’s detail page. Instead of bouncing between sites and digging through GitHub, you can consult the core documentation for every extension in one central place. For exceptionally large extensions, we will aggregate their information and indexes so that the catalog still provides an authoritative, dependable starting point.
The Chinese Pigsty site has already translated the documentation for PgBouncer, pgBackRest, and Patroni. We will gradually work through and maintain the rest—including PostgreSQL itself.
Translating the PostgreSQL Ecosystem’s Three Core Components in a Day
That is part of the larger vision: to become a dependable source of essential information for the PostgreSQL ecosystem.
To be honest, whenever I finish my “real work” with AI tokens left to burn, I spend them filling gaps like these. It is a useful backstop—and a little public service while I am at it.
Friendly to Agents and Humans Alike
Now let us look at how the catalog is designed.
Although the technology stack has come full circle to Hugo + Docsy, Claude Code makes it possible to build an excellent experience on a purely static site. I followed one core design principle: make it friendly to both AI agents and human readers.
Being agent-friendly means that the site’s source is public, written in Markdown, and subject to one hard rule: keep the noise down. Markdown should not be buried under raw HTML, shortcodes, or formatting clutter, all of which make the content harder for agents to parse and read.
Being human-friendly means organizing information into clear, attractive visual forms so that readers can spot issues quickly and focus on what matters.
Here is one concrete example. In this version, we tried something genuinely useful: combining every extension into one large matrix. For each combination of PostgreSQL version and operating system, a cell tells you how many packages are available and which repository they come from. Click it, and you can download them directly.
Yet I did not build this with a maze of complex HTML. The source remains standard Markdown, wrapped only in a shortcode that performs the necessary transformation during the Hugo build. Custom CSS then turns the result into a polished presentation.
We also added a series of categorized indexes that expose extension metadata from different angles, making extensions much easier to find. Site search is considerably better than it was in earlier versions as well.
This time, we also included extensions that exist only in particular PostgreSQL forks:
Five Versions, Back Where We Started
There is one last topic—less technical, but more personal: choosing a documentation framework.
The extension catalog has gone through five versions:
- Hugo + Docsy—the original version, integrated into the main Pigsty site
- Docsify
- Next.js
- Hugo + Hextra—the standalone pgext.cloud site
- Hugo + Docsy—the current version, back on the main Pigsty site
The standalone pgext.cloud site lacked a Chinese ICP filing and was hosted on Cloudflare. Some users in mainland China reported unreliable access and suspected that it was blocked. After weighing the options, I decided it was better to use a registered domain and keep things straightforward.
The first version: Hugo + Docsy, just like this one.
The second version: Docsify.
A Piglet Riding an Elephant: PIG, the Package Manager for PostgreSQL and Its Extensions
The third version: Next.js + Fumadocs.
A Database Veteran Ventures into the Modern Frontend Jungle (Chinese original)
Eventually, I got tired of all the hassles that come with dynamic sites and returned to a static one.
The fourth version: Hugo + Hextra.
PG Extension Cloud: Unlock the Complete PostgreSQL Experience, Free and Without a VPN
Hextra is another lightweight theme in the same vein as Fumadocs. I like it a great deal. It is excellent for small projects, such as book translations, but starts to show its limits on a large documentation site. I still gladly use it for my books, tutorials, and smaller projects.
The fifth version: Hugo + Docsy.
The conclusion is simple:
If you are building a static documentation site, just use Hugo. Docsy is a Google-backed theme used by the Kubernetes and etcd documentation sites. Its fundamentals are solid, its search works well, its structure is clear, and it remains actively maintained. For a lightweight project, use Hextra; for a heavyweight one, use Docsy. If you need a content-rich dynamic site, Next.js is worth considering, but it can indeed be rather heavy.
I have used Hugo for nearly a decade, and it has never let me down. After trying so many new things, I discovered that the framework I chose six or seven years earlier was still the best fit. That seems to prove an old lesson: solid, boring technology is often the best technology. A website’s value does not come from how flashy it looks, but from whether the information inside it is worth reading. Content is still king.
Would the time spent on all this experimentation have been better used making video tutorials or writing hands-on case studies? Perhaps. But after making the full circuit, I now understand the available options, their trade-offs, and their limits—and I have sharpened my own web-design skills and taste along the way. That is valuable in itself.
Who can say for sure? Tinkering is half the fun.
Originally published in Chinese on vonng.com.
9 - Forging a China-Rooted, Global PostgreSQL Distro
Hi, I’m Feng Ruohang, author of Pigsty and an independent open-source contributor. Let’s talk about how to build a PostgreSQL distribution that is rooted in China and useful to the whole world.
The question isn’t whether PG will win—it already has. The question is: What role do we play in that victory? Spectator or protagonist? Follower or leader?
Why now
PostgreSQL is the default database
Stack Overflow’s 2025 survey shows 58.2% of professional developers use PG—18.6 points ahead of MySQL, and the gap is widening. New SaaS, AI startups, even OpenAI default to PG. DB-Engines rankings and JetBrains surveys tell the same story.
Capital agrees: in 2025 Databricks bought Neon (~$1 B) and Snowflake bought Crunchy Data ($250 M). AWS Aurora DSQL, Azure HorizonDB, GCP AlloyDB—all PG. Technology won, money followed.
China is missing from the PG narrative
Despite hundreds of domestic “PG-derived” products, our presence in the global ecosystem is faint. Until recently there wasn’t a single Chinese committer on the PG core list. The most visible Chinese-led PG project by GitHub stars is… Pigsty, a one-man project. That’s both flattering and a little sad.
At PG conferences I’ve met only a handful of Chinese developers. We’re spectators at our own victory parade.
What must change
The kernel wars are over; the fight shifts to distributions. Whoever controls the distro controls the experience—like Ubuntu did for Linux. We need a PG “Ubuntu” built with China’s strengths but serving global developers, the way DeepSeek did in AI.
Pigsty as a case study
Pigsty started at Tantan (China’s #2 dating app). We were dealing with 2.5 M global QPS, PL/pgSQL-heavy business logic, hundreds of physical clusters. Off-the-shelf tooling couldn’t cope, so we built our own HA, backups, monitoring, IaC. China’s scale was the forge. If it survives Tantan, it’s overkill everywhere else.
But “rooted in China” isn’t enough; “facing the world” means becoming part of the global supply chain. That requires obsessing over developer experience, not just DBA comfort.
In 2023 Pigsty already did HA + backups + observability + bare-metal delivery. Yet something was missing—features. PG’s true power is extensions. MySQL spends years grafting on vectors; PG’s community ships pgvector and kneecaps an entire market in months.
So I built an extension repository. I waited for others to do it, nobody did, so I compiled them myself: first a dozen, then dozens, then hundreds. Today Pigsty provides 437 extensions across EL9/EL8/Debian/Ubuntu, more than the official PGDG repos. That makes Pigsty part of the upstream supply chain: when developers apt install an extension, they’re using binaries built in China yet serving users worldwide.
Vision
- Rooted in China: leverage our scale, scenarios, and demand to harden solutions under extreme stress.
- Facing the world: ship battle-tested, developer-friendly distros and extension repos that anyone can consume, just like they consume Debian packages.
- Play to our strengths: we may not have a kernel committer yet, but we can dominate tooling, packaging, automation, and integrations—the layers that actually reach users.
Pigsty isn’t the only answer, but it proves a point: a single Chinese engineer, working the right problem, can earn a seat at PostgreSQL’s global table. Imagine what we could do together.
Originally published on vonng.com.
10 - On Trusting Open-Source Supply Chains
My earlier post “PG ‘Export Controls’ and Supply-Chain Trust” drew a comment from an administrator at Tsinghua University’s TUNA mirror:
“As a university mirror admin, calling us ‘lying flat’ or ‘irresponsible’ is unfair and demoralizing.”
I replied:
Thanks for the feedback, and for the time and work that TUNA and other university mirrors have contributed over the years. I see that TUNA’s PostgreSQL repository has resumed synchronization, which is good news.
When I first found the problem, I was using Alibaba Cloud’s PostgreSQL mirror. I later noticed the same problem at TUNA and reported it on the mailing list. The reply said that the list belonged to TUNA, not Alibaba Cloud. After I clarified the report, I heard nothing further. That experience affected the tone of my article.
Looking back, “lying flat” and “irresponsible” were too emotional, especially when applied to your team. They could read as moral judgments on volunteers, which was not my intention. I apologize for that wording. I have replaced it with descriptions such as “not updated for a long time” and “no longer maintained.”
I agree that university mirrors are volunteer projects without contractual service commitments. But downstream users still face a real problem: after PGDG cut off rsync, major mirrors in China remained months out of date. Users following instructions to use a recommended mirror experienced a supply-chain interruption. That damages trust.
My conclusion is that treating a volunteer mirror as critical production infrastructure, without a service commitment, is a mistake. I now host my own PGDG mirror and control that part of my supply chain. Thank you for explaining the maintainer’s perspective. Helping users understand what they can expect from mirrors is valuable in itself.
My reflections
After receiving the comment, I checked TUNA again. PostgreSQL 18 packages were available, although “Last Update” still showed May 16, 2025. That suggested a manual sync. Besides the Pigsty repository, China had another node providing reasonably current PGDG packages. Credit to TUNA for restoring it.
When this outage began, Pigsty used Alibaba Cloud’s PostgreSQL mirror. My criticism was aimed mainly at a large company with substantial resources — familiar territory for my Cloud Mudslide articles. Alibaba Cloud benefits heavily from open source, yet left its PostgreSQL mirror stale. It was the TUNA volunteers who responded, and I understand why they objected.
I do not think that providing a free mirror obliges either group to maintain it forever. But users will still judge the result. My frustration was real; applying it to university volunteers in those terms was unfair. I changed the wording to describe the maintenance problem itself.
When I found the global mirror synchronization problem, I emailed Alibaba Cloud. I also checked other mirrors and reported the same problem to TUNA. My message contained TUNA URLs whose labels still said “ALIYUN,” which prompted the reply about the wrong mailing list. I corrected that mistake and explained the shared upstream problem. There was no further reply until this November discussion.
After waiting about another month, I set up a PGDG mirror for China on Tencent Cloud, alongside Pigsty’s own repository. I removed Alibaba Cloud’s PostgreSQL mirror from Pigsty’s dependencies and took control of PostgreSQL package distribution. Without this interruption, I probably would not have done it so soon.
I am often blunt with cloud vendors. That does not mean I dismiss the difficulties volunteers face. University maintainers deserve more support and fewer moral accusations. That is why I changed my wording.
Open source and trust
I recently experienced the same problem from the maintainer’s side. I maintain several open-source projects, including pg_exporter, a Prometheus exporter used by some PostgreSQL vendors.
On November 20, I noticed a comment on an issue opened in September. In October, the user had explained that two issues had gone unanswered for a month. They had been excited about version 1.0 and considered contributing, but the lack of response had changed their mind:
“I am not willing to bet on a project with a maintainer who is absent.”
They hoped my absence was temporary and wished both me and the project well.
I had spent September driving around Xinjiang and barely used a computer. I had opened the issues, then forgotten about them. My lack of attention cost the project a user’s trust, and perhaps a future contributor. I regretted letting them down. I put other work aside, addressed the reported problems, and released version 1.0.3.
When an upstream mirror stops working and a report receives neither a response nor a fix, I feel the same way that user did. I do not want to depend on a project whose maintainer appears absent.
The software supply chain
In “PG ‘Export Controls’ and Supply-Chain Trust,” I argued that publishing source code is only one way to build trust. Continued effort, dependable delivery, and a willingness to address problems matter too.
I understand the mirror administrator’s frustration. They receive no pay, spend their own time maintaining the service, and take on security and compliance responsibilities. Being called irresponsible is a poor reward for that work.
There is an uncomfortable gap between the two perspectives. A maintainer can reasonably say, “I am not paid, and I have made no commitment.” A user sees a public service and starts to treat it as dependable infrastructure. That expectation may be unreasonable, but it exists.
A mirror also occupies a specific place in the supply chain. It carries software from upstream to downstream, so stale packages can directly affect production systems. Providers differ in reliability, in whether they offer an SLA, and in whether they accept responsibility when something fails.
Consistent maintenance and clear expectations build trust. Stale or unreliable results erode it. The TUNA administrator’s statement that university mirrors make no service guarantees is honest. It also tells a production operator where that service’s limits are.
My point is that a production supply chain should not depend on an upstream service that explicitly makes no commitment to its availability.
Quality and commitments
TUNA provides useful services. I use its PyPI and Homebrew mirrors on my own computer to speed up downloads. Early versions of Pigsty also used some of TUNA’s PostgreSQL mirrors. After several unexpected HTTP 403 failures disrupted automated deployments, I switched to Alibaba Cloud.
Alibaba Cloud’s mirror was generally reliable until PGDG stopped rsync access. Its PostgreSQL packages then remained months out of date. That reduced my trust, so I took over the PGDG mirror myself. At the time of writing, I still use Alibaba Cloud for operating-system packages. If those mirrors repeatedly fail, I may also host Rocky Linux, Ubuntu, and Debian mirrors.
The choices follow from the commitments each party makes:
- TUNA says university mirrors offer no service commitment. Downstream users can choose their use cases accordingly.
- Alibaba Cloud has not made that point explicit. I judge its service by its record. If it keeps failing, I will report the problems, criticize the service, or replace it.
- For my Pigsty and PGDG APT/DNF repositories, I make a commitment: if they break, I will fix them. Failure damages my reputation; for paying customers, it also means failing an SLA.
Mirrors, repositories, and distributions support the rest of our infrastructure. Accepting responsibility for their operation is part of becoming a supplier that others can rely on.
When an upstream says, “Don’t count on me,” my response is, “Then I’ll take responsibility for it myself.”
A word from our sponsor
I rarely pass up a chance to mention my own work. These three public repositories may be useful to you. They have global endpoints behind Cloudflare and separate endpoints in China, and can be configured through PIG, APT, or DNF. See the repository documentation for current addresses and setup instructions.
PGDG mirror
The mirror copies the PostgreSQL Global Development Group’s packages for EL 7–10, Debian 11–13, and Ubuntu 20.04–24.04. On November 22, 2025, it had just completed a sync and was being updated weekly.
PIGSTY PGSQL
This repository supplements PGDG with PostgreSQL variants, including PolarDB, IvorySQL, Babelfish, OrioleDB, OpenHaloDB, Percona TDE, and Supabase. At the time of writing, it provided 437 PostgreSQL extensions and ecosystem tools across 14 Linux distribution major versions.
PIGSTY INFRA
This repository provides database tools, Grafana, Prometheus, and exporters. The following package versions describe the November 22, 2025 snapshot.
Databases and ecosystem tools
| Package | Version |
|---|---|
| IvorySQL | 4.6 |
| etcd | 3.6.6 |
| minio | 20250907161309 |
| mcli | 20250813083541 |
| kafka | 4.0.0 |
| duckdb | 1.4.2 |
| ferretdb | 2.7.0 |
| tigerbeetle | 0.16.60 |
| juicefs | 1.3.0 |
| dblab | 0.34.2 |
| v2ray | 5.28.0 |
| pig | 0.7.2 |
| vip-manager | 4.0.0 |
| pev2 | 1.17.0 |
| promscale | 0.17.0 |
| pgschema | 1.4.2 |
Prometheus and exporters
| Package | Version |
|---|---|
| prometheus | 3.7.3 |
| pushgateway | 1.11.2 |
| alertmanager | 0.29.0 |
| blackbox_exporter | 0.27.0 |
| VictoriaMetrics | 1.129.1 |
| VictoriaLogs | 1.37.2 |
| pg_exporter | 1.0.3 |
| pgbackrest_exporter | 0.21.0 |
| node_exporter | 1.10.2 |
| keepalived_exporter | 1.7.0 |
| nginx_exporter | 1.5.1 |
| zfs_exporter | 3.8.1 |
| mysqld_exporter | 0.18.0 |
| redis_exporter | 1.80.0 |
| kafka_exporter | 1.9.0 |
| mongodb_exporter | 0.47.1 |
Grafana and log tools
| Package | Version |
|---|---|
| grafana | 12.3.0 |
| loki | 3.1.1 |
| promtail | 3.0.0 |
| vector | 0.51.1 |
| grafana-infinity-ds | 3.6.0 |
| grafana-vmlogs-ds | 0.21.4 |
| grafana-vmetrics-ds | 0.19.6 |
| grafana-plugins | 12.0.0 |
Utilities
| Package | Version |
|---|---|
| sealos | 5.1.1 |
| rclone | 1.71.2 |
| restic | 0.18.1 |
| mtail | 3.0.8 |
| genai-toolbox | 0.18.0 |
| sqlcmd | 1.8.0 |
Originally published on vonng.com.
11 - PG Extension Cloud: Unlocking PostgreSQL’s Entire Ecosystem
PostgreSQL’s killer feature is extensibility. PostGIS, pgvector, pg_duckdb, pg_search—extensions turn PG into GIS engine, vector DB, analytics warehouse, search cluster. But compiling and shipping them reliably across distros is a nightmare, especially when official mirrors freeze or your network can’t reach upstream.
After two years of grinding, I’m launching PGEXT.CLOUD: the infrastructure for discovering, packaging, and installing PG extensions.
What’s inside
- Extension catalog – Browse 431 extensions with metadata, docs, compatibility matrices, how-to guides. Think “Wikipedia for PG extensions.”
- Binary repos – Native RPM/DEB packages for 14 Linux releases and 6 major PG versions. No Docker-only traps.
pigCLI – A 4 MB Go tool that wraps your existing package manager and hides the matrix of platforms/versions.
Try it on a fresh server/container:
Behind those three lines sits combinatorial chaos—14 distros × 6 PG versions × 431 extensions. PIG selects the matching packages for the host.
Why we needed this
Official PGDG repos ship ~135 extensions. Popular ones (PostGIS, pgvector) are there, but many heavy-hitters aren’t: pg_duckdb, pg_mooncake, plv8, Supabase’s Rust extensions. PGDG maintainers understandably don’t want to maintain ten-minute Rust builds.
I hoped projects like Tembo’s trunk or pgxman would solve distribution. They didn’t. So I built it myself. Today PGEXT.CLOUD packages 260 EL extensions and 241 Debian extensions—about 72% of everything listed. The catalog tracks availability by OS/version and documents installation for every extension.
Smooth installs
pig isn’t a new package manager; it’s a piggyback layer over yum/dnf/apt. You can still use apt install postgresql-18-pgvector directly—the repos are standard. pig just automates repo setup, architecture detection, PG version switching, and dependency resolution.
Open supply chain
Some folks asked, “You’re in China—how do we trust your binaries?” Supply-chain trust is hard regardless of nationality; even PGDG’s yum repo relies on Devrim’s reputation. My answer: everything is open. The build scripts, Dockerfiles, and tooling are public. You can rebuild any package yourself in an isolated environment:
Then:
The packages on PGEXT.CLOUD are built exactly this way. If you don’t trust me, rebuild locally and host your own repo. That’s the point: open tooling, reproducible builds, no lock-in.
PGEXT.CLOUD is my attempt to make PostgreSQL’s extension ecosystem accessible. Discover what exists, install it in seconds, and unleash PG’s full potential.
Originally published on vonng.com.
12 - Build and Packaging: An Overlooked but Scarce Skill
I was recently chatting with my friend Yurii, the founder of Omnigres. He wants to hire a PostgreSQL packaging expert and has even coined a title for the role: EEE, or Extension Ecosystem Engineer. It is an interesting idea. The job description is public, so I have included it at the end.
A Scarce Skill: Linux Packaging
I think this job description asks for a little too much: DevRel + SRE + DBA + build engineer + PostgreSQL specialist, all in one person. It almost reads as though it were written for me. But I have genuinely never met anyone else with that exact combination, so I advised Yurii that hiring a build engineer who knows Debian and Enterprise Linux packaging inside out would be more realistic. Even that will be difficult, though. Scarce hardly begins to describe people who understand build and packaging work. DevRel talent may be scarcer still.
The context here is specifically building and packaging PostgreSQL kernels and extensions for Linux. Most of the code is C or C++, with some Rust, Java, Go, and other languages mixed in. The main deliverables are RPM and DEB packages distributed through YUM and APT repositories. I believe this is a remarkably valuable skill that gets very little attention.
When Did I Realize This?
I first recognized the importance of packaging in 2017, during an interview with Pivotal. One of the interviewers asked, almost in passing, “Do you know how to package software? We don’t have anyone who does.” I wondered what kind of package he meant. RPMs? As it turned out, yes. Later, Yao of YMatrix, who had also come out of Pivotal, asked me much the same thing: “You know build and packaging work pretty well, don’t you? We badly need that skill right now.” That made the gap stick in my mind.
Since then, I have examined software released by many database companies in China and abroad. Their packaging is often painful to behold. How, for example, did Greenplum used to ship to customers? As a single CentOS 7.9 RPM. That was it. Wanted to run it on EL 8, EL 9, Ubuntu, Debian, or another Linux distribution? Tough luck.
Alibaba Cloud’s PolarDB for PostgreSQL and HighGo’s IvorySQL also started with only one or two EL RPMs. After a great deal of pushing from me, they eventually covered the mainstream Linux distributions. For the MySQL-compatible OpenHalo kernel and OrioleDB, I simply stepped in and packaged them myself.
Why Build and Packaging Matter
Packaging expertise is scarce, but where does its value come from? Most end users do not care whether your software is open source. What they care about is whether a stable, reliable—and preferably free—binary package is available to download. Greenplum is now closed source, yet friends still ask me from time to time for Greenplum RPMs. Yao’s YMatrix, a closed-source branch of GP7, is commercial software. But it offers a free trial download, so people can still use it. Whether its source is open hardly matters to them.
A more recent example is the KubeSphere community’s binary cutoff. The source code was still there, but the project deleted its binary artifacts—the container images—and that directly affected end users. Whether the code was open source made no practical difference to them. The real supply-chain chokepoint has never been source code, but the finished software artifacts users actually run.
Packaging Makes Open Source Self-Reliant
Open-source expert Tison explored this issue in depth in his articles “How Can You Use Open Source Software with Confidence?” and “Can Open Source Software Be Cut Off?”. His conclusion is that open-source software itself cannot be cut off, but its artifacts can. If you want to use open source with confidence, the most important safeguard is to keep a local copy of the software or operate your own package repository. Build and packaging work is the foundation for that independence.
Consider the recent PGDG repository supply disruption. Almost every mirror worldwide lost synchronization with the PGDG upstream and remained stuck on versions five months out of date. At the time, only xTom in Germany, Yandex in Russia, and Pigsty in China were providing manually updated mirrors of the latest PGDG packages.
Of course, a mirror merely copies binary artifacts built by someone else. Imagine the more extreme case: instead of merely stopping incremental synchronization, PGDG locked everything down completely. To build an independent repository from scratch, with separate packages for RISC-V, MIPS, ARM, and the rest of the architectural menagerie, you would still have to cross the build-and-packaging barrier.
Not All Packaging Is Equal
Someone will inevitably say, “But it is open source. You can compile it yourself.” That is true. Software written in modern languages often comes with a much smoother packaging workflow. Go programs, for example, are exceptionally easy to build and package. Tools such as GoReleaser can build an entire cross-platform matrix in one shot, generate RPM and DEB packages, build and push Docker images, and create a GitHub Release automatically. With vibe coding, you could probably implement such a workflow in under half an hour.
But that is not what we are talking about. We are talking about ecosystem-scale projects such as Debian and PostgreSQL, especially the C and C++ software at their core. Packaging the PostgreSQL database is not a matter of producing a handful of RPMs, either. Across the 10 Linux distributions and five PostgreSQL major versions I support, plus extensions and tools, I now provide roughly 40,000 RPM and DEB packages.
Build and packaging work is not easy. You must untangle dependencies involving glibc, ICU, OpenSSL, and PostGIS’s enormous dependency tree; deal with the obscure system libraries required by assorted extensions; resolve version conflicts across distributions and even across major releases of the same distribution; and master a whole toolbox that includes CMake, Make, Ninja, Cargo, and more.
Why Not Docker?
Docker looks like a shortcut around packaging: build once, run anywhere. If only. Docker does remove Linux distribution releases—EL 9, Debian 12, Ubuntu 24.04, and so on—from the build matrix. But you still need separate builds for PostgreSQL major versions, system architectures, and hundreds of extensions in multiple versions. And if you inspect PostgreSQL Docker images, you will find that their Dockerfiles often still use apt install to install PGDG’s DEB packages. Linux packages are upstream of Docker images, not the other way around.
Second, extensions are one of PostgreSQL’s defining advantages over other databases, yet container images still have no elegant answer to the problem of persistent PostgreSQL extensions. You do not know which of hundreds of extensions a user will need, but installing every one of them makes an image bloated and foolish. Álvaro is doing some pioneering work in this area, but in my view the approach remains some distance from mature operational practice.
Is Putting a Database in Docker a Good Idea?
Should Databases Be Deployed in Kubernetes?
How I Got Into Packaging
I started doing this work only about two years before writing this article. I wanted Pigsty to support self-hosted Supabase, but Supabase depended on more than a dozen PostgreSQL extensions, most of which were absent from the official PGDG binary repositories. I asked Devrim, the maintainer of the PGDG YUM repository, about them. He told me that extensions written in Rust would never make it into PGDG because they took too long to compile. So I rolled up my sleeves and built the RPMs myself.
Once the RPMs existed, I thought I might as well produce DEBs too. And once I supported more than a dozen PostgreSQL extensions, why not package the other 200-plus extensions missing from the official PGDG repositories?
Step by step, that effort grew into the PostgreSQL extension repository I maintain. At the time of writing, it contained nine flavors of the PostgreSQL kernel and more than 200 PostgreSQL extensions—423 available extensions when combined with PGDG. It offered the world’s broadest selection of usable PostgreSQL extension artifacts. Without false modesty, when it comes to PostgreSQL build and packaging work, Devrim on the YUM repository, Christoph on the APT repository, Álvaro on the OCI repository, David Wheeler on PGXN, and I are among the strongest practitioners in the field.
The clearest example is Supabase. As the darling of the AI wave and perhaps the database sector’s biggest winner, it should have drawn an army of vendors into the market. Yet at the time of writing, the only open-source PostgreSQL distributions capable of delivering self-hosted Supabase were Pigsty, my Linux-native distribution based on RPM and DEB packages, and Álvaro’s StackGres, based on OCI images and Kubernetes.
That is because we solved the build, packaging, and distribution problems for Supabase’s specialized extensions. This is the actual bottleneck. Even if Supabase publishes the source code for those extensions—and later switches to the OrioleDB kernel—how many people understand that code? How many users can turn it into something they can actually run?
Engineers who know EL or Debian packaging do exist. Engineers who also understand the PostgreSQL ecosystem well enough to build hundreds of PostgreSQL packages across more than ten Linux distributions are genuinely rare.
A Vanishingly Rare Craft
In practice, I have found this skill astonishingly scarce. Yurii asked me who else understands it. In China, nobody comes to mind. Even globally, perhaps the author of ZomboDB, who also created pgrx and was hired by ParadeDB, could do it well. Beyond that, I struggle to name anyone.
The PostgreSQL ecosystem has a huge number of extensions, but ParadeDB is the only extension vendor I know that ships mainstream Linux RPM and DEB packages at release time for pg_search. They do it because they release so frequently. I grew tired of packaging every release for them, so I taught them the process step by step. PGroonga, TimescaleDB, and Citus also produce their own packages, but those packages do not consistently follow PGDG conventions and their build matrices often have holes. Citus has long lacked ARM packages; TimescaleDB misses several specific distributions; and PGroonga packages against the PostgreSQL version bundled by Debian. The list goes on.
The same pattern appears among Chinese database vendors. Alibaba Cloud’s PolarDB for PostgreSQL and IvorySQL once offered only a few EL RPMs. After I pushed them hard, they eventually produced packages for all 10 mainstream Linux distributions supported by Pigsty. I also helped them fix several elementary packaging mistakes. For the MySQL-compatible OpenHalo, I simply built the DEB and RPM packages myself. Supabase’s OrioleDB appeared to lack this capability as well, so I packaged it too and made it work out of the box in Pigsty.
Conclusion
Value often comes from non-consensus skills. Build and packaging work is a perfect example. To a casual observer, it looks like little more than compiling some code and wrapping it in a package. In reality, it is an exceptionally scarce craft—one whose absence creates painful supply-chain chokepoints.
References
Originally published in Chinese on vonng.com.
13 - The PostgreSQL 'Supply Cut' and Trust Issues in Software Supply Chain
This month, KubeSphere removed its container images, prompting a debate about open-source supply chains. A quieter disruption had already affected PostgreSQL: PGDG had closed its mirror synchronization channels. For me, that disruption tested how database vendors and cloud providers respond when their upstream supply chain fails.
I was disappointed by the response. I stopped relying on Chinese cloud and university mirrors and built a domestic mirror of the PGDG YUM and APT repositories myself.
PGDG’s “Supply Cut”
PostgreSQL is one of the most widely used open-source databases. Many Linux users install it through the PGDG APT and YUM repositories. In mid-May, PGDG (PostgreSQL Global Development Group) closed the FTP and rsync channels used to synchronize its repositories. Most mirrors stopped receiving updates and continued serving packages that were months old.
I covered this in detail in “Chokepoint: PGDG Cuts Mirror Sync Channels” on July 7th. At that time, Germany’s xTom appeared to update its mirror manually each month. Most other mirrors still held packages from March, April, or May. When I checked again yesterday, Russia’s Yandex had also updated its APT mirror manually; the others were still stale.
| Provider | Region | Sync Timestamp | URL |
|---|---|---|---|
| Alibaba-Cloud | China | 2025-03-31 | sync timestamp |
| Tencent Cloud | China | 2025-03-31 | sync timestamp |
| Volcano Cloud | China | 2025-03-10 | sync timestamp |
| Huawei Cloud | China | 2024-01-02 | sync timestamp |
| Tsinghua TUNA | China | 2025-03-31 | historical screenshot |
| Zhejiang Univ | China | 2025-03-31 | sync timestamp |
| USTC | China | Removed | removal notice |
| TrueNetwork | Russia | 2025-01-31 | sync timestamp |
| JAIST | Japan | 2025-03-31 | sync timestamp |
| DOTSRC | Denmark | 2025-03-31 | sync timestamp |
| MirrorService | UK | 2025-03-31 | sync timestamp |
| Princeton Univ | USA | 2025-03-31 | sync timestamp |
| YANDEX | Russia | 2025-08-13 | mirror |
| XTOM | Germany | 2025-07-24 | mirror |
| PIGSTY | China | 2025-08-14 | repository docs |
Mirrors “Stop Updating”
For instance, the 17.5 May update fixed CVE-2025-4207 GB18030-related vulnerability, and the just-released 17.6 series fixed 3 CVEs and 55 bugs. If you’re a mirror user, you can’t update and patch in time. Not to mention PostgreSQL 18 releasing next month. We’re still in the early stages — just two PG minor versions behind, but soon it’ll be a major version behind. All those accumulated vulnerability patches and security fixes become unavailable to domestic users, creating increasingly larger exposure risks.
From a mirror user’s perspective, the flow of updates had stopped. PGDG did provide an alternative: direct downloads through a CDN.
Why PGDG “Cut Supply”
In the PostgreSQL mailing list, on May 20th, a Korean mirror maintainer asked why rsync sync with PGDG official repository suddenly broke.
Dave Page explained that FTP and rsync had never been officially supported services. The repositories ran on two physical machines and handled about 10 TB of traffic each day, including substantial unwanted traffic. PGDG moved downloads behind Fastly’s CDN.
The reasoning is understandable: a CDN can serve users directly across many locations. PGDG kept HTTP access available and closed FTP and rsync. Users who could reach the CDN could switch to the official repository.
What This Meant for Users in China
The disruption had less impact where users could reach PGDG’s CDN. In mainland China, access to those CDN endpoints was unreliable or blocked. Stale domestic mirrors therefore left many users without a practical source of current packages.
A proxy could restore access, but not every user could configure one, and downloads could still be slow. Domestic mirrors remained important. Docker did not remove that dependency: Docker Hub also had access problems, and many PostgreSQL images installed packages from the same APT repositories.
From this angle, Chinese users faced a real supply-chain bottleneck. PGDG had supplied an alternative, but local network restrictions made it hard to use. Whatever the cause, someone needed to restore access to current packages.
Common choices included Alibaba Cloud, Tsinghua University’s TUNA, Zhejiang University, and USTC. The mirrors I checked had stopped updating. That was frustrating, although these were free services rather than contracted suppliers.
Supply Chain Risk
In “How to Safely Use Open-Source Software?” and “Does Open-Source Software Have Supply Cut Risk?”, Tison distinguishes access to source code from access to a supplier’s services. His argument is that a supplier cannot withdraw the rights already granted over an open-source copy. Expectations of continued service often get confused with those rights.
Users depend on a concrete supply chain, however. RPMs, DEBs, container images, and the repositories that distribute them can disappear or stop receiving updates. Keeping that delivery path available takes work and money.
Distribution has costs. Developers may enjoy writing software, but packaging, repository maintenance, and reliable delivery require sustained work. At ¥0.80 per GB, 10 TB of daily traffic would cost thousands of yuan a day. Universities and large internet companies often operate mirrors because they already have the infrastructure and need the packages themselves.
Most users do not pay PGDG or mirror operators. An open-source license does not require its authors to operate a package repository indefinitely. Users who depend on a free service still need to decide how to maintain their own supply chain.
How to Solve Supply Chain Risk?
Could commercial database vendors solve this? Many Chinese database products derive from PostgreSQL, so I expected at least some vendors to maintain a local mirror. Yet many depended on Alibaba Cloud or TUNA themselves. Others shipped a few RPM files rather than maintaining a repository at all.
I maintain a repository with nine PostgreSQL kernel variants and more than 200 additional extensions—over 400 available extensions when combined with PGDG. Building it has given me substantial packaging experience, alongside the work of Devrim on YUM, Christoph on APT, Álvaro on OCI delivery, and David Wheeler on PGXN.
I still use PGDG for the standard PostgreSQL packages. Devrim and Christoph already do that work well; Pigsty’s repository supplements it with extensions. PGDG is therefore an upstream supplier for Pigsty, and Alibaba Cloud had been our indirect supplier in China. When that mirror and the alternatives stopped updating, I needed a replacement.
I reported the problem to Alibaba Cloud and TUNA and discussed it with Dege. Weeks passed without a resolution. I was disappointed, but waiting longer would not solve the problem for Pigsty users.
I’ll Do It Myself
So I built the mirror. FTP and rsync were closed, but HTTP remained available. Yesterday, with help from Claude Code, I spent about two hours writing a synchronization process with apt-mirror and reposync, then downloaded and tested the packages. The solution was much simpler than I had expected. It was frustrating to see such a small amount of work remain undone for so long.
The complete repository occupied hundreds of gigabytes. I selected x86_64 and aarch64 packages for PostgreSQL 13–17 on Debian 11/12/13, Ubuntu 22.04/24.04, and EL 7/8/9/10. Keeping only the latest package versions reduced the mirror to a few dozen gigabytes. After about two hours of downloading, I published the mirror behind a domestic CDN. PIG v0.6.1 uses this mirror in mainland China, and Pigsty v3.6.1 will follow. Pigsty can now maintain its own path from PGDG to local users.
The mirror is free to use, with or without Pigsty. Public downloads add bandwidth costs, but maintaining the mirror is also part of my commitment to customers, and everyone benefits from that work. Contributions of servers, CDN capacity, or funding are welcome.
This reminded me of the extension repository. Two years ago, I hoped projects such as Tembo and pgxman would provide the delivery tools I needed. After waiting for months, I built PIG, the extension catalog, and the package repository myself. Projects such as Omnigres and Autobase now use that repository to deliver packages to their users. My own supply chain has become part of theirs.
Publishing source code alone does not provide a reliable supply of packages. Trust comes from sustained work: maintaining releases, keeping delivery commitments, and responding when things break. An open-source repository is one part of that commitment.
Originally published on vonng.com.
14 - PGDG Cuts Off Mirror Sync Channel
Recently, while building Pigsty offline packages, I discovered that the PostgreSQL version installed during local testing wasn’t quite right - 17.4 was behind the latest 17.5 by one minor version. Also, when testing on EL10, I found several repositories were throwing errors. Strangely, using the global default repository in Hong Kong worked fine, but once using Chinese mirror sites locally, errors occurred.
Upon closer inspection, I found that domestic mirror sites had all lost synchronization with the PostgreSQL upstream repository: Tsinghua University Open-Source Software Mirror Site (TUNA) last successful sync was May 16th, while Alibaba-Cloud Mirror Site’s last sync timestamp was March 31, 2025. Foreign mirror sites like mirrors.xtom.de also had this problem, with last sync on June 20th, though you could clearly see signs of manual updates and disconnection from sync.
I searched and found that on May 20th in the PostgreSQL mailing list, a Korean mirror site maintainer had already asked about this issue - the mirror site maintainer asked why rsync synchronization with PGDG official repository suddenly broke?
PostgreSQL contributor Dave Page replied that due to massive amounts of illegal traffic flooding in, they decided to permanently shut down the previously unofficial FTP server, no longer providing rsync sync options, only allowing HTTP access.
PostgreSQL, as the world’s most popular database software, has the vast majority of users downloading and installing pre-built binary software packages through PGDG official repositories rather than compiling from source. This repository is hosted on just two physical machines - according to PostgreSQL Infra Team statistics, roughly 66 million requests per day (about 750 downloads per second), about 10TB of data transfer daily.
At PGConf.dev 2025, the infrastructure presentation described a move from four servers to two, with a CDN in front. Then seeing this traffic was too much to handle, they just cut off rsync/ftp, and all downstream PostgreSQL repositories worldwide went dark. Honestly, I think this is quite ridiculous - if you block all these mirror sites, when users flood directly to the original upstream, won’t the traffic be even greater?
But honestly, you can’t really blame them for anything, because this is just open source STYLE - no warranty - after all, they’re not charging money, developers have no obligation to keep doing charity. But from another perspective, this really strangled global users’ supply chain: for example, if users using mirror sites can’t timely update to 17.5 which fixes CVE vulnerabilities.
I’ve already reported this issue to Alibaba-Cloud Mirror and Tsinghua TUNA Mirror maintainers to see if it can be fixed recently. For example, using HTTP to pull updates. If it can’t be resolved in the short term, I’m prepared to pull down part of the PGDG repository myself and put it on Cloudflare to make a mirror site first.
From a supply chain security perspective, forking and modifying a PG kernel indeed has no real use. But maintaining a self-controlled software binary product repository has critical significance for operational autonomy and control.
I’ve also been thinking about setting up a mirror site domestically myself, since I’ve already set up a Pigsty APT/YUM repository, adding PG wouldn’t be a big deal. But actually Alibaba-Cloud and TUNA have been doing quite well before, so I’ve always used these two as default configurations for domestic users.
As for the long term, actually I could recompile and package a dedicated PostgreSQL repository, especially since I’ve recently packaged several PG branch kernels, plus over 250 extensions in the PG ecosystem not included by PGDG. I’m already a veteran packager when it comes to building APT/YUM repositories. However, the main issue is maintenance takes too much time, and domestic traffic costs are also too expensive. But if there’s a sponsor willing to support unlimited traffic high-bandwidth servers, I’d be happy to do some extra volunteer work.
Originally published on vonng.com.
15 - Postgres Extensions Day — See You There!
The annual PostgreSQL developer conference, PGConf.dev, will be held in Montreal in May. Postgres Extensions Day takes place the day before it, with a focus on extension development, packaging, and release. The agenda has just been released, with 14 talks.
This time, I’ll be speaking in the first afternoon session: “The Missing Postgres Extension Repo and Package Manager”. I’ll introduce Pigsty’s extension repository and the PIG package manager, and discuss the work of building and maintaining PostgreSQL extensions. It is a chance to share a Chinese developer and database vendor’s experience with developers from around the world — even if that vendor is a one-person shop, haha.
Postgres Extensions Day is scheduled for May 12, 2025, at Plaza Centre-Ville in Montreal, Quebec, Canada. PGConf.dev 2025 follows at the same venue on May 13–16.
Last year’s PG developer conference in Vancouver was incredibly rewarding, though there were very few participants from China. Not sure how this edition will be - if you’re also going, please leave a comment and we can meet up in person!
If you plan to attend, register separately at pgext.day. The Postgres Extension Developers Coalition organizes this independent event. Attendance is free; a regular PGConf.dev ticket costs CAD 500 before tax.
Below is the PG Extension Summit agenda - looking forward to seeing readers at the extension summit!
Extension Summit Schedule
1. From pl/v8 to pl/<any>: Towards Easier Extension Development
9:00 am → 25 min, Hannu Krosing
From pl/v8 to pl/<any>: towards easier extension development
pg_tle lets developers write and deploy trusted-language extensions without superuser privileges. It also provides hooks for trusted language functions, such as enforcing password policies. pl/<any> further allows using any language to write database functions, thereby implementing extensions. The main approach is writing Language Handlers in JavaScript and using languages that can be transpiled to JavaScript as PostgreSQL’s embedded (or “pl/”) language.
Examples include:
- pl/jsonschema: Based on the AJV JSON Schema validation library, directly converting JSON Schema definitions into runnable validation functions, sometimes far outperforming pg_jsonschema wrapped with Rust + PGRX.
- pl/wasm: Runs compiled WebAssembly as standard PostgreSQL functions. For compute-intensive code, the session abstract reports execution times about 2–3 times those of native code.
- pl/codelength: Example handler that converts any source code into a function returning the original code’s length.
Future expansions on pl/v8 could include:
- Writing custom FDWs (similar to Python’s Multicorn)
- Writing custom logical decoding plugins
- Exposing more hooks and trace points for JavaScript handlers
- Allowing users to directly construct plan trees, even adding new node types or monitoring probes
2. Upgrade as an Extension
9:30 am → 25 min, Andrey Borodin
Upgrade as an extension
(No content description available, but the title alone sounds exciting!)
3. Inlining Postgres Functions: Now and Then
10:00 am → 25 min, Paul Jungwirth
Inlining Postgres Functions, Now and Then
When PostgreSQL calls user-defined functions (or built-in functions), it might attempt inlining, providing new possibilities for SQL developers and extension authors. This talk will introduce two inlining methods currently used by PostgreSQL (available now) and a patch in development aimed at supporting inlining for most set-returning functions. Your functions can replace themselves with a “plan tree,” which the optimizer then merges with other query parts - almost like writing a macro!
4. Postgres à la Carte: Dynamic Container Images with Your Choice of Extensions
10:30 am → 25 min, Alvaro Hernandez
Postgres à la carte: dynamic container images with your choice of extensions
When building Postgres container images, required extensions are typically bundled, but security and size concerns prevent packaging all hundreds of available extensions at once. However, different users need vastly different extension combinations, and building dedicated container images for every possible combination would exceed the number of atoms in the universe.
Dynamic OCI container images can be generated on demand with the required extensions. These images can be used in any OCI-compatible environment like Kubernetes.
This talk will explore the concepts and technology behind dynamic container images and how to apply them for loading arbitrary extension combinations into Postgres images. The presentation will feature extensive demonstrations!
5. Cppgres: One Less Reason to Hate C++
11:00 am → 25 min, Yurii Rashkovskii
Cppgres: One less reason to hate C++
Writing Postgres extensions in C often feels tedious, error-prone, and repetitive. While many developers avoid C++ due to its complexity, modern C++ offers rich features making it easier to write reliable, maintainable Postgres extensions.
If you’re considering switching to Rust, consider C++ first - using the same compiler while enjoying more safety and usability.
This talk will introduce Cppgres: a lightweight, header-only C++20 library for writing safer, more readable Postgres extensions. Using concepts, automatic type deduction, and other modern C++ techniques, you can write concise, efficient, maintainable extensions. The talk will show how these features apply to extension development.
6. Working with MemoryContexts and Debugging Memory Leaks in Postgres
11:30 am → 25 min, Phil Eaton
Working with MemoryContexts and debugging memory leaks in Postgres
This talk will focus on creating and switching MemoryContexts in real scenarios, using tools like Linux’s eBPF to discover memory leaks. Content is based on real production cases, summarizing experiences and practical techniques from writing extensions and finding bugs.
7. Postgres as a Control Plane: Challenges in Offloading Compute via Extensions
12:00 pm → 25 min, Sweta Vooda
Postgres as a Control Plane: Challenges in Offloading Compute via Extensions
As Postgres’s role expands from storage layer to control plane, extensions orchestrating external systems (like vector search engines) must balance performance, consistency, and integration.
This talk will explore designing Postgres extensions to offload computation while maintaining SQL simplicity and transactional guarantees. Examples from pgvector-remote cover buffering, predicate pushdown, connection pooling, and VACUUM.
The session is aimed at engineers who want to offload computation while keeping a SQL interface.
8. Lunch
12:30 pm → 60 min
9. The Missing Postgres Extension Repo and Package Manager
1:30 pm → 25 min, Ruohang Feng
The Missing Postgres Extension Repo and Package Manager
Haha, that’s really me.
While PostgreSQL extensions are powerful and flexible, most users prefer “out-of-the-box” rather than compiling and manually building themselves. I have built a repository with packages for more than 200 extensions, filling gaps in the official PGDG repository. These RPM/DEB packages cover five Linux distributions, five major PostgreSQL versions, and x86-64/ARM64 architectures.
This talk will explore building this repository, including challenges like cross-distribution compatibility, multi-architecture support, version alignment, sharing experiences, lessons, and future improvements to make PostgreSQL extension installation easier.
10. How to Automatically Release Your Extensions on PGXN
2:00 pm → 25 min, David Wheeler
How to automatically release your extensions on PGXN
There’s currently no unified release center for all PostgreSQL extensions. While PGXN is the largest extension source code release service, it only includes about one-third of public extensions, and some versions aren’t current enough.
PGXN aims to become the root registry for all extension versions, hoping to sync all release information downstream to enable automated build processes. To achieve this, developers need to proactively upload extension updates to PGXN, benefiting the entire PostgreSQL community.
This talk will demonstrate setting up release processes on PGXN and achieving automation through Git, JSON, GitHub workflows, keeping your extensions current with one-click publishing to PGXN.
11. Extending PostgreSQL with Java: Overcoming Development Challenges in Bridging Java and C Applications
2:30 pm → 25 min, Cary Huang
Extending PostgreSQL with Java: Overcoming Development Challenges in Bridging Java and C Application
Java and C have vastly different design philosophies and memory management approaches. These seemingly opposite languages can work together seamlessly with the right methods to extend C-based PostgreSQL and integrate with Java applications or libraries.
This talk will share the development journey of the SynchDB project, which writes C extensions on the PostgreSQL side and integrates Java-version Debezium Embedded, guiding data change streams from MySQL, SQL Server, Oracle, and other sources into PostgreSQL.
The talk covers the challenges of using C and Java within one extension, including:
- JNI-based cross-language calls
- The process of embedding Debezium Embedded in C extensions
- Handling memory management and performance overhead
- Architectural integration of two language components
- Best practices for error handling, monitoring, and maintainability
Attendees will learn how to bring change data from other databases into PostgreSQL and combine C and Java within one extension.
12. Rethinking OLAP Architecture: The Journey to pg_mooncake v0.2
3:00 pm → 25 min, Cheng Chen
Rethinking OLAP Architecture: The Journey to pg_mooncake v0.2
In this talk, we’ll explore shortcomings of pg_mooncake v0.1 and major architectural changes made in v0.2. We’ll share lessons learned using Postgres replication, background worker processes, and extension-form inter-process communication (IPC).
13. Spat: Hijacking Shared Memory for a Redis-Like Experience in PostgreSQL
3:30 pm → 25 min, Florents Tselai
Spat: Hijacking Shared Memory for a Redis-Like Experience in PostgreSQL
Traditional databases typically use shared memory for work areas like query execution, caching, and transaction management - invisible to users. But what if we transformed it into high-performance data structures and caches for direct user use?
This talk will introduce PostgreSQL’s shared memory APIs exposed to extension developers (including the new DSM Registry) and how to build Spat: an in-memory data structure server storing data entirely in shared memory, providing Redis-like experience within PostgreSQL.
Spat provides key-value storage patterns supporting strings, lists, sets, hashes, and other structures, becoming lightweight, high-speed temporary storage within PostgreSQL. The talk will discuss the challenges of using shared memory this way.
14. Scaling PostgreSQL with Citus: Distributed Data for Modern Applications
4:00 pm → 25 min, Mehmet Yilmaz
Scaling PostgreSQL with Citus: Distributed Data for Modern Applications
This talk will explore how the Citus extension transforms PostgreSQL into a horizontally scalable distributed database. It covers Citus architecture, extension deployment, and production use.
Content includes:
- How Citus extends PostgreSQL to support distributed query processing and data sharding
- Best practices for extension packaging, release, and deployment in different environments
- Considerations for performance tuning and security mechanisms in distributed Postgres cluster operations
- Real success cases and lessons learned
15. Extensibility - New Options and a Wish List
4:30 pm → 25 min, Alastair Turner
Extensibility - new options and a wish list
Now is a great time to be a PostgreSQL extension developer - the community continues growing, even spawning dedicated extension summit events.
Meanwhile, Postgres continues opening more extensible areas. Over the past year, several core commits made EXPLAIN, cumulative statistics, COPY, and other parts extensible, but proposals in some areas like storage still await progress.
This talk will introduce recent new extensible areas (with example code) and discuss areas that still need work, especially storage.
16. Dinner
6:00 pm – 9:00 pm
Dinner
Looking Back at PGConf.dev 2024
- Andreas Scherbaum PostgreSQL Development Conference 2024 - Review
- PgCon 2024 Developer Meeting
- Robert Haas: 2024.pgconf.dev and Growing the Community
- How engaging was PGConf.dev really?
- Cary Huang: PGConf.dev 2024：Shaping PostgreSQL’s Future in Vancouver
- PGCon.Dev Extension Ecosystem Summit Notes @ Vancouver
- PG Conference 2024 Opening, Where’s the Vancouver Foodie Travel Group?
Originally published on vonng.com.
16 - Pig, The Postgres Extension Wizard
PIG is a Go command-line tool for installing PostgreSQL and extensions through the system package manager. It selects packages for the operating system, CPU architecture, and PostgreSQL version, then uses APT, YUM, or DNF to install them.
The extension repository supplies native RPM and DEB packages, including TimescaleDB, Citus, pgvector, and Rust extensions. It follows PGDG packaging conventions so these packages can be used with the standard PostgreSQL packages.
I built PIG to make that repository easier to use. You can search by extension name, inspect its metadata, and install the matching package without assembling package names yourself.
PIG v0.1 release · Source code · The ideal way to deliver PostgreSQL extensions
Get Started
Install the pig package with the installer or the system package manager.
Then it’s ready to use; assume you want to install the pg_duckdb extension:
Extension management
Repository management
Originally published on vonng.com.
17 - The Ideal Way to Deliver PostgreSQL Extensions
PostgreSQL Is Eating the Database World through the power of extensibility. When this post was first published, the repository packaged 340 PostgreSQL extensions as RPM / DEB packages for mainstream Linux distributions. The live Pigsty Extension Catalog has kept growing since then.
I believe the PostgreSQL community has reached a consensus on the importance of extensions. So the real question now becomes: “What should we do about it?”
What’s the primary problem with PostgreSQL extensions? In my opinion, it’s their accessibility. Extensions are useless if most users can’t easily install and enable them. But it’s not that easy.
Even the largest cloud PostgreSQL vendors are struggling with this. They have some inherent limitations (multi-tenancy, security, licensing) that make it hard for them to fully address this issue.
So here’s my plan: I’ve created a repository that hosts 340 of the most capable extensions in the PostgreSQL ecosystem, available as RPM / DEB packages on mainstream Linux OS distros. The goal is to take PostgreSQL one solid step closer to becoming the all-powerful database and achieve the great alignment between the Debian and EL OS ecosystems.
The status quo
The PostgreSQL ecosystem is rich with extensions, but how do you actually install and use them? This initial hurdle becomes a roadblock for many. There are some existing solutions:
PGXN says, “You can download and compile extensions on the fly with pgxnclient.”
Tembo says, “We have prepared pre-configured extension stack as Docker images.”
StackGres & Omnigres says, “We download .so files on the fly.” All solid ideas.
Based on my experience, the vast majority of users still rely on their operating system’s package manager to install PG extensions. On-the-fly compilation and downloading shared libraries might not be viable for production environments, because many database setups don’t have internet access or a proper toolchain ready.
In the meantime, existing OS package managers like yum/dnf/apt already solve issues like dependency resolution, upgrades, and version management well.
There’s no need to reinvent the wheel or disrupt existing standards. So the real question is: Who’s going to package these extensions into ready-to-use software?
PGDG has already made a fantastic effort with official YUM and APT repositories. In addition to the 70 built-in Contrib extensions bundled with PostgreSQL, the PGDG YUM repo offers 128 RPM extensions, while the APT repo offers 104 DEB extensions. These extensions are compiled and packaged in the same environment as the PostgreSQL kernel, making them easy to install alongside the PostgreSQL binary packages. In fact, even most PostgreSQL Docker images rely on the PGDG repo to install extensions.
I’m deeply grateful for Devrim’s maintenance of the PGDG YUM repo and Christoph’s work with the APT repo. Their efforts to make PostgreSQL installation and extension management seamless are incredibly valuable. But as a distribution creator myself, I’ve encountered some challenges with PostgreSQL extension distribution.
What’s the challenge?
The first major issue facing extension users is Alignment.
In the two primary Linux distro camps — Debian and EL — there’s a significant number of PostgreSQL extensions. Excluding the 70 built-in Contrib extensions bundled with PostgreSQL, the YUM repo offers 128 extensions, and the APT repo provides 104.
However, when we dig deeper, we see that alignment between the two repos is not ideal. The combined total of extensions across both repos is 153, but the overlap is just 79. That means only half of the extensions are available in both ecosystems!
Only half of the extensions are available in both EL and Debian ecosystems!
Next, we run into further alignment issues within each ecosystem itself. The availability of extensions can vary between different major OS versions.
For instance, pljava, sequential_uuids, and firebird_fdw are only available in EL9, but not in EL8. Similarly, rdkit is available in Ubuntu 22+ / Debian 12+, but not in Ubuntu 20 / Debian 11.
There’s also the issue of architecture support. For example, citus does not provide arm64 packages in the Debian repo.
And then we have alignment issues across different PostgreSQL major versions. Some extensions won’t compile on older PostgreSQL versions, while others won’t work on newer ones. Some extensions are only available for specific PostgreSQL versions in certain distributions, and so on.
These alignment issues lead to a significant number of permutations. For example, if we consider five mainstream OS distributions (el8, el9, debian12, ubuntu22, ubuntu24),
two CPU architectures (x86_64 and arm64), and six PostgreSQL major versions (12–17), that’s 60 RPM/DEB build combinations per extension, just for one extension!
On top of alignment, there’s the problem of completeness. PGXN lists over 375 extensions, but the PostgreSQL ecosystem could have as many as 1,000+. The PGDG repos, however, contain only about one-tenth of them.
There are also several powerful new Rust-based extensions that PGDG doesn’t include, such as pg_graphql, pg_jsonschema, and wrappers for self-hosting Supabase;
pg_search as an Elasticsearch alternative; and the now-archived pg_analytics, pg_parquet, and pg_mooncake for OLAP processing. The reason? They are too slow to compile…
What’s the solution?
Over the past six months, I’ve focused on consolidating the PostgreSQL extension ecosystem. Recently, I reached a milestone I’m quite happy with. I’ve created a PG YUM/APT repository with a catalog of 340 available PostgreSQL extensions.
Here are some key stats for the repo: It hosts 340 extensions in total. Excluding the 70 built-in extensions that come with PostgreSQL, this leaves 270 third-party extensions. The PGDG repositories supply part of this set. I maintain, fix, compile, package, and distribute the additional extensions through Pigsty. The table below shows availability by repository and platform.
| OS \ Entry | All | PGDG | PIGSTY | CONTRIB | MISC | MISS | PG17 | PG16 | PG15 | PG14 | PG13 | PG12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| RPM | 334 | 115 | 143 | 70 | 4 | 6 | 301 | 330 | 333 | 319 | 307 | 294 |
| DEB | 326 | 104 | 144 | 70 | 4 | 14 | 302 | 322 | 325 | 316 | 303 | 293 |
For each extension, I’ve built versions for the 6 major PostgreSQL versions (12–17) across five popular Linux distributions: EL8, EL9, Ubuntu 22.04, Ubuntu 24.04, and Debian 12. I’ve also provided some limited support for older OS versions like EL7, Debian 11, and Ubuntu 20.04.
This repository also addresses most of the alignment issue. Initially, there were extensions in the APT and YUM repos that were unique to each, but I’ve worked to port as many of these unique extensions to the other ecosystem. Now, only 7 APT extensions are missing from the YUM repo, and 16 extensions are missing in APT—just 6% of the total. Many missing PGDG extensions have also been resolved.
I’ve created a comprehensive directory listing all supported extensions, with detailed info, dependency installation instructions, and other important notes.
I hope this repository can serve as the ultimate solution to the frustration users face when extensions are difficult to find, compile, or install.
How to use this repo?
Now, for a quick plug — what’s the easiest way to install and use these extensions?
The simplest option is to use the OSS PostgreSQL distribution: Pigsty. The repo is autoconfigured by default, so all you need to do is declare them in the config inventory.
For example, the self-hosting Supabase config requires extensions that aren’t available in the PGDG repo. You can simply download, install, configure/preload, and create extensions by referring to their names.
To simply add extensions to existing clusters:
Although this repo is designed to be used with Pigsty, it is not mandatory. You can still enable this repository on any EL/Debian/Ubuntu system with a simple one-liner in the shell:
APT Repo
For the Debian 12 and Ubuntu 22.04/24.04 targets covered here, add the APT repository as follows. See the current repository reference for supported platforms and repository setup.
YUM Repo
For EL 8/9 and compatible platforms, use the following commands to add the YUM repository:
What’s in this repo?
The live catalog organizes extensions by category, platform, repository, language, license, and attributes. It started with categories such as TIME, GIS, RAG, FTS, OLAP, FEAT, LANG, TYPE, FUNC, ADMIN, STAT, SEC, FDW, SIM, and ETL, and continues to evolve as the extension ecosystem grows.
Check the Pigsty Extension Catalog for the current details.
Some Thoughts
Each major PostgreSQL version introduces changes, making the maintenance of 140+ extension packages a bit of a beast.
Especially when some extension authors haven’t updated their work in years. In these cases, you often have no choice but to take matters into your own hands. I’ve personally fixed several extensions and ensured they support the latest PostgreSQL major versions. For those authors I could reach, I’ve submitted numerous PRs and issues to keep things moving forward.
Back to the point: my goal with this repo is to establish a standard for PostgreSQL extension installation and distribution, solving the distribution challenges that have long troubled users.
A recent milestone is that, the popular open-source PostgreSQL HA cluster project postgresql_cluster, has made this extension repository the default upstream for PG extension installation.
Currently, this repository (repo.pigsty.io) is hosted on Cloudflare. In the past month, the repo and its mirrors have served about 300GB of downloads. Given that most extensions are just a few KB to a few MB, that amounts to nearly a million downloads per month. Since Cloudflare doesn’t charge for traffic, I can confidently commit to keeping this repository completely free and under active maintenance for the foreseeable future, as long as Cloudflare doesn’t charge me too much.
I believe my work can help PostgreSQL users worldwide and contribute to the thriving PostgreSQL ecosystem. I hope it proves useful to you as well. Enjoy PostgreSQL!
Originally published on vonng.com.