Question: How can you horizontally scale PostgreSQL?

Answer

Horizontal scaling, often referred to as scaling out, involves adding more machines or instances to your pool of resources to manage an increased load. It's a common strategy for databases that need to handle large volumes of data or high traffic while maintaining performance. For PostgreSQL, which is traditionally a single-server database system, horizontal scaling isn't built-in but can be achieved through several methods:

1. Replication

PostgreSQL supports replication by default, which allows the database operations to be mirrored from a primary server to one or more secondary servers. This can help in distributing read queries across multiple servers, thereby improving the read capacity. However, write operations still go through the primary server.

-- On the primary, enable replication in postgresql.conf
wal_level = replica
max_wal_senders = 3
hot_standby = on

-- Also, configure pg_hba.conf to allow connections from replicas

2. Partitioning

Partitioning splits large tables into smaller, more manageable pieces, while still allowing them to be queried together. PostgreSQL supports table partitioning natively. This can indirectly aid in horizontal scaling when combined with other techniques, as each partition can potentially be moved to separate physical hardware.

CREATE TABLE measurement (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2020 PARTITION OF measurement
    FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

3. Foreign Data Wrappers (FDW)

FDWs allow PostgreSQL databases to query external data sources as if they were local tables. This means you could distribute your data across multiple PostgreSQL instances and use FDWs to query them from a central instance.

-- Install the postgres_fdw extension
CREATE EXTENSION postgres_fdw;

-- Create a foreign server
CREATE SERVER remote_postgres
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'serverip', dbname 'remote_db');

-- Create a user mapping
CREATE USER MAPPING FOR local_user
SERVER remote_postgres
OPTIONS (user 'remote_user', password 'remote_pass');

4. Sharding

While not natively supported in core PostgreSQL, sharding can be implemented using third-party extensions like Citus. Citus extends PostgreSQL into a distributed database, allowing it to scale horizontally across many servers. Sharding involves breaking down your database into smaller chunks called shards, which can be spread across multiple servers.

-- After installing Citus and setting up your coordinator and worker nodes
SELECT create_distributed_table('my_table', 'my_distribution_column');

Each of these strategies has its own benefits and trade-offs. Replication is great for increasing read capacity but doesn't help with write scalability. Partitioning and FDWs don't provide true horizontal scaling but can improve manageability and performance for certain workloads. Sharding with Citus provides true horizontal scaling but introduces complexity and requires careful planning and management.

When considering horizontal scaling for PostgreSQL, think about your specific needs regarding read/write distribution, data size, and complexity you're prepared to manage.