Database Scaling Techniques: A Deep Dive into Horizontal Partitioning

Horizontal data partitioning is a database design technique that divides a single large table into smaller, more manageable subsets based on its rows. In this setup, every single partition retains the exact same columns (schema) but holds a distinct subset of the records.

Partitioning vs. Sharding

While they rely on the same logical approach, there is a distinct boundary between the two terms:

Horizontal Partitioning: Splitting the rows into distinct logical groups within the same database instance.

Database Sharding: Distributing those horizontal slices across multiple independent physical servers or nodes.

Sharding is basically horizontal partitioning taken to distributed system levels. Like — different DB instances which have different CPU, RAM and other H/W level separation.

Different Approaches of Horizontal Partitioning

Assume the logical table is:

CREATE TABLE orders (
a    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    order_date DATE,
    amount NUMERIC(12,2),
    region VARCHAR(20)
);

Fig: Horizontal Data Partitioning

Approach #01: Internal Database Partitioning (PostgreSQL Native Partitioning)

One logical table, database manages partitions internally.

CREATE TABLE orders (
    order_id BIGINT,
    customer_id BIGINT,
    order_date DATE,
    amount NUMERIC(12,2),
    region VARCHAR(20)
) PARTITION BY LIST (region);

The partitions are as below-

CREATE TABLE orders_north
PARTITION OF orders
FOR VALUES IN ('North');

CREATE TABLE orders_south
PARTITION OF orders
FOR VALUES IN ('South');

CREATE TABLE orders_east
PARTITION OF orders
FOR VALUES IN ('East');

CREATE TABLE orders_west
PARTITION OF orders
FOR VALUES IN ('West');

Then if we insert the record using the following script -

INSERT INTO orders
VALUES (101,1,'2026-01-01',120,'North');

And then retrieve data using the below query -

SELECT * FROM orders;

Application never knows partitions exist. But, the performance will be improved.

Approach #02: Different Tables in Same Database

pos_db
 ├── orders_north
 ├── orders_south
 ├── orders_east
 └── orders_west

The scripts to create the table is -

CREATE TABLE orders_north (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    order_date DATE,
    amount NUMERIC(12,2),
    region VARCHAR(20)
);

CREATE TABLE orders_south (
    LIKE orders_north
);

CREATE TABLE orders_east (
    LIKE orders_north
);

CREATE TABLE orders_west (
    LIKE orders_north
);

The application routing:

if(region.equals("North")){
    insert into orders_north;
}

The generic query could be -

SELECT * FROM orders_north
UNION ALL
SELECT * FROM orders_south
UNION ALL
SELECT * FROM orders_east
UNION ALL
SELECT * FROM orders_west;

Approach #03: Different Schemas in Same Database

pos_db
 ├── north.orders
 ├── south.orders
 ├── east.orders
 └── west.orders

The schemas are as follows -

CREATE SCHEMA north;
CREATE SCHEMA south;
CREATE SCHEMA east;
CREATE SCHEMA west;

To create tables execute the following script -

CREATE TABLE north.orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    order_date DATE,
    amount NUMERIC(12,2),
    region VARCHAR(20)
);

CREATE TABLE south.orders (
    LIKE north.orders
);

CREATE TABLE east.orders (
    LIKE north.orders
);

CREATE TABLE west.orders (
    LIKE north.orders
);

Then insert one record —

INSERT INTO north.orders
VALUES (101,1,'2026-01-01',120,'North');

The global query could be -

SELECT * FROM north.orders
UNION ALL
SELECT * FROM south.orders
UNION ALL
SELECT * FROM east.orders
UNION ALL
SELECT * FROM west.orders;

This will retrieve all the records.

Approach #04: Different Databases on Same Server

PostgreSQL Server
 ├── orders_north_db
 ├── orders_south_db
 ├── orders_east_db
 └── orders_west_db

To create different DBs, execute queries like -

CREATE DATABASE orders_north_db;
CREATE DATABASE orders_south_db;
CREATE DATABASE orders_east_db;
CREATE DATABASE orders_west_db;

Within the each and every individual DB, it needs to create table like-

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    order_date DATE,
    amount NUMERIC(12,2),
    region VARCHAR(20)
);

Java application may route to-

northDataSource.getConnection()
southDataSource.getConnection()

No native SQL can query all databases together without FDW, dblink, etc.

Approach #05: Different Database Instances (Sharding)

Each infrastructure would be like-

Postgres Instance 1 → orders_north
Postgres Instance 2 → orders_south
Postgres Instance 3 → orders_east
Postgres Instance 4 → orders_west

Create table in each instance -

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    order_date DATE,
    amount NUMERIC(12,2),
    region VARCHAR(20)
);

Application shard routing:

switch(region){
   case "North" -> shard1;
   case "South" -> shard2;
   case "East"  -> shard3;
   case "West"  -> shard4;
}

To instantiate the shard-

DataSource shard = shardRouter.get(region);

This is what most distributed systems call sharding.

Approach #06: Different Physical Servers

The infrastructure could be managed as like-

Server-A → orders_north Server-B → orders_south Server-C → orders_east Server-D → orders_west

The same schema will be applied -

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    order_date DATE,
    amount NUMERIC(12,2),
    region VARCHAR(20)
);

To route, the application performs the followings-

Map<String,DataSource> regionServers;
regionServers.get(region)

Comparison

Fig: Comparison Report

For a large POS system, the progression is typically:

Fig: Progression

When Partitioning Effective?

On the following cases, the partioning is very helpful -

• Very large tables (millions–billions of rows).
• Time-series data (logs, transactions).
• Multi-region systems.
• High write throughput systems.

In sum, horizontal data partitioning is a database scaling technique where rows of a table are distributed across multiple partitions while keeping the same schema in each partition. It improves performance and scalability by reducing the amount of data scanned during queries. Common approaches include range, list, and hash partitioning, each suited to different data patterns and access needs.

It can be implemented within a single database instance or across multiple database servers in distributed systems. When designed well, it significantly improves throughput, but poor partitioning keys can lead to uneven load and degraded performance.