lvorySQL 2025生态大会暨PostgreSQL高峰论坛：PG18可观测性增强-解锁精细化运维新特性

PG18可观测性增强解锁精细化运维新特性IvorySQL

2025生态大会暨PostgreSQL高峰论坛概述•

监控增强•

I/O监控•

表级监控•

后台进程监控•

进度监控•

诊断能力提升•

EXPLAIN(VACUUM/ANALYZE)•

内存•日志增强IvorySQL

2025生态大会暨PostgreSQL高峰论坛I/O监控•I/O统计精度从块升级为字节，

pg_stat_io视图增加字段•

read_bytes•

write_bytes•

extend_bytes•

多维度I/O统计•relation,•temp_relation•

wal•

进程级I/O统计•

pg_stat_get_backend_io(pid)•

pg_stat_get_backend_wal(pid)•

可重置统计信息•

pg_stat_reset_backendIvorySQL

2025生态大会暨PostgreSQL高峰论坛表级监控•

pg_stat_all_tables增加耗时统计•total_vacuum_time

--

手动VACUUM总耗时•total_autovacuum_time

--

自动VACUUM总耗时•

total_analyze_time

--

手动ANALYZE总耗时•total_autoanalyze_time

--

自动ANALYZE总耗时IvorySQL

2025生态大会暨PostgreSQL高峰论坛后台进程监控•pg_stat_checkpointer视图增加监控项•num_done：

--

完成的检查点数•

sl

ru_written：

--

SLRU缓冲区写入数•write_time：

--

共享缓冲区写入耗时•

sync_time：

--

同步总耗时•

pg_stat_database视图增加并行统计•parallel_workers_to_launch

--

计划启动的并行worker数•parallel_workers_launched

--

实际启动的并行worker数IvorySQL

2025生态大会暨PostgreSQL高峰论坛进度监控vacuum/analyze增加延迟统计•pg_stat_progress_vacuum视图增加延迟统计•

delay_time•pg_stat_progress_analyze视图增加延迟统计•

delay_time•track_cost_delay_timing参数•

on

--

打开延迟统计•

off

--

关闭延迟统计（默认）•

相关参数•vacuum_cost_delay•vacuum_cost_limit•vacuum_cost_page_dirty•vacuum_cost_page_hit•

vacuum_cost_page_missIvorySQL

2025生态大会暨PostgreSQL高峰论坛PgStatIvorySQL

2025生态大会暨PostgreSQL高峰论坛1.

WAL:

records=3

bytes=1234

buffers

full=02.

Buffers:

shared

hit=22

read=33.

Index

Searches:

104.

Storage:

Memory

Maximum

Storage:

1024kB5.

Worker

0:

Heap

Blocks:

exact=60

lossy=46.

Disabled:

true7.

Window:

(PARTITION

BY

category

ORDER

BY

value)3.

显示每个索引节点扫描的次数4.

为Material、Window

Aggregate、CTE节点添加内存磁盘使用详情5.

为Parallel

Bitmap

Heap

S

can添加worker缓存统计1.

增加WAL缓冲区满的次数2.

默认包含BUFFERS输出

ANALYZE

EXPLAIN7.

显示窗口函数的参数IvorySQL

2025生态大会暨PostgreSQL高峰论坛EXPLAIN6.

指示禁用的节点

WAL

EXPLAINexplain

(analyze,verbose,wal)

select

count(*)

over

(partition

by

category)

from

test_data

where

id>10000;WindowAggOutput:

count(*)

OVER

w1,

categoryWindow:

w1

AS

(PARTITION

BY

test_data.category)Storage:

Memory

Maximu

Storage:

17kBBuffers:

shared

hit=28839

read=1584I/O

Timings:

shared

read=187.374->

Index

Scan

using

idx_test_data_category

on

public.test_dataOutput:

categoryFilter:

(test_data.id

>

10000)Rows

Removed

by

Filter:

10000Index

Searches:

1Buffers:

shared

hit=28839

read=1584I/O

Timings:

shared

read=187.374IvorySQL

2025生态大会暨PostgreSQL高峰论坛GatherOutput:

id,

category,

valueWorkers

Planned:

2Workers

Launched:

2Buffers:

shared

hit=6700->

Parallel

Bitmap

Heap

S

can

on

public.test_parallel

Output:

id,

category,

valueRecheck

Cond:

(test_parallel.category

=

ANY

('{1,2,3,4,5}'::integer[]))Rows

Removed

by

Index

Recheck:

172699Heap

Blocks:

exact=123

lossy=1463Buffers:

shared

hit=6700Worker

0:

actual

time=74.481..488.886

rows=207004.00

loops=1Heap

Blocks:

exact=209

lossy=2129Buffers:

shared

hit=2358Worker

1:

actual

time=76.028..491.388

rows=202855.00

loops=1Heap

Blocks:

exact=202

lossy=2076EXPLAINEXPLAIN(ANALYZE,VERBOSE)SELECT

*

FROM

test_parallelWHERE

category

IN

(1,

2,

3,

4,

5);IvorySQL

2025生态大会暨PostgreSQL高峰论坛1.

I/O

timings:

read:

0.200

ms,

write:

0.000

ms2.

avg

read

rate:

0.006

MB/s,

avg

write

rate:

0.019

MB/s3.

WAL

usage:

10

records,

3

full

page

images,

19352

bytes,

0

buffers

full4.

system

usage:

CPU:

user:

1.16

s,

system:

0.09

s,

elapsed:

1.26

sVACUUM/ANALYZE1.

I/O2.

平均读统计3.

WAL4.

CPU

VERBOAE

ANALYZEVACUUMIvorySQL

2025生态大会暨PostgreSQL高峰论坛VACUUM/ANALYZEpostgres=#

analyze

verbose

test_data;INFO:

analyzing

"public.test_data"INFO:

"test_data":

scanned

915

of

915

pages,

containing

110010

live

rows

and

1dead

rows;

30000

rows

in

sample,

110010

estimated

total

rowsINFO:

finished

analyzing

table

"postgres.public.test_data"I/O

timings:

read:

0.200

ms,

write:

0.000

msavg

read

rate:

0.006

MB/s,

avg

write

rate:

0.019

MB/sbuffer

usage:

1236

hits,

1

reads,

3

dirtiedWAL

usage:

10

records,

3

full

page

images,

19352

bytes,

0

buffers

fullsystem

usage:

CPU:

user:

1.16

s,

system:

0.09

s,

elapsed:

1.26

sANALYZEIvorySQL

2025生态大会暨PostgreSQL高峰论坛内存诊断•跨进程内存上下文统计信息查询pg_get_process_memory_contexts(pid,summary,timeout)•pg_backend_memory_contexts视图增强•添加path列显示内存上下文的层次路径•添加type列显示内存上下文类型•

level列改为从1开始（之前从0开始）•移除parent列（被path替代）IvorySQL

2025生态大会暨PostgreSQL高峰论坛内存诊断内存监控进程化pg_get_process_memory_conte

xts函数原理。IvorySQL

2025生态大会暨PostgreSQL高峰论坛日志增强•log_connections从布尔值扩展为支持多种连接阶段的细粒度日志记录•

on•

off（默认）•

receipt•

authentication•

authorization•

setup_durations•all•新增log_lock_failure，记录锁获取失败的详细信息(SELECT

...

NOWAIT)•log_line_prefix新增%L，在日志中显示服务器IP地址IvorySQL

2025生态大会暨PostgreSQL高峰论坛日志增强--

日志输出示例2025-06-23

10:53:06.377

CST

[92142]

LOG:

connection

received:

host=10.11.12.10

port=409702025-06-23

10:53:06.380

CST

[92142]

LOG:

connection

authenticated:

user="postgres"

method=trust

(/home/postgres/pgsql/data/pg_hba.conf:120)2025-06-23

10:53:06.380

CST

[92142]

LOG:

connection

authorized:

user=postgres

database=postgres

application_name=psql2025-06-23

10:53:06.384

CST

[92142]

LOG:

connection

ready:

setup

total=8.442

ms,

fork=0.808

ms,

authentication=0.754

ms--

日志输出示例2025-06-23

10:56:53.804

CST

[92142]

LOG:

process

92142

could

not

obtain

ShareLock

on

transaction

8072025-06-23

10:56:53.804

CST

[92142]

DETAIL:

Process

holding

the

lock:

92085,

Waitqueue:

.2025-06-23

10:56:53.804

CST

[92142]

STATEMENT:

select

*

from

t

for

update

nowait;2025-06-23

10:56:53.804

CST

[92142]

ERROR:

could

not

obtain

lock

on

row

in

relation

"t"2025-06-23

10:56:53.804

CST

[92142]

STATEMENT:

select

*

from

t

for

update

nowait;IvorySQL

2025生态大会暨PostgreSQL高峰论坛实践慢SQL诊断--

1.

识别慢查询SELECT

pid,

query,

query_start,

stateFROM

pg_stat_activityWHERE

state

=

'active'

AND

now()

-

query_start

>

interval

'5

minutes';--

2.

分析I/O情况SELECT

*

FROM

pg_stat_get_backend_io(pid);--

3.

分析内存使用SELECT

*

FROM

pg_get_process_memory_contexts(pid,

false,

10.0);--

4.

详细执行计划EXPLAIN

(ANALYZE,

VERBOSE,

BUFFERS)

<slow_query>;IvorySQL

2025生态大会暨PostgreSQL高峰论坛实践性能测试--

1.

重置统计SELECT

pg_stat_reset_shared('io');SELECT

pg_stat_reset_backend_stats(pg_backend_pid());--

2.

执行测试--

...

执行测试SQL

...--

3.

收集结果SELECT

*

FROM

pg_stat_get_backend_io(pg_backend_pid());SELECT

*

FROM

pg_s