Pgstats
Collects PostgreSQL statistics, and either saves them in CSV files or print them on the stdout
Install / Use
/learn @gleu/PgstatsREADME
README
This repository contains the source of a collection of tools.
pgstat is a vmstat-like tool for PostgreSQL.
pgreport is a reporting tool for PostgreSQL. It tries to get a lot of informations from the metadata and statistics of PostgreSQL.
pgwaitevent gathers every wait event for a specific PID, grouping them by queries.
pgcsvstat outputs PostgreSQL statistics views into CSV files. The idea is that you can load them on any spreadsheet to get the graphs you want.
pgdisplay tries to display a table in an informative way. Still pretty much experimental.
They all should be compatible with the latest PostgreSQL release (18 right now), and down to the oldest stable release (9.5 right now). They may also be compatible with much older releases (8.x for most of them).
Requirements
To compile these tools, you will need the libpq library (.so), the libpgcommon, and libpgport libraries (.a), the PostgreSQL 14+ header files, and the pg_config tool. The header files and the tool are usually available in a -dev package.
To use them once compiled, you only need the libpq library. Any version should be fine.
Compilation
You only have to do:
make
make install
Usage
Use --help to get informations on all command line options for these three tools.
More informations on pgstat
pgstat is an online command tool that connects to a database and grabs its activity statistics. As PostgreSQL has many statistics, you have a command switch to choose the one you want (-s):
- archiver for pg_stat_archiver (9.4+)
- bgwriter for pg_stat_bgwriter
- checkpointer for pg_stat_checkpointer (17+)
- connection for connections by type (9.2+)
- database for pg_stat_database
- table for pg_stat_all_tables
- tableio for pg_statio_all_tables
- index for pg_stat_all_indexes
- function for pg_stat_user_function
- statement for pg_stat_statements
- xlog for xlog writes (9.2+)
- tempfile for temporary file usage
- waitevent for wait events usage (9.6+)
- progress_analyze to get the progress on an ANALYZE statement (13+)
- progress_basebackup to get the progress on a BASE BACKUP (replication) statement (13+)
- progress_cluster to get the progress on a CLUSTER/VACUUM FULL statement (12+)
- progress_createindex to get the progress on a CREATE INDEX statement (12+)
- progress_vacuum to get the progress on a VACUUM statement (9.6+)
- pbpools for pgBouncer pools statistics
- pbstats for pgBouncer general statistics
It looks a lot like vmstat. You ask it the statistics you want, and the frequency to gather these statistics. Just like this:
$ pgstat -s connection
- total - active - lockwaiting - idle in transaction - idle -
1546 15 0 0 1531
1544 17 0 0 1527
1544 14 0 0 1530
1546 26 0 0 1520
1543 21 0 0 1522
Yeah, way too many idle connections. Actually, way too many connections. Definitely needs a pooler there.
This is what happens on a 10-seconds 10-clients pgbench test:
$ pgstat -s database 1
- backends - ------ xacts ------ -------------- blocks -------------- -------------- tuples -------------- ------ temp ------ ------- misc --------
commit rollback read hit read_time write_time ret fet ins upd del files bytes conflicts deadlocks
1 224041 17 24768 2803774 0 0 4684398 234716 2105701 16615 113 1 14016512 0 0
1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 3 0 0 205 0 0 92 92 0 0 0 0 0 0 0
11 20 0 0 500 0 0 1420 184 0 1 0 0 0 0 0
11 69 0 1 4438 0 0 1736 986 68 204 0 0 0 0 0
11 136 0 12 4406 0 0 1767 270 135 405 0 0 0 0 0
11 108 0 0 3434 0 0 1394 214 107 321 0 0 0 0 0
11 96 0 0 3290 0 0 1240 190 95 285 0 0 0 0 0
11 125 0 0 4045 0 0 1620 248 124 372 0 0 0 0 0
11 126 0 0 4222 0 0 1628 250 125 375 0 0 0 0 0
11 111 0 0 3644 0 0 1436 220 110 330 0 0 0 0 0
11 78 0 0 2549 0 0 1918 161 75 225 0 0 0 0 0
11 118 0 0 3933 0 0 1524 234 117 351 0 0 0 0 0
1 130 0 0 4276 0 0 1685 258 129 387 0 0 0 0 0
1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0
You clearly see when it starts, when it stops, and what it did during the 10 seconds. You can filter on a specific database with the -f command line option. Here is what happens at the tables level:
$ pgstat -s table -d b1 1
-- sequential -- ------ index ------ ----------------- tuples -------------------------- -------------- maintenance --------------
scan tuples scan tuples ins upd del hotupd live dead analyze vacuum autovacuum analyze autoanalyze
68553 1467082 264957 266656 7919869 59312 113 57262 4611779 3782 5401 22 10 4 22
3 430 0 0 0 0 0 0 0 0 0 0 0 0 0
3 430 0 0 0 0 0 0 0 0 0 0 0 0 0
231 2351 1116 1222 61 184 0 180 61 124 245 2 0 0 0
431 1750 240 240 120 360 0 358 120 242 480 0 0 0 0
385 1640 220 220 110 330 0 327 110 11 440 0 0 0 0
340 1475 190 190 95 285 0 285 95 189 380 0 0 0 0
398 1651 222 222 111 333 0 331 111 -2 444 0 0 0 0
353 1519 198 198 99 297 0 293 99 200 396 0 0 0 0
335 1453 186 186 93 279 0 274 93 -210 372 0 0 0 0
446 1838 256 256 128 384 0 381 128 104 512 0 0 0 0
425 1739 238 238 119 357 0 354 119 241 476 0 0 0 0
360 1552 204 204 102 306 0 305 102 -10 408 0 0 0 0
386 1629 218 218 109 327 0 325 109 57 436 0 0 0 0
437 1761 242 242 121 363 0 363 121 -292 484 0 0 0 0
373 1563 206 206 103 309 0 305 103 -1 412 0 0 0 0
323 1442 184 184 92 276 0 273 92 188 368 0 0 0 0
412 1706 232 232 116 348 0 346 116 76 464 0 0 0 0
291 1332 164 164 82 246 0 245 82 -216 328 0 0 0 0
189 1013 106 106 53 159 0 158 53 106 212 0 0 0 0
346 1508 196 196 98 294 0 290 98 -18 392 0 0 0 0
304 1376 172 172 86 258 0 258 86 -156 344 0 0 0 0
442 1794 248 248 124 372 0 368 124 -260 496 0 0 0 0
9 1371 157 260 0 13 0 13 -11602 -329 -6053 0 2 0 3
3 430 0 0 0 0 0 0 0 0 0 0 0 0 0
3 430 0 0 0 0 0 0 0 0 0 0 0 0 0
You can also filter by table name with the -f command line switch:
$ pgstat -s table -d b1 -f pgbench_history 1
-- sequential -- ------ index ------ ----------------- tuples ----------------------
Related Skills
feishu-drive
351.2k|
things-mac
351.2kManage Things 3 via the `things` CLI on macOS (add/update projects+todos via URL scheme; read/search/list from the local Things database)
clawhub
351.2kUse the ClawHub CLI to search, install, update, and publish agent skills from clawhub.com
codebase-memory-mcp
1.3kHigh-performance code intelligence MCP server. Indexes codebases into a persistent knowledge graph — average repo in milliseconds. 66 languages, sub-ms queries, 99% fewer tokens. Single static binary, zero dependencies.
