Sistem kami mulai kewalahan menanggung beban. Halaman web menjadi lambat, dashboard sering time out, dan tim customer support mulai menyadarinya.

Biang keladinya? Sebuah query krusial yang digunakan di berbagai layanan dan seiring waktu menjadi semakin kompleks. Kami terus menambahkan lebih banyak join, filter, dan kalkulasi—hingga akhirnya menjadi bagian paling lambat dari sistem kami.

Kami sudah mencoba segalanya: membuat index, melakukan denormalization, bahkan menggunakan query hints. Tidak ada yang berhasil—sampai seorang developer menulis ulang query tersebut menggunakan sebuah trik SQL yang tak terduga:

CTE (Common Table Expressions)—tetapi digunakan secara inline dan berulang.

Satu perubahan ini berhasil memangkas waktu query kami hingga lebih dari 80%.

Masalahnya: Query Analitis yang Membengkak

Berikut adalah versi sederhana dari query yang kami jalankan:

SELECT
    users.id,
    users.name,
    COUNT(DISTINCT orders.id) AS order_count,
    SUM(orders.amount) AS total_spent,
    MAX(logins.timestamp) AS last_login
FROM users
LEFT JOIN orders ON users.id = orders.user_id
LEFT JOIN logins ON users.id = logins.user_id
WHERE users.created_at >= '2023-01-01'
GROUP BY users.id;

Query ini dijalankan pada tabel dengan 5 juta users, 50 juta orders, dan 80 juta login events. Rata-rata, query ini membutuhkan waktu lebih dari 9,3 detik untuk selesai.

Solusinya: Reusable Inline CTEs

Masalahnya bukan hanya volume data—tetapi juga join dan proses scan yang berulang (redundant). Untuk setiap user, kami menggabungkan tabel besar seperti `orders` dan `logins` hanya untuk menghitung agregat. Jadi, kami melakukan refactor menggunakan CTEs—bukan hanya untuk menyederhanakan logika, tetapi untuk melakukan pra-agregasi dan menggunakan kembali hasilnya.

WITH recent_users AS (
    SELECT id, name
    FROM users
    WHERE created_at >= '2023-01-01'
),
order_stats AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
    FROM orders
    GROUP BY user_id
),
last_logins AS (
    SELECT user_id, MAX(timestamp) AS last_login
    FROM logins
    GROUP BY user_id
)
SELECT
    u.id,
    u.name,
    o.order_count,
    o.total_spent,
    l.last_login
FROM recent_users u
LEFT JOIN order_stats o ON u.id = o.user_id
LEFT JOIN last_logins l ON u.id = l.user_id;

Penjelasan Kode:

1. `WITH recent_users AS (...)`: CTE pertama ini bertindak sebagai saringan awal. Ia hanya mengambil `id` dan `name` dari user yang dibuat setelah tanggal 1 Januari 2023. Hasilnya adalah sebuah tabel virtual yang jauh lebih kecil dari tabel `users` asli.
2. `order_stats AS (...)`: CTE kedua melakukan pra-agregasi. Ia menghitung jumlah pesanan (`order_count`) dan total belanja (`total_spent`) untuk setiap `user_id` dari tabel `orders`. Dengan `GROUP BY user_id`, database tidak perlu lagi memproses setiap baris pesanan satu per satu di query utama.
3. `last_logins AS (...)`: Mirip dengan sebelumnya, CTE ini mencari waktu login terakhir (`last_login`) untuk setiap `user_id`.
4. `SELECT ... FROM recent_users u ...`: Query utama sekarang menjadi sangat sederhana. Ia hanya perlu menggabungkan (`LEFT JOIN`) hasil dari tiga CTE yang sudah diproses sebelumnya. Setiap join sekarang dilakukan pada dataset yang jauh lebih kecil dan sudah teragregasi, bukan lagi pada tabel mentah yang masif.

Sekarang, setiap join hanya mengakses dataset yang sudah dihitung, dikelompokkan, dan berukuran lebih kecil—bukan lagi tabel mentah.

Mengapa Ini Berhasil?

Ada dua prinsip utama di balik percepatan ini:

1. Menghindari Pekerjaan Redundan: Setiap `LEFT JOIN` pada query asli memindai seluruh tabel dan menghitung ulang agregat untuk setiap baris. Dengan CTE yang sudah di-pra-agregasi, query planner bekerja dengan data yang jauh lebih sedikit.
2. Memecah Kompleksitas: Query yang besar dan rumit seringkali membingungkan optimizer. Dengan memisahkan logika ke dalam CTE, kita memberi database lebih banyak kendali untuk menjalankan dan mengoptimalkan setiap sub-rencana secara paralel.

Hal yang Perlu Diperhatikan

• Perilaku CTE di PostgreSQL: Di versi lama PostgreSQL, CTE bisa bertindak sebagai optimization barrier, yang berarti database tidak akan menggabungkannya ke dalam query utama untuk optimasi lebih lanjut. Namun, sejak PostgreSQL 12, CTE secara default akan di-inline kecuali Anda secara eksplisit menggunakan perintah `MATERIALIZED`.
• Bukan untuk Filter Sederhana: Teknik ini paling bersinar ketika Anda dapat menggunakan kembali hasil CTE atau ketika pra-agregasi dapat mengurangi ukuran pindaian data secara signifikan. Untuk filter sederhana, dampaknya mungkin tidak terasa.
• Selalu Periksa Execution Plan: Gunakan `EXPLAIN ANALYZE` untuk memverifikasi manfaatnya. Terkadang, tergantung pada database Anda, subquery bisa berkinerja lebih baik daripada CTE.

Pelajaran yang Kami Dapat

• CTE Bukan Sekadar Keterbacaan: CTE bisa menjadi pendorong performa yang masif ketika digunakan untuk pra-agregasi data.
• Kompleksitas Query Datang Diam-diam: Apa yang awalnya merupakan query cepat bisa berubah menjadi hambatan skalabilitas tanpa kita sadari.
• Memecah Logika Meningkatkan Perencanaan: Ini berlaku baik bagi manusia yang membaca kode maupun bagi query planner yang menjalankannya.

Sebelum Anda Melakukan Optimasi…

1. Lihat query terlambat Anda (misalnya, melalui `pg_stat_statements`).
2. Identifikasi join yang berulang dan logika yang tumpang tindih.
3. Pertimbangkan untuk memindahkan agregasi berat ke dalam CTE yang dapat digunakan kembali.
4. Selalu lakukan benchmark pada setiap perubahan—apa yang berhasil di satu skema belum tentu berhasil di skema lain.

Pikiran Akhir

CTE sering diajarkan hanya sebagai fitur untuk meningkatkan keterbacaan kode. Namun, ketika digunakan untuk melakukan pra-agregasi data dan mengisolasi logika, CTE dapat membuka peningkatan performa yang luar biasa—bahkan tanpa menyentuh index atau skema.

Trik ini membantu kami menghindari restrukturisasi arsitektur besar-besaran. Jika Anda menghadapi join yang berjalan lama dan query analitis yang lambat, cobalah lakukan refactor dengan CTE.

Ini mungkin akan menyelamatkan Anda dari frustrasi berhari-hari—dan ribuan dolar dalam biaya komputasi.