pg_partman tool for Postgres partitioning
pg_partman is a notable tool available for Postgres partitioning. Native partitioning was introduced in Postgres 10 and is generally thought to have better performance than pg_partman.
Supabase · Database · all subjects
database/partitions
15 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
Table partitioning overview
Table partitioning is a technique that allows you to divide a large table into smaller, more manageable parts called partitions. Each partition contains a subset of data based on specified criteria, such as a range of values or a specific condition.
Benefits of table partitioning
Table partitioning provides four main benefits: improved query performance by allowing queries to target specific partitions and reduce data scanned; scalability by enabling adding or removing partitions as data grows; efficient data management by simplifying data loading, archiving, and deletion on smaller partitions; and enhanced maintenance operations by optimizing vacuuming and indexing tasks.
Range partitioning
Range partitioning divides data into partitions based on a specified range of values. For example, a sales table can be partitioned by date, with each partition representing a specific time range such as one partition per month.
List partitioning
List partitioning divides data into partitions based on a specified list of values. For example, a customer table can be partitioned by region, with each partition containing customers from a specific region such as one partition for US customers and another for European customers.
Hash partitioning
Hash partitioning distributes data across partitions using a hash function. This method provides a way to evenly distribute data among partitions for load balancing. However, it does not allow direct querying based on specific values.
Partitioning column must be in constraints
When creating a partitioned table, the column that you are partitioning with must be included in any unique index. For range partitioning, this requires specifying a composite primary key that includes the partitioning column.
Creating range partitioned table syntax
To create a partitioned table using range partitioning, append `partition by range (<column_name>)` to the table creation statement. Example: `create table sales (id bigint generated by default as identity, order_date date not null, customer_id bigint, amount bigint, primary key (order_date, id)) partition by range (order_date);`
Creating range partition example
To create individual range partitions, use: `create table sales_2000_01 partition of sales for values from ('2000-01-01') to ('2000-02-01');` The partition receives data where the partitioning column falls within the specified range.
Querying parent partitioned table
When you query the parent table of a partitioned table, Postgres automatically routes the query to the relevant partitions based on the conditions in the query. This allows retrieving data from all partitions simultaneously. Example: `select * from sales where order_date >= '2000-01-01' and order_date < '2000-03-01';` retrieves data from both sales_2000_01 and sales_2000_02 partitions.
Querying specific partitions
If you only need data from a specific partition, you can directly query that partition instead of the parent table. Example: `select * from sales_2000_02;` retrieves data only from the sales_2000_02 partition.
When to partition tables guidelines
Partitions introduce complexity and should be avoided until needed. Guidelines: avoid partitions for performance until you see performance degradation on non-partitioned tables; if using partitions as a management tool, create them at any time; if unsure how to partition data, it is probably too early to implement partitioning.
List partitioning syntax example
To create a list partitioned table: `create table customers (id bigint generated by default as identity, name text, country text, primary key (country, id)) partition by list(country);` Then create partitions with: `create table customers_americas partition of customers for values in ('US', 'CANADA');`
Hash partitioning syntax example
To create a hash partitioned table: `create table products (id bigint generated by default as identity, name text, category text, price bigint) partition by hash (id);` Then create partitions with: `create table products_one partition of products for values with (modulus 2, remainder 1);`
Table partitioning for large deletes
If large deletes will occur regularly in the business cycle, consider using table partitioning as a management tool.