Scaling PostgreSQL 16 Horizontally

Horizontal scaling involves the strategic distribution of data across multiple servers, ensuring that data integrity, transaction safety, and query performance remain intact.

Read Replicas

A read replica typically refers to a “standby” server that redundantly duplicates all the data on a primary server, stays continuously up-to-date with the primary server, and allows clients to query (but not update) the data it contains. In Postgres world, this type of server is called a hot standby.

A PostgreSQL server normally starts up, accepts changes from clients via SQL commands, and generates a change feed in the form of a series of WAL (write ahead log) records. (These records then get checkpointed into actual row/column data.) A PostgreSQL standby server starts up and stays in what is known as recovery mode. In recovery mode, the Postgres server does not accept changes via SQL commands, but accepts only WAL records generated by another server as input. These WAL records are then replayed, and consequently the same changes that are happening on the primary server are recreated on the standby server.

The process of collecting newly generated WAL files, copying them over to a standby server, and placing them in the appropriate location on the standby’s data directory, is called log shipping in Postgres jargon. The most popular technique of log shipping is streaming replication.

$ psql -h /tmp -p 6001 postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.6001" failed: FATAL:  the database system is not accepting connections
DETAIL:  Hot standby mode is disabled.

But it stands to reason that read-only queries that do not update the data in any way may be allowed, and that is indeed what a hot standby allows. To make a regular standby into a hot standby, change this configuration and restart the Postgres server:

hot_standby = on

Clients can now connect to the hot standby server:

$ psql -h /tmp -p 6001 postgres
psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

There are however, some caveats about Postgres’ hot standby feature:

Standby servers replicate the entire data on the primary server – called physical replication in Postgres jargon. The “entire data” consists of all the databases living on the primary server. Physical replication is an all-or-nothing deal, there is no way to replicate only a subset of databases using this approach.
Temporary tables cannot be created – creation of tables, even temporary ones, require updation of system catalogs
Queries that require row locks and some other types of locks are not allowed
Prepared transactions are not allowed
Certain changes on the primary (like a vacuum) can lead to cancellation of query running on a hot standby (which needed the rows that just got vacuumed in the primary)

That said, physical replication feature of Postgres has been around for a long time now, is heavily used in production everywhere, is rock solid and well-documented. It is available in the standard PostgreSQL installation, no other extensions or tools required. It’s gotchas and their workarounds are only a web search away. A hot standby can serve not only as a read replica, but also as a failover candidate in a HA setup.

Multiple Read Replicas

Multiple read replicas are often used to get the data nearer to their place of use inorder to reduce application query latency. With PostgreSQL, you can have multiple standby servers for a single primary server, or cascade standbys from another standby.

There are no additional settings required for connecting multiple standbys to a primary, or for cascading another standby from an existing standby. Do check however, that the connection limits, wal sender process limits and the system resources are high enough to support multiple standbys.Read more.

Delayed Replication Having a replica that stays a few hours or even days behind the current primary database state can be an effective way to mitigate the results of disastrous queries (“oops I forgot the where clause to the delete when running on prod”). Lost or overwritten data can be manually queried out from a delayed replica, and suitable corrective actions can be taken.

Without a delayed replica, fixing such an issue would require restoring the latest backup, PITR-ing up to the time just before the command execution, then fetching data from that point. On the downside though, having a fully operational server just to recover from such mistakes maybe too expensive.

If you do need it however, it’s fairly simple to configure your standby to be a delayed replica:

# add this to your standby to make it lag behind the primary by 4 hours
recovery_min_apply_delay = '4h'

Replicating Only Some Tables

A publication can be setup to publish changes to all tables (the list of tables present at the time of creation of the publication, tables created afterwards are not automatically included), or a specified list of tables:

-- include all tables present at time of creation of publication
create publication pub2 for all tables;

-- include only specified list of tables
create publication pub3
    for table tbl3, tbl4, tbl5;

PostgreSQL v15 added the ability to specify all tables in one or more schemas too:

-- Postgres v15+ only
create publication pub4
    for tables in schema schema1, schema2;

The list of tables in a publication can be changed at a later time using ALTER PUBLICATION.

Replicating Only Some Rows of a Table

Postgres v15 also added the powerful capability of publishing only the selected rows of a table. You can now specify a row filter for a publication, and only the rows that satisfy this expression will be considered for replication. Read more about row filters here.

Row filter expressions cannot be too complicated (for example, it cannot include calls to stored functions), and are more like table-level CHECK constraints than query-level WHERE clauses in that regard. Here is an example that publishes only rows where a certain column’s value is even:

postgres=# create table tbl2 (a int primary key);
CREATE TABLE

postgres=# insert into tbl2 (a) select generate_series(1, 100);
INSERT 0 100

postgres=# create publication pub2 for table tbl2 where (a % 2 = 0);
CREATE PUBLICATION
On the subscription side, you can see that only half the rows are present:

postgres=# create table tbl2 (a int primary key);
CREATE TABLE

postgres=# create subscription sub2 connection 'host=/tmp port=6000 dbname=postgres' publication pub2;
NOTICE:  created replication slot "sub2" on publisher
CREATE SUBSCRIPTION

postgres=# select count(*) from tbl2;
 count
-------
    50
(1 row)

Replicating Only Some Columns of a Table

Starting with Postgres v15 again, it is possible to replicate only a subset of the columns of a table. Along with the table name, the list of columns also can be specified like this:

postgres=# create table tbl3 (a int primary key, b text, c text);
CREATE TABLE

postgres=# create publication pub3 for table tbl3 (a, c);
CREATE PUBLICATION

postgres=# insert into tbl3 (a, b, c) values (1, 'hello', 'world');
INSERT 0 1

On the subscription side, the table which receives the replicated data has only the subset of columns:

postgres=# create table tbl3 (a int primary key, c text);
CREATE TABLE

postgres=# create subscription sub3 connection 'host=/tmp port=6000 dbname=postgres' publication pub3;
NOTICE:  created replication slot "sub3" on publisher
CREATE SUBSCRIPTION

postgres=# select * from tbl3;
 a |   c
---+-------
 1 | world
(1 row)

The row filter and column list can be specified together for a publication, so as to replicate only a subset of rows and columns of a table. All that with just a couple of simple commands, and no external tooling!

Replicating Only Some Operations on a Table

Rows of a table can get inserted, updated, deleted and the table itself can be truncated. The publisher of a table can be configured to include only one or more of these 4 operations into the replication data.

For example, here is how to setup a publisher that replicates only inserts and updates to a table:

postgres=# create table tbl4 (a int primary key, b text);
CREATE TABLE

postgres=# create publication pub4 for table tbl4 with (publish = 'insert, update');
CREATE PUBLICATION

postgres=# select * from tbl4;
 a | b
---+---
(0 rows)

Now let’s setup and start the subscriber:

postgres=# create table tbl4 (a int primary key, b text);
CREATE TABLE

postgres=# create subscription sub4 connection 'host=/tmp port=6000 dbname=postgres' publication pub4;
NOTICE:  created replication slot "sub4" on publisher
CREATE SUBSCRIPTION

Then let’s insert, update and delete some rows on the publisher:

postgres=# select * from tbl4;
 a | b
---+---
(0 rows)

postgres=# insert into tbl4 (a, b) values (1, 'jello');
INSERT 0 1
postgres=# insert into tbl4 (a, b) values (2, 'world');
INSERT 0 1
postgres=# update tbl4 set b='hello' where a=1;
UPDATE 1
postgres=# delete from tbl4 where a=2;
DELETE 1
postgres=# select * from tbl4;
 a |   b
---+-------
 1 | hello
(1 row)

And see what happens on the subscriber:

postgres=# select * from tbl4;
 a |   b
---+-------
 2 | world
 1 | hello
(2 rows)

The subscriber’s table contains the row that was deleted from the publisher, since the delete operations were not included in the changes sent across.

This also illustrates why improper use of logical replication can break transaction atomicity: if a row was created and then deleted within a single transaction on the publisher side, the subscriber will still end up with a row that was never seen by any other transaction on the publisher side.

Note that initially when the subscription is started, all rows from the source are copied over to the destination. Further replication is subject to the restrictions specified.

Muti Master and Bi-directional Replication

The terms “multi-master”, “multi-writer” and “bi-directional replication” refer to a deployment with two or more linked servers, any of which can consistently update a shared set of databases. A client application would be able to perform updates from any server, and the changes would be replicated to other servers, while maintaining consistency and integrity of the entire data.

As such, there is no “multi-master” mode of replication in core PostgreSQL. There are third-party extensions and tools that implement generic multi-master replication, but they are not as widely available or popular as PostgreSQL itself.

Logical replication can go a long way to implementing, in a schema-specific way, the goals you want to realize using a generic multi-master solution. Publications and subscriptions can be setup on any stock PostgreSQL server, and replication in any direction can be achieved easily. Conflict resolution must be handled at schema design and application level, as opposed to any features provided by a generic multi-master solution.

Synchronous Replication

By default, when a transaction is committed in PostgreSQL, it writes out the required WAL records and waits for the flush of this data to the disk. When the server is a primary replicating to one or more standby servers, this behavior can be further tightened to reduce the risk of data loss even more. This can be achieved by configuring the settings synchronous_commit and synchronous_standby_names.

The value of remote_write for synchronous_commit makes Postgres wait for the standby to write the WAL records to its disk before replying to the application that the commit has completed. The application_name of the standby that has to receive the WAL records is specified with synchronous_standby_names, like this:

synchronous_commit = remote_write
synchronous_standby_names = mystandby1

A remote_write does not necessarily mean that a query started on the standby right after the commit completes can see the changes contained in that commit. If such a guarantee is needed, use a value of remote_apply for synchronous_commit:

synchronous_commit = remote_apply
synchronous_standby_names = mystandby1

It is even possible to setup a quorum commit, that is to say, at least k of n nodes must have received the WAL records for the primary’s commit to succeed:

synchronous_commit = remote_write
synchronous_standby_names = ANY 2 (mystandby1, mystandby2, mystandby3)

What is probably most interesting about synchronous_commit is the ability to specify it at the transaction level rather than as a global setting. Enabling it for only certain transactions, like for example one which updates account balances, brings extra safety for operations when required.

BEGIN;
SET synchronous_commit = 'remote_write';
-- use the global setting for synchronous_standby_names
-- update data here
COMMIT;

Accessing Tables Across Servers Not all data distributed across servers may need to be replicated or updated in real time. Data might be mostly static, for reference, lookup, or historical and so on. Such data can be accessed from main OLTP/OLAP servers using foreign data wrappers (FDW).

FDWs allow you to work with “foreign data”, which may reside anywhere external to the Postgres server. The ability to work with data from another Postgres server is implemented by postgres_fdw, an extension available in core PostgreSQL.

Postgres FDW

Here is how Postgres FDW looks in practice. Assume there is a source database with a table in it, like this:

srcdb=# create table srct (a int primary key);
CREATE TABLE

srcdb=# insert into srct (a) select generate_series(1, 100);
INSERT 0 100

At a destination server, you can setup a foreign table srct, which acts a proxy table for the actual srct table that lives in our source database:

destdb=# create extension postgres_fdw;
CREATE EXTENSION

destdb=# create server src foreign data wrapper postgres_fdw options (host '/tmp', port '6000', dbname 'srcdb');
CREATE SERVER

destdb=# create user mapping for current_user server src;
CREATE USER MAPPING

destdb=# import foreign schema public limit to (srct) from server src into public;
IMPORT FOREIGN SCHEMA

destdb=# select count(*) from srct;
 count
-------
   100
(1 row)

The foreign table takes up no space and contains no data – it simply serves as a placeholder to refer to the actual table living elsewhere. The destination Postgres server’s postgres_fdw extension will establish and maintain a connection to the source Postgres server, translating every query involving the foreign table into appropriate network calls.

The foreign table can work seamless with regular local tables, like in this join:

destdb=# create table destt (b int primary key, c text);
CREATE TABLE

destdb=# insert into destt (b,c) values (10,'foo'), (20,'bar');
INSERT 0 2

destdb=# select a,b,c from srct join destt on srct.a = destt.b;
 a  | b  |  c
----+----+-----
 10 | 10 | foo
 20 | 20 | bar
(2 rows)

The main work of the FDW is to push down the work to the remote server as much as possible and to minimize the amount of data sent back and forth between the two servers. For example, you would want the remote server to process a LIMIT clause, rather than fetching all the rows over then applying a LIMIT clause locally. However, given the complexity of SQL as well as PostgreSQL’s query planner and executor, this is no easy task. The efficiency keeps improving each version, but some queries might take a little too much time or work_mem that you would expect.

Materialized Views + Foreign Data Wrappers

Depending upon your use case, combining materialized views with FDW can offer a reasonable balance between replicating a full table and having it totally remote (foreign). The materialized view can effectively function as a local cache, which in turn can be used along with local-only tables for local-grade performance.

destdb=# create materialized view destmv as select a,b,c from srct join destt on srct.a = destt.b;
SELECT 2

destdb=# select * from destmv;
 a  | b  |  c
----+----+-----
 10 | 10 | foo
 20 | 20 | bar
(2 rows)

The “cache” can be refreshed anytime, peridocally or otherwise, with a regular “REFRESH MATERIALIZED VIEW” command. As a bonus, (local) indexes can be defined on the view to further speed up queries.

Distribute Rows of a Table Across Servers Sharding of rows of a single table across multiple servers while presenting the unified interface of a regular table to SQL clients is perhaps the most sought-after solution to handling big tables. This approach keeps the applications simpler, and keeps DBAs working harder!

Splitting tables into parts so that queries and work with only relevant rows, hopefully in parallel, is the core principle behind sharding. PostgreSQL v10 introduced the partitioning feature, which has since then seen many improvements and wide adoption. Read more about partitioning here.

Vertically scaling using paritioning involves creating partitions in different tablespaces (on different disks). Horizontal scaling involves combining partitioning and FDW.

Partitioning + FDW

Going with the example from the Postgres documentation, let’s create the partition root table measurement having one local partition table and one foreign partition table:

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

destdb=# CREATE TABLE measurement_y2023
PARTITION OF measurement
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE

destdb=# CREATE FOREIGN TABLE measurement_y2022
PARTITION OF measurement
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01')
SERVER src;
CREATE FOREIGN TABLE

The foreign table is only a proxy, so the actual table itself must be present on the foreign server:

srcdb=# CREATE TABLE measurement_y2022 (
    city_id         int not null,
    logdate         date not null
        CHECK (logdate >= '2022-01-01' and logdate <= '2023-01-01'),
    peaktemp        int,
    unitsales       int
);
CREATE TABLE

We can now insert rows into the root table and have it routed to the appropriate partition. You can see that the SELECT query performs both a local scan and a foreign scan and combines the results.

destdb=# insert into measurement (city_id, logdate, peaktemp, unitsales)
values (1, '2022-01-03', 66, 100), (1, '2023-01-03', 67, 300);
INSERT 0 2

destdb=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2022-01-03 |       66 |       100
       1 | 2023-01-03 |       67 |       300
(2 rows)

destdb=# explain select * from measurement;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Append  (cost=100.00..219.43 rows=3898 width=16)
   ->  Foreign Scan on measurement_y2022 measurement_1  (cost=100.00..171.44 rows=2048 width=16)
   ->  Seq Scan on measurement_y2023 measurement_2  (cost=0.00..28.50 rows=1850 width=16)
(3 rows)

However, both partitioning and foreign tables still have implementation limitations in PostgreSQL, which means that this setup works satisfactorily only for simple tables and basic queries.

Scaling PostgreSQL 16 Horizontally

Read Replicas

Multiple Read Replicas

Replicating Only Some Tables

Replicating Only Some Rows of a Table

Replicating Only Some Columns of a Table

Replicating Only Some Operations on a Table

Muti Master and Bi-directional Replication

Synchronous Replication

Postgres FDW

Materialized Views + Foreign Data Wrappers

Partitioning + FDW

Ref:

Related Posts

PostgreSQL extension - PgVector

Free EBook - Designing Data-Intensive Applications

PostgreSQL 17.1 released today

Free EBook - Apache Iceberg The Definitive Guide