postgre-sql

UPDATE users SET name = 'Bob' WHERE id = 1;

-- Dọn rác cơ bản
VACUUM VERBOSE users;
 
-- Dọn rác và dồn mảnh (Trả lại dung lượng đĩa thực tế nhưng sẽ LOCK TABLE toàn bộ!)
VACUUM FULL users;
 
-- Cập nhật lại thống kê dữ liệu cho Query Planner
VACUUM ANALYZE users;

-- Cấu hình tinh chỉnh ngưỡng kích hoạt Autovacuum
ALTER TABLE users SET (
   autovacuum_vacuum_threshold = 50,           -- Số dead tuples tối thiểu để kích hoạt
   autovacuum_vacuum_scale_factor = 0.1,       -- Kích hoạt khi 10% table biến thành rác
   autovacuum_analyze_threshold = 50,
   autovacuum_analyze_scale_factor = 0.05
);

CREATE INDEX idx_users_email ON users(email);
-- Tốt cho: =, <, >, <=, >=, BETWEEN, IN, LIKE 'prefix%'
-- (LIKE 'prefix%' chỉ dùng được index nếu DB collation là "C",
--  còn không phải tạo index với operator class: users(email text_pattern_ops))
-- Không tốt cho: LIKE '%suffix', full-text search

CREATE INDEX idx_users_email_hash ON users USING hash(email);
-- Chỉ tốt cho: = (equality)
-- Có thể nhỏ hơn B-Tree khi key dài; thực tế B-Tree thường đủ tốt
-- Chỉ an toàn khi crash (WAL-logged) từ PostgreSQL 10

CREATE INDEX idx_users_tags ON users USING gin(tags);
-- Tốt cho: arrays, JSONB, full-text search
-- @>, ?, ?|, ?&, @@

CREATE INDEX idx_locations_point ON locations USING gist(coordinates);
-- Tốt cho: geometric, range types, full-text search -> dữ liệu không gian, hình học
-- <<, >>, &&, @>

-- Chỉ index rows thỏa condition → nhỏ hơn, nhanh hơn
CREATE INDEX idx_orders_pending ON orders(created_at)
   WHERE status = 'PENDING';

CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Tốt cho: WHERE user_id = ? AND status = ?
-- Cũng tốt cho: WHERE user_id = ? (leftmost prefix)
-- KHÔNG tốt cho: WHERE status = ? (cột đầu không có trong điều kiện;
--   PG 18 có skip scan nhưng chỉ lợi khi cột đầu ít giá trị, user_id thì không)

CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (status, total);
-- Index-only scan: không cần đọc heap table

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 'abc' AND status = 'PENDING';
 
-- Output:
-- Index Scan using idx_orders_user_status on orders (cost=0.43..8.45 rows=1 width=200)
--   (actual time=0.023..0.025 rows=1 loops=1)
--   Index Cond: (user_id = 'abc' AND status = 'PENDING')
--   Buffers: shared hit=4
-- Planning Time: 0.150 ms
-- Execution Time: 0.050 ms

-- 1. Avoid SELECT *
SELECT id, name, email FROM users WHERE ...;
 
-- 2. EXISTS vs IN: PostgreSQL thường biến cả hai thành semi-join, plan như nhau.
--    Chỗ cần tránh là NOT IN (subquery): subquery có NULL là ra rỗng, và khó tối ưu → dùng NOT EXISTS
SELECT * FROM orders o WHERE EXISTS (
   SELECT 1 FROM users u WHERE u.id = o.user_id AND u.active = true
);
 
-- 3. Pagination with keyset (không dùng OFFSET cho large datasets)
SELECT * FROM orders WHERE id > :last_id ORDER BY id LIMIT 20;
-- Thay vì: SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000; (slow!)
 
-- 4. Batch operations
INSERT INTO orders (id, user_id, total) VALUES
   ('1', 'u1', 100), ('2', 'u2', 200), ('3', 'u3', 300);
 
-- 5. Use COPY for bulk load
-- (đọc file trên MÁY CHỦ database, cần superuser hoặc role pg_read_server_files;
--  file nằm ở máy client thì dùng \copy của psql, hoặc CopyManager của JDBC driver)
COPY orders FROM '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);

-- Set per transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... operations
COMMIT;

-- Range partitioning (by date)
CREATE TABLE orders (
   id UUID,
   created_at TIMESTAMP NOT NULL,
   user_id UUID,
   total DECIMAL,
   PRIMARY KEY (id, created_at) -- khoá chính/unique của bảng partition phải chứa cột partition
) PARTITION BY RANGE (created_at);
 
CREATE TABLE orders_2024_01 PARTITION OF orders
   FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
   FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
 
-- Query tự động chỉ scan partition cần thiết (partition pruning)
SELECT * FROM orders WHERE created_at >= '2024-01-15' AND created_at < '2024-02-01';
-- → chỉ scan orders_2024_01
-- (chỉ có >= '2024-01-15' thì phải scan cả orders_2024_01 lẫn orders_2024_02)

# HikariCP (Spring Boot default)
spring:
  datasource:
    hikari:
      maximum-pool-size: 20 # max connections
      minimum-idle: 5 # min idle connections
      connection-timeout: 30000 # wait for connection (ms)
      idle-timeout: 600000 # close idle connection after 10min
      max-lifetime: 1800000 # max connection age 30min
      leak-detection-threshold: 60000

-- Active connections
SELECT pid, usename, application_name, state, query_start, query
FROM pg_stat_activity WHERE state = 'active';
 
-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
 
-- Index usage
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes ORDER BY idx_scan DESC;
 
-- Unused indexes (candidates for removal)
-- Bỏ qua unique index (đang giữ ràng buộc). Số liệu tính từ lần reset stats gần nhất
-- và riêng cho từng node: index không dùng ở primary có thể đang phục vụ query trên replica
SELECT s.indexrelname, s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique;
 
-- Long running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity WHERE state != 'idle'
ORDER BY duration DESC LIMIT 10;
 
-- Dead tuples (need vacuum)
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC;
 
-- Lock conflicts (PostgreSQL 9.6+)
-- pg_blocking_pids bắt được cả chờ row lock (transactionid), thứ mà join theo relation bỏ sót
SELECT blocked.pid, blocked.query,
       blocking.pid AS blocking_pid, blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

// JPQL FAILS khi :email = null:
@Query("SELECT u FROM User u WHERE LOWER(u.email) = LOWER(:email)")
// Error: function lower(bytea) does not exist
 
// FIX: Native query với CAST
@Query(value = "SELECT * FROM users WHERE LOWER(CAST(:email AS text)) = LOWER(email)", nativeQuery = true)
List<User> findByEmail(@Param("email") String email);
 
// Nullable params FIX:
@Query(value = "SELECT * FROM orders WHERE (CAST(:status AS text) IS NULL OR status = CAST(:status AS text))", nativeQuery = true)
List<Order> findByStatus(@Param("status") String status);
 
// Còn stringtype=unspecified trên JDBC URL là chuyện khác:
// jdbc:postgresql://host:5432/db?stringtype=unspecified
// → driver gửi String dạng "unknown" để server tự suy ra kiểu, hay dùng khi
//   bind String vào cột uuid / jsonb / enum mà không muốn CAST từng chỗ

// Enum phải convert sang String trước khi pass vào native query
String statusStr = status != null ? status.name() : null;
repository.findByStatus(statusStr);