Edit

持久化数据库

版本: v1.0 最后更新: 2025-10-07

PostgreSQL 配置

核心业务数据

PostgreSQL 是 Zanbara 的核心数据库，存储所有关键业务数据。

适用场景

  • 用户账户（余额、保证金）

  • 订单记录（历史订单）

  • 持仓数据（仓位状态）

  • 清算记录（审计追溯）

  • 结算批次

数据库架构

PostgreSQL 16
├─ 主库 (Master) - 8C32G
│  └─ 写入 + 读取
│
├─ 从库1 (Replica) - 8C32G
│  └─ 只读查询
│
└─ 从库2 (Replica) - 8C32G
   └─ 备份 + 分析查询

Schema 设计示例

-- 用户表
CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wallet_address VARCHAR(44) UNIQUE NOT NULL,
    balance NUMERIC(20, 6) NOT NULL DEFAULT 0,
    margin_balance NUMERIC(20, 6) NOT NULL DEFAULT 0,
    trade_count INTEGER NOT NULL DEFAULT 0,
    total_volume NUMERIC(30, 6) NOT NULL DEFAULT 0,
    kyc_level VARCHAR(20) DEFAULT 'NONE',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_wallet ON users(wallet_address);
CREATE INDEX idx_users_created ON users(created_at DESC);

-- 订单表 (按月分区)
CREATE TABLE orders (
    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(user_id),
    symbol VARCHAR(20) NOT NULL,
    side VARCHAR(10) NOT NULL,
    order_type VARCHAR(20) NOT NULL,
    price NUMERIC(20, 6),
    size NUMERIC(20, 8) NOT NULL,
    filled_size NUMERIC(20, 8) NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    leverage SMALLINT NOT NULL,
    metadata JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- 创建月度分区
CREATE TABLE orders_2025_10 PARTITION OF orders
    FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');

CREATE TABLE orders_2025_11 PARTITION OF orders
    FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');

-- 高性能索引
CREATE INDEX idx_orders_user ON orders(user_id, created_at DESC);
CREATE INDEX idx_orders_status ON orders(status, created_at DESC)
    WHERE status IN ('PENDING', 'PARTIAL');
CREATE INDEX idx_orders_symbol ON orders(symbol, created_at DESC);

-- 持仓表
CREATE TABLE positions (
    position_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(user_id),
    symbol VARCHAR(20) NOT NULL,
    side VARCHAR(10) NOT NULL,
    size NUMERIC(20, 8) NOT NULL,
    entry_price NUMERIC(20, 6) NOT NULL,
    liquidation_price NUMERIC(20, 6),
    margin NUMERIC(20, 6) NOT NULL,
    leverage SMALLINT NOT NULL,
    unrealized_pnl NUMERIC(20, 6) DEFAULT 0,
    margin_mode VARCHAR(20) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, symbol)
);

CREATE INDEX idx_positions_user ON positions(user_id);
CREATE INDEX idx_positions_liquidation ON positions(liquidation_price)
    WHERE liquidation_price IS NOT NULL;

-- 成交记录表 (按月分区)
CREATE TABLE trades (
    trade_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL,
    user_id UUID NOT NULL REFERENCES users(user_id),
    symbol VARCHAR(20) NOT NULL,
    side VARCHAR(10) NOT NULL,
    price NUMERIC(20, 6) NOT NULL,
    size NUMERIC(20, 8) NOT NULL,
    fee NUMERIC(20, 6) NOT NULL,
    fee_token VARCHAR(10) NOT NULL,
    is_maker BOOLEAN NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (timestamp);

-- 物化视图：用户统计
CREATE MATERIALIZED VIEW user_stats AS
SELECT
    user_id,
    COUNT(*) as trade_count,
    SUM(size * price) as total_volume,
    AVG(CASE WHEN is_maker THEN fee ELSE NULL END) as avg_maker_fee,
    AVG(CASE WHEN NOT is_maker THEN fee ELSE NULL END) as avg_taker_fee,
    MAX(timestamp) as last_trade_time
FROM trades
GROUP BY user_id;

CREATE UNIQUE INDEX idx_user_stats_user ON user_stats(user_id);

性能优化配置

# postgresql.conf

# 内存配置
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 64MB
maintenance_work_mem = 2GB

# WAL配置
wal_buffers = 16MB
max_wal_size = 4GB
min_wal_size = 1GB

# 检查点配置
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9

# 连接配置
max_connections = 200
superuser_reserved_connections = 3

# 查询优化
random_page_cost = 1.1  # SSD优化
effective_io_concurrency = 200

# 统计信息
shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 2048
pg_stat_statements.track = all

连接池配置 (PgBouncer)

# pgbouncer.ini

[databases]
zanbara = host=postgres-primary port=5432 dbname=zanbara

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
reserve_pool_size = 10
reserve_pool_timeout = 5

server_idle_timeout = 600
server_lifetime = 3600
server_connect_timeout = 15

TimescaleDB 扩展

K线数据存储

-- 启用 TimescaleDB 扩展
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 价格Tick表
CREATE TABLE price_ticks (
    time TIMESTAMPTZ NOT NULL,
    symbol TEXT NOT NULL,
    price NUMERIC(20, 6) NOT NULL,
    volume NUMERIC(20, 8) NOT NULL,
    source TEXT NOT NULL
);

-- 转换为超表（自动分区）
SELECT create_hypertable('price_ticks', 'time');

-- 创建索引
CREATE INDEX idx_price_ticks_symbol_time ON price_ticks(symbol, time DESC);

-- 连续聚合：1分钟K线
CREATE MATERIALIZED VIEW klines_1m
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 minute', time) as bucket,
    symbol,
    first(price, time) as open,
    max(price) as high,
    min(price) as low,
    last(price, time) as close,
    sum(volume) as volume
FROM price_ticks
GROUP BY bucket, symbol
WITH NO DATA;

-- 启用自动刷新（每30秒）
SELECT add_continuous_aggregate_policy('klines_1m',
    start_offset => INTERVAL '1 hour',
    end_offset => INTERVAL '30 seconds',
    schedule_interval => INTERVAL '30 seconds');

-- 数据保留策略（保留90天Tick数据）
SELECT add_retention_policy('price_ticks', INTERVAL '90 days');

-- 压缩策略（7天后压缩）
ALTER TABLE price_ticks SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'symbol',
    timescaledb.compress_orderby = 'time DESC'
);

SELECT add_compression_policy('price_ticks', INTERVAL '7 days');

查询示例

-- 查询最近1小时的SOL-PERP K线（1分钟）
SELECT * FROM klines_1m
WHERE symbol = 'SOL-PERP'
  AND bucket >= NOW() - INTERVAL '1 hour'
ORDER BY bucket DESC;

-- 查询最近24小时交易量最大的交易对
SELECT
    symbol,
    SUM(volume * close) as volume_usd
FROM klines_1m
WHERE bucket >= NOW() - INTERVAL '24 hours'
GROUP BY symbol
ORDER BY volume_usd DESC
LIMIT 10;

ClickHouse 配置

历史数据分析

ClickHouse 用于存储和分析历史数据，不影响核心交易性能。

建表示例

-- 历史交易表
CREATE TABLE trades_history (
    trade_id UUID,
    user_id String,
    symbol String,
    side String,
    price Decimal(20, 6),
    size Decimal(20, 8),
    fee Decimal(20, 6),
    is_maker UInt8,
    timestamp DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (symbol, timestamp)
SETTINGS index_granularity = 8192;

-- 物化视图：每日统计
CREATE MATERIALIZED VIEW trades_daily_stats
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (symbol, date)
AS SELECT
    toDate(timestamp) as date,
    symbol,
    count() as trade_count,
    sum(size * price) as volume,
    avg(price) as avg_price,
    sum(fee) as total_fees
FROM trades_history
GROUP BY date, symbol;

数据导入策略

# 定时ETL：每天凌晨从PostgreSQL导入
clickhouse-client --query="
INSERT INTO trades_history
SELECT * FROM postgresql('postgres-primary:5432', 'zanbara', 'trades', 'user', 'pass')
WHERE timestamp >= today() - INTERVAL 1 DAY
"

数据备份策略

PostgreSQL 备份

# 基础备份（每天）
pg_basebackup -h postgres-primary -D /backup/base-$(date +%Y%m%d) -Fp -Xs -P

# 增量备份（每小时）
pg_dump -h postgres-primary -U postgres zanbara \
    --format=custom \
    --file=/backup/zanbara-$(date +%Y%m%d-%H%M).dump

# 恢复示例
pg_restore -h postgres-replica -U postgres -d zanbara /backup/zanbara-20251007-0300.dump

自动备份脚本

#!/bin/bash
# /scripts/backup-postgres.sh

BACKUP_DIR="/backup/postgres"
RETENTION_DAYS=30

# 创建备份
pg_dump -h postgres-primary -U postgres zanbara \
    --format=custom \
    --file=$BACKUP_DIR/zanbara-$(date +%Y%m%d-%H%M).dump

# 删除旧备份
find $BACKUP_DIR -name "*.dump" -mtime +$RETENTION_DAYS -delete

# 上传到S3
aws s3 sync $BACKUP_DIR s3://zanbara-backups/postgres/

监控指标

关键指标

-- 数据库大小
SELECT pg_size_pretty(pg_database_size('zanbara'));

-- 表大小
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 慢查询
SELECT
    query,
    mean_exec_time,
    calls,
    total_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 连接数
SELECT count(*) FROM pg_stat_activity;

-- 缓存命中率
SELECT
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as cache_hit_ratio
FROM pg_statio_user_tables;

故障排查

常见问题

1. 慢查询

-- 查找锁等待
SELECT * FROM pg_stat_activity WHERE wait_event_type IS NOT NULL;

-- 查找长时间运行的查询
SELECT pid, now() - query_start as duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- 终止查询
SELECT pg_terminate_backend(pid);

2. 连接耗尽

-- 查看当前连接
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

-- 杀掉空闲连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle' AND query_start < NOW() - INTERVAL '30 minutes';

3. 磁盘空间

# 检查表膨胀
SELECT
    schemaname || '.' || tablename as table,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

# VACUUM FULL（释放空间，但会锁表）
VACUUM FULL ANALYZE orders;

相关文档

维护: Zanbara 技术团队

Previous技术栈总览NextPostgreSQL 配置

Last updated