*[Tabular data](data-storage-and-retrieval/tabular-data.md)

*[Vectors](data-storage-and-retrieval/vectors.md)

*[Partitioning](data-storage-and-retrieval/partitioning.md)

*[Documents](data-storage-and-retrieval/documents.md)

*[Deploying PostgresML](deploying-postgresml/README.md)

*[PostgresML Cloud](deploying-postgresml/postgresml-cloud/README.md)

*[Plans](deploying-postgresml/postgresml-cloud/plans/README.md)

WIP:[https://www.kaggle.com/datasets/rounakbanik/the-movies-dataset](https://www.kaggle.com/datasets/rounakbanik/the-movies-dataset)

Partitioning is the act of splitting Posgres tables into multiple smaller tables, which allows to query each smaller table independently. This is useful and sometimes necessary when tables get so large that querying a single table becomes too slow. Partitioning requires detailed knowledge of the dataset and uses that knowledge to help Postgres execute faster queries.

Postgres supports three (3) kinds of partitioning schemes: by range, by list, and by hash. 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), we can 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 and December 31, 2020,

* 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).

You can create a partition by range in Postgres with 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.

Let's start with the parent table:

CREATETABLEenergy_consumption (

"Datetime"TIMESTAMPTZ,

) PARTITION BY RANGE("Datetime");

Now, let's add a couple child tables:

PARTITION OF energy_consumption

FORVALUESFROM ('2004-01-01') TO ('2011-12-31');

PARTITION OF energy_consumption

FORVALUESFROM ('2011-12-31') TO ('2018-12-31');

Postgres partition bounds are defined as`[start, end)`, which means the start of the range is included and the end of the range is excluded.

Let's ingest the dataset into our partitioned table and see what we get:

We have a grand total of 121,273 rows. If we partitioned the dataset correctly, the two child tables should have roughly the same number of rows:

Nicely done. The two tables are pretty close to each other 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, we could continue to 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.

To make this 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:

avg("AEP_MW")

FROM energy_consumption

WHERE"Datetime" BETWEEN'2004-01-01'AND'2005-01-01';

If we look at the query plan, we'll see that Postgres only queries the first child table we created:

which reduces the number of rows it has to scan by half. By adding more smaller partitions, we can significantly reduce the amount of data Postgres needs to scan to execute 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.

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.

Once the dataset gets large enough, and we're talking millions of rows, it's no longer practical to query the table directly. The amount of data Postgres has to scan becomes large and queries become slow.To help withthat, tables should have indexes that order and organize commonly read columns. Searchinga B-Treeindex can be done in_O(log n)_ time, which is orders of magnitude faster than the_O(n)_ full tablesearch.

Once the dataset gets large enough, and we're talking millions of rows, it's no longer practical to query the table directly. The amount of data Postgres has to scan becomes large and queries become slow.Atthat point, tables should have indexes that order and organize commonly read columns. Searchinganindex can be done in_O(log n)_ time, which is orders of magnitude faster than the_O(n)_ full tablescan.

Postgres automatically uses indexes when its possible and optimal to do so. From our example, wecanfilter the dataset by the "Address" column,and we can do so very quickly because of the index we created:

Postgres automatically uses indexes when its possible and optimal to do so. From our example,ifwe filter the dataset by the "Address" column,Postgres will use the index we created and return a result quickly:

Since we have a unique index on the table, wewill only see one row with that address.

Since we have a unique index on the table, weexpect to see only one row with that address.

To double check that Postgres is using an index, we cancheckthe query execution plan. A query plan is a list of steps that Postgres will take to get thequeryresultwe requested. To see the query plan, prepend the keyword`EXPLAIN` to the query you're running:

To double check that Postgres is using an index, we cantake a look atthe query execution plan. A query plan is a list of steps that Postgres will take to get the resultof the query. To see the query plan, prepend the keyword`EXPLAIN` to the query you'd like to run:

The plan indicates it will use an "Index Scan" onthe index`usa_house_prices_Address_index` which is what we're expecting.

The plan indicatesthatit will use an "Index Scan" on`usa_house_prices_Address_index` which is what we're expecting. Using`EXPLAIN` doesn't actually run the query, so it's safe to use on production systems.

Using`EXPLAIN` doesn't actually run the query, so it's safe to use on production systems.

The ability to create indexes on datasets of any size, and to then efficiently query that data with those indexes is what separates Postgres from most ad-hoc tools like Pandas and Arrow. Postgres can store and query data that would never fit in memory, and it can do that quicker and more efficiently than most other database systems used in the industry.

The ability to create indexes on datasets of any size, and to efficiently query that data using indexes, is what separates Postgres from most ad-hoc tools like Pandas and Arrow. Postgres can store and query data that would never fit in memory, and it can do that quicker and more efficiently than most other database systems used in the industry.

Postgres indexes require no special maintenance. They are automatically updated when data is addedor removed. Postgres also ensures that indexes are efficiently organized and are ACID compliant. The database guarantees that the data is always consistent, no matter how many concurrent changes are made.

Postgres indexes require no special maintenance. They are automatically updated when data is addedand removed. Postgres also ensures that indexes are efficiently organized and are ACID compliant: the database guarantees that the data is always consistent, no matter how many concurrent changes are made.