0Pricing
Advanced PostgreSQL: Indexing, Partitioning, Replication · レッスン

パーティショニングによるインデックスのスケーリング

インデックスとパーティションテーブルの相互作用、および効果的なローカルインデックスとグローバルインデックスを作成する戦略を学びます。

「パーティショニングによるインデックスのスケーリング」はCoddyKit上の無料Advanced PostgreSQL: Indexing, Partitioning, Replicationレッスンです。 これはレッスン1/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはAdvanced PostgreSQL: Indexing, Partitioning, Replication学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Advanced PostgreSQL: Indexing, Partitioning, Replicationコースには全4レッスンが含まれています。

このレッスンの一部はまだ翻訳されておらず、英語で表示されています。

Indexes & Partitioning Synergy

When dealing with truly massive datasets in PostgreSQL, combining indexing and partitioning isn't just an option—it's often a necessity for maintaining performance.

This lesson explores how indexes interact with partitioned tables, and the strategies for creating indexes that scale effectively alongside your data divisions.

Understanding Local Indexes

A local index is an index that is created on an individual partition of a partitioned table. When you create an index on the parent partitioned table, PostgreSQL automatically creates a separate index for each of its partitions.

  • Each local index only covers the data within its specific partition.
  • They are implicitly managed as you add or remove partitions.
  • This is the most common and often most efficient type of index for partitioned tables.

Creating Local Indexes

Creating a local index is straightforward. You simply create the index on the parent partitioned table. PostgreSQL handles the creation of individual indexes for each child partition automatically.

Try running this example:

CREATE TABLE sensor_data (
    id SERIAL,
    log_time TIMESTAMPTZ NOT NULL,
    value NUMERIC
) PARTITION BY RANGE (log_time);

CREATE TABLE sensor_data_y2023 PARTITION OF sensor_data
FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2024-01-01 00:00:00');

-- This creates local indexes on all current and future partitions
CREATE INDEX idx_sensor_logtime ON sensor_data (log_time);

Local Index Advantages

Local indexes shine when queries involve the partition key. Here's why:

  • Partition Pruning: PostgreSQL's query planner can quickly identify and scan only the relevant partitions based on your query's `WHERE` clause.
  • Smaller Indexes: Each local index is smaller, containing only a subset of the data, leading to faster lookups within a partition.
  • Reduced I/O: Less data needs to be read from disk, improving query speed.

Understanding Global Indexes

A global index, unlike a local index, spans across all partitions of a partitioned table. It's a single, monolithic index that acts much like an index on a regular, non-partitioned table.

  • It's not tied to any specific partition.
  • Updates to any partition affect the single global index.
  • They are less common for general query optimization on partitioned tables.

Creating Global Indexes

To create a global index, you typically use the ONLY keyword when specifying the parent table. This tells PostgreSQL to create the index directly on the parent, not implicitly on each child.

Global indexes are often used for enforcing unique constraints across the entire partitioned table on columns that are not the partition key.

Try running this example:

-- Assuming sensor_data is already partitioned
-- Create a unique global index across all partitions
CREATE UNIQUE INDEX idx_sensor_id_global ON ONLY sensor_data (id);

Global Index Considerations

While global indexes have their uses, they come with trade-offs:

  • Maintenance Overhead: Inserts, updates, or deletes to any partition require updates to the single global index, which can be slower.
  • No Partition Pruning Benefit: Queries using a global index don't benefit from partition pruning on the index itself, as it spans all data.
  • Use Cases: Best for unique constraints on non-partition key columns, or queries that frequently access non-partition key columns across the entire dataset.

Local vs. Global: When?

Choosing between local and global indexes depends on your workload:

  • Local Indexes: Ideal when queries frequently filter data using the partition key (e.g., date ranges, specific categories). They leverage partition pruning for speed.
  • Global Indexes: Consider for enforcing unique constraints on columns that are not the partition key, or for queries that frequently access non-partition key columns across the entire table.

Most often, local indexes are the preferred choice for performance on partitioned tables.

Practical Example Scenario

Imagine an orders table partitioned by order_date.

  • A local index on order_date would be highly efficient for queries like WHERE order_date BETWEEN X AND Y because of partition pruning.
  • A global index on customer_id would be efficient for WHERE customer_id = Z across all orders, especially if you need to find all orders for a customer regardless of date.

The key is aligning your index strategy with your most common query patterns.

Indexing Partitioned Tables

Consider a large sales table partitioned by sale_date. Which index type is generally more efficient for queries like SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31' AND product_id = 123;?

Scaling Indexes Summary

In this lesson, we explored how indexes interact with partitioned tables. You learned about:

  • Local indexes: Created per partition, ideal for queries using the partition key, benefiting from partition pruning.
  • Global indexes: Span all partitions, useful for unique constraints or queries on non-partition key columns across the whole table, but with more maintenance overhead.

Mastering the interplay between partitioning and indexing is key to unlocking high performance in large-scale PostgreSQL databases.

よくある質問

「パーティショニングによるインデックスのスケーリング」レッスンは無料ですか？

はい。「パーティショニングによるインデックスのスケーリング」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Advanced PostgreSQL: Indexing, Partitioning, Replicationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Advanced PostgreSQL: Indexing, Partitioning, Replicationコースには全4レッスンが含まれています。

「パーティショニングによるインデックスのスケーリング」で何を学びますか？

インデックスとパーティションテーブルの相互作用、および効果的なローカルインデックスとグローバルインデックスを作成する戦略を学びます。 ブラウザで直接実行するハンズオンコードでAdvanced PostgreSQL: Indexing, Partitioning, Replicationを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

Advanced PostgreSQL: Indexing, Partitioning, Replicationを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのAdvanced PostgreSQL: Indexing, Partitioning, Replicationは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。

「パーティショニングによるインデックスのスケーリング」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このAdvanced PostgreSQL: Indexing, Partitioning, Replicationレッスンでコードを書いて実行できますか？

はい。すべてのAdvanced PostgreSQL: Indexing, Partitioning, Replicationレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. パーティショニングによるインデックスのスケーリング
  2. インデックスとパーティショニング戦略の選択
  3. 実践的なケーススタディ
  4. 大規模なパーティションテーブル向けBRINインデックス
← Advanced PostgreSQL: Indexing, Partitioning, Replicationに戻る