PostgreSQL wire protocol

CrateDB supports the PostgreSQL wire protocol v3.0 (frontend/backend protocol), which enables many PostgreSQL-compatible tools and client libraries to connect seamlessly.

Note: PostgreSQL introduced protocol v3.2 in PostgreSQL 18, but CrateDB currently implements v3.0 for maximum compatibility.

By default, when PostgreSQL wire protocol support is enabled, CrateDB listens on port 5432. You can configure a different port in the Ports configuration.

While connection and communication are PostgreSQL-compatible, all SQL statements must be compatible with CrateDB’s SQL dialect. Notable differences include:

  • No transaction support — CrateDB automatically commits all operations. Clients should enable autocommit mode.

  • setFetchSize in JDBC — To use it, you can set autoCommit to false.

    • The client will then fetch up to the specified number of rows into memory for SELECT queries.

    • Write operations still auto-commit, and COMMIT/ROLLBACK calls are ignored.

    • For more details, see the PostgreSQL JDBC Query documentation.

1. Server Compatibility

  • CrateDB emulates PostgreSQL server version 14 for compatibility.

  • Some ParameterStatus messages (like server_version) may report PostgreSQL 9.5 for client compatibility.

2. Start-up

When using the PostgreSQL protocol:

  • Default port: 5432

  • Configurable via psql.port in the CrateDB settings

  • UTF-8 is the only supported character encoding

3. SSL Support

SSL/TLS encryption can be enabled. See Secured communications (SSL/TLS).

4. Authentication

Authentication is configured via Host-Based Authentication (HBA).

5. Parameter Status

After authentication, CrateDB sends:

  • Standard PostgreSQL parameters (e.g., server_version, server_encoding)

  • CrateDB-specific crate_version parameter (reports the CrateDB version number)

6. Database Selection

CrateDB uses schemas instead of databases. The database parameter in the connection string sets the default schema for the session (defaults to doc).

7. Query Modes

  • Simple Query Mode — Fully implemented.

  • Extended Query Mode — Supported with limitations:

    • ParameterDescription works for most cases except DDL

    • Bulk execution is optimized: statements are executed after receiving the Sync message

8. COPY Operations

  • The PostgreSQL COPY sub-protocol is not supported.

  • Use CrateDB’s COPY FROM/TO syntax for bulk data transfer.

9. Function Calls

The legacy PostgreSQL function call sub-protocol is not supported.

10. Canceling Requests

Cancel requests are fully supported.

11. pg_catalog and pg_type

  • A trimmed-down pg_catalog schema is implemented for compatibility with clients expecting PostgreSQL system tables.

  • The pg_type table supports necessary OIDs for common types, enabling clients to stream arrays and other complex types.

12. OID Types

  • Supported: oid, regproc, regclass, oidvector

  • Casting behavior differs from PostgreSQL:

    • Casting a string produces a hash-based OID

    • Casting an integer uses the integer as the OID

13. Transaction Isolation

  • SHOW TRANSACTION ISOLATION LEVEL returns read uncommitted for JDBC compatibility.

  • BEGIN/COMMIT statements are accepted but ignored.

14. BEGIN, START, and COMMIT

These are parsed for compatibility with libraries (e.g., pgx, lib/pq) but have no transactional effect.

15. Client Compatibility

  • JDBC: Compatible with pgjdbc version ≥ 9.4.1209

  • Some features require newer versions (e.g., OBJECT and GEO_SHAPE streaming)

16. Limitations

  • Reflection methods like conn.getMetaData().getTables(...) won’t work — use SHOW TABLES or information_schema queries.

  • Multidimensional arrays are streamed as JSON strings (no equal-length enforcement)

  • Transaction APIs exist but have no effect

  • JDBC escape syntax quirks may require disabling escape processing

17. Connection Failover & Load Balancing

Supported as described in PostgreSQL JDBC connection failover.

Note: Do not use targetServerType, as CrateDB has no master-replica distinction.

18. Implementation Differences

While the PostgreSQL wire protocol ensures compatibility with many tools, some PostgreSQL-specific SQL extensions are not available in CrateDB. CrateDB’s query engine is optimized for real-time analytics and OLAP workloads with horizontal scalability.

See also:

  • SQL compatibility

  • Clustering

19. Data Type Differences

  • Dates/Times: No TIME without time zone; INTERVAL missing some units

  • Objects: Use CrateDB’s OBJECT type instead of PostgreSQL JSON/composite types

  • Arrays: CrateDB supports ragged arrays

  • Text Search: PostgreSQL full-text search functions/operators are not compatible

If you are missing a feature or encounter a compatibility issue, please open a request on GitHub.

PreviousDriversNextHTTP SQL endpoint

Last updated