A case for vertical scaling
If you have read any article or a book on system design then you probably know what vertical and horizontal scaling is and benefits of horizontal scaling. Before I explain how to setup proper horizontal scaling with Postgres let me make a case when you should not try this.
Simplicity: Single node database means you can run your database out of the box. Although I recommend you run PGTune for a quick preset or visit postconf a full breakdown
Easier backup and recovery: No need to think about state across replicas when creating backups or applying a backup.
No network overhead especially with write heavy operations.
A temporary fix: If need a fix right now, this will provide an instant relief.
Prerequisite
Make sure you have following tools installed.
Following the guide requires you have basic understanding of Kubernetes, CRD, Helm. Nothing deep a quick AI summary will suffice.
Replication
Replication means keeping multiple copies of data on multiple machines connected via network. Here is why you might want to do that:
- It keeps you data close to your users.
- It acts as a hot backup of a follower goes down.
- It helps with scaling if most of your workload is read operation (which is the case for most OLTP)
Here pg-pool acts as load balancer, it distributes read request evenly among followers and mutation request to the leader. Notice that Leader periodically syncs it WAL with it’s followers.
Setup StackGres and enable load balancer
minikube addons enable metallb
minikube tunnel
helm install stackgres-operator stackgres-charts/stackgres-operator\ --namespace stackgres-operator \
--create-namespace
Define CRD for replicated cluster
# replication.yaml
apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
name: cluster
spec:
instances: 3 # 1 primary + 2 replicas
postgres:
version: "15"
pods:
persistentVolume:
size: "1Gi"
profile: development
postgresServices:
primary:
type: LoadBalancer
replicas:
type: LoadBalancer
Apply the CRD
kubectl apply -f ./replication.yaml
kubectl get pods -w
Get credentials
PG_PASSWORD=$(kubectl -n default get secret cluster --template '{{ printf "%s" (index .data "superuser-password" | base64decode) }}')
echo "The superuser password is: $PG_PASSWORD"
See who is who
kubectl exec -it cluster-0 -c patroni -- patronictl list
Kill the primary
kubectl exec -it cluster-0 -c patroni -- patronictl list
See who is in charge now
Patroni should have elected a new leader by now.
kubectl exec -it cluster-1 -c patroni -- patronictl list
Tell something only to the primary
PRIMARY=$(kubectl exec -it cluster-1 -c patroni -- patronictl list | grep Leader | awk '{print $2}')
kubectl exec -it $PRIMARY -c patroni -- psql -U postgres -c "CREATE TABLE replication_test_table (id SERIAL PRIMARY KEY, data TEXT);"
kubectl exec -it $PRIMARY -c patroni -- psql -U postgres -c "INSERT INTO replication_test_table (data) VALUES ('Spread the word about our lord savior PostgreSQL!');"
Primary tell his followers
kubectl exec -it cluster-0 -c patroni -- psql -U postgres -c "SELECT * FROM replication_test_table;"
kubectl exec -it cluster-1 -c patroni -- psql -U postgres -c "SELECT * FROM replication_test_table;"
kubectl exec -it cluster-2 -c patroni -- psql -U postgres -c "SELECT * FROM replication_test_table;"
As you can see how quickly the word has spread. This is possible because StackGres uses Patroni under the hood to coordinate all the replication.
Partitioning
Partitioning splits the data (table in our case) into smaller, more manageable parts. This is done within a single database instance. Postgres supports this out of the box. It is defined in data definition layer and having multiple replicas for makes a partition highly available. It works best for time-series data, logs, or region-based segmentation.
Types of Partitioning
Range Partitioning – Data is partitioned based on value ranges (e.g., date ranges).
List Partitioning – Partitioning based on a list of values (e.g., regions or categories).
Hash Partitioning – Data is distributed using a hash function (e.g., MOD(user_id, 4)).
Following code create a table orders and derives three tables from it using range, list and hash based partition in a hierarchical way. Order table is split by year, year is further split into regions and region is finally split by hash.
Notice that only hash based partition grantees that all partition are of same size.
Setup StackGres and enable load balancer
helm install stackgres-operator stackgres-charts/stackgres-operator \
--namespace stackgres-operator \
--create-namespace
minikube addons enable metallb
minikube tunnel
Get credentials
PG_PASSWORD=$(kubectl -n default get secret cluster --template '{{ printf "%s" (index .data "superuser-password" | base64decode) }}')
echo "The superuser password is: $PG_PASSWORD"
Your database should now be available at postgresql://postgres::localhost:5432
Now open an SQL Editor like pgAdmin, and run the following.
-- Parent table
CREATE TABLE orders (
order_id INT,
customer_id INT,
order_date DATE,
region TEXT,
amount INT,
PRIMARY KEY (order_id, order_date, region, customer_id)
) PARTITION BY RANGE (order_date);
-- Range: Year 2024
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
PARTITION BY LIST (region);
-- Range: Year 2025
CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01')
PARTITION BY LIST (region);
-- 2024 - US region
CREATE TABLE orders_2024_us PARTITION OF orders_2024
FOR VALUES IN ('US')
PARTITION BY HASH (customer_id);
-- 2024 - EU region
CREATE TABLE orders_2024_eu PARTITION OF orders_2024
FOR VALUES IN ('EU')
PARTITION BY HASH (customer_id);
-- 2024 - US - Hash partitions
CREATE TABLE orders_2024_us_0 PARTITION OF orders_2024_us FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE orders_2024_us_1 PARTITION OF orders_2024_us FOR VALUES WITH (MODULUS 2, REMAINDER 1);
-- 2024 - EU - Hash partitions
CREATE TABLE orders_2024_eu_0 PARTITION OF orders_2024_eu FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE orders_2024_eu_1 PARTITION OF orders_2024_eu FOR VALUES WITH (MODULUS 2, REMAINDER 1);