Piye carane table partitioning maujud ing PostgreSQL?

Question

Accepted Answer

**Partitioning** mbagi tabel gedhe dadi potongan fisik cilik (**partitions**) adhedhasar nilai kolom, nanging tetep dadi siji tabel logis. PostgreSQL's **declarative partitioning** (Postgres 10+) nggawe iki beresih. Iki ningkatake performance lan manageability kanggo tabel sing gedhe banget.

## Declarative partitioning (cara modern)

```sql
-- create a partitioned table, partitioned by RANGE on a date
CREATE TABLE orders (id INT, order_date DATE, amount NUMERIC)
PARTITION BY RANGE (order_date);

-- create partitions for each range
CREATE TABLE orders_2023 PARTITION OF orders
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
```

`orders` punika siji tabel logis, disimpen fisik ing per-year partitions. Inserts lan queries dijumbuhi menyang partition sing tepat kanthi otomatis.

## Partition pruning — keuntungan performance sing utama

```sql
-- a query filtered on the partition key scans ONLY relevant partitions
SELECT * FROM orders WHERE order_date >= '2024-06-01';
-- → Postgres skips orders_2023 entirely ("partition pruning") — scans only orders_2024
```

**Partition pruning** punika kamenangan utama: queries sing filter ing partition key mung menyapu partitions sing relevan, drastis ngurangi data sing discan ing tabel gedhe.

## Strategi partition

```text
PARTITION BY RANGE → value ranges (dates, numbers) — common for time-series
PARTITION BY LIST  → discrete values (region, status)
PARTITION BY HASH  → hash of the key (even distribution across N partitions)
```

## Keuntungan maintenance

```sql
-- drop old data instantly by dropping a whole partition (vs slow DELETE)
DROP TABLE orders_2020;              -- removes all 2020 data instantly
-- vs DELETE FROM orders WHERE year = 2020 → slow, creates bloat

-- detach/attach partitions; automate with pg_partman extension
```

Nglowokake sawijine partition kanggo archive/ngilangi data lawas punika instan — kauntungan operasional gedhe tinimbang `DELETE` kanggo dataset gedhe.

## Mengapa iki penting

Table partitioning punika teknik PostgreSQL penting kanggo ngatur lan query **tabel sing gedhe banget**, lan pamahaman babagan iki bernilai kanggo kerja karo Postgres ing skala gedhe.

Semene tabel tuwuh menyang ratusan miliun utawa miliaran baris, partitioning ngatasi performance lan manageability kanthi mbagi tabel dadi potongan fisik cilik.

Postgres's **declarative partitioning** (Postgres 10+) nggawe iki beresih lan punika standar modern kanggo dipahami.

Keuntungan performance utama punika **partition pruning** — queries sing filter ing partition key scan mung partitions sing relevan (ndhelek sing liya), drastis ngurangi data sing discan kanggo tabel gedhe (utamane berharga kanggo time-series data sing difilter dening tanggal, kasus sing umum banget).

Padasar performance, partitioning nyedhiyakake keuntungan **maintenance** gedhe: archive utawa ngilangi data lawas dadi instan kanthi nlowokake sawijine partition (lawan slow, bloat-creating `DELETE` saka miliaran baris — kauntungan operasional signifikan kanggo data karo kebijakan retention, kaya logs lan events).

Pamahaman babagan **strategies** (RANGE kanggo date/numeric ranges — umum kanggo time-series; LIST kanggo nilai diskret; HASH kanggo distribusi merata) lan **kapan nggunakake partitioning** (tabel gedhe banget karo partition key natural, utamane time-series karo archive reguler — ora tabel cilik ngendhi indexing cukup) nggambarake penilaian sing apik.

Kulawargane ngatur tabel gedhe banget punika tantangan real ing sistem PostgreSQL scaled, lan kulawargane declarative partitioning (karo partition pruning lan partition-drop maintenance efisien) punika alat standar — nambahi indexing — pamahaman PostgreSQL table partitioning bernilai, praktis-relevan pengetahuan kanggo database scalability, utamane penting kanggo aplikasi large-scale, high-volume, utawa time-series lan topik umum nunjukake kemampuan ngatur Postgres ing skala.