Tabel, query, dan indeks
Kolom identity menyediakan ID. CHECK membatasi nilai yang diizinkan, sementara NOT NULL secara terpisah menolak nilai yang hilang. timestamptz menyatakan suatu saat; tampilannya mengikuti zona waktu sesi.
CREATE TABLE dispatches (
dispatch_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
branch text NOT NULL,
status text NOT NULL CHECK (status IN ('open', 'closed')),
scheduled_at timestamptz,
details jsonb NOT NULL DEFAULT '{}'::jsonb
);
SELECT branch, COUNT(*) AS open_jobs
FROM dispatches
WHERE status = 'open'
GROUP BY branch
HAVING COUNT(*) > 1
ORDER BY open_jobs DESC, branch;
CREATE INDEX dispatch_open_branch
ON dispatches (branch) WHERE status = 'open';
CREATE INDEX dispatch_priority
ON dispatches ((details ->> 'priority'));
CREATE INDEX dispatch_details_gin
ON dispatches USING gin (details);
SELECT dispatch_id FROM dispatches
WHERE details @> '{"priority":"high"}'::jsonb;
BEGIN;
SAVEPOINT before_change;
UPDATE dispatches SET status = 'closed' WHERE dispatch_id = 1;
ROLLBACK TO SAVEPOINT before_change;
COMMIT;
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT dispatch_id FROM dispatches
WHERE branch = 'West' AND status = 'open';
WHERE memfilter baris sebelum agregasi dan HAVING memfilter kelompok sesudahnya. Kunci urut kedua memberi hasil tetap ketika hitungan sama. COUNT(column) menghitung nilai non-NULL; COUNT(*) menghitung baris. Gunakan IS NULL, karena = NULL tidak menghasilkan true.
Pemilihan indeks
Indeks parsial pada contoh hanya memuat dispatch terbuka. Indeks ekspresi menargetkan teks prioritas, sedangkan GIN mendukung operasi JSONB seperti containment. Ketiganya menunjukkan sintaks, bukan anjuran memasang semua indeks. Masing-masing menambah penyimpanan dan pekerjaan penulisan.
Keberadaan indeks tidak menjamin planner akan memakainya. Periksa distribusi data dan rencana aktual di pelajaran indeks.
Transaksi dan EXPLAIN
ROLLBACK TO SAVEPOINT membatalkan perubahan setelah savepoint tanpa menutup transaksi. COMMIT mengakhirinya. Transaksi yang gagal perlu rollback sebelum perintah lain; perubahan sequence merupakan pengecualian terhadap anggapan rollback biasa.
EXPLAIN memperkirakan rencana. ANALYZE menjalankan statement, termasuk perubahan data jika statement menulis. Uji perubahan pada lingkungan sementara dengan batas transaksi yang sesuai.
JSONB dan izin
-> menghasilkan nilai JSON; ->> menghasilkan teks; @> memeriksa containment. Kunci hilang dan JSON null perlu dibedakan.
Hak akses tabel dan row policy adalah pemeriksaan terpisah. Pemilik tabel biasanya melewati policy; superuser serta role BYPASSRLS juga melewatinya. Uji dengan role aplikasi yang dituju pada latihan row-level security. Gunakan soal PostgreSQL untuk hasil pembanding yang dapat dijalankan.