Net's Digital Garden 🌱

Database Cursors

อ่านราว 2 นาที

Database Cursors

กลไกจัดการ result set ขนาดใหญ่โดยไม่โหลดทั้งหมดเข้า memory — รวมถึง pagination techniques

ปัญหา: Query คืน Result ใหญ่มาก

SELECT * FROM transactions WHERE year=2023;
-- คืน 100 ล้าน row → 50 GB → app crash จาก OOM!

Server-Side Cursor

DB server ค้าง state ไว้ → client FETCH ทีละ batch

BEGIN;
DECLARE my_cursor CURSOR FOR 
  SELECT * FROM transactions WHERE year=2023;
FETCH 100 FROM my_cursor;  -- ดึง 100 แถว
FETCH 100 FROM my_cursor;  -- ดึงอีก 100 แถว
CLOSE my_cursor;
COMMIT;
  • ข้อดี: client memory = constant
  • ข้อเสีย: ถือ resources บน server (memory, locks, transaction snapshot) → block VACUUM

Client-Side Cursor

DB ส่ง result ทั้งหมดให้ client → client iterate ใน memory — ปลอม cursor

ใน Python (psycopg2)

# Default — ดึงทั้งหมด (client-side)
cur = conn.cursor()
cur.execute("SELECT * FROM big_table")
 
# Named cursor → server-side
cur = conn.cursor(name='server_cursor')
cur.itersize = 1000
cur.execute("SELECT * FROM big_table")
for row in cur:  # fetch ทีละ batch อัตโนมัติ
    process(row)

SQL Server Cursor Types

Typeลักษณะ
StaticSnapshot ตอน open → เห็นค่าคงที่
Dynamicเห็น changes ระหว่าง iterate
Forward-onlyเคลื่อนทางเดียว, memory น้อยสุด
KeysetFix keys ตอน open แต่ค่า row อ่าน live

Modern best practice: หลีกเลี่ยง cursor ถ้าทำได้ — ใช้ set-based operation (UPDATE..WHERE, batch INSERT) แทน loop ทีละ row

Pagination — ทางเลือกของ Cursor

OFFSET Pagination (ช้าเมื่อ offset ใหญ่)

SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 1000000;
-- DB ต้อง scan + ทิ้ง 1M rows ก่อนคืน 10 → O(n) ช้ามาก!

Keyset Pagination (เร็วเสมอ — แนะนำ)

-- รอบแรก
SELECT * FROM posts ORDER BY id DESC LIMIT 10;
-- ส่งกลับ: rows + last_id (เช่น 980)
 
-- รอบถัดไป
SELECT * FROM posts WHERE id < 980 ORDER BY id DESC LIMIT 10;
-- ใช้ index → O(log n) เร็วเสมอ!

ทำไม Twitter/Facebook ใช้ “Load more” ไม่ใช่ page numbers? เพราะ keyset pagination ดีกว่า OFFSET — แต่ไม่ตรงกับ “ไปหน้า 57” ได้

Key Points

  • Server-side cursor = memory คงที่ แต่ถือ resources บน server
  • Client-side cursor = ปลอม cursor, โหลด memory มาก
  • หลีกเลี่ยง cursor ถ้าทำได้ — ใช้ set-based operations
  • OFFSET ใหญ่ = disaster → O(n) scan
  • Keyset pagination เร็วเสมอ O(log n) — ใช้ WHERE id > last_id
  • Infinite scroll → ใช้ keyset ส่ง last_id กลับเป็น cursor

มุมมองกราฟ

หน้าที่กล่าวถึง