Lessons Learned From 5 Years of Scaling PostgreSQL (onesignal.com)
Postgres scaling advice for 2021 - CYBERTEC | PostgreSQL (cybertec-postgresql.com)
Slice your data for autovacuum to work better! NO HUGE TABLES
UPDATE in postgresql is basical DELETE and INSERT.{
"big_column_with_large_data_less_updates": "",
"some_jsonb_large": {},
"id": "int",
"created_at": "Date",
"updated_at": "Date",
"some_frequently_updatd_column": ""
}
updated_at is changed or some other property, that is frequently changed, IT DOES NOT make sense to delete and insert the whole row again!// Table_D_1
{
"big_column_with_large_data_less_updates": "",
"some_jsonb_large": {},
"id": "int",
"created_at": "Date",
}
// Table_D_2 with 1-1 on Table_D_1
{
"updated_at": "Date",
"some_frequently_updatd_column": ""
}
If IO becomes a bottleneck, then you can rely upon "Postgres native remote partitions", so old data lives on another node!
One cool example of when to do partitioning is, tenant based partitioning 🤯
Another example is to move out the old / less frequently accessed data to a different table (LOCAL PARTITIONING)
CREATE TABLE temperatures (
at date,
city text,
mintemp integer,
maxtemp integer
);
CREATE TABLE temperatures_2019 (LIKE temperatures);
INSERT INTO temperatures_2019 SELECT * FROM temperatures WHERE
extract(year from at) = 2019;
DELETE FROM temperatures WHERE extract(year from at) = 2019;
CREATE TABLE temperatures (
at date,
city text,
mintemp integer,
maxtemp integer
)
PARTITION BY RANGE (at);
CREATE TABLE temperatures_2017
PARTITION OF temperatures
FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');
CREATE TABLE temperatures_2018
PARTITION OF temperatures
FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
temp=# INSERT INTO temperatures (at, city, mintemp, maxtemp)
temp-# VALUES ('2018-08-03', 'London', 63, 90);
INSERT 0 1
temp=# INSERT INTO temperatures (at, city, mintemp, maxtemp)
temp-# VALUES ('2017-08-03', 'London', 59, 70);
INSERT 0 1
temp=# SELECT * FROM temperatures;
at | city | mintemp | maxtemp
------------+--------+---------+---------
2017-08-03 | London | 59 | 70
2018-08-03 | London | 63 | 90
(2 rows)
temp=# SELECT * FROM temperatures_2017;
at | city | mintemp | maxtemp
------------+--------+---------+---------
2017-08-03 | London | 59 | 70
(1 row)
temp=# SELECT * FROM temperatures_2018;
at | city | mintemp | maxtemp
------------+--------+---------+---------
2018-08-03 | London | 63 | 90
(1 row)
CREATE TABLE temperatures_2017
PARTITION OF temperatures (
mintemp NOT NULL,
maxtemp NOT NULL,
CHECK (mintemp <= maxtemp),
PRIMARY KEY (at, city)
)
FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');
CREATE EXTENSION commandCREATE 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');
-- 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)