PGConf.EU 2025 Establishing the PostgreSQL standard What is Postgres compatible

From PostgreSQL wiki
Jump to navigationJump to search

Establishing the PostgreSQL Standard: What's Postgres compatible?

Website on PGConf.EU conference system

About

What does it mean to be "PostgreSQL compatible"? As PostgreSQL becomes "the new Linux" for the enterprise, this question becomes increasingly important. This project invites Postgres developers, contributors, and community members to define a practical framework of criteria and tests for PostgreSQL compatibility.

Summary of Proceedings (Riga Consensus)

Work has pivoted from a binary "Pass/Fail" certification to a granular compatibility matrix.

Compatibility Matrix

Blunt verdicts stifle innovation. The standard will use a weighted checklist (Core vs. Optional) to accommodate derivatives that are 95% compatible but differ on specific features.

"Managed" PG

The matrix allows for specific exceptions in "Managed" environments (e.g., restricted superuser or file-system access) without failing the entire suite.

No Silent Failures

Compatibility requires that SQL commands perform the expected function. A command like CREATE INDEX must actually build the index, not merely return "success" without action.

Discussion Topics

Behavior & Core Experience

What specific behavioral tests, run over a client connection, should be included?

Core SQL Compliance

Everything covered by Postgres documentation in "Part II: SQL Commands" is required. Even if rules are rarely used, a "compatible" system must support them.

Implicit Behaviors

We must test undocumented behaviors that users implicitly rely on.

  • Example: INSERT ... SELECT ... ORDER BY must respect the physical insertion order (critical for clustering/performance).

Transaction Isolation

Must support standard levels (Read Committed, Repeatable Read, Serializable) with identical behavior regarding race conditions and automatic deadlock detection.

Error Codes

Must return identical SQLSTATE error codes for common errors to ensure application-level error handling remains consistent.

System Catalogs

  • The pg_catalog must be present.
  • Standard monitoring tools must be able to connect and query catalogs without crashing, even if the engine does not populate every data point.

Connectivity

  • psql must report a standard version structure to prevent client warnings/errors.
  • Standard drivers (JDBC, psycopg) must work without modification.
  • pg_dump must be able to export schema from Community Postgres and import to the derivative.

Hard Limits

Derivatives must generally match Postgres hard limits (e.g., max columns). Significant reductions may deem a product incompatible.

Functionality

Feature Dependencies (Implied Support)

Triggers imply PL/pgSQL: While triggers can be written in C or SQL, the vast majority of the ecosystem relies on PL/pgSQL. Therefore, Trigger compatibility implies full PL/pgSQL language support.

Execution Plans vs. Results

  • Identical execution plans are not required (innovation in storage/planning is allowed).
  • However, functional results must match. For example, if Partitioning is supported, partition pruning logic must effectively reduce the scan, even if the specific plan node differs.

Indexing

Commands must perform the work. CREATE INDEX cannot be a "no-op" success.

Data Types

Support for specific types like ARRAY, BYTEA, and JSONB is required for Core compatibility.

Backups & Replication

Logical Replication

  • Must be bi-directional. (Create subscription/publication from Product X to PostgreSQL, and from PostgreSQL to Product X).
  • Must be monitorable via standard replication catalogs.
  • Vendor-specific extensions must not break the connection to a vanilla Postgres node.

Physical Replication

  • Must allow physical copying of binary files (or an equivalent export of the cluster's canonical state).
  • Must work In and Out. A vendor "Middleman" or proxy service is acceptable if the external interface complies.
  • Hybrid Clusters: Should support mixing Vanilla and Vendor nodes (e.g., promoting Vanilla master over Vendor replica).

Tools

Compatibility with standard tools (Patroni, pgbasebackup, RepMgr) is a key metric for the "Backup" compatibility tier.

Recovery

Point-In-Time Recovery (PITR) and access to WAL data are required.

The Test Harness

External Architecture

The test suite sits outside the PostgreSQL codebase.

Versioning

Test suite versions correspond numerically to Postgres versions but are independent entities.

Vendor Responsibility (The "Animal")

Vendors must provide a compatible build target (container/environment) for testing.

Bug-for-Bug

We are testing feature compliance, not bugs. If a derivative fixes a known Postgres bug, it should not fail the compatibility test.

De-prioritized Items (Future Work)

The following topics were discussed in previous sessions but were not included in the primary Riga Consensus or are pending further definition:

  • Support for the v3 client wire protocol, including all standard authentication methods (e.g., SCRAM, MD5, GSSAPI).
  • Compatibility with SSL/TLS and its various modes (prefer, require, verify-ca, etc.).
  • Level of compatibility required for configuration files and GUCs (e.g. postgresql.conf, pg_hba.conf).
  • Support for other standard procedural languages beyond PL/pgSQL (e.g. PL/Python, PL/Perl).
  • Specific behavior of indexing methods beyond creation (e.g. GIN, GiST, BRIN).
  • Support for specific time functions, such as SELECT now().
  • Standard for compatibility with Postgres extensions.
  • Compatibility with standard Foreign Data Wrappers (FDWs).
  • ABI (Application Binary Interface) stability checker tests.
  • Brand Identity: If a product satisfies all compatibility criteria but is not the vanilla C implementation, is it "Postgres" or "Postgres-compatible"?
Retrieved from "https://wiki.postgresql.org/index.php?title=PGConf.EU_2025_Establishing_the_PostgreSQL_standard_What_is_Postgres_compatible&oldid=42263"