PostgreSQL

Usage Notes

• Recommended for single-region deployments
• Postgres 15 or newer is required for optimal performance
• Resiliency to failures only when PostgreSQL is operating with a follower and proper failover
• Carries setup and operational complexity of running PostgreSQL
• Does not rely on any non-standard PostgreSQL extensions
• Compatible with managed PostgreSQL services (e.g. AWS RDS)
• Can be scaled out on read workloads using read replicas

Developer Notes

• Code can be found here (opens in a new tab)
• Documentation can be found here (opens in a new tab)
• Implemented using pgx (opens in a new tab) for a SQL driver and connection pooling
• Stores migration revisions using the same strategy as Alembic (opens in a new tab)
• Implements its own MVCC (opens in a new tab) model by storing its data with transaction IDs

Read Replicas

SpiceDB supports Postgres read replicas and does it while retaining consistency guarantees. Typical use cases are:

• scale read workloads/offload reads from the primary
• deploy SpiceDB in other regions with primarily read workloads

Read replicas are typically configured with asynchronous replication, which involves replication lag. That would be problematic to SpiceDB's ability to solve the new enemy problem but it addresses the challenge by checking if a revision has been replicated into the target replica. If missing, it will fall back to the primary.

All API consistency options will leverage replicas, but the ones that benefit the most are those that involve some level of staleness as it increases the odds a revision has replicated. minimize_latency, at_least_as_fresh, and at_exact_snapshot consistency modes have the highest chance of being redirected to a replica.

SpiceDB supports Postgres replicas behind a load-balancer, and/or individually listing replica hosts. When multiple URIs are provided, they will be queried using a round-robin strategy.
Please note that the maximum number of replica URIs to list is 16.

Read replicas are configured with the --datastore-read-replica-* family of flags.

SpiceDB supports PgBouncer (opens in a new tab) connection pooler and is part of the test suite.

Transaction IDs and MVCC

The Postgres implementation of SpiceDB's internal MVCC mechanism involves storing the internal transaction ID count associated with a given transaction in the rows written in that transaction. Because this counter is instance-specific, there are ways in which the data in the datastore can become desynced with that internal counter. Two concrete examples are the use of pg_dump and pg_restore to transfer data between an old instance and a new instance and setting up logical replication between a previously-existing instance and a newly-created instance.

If you encounter this, SpiceDB can behave as though there is no schema written, because the data (including the schema) is associated with a future transaction ID and therefore isn't "visible" to Spicedb. If you run into this issue, the fix is documented here (opens in a new tab)

Configuration

Required Parameters

Optional Parameters