Advanced PostgreSQL: Indexing, Partitioning, Replication · 강의

파티션 가지치기 및 제외

최적화 프로그램이 파티션 키를 사용하여 관련 없는 파티션을 제외하고 스캔할 데이터 양을 크게 줄이는 방식을 심층적으로 알아봅니다.

파티션 가지치기 및 제외은(는) CoddyKit의 무료 Advanced PostgreSQL: Indexing, Partitioning, Replication 강의입니다. 이것은 4개 중 3번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 Advanced PostgreSQL: Indexing, Partitioning, Replication 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. Advanced PostgreSQL: Indexing, Partitioning, Replication 강의에는 총 4개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

What is Partition Pruning?

Welcome to a key optimization technique in PostgreSQL: Partition Pruning. This is where the database intelligently skips scanning partitions that cannot possibly contain the data a query is looking for.

Think of it as filtering bookshelves: if you're looking for a book published in 2023, you wouldn't check shelves marked '1990-1999' or '2000-2010'.

How the Optimizer Works

When you execute a query on a partitioned table, PostgreSQL's query planner examines the WHERE clause. It compares the conditions in your query to the definitions of your table's partitions.

If the query's conditions guarantee that certain partitions cannot possibly hold any matching rows, the optimizer simply excludes those partitions from the scan plan. This significantly reduces the amount of data that needs to be read from disk.

Pruning with Range Partitions

Partition pruning is most evident with range-partitioned tables, especially those partitioned by date or timestamp. For example, if you have a table partitioned by month, and you query for data from a specific week, only the relevant month partition(s) will be scanned.

This is incredibly powerful for time-series data, as queries often target specific timeframes.

Demo: Range Pruning in Action

Let's create a simple range-partitioned table and see how EXPLAIN shows pruning. We'll partition by sale_date.

Notice how the EXPLAIN output will only show a scan on the relevant partition, not the others.

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

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

CREATE TABLE sales_2023_q2 PARTITION OF sales
FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

INSERT INTO sales VALUES (1, '2023-01-15', 100);
INSERT INTO sales VALUES (2, '2023-04-20', 200);

EXPLAIN SELECT * FROM sales WHERE sale_date = '2023-01-15';

Pruning with List Partitions

Partition pruning also works effectively with list-partitioned tables. If your table is partitioned by a discrete value, like a region or a status code, and your query filters on that specific value, only the corresponding partition will be scanned.

This is useful when you often query data specific to certain categories or groups.

Demo: List Pruning Example

Here's an example using a list-partitioned table based on a region column. Observe the EXPLAIN output to see only the 'North' partition being scanned.

CREATE TABLE products (
    product_id INT,
    region TEXT,
    price NUMERIC
) PARTITION BY LIST (region);

CREATE TABLE products_north PARTITION OF products
FOR VALUES IN ('North');

CREATE TABLE products_south PARTITION OF products
FOR VALUES IN ('South');

INSERT INTO products VALUES (101, 'North', 50.00);
INSERT INTO products VALUES (102, 'South', 75.00);

EXPLAIN SELECT * FROM products WHERE region = 'North';

Static vs. Dynamic Pruning

PostgreSQL employs two main types of pruning:

Static Pruning: Occurs at query planning time. The planner can see the explicit values in your WHERE clause and immediately exclude partitions.
Dynamic Pruning: Happens during query execution. This is for more complex cases, like when the partition key is filtered by the result of a subquery or a parameter from a join. The database determines which partitions to scan as it runs.

When Pruning Might Not Occur

While powerful, partition pruning isn't always possible:

Complex Expressions: If your WHERE clause uses a function or complex expression on the partition key (e.g., EXTRACT(MONTH FROM sale_date) = 1).
Non-Partition Key Filters: Queries filtering only on columns not part of the partition key will scan all partitions.
Joins: Pruning with joins can be trickier, especially if the join condition doesn't directly involve the partition key or if the values are not known until runtime.

Verifying Pruning with EXPLAIN

To confirm that partition pruning is working, always use EXPLAIN (or EXPLAIN ANALYZE). Look for lines like:

-> Partition Selector (Dyanmic Partition Pruning)
-> Append (partitions: 1)
-> Result (partitions: 1)

The key is seeing a limited number of partitions selected, rather than scanning the entire partitioned table or all its child tables.

Quick Check: Pruning Benefits

Understanding partition pruning is crucial for optimizing queries on large partitioned tables. Let's test your knowledge!

Pruning Power-Up!

You've mastered partition pruning! You now understand that it's a vital PostgreSQL optimization that:

Significantly reduces the amount of data scanned.
Works by comparing WHERE clauses with partition definitions.
Is especially effective with range and list partitions.
Can be static (planning time) or dynamic (execution time).
Can be verified using EXPLAIN.

By leveraging partition pruning, you ensure your queries run as efficiently as possible on large datasets!

자주 묻는 질문

“파티션 가지치기 및 제외” 강의는 무료인가요?

네 — “파티션 가지치기 및 제외” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 Advanced PostgreSQL: Indexing, Partitioning, Replication 강의 전체를 잠금 해제할 수 있습니다. Advanced PostgreSQL: Indexing, Partitioning, Replication 강의에는 총 4개의 강의가 포함되어 있습니다.

“파티션 가지치기 및 제외”에서 뭘 배우나요?

최적화 프로그램이 파티션 키를 사용하여 관련 없는 파티션을 제외하고 스캔할 데이터 양을 크게 줄이는 방식을 심층적으로 알아봅니다. 브라우저에서 직접 실행하는 실습 코드로 Advanced PostgreSQL: Indexing, Partitioning, Replication을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

Advanced PostgreSQL: Indexing, Partitioning, Replication을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 Advanced PostgreSQL: Indexing, Partitioning, Replication은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 3번째 강의입니다.

“파티션 가지치기 및 제외” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 Advanced PostgreSQL: Indexing, Partitioning, Replication 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 Advanced PostgreSQL: Indexing, Partitioning, Replication 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

← Advanced PostgreSQL: Indexing, Partitioning, Replication(으)로 돌아가기