Tag: sharding

If you’re running PostgreSQL at scale, sharding and high availability have traditionally meant invasive changes—rewriting application queries, introducing multiple proxies, or managing fragile logical replication setups. PgDog aims to shift this paradigm: it lets you scale Postgres horizontally, load-balance, and shard databases with minimal operational friction and without forcing large-scale rewrites. With PgDog confirmed in production and powering millions of queries per second, it’s essential to understand how it actually works, what its current limitations are, and how you can leverage it for real-world scaling needs.

Key Takeaways:

• PgDog delivers horizontal scaling for PostgreSQL with minimal application changes—queries must include a sharding key for optimal routing
• Combines connection pooling, load balancing, and sharding in a single, multi-threaded proxy
• Direct-to-shard queries are robust; cross-shard query support is partial and actively evolving
• Automatic failover and SQL parsing are built-in, with production deployments confirmed
• Understanding query routing and sharding key requirements is critical for success

Why PgDog Matters Now

PostgreSQL’s vertical scaling limitations become apparent quickly for SaaS, analytics, and multi-tenant workloads. Traditional sharding solutions—like Citus or homegrown proxies—frequently require disruptive schema changes or custom routing logic inside every service. PgDog (official documentation) takes a different approach: it acts as a proxy layer that unifies connection pooling, load balancing, and sharding, so your application can scale horizontally with less operational complexity.

Key developments that make PgDog relevant right now:

• PgDog is in production at dozens of companies, handling millions of queries per second (Show HN update)
• Direct-to-shard queries are proven and reliable; cross-shard query support is present for some operations but remains an area of active improvement
• Teams use PgDog to replace older single-threaded poolers (such as PgBouncer) both in the cloud and on-premises

For teams that need to scale Postgres horizontally without the risk and downtime of a schema rewrite, PgDog offers a pragmatic solution: scale out Postgres, using standard SQL, with minimal disruption. You do need to ensure your queries include a sharding key for proper routing, but no architectural overhaul is required.

If you’re interested in how modern developer tools are changing infrastructure, our analysis of next-gen JavaScript toolchains covers similar trends.

Getting Started with PgDog

PgDog is packaged as a single executable and can be deployed via Helm or Docker—making it straightforward for cloud-native environments and modern CI/CD pipelines.

Installation Example: Helm

helm repo add pgdogdev https://helm.pgdog.dev
helm install pgdog pgdogdev/pgdog

This will deploy PgDog inside your Kubernetes cluster, exposing a single endpoint for PostgreSQL traffic. For Docker or other deployment models, consult the official docs.

How PgDog Integrates with Your Stack

PgDog sits between your application and your Postgres cluster, providing:

• Connection pooling: reduces overhead by managing backend connections efficiently
• Load balancing: distributes traffic across replicas at the transaction level
• Query routing: parses SQL to route reads and writes to the correct shard or replica

You configure your application to point to the PgDog endpoint, not directly to Postgres. Minimal application changes are needed—however, your queries should include the sharding key for proper routing.

Configuration Overview

Configuration is managed via YAML or environment variables. The official documentation does not provide a sample YAML schema in the research sources, so configuration details may vary. Refer to the official documentation for the most current configuration parameters and structure.

Sharding, Routing, and Cross-Shard Queries

PgDog’s core strength is routing queries to the correct shard or replica—reducing the need for custom query logic or application-level fan-out. Here’s how it works based on the primary documentation and real-world reports.

Direct-to-Shard Queries

In most multi-tenant applications, a sharding key (such as tenant_id) is present in every query. PgDog’s SQL parser, derived from the Postgres source code itself, inspects your queries and routes them to the appropriate database:

-- Direct-to-shard query, routed by tenant_id
SELECT * FROM accounts
WHERE tenant_id = $1;

This ensures that the query only touches the relevant shard, with no changes needed to your ORM or SQL generators—as long as the sharding key is included.

Cross-Shard (Multi-Database) Queries

PgDog supports certain types of cross-shard operations, such as multi-tuple inserts, aggregates, and sorts. For example, a single insert statement can target multiple shards:

-- Cross-shard insert statement
INSERT INTO users (id, tenant_id, email)
VALUES ($1, $2, $3),
       ($4, $5, $6);

PgDog’s query engine interprets and executes these inserts across the appropriate databases (source). However, cross-shard (multi-database) queries remain an evolving feature—while support exists for some aggregates, sorts, and inserts, not all complex joins or operations are currently supported (Show HN update).

Distributed SQL Capabilities

• Aggregates and sorting across shards are supported for some queries
• Sharding key updates (moving rows between shards) are supported
• Two-phase transactions enable distributed transaction safety
• Replicated tables can be used for reference data across shards

Developers should note that cross-shard support is partial and under active development; not every cross-shard use case will work out-of-the-box.

Failover and Replication

PgDog automatically detects replica lag, hardware faults, and primary failover events, rerouting traffic as needed to maintain high availability. Logical replication is supported for online scaling and migration, allowing for cluster growth without taking the application offline (source).

PgDog vs. PgBouncer vs. Citus: Feature Comparison

Production Lessons and Advanced Features

With PgDog widely deployed, several practical lessons have emerged:

• Teams have successfully scaled from single-node to multi-shard clusters without downtime or major app changes—provided all queries use the sharding key
• Operational complexity is reduced by consolidating pooling, load balancing, and sharding in one binary
• The SQL parser’s Postgres heritage means better compatibility than most proxies, but not all advanced SQL is supported
• Logical replication and online resharding enable non-disruptive cluster growth

Real-World Deployment Example

# Example: Deploying PgDog as a sidecar in Kubernetes (notional)
apiVersion: v1
kind: Pod
metadata:
  name: myapp
spec:
  containers:
    - name: myapp
      image: myorg/myapp:latest
      env:
        - name: DATABASE_URL
          value: "postgres://pgdog:5432/mydb"
    - name: pgdog
      image: pgdogdev/pgdog:latest
      ports:
        - containerPort: 5432

This is a notional example only. Consult the official documentation for supported deployment patterns and configuration details.

Best Practices for Sharding Keys

• Ensure every query includes the sharding key (e.g., tenant_id) for efficient routing
• Replicate reference tables across all shards if needed
• Monitor cross-shard query usage, and optimize hot paths for direct-to-shard queries

Scaling and Failover Operations

• Use PgDog’s logical replication to add new shards online
• Test failover scenarios in staging—PgDog will reroute traffic automatically on failover events
• Adjust pool sizes and replica counts based on observed workload and performance metrics

Common Pitfalls or Pro Tips

Adopting a new scaling proxy introduces new operational considerations. Experienced teams report:

• Missing Sharding Keys: Queries that lack a sharding key may be broadcast or fail—always include the key in WHERE clauses
• Unsupported SQL Syntax: PgDog’s parser is robust, but some advanced or vendor-specific SQL may not be supported—test critical queries thoroughly
• Cross-Shard Query Limitations: Complex joins and aggregations across shards are not fully supported—plan schema and access patterns accordingly
• Monitoring and Logging: Enable query logging and metrics early to detect misrouted or slow queries before they impact users
• Failover Validation: Validate failover behavior in a staging environment before relying on it in production

Pro tip: Treat PgDog as an accelerator for scaling PostgreSQL, but not a universal solution to every scaling challenge. For best results, design workloads to maximize direct-to-shard query patterns and minimize reliance on distributed cross-shard operations.

Conclusion & Next Steps

PgDog is redefining how teams scale PostgreSQL—removing friction and reducing the need for disruptive application rewrites. Its open-source nature, native SQL parsing, and proven production usage make it a serious contender for SaaS, analytics, and multi-tenant platforms pushing the limits of vertical scaling.

If you’re starting a Postgres scaling project, focus on:

• Reviewing the official documentation and guides for up-to-date configuration and deployment
• Testing direct-to-shard and cross-shard queries in a staging environment before production rollout
• Validating failover and setting up monitoring tailored to your workload

For more on modern infrastructure evolution, see our coverage of the Oxc JavaScript compiler and the Ladybird browser’s Rust adoption.

As PgDog’s cross-shard query capabilities mature, watch for more production stories, ecosystem integrations, and detailed migration guides—this space is moving fast.