Pendahuluan

PostgreSQL jauh lebih dari sekadar database relasional biasa. Dengan fungsi analitik canggih, tipe data fleksibel, dan kemampuan ekstensi yang luas, PostgreSQL menjadi tulang punggung yang kuat untuk alur kerja machine learning. Banyak engineer ML yang meremehkan apa yang bisa dilakukan langsung di lapisan database, seringkali menarik data mentah ke Python untuk transformasi yang sebenarnya bisa ditangani PostgreSQL dengan lebih efisien.

Tutorial ini membahas teknik-teknik PostgreSQL lanjutan yang langsung dapat diterapkan pada pipeline ML: rekayasa fitur dengan window functions, pengorganisasian query dengan CTE, caching fitur terhitung dengan materialized views, penyimpanan metadata eksperimen dengan JSONB, penanganan dataset besar dengan partisi, pemantauan performa query, dan integrasi semuanya dengan Python menggunakan psycopg2 dan SQLAlchemy.

Prasyarat

• PostgreSQL 14+ terinstal dan berjalan
• Python 3.9+ dengan pip
• Pengetahuan dasar SQL (SELECT, JOIN, GROUP BY)
• Pemahaman konsep ML (fitur, data pelatihan, eksperimen)
• Instal paket Python yang dibutuhkan:

# Instal dependensi
pip install psycopg2-binary sqlalchemy pandas

import psycopg2
import sqlalchemy
import pandas as pd

Window Functions untuk Rekayasa Fitur

Window functions adalah salah satu alat paling ampuh untuk rekayasa fitur ML. Fungsi ini memungkinkan Anda menghitung agregat pada baris-baris yang berhubungan dengan baris saat ini tanpa menciutkan hasil — sangat cocok untuk membuat statistik bergulir (rolling statistics), fitur lag, dan fitur peringkat.

Statistik Bergulir (Rolling Statistics)

-- Buat tabel transaksi contoh
CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    customerid INTEGER NOT NULL,

    amount DECIMAL(10, 2) NOT NULL,
    category VARCHAR(50),
    createdat TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Rata-rata transaksi bergulir (7 hari terakhir) per pelanggan
SELECT
    id,
    customerid,

    amount,
    createdat,
    AVG(amount) OVER (
        PARTITION BY customerid

        ORDER BY createdat
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) AS rata2bergulir7h,
    STDDEV(amount) OVER (
        PARTITION BY customerid

        ORDER BY createdat
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) AS stddevbergulir7h,
    COUNT() OVER (

        PARTITION BY customerid

        ORDER BY createdat
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) AS jumlahtxn7h
FROM transactions
ORDER BY customerid, createdat;

-- Fitur lag: jumlah transaksi sebelumnya dan delta waktu
SELECT
    id,
    customerid,

    amount,
    createdat,
    LAG(amount, 1) OVER w AS jumlahsebelumnya1,
    LAG(amount, 2) OVER w AS jumlahsebelumnya2,
    LAG(amount, 3) OVER w AS jumlahsebelumnya3,
    amount - LAG(amount, 1) OVER w AS selisihjumlah,

    EXTRACT(EPOCH FROM (
        createdat - LAG(createdat, 1) OVER w

    )) / 3600.0 AS jamsejaktxnterakhir,
    NTILE(10) OVER (
        PARTITION BY customerid

        ORDER BY amount
    ) AS desiljumlah
FROM transactions