Articles

Lessons Learned From 5 Years of Scaling PostgreSQL (onesignal.com)

Postgres scaling advice for 2021 - CYBERTEC | PostgreSQL (cybertec-postgresql.com)

Maybe you don't need Horizontal scaling. A tale of SINGLE NODE!

Scaling Preparations

Better Auto Vacuum

Declarative Partitioning

Foreign Data Wrapper

CREATE EXTENSION postgres_fdw;
CREATE SERVER box2 FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'box2', dbname 'box2db');
CREATE USER MAPPING FOR alice SERVER box2
    OPTIONS (user 'box2alice');
-- on box2
CREATE TABLE foo (id int, someLegacyUUID varchar(30), name varchar(255));

-- on your server
IMPORT FOREIGN SCHEMA public LIMIT TO (foo)
    FROM SERVER box2 INTO public;

-- Or add only specifc table with limited
CREATE FOREIGN TABLE IF NOT EXISTS <schema>.foo (
	someLegacyUUID uuid no null, -- here casted from varchar(30) to uuid
	name varchar(255)
) 
SERVER box2;

-- If you want to use a different name for the table, in your imported fdt, you can do that as well
-- also you don't need to type schema name if it is public
CREATE FOREIGN TABLE IF NOT EXISTS my_fdt_foo ( 
	...
)
SERVER box2
OPTIONS(table_name 'foo');

Combine Partitioning & Foreign Data Mapping

-- on box2 server
CREATE TABLE temperatures_2016 (
    at      date,
    city    text,
    mintemp integer,
    maxtemp integer
);
-- default OR your server
CREATE FOREIGN TABLE temperatures_2016
    PARTITION OF temperatures
    FOR VALUES FROM ('2016-01-01') TO ('2017-01-01')
    SERVER box2; -- This creates foreign table mapping
temp=# INSERT INTO temperatures (at, city, mintemp, maxtemp)
temp-#     VALUES ('2016-08-03', 'London', 63, 73);
INSERT 0 1
temp=# SELECT * FROM temperatures ORDER BY at;
     at     |  city  | mintemp | maxtemp
------------+--------+---------+---------
 2016-08-03 | London |      63 |      73
 2017-08-03 | London |      59 |      70
 2018-08-03 | London |      63 |      90
(3 rows)

temp=# SELECT * FROM temperatures_2016;
     at     |  city  | mintemp | maxtemp
------------+--------+---------+---------
 2016-08-03 | London |      63 |      73
(1 row)