Creating partitioned tables in PostgreSQL - Mechanics & Internals

Overview - Creating partitioned tables

What is it?

Creating partitioned tables means dividing a large table into smaller, manageable pieces called partitions. Each partition holds a subset of the data based on a specific rule, like ranges or lists of values. This helps the database handle big data more efficiently by working with smaller parts instead of the whole table at once. Partitioned tables look like one table but are actually many tables behind the scenes.

Why it matters

Without partitioned tables, databases can slow down when working with very large datasets because every query scans the entire table. Partitioning solves this by limiting queries to relevant partitions, making data retrieval faster and maintenance easier. This is important for businesses that handle huge amounts of data, like online stores or banks, where speed and reliability matter.

Where it fits

Before learning partitioned tables, you should understand basic SQL tables, how to create tables, and simple queries. After mastering partitioning, you can learn about indexing on partitions, query optimization, and advanced data management techniques like sharding or distributed databases.

Mental Model

Core Idea

Partitioned tables split one big table into smaller pieces based on rules, so the database can find and manage data faster and easier.

Think of it like...

Imagine a huge library with millions of books all on one giant shelf. Partitioning is like organizing the books into separate shelves by genre or author, so you only look at the shelf you need instead of searching the whole library.

Main Table
  │
  ├─ Partition 1 (e.g., dates 2020-2021)
  ├─ Partition 2 (e.g., dates 2022-2023)
  └─ Partition 3 (e.g., dates 2024+)

Each partition is a smaller table holding part of the data.

Build-Up - 8 Steps

1

Foundation

Understanding basic tables

Concept: Learn what a table is and how data is stored in rows and columns.

A table in a database is like a spreadsheet with rows and columns. Each row is a record, and each column is a field describing that record. For example, a 'sales' table might have columns for 'id', 'date', and 'amount'.

Result

You can create and query simple tables to store and retrieve data.

Knowing how tables store data is essential before splitting them into parts.

2

Foundation

Why large tables slow down queries

Concept: Understand how big tables affect performance.

When a table has millions of rows, searching or updating data can take longer because the database scans many rows. This is like looking for a book in a huge unsorted pile.

Result

You see that big tables can cause slow queries and maintenance challenges.

Recognizing performance issues motivates the need for partitioning.

3

Intermediate

Introduction to table partitioning

🤔Before reading on: do you think partitioning physically splits data or just creates a view? Commit to your answer.

Concept: Partitioning physically divides a table into smaller tables based on rules.

Partitioning breaks a big table into smaller tables called partitions. Each partition holds rows matching a condition, like a date range or a list of values. The main table acts as a parent that links to these partitions.

Result

You understand that partitioning is a physical data organization, not just a logical grouping.

Knowing partitioning physically separates data helps grasp how queries can be faster.

4

Intermediate

Types of partitioning in PostgreSQL

🤔Before reading on: do you think PostgreSQL supports only one way to partition tables? Commit to your answer.

Concept: PostgreSQL supports range, list, and hash partitioning methods.

Range partitioning divides data by ranges, like dates. List partitioning divides data by specific values, like regions. Hash partitioning distributes data evenly using a hash function. Each method suits different use cases.

Result

You can choose the right partitioning method based on your data and queries.

Understanding partition types helps design efficient data layouts.

5

Intermediate

Creating a partitioned table syntax

Concept: Learn how to write SQL to create partitioned tables and partitions.

In PostgreSQL, you create a partitioned table with 'PARTITION BY' clause. Then you create child tables as partitions with 'FOR VALUES' specifying the partition rule. For example: CREATE TABLE sales ( id serial, sale_date date, amount numeric ) PARTITION BY RANGE (sale_date); CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Result

You can create a partitioned table and add partitions with SQL commands.

Knowing the syntax lets you implement partitioning practically.

6

Advanced

Querying partitioned tables efficiently

🤔Before reading on: do you think queries on partitioned tables scan all partitions or only relevant ones? Commit to your answer.

Concept: PostgreSQL automatically routes queries to relevant partitions using constraint exclusion.

When you query a partitioned table with conditions matching partition rules, PostgreSQL scans only the matching partitions. For example, a query filtering 'sale_date' in 2022 will scan only 'sales_2022' partition, speeding up the query.

Result

Queries become faster because unnecessary partitions are skipped.

Understanding query routing explains why partitioning improves performance.

7

Advanced

Managing partitions and maintenance

Concept: Learn how to add, remove, and maintain partitions over time.

Partitions can be added or dropped as data grows or ages. For example, you can add a new partition for 2024 data or drop old partitions to archive data. This helps keep tables manageable and improves performance.

Result

You can maintain large datasets by managing partitions without downtime.

Knowing partition management is key for long-term database health.

8

Expert

Partitioning internals and performance trade-offs

🤔Before reading on: do you think partitioning always improves performance? Commit to your answer.

Concept: Partitioning improves some queries but adds overhead for others and requires careful design.

Internally, PostgreSQL uses partition pruning to skip partitions during queries. However, too many partitions can slow down planning time. Also, some operations like foreign keys or global indexes are limited on partitioned tables. Understanding these trade-offs helps design better systems.

Result

You appreciate when partitioning helps and when it might hurt performance.

Knowing internal trade-offs prevents misuse and guides expert partitioning strategies.

Under the Hood

PostgreSQL creates a parent table that holds no data but defines the structure. Each partition is a child table with a constraint defining which rows it holds. When a query runs, the planner uses these constraints to prune partitions that cannot contain matching rows, scanning only relevant partitions. Inserts are routed to the correct partition based on the partition key. This reduces I/O and speeds up queries.

Why designed this way?

Partitioning was designed to handle very large tables by breaking them into smaller, manageable pieces. This approach balances query speed and data organization without changing application queries. Alternatives like sharding require more complex application logic, so partitioning offers a simpler, integrated solution.

┌───────────────┐
│ Parent Table  │
│ (no data)     │
└──────┬────────┘
       │
 ┌─────┴─────┐  ┌─────┴─────┐  ┌─────┴─────┐
 │Partition 1│  │Partition 2│  │Partition 3│
 │(range 1)  │  │(range 2)  │  │(range 3)  │
 └───────────┘  └───────────┘  └───────────┘

Query → Planner → Partition Pruning → Scan relevant partitions

Myth Busters - 4 Common Misconceptions

Quick: Does partitioning automatically create indexes on all partitions? Commit yes or no.

Common Belief: Partitioning automatically creates indexes on all partitions when you create the parent table.

Reality: Indexes must be created separately on each partition; the parent table's indexes do not propagate automatically.

Why it matters: Without indexes on partitions, queries can be slow, defeating the purpose of partitioning.

Quick: Do you think foreign keys work across partitions automatically? Commit yes or no.

Common Belief: Foreign keys can be defined between partitioned tables and other tables without restrictions.

Reality: PostgreSQL does not support foreign keys that reference partitioned tables or from partitions to other tables directly.

Why it matters: Assuming foreign keys work can lead to data integrity issues or errors in production.

Quick: Does partitioning always improve query speed? Commit yes or no.

Common Belief: Partitioning always makes queries faster because data is split into smaller parts.

Reality: Partitioning improves queries that filter on the partition key but can slow down queries that scan many partitions or do complex joins.

Why it matters: Misusing partitioning can degrade performance and increase complexity.

Quick: Is partitioning just a logical grouping without physical data separation? Commit yes or no.

Common Belief: Partitioning is only a logical way to organize data; all data stays in one table physically.

Reality: Partitioning physically stores data in separate child tables, not just a logical grouping.

Why it matters: Understanding physical separation is key to grasping performance benefits and maintenance.

Expert Zone

1

Partition pruning depends on query conditions being explicit and matching partition keys; implicit casts or functions can disable pruning.

2

Too many small partitions increase planning time and can hurt performance more than help.

3

Global indexes are not supported on partitioned tables, so indexing strategies must be planned per partition.

When NOT to use

Avoid partitioning when your table is small or queries rarely filter on the partition key. Instead, use regular indexing or materialized views. For distributed data across servers, consider sharding or distributed databases like Citus.

Production Patterns

In production, partitioning is often used for time-series data like logs or sales, where new partitions are added regularly and old ones archived. Automated scripts manage partition creation and dropping. Queries filter on partition keys to benefit from pruning. Monitoring partition sizes and query plans is standard practice.

Connections

Indexing

Partitioning works closely with indexing to speed up queries on partitions.

Knowing how indexes work helps optimize partitioned tables since each partition needs its own indexes.

Sharding

Partitioning is a form of data division similar to sharding but usually within one database instance.

Understanding partitioning clarifies the basics before moving to more complex distributed sharding.

File System Organization

Partitioning is like organizing files into folders to improve access speed and management.

Recognizing this connection helps understand why physical data separation improves performance.

Common Pitfalls

#1 Creating partitions without matching the partition key data types.

Wrong approach:

CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES FROM ('2022-01-01') TO (2023-01-01); -- missing quotes around date

Correct approach:

CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Root cause:

Misunderstanding that partition values must match the data type exactly, including proper quoting for dates.

#2 Not creating indexes on partitions after partition creation.

Wrong approach:

CREATE TABLE sales PARTITION BY RANGE (sale_date); -- no indexes on partitions

Correct approach:

CREATE INDEX ON sales_2022 (sale_date); -- create indexes on each partition

Root cause:

Assuming indexes on parent table apply to partitions automatically.

#3 Querying partitioned tables without filtering on partition keys.

Wrong approach:

SELECT * FROM sales WHERE amount > 1000; -- no partition key filter

Correct approach:

SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2023-01-01' AND amount > 1000;

Root cause:

Not using partition key in queries disables partition pruning, causing full scans.

Key Takeaways

Partitioned tables split large tables into smaller, manageable pieces based on rules like ranges or lists.

This physical separation helps queries run faster by scanning only relevant partitions, not the whole table.

PostgreSQL supports range, list, and hash partitioning, each suited for different data patterns.

Proper indexing on each partition and filtering queries on partition keys are essential for performance.

Partitioning has limits and trade-offs; understanding internals helps avoid common mistakes and design better databases.