Apa iku table partitioning?

Question

Accepted Answer

**Partitioning** mbagi tabel gedhe dadi potongan fisik luwih cilik (**partitions**) adhedhasar nilai kolom — nanging isih kelihatan minangka siji tabel logis kanggo query. Iki nambah performa lan kemudahan kelola kanggo tabel gedhe banget kanthi ngidini database kanggo scan utawa gawe-gawe mung partitions sing relevan.

## Carane partitioning bisa gumana

```sql
-- a huge `orders` table partitioned BY RANGE on the order date
CREATE TABLE orders (id INT, order_date DATE, amount DECIMAL)
PARTITION BY RANGE (order_date);

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 is ONE logical table, but stored as separate per-year partitions
```

Tabel iku siji entitas logis, nanging simpen fisik ing partitions. Query lan insert menyang partisi sing tepat kanthi otomatis.

## Kauntungan utama: partition pruning

```sql
-- a query filtered by the partition key only scans RELEVANT partitions
SELECT * FROM orders WHERE order_date >= '2024-06-01';
-- → the database skips orders_2023 entirely, scanning only orders_2024 ("partition pruning")
```

**Partition pruning** minangka kauntungan performa utama: query sing difilter ing partition key mung ngenakan partitions sing relevan, lompat sing liya — ngubaah scan miliaran baris dadi scan mung subset sing relevan.

## Strategi partitioning

```text
RANGE → by a value range (dates, numeric ranges) — common for time-series data
LIST  → by a list of discrete values (region, category)
HASH  → by a hash of the key (even distribution across partitions)
```

## Kauntungan liya

```text
✓ Maintenance — drop/archive old data by dropping a whole partition (instant vs DELETE)
  e.g. DROP TABLE orders_2020 — far faster than DELETE WHERE year = 2020
✓ Smaller indexes per partition; parallel operations across partitions
✓ Manage huge tables in manageable pieces
```

## Kapan nggunakake partitioning

```text
✓ VERY LARGE tables (hundreds of millions / billions of rows) where queries filter
  on a natural partition key (often time/date for logs, events, orders)
✓ Time-series data where you regularly archive/drop old data
✗ Small/medium tables — partitioning adds complexity with little benefit
  (proper indexing usually suffices)
→ Partitioning complements indexing for VERY large tables; it's not a substitute.
```

## Kenapa penting

Table partitioning minangka teknik penting kanggo ngatur lan query **tabel gedhe banget**, lan pangerten babagan iki minangka ilmu level senior sing berharga kanggo ngurus database ing skala besar.

Semene tabel tuwuh dadi ratusan juta utawa miliaran baris, malah query indexed lan operasi pemeliharaan bisa dadi alon, lan partitioning ngatasi iki kanthi mbagi tabel dadi potongan fisik luwih cilik.

Kauntungan performa kunci iku **partition pruning** — query sing difilter ing partition key mung scan partitions sing relevan (lompat sing liya), sing ngecilake data sing discan kanggo tabel gedhe (utamane umum kanggo data time-series sing difilter adhedhasar tanggal).

Mangkono saka performa query, partitioning nyediakake **kauntungan pemeliharaan** gedhe: nyimpen utawa mbusak data lawas dadi instan (mbusak seluruh partisi vs slow, mahal `DELETE` miliaran baris — kauntungan operasional nyata kanggo data log/event kanthi kebijakan retention), plus index per-partisi luwih cilik lan operasi paralel.

Pangerten babagan **strategi** (RANGE kanggo tanggal/range numerik — umum kanggo time-series; LIST kanggo nilai diskrit; HASH kanggo distribusi rata) lan penting banget **kapan nggunakake partitioning** (tabel gedhe banget kanthi partition key alami, utamane time-series kanthi arsip biasa — *bukan* tabel cilik/menengah kang indexing cukup lan partitioning mung nambah kompeksitas) nunjukake pertimbangan mateng.
// Since managing very large tables is a real challenge in scaled systems, and since partitioning (with partition pruning and efficient maintenance) is the standard tool for it — complementing rather than replacing indexing — understanding table partitioning is valuable senior-level knowledge for database scalability, particularly relevant for large-scale, time-series, or high-volume data systems and a topic that demonstrates understanding of managing databases at scale.

Semene ngatur tabel gedhe banget minangka tantangan nyata ing sistem scaled, lan semene partitioning (kanthi partition pruning lan pemeliharaan efisien) minangka alat standar kanggo iku — nambah tinimbang ngganti indexing — pangerten table partitioning minangka ilmu level senior sing berharga kanggo skalabilitas database, utamane relevan kanggo sistem skala besar, time-series, utawa volume tinggi data lan topik sing nuduhake pangerten babagan ngatur database ing skala besar.