Hướng dẫn thiết kế, quản lý và tối ưu cơ sở dữ liệu PostgreSQL với sự hỗ trợ của Claude Code CLI.
# Cài đặt PostgreSQL
sudo apt update
sudo apt install -y postgresql postgresql-contrib
# Kiểm tra trạng thái
sudo systemctl status postgresql
# Đăng nhập vào PostgreSQL
sudo -u postgres psql
-- Tạo user mới
CREATE USER myapp WITH PASSWORD 'secure_password_here';
-- Tạo database
CREATE DATABASE myapp_db OWNER myapp;
-- Cấp quyền
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp;
-- Cho phép tạo schema
\c myapp_db
GRANT ALL ON SCHEMA public TO myapp;
# docker-compose.yml
version: "3.8"
services:
postgres:
image: postgres:16-alpine
container_name: myapp-postgres
environment:
POSTGRES_USER: myapp
POSTGRES_PASSWORD: secure_password_here
POSTGRES_DB: myapp_db
ports:
- "5432:5432"
volumes:
- postgres_data:/var/lib/postgresql/data
- ./init.sql:/docker-entrypoint-initdb.d/init.sql
healthcheck:
test: ["CMD-SHELL", "pg_isready -U myapp -d myapp_db"]
interval: 10s
timeout: 5s
retries: 5
volumes:
postgres_data:
# Khởi chạy
docker compose up -d
# Kiểm tra kết nối
docker exec -it myapp-postgres psql -U myapp -d myapp_db
# Định dạng chuẩn
DATABASE_URL="postgresql://user:password@host:port/database?schema=public"
# Ví dụ
DATABASE_URL="postgresql://myapp:secure_password@localhost:5432/myapp_db?schema=public"
# Với SSL
DATABASE_URL="postgresql://myapp:secure_password@db.example.com:5432/myapp_db?sslmode=require"
deleted_at thay vì xóa vĩnh viễncreated_at và updated_atThiết kế PostgreSQL schema cho hệ thống quản lý bán hàng (POS) với:
Các bảng chính:
- stores (cửa hàng, có thể nhiều chi nhánh)
- users (nhân viên, có role: admin, manager, cashier)
- categories (danh mục sản phẩm, hỗ trợ nested)
- products (sản phẩm, có variants)
- inventory (tồn kho theo cửa hàng)
- orders (đơn hàng)
- order_items (chi tiết đơn hàng)
- customers (khách hàng thân thiết)
- payments (thanh toán, hỗ trợ nhiều phương thức)
Yêu cầu:
- UUID cho primary keys
- Soft delete cho products và customers
- Audit trail (ai tạo, ai sửa)
- Indexes phù hợp
- Check constraints cho giá và số lượng
- Viết SQL thuần (không ORM)
-- Bật extension UUID
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Bảng users
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
full_name VARCHAR(100) NOT NULL,
role VARCHAR(20) NOT NULL DEFAULT 'user'
CHECK (role IN ('admin', 'manager', 'user')),
is_active BOOLEAN NOT NULL DEFAULT true,
last_login_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Bảng products
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
name VARCHAR(255) NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
description TEXT,
price DECIMAL(12, 2) NOT NULL CHECK (price >= 0),
compare_at_price DECIMAL(12, 2) CHECK (compare_at_price >= 0),
sku VARCHAR(100) UNIQUE,
stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
category_id UUID REFERENCES categories(id),
is_published BOOLEAN NOT NULL DEFAULT false,
created_by UUID REFERENCES users(id),
deleted_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Index cho tìm kiếm
CREATE INDEX idx_products_name ON products USING gin(to_tsvector('vietnamese', name));
CREATE INDEX idx_products_category ON products(category_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_products_price ON products(price) WHERE deleted_at IS NULL;
-- Trigger tự động cập nhật updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER trigger_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION update_updated_at();
// prisma/schema.prisma
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model User {
id String @id @default(uuid())
email String @unique
passwordHash String @map("password_hash")
fullName String @map("full_name")
role Role @default(USER)
isActive Boolean @default(true) @map("is_active")
lastLoginAt DateTime? @map("last_login_at")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
products Product[]
orders Order[]
@@map("users")
}
model Product {
id String @id @default(uuid())
name String
slug String @unique
description String?
price Decimal @db.Decimal(12, 2)
stockQuantity Int @default(0) @map("stock_quantity")
categoryId String? @map("category_id")
isPublished Boolean @default(false) @map("is_published")
createdBy String? @map("created_by")
deletedAt DateTime? @map("deleted_at")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
category Category? @relation(fields: [categoryId], references: [id])
creator User? @relation(fields: [createdBy], references: [id])
images ProductImage[]
@@index([categoryId])
@@index([price])
@@map("products")
}
enum Role {
ADMIN
MANAGER
USER
}
# Các lệnh Prisma thường dùng
npx prisma migrate dev --name add_products # Tạo và chạy migration
npx prisma migrate deploy # Deploy migration lên production
npx prisma db push # Đẩy schema (không tạo migration)
npx prisma studio # Mở giao diện quản lý
npx prisma generate # Tạo client
npx prisma db seed # Chạy seed data
// models/user.go
package models
import (
"time"
"github.com/google/uuid"
"gorm.io/gorm"
)
type User struct {
ID uuid.UUID `gorm:"type:uuid;primary_key;default:uuid_generate_v4()"`
Email string `gorm:"uniqueIndex;not null"`
PasswordHash string `gorm:"column:password_hash;not null"`
FullName string `gorm:"column:full_name;not null"`
Role string `gorm:"default:user;check:role IN ('admin','manager','user')"`
IsActive bool `gorm:"default:true"`
LastLoginAt *time.Time `gorm:"column:last_login_at"`
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt gorm.DeletedAt `gorm:"index"`
}
// Kết nối database
func ConnectDB() (*gorm.DB, error) {
dsn := os.Getenv("DATABASE_URL")
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
})
if err != nil {
return nil, err
}
// Connection pool
sqlDB, _ := db.DB()
sqlDB.SetMaxIdleConns(10)
sqlDB.SetMaxOpenConns(100)
sqlDB.SetConnMaxLifetime(time.Hour)
return db, nil
}
// repository/user_repo.go
package repository
import (
"context"
"github.com/jmoiron/sqlx"
_ "github.com/lib/pq"
)
type UserRepo struct {
db *sqlx.DB
}
func NewUserRepo(db *sqlx.DB) *UserRepo {
return &UserRepo{db: db}
}
func (r *UserRepo) FindByEmail(ctx context.Context, email string) (*User, error) {
var user User
err := r.db.GetContext(ctx, &user,
`SELECT id, email, full_name, role, is_active
FROM users
WHERE email = $1 AND is_active = true`,
email,
)
if err != nil {
return nil, err
}
return &user, nil
}
func (r *UserRepo) ListWithPagination(ctx context.Context, page, limit int) ([]User, int, error) {
var total int
err := r.db.GetContext(ctx, &total,
`SELECT COUNT(*) FROM users WHERE is_active = true`,
)
if err != nil {
return nil, 0, err
}
var users []User
offset := (page - 1) * limit
err = r.db.SelectContext(ctx, &users,
`SELECT id, email, full_name, role, created_at
FROM users
WHERE is_active = true
ORDER BY created_at DESC
LIMIT $1 OFFSET $2`,
limit, offset,
)
return users, total, err
}
# Tạo migration mới
npx prisma migrate dev --name add_order_status
# Xem trạng thái migration
npx prisma migrate status
# Reset database (cẩn thận!)
npx prisma migrate reset
# Deploy lên production
npx prisma migrate deploy
# Cài đặt
go install -tags "postgres" github.com/golang-migrate/migrate/v4/cmd/migrate@latest
# Tạo migration
migrate create -ext sql -dir migrations -seq add_orders_table
# Chạy migration
migrate -database "${DATABASE_URL}" -path migrations up
# Rollback 1 bước
migrate -database "${DATABASE_URL}" -path migrations down 1
# Kiểm tra version
migrate -database "${DATABASE_URL}" -path migrations version
-- migrations/000002_add_orders_table.up.sql
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL REFERENCES users(id),
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'confirmed', 'shipping', 'delivered', 'cancelled')),
total_amount DECIMAL(12, 2) NOT NULL CHECK (total_amount >= 0),
shipping_address JSONB,
note TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
-- migrations/000002_add_orders_table.down.sql
DROP TABLE IF EXISTS orders;
Tạo migration thêm bảng "reviews" vào database hiện tại:
- Liên kết với users và products
- Rating (1-5 sao)
- Tiêu đề và nội dung review
- Hình ảnh đính kèm (JSONB array)
- Trạng thái duyệt (pending, approved, rejected)
- Cần index cho product_id, user_id, và rating
Tạo cả file up và down migration.
-- Phân tích query plan
EXPLAIN ANALYZE
SELECT p.*, c.name as category_name
FROM products p
LEFT JOIN categories c ON c.id = p.category_id
WHERE p.deleted_at IS NULL
AND p.is_published = true
AND p.price BETWEEN 100000 AND 500000
ORDER BY p.created_at DESC
LIMIT 20;
Phân tích và tối ưu query sau. Dữ liệu hiện có:
- Bảng orders: 2 triệu records
- Bảng order_items: 10 triệu records
- Bảng products: 50,000 records
Query hiện tại chạy mất 15 giây:
SELECT
p.name,
COUNT(oi.id) as total_sold,
SUM(oi.quantity * oi.price) as revenue
FROM products p
JOIN order_items oi ON oi.product_id = p.id
JOIN orders o ON o.id = oi.order_id
WHERE o.created_at >= '2024-01-01'
AND o.status = 'delivered'
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 20;
Hãy:
1. Phân tích vấn đề hiệu suất
2. Đề xuất indexes cần thêm
3. Viết lại query tối ưu hơn
4. Xem xét materialized view nếu cần
-- 1. Dùng CTE thay vì subquery lồng nhau
WITH monthly_sales AS (
SELECT
date_trunc('month', created_at) as month,
SUM(total_amount) as revenue,
COUNT(*) as order_count
FROM orders
WHERE status = 'delivered'
GROUP BY date_trunc('month', created_at)
)
SELECT * FROM monthly_sales ORDER BY month DESC;
-- 2. Partial index cho điều kiện phổ biến
CREATE INDEX idx_orders_active ON orders(created_at DESC)
WHERE status NOT IN ('cancelled', 'refunded');
-- 3. Covering index (tránh table lookup)
CREATE INDEX idx_products_listing ON products(category_id, price)
INCLUDE (name, slug, stock_quantity)
WHERE deleted_at IS NULL AND is_published = true;
-- 4. Materialized view cho báo cáo
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
date_trunc('day', o.created_at) as sale_date,
COUNT(*) as order_count,
SUM(o.total_amount) as revenue,
AVG(o.total_amount) as avg_order_value
FROM orders o
WHERE o.status = 'delivered'
GROUP BY date_trunc('day', o.created_at);
-- Refresh hàng ngày
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;
-- 1. B-tree (mặc định) — tốt cho =, <, >, BETWEEN, ORDER BY
CREATE INDEX idx_users_email ON users(email);
-- 2. Hash — chỉ tốt cho = (equality)
CREATE INDEX idx_users_email_hash ON users USING hash(email);
-- 3. GIN — tốt cho full-text search, JSONB, array
CREATE INDEX idx_products_search ON products
USING gin(to_tsvector('vietnamese', name || ' ' || COALESCE(description, '')));
-- 4. GiST — tốt cho geometric, range, full-text
CREATE INDEX idx_stores_location ON stores USING gist(location);
-- 5. Composite index — nhiều cột
CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at DESC);
-- 6. Partial index — chỉ index subset
CREATE INDEX idx_products_published ON products(price, created_at)
WHERE is_published = true AND deleted_at IS NULL;
-- 7. Expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Cài đặt unaccent extension
CREATE EXTENSION IF NOT EXISTS unaccent;
-- Tìm kiếm sản phẩm tiếng Việt
SELECT * FROM products
WHERE to_tsvector('simple', unaccent(name))
@@ to_tsquery('simple', unaccent('điện thoại'));
-- Index cho tìm kiếm tiếng Việt
CREATE INDEX idx_products_fts ON products
USING gin(to_tsvector('simple', unaccent(name)));
Phân tích indexes hiện tại của bảng "orders" và đề xuất cải thiện:
Bảng orders có 2 triệu records với các cột:
- id, user_id, status, total_amount, shipping_address (JSONB)
- payment_method, note, created_at, updated_at
Các query thường chạy:
1. Lọc theo user_id + status, sắp xếp theo created_at DESC
2. Tìm kiếm trong shipping_address->>'city'
3. Báo cáo doanh thu theo ngày/tháng
4. Đếm đơn hàng theo status
Hãy đề xuất indexes tối ưu và giải thích lý do.
# Backup toàn bộ database (custom format — nén)
pg_dump -h localhost -U myapp -d myapp_db -Fc -f backup_$(date +%Y%m%d_%H%M%S).dump
# Backup chỉ schema
pg_dump -h localhost -U myapp -d myapp_db --schema-only -f schema.sql
# Backup chỉ data
pg_dump -h localhost -U myapp -d myapp_db --data-only -f data.sql
# Backup bảng cụ thể
pg_dump -h localhost -U myapp -d myapp_db -t orders -t order_items -Fc -f orders_backup.dump
# Restore từ custom format
pg_restore -h localhost -U myapp -d myapp_db -c backup.dump
# Restore từ SQL file
psql -h localhost -U myapp -d myapp_db < schema.sql
#!/bin/bash
# scripts/backup-db.sh
# Cấu hình
DB_HOST="localhost"
DB_USER="myapp"
DB_NAME="myapp_db"
BACKUP_DIR="/backups/postgresql"
RETENTION_DAYS=30
# Tạo thư mục backup
mkdir -p "$BACKUP_DIR"
# Tên file backup
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"
# Thực hiện backup
echo "Bắt đầu backup database $DB_NAME..."
pg_dump -h "$DB_HOST" -U "$DB_USER" -d "$DB_NAME" -Fc -f "$BACKUP_FILE"
if [ $? -eq 0 ]; then
echo "Backup thành công: $BACKUP_FILE"
# Nén thêm
gzip "$BACKUP_FILE"
echo "Đã nén: ${BACKUP_FILE}.gz"
else
echo "Backup thất bại!"
exit 1
fi
# Xóa backup cũ
echo "Xóa backup cũ hơn $RETENTION_DAYS ngày..."
find "$BACKUP_DIR" -name "*.dump.gz" -mtime +$RETENTION_DAYS -delete
echo "Hoàn tất!"
# Thêm vào crontab — backup hàng ngày lúc 2 giờ sáng
crontab -e
# 0 2 * * * /path/to/scripts/backup-db.sh >> /var/log/db-backup.log 2>&1
# Cài đặt
sudo apt install -y pgbouncer
# Cấu hình
sudo nano /etc/pgbouncer/pgbouncer.ini
; /etc/pgbouncer/pgbouncer.ini
[databases]
myapp_db = host=localhost port=5432 dbname=myapp_db
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; Chế độ pooling
pool_mode = transaction
; Giới hạn kết nối
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
; Timeout
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout = 30
; Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60
# Khởi động PgBouncer
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer
# Kiểm tra
psql -h 127.0.0.1 -p 6432 -U myapp myapp_db
services:
postgres:
image: postgres:16-alpine
environment:
POSTGRES_USER: myapp
POSTGRES_PASSWORD: secret
POSTGRES_DB: myapp_db
volumes:
- postgres_data:/var/lib/postgresql/data
pgbouncer:
image: edoburu/pgbouncer
environment:
DATABASE_URL: "postgres://myapp:secret@postgres:5432/myapp_db"
POOL_MODE: transaction
MAX_CLIENT_CONN: 1000
DEFAULT_POOL_SIZE: 25
ports:
- "6432:5432"
depends_on:
- postgres
Ứng dụng kết nối qua PgBouncer thay vì trực tiếp:
DATABASE_URL="postgresql://myapp:secret@localhost:6432/myapp_db"
-- Kiểm tra kết nối hiện tại
SELECT
datname,
usename,
state,
COUNT(*) as connections
FROM pg_stat_activity
GROUP BY datname, usename, state
ORDER BY connections DESC;
-- Top 10 query chậm nhất
SELECT
mean_exec_time::decimal(10,2) as avg_ms,
calls,
total_exec_time::decimal(10,2) as total_ms,
query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- Kích thước bảng
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) as table_size,
pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) as index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;
-- Indexes không được sử dụng
SELECT
schemaname,
tablename,
indexname,
idx_scan as times_used,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- Cache hit ratio (nên > 99%)
SELECT
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as cache_hit_ratio
FROM pg_statio_user_tables;
-- Trong postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
-- Sau khi restart PostgreSQL
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Reset thống kê
SELECT pg_stat_statements_reset();
Thiết kế database PostgreSQL cho hệ thống quản lý bệnh viện:
Yêu cầu nghiệp vụ:
- Quản lý bệnh nhân (thông tin cá nhân, lịch sử khám)
- Quản lý bác sĩ (chuyên khoa, lịch làm việc)
- Đặt lịch khám
- Bệnh án điện tử
- Kê đơn thuốc
- Thanh toán và bảo hiểm
Yêu cầu kỹ thuật:
- UUID cho ID
- Soft delete
- Audit trail
- Full-text search cho bệnh nhân
- JSONB cho dữ liệu linh hoạt
- Viết Prisma schema
Database đang chạy chậm. Đây là output của pg_stat_statements cho 5 query chậm nhất:
[dán output vào đây]
Và đây là danh sách indexes hiện tại:
[dán output \di+ vào đây]
Hãy phân tích và đề xuất:
1. Indexes cần thêm/xóa
2. Query cần viết lại
3. Cấu hình PostgreSQL cần thay đổi
4. Có cần materialized view không
Tôi cần migration để:
1. Tách bảng "users" thành "users" và "user_profiles"
2. Di chuyển các cột profile sang bảng mới
3. Thêm foreign key
4. Cập nhật tất cả code liên quan
5. Không được downtime
Viết migration an toàn với rollback plan.
Tạo seed data cho database e-commerce:
- 10 categories
- 100 products với giá VNĐ thực tế
- 50 users
- 200 orders với trạng thái khác nhau
- Reviews cho sản phẩm
Dùng Prisma seed hoặc SQL INSERT.
Dữ liệu phải realistic (tên tiếng Việt, địa chỉ VN, giá VNĐ).
Gợi ý tiếp theo: