Partitioning isthe act ofsplitting Posgres tables into multiple smaller tables,which allows to queryeach smaller table independently. This is useful and sometimes necessary when tables get so large thatquerying a single tablebecomes too slow. Partitioning requires detailed knowledge of the dataset and uses that knowledge to help Postgres executefasterqueries.

Partitioning is splitting Posgres tables into multiple smaller tables,with the intention of queryingeach smaller table independently. This is useful and sometimes necessary when tables get so large thataccessing thembecomes too slow. Partitioning requires detailed knowledge of the dataset and uses that knowledge to help Postgres execute queries faster.

Postgres supports three (3) kinds ofpartitioning schemes: by range, bylist, and byhash. Each scheme is appropriate for different use cases, and choosing the right scheme is important to get the best performance out of your data.

Postgres supports three (3) kinds ofpartition schemes: by range, byhash, and bylist. Each scheme is appropriate for different use cases, and choosing the right scheme is important to get the best performance out of your data.

Partition by range operates on numerical values. Dates, numbers and vectors can be used as range partition keys because their range of values can be split into non-overlapping parts.

For example, if we have a table with a date column (`TIMESTAMPTZ`, a date and time with timezone information), wecan create three (3) partitions with the following bounds:

For example, if we have a table with a date column (`TIMESTAMPTZ`, a date and time with timezone information), wecould create three (3) partitions with the following bounds:

* partition 1 will contain all dates prior to January 1, 2000,

* partition 2 will contain all dates between January 1, 2000 andDecember 31, 2020,

* partition 2 will contain all dates between January 1, 2000 andJanuary 1, 2021,

* partition 3 will contain all dates after January 1, 2021.

While these ranges are not even, we chose them because of some knowledge we have about our dataset. In our hypothetical example, we know that these date ranges will split our dataset into roughly three (3) evenly sized tables.

Let's build some real partitions with a dataset from Kaggle:[Hourly Energy Consumption](https://www.kaggle.com/datasets/robikscube/hourly-energy-consumption).

Youcan create a partition by rangein Postgreswith just a few queries. Partitioning requires two types of tables: the parent table which defines the partitioning scheme, and the child tables which define the ranges and store the actual data.

In Postgres, youcan create a partition by range with just a few queries. Partitioning requires creating two types of tables: the parent table which defines the partitioning scheme, and the child tables which define the ranges and store the actual data.

Let's start with the parent table:

Nicely done. The twotablesare pretty close to each other which creates a roughly even distribution of data in our partitioning scheme.

Nicely done. The twotable countsare pretty close, which creates a roughly even distribution of data in our partitioning scheme.

Postgres allows to query each partition individually, which is nice if we know what the range specification is. While this works in this example, in a living dataset, wecouldcontinueto add partitions to include more values. If we wanted to store dates for the years 2019 through 2023, for example, we would need to make at least one more child table.

Postgres allows to query each partition individually, which is nice if we know what the range specification is. While this works in this example, in a living dataset, wewould needcontinueadding partitions to include more values. If we wanted to store dates for the years 2019 through 2023, for example, we would need to make at least one more child table.

To make thisuserfriendly, Postgres allows us to query the parent table instead. As long as we specify the partition key, we are guaranteed to get the most efficient query plan possible:

To makereadingthisdata user-friendly, Postgres allows us to query the parent table instead. As long as we specify the partition key, we are guaranteed to get the most efficient query plan possible:

If we look at the query plan, we'll see that Postgres only queriesthe firstchildtable we created:

If we look at the query plan, we'll see that Postgres only queriesone of thechildtables we created:

which reduces the number of rowsit has to scan by half. By adding moresmallerpartitions, we can significantly reduce the amount of data Postgres needs to scan toexecute a query. That being said, scanning multiple tables could be more expensive than scanning just one table, so adding too many partitions quickly reduces its benefits if the queries need to scan more than a few child tables.

This reduces the number of rowsPostgres has to scan by half. By adding more partitions, we can significantly reduce the amount of data Postgres needs to scan toperform a query.

Partitioning by hash, unlike by range, can be applied to any data type, including text. A hash function is applied to the partition key to create a reasonably unique number, and that number is then divided by the number of partitions to find the right child table for the row.

Partitioning by hash, unlike by range, can be applied to any data type, including text. A hash function is executed on the partition key to create a reasonably unique number, and that number is then divided by the number of partitions to find the right child table for the row.

To create a table partitioned by hash, the syntax is similar to partition by range. Let's use the USA House Prices dataset we used in[Vectors](vectors.md)and[Tabular data](tabular-data.md), and split that table into two (2) roughly equal parts. Since we already have the`usa_house_prices` table, let's create a new one with the same columns, except this one will be partitioned:

CREATETABLEusa_house_prices_partitioned (

"Avg. Area Income"REALNOT NULL,

"Avg. Area House Age"REALNOT NULL,

"Avg. Area Number of Rooms"REALNOT NULL,

"Avg. Area Number of Bedrooms"REALNOT NULL,

"Area Population"REALNOT NULL,

"Price"REALNOT NULL,

) PARTITION BY HASH("Address");

Let's add two (2) partitions by hash. Hashing uses modulo arithmetic; when creating a child data table with these scheme, you need to specify the denominator and the remainder:

PARTITION OF usa_house_prices_partitioned

FORVALUES WITH (modulus2, remainder0);

PARTITION OF usa_house_prices_partitioned

FORVALUES WITH (modulus2, remainder1);

Importing data into the new table can be done with just one query:

INSERT INTO usa_house_prices_partitioned

SELECT*FROM usa_houses_prices;

Let's validate that our partitioning scheme worked:

Great! As expected, hashing split our dataset into roughly equal parts. To take advantage of this when reading data, you need to specify the partition key "Address" in every query. Postgres will hash the key using the same hashing function and query the child table that can contain the row with the "Address" value:

When discussing[Vectors](vectors.md), we mentioned that HNSW indexes slow down table inserts as the table grows over time. Partitioning is a great tool to help us scale vector indexes used for ANN search.

For this example, we'll be using a section of the[Amazon Reviews](https://cseweb.ucsd.edu/\~jmcauley/datasets.html#amazon\_reviews) dataset that we've embedded using the`intloat/e5-large` embeddings model. Our subset of the data contains 250,000 rows and two columns:

| Column| Data type| Example|

| ---------------------------| --------------| --------------------------------------------|

You can[download](https://static.postgresml.org/datasets/amazon\_reviews\_with\_embeddings.csv.gz) this dataset in CSV format from our CDN. To unzip it, install`pigz` and run:

unpigz amazon_reviews_with_embeddings.csv.gz

Let's get started by creating a partitioned table with three (3) child partitions. We'll be using hash partitioning on the`review_body` column which should produce three (3) roughly equally sized tables.

CREATETABLEamazon_reviews_with_embedding (

review_bodyTEXT,

review_embedding_e5_large VECTOR(1024)

) PARTITION BY HASH(review_body);

PARTITION OF amazon_reviews_with_embedding

FORVALUES WITH (modulus3, remainder0);

PARTITION OF amazon_reviews_with_embedding

FORVALUES WITH (modulus3, remainder1);

PARTITION OF amazon_reviews_with_embedding

FORVALUES WITH (modulus3, remainder2);

This creates a total of four (4) tables: one parent table defining the schema and three (3) child tables that will contain the review text and the embeddings vectors. To import data into the tables, you can use`COPY`:

Now that we've split our 250,000 vectors into three (3) tables, we can create an HNSW index on each partition independently. This allows us to shard the index into three (3) equally sized parts and because Postgres is a database server, we can do so in parallel.

If you're doing this with`psql`, open up three (3) terminal tabs, connect to your PostgresML database and create an index on each partition separately:

{% tabs %}

{% tab title="Tab 1" %}

SET maintenance_work_mem TO'2GB';

amazon_reviews_with_embedding_1

USING hnsw(review_embedding_e5_large vector_cosine_ops);

{% endtab %}

{% tab title="Tab 2" %}

SET maintenance_work_mem TO'2GB';

amazon_reviews_with_embedding_2

USING hnsw(review_embedding_e5_large vector_cosine_ops);

{% endtab %}

{% tab title="Tab 3" %}

SET maintenance_work_mem TO'2GB';

amazon_reviews_with_embedding_3

USING hnsw(review_embedding_e5_large vector_cosine_ops);

{% endtab %}

{% endtabs %}

This is an example of scaling vector search using partitions. We are increasing our indexing speed 3x because we can create HNSW indexes on separate tables in parallel. Since we have separate indexes for each partition, we are also reducing the size of the HNSW index by 3x, making sure that`INSERT` queries against the data remain sufficiently quick.

To perform an ANN search using the indexes we created, we don't have to do anything special. Postgres will automatically scan all three (3) indexes for the closest matches and combine them into one result:

review_body,

review_embedding_e5_large<=>pgml.embed(

'intfloat/e5-large',

)::vector(1024)AS cosine_distance

FROM amazon_reviews_with_embedding

ORDER BY cosine_distance

LIMIT9;

Since scanning HNSW indexes is very quick, we are okay with having to scan all indexes we created for every query. As of this writing,`pgvector` doesn't support partitioning its indexes because this requires splitting the graph in distinct sections. Work on this front will continue and we'll add support for sharding HNSW indexes in the future.

To validate that Postgres is using indexes, prepend`EXPLAIN` to the query. You should see three (3) index scans, one for each partition table.