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%'
-- 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)
-- Nhỏ hơn B-Tree, nhanh hơn cho equality lookups

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 = ? (không dùng được index)

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. Use EXISTS instead of IN for subqueries
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
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 PRIMARY KEY,
   created_at TIMESTAMP NOT NULL,
   user_id UUID,
   total DECIMAL
) 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';
-- → chỉ scan orders_2024_01

# 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)
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey%';
 
-- 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
SELECT blocked.pid, blocked.query, blocking.pid, blocking.query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks bk ON bk.relation = bl.relation AND bk.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = bk.pid
WHERE NOT bl.granted;

// JDBC URL: jdbc:postgresql://host:5432/db?stringtype=unspecified
// → driver gửi params dạng bytea thay vì varchar
 
// JPQL FAILS:
@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);

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