Scaling strategies

Introduction

Scaling strategies in PostgreSQL are methods that help the database handle larger volumes of data, support high user concurrency and maintain high performance. These strategies are divided into the following main groups:

In-Database Scaling

First, you should choose Data & Structure Optimization solutions right within the Database, which saves resources and avoids incurring costs associated with upgrading hardware or purchasing additional servers.

• Table Partitioning
  • Divide a massive table (hundreds of millions of rows) into smaller tables by Range, List, or Hash.
  • Example: Divide the orders table by month. Postgres only needs to scan the exact partition containing the required data (Partition Pruning).
• Indexing: Use the correct index types such as B-Tree (default), GIN (for JSONB/Full-text search) and BRIN (for large time-series data) to accelerate query speeds.
• Vacuuming: Configure optimized Autovacuum to clean up Dead Tuples, avoiding Table Bloat that inflates disk space usage and slows down queries.

Vertical Scaling (Scale Up)

• This is the simplest approach and usually the first step when PostgreSQL becomes overloaded.
• This method is executed by upgrading hardware such as:
  • Increasing the number of CPUs and RAM capacity
  • Switching to NVMe/SSD drives with high IOPS (Input/Output Operations Per Second).
    • Because actual data ultimately must be read from and written to disk
    • If the drive has low IOPS, CPU and RAM no matter how powerful will have to wait for the disk to complete processing (this phenomenon is called High I/O Wait).
    • Using NVMe/SSD drives helps release this bottleneck, helping Postgres process large volumes of queries simultaneously without slowing down.
• Optimize configuration parameters in the postgresql.conf file:
  • shared_buffers: Increase data cache memory, typically set to ~25% of total RAM
  • work_mem: Increase memory for SORT and JOIN operations
  • effective_cache_size: Helps the Query Planner estimate operating system cache size more accurately.
• Advantages: Requires no application source code changes and is easy to deploy.
• Disadvantages:
  • Physical Limit: Depending on the Mainboard, a computer will always have limits on usable RAM and CPU
  • Non-linear Cost:
    • The price of high-end server components does not increase proportionally (linearly), but rather follows an exponential curve
    • If you use a machine with 16 vCPUs and 64GB RAM and want to double it each time, the cost increases exponentially higher.

Horizontal Scaling (Scale Out)

When a single server reaches its hardware limits, you need to distribute the load across multiple servers.

• Read Replicas: Separate Read / Write
  • Use Streaming Replication to copy data from Primary/Master Nodes to Standby/Replicas Nodes
  • Combine with high availability management toolsets like Patroni, etcd and Haproxy
  • All Write operations (INSERT, UPDATE, DELETE) are sent to the Primary, while Read operations (SELECT) are load-balanced across Read Replicas.
• Sharding: Row-based data distribution
  • Divide large table data into shards residing on completely independent servers.
  • The most common way to create Distributed Postgres is using the Citus Extension, which automatically shards tables based on a distribution_key (for example: tenant_id or user_id).
  • Application-level Sharding means the application manages query routing logic to each separate database.
• Connection Pooling: Connection management
  • Postgres uses a process-per-connection model, consuming significant RAM when thousands of open connections exist.
  • Use middleware such as PgBouncer, pgCat or Suparvisor to create connection pools, helping Postgres process high concurrent requests efficiently.

Separating Analytical Workloads

• This is an infrastructure optimization solution when the system requires running reports and analytics queries on massive data volumes that, if run on the operational Database, would slow down daily transactional operations.
• Use Logical Replication / CDC (Change Data Capture) to push data from Postgres to specialized OLAP Databases like ClickHouse or Snowflake using Debezium/Kafka.

Happy coding!

See more articles here.