PostgreSQL 17新特性全面解析

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 启用并行查询
SET max_parallel_workers_per_gather = 4;
SET parallel_tuple_cost = 0.1;
SET parallel_setup_cost = 1000.0;

-- 复杂查询自动并行执行
EXPLAIN (ANALYZE, BUFFERS)
SELECT
    o.order_id,
    c.customer_name,
    SUM(oi.quantity * oi.price) as total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2024-01-01'
GROUP BY o.order_id, c.customer_name
HAVING SUM(oi.quantity * oi.price) > 1000;

-- 并行创建索引
CREATE INDEX CONCURRENTLY idx_orders_customer_date
ON orders(customer_id, order_date)
WITH (parallel_workers = 4);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 新的VACUUM选项
VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) large_table;

-- 并行VACUUM
ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE large_table SET (autovacuum_vacuum_cost_delay = '10ms');
ALTER TABLE large_table SET (autovacuum_vacuum_cost_limit = 200);

-- 监控VACUUM进度
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 新的SQL/JSON路径表达式
SELECT
    jsonb_path_query_array(
        data,
        '$.items[*] ? (@.price > 100)'
    ) as expensive_items
FROM products;

-- JSON_TABLE函数
SELECT *
FROM jsonb_table(
    '{"orders": [{"id": 1, "items": [{"name": "A", "qty": 2}]}]}',
    '$.orders[*]' COLUMNS (
        order_id int PATH '$.id',
        item_count int PATH '$.items.size()'
    )
);

-- JSON_EXISTS函数
SELECT
    product_id,
    product_name
FROM products
WHERE jsonb_exists(
    attributes,
    '$.details ? (@.warranty == true)'
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- 新的JSON聚合函数
SELECT
    customer_id,
    jsonb_agg(
        jsonb_build_object(
            'order_id', order_id,
            'total', total_amount,
            'date', order_date
        ) ORDER BY order_date
    ) as orders
FROM orders
GROUP BY customer_id;

-- JSON_OBJECTAGG
SELECT
    json_object_agg(
        product_name,
        json_build_object('price', price, 'stock', stock)
    ) as product_catalog
FROM products;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 完整的MERGE支持
MERGE INTO target_table t
USING source_table s
ON t.id = s.id
WHEN MATCHED AND s.status = 'deleted' THEN
    DELETE
WHEN MATCHED AND t.updated_at < s.updated_at THEN
    UPDATE SET
        name = s.name,
        price = s.price,
        updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (id, name, price, created_at)
    VALUES (s.id, s.name, s.price, CURRENT_TIMESTAMP);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 原生分区修剪
CREATE TABLE orders (
    order_id BIGINT,
    order_date DATE,
    customer_id INTEGER,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

-- 自动创建分区
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- 分区裁剪优化
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-02-01' AND '2024-02-29';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- 增量排序支持
CREATE INDEX idx_products_name_price
ON products(name ASC NULLS LAST, price DESC);

-- INCLUDE索引优化
CREATE INDEX idx_orders_customer_date
ON orders(customer_id)
INCLUDE (order_date, total_amount);

-- 索引使用统计
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 并行创建索引
CREATE INDEX CONCURRENTLY idx_large_table_column
ON large_table(column)
WITH (parallel_workers = 4);

-- 监控索引创建进度
SELECT
    pid,
    phase,
    tuples_done,
    tuples_total
FROM pg_stat_progress_create_index;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 发布表的部分数据
CREATE PUBLICATION sales_publication
FOR TABLE orders
WHERE (order_date >= '2024-01-01');

-- 行过滤订阅
CREATE SUBSCRIPTION sales_subscription
CONNECTION 'host=replica dbname=sales'
PUBLICATION sales_publication
WITH (slot_name = sales_slot, create_slot = false);

-- 复制冲突解决
ALTER SUBSCRIPTION sales_subscription SET (skip_transaction = 'all');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 配置同步复制
ALTER SYSTEM SET synchronous_commit = 'remote_apply';
ALTER SYSTEM SET synchronous_standby_names = 'standby1,standby2';

-- 监控复制延迟
SELECT
    application_name,
    state,
    sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag_bytes
FROM pg_stat_replication;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 角色层次结构
CREATE ROLE read_only;
CREATE ROLE read_write INHERIT;
GRANT read_only TO read_write;

-- 动态权限管理
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;

-- 行级安全策略
CREATE POLICY user_orders_policy ON orders
    FOR ALL TO authenticated_users
    USING (customer_id = current_user_id())
    WITH CHECK (customer_id = current_user_id());

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 字段级加密
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 使用加密函数
INSERT INTO sensitive_data (id, encrypted_data)
VALUES (1, pgp_sym_encrypt('secret information', 'encryption_key'));

-- 索引加密数据
CREATE INDEX idx_encrypted_hash
ON sensitive_data USING hash (md5(encrypted_data));

-- 证书认证配置
ALTER SYSTEM SET ssl_cert_file = '/etc/ssl/certs/server.crt';
ALTER SYSTEM SET ssl_key_file = '/etc/ssl/private/server.key';
SELECT pg_reload_conf();

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 扩展统计信息
CREATE STATISTICS s1 (dependencies, ndistinct) ON customer_id, order_date FROM orders;

-- 查询统计信息
SELECT
    schemaname,
    tablename,
    attname,
    n_distinct,
    correlation
FROM pg_stats
WHERE tablename = 'orders';

-- 实时查询监控
SELECT
    pid,
    now() - query_start as duration,
    query,
    state,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE state = 'active';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- 查询性能分析
EXPLAIN (ANALYZE, BUFFERS, TIMING, SUMMARY, VERBOSE)
SELECT *
FROM large_table
WHERE indexed_column = value;

-- 自动扩展统计
ALTER SYSTEM SET track_activity_query_size = 16384;
ALTER SYSTEM SET log_min_duration_statement = '1000ms';

-- 慢查询日志
SELECT
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- 时序数据表设计
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER NOT NULL,
    metric_name TEXT NOT NULL,
    value DOUBLE PRECISION,
    tags JSONB
) PARTITION BY RANGE (time);

-- 自动分区管理
CREATE OR REPLACE FUNCTION create_monthly_partition(table_name text, start_date date)
RETURNS void AS $$
DECLARE
    partition_name text;
    end_date date;
BEGIN
    partition_name := table_name || '_' || to_char(start_date, 'YYYY_MM');
    end_date := start_date + interval '1 month';

    EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
                    FOR VALUES FROM (%L) TO (%L)',
                   partition_name, table_name, start_date, end_date);
END;
$$ LANGUAGE plpgsql;

-- 时间窗口查询
SELECT
    time_bucket('1 hour', time) as hour,
    device_id,
    metric_name,
    avg(value) as avg_value,
    max(value) as max_value,
    min(value) as min_value
FROM metrics
WHERE time >= now() - interval '24 hours'
GROUP BY hour, device_id, metric_name;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- 全文搜索配置
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 创建全文搜索索引
CREATE INDEX idx_products_search
ON products
USING gin(to_tsvector('english', name || ' ' || description));

-- 高级搜索查询
SELECT
    product_id,
    name,
    description,
    ts_rank(search_vector, query) as rank
FROM products,
     plainto_tsquery('english', 'high performance laptop') as query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

-- 模糊搜索
SELECT *
FROM products
WHERE name % 'laptap'  -- 相似度搜索
ORDER BY similarity(name, 'laptap') DESC;

1
2
3
4
5
6
7
8
9

# 备份数据库
pg_dumpall -U postgres > backup_before_17.sql

# 检查兼容性
pg_upgrade --check \
    --old-bindir=/usr/pgsql-16/bin \
    --new-bindir=/usr/pgsql-17/bin \
    --old-datadir=/var/lib/pgsql/16/data \
    --new-datadir=/var/lib/pgsql/17/data

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 停止服务
systemctl stop postgresql-16

# 执行升级
pg_upgrade \
    --old-bindir=/usr/pgsql-16/bin \
    --new-bindir=/usr/pgsql-17/bin \
    --old-datadir=/var/lib/pgsql/16/data \
    --new-datadir=/var/lib/pgsql/17/data \
    --old-port=5432 \
    --new-port=5433

# 启动新版本
systemctl start postgresql-17

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 配置建议
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;

-- 重新加载配置
SELECT pg_reload_conf();

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_buffercache;

-- 监控查询
SELECT
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted
FROM pg_stat_database
WHERE datname not in ('template0', 'template1');