DOCSEARCHbyAlgolia
Submit Docs

PgDog Documentation

website·Indexed 34 days ago

https://docs.pgdog.dev

Documentation for PgDog, a PostgreSQL connection pooler written in Rust. It features database mirroring, sharding (table and schema-based), multitenancy, and a rewrite engine for SQL queries. The Enterprise Edition includes a control plane for high availability, autoscaling, automatic database discovery, and quality of service (QoS) query prioritization.

Tokens
55.2K
Snippets
288
Records
447
Agent score
66%

What's inside PgDog

  1. Overview of PgDog capabilities

    PgDog is a high-performance tool written in Rust that acts as a transparent layer between applications and PostgreSQL databases. It provides three primary functions:

    1. Connection Pooling: Multiplexes thousands of application connections over a small number of server connections, supporting SET statements, LISTEN/NOTIFY, and advisory locks without breaking connection state.
    2. Load Balancing: Distributes read queries across read replicas using built-in algorithms, allowing read scaling without application changes or external infrastructure like HAProxy.
    3. Sharding: Enables horizontal scaling by splitting tables and indices across multiple machines, automatically routing queries and managing data migration via the PostgreSQL logical replication protocol.

  2. Overview of PgDog Connection Pooling

    PgDog is a multithreaded, asynchronous connection pooler built with the Tokio Rust runtime. It allows thousands of application connections to be proxied using only a small number of actual PostgreSQL server connections, which is critical for maintaining performance in large-scale production databases.

  3. Overview of PgDog Control Plane High Availability

    The PgDog control plane supports high availability by deploying multiple instances across different machines. It uses the Raft consensus algorithm to elect a leader instance that performs all actions and monitors connected PgDog instances. Standby instances remain reachable via HTTP and automatically forward API requests to the leader. If the leader fails, Raft promotes a standby instance to ensure minimal downtime.

  4. Overview of Sharded Sequences

    Sharded sequences provide a way to generate globally unique, monotonically increasing 64-bit integers for primary keys in sharded tables directly within the database. This approach avoids the large gaps or extremely large numbers associated with timestamp-based IDs.

    Note: This is an experimental feature. It is only compatible with sharded tables; omnisharded tables cannot use database sequences because they cannot guarantee the same number across all shards.

  5. Overview of Multitenancy strategies in PgDog

    PgDog supports two primary methods for separating tenant data:

    1. Physical Multitenancy: Separates data into multiple Postgres databases. This provides the highest level of security and workload isolation, as data from one tenant is physically isolated from others.
    2. Virtual Multitenancy: Separates data within a single Postgres database using either different schemas or specific table rows. This is a more cost-effective approach for smaller customers with lower isolation requirements.

  6. Overview of PgDog PostgreSQL Sharding

    PgDog provides a sharding layer that splits a PostgreSQL database (including tables and indices) across multiple independent servers. It acts as a query router, making the sharding transparent to the application, which continues to interact with the system as if it were a single regular PostgreSQL instance.

  7. Overview of the PgDog Enterprise Control Plane

    The PgDog Enterprise control plane is an application deployed alongside PgDog to synchronize administrative tasks across multi-node deployments. It is required for performing atomic configuration changes, toggling maintenance mode, and executing resharding operations. The control plane and PgDog processes communicate over HTTP(S) using a proprietary protocol to exchange metrics, commands, and metadata.

  8. Overview of PgDog sharding functions

    Sharding functions determine how PgDog routes SQL queries to one or more shard numbers. Once a shard number is determined, the query is sent to the corresponding database configured in pgdog.toml. PgDog supports two primary types of sharding: Column-based (using Postgres partition logic) and Schema-based (mapping schemas to shards).

  9. Overview of PgDog Control Plane Alerts

    The PgDog control plane monitors real-time metrics from all PgDog instances. When metrics exceed pre-configured thresholds, the control plane automatically creates an incident via a supported integration. Alerts are disabled by default.

  10. Handle abandoned transactions and queries with Connection Recovery

    PgDog prevents connection storms and database resource contention by attempting to preserve server connections when a client disconnects abruptly.

    • Abandoned Transactions: If a client disconnects during a transaction, PgDog automatically executes a ROLLBACK to ensure no partial changes are persisted.
    • Abandoned Queries: If a client disconnects while receiving query response data, PgDog drains the server connection by discarding DataRow messages and sending a Sync message to resynchronize the extended protocol state before returning the connection to the pool.

  11. Understand Omnisharded tables in PgDog

    Omnisharded tables (also known as mirrored or replicated tables) contain the same data on all shards. They are ideal for relatively static metadata used in joins or data that does not fit the database sharding schema, such as global settings, country lists, or blocked IP lists.

  12. Understand the PgDog schema synchronization process

    PgDog automates schema synchronization during resharding to ensure source and destination databases have compatible columns and data types using pg_dump. The process consists of four phases:

    1. Pre-data: Creates identical tables, primary key constraints, and unique indexes on all shards. Secondary indexes are not created yet to optimize data loading speed.
    2. Post-data: Creates secondary indexes on all tables and shards after data movement is complete.
    3. Cutover: Executed while application queries are blocked; restores constraints and moves sequence values.
    4. Post-cutover: Ensures the rollback database cluster can handle reverse logical replication.