PostgreSQL Compatibility Index: The Fellowship of the Database

In the mystical realm of databases, a new hero rises every few moons — a shiny, next-gen PostgreSQL derivative, boldly claiming to be “Postgres-compatible.” Like Frodo bearing the One Ring, these new contenders promise to carry us beyond the limits of vanilla Postgres into the promised lands of limitless scaling, AI integrations, and zero downtime magic.

But while we DBAs gaze at these marvels with starry-eyed wonder, QA teams hunker down in Helm’s Deep, bracing for the inevitable orc army of compatibility bugs. They’ve seen the promises before, and they know the truth: “compatible” is often just marketing speak for “we hope it mostly works.”

DBAs love the idea of embracing the future. We get excited when a new database claims to support distributed transactions, vector embeddings, and geospatial analytics while still being Postgres at heart. Imagine the scalability! The performance boosts! The bragging rights in Slack!

But QA? QA sees a Mordor of testing cycles, long nights, and a hundred Jira tickets labeled “Regression: Foreign Key not supported.”

The One Index to Rule Them All

Enter the PostgreSQL Compatibility Index (PCI): a tool to help both DBAs and QA teams navigate this epic saga. The PCI is like the Council of Elrond — everyone gets to know the facts, lay their cards on the table, and decide whether this new database is the Aragorn we need or just another Boromir, doomed to fail us at a critical moment.

Here’s how it works:

• PCI runs a battery of tests on the database in question, poking at every feature Postgres has to offer.
• Each feature is scored, depending on whether it works, or implodes spectacularly.
• The results are weighted, calculated, and distilled into a single PCI score, a percentage of compatibility perfection.

And just like Gandalf, PCI doesn’t pull punches. It will expose the Balrog-sized gaps in compatibility that marketing conveniently forgot to mention.

PCI Autotest in action, failures were induced on purpose to check scoring.

Improved reporting shows category and feature failed along with PCI Score.

For the Manual Wizards in the Realm of Compatibility

Not every compatibility journey requires the magic of automated tests — sometimes, a manual spellbook does the trick. The PostgreSQL Compatibility Index (PCI) also allows you to calculate scores manually by crafting a JSON input file with a fixed set of characteristics as defined in pci_calculator.py.

For instance, here’s how the PCI score was conjured for databases like CockroachDB, Amazon DSQL, and Yugabyte, using manually filled JSON files.

Below is an example of the input json for Yugabyte.

{
    "data_types": {
        "Primitive Types": "full",
        "Complex Types": "partial",
        "JSONB": "full",
        "Geospatial Types": "partial",
        "Custom Types": "full",
        "Full-Text Search": "full",
        "Vector": "no"
    },
    "ddl_features": {
        "Schemas": "full",
        "Sequences": "full",
        "Views": "full",
        "Materialized Views": "full"
    },
    "sql_features": {
        "CTEs": "full",
        "Upsert": "full",
        "Window Functions": "full",
        "Subqueries": "full"
    },
    "procedural_features": {
        "Stored Procedures": "full",
        "Functions": "full",
        "Triggers": "full"
    },
    "transaction_features": {
        "ACID Compliance": "full",
        "Isolation Levels": "full",
        "Nested Transactions": "no",
        "Row-Level Locking": "full"
    },
    "extensions": {
        "Extension Support": "partial",
        "Foreign Data Wrappers": "partial",
        "Custom Plugins": "partial"
    },
    "performance": {
        "Index Types": "partial",
        "Partitioning": "full",
        "Parallel Query Execution": "no"
    },
    "constraints": {
        "Foreign Key": "full",
        "Check": "full",
        "Not Null": "full",
        "Unique": "full",
        "Exclusion": "no"
    },
    "security": {
        "Role Management": "full",
        "GRANT/REVOKE Privileges": "full",
        "Row-Level Security": "full"
    },
    "replication": {
        "Streaming Replication": "full",
        "Logical Replication": "full"
    },
    "notifications": {
        "LISTEN/NOTIFY": "no",
        "Event Triggers": "no"
    },
    "miscellaneous": {
        "Temporary Tables": "full",
        "Monitoring and Statistics": "full"
    },
    "utilities": {
        "pg_dump": "full",
        "pg_stat_statements": "full",
        "pg_walinspect": "no",
        "amcheck": "full"
    },
    "penalty": {
        "superuser_restricted": "no",
        "transaction_limits": "no",
        "read_limits": "no"
    }
}

Why the manual mode? Because automating tests for every single characteristic using only SQL/PLpgSQL can feel like forging the One Ring — it’s a monumental task, especially when new-gen databases uses different nomenclatures or semantics for core features. For these situations, manual mode is your trusty sword and shield.

So the next time a database vendor claims “Postgres compatibility,” don’t just trust the marketing pitch. Run PCI, check the score, and then decide whether you’re leading your team to Gondor — or straight into Mordor.

Join the Quest: Open Source Awaits!

The code for the PostgreSQL Compatibility Index (PCI) is freely available under the MIT License, because the journey to compatibility should be a collaborative effort. Whether you’re inspired to add new features, improve existing ones, or simply sharpen the edges of the tool, your contributions are welcome!

Feel free to explore the repository and send a PR to help enhance PCI for the entire Postgres community:
PostgreSQL Compatibility Index on GitHub

May your queries be efficient, your indexes be optimized, and your compatibility claims… actually compatible.