This is the multi-page printable view of this section. .

Return to the regular view of this page.

Article

Original long-form articles on PostgreSQL monitoring, diagnosis, capacity, logging, and the systems around PG Exporter

1: Victoria: The Observability Stack That Slaps the Industry
2: PGCon.Dev 2024, The conf that shutdown PG for a week
3: PostgreSQL Macro Query Optimization with pg_stat_statements
4: 如何用Pigsty监控现有PostgreSQL (RDS/PolarDB/自建)？
5: A Methodology for Diagnosing PostgreSQL Slow Queries
6: Pigsty正式发布
7: Golden Monitoring Metrics: Errors, Latency, Throughput, Saturation
8: PostgreSQL监控系统Pigsty概述
9: PostgreSQL's KPI
10: PostgreSQL监控系统概览
11: Relation Bloat Monitoring and Management
12: Monitoring Table Size in PostgreSQL
13: PostgreSQL Server Log Regular Configuration

This column republishes the original long-form articles from vonng.com behind PG Exporter’s monitoring practice. Each article keeps its original publication date, complete text, authorship, featured image, and inline figures; articles are not merged, condensed, or rewritten into summaries. When no original English edition exists, the default route preserves the Chinese original instead of inventing a translation.

The historical screenshots, commands, product names, and operating assumptions remain part of the original record. For current PG Exporter behavior, use the documentation, Design records, and Release notes.

1 - Victoria: The Observability Stack That Slaps the Industry

I’ve spent the last few weeks preparing Pigsty v4.0. The headliner: ripping out Prometheus + Loki and dropping in the full Victoria stack. VictoriaMetrics is no-frills brute force—it just works and it’s ridiculous. The observability portion is done, so here’s a beta for early testers.

First impressions

Maybe you haven’t heard of VictoriaMetrics, but you definitely know Prometheus. Victoria is Prometheus’ big brother—built by Belarusian wizard Aliaksandr Valialkin. Back at Tantan we tracked ~50 M time series using twelve 64C/256G nodes of Prometheus. I swapped in a three-node distributed Victoria cluster and it didn’t even break a sweat. Later tests showed a single beefy node could handle it. Memory/disk dropped to ¼ of Prometheus; query speed jumped 4×. It blew me away.

Industry benchmarks back it up. VM routinely crushes InfluxDB, Prometheus, TimescaleDB in ingestion throughput and high-cardinality queries.

Pigsty used to ship Prometheus by default and keep VM as a “pro” module. Two things pushed me to refactor:

Grafana Loki/Promtail were aging out. VictoriaLogs was the obvious replacement.
A customer (the film studio behind Movie Hurricane) needed production-grade Victoria. I decided to redo the entire infra layer.

Victoria is a full suite: metrics, logs, traces. So Pigsty v4 rewrites the infra module accordingly.

Why Victoria?

Before performance, let’s talk about the man behind it — Aliaksandr Valialkin (@valyala). Before Victoria he was CTO at ad-tech shop VertaMedia. In Go circles he’s legendary. His fasthttp has 23k stars and is 10× faster than net/http (150M concurrent connections, 200k RPS). His quicktemplate is 20× faster than html/template; fastjson beats encoding/json by 15×.

Common thread: zero allocations on the hot path. That philosophy permeates Victoria. No third-party deps, ruthless memory management, simple architecture with AK‑47 reliability. He also has the swagger to back it up: he publishes benchmarks that faceplant competitors and never blinks.

How strong is Victoria?

We tested on ten nodes ingesting all metrics/logs. Pigsty v4’s VictoriaMetrics + VictoriaLogs consumed 0.2 vCPU and 1GB RAM for the entire stack (Grafana, Alertmanager included). Daily load: 120k time series in 600 MB RAM, 1.1B samples in 440MB storage, 500k log lines in under 6MB.

For comparison, Pigsty v3.7 on the same ten nodes with Prometheus + Loki ate about the same resources in just ten hours—data volume too small to highlight the disparity, but it scales horribly.

Victoria won’t just sip resources—it’s faster queries, better compression, higher cardinality tolerance, and effortless clustering.

Architecture

Pigsty v4 builds a fully distributed Victoria setup: separate ingest/query nodes, replication, HA, plus VictoriaLogs and VictoriaTraces. The stack exposes Grafana dashboards, Alertmanager routes, Nginx ingress, and integrates with existing host/DB exporters.

Even self-monitoring is wired up, and adding your own app metrics is a matter of dropping in config files.

Pigsty is no longer just a PostgreSQL distro—it’s now an observability distro too.

Getting started

We introduced infra.yml, which installs only the Victoria stack (no PostgreSQL/Etcd). Want pure Victoria on any mainstream Linux? Run:

curl https://repo.pigsty.cc/beta | bash
./configure -c infra
./infra.yml

The config is straightforward; add more nodes or replicas as needed.

Everything bootstraps itself:

A three-node install gives three independent replicas out of the box:

Pigsty v4 is still beta, but the Victoria portion is rock solid. Remaining work is dashboard polish and docs. If you want the easiest way to try Victoria, this is it.

v4.0 stable ships January 2026 with full docs and additional features, including Victoria’s native distributed mode.

Final thoughts

Upgrading to Victoria benefited me directly. Opening Grafana and having sub-second, buttery-smooth queries is pure joy. Remember waiting seconds for Loki searches? Never again.

VictoriaMetrics embodies the purest form of open source: a lone expert ships something that dunks on industry giants, releases it under a permissive license, and doesn’t play licensing shell games. No VC puppet strings, no bait-and-switch—just product excellence. More people should know about it and use it.

2 - PGCon.Dev 2024, The conf that shutdown PG for a week

PGCon.Dev, once known as PGCon—the annual must-attend gathering for PostgreSQL hackers and key forum for its future direction, has been held in Ottawa since its inception in 2007.

This year marks a new chapter as the original organizer, Dan, hands over the reins to a new team, and the event moves to SFU’s Harbour Centre in Vancouver, kicking off a new era with grandeur.

How engaging was this event? Peter Eisentraut, member of the PostgreSQL core team, noted that during PGCon.Dev, there were no code commits to PostgreSQL – resulting in the longest pause in twenty years, a whopping week! a historic coding ceasefire! Why? Because all the developers were at the conference!

Considering the last few interruptions, which occurred in the early days of the project twenty years ago,

I’ve been embracing PostgreSQL for a decade, but attending a global PG Hacker conference in person was a first for me, and I’m immensely grateful for the organizer’s efforts. PGCon.Dev 2024 wrapped up on May 31st, though this post comes a bit delayed as I’ve been exploring Vancouver and Banff National Park ;)

Day Zero: Extension Summit

Day zero is for leadership meetings, and I’ve signed up for the afternoon’s Extension Ecosystem Summit.

Maybe this summit is somewhat subtly related to my recent post, “Postgres is eating the database world,” highlighting PostgreSQL’s thriving extension ecosystem as a unique and critical success factor and drawing the community’s attention.

I participated in David Wheeler’s Binary Packing session along with other PostgreSQL community leaders. Despite some hesitation to new standards like PGXN v2 from current RPM/APT maintainers. In the latter half of the summit, I attended a session led by Yurii Rashkovskii, discussing extension directory structures, metadata, naming conflicts, version control, and binary distribution ideas.

Prior to this summit, the PostgreSQL community had held six mini-summits discussing these topics intensely, with visions for the extension ecosystem’s future development shared by various speakers. Recordings of these sessions are available on YouTube.

And after the summit, I had a chance to chat with Devrim, the RPM maintainer, about extension packing, which was quite enlightening.

“Keith Fan Group” – from Devrim on Extension Summit

The core of PGCon.Dev lies in its sessions. Unlike some China domestic conferences with mundane product pitches or irrelevant tech details, PGCon.Dev presentations are genuinely engaging and substantive. The official program kicked off on May 29th, after a day of closed-door leadership meetings and the Ecosystem Summit on the 28th.

The opening was co-hosted by Jonathan Katz, 1 of the 7 core PostgreSQL team members and a chief product manager at AWS RDS, and Melanie Plageman, a recent PG committer from Microsoft. A highlight was when Andres Freund, the developer who uncovered the famous xz backdoor, was celebrated as a superhero on stage.

Following the opening, the regular session tracks began. Although conference videos aren’t out yet, I’m confident they’ll “soon” be available on YouTube. Most sessions had three tracks running simultaneously; here are some highlights I chose to attend.

Pushing the Boundaries of PG Extensions

Yurii’s talk, “Pushing the Boundaries of PG Extensions,” tackled what kind of extension APIs PostgreSQL should offer. PostgreSQL boasts robust extensibility, but the current extension API set is decades old, from the 9.x era. Yurii’s proposal aims to address issues with the existing extension mechanisms. Challenges such as installing multiple versions of an extension simultaneously, avoiding database restarts post-extension installations, managing extensions as seamlessly as data, and handling dependencies among extensions were discussed.

Yurii and Viggy, founders of Omnigres, aim to transform PostgreSQL into a full-fledged application development platform, including hosting HTTP servers directly within the database. They designed a new extension API and management system for PostgreSQL to achieve this. Their innovative improvements represent the forefront of exploration into PostgreSQL’s core extension mechanisms.

I had a great conversation with Viggy and Yurii. Yurii walked me through compiling and installing Omni. I plan to support the Omni extension series in the next version of Pigsty, making this powerful application development framework plug-and-play.

Anarchy in DBMS

Abigale Kim from CMU, under the mentorship of celebrity professor Andy Pavlo, delivered the talk “Anarchy in the Database—A Survey and Evaluation of DBMS Extensibility.” This topic intrigued me since Pigsty’s primary value proposition is about PostgreSQL’s extensibility.

Kim’s research revealed interesting insights: PostgreSQL is the most extensible DBMS, supporting 9 out of 10 extensibility points, closely followed by DuckDB. With over 375+ available extensions, PostgreSQL significantly outpaces other databases.

Kim’s quantitative analysis of compatibility levels among these extensions resulted in a compatibility matrix, unveiling conflicts—most notably, powerful extensions like TimescaleDB and Citus are prone to clashes. This information is very valuable for users and distribution maintainers. Read the detailed study.

I joked with Kim that — now I could brag about PostgreSQL’s extensibility with her research data.

How PostgreSQL is Misused and Abused

The first-afternoon session featured Karen Jex from CrunchyData, an unusual perspective from a user — and a female DBA. Karen shared common blunders by PostgreSQL beginners. While I knew all of what was discussed, it reaffirmed that beginners worldwide make similar mistakes — an enlightening perspective for PG Hackers, who found the session quite engaging.

PostgreSQL and the AI Ecosystem

The second-afternoon session by Bruce Momjian, co-founder of the PGDG and a core committee member from the start, was unexpectedly about using PostgreSQL’s multi-dimensional arrays and queries to implement neural network inference and training.

Haha, some ArgParser code. I see it

During the lunch, Bruce explained that Jonathan Katz needed a topic to introduce the vector database extension PGVector in the PostgreSQL ecosystem, so Bruce was roped in to “fill the gap.”

PB-Level PostgreSQL Deployments

The third afternoon session by Chris Travers discussed their transition from using ElasticSearch for data storage—with a poor experience and high maintenance for 1PB over 30 days retention, to a horizontally scaled PostgreSQL cluster perfectly handling 10PB of data. Normally, PostgreSQL comfort levels on a single machine range from several dozen to a few hundred TB. Deployments at the PB scale, especially at 10PB, even within a horizontally scaled cluster, are exceptionally rare. While the practice itself is standard—partitioning and sharding—the scale of data managed is truly impressive.

Highlight: When Hardware and Database Collide

Undoubtedly, the standout presentation of the event, Margo Seltzer’s talk “When Hardware and Database Collide” was not only the most passionate and compelling talk I’ve attended live but also a highlight across all conferences.

Professor Margo Seltzer, formerly of Harvard and now at UBC, a member of the National Academy of Engineering and the creator of BerkeleyDB, delivered a powerful discourse on the core challenges facing databases today. She pinpointed that the bottleneck for databases has shifted from disk I/O to main memory speed. Emerging hardware technologies like HBM and CXL could be the solution, posing new challenges for PostgreSQL hackers to tackle.

This was a refreshing divergence from China’s typically monotonous academic talks, leaving a profound impact and inspiration. Once the conference video is released, I highly recommend checking out her energizing presentation.

Following Margo’s session, the official Social Event took place at Rogue Kitchen & Wetbar, just a street away from the venue at Waterfront Station, boasting views of the Pacific and iconic Vancouver landmarks.

The informal setting was perfect for engaging with new and old peers. Conversations with notable figures like Devrim, Tomasz, Yurii, and Keith were particularly enriching. As an RPM maintainer, I had an extensive and fruitful discussion with Devrim, resolving many longstanding queries.

The atmosphere was warm and familiar, with many reconnecting after long periods. A couple of beers in, conversations flowed even more freely among fellow PostgreSQL enthusiasts. The event concluded with an invitation from Melanie for a board game session, which I regretfully declined due to my limited English in such interactive settings.

Day 2: Debate, Lunch, and Lighting Talks

Multi-Threading Postgres

The warmth from the previous night’s socializing carried over into the next day, marked by the eagerly anticipated session on “Multi-threaded PostgreSQL,” which was packed to capacity. The discussion, initiated by Heikki, centered on the pros and cons of PostgreSQL’s process and threading models, along with detailed implementation plans and current progress.

The threading model promises numerous benefits: cheaper connections (akin to a built-in connection pool), shared relation and plan caches, dynamic adjustment of shared memory, config changes without restarts, more aggressive Vacuum operations, runtime Explain Analyze, and easier memory usage limits per connection. However, there’s significant opposition, maybe led by Tom Lane, concerned about potential bugs, loss of isolation benefits from the multi-process model, and extensive incompatibilities requiring many extensions to be rewritten.

Heikki laid out a detailed plan to transition to the threading model over five to seven years, aiming for a seamless shift without intermediate states. Intriguingly, he cited Tom Lane’s critical comment in his presentation:

For the record, I think this will be a disaster. There is far too much code that will get broken, largely silently, and much of it is not under our control. – regards, tom lane

Although Tom Lane smiled benignly without voicing any objections, the strongest dissent at the conference came not from him but from an extension maintainer. The elder developer, who maintained several extensions, raised concerns about compatibility, specifically regarding memory allocation and usage. Heikki suggested that extension authors should adapt their work to a new model during a transition grace period of about five years. This suggestion visibly upset the maintainer, who left the meeting in anger.

Given the proposed threading model’s significant impact on the existing extension ecosystem, I’m skeptical about this change. At the conference, I consulted on the threading model with Heikki, Tom Lane, and other hackers. The community’s overall stance is one of curious & cautious observation. So far, the only progress is in PG 17, where the fork-exec-related code has been refactored and global variables marked for future modifications. Any real implementation would likely not occur until at least PG 20+.

Hallway Track

The sessions on the second day were slightly less intense than the first, so many attendees chose the “Hallway Track”—engaging in conversations in the corridors and lobby. I’m usually not great at networking as an introvert, but the vibrant atmosphere quickly drew me in. Eye contact alone was enough to spark conversations, like triggering NPC dialogue in an RPG. I also managed to subtly promote Pigsty to every corner of the PG community.

Despite being a first-timer at PGCon.Dev, I was surprised by the recognition and attention I received, largely thanks to the widely read article, “PostgreSQL is eating the Database world.” Many recognized me by my badge Vonng / Pigsty.

A simple yet effective networking trick is never to underestimate small gifts’ effect. I handed out gold-plated Slonik pins, PostgreSQL’s mascot, which became a coveted item at the conference. Everyone who talked with me received one, and those who didn’t have one were left asking where to get one. LOL

Anyway, I’m glad to have made many new friends and connections.

Multinational Community Lunch

As for lunch, HighGo hosted key participants from the American, European, Japanese, and Chinese PostgreSQL communities at a Cantonese restaurant in Vancouver. The conversation ranged from serious technical discussions to lighter topics. I’ve made acquaintance with Tatsuro Yamada, who gives a talk, “Advice is seldom welcome but efficacious”, and Kyotaro Horiguchi, a core contributor to PostgreSQL known for his work on WAL replication and multibyte string processing and the author of pg_hint_plan.

Another major contributor to the PostgreSQL community, Mark Wong organizes PGUS and has developed a series of PostgreSQL monitoring extensions. He also manages community merchandise like contributor coins, shirts, and stickers. He even handcrafted a charming yarn elephant mascot, which was so beloved that one was sneakily “borrowed” at the last PG Conf US.

Bruce, already a familiar face in the PG Chinese community, Andreas Scherbaum from Germany, organizer of the European PG conferences, and Miao Jian, founder of Han Gao, representing the only Chinese database company at PGCon.Dev, all shared insightful stories and discussions about the challenges and nuances of developing databases in their respective regions.

On returning to the conference venue, I had a conversation with Jan Wieck, a PostgreSQL Hackers Emeritus. He shared his story of participating in the PostgreSQL project from the early days and encouraged me to get more involved in the PostgreSQL community, reminding me its future depends on the younger generation.

Making PG Hacking More Inclusive

At PGCon.Dev, a special session on community building chaired by Robert Hass, featured three new PostgreSQL contributors sharing their journey and challenges, notably the barriers for non-native English speakers, timezone differences, and emotionally charged email communications.

Robert emphasized in a post-conference blog his desire to see more developers from India and Japan rise to senior positions within PostgreSQL’s ranks, noting the underrepresentation from these countries despite their significant developer communities.

While we’re at it, I’d really like to see more people from India and Japan in senior positions within the project. We have very large developer communities from both countries, but there is no one from either of those countries on the core team, and they’re also underrepresented in other senior positions. At the risk of picking specific examples to illustrate a general point, there is no one from either country on the infrastructure team or the code of conduct committee. We do have a few committers from those countries, which is very good, and I was pleased to see Amit Kapila on the 2024.pgconf.dev organizing commitee, but, overall, I think we are still not where we should be. Part of getting people involved is making them feel like they are not alone, and part of it is also making them feel like progression is possible. Let’s try harder to do that.

Frankly, the lack of mention of China in discussions about inclusivity at PGCon.Dev, in favor of India and Japan, left a bittersweet taste. But I think China deserves the snub, given its poor international community engagement.

China has hundreds of “domestic/national” databases, many mere forks of PostgreSQL, yet there’s only a single notable Chinese contributor to PostgreSQL is Richard Guo from PieCloudDB, recently promoted to PG Committer. At the conference, the Chinese presence was minimal, summing up to five attendees, including myself. It’s regrettable that China’s understanding and adoption of PostgreSQL lag behind the global standard by about 10-15 years.

I hope my involvement can bootstrap and enhance Chinese participation in the global PostgreSQL ecosystem, making their users, developers, products, and open-source projects more recognized and accepted worldwide.

Lightning Talks

Yesterday’s event closed with a series of lightning talks—5 minutes max per speaker, or you’re out. Concise and punchy, the session wrapped up 11 topics in just 45 minutes. Keith shared improvements to PG Monitor, and Peter Eisentraut discussed SQL standard updates. But from my perspective, the highlight was Devrim Gündüz’s talk on PG RPMs, which lived up to his promise of a “big reveal” made at the bar the previous night, packing a 75-slide presentation into 5 lively minutes.

Speaking of PostgreSQL, despite being open-source, most users rely on official pre-compiled binaries packages rather than building from source. I maintain 34 RPM extensions for Pigsty, my Postgres distribution, but much of the ecosystem, including over a hundred other extensions, is managed by Devrim from the official PGDG repo. His efforts ensure quality for the world’s most advanced and popular database.

Devrim is a fascinating character — a Turkish native living in London, a part-time DJ, and the maintainer of the PGDG RPM repository, sporting a PostgreSQL logo tattoo. After an engaging chat about the PGDG repository, he shared insights on how extensions are added, highlighting the community-driven nature of PGXN and recent popular additions like pgvector, (which I made the suggestion haha).

Interestingly, with the latest Pigsty v2.7 release, four of my maintained (packaging) extensions (pgsql-http, pgsql-gzip, pg_net, pg_bigm) were adopted into the PGDG official repository. Devrim admitted to scouring Pigsty’s extension list for good picks, though he humorously dismissed any hopes for my Rust pgrx extensions making the cut, reaffirming his commitment to not blending Go and Rust plugins into the official repository. Our conversation was so enriching that I’ve committed myself to becoming a “PG Extension Hunter,” scouting and recommending new plugins for official inclusion.

Day 3: Unconference

One of the highlights of PGCon.Dev is the Unconference, a self-organized meeting with no predefined agenda, driven by attendee-proposed topics. On day three, Joseph Conway facilitated the session where anyone could pitch topics for discussion, which were then voted on by participants. My proposal for a Built-in Prometheus Metrics Exporter was merged into a broader Observability topic spearheaded by Jeremy.

The top-voted topics were Multithreading (42 votes), Observability (35 votes), and Enhanced Community Engagement (35 votes). Observability features were a major focus, reflecting the community’s priority. I proposed integrating a contrib monitoring extension in PostgreSQL to directly expose metrics via HTTP endpoint, using pg_exporter as a blueprint but embedded to overcome the limitations of external components, especially during crash recovery scenarios.

There’s a clear focus on observability among the community. As the author of pg_exporter, I proposed developing a first-party monitoring extension. This extension would integrate Prometheus monitoring endpoints directly into PostgreSQL, exposing metrics via HTTP without needing external components.

The rationale for this proposal is straightforward. While pg_exporter works well, it’s an external component that adds management complexity. Additionally, in scenarios where PostgreSQL is recovering from a crash and cannot accept new connections, external tools struggle to access internal states. An in-kernel extension could seamlessly capture this information.

The suggested implementation involves a background worker process similar to the bgw_replstatus extension. This process would listen on an additional port to expose monitoring metrics through HTTP, using pg_exporter as a blueprint. Metrics would primarily be defined via a Collector configuration table, except for a few critical system indicators.

This idea garnered attention from several PostgreSQL hackers at the event. Developers from EDB and CloudNativePG are evaluating whether pg_exporter could be directly integrated into their distributions as part of their monitoring solutions. And finally, an Observability Special Interest Group (SIG) was formed by attendees interested in observability, planning to continue discussions through a mailing list.

Issue: Support for LoongArch Architecture

During the last two days, I have had some discussions with PG Hackers about some Chinese-specific issues.

A notable suggestion was supporting the LoongArch architecture in the PGDG global repository, which was backed by some enthusiastically local chip and OS manufacturers. Despite the interest, Devrim indicated a “No” due to the lack of support for LoongArch in OS Distro used in the PG community, like CentOS 7, Rocky 8/9, and Debian 10/11/12. Tomasz Rybak was more receptive, noting potential future support if LoongArch runs on Debian 13.

In summary, official PG RPMs might not yet support LoongArch, but APT has a chance, contingent on broader OS support for mainstream open-source Linux distributions.

Issue: Server-side Chinese Character Encoding

At the recent conference, Jeremy Schneider presented an insightful talk on collation rules that resonated with me. He highlighted the pitfalls of not using C.UTF8 for collation, a practice I’ve advocated for based on my own research, and which is detailed in his presentation here.

Post-talk, I discussed further with Jeremy and Peter Eisentraut the nuances of character sets in China, especially the challenges posed by the mandatory GB18030 standard, which PostgreSQL can handle on the client side but not the server side. Also, there are some issues about 20 Chinese characters not working on the convert_to + gb18030 encoding mapping.

Closing

The event closed with Jonathan Katz and Melanie Plageman wrapping up an exceptional conference that leaves us looking forward to next year’s PGCon.Dev 2025 in Canada, possibly in Vancouver, Toronto, Ottawa, or Montreal.

Inspired by the engagement at this conference, I’m considering presenting on Pigsty or PostgreSQL observability next year.

Notably, following the conference, Pigsty’s international CDN traffic spiked significantly, highlighting the growing global reach of our PostgreSQL distribution, which really made my day.

Pigsty CDN Traffic Growth after PGCon.Dev 2024

Some slides are available on the official site, and some blog posts about PGCon are here.Dev 2024:

3 - PostgreSQL Macro Query Optimization with pg_stat_statements

In production online databases, slow queries not only affect end-user experience but also waste system resources, increase resource saturation, cause deadlocks and transaction conflicts, increase database connection pressure, and lead to master-slave replication delays. Therefore, query optimization is one of the core responsibilities of DBAs.

On the path of query optimization, there are two different approaches:

Macro Optimization: Analyze the overall workload, dissect and drill down, identifying and improving the worst-performing parts from top to bottom.

Micro Optimization: Analyze and improve specific queries, requiring slow query logs, mastering EXPLAIN mysteries, and understanding execution plan intricacies.

Today we’ll discuss the former. Macro optimization has three main goals and motivations:

Reduce Resource Consumption: Lower the risk of resource saturation, optimize CPU/memory/IO, typically using query total time/total IO as optimization targets.

Improve User Experience: The most common optimization goal, in OLTP systems typically using reduced average query response time as the optimization target.

Balance Workload: Ensure appropriate proportional relationships in resource usage/performance between different query groups.

The key to achieving these goals lies in data support. But where does the data come from?

—— pg_stat_statements！

Extension: PGSS

pg_stat_statements, hereafter abbreviated as PGSS, is the core tool for practicing the macro way.

PGSS comes from the official PostgreSQL Global Development Group, distributed as a first-party extension alongside the database kernel itself, providing methods for tracking SQL statement-level metrics.

The PostgreSQL ecosystem has many extensions, but if there’s one that’s “mandatory”, I would answer without hesitation: PGSS. This is also one of the two extensions that Pigsty enables by default and actively loads, even “taking liberties” to do so. (The other is auto_explain for micro optimization)

PGSS needs to be explicitly specified for loading in shared_preload_library and explicitly created in the database via CREATE EXTENSION. After creating the extension, you can access query statistics through the pg_stat_statements view.

In PGSS, each type of query in the system (i.e., queries with the same execution plan after variable extraction) is assigned a query ID, followed by call count, total execution time, and various other metrics. Its complete schema definition (PG15+) is as follows:

CREATE TABLE pg_stat_statements
(
    userid                 OID,     -- (Label) User OID executing this statement
    dbid                   OID,     -- (Label) Database OID containing this statement
    toplevel               BOOL,    -- (Label) Whether this statement is top-level SQL
    queryid                BIGINT,  -- (Label) Query ID: hash of normalized query
    query                  TEXT,    -- (Label) Normalized query statement text
    plans                  BIGINT,  -- (Counter) Number of times this statement was planned
    total_plan_time        FLOAT,   -- (Counter) Total time spent planning this statement
    min_plan_time          FLOAT,   -- (Gauge) Minimum planning time
    max_plan_time          FLOAT,   -- (Gauge) Maximum planning time
    mean_plan_time         FLOAT,   -- (Gauge) Average planning time
    stddev_plan_time       FLOAT,   -- (Gauge) Standard deviation of planning time
    calls                  BIGINT,  -- (Counter) Number of times this statement was executed
    total_exec_time        FLOAT,   -- (Counter) Total time spent executing this statement
    min_exec_time          FLOAT,   -- (Gauge) Minimum execution time
    max_exec_time          FLOAT,   -- (Gauge) Maximum execution time
    mean_exec_time         FLOAT,   -- (Gauge) Average execution time
    stddev_exec_time       FLOAT,   -- (Gauge) Standard deviation of execution time
    rows                   BIGINT,  -- (Counter) Total rows returned by this statement
    shared_blks_hit        BIGINT,  -- (Counter) Total shared buffer blocks hit
    shared_blks_read       BIGINT,  -- (Counter) Total shared buffer blocks read
    shared_blks_dirtied    BIGINT,  -- (Counter) Total shared buffer blocks dirtied
    shared_blks_written    BIGINT,  -- (Counter) Total shared buffer blocks written to disk
    local_blks_hit         BIGINT,  -- (Counter) Total local buffer blocks hit
    local_blks_read        BIGINT,  -- (Counter) Total local buffer blocks read
    local_blks_dirtied     BIGINT,  -- (Counter) Total local buffer blocks dirtied
    local_blks_written     BIGINT,  -- (Counter) Total local buffer blocks written to disk
    temp_blks_read         BIGINT,  -- (Counter) Total temp buffer blocks read
    temp_blks_written      BIGINT,  -- (Counter) Total temp buffer blocks written to disk
    blk_read_time          FLOAT,   -- (Counter) Total time spent reading blocks
    blk_write_time         FLOAT,   -- (Counter) Total time spent writing blocks
    wal_records            BIGINT,  -- (Counter) Total WAL records generated
    wal_fpi                BIGINT,  -- (Counter) Total WAL full page images generated
    wal_bytes              NUMERIC, -- (Counter) Total WAL bytes generated
    jit_functions          BIGINT,  -- (Counter) Number of functions JIT-compiled
    jit_generation_time    FLOAT,   -- (Counter) Total time spent generating JIT code
    jit_inlining_count     BIGINT,  -- (Counter) Number of times functions were inlined
    jit_inlining_time      FLOAT,   -- (Counter) Total time spent inlining functions
    jit_optimization_count BIGINT,  -- (Counter) Number of times queries were JIT-optimized
    jit_optimization_time  FLOAT,   -- (Counter) Total time spent on JIT optimization
    jit_emission_count     BIGINT,  -- (Counter) Number of times code was JIT-emitted
    jit_emission_time      FLOAT,   -- (Counter) Total time spent on JIT emission
    PRIMARY KEY (userid, dbid, queryid, toplevel)
);

PGSS view SQL definition (PG 15+ version)

PGSS also has some limitations: First, currently executing query statements are not included in these statistics and need to be obtained from pg_stat_activity. Second, failed queries (e.g., statements canceled due to statement_timeout) are also not counted in these statistics — this is a problem for error analysis to solve, not a concern for query optimization.

Finally, the stability of query identifier queryid needs special attention: when the database binary version and system data directory are identical, the same type of query will have the same queryid (i.e., on physical replication master-slave, same-type queries have the same queryid by default), but this doesn’t apply to logical replication. However, users should not rely too heavily on this assumption.

Raw Data

Columns in the PGSS view can be divided into three categories:

Descriptive Label Columns: Query ID (queryid), database ID (dbid), user (userid), a top-level query marker, and normalized query text (query).

Measurement Metrics (Gauge): Eight statistics related to minimum, maximum, mean, and standard deviation, prefixed with min, max, mean, stddev and suffixed with plan_time and exec_time.

Cumulative Metrics (Counter): Other metrics besides the above eight columns and label columns, such as calls, rows, etc. The most important and useful metrics are in this category.

First, let’s explain queryid: queryid is a hash value generated from the normalized query after parsing the query statement and stripping constants, so it can be used to identify the same type of query. Different query statements may have the same queryid (same structure after normalization), and the same query statement may have different queryids (e.g., due to different search_path, resulting in different actual tables being queried).

The same query may be executed by different users in different databases. Therefore, in the PGSS view, the four label columns queryid, dbid, userid, toplevel together form the “primary key” that uniquely identifies a record.

For metric columns, measurement-type metrics (GAUGE) are mainly the eight statistics related to execution time and planning time, but users have no good way to control the statistical range of these statistics, so their practical value is limited.

The really important metrics are cumulative metrics (Counter), such as:

calls: How many times this query group was invoked.

total_exec_time + total_plan_time: Total time consumed by the query group.

rows: Total rows returned by the query group.

shared_blks_hit + shared_blks_read: Total buffer pool hits and read operations.

wal_bytes: Total WAL bytes generated by queries in this group.

blk_read_time and blk_write_time: Total time spent on block read/write IO

Here, the most meaningful metrics are calls and total_exec_time, which can be used to calculate the core metrics QPS (throughput) and RT (latency/response time) for query groups, though other metrics also have reference value.

Visualizing a query group snapshot from the PGSS view

To interpret cumulative metric data, data from just one moment is insufficient. We need to compare at least two snapshots from different moments to draw meaningful conclusions.

As a special case, if the range you’re interested in happens to be from the beginning of the statistical period (usually when this extension was enabled) to now, then you indeed don’t need to compare “two snapshots.” But users’ time granularity of interest usually isn’t so coarse, and is often in units of minutes, hours, or days.

Calculating historical time-series metrics from multiple PGSS query group snapshots

Fortunately, tools like Pigsty monitoring system periodically (every 10s by default) take snapshots of top queries (top 256 by time consumption). With many snapshots of different types of cumulative metrics M at different times, we can calculate three important derived metrics for any cumulative metric:

dM/dt: Derivative of metric M with respect to time, i.e., increment per second.

dM/dc: Derivative of metric M with respect to call count, i.e., average increment per call.

%M: Percentage of metric M in the overall workload.

These three types of metrics correspond exactly to the three types of macro optimization goals. The time derivative dM/dt reveals resource usage per second, typically used for resource consumption reduction optimization goals. The call count derivative dM/dc reveals resource usage per call, typically used for user experience improvement optimization goals. The percentage metric %M shows the percentage a query group occupies in the overall workload, typically used for workload balancing optimization goals.

Time Derivatives

Let’s first look at the first type of metrics: time derivatives. Here, the metrics M we can use include: calls, total_exec_time, rows, wal_bytes, shared_blks_hit + shared_blks_read, and blk_read_time + blk_write_time. Other metrics also have reference value, but let’s start with the most important ones.

Visualizing time derivative metrics dM/dt

Calculating these metrics is actually quite simple, we just need to:

First calculate the difference in metric value M between two snapshots: M2 - M1
Then calculate the time difference between two snapshots: t2 - t1
Finally calculate (M2 - M1) / (t2 - t1)

Production environments typically use data sampling intervals like 5s, 10s, 15s, 30s, 60s. For load analysis, we typically use 1m, 5m, 15m as common analysis window sizes.

For example, when we calculate QPS, we would calculate QPS for the last 1 minute, 5 minutes, and 15 minutes respectively. Longer windows provide smoother curves that better reflect long-term trends, but hide short-term fluctuation details and are not conducive to discovering momentary anomalies, so metrics of different granularities need to be viewed together.

Showing QPS for a specific query group in 1/5/15 minute windows

If you use Pigsty / Prometheus to collect monitoring data, you can use PromQL to easily complete these calculations. For example, to calculate QPS for all queries in the last minute, use: rate(pg_query_calls{}[1m])

QPS

When M is calls, the result of time differentiation is QPS, with units of queries per second (req/s). This is a very fundamental metric. Query QPS is a throughput metric that directly reflects the load situation imposed by business. If a query’s throughput is too high (e.g., 10000+) or too low (e.g., 1-), it may be worth attention.

QPS: 1/5/15 minute µ/CV, ±1/3σ distribution

If we sum up all query groups’ QPS metrics (without exceeding PGSS collection range), we get the so-called “global QPS.” Another way to obtain global QPS is through client-side instrumentation, collection at connection pool middleware like Pgbouncer, or using eBPF probes. But none are as convenient as PGSS.

Note that QPS metrics don’t have load-wise horizontal comparability. Different query groups may have the same QPS but vastly different per-query execution times. Even the same query group may produce dramatically different load levels at different times due to different execution plans. Execution time per second is a better metric for measuring load.

Execution Time Per Second

When M is total_exec_time (+ total_plan_time, optional), we get one of the most important metrics in macro optimization: execution time spent on query groups. Interestingly, this derivative has units of seconds/per second, so the numerator and denominator cancel out, making it actually a dimensionless metric.

This metric means: how many seconds per second does the server spend processing queries in this query group, e.g., 2 s/s means the server spends two seconds of execution time on this query group every second; for multi-core CPUs, this is certainly possible: just use the full time of two CPU cores.

Execution time per second: 1/5/15 minute averages

Therefore, this value can also be understood as a percentage: it can exceed 100%, and in this perspective, it’s a metric similar to host load1, load5, load15, revealing the load level generated by this query group. If divided by CPU core count, you can even get a normalized query load contribution metric.

However, we need to note that execution time includes time waiting for locks and I/O. So it’s possible that query execution time is very long but has no impact on CPU load. For precise slow query analysis, we need to refer to wait events for further analysis.

Rows Per Second

When M is rows, we get the number of rows returned by this query group per second, with units of rows per second (rows/s). For example, 10000 rows/s means this type of query spits out 10,000 rows of data to clients every second. Returned rows consume client processing resources, making this a very meaningful metric when examining application client data processing pressure.

Rows returned per second: 1/5/15 minute averages

Shared Buffer Access Bandwidth

When M is shared_blks_hit + shared_blks_read, we get the number of shared buffer blocks hit/read per second. If we multiply this by the default block size of 8KiB (rarely other sizes like 32KiB), we get the bandwidth of a query type “accessing” memory/disk: bytes per second.

For example, if a certain query type accesses 500,000 shared buffers per second, that’s 3.8 GiB/s of internal access data flow: this is significant load and might be a good optimization candidate. Perhaps you should examine this query to see if it deserves this “resource consumption.”

Shared buffer access bandwidth and buffer hit rate

Another valuable derived metric is buffer hit rate: hit / (hit + read). It can be used to analyze possible causes of performance changes — cache misses. Of course, repeatedly accessing the same blocks in the shared buffer pool doesn’t actually re-read, and even real reads might be from memory’s FS Cache rather than disk. So this is just a reference value, but it’s indeed a very important macro query optimization reference metric.

WAL Volume

When M is wal_bytes, we get the rate at which this query generates WAL, with units of bytes per second (B/s). This metric was introduced in PostgreSQL 13 and can quantitatively reveal the WAL size generated by queries: the more and faster WAL is written, the greater the pressure on disk flushing, physical/logical replication, and log archiving.

A typical example is: BEGIN; DELETE FROM xxx; ROLLBACK;. Such transactions delete lots of data, generate large amounts of WAL but perform no useful work. This metric can identify them.

WAL byte rate: 1/5/15 minute averages

Two notes here: we mentioned earlier that PGSS cannot track failed statements, but here the transaction ROLLBACK failed, but the statement was successfully executed, so it will be tracked and recorded by PGSS.

Second: in PostgreSQL, not only INSERT/UPDATE/DELETE generate WAL logs, SELECT operations can also generate WAL logs because SELECT might modify hint bits on tuples, causing page checksum changes and triggering WAL log writes.

There’s even this possibility: if read load is very large, it has a high probability of causing FPI image generation, producing considerable WAL volume. You can further check the wal_fpi metric.

Shared buffer dirty/writeback bandwidth

For versions below 13, shared buffer dirty/writeback bandwidth metrics can serve as an approximate lower substitute for analyzing query group write load characteristics.

I/O Time

When M is blks_read_time + blks_write_time, we get the proportion of time query groups spend on block I/O, with units of “seconds per second”, like the execution time per second metric, also reflecting the time proportion occupied by operations.

I/O time is very helpful for analyzing query spike causes

Because PostgreSQL uses the operating system’s FS Cache, even if block reads/writes are executed here, they might still be buffer operations occurring in memory at the filesystem level. So it can only serve as a reference metric and should be used cautiously, needing cross-reference with host node disk I/O monitoring.

Time derivative metrics dM/dt can show the overall workload inside a database instance/cluster, especially useful for resource usage optimization scenarios. But if your optimization goal is improving user experience, then another group of metrics — call count derivatives dM/dc — might be more meaningful.

Call Count Derivatives

Above we calculated six types of important metrics’ derivatives with respect to time. Another class of derived metrics is calculated by differentiating with respect to “call count”, i.e., the denominator changes from time difference to QPS.

This class of metrics is even more important than the former because it provides several core metrics directly related to user experience, such as the most important — query response time (RT, Response Time), or latency.

Calculating these metrics is also simple, we just need to:

Calculate the difference in metric value M between two snapshots: M2 - M1
Then calculate the difference in calls between two snapshots: c2 - c1
Then calculate (M2 - M1) / (c2 - c1)

For PromQL implementation, call count derivative metrics dM/dc can be calculated using “time derivative metrics dM/dt”. For example, to calculate RT, you can use execution time per second / queries per second, dividing the two metrics:

rate(pg_query_exec_time{}[1m]) / rate(pg_query_calls{}[1m])

dM/dt can be used to calculate dM/dc

Call Count

When M is calls, differentiating with respect to itself is meaningless (result will always be 1).

Average Latency/Response Time/RT

When M is total_exec_time, differentiating with respect to call count gives RT, or response time/latency. Its unit is seconds (s). RT directly reflects user experience and is the most important metric in macro performance analysis. This metric means: the average query response time for this query group on the server. If conditions allow enabling pg_stat_statements.track_planning, you can add total_plan_time for more accurate and representative results.

RT: 1/5/15 minute µ/CV, ±1/3σ distribution

Two special situations need emphasis: First, PGSS doesn’t track failed/executing statements; Second, PGSS statistical data is limited by the (pg_stat_statements.max) parameter and may have sampling bias. Despite these limitations, PGSS is undoubtedly the most reliable source for obtaining crucial query statement group latency data. As mentioned above, there are other ways to collect query RT data at other observation points, but they would be much more troublesome.

You can instrument on the client side, collecting statement execution times and reporting through metrics or logs; you can also try using eBPF to probe statement RT, which has high requirements for infrastructure and engineers. Pgbouncer and PostgreSQL (14+) do provide RT metrics, but unfortunately, their granularity is at the database level, none can achieve PGSS’s query statement group-level metric collection.

RT: Statement-level/Connection pool-level/Database-level

Unlike throughput metrics like QPS, RT has horizontal comparability: for example, if a query group’s RT is usually within 1 millisecond, then events exceeding 10ms should be considered serious deviations for analysis.

When failures occur, RT views are also helpful for pinpointing causes: if all queries’ overall RT slows down, it’s most likely related to resource insufficiency. If only specific query groups’ RT changes, it’s more likely caused by slow queries and should be investigated further. If RT changes coincide with application deployments, consider whether to rollback those deployments.

Additionally, in performance analysis, stress testing, and benchmarking, RT is also the most important metric. You can evaluate system performance by comparing typical queries’ latency performance in different environments (e.g., different PG major versions, different hardware, different configuration parameters) and continuously adjust and improve system performance based on this.

RT is so important that it spawns many downstream metrics: 1/5/15 minute mean µ and standard deviation σ are naturally essential; ±σ and ±3σ over the past 15 minutes can measure RT fluctuation range; 95th and 99th percentiles over the past hour also have reference value.

RT is the core metric for evaluating OLTP workloads. No amount of emphasis on its importance is excessive.

Average Returned Rows

When M is rows, we get the average number of rows returned per query, with units of rows per query. For OLTP workloads, typical query patterns are point queries, returning a few records per query.

Point queries by primary key, average returned rows stable at 1

If a query group returns hundreds or even thousands of rows to clients per query, it should be examined. If this is intentional design, such as batch loading tasks/data dumps, then no action is needed. If these are requests initiated by applications/clients, there might be errors, such as statements lacking LIMIT restrictions or queries lacking pagination design. Such queries should be adjusted and fixed.

Average Shared Buffer Read/Hit

When M is shared_blks_hit + shared_blks_read, we get the average number of shared buffer “hits” and “reads” per query. If we multiply this by the default block size of 8KiB, we get the “bandwidth” of this query type per execution, with units of B/s: how many MB of data does each query access/read on average?

Point queries by primary key, average returned rows stable at 1

Query average accessed data volume usually matches average returned rows. If your query only returns a few rows on average but accesses gigabytes of data blocks, you need special attention: such queries are very sensitive to data hot/cold states. If all blocks are in the buffer, performance might be acceptable, but if starting from disk cold, execution time might change dramatically.

Of course, don’t forget PostgreSQL’s double caching issue — so-called “read” data might have already been cached once at the operating system filesystem level. So you need cross-reference with operating system monitoring metrics, or pg_stat_kcache, pg_stat_io system views for analysis.

Another pattern worth attention is sudden changes in this metric, which usually means this query group’s execution plan might have flipped/degraded, very worthy of attention and further study.

Average WAL Volume

When M is wal_bytes, we get the average WAL size generated per query. This is a field newly introduced in PostgreSQL 13. This metric can measure query change footprint size and calculate read/write ratios and other important evaluation parameters.

Stable QPS but periodic WAL fluctuations, inferred to be FPI influence

Another use is checkpoint optimization: if you observe periodic fluctuations in this metric (period approximately equal to checkpoint_timeout), you can optimize the amount of WAL generated by queries by adjusting checkpoint intervals.

Call count derivative metrics dM/dc can show the workload characteristics of a query type, very useful for optimizing user experience. Especially RT is the golden metric for performance optimization — no amount of emphasis on its importance is excessive.

dM/dc metrics like these provide important absolute value metrics, but to find which queries have the greatest potential optimization benefits, you need %M percentage metrics.

Percentage Metrics

Now let’s study the third type of metrics: percentage metrics. That is, the proportion a certain query group occupies relative to the overall workload.

Percentage metrics M% provide us with the proportion of a certain query group relative to the overall workload, helping us identify “major players” in frequency, time, I/O time/count, find query groups with the greatest potential optimization benefits, and serve as important basis for priority assessment.

Common percentage metrics %M overview

For example, if a certain query group has 1000 QPS in absolute value, which seems like a lot; but if it only accounts for 3% of the entire workload, then the benefits and priority of optimizing this query aren’t that high; conversely, if it accounts for more than 50% of the entire workload — if you can optimize it away, you can cut half of the entire instance’s throughput, making its optimization priority very high.

A common optimization strategy is: first sort all query groups by the important metrics mentioned above: calls, total_exec_time, rows, wal_bytes, shared_blks_hit + shared_blks_read, and blk_read_time + blk_write_time dM/dt values over a period of time, take TopN (say N=10 or more), and add them to the optimization candidate list.

Selecting TopSQL for optimization by specific criteria

Then, for each query group in the optimization candidate list, analyze their dM/dc metrics in turn, combined with specific query statements and slow query logs/wait events for analysis, decide whether this is a query worth optimizing. For queries decided (Plan) to optimize, you can use techniques introduced in the subsequent “micro optimization” article for tuning (Do), and use monitoring systems to evaluate optimization effects (Check), summarize and analyze before entering the next PDCA Deming cycle, continuing management optimization.

Besides taking TopN on metrics, visualization can also be used. Visualization greatly helps identify “major contributors” from workloads. Complex judgment algorithms might not match human DBAs’ intuition for monitoring pattern recognition. To form a sense of proportion, we can use pie charts, tree maps, or stacked time series charts.

Stacking QPS of all query groups

For example, we can use pie charts to identify queries with the highest time consumption/IO usage in the past hour, use 2D tree maps (size represents total time consumption, color represents average RT) to show an additional dimension, and use stacked time series charts to show how proportions change over time.

We can also directly analyze current PGSS snapshots, sort by different concerns, and select queries to be optimized according to your own criteria.

I/O time is very helpful for analyzing query spike causes

Summary

Finally, let’s summarize the content above.

PGSS provides rich metrics, among which the most important cumulative metrics can be processed in three ways:

dM/dt: Derivative of metric M with respect to time, revealing resource usage per second, typically used for resource consumption reduction optimization goals.

dM/dc: Derivative of metric M with respect to call count, revealing resource usage per call, typically used for user experience improvement optimization goals.

%M: Percentage metrics show the percentage a query group occupies in the overall workload, typically used for workload balancing optimization goals.

Usually, we select high-value candidate optimization queries based on %M: percentage metric Top queries, and use dM/dt and dM/dc metrics for further evaluation, confirming whether there’s optimization space and feasibility, and evaluating post-optimization effects. This cycles continuously.

After understanding macro optimization methodology, we can use this approach to locate and optimize slow queries. Here’s a specific example of Using Monitoring Systems to Diagnose PG Slow Queries. In the next article, we’ll introduce experience and techniques for PostgreSQL query micro optimization.

References

[1] PostgreSQL HowTO: pg_stat_statements by Nikolay Samokhvalov

[2] pg_stat_statements

[3] Using Monitoring Systems to Diagnose PG Slow Queries

[4] How to Monitor Existing PostgreSQL (RDS/PolarDB/Self-built) with Pigsty?

[5] Pigsty v2.5 Released: Ubuntu/Debian Support and Monitoring Redesign/New Extensions

[6] PostgreSQL Monitoring System Pigsty Overview

4 - 如何用Pigsty监控现有PostgreSQL (RDS/PolarDB/自建)？

Pigsty 是一个开箱即用的 PostgreSQL 发行版，与本地优先的 RDS 开源替代。但它也可以单独作为一个 PostgreSQL/主机监控系统来使用。本文以阿里云为例，介绍了用一台 ECS 安装部署 Pigsty，并用于监控云上的 PolarDB 与 RDS for PostgreSQL，为现有数据库带来极致的观测能力。

嵌入媒体

快速上手

使用 Pigsty 教程分为四个步骤：

1.申请用于部署 Pigsty 的 ECS 服务器2.在 ECS 服务器上完整单机安装 Pigsty3.配置 PolarDB / RDS 的监控用户、模式、视图、黑白名单4.将 RDS / PolarDB for PG 接入 Pigsty 监控系统中

只要您有阿里云账号，使用按量付费模式（需要账户余额¥100以上），费用大致为 ¥4 / 小时，一小时内收工。

Why Pigsty

在讲 How 之前先说 Why。为什么要用一个额外的监控系统来做这件事？难道各家云厂商不是已经提供 RDS 监控了吗？没有错，只不过各家云厂商的 RDS PostgreSQL 的监控实在太简单了：这种程度的监控，对于回答数据库活着还是死了这件事也许足够了，但对于稍微高级一丁点儿的管理工作：性能优化，故障诊断都无能为力。

Pigsty 的监控系统便是为了解决这个问题而生的。Pigsty 提供了基于开源的 Grafana / Prometheus 现代可观测性技术栈做监控的最佳实践。整套系统同样被设计为一键拉起，开箱即用的 INFRA 模块。Pigsty 所管理的任何组件都会被自动纳入监控之中，包括主机节点，负载均衡 HAProxy，数据库 Postgres，连接池 Pgbouncer，元数据库 ETCD，KV缓存 Redis，对象存储 MinIO，……，以及整套监控基础设施本身。大量的 Grafana 监控面板与预置告警规则会让你的系统观测能力有质的提升。

Pigsty提供的监控面板概览\

无论是故障分析还是慢查询优化、无论是水位评估还是资源规划，Pigsty 为您提供全面的数据支撑，真正做到数据驱动。在 Pigsty 中，超过三千类监控指标被用于描述整个系统的方方面面，并被进一步加工、聚合、处理、分析、提炼并以符合直觉的可视化模式呈现在您的面前。从全局大盘总览，到某个数据库实例中单个对象（表，索引，函数）的增删改查详情都能一览无余。您可以随意上卷下钻横向跳转，浏览系统现状与历史趋势，并预测未来的演变。

对于现有的 PostgreSQL 数据库特别是 RDS 云数据库来说，虽然 Pigsty 拿不到主机监控数据，也没有高可用/连接池等组件的监控数据，也缺少原生的数据库日志。但能利用好 PostgreSQL 本身的监控指标，已经有着足够强大的力量了。

您可以查阅公开Demo：https://demo.pigsty.cc 来了解 Pigsty 监控系统提供的能力。

使用 Terraform 申请 ECS 服务器

单机安装 Pigsty 需要一台 x86_64 ECS 云服务器，EL 7-9操作系统（建议使用 Rocky 8.6/9.1），规格最小 1C2G 。您可以直接在控制台上申请资源，或者使用 Pigsty 提供的 Terraform 模板一键完成资源申请与置备。默认配置会使用固定的 10.10.10.10 IP地址与密码 PigstyDemo4，并分配一个公网IP以供访问。

在ECS上完整安装单机版 Pigsty

登陆 ECS 后，可以通过以下命令完成 Pigsty 的单机安装

bash -c "$(curl -fsSL https://get.pigsty.cc/latest)"   # 下载 Pigsty
cd ~/pigsty; ./bootstrap;                              # 提示下载离线软件包
./configure                                            # 配置 Pigsty
vi pigsty.yml  # 定制一些配置，比如修改各种密码
./install.yml  # 完成完整的单机安装，使用离线包完整安装约10分钟

Pigsty的详细安装过程介绍：https://pigsty.cc/doc/#/zh/INSTALL，整个过程耗时约十几分钟。

安装完成后，您可以通过 ECS 公网 IP 地址上的 3000 端口访问监控系统 Grafana，监控界面中会显示出完整的自我监控。例如本例中：http://59.110.161.154:3000 ，默认用户名密码为：admin / pigsty。

我们建议您使用域名/https访问 Pigsty 的 Web 界面，注意在生产环境使用时，我们强烈建议您修改默认密码，并谨慎对公网暴露界面。

配置 PolarDB / RDS PG 监控

PolarDB 的配置细节在此不再赘述，关键是要有一个给监控用户使用的连接串，可以从 ECS 上访问 PolarDB 主库 / 从库，并确保相关监控用户具有足够的读取权限，以及相关监控扩展已经完成安装。

•创建集群，确保 PolarDB 与ECS在同一个可用区/子网内，可以从ECS访问PolarDB。•创建集群账号：管理用户 dbuser_dba，高权限账号，用来对集群进行进一步的配置。•添加集群访问白名单：将 ECS 内网IP地址 10.10.10.10 添加到集群白名单中。•创建一个业务数据库，这里以 test 为例。

配置好数据库集群的账号、白名单、数据库之后，使用高权限管理用户连接至新创建的数据库

$ psql postgres://dbuser_dba:[email protected]:1921/postgres

使用高权限用户创建专用监控用户，当然您也可以在控制台创建。

强烈建议安装 pg_stat_statements 扩展，它可以提供非常重要的关于查询的

将现有PG数据库接入监控

为了将 RDS for PostgreSQL 实例与 PolarDB 实例纳入监控，您需要将这些目标实例的身份信息与连接信息告诉 Pigsty。编辑 pigsty.yml 配置文件，在 all.children.infra.vars.pg_exporters 定义这些待监控的远程数据库实例，这是一个字典，Key 为唯一分配的本地监控组件端口号，Value为目标实例的配置信息。

这里，我们接入了一个一主一从的 PolarDB 集群，并将其命名为 pg-polar，一个基础版（单节点）的 RDS for PostgreSQL 实例并命名为 pg-rds，以及一个高可用版本并带有一个只读节点的 RDS 集群 pg-rdsha。其配置如下所示，并不是所有参数都是必须的，通常来说，只有 pg_cluster，pg_seq，与 pg_host （pg_port，如果不是5432）是需要修改的，其他参数可以按需指定覆盖。

定义好这些配置选项后，您可以使用以下命令，将其纳入到 Pigsty 的监控系统中：

    bin/pgmon-add pg-polar
    bin/pgmon-add pg-rds
    bin/pgmon-add pg-rdsha

然后，您就可以在 Pigsty 监控系统中看到这三个新集群了。

点击浅灰蓝色的集群名/实例名，即可跳转到对应集群/实例 PGRDS 监控面板上：

点击浅灰蓝色的集群名/实例名，即可跳转到对应集群/实例 PGRDS 监控面板上：

首屏包含了最为关键的信息，集群实例成员，存活状态，数据库列表与导航，13个核心监控指标。点击都可以展开更详细的信息。\

在 PGRDS Cluster 与 PGRDS Instance 之间，可以方便地在图表元素上点击跳转，快速上卷下钻。

同时，您依然可以复用 Overview / Database 层次的所有监控面板，查阅数据库内部的详细细节，比如每一类查询的 QPS / RT，或者每个表上的增删改查：

Pigsty的监控系统还允许您直接访问数据库的 Catalog （可选），从系统视图中查阅统计数据。例如锁等待 / TopSQL 等。

更多监控系统的细节就不在此展开了，欢迎访问 Pigsty 文档： https://pigsty.cc ，或者访问 Demo 在线体验：https://demo.pigsty.cc 。点击“查看原文”，访问 Bilibili 视频版教程。

如果您对 Pigsty 与 PostgreSQL 感兴趣，也欢迎微信搜索 pigsty-cc 添加 Pigsty 小助手，加入 PGSQL x Pigsty 交流群中。

5 - A Methodology for Diagnosing PostgreSQL Slow Queries

“You can’t optimize what you can’t measure.”

Slow queries hog connections, hold locks, block replication, trigger deadlocks, and waste resources. Every DBA must know how to find and fix them quickly.

Traditional tools

pg_stat_statements – essential extension that aggregates execution stats per normalized query: calls, total/mean/max time, rows per call, I/O time, etc. Always enable it.
Slow query logs – controlled via log_min_duration_statement. Great for one-off incidents or forensic analysis, but sampling thresholds mean you miss sub-threshold issues. Full logging is expensive but the ultimate truth when you need it.

Why monitoring helps

Static snapshots don’t show trends. Monitoring systems (Pigsty in my case) sample every few seconds, letting you rewind, compare before/after, and show stakeholders what’s happening. They also calm nervous bosses during incidents.

Workflow (simulated incident)

We spin up the Pigsty sandbox, run pgbench load (50 TPS writes on the primary, 1000 TPS reads on a replica), then deliberately drop pgbench_accounts_pkey to break index scans.

1. Detection

Cluster dashboards show QPS collapsing and response times spiking (1 ms → 300 ms). System load shoots above 200%, alarms fire.

2. Identification

Use the PG Query dashboard to find the worst offender. Query ID -6041100154778468427 has mean latency jumping from microseconds to hundreds of milliseconds while QPS plummets. Drill into PG Stat Statements to see the normalized SQL: SELECT abalance FROM pgbench_accounts WHERE aid = $1.

3. Hypothesis

Simple point lookup suddenly slow? Most likely the index vanished. Check PG Table Catalog and PG Table Detail: index scans drop to zero, seq scans soar. Hypothesis confirmed.

4. Fix

Recreate the index:

ALTER TABLE pgbench_accounts ADD PRIMARY KEY (aid);

Latency falls from seconds to milliseconds, QPS recovers, system load normalizes. Dashboards provide immediate feedback.

Summary

Detect – monitor query latency, concurrency, and system load.
Identify – use pg_stat_statements/monitoring to find the exact query (by query ID).
Hypothesize – analyze the SQL, review table/index metrics.
Fix & verify – add indexes, rewrite queries, adjust schema, then watch metrics confirm success.

Pigsty’s dashboards wrap these steps into a workflow, but the methodology applies with any monitoring stack: measure, locate, hypothesize, fix, verify.

6 - Pigsty正式发布

今天我很荣幸的宣布，Pigsty正式发布了！

官方网站：https://pigsty.cc

Pigsty是什么？

Pigsty是针对大规模PostgreSQL集群的监控系统
Pigsty是高可用PostgreSQL集群的供给方案
Pigsty基于开源生态构建，是免费的开源软件

Pigsty 针对大规模数据库集群监控与管理而设计，提供业界顶尖的PostgreSQL监控系统与开箱即用的高可用数据库供给方案。Pigsty基于开源生态构建，旨在降低PostgreSQL使用管理的门槛，为用户带来极致的可观测性与丝滑的数据库使用体验。\

Pigsty是监控系统

PostgreSQL是世界上最好的开源关系型数据库，但在其生态中却缺少一个足够好的监控系统。Pigsty即旨在解决这一问题：提供世界上最好的PostgreSQL监控系统，

开发Pigsty的初衷是：作者需要对一个大规模PostgreSQL集群进行管理，但找遍所有市面上的开源与商业监控系统方案后，发现没有一个是“足够好用”的。本着“我行我上”的精神，开发设计了本系统。

Pigsty的界面基于Grafana深度定制，由30+监控面板，上千+仪表盘，18万行JSON定制而成，涵盖数据库与基础设施的方方面面。

Pigsty提供近1200个监控指标，一骑绝尘，远超市面上现有的相关产品。提供从全局大盘汇总到某一个数据对象增删改查的全域数据支持。\

Pigsty是供给方案

Pigsty同时还是一个高可用数据库集群供给方案。

监控系统要想发行与演示，必须要先有被监控的对象。可许多用户自建的数据库实在是千奇百怪。所以这里，Pigsty项目决定将数据库供给方案作为项目的一部分发布。

将主从复制，故障切换，流量代理，连接池，服务发现，基本权限系统等成熟的生产级部署方案打包至本项目中，真正让用户做到 立等可取，开箱即用。

数据库供给方案所做的事情一言以蔽之：您填写一张表单，然后系统会自动根据表单的内容创建出对应的数据库集群。真正做到傻瓜式数据库管理。

Pigsty通过130+配置项定义了数据库与基础设施的方方面面，采用声明式的语法与幂等的执行机制，使用代码定义基础设施，在物理机与虚拟机上达到了与Kubernetes类似的舒爽体验，简单易用。

Pigsty是开源软件

Pigsty依托开源，回馈社区，是免费的开源软件。Pigsty基于 Apache 2.0 协议开源，但也提供 专业版 与 可选的商业支持服务。欢迎各位贡献ISSUE与PR，也欢迎捐赠与赞助。

Pigsty的监控系统基于开源组件Prometheus，Grafana，Alertmanager, Exporter进行深度定制开发。同时还包括Nginx, Dnsmasq/CoreDNS, NTP/Chrony, Consul/Etcd等基础设施。遵循业界监控最佳实践，可以方便地与已有监控基础设施集成。

Pigsty的供给方案基于流行的DevOps工具Ansible进行开发，部署涉及的组件包括：Postgres, Pgbouncer, Patroni, HAProxy, Keepalived。所有部署逻辑都以Ansible Role的方式编写，可以方便地进行集成、定制与二次开发。

PostgreSQL是世界上最先进的开源关系型数据库，而Pigsty旨在成为世界上最先进的开源关系型数据库的监控系统与供给方案。希望Pigsty能在各位使用PostgreSQL的过程中起到帮助。

Pigsty可以开箱即用

Pigsty提供了详实的中英文档供您参考。

更重要的是，Pigsty既提供了可公开访问的演示Demo，也自带了基于Vagrant的本地沙箱。您可以使用以下命令简单的在自己的笔记本上一键拉起带有数据库集群与监控基础设施的沙箱环境。

make up          # 拉起vagrant虚拟机
make ssh         # 配置虚拟机ssh访问
make init        # 初始化Pigsty
sudo make dns    # 写入Pigsty静态DNS域名（需要sudo,可选）
make mon-view    # 打开Pigsty首页（默认用户密码：admin:admin）

也可以在修改极少量配置后，使用完全相同的工作流初始化生产环境。

Pigsty的相关站点\

Pigsty提供了详实的中英文档供您参考。

中文站点：https://pigsty.cc

英文站点：https://pigsty.cc/en/

官方演示：http://demo.pigsty.cc

Github仓库：https://github.com/Vonng/pigsty

7 - Golden Monitoring Metrics: Errors, Latency, Throughput, Saturation

Preface

Playing with databases and playing with cars have something in common - they both require frequently checking the dashboard.

What are you doing staring at the dashboard? Looking at metrics. Why look at metrics? You need to understand the current operating state to effectively apply control.

Cars have many metrics: speed, tire pressure, torque, brake pad wear, various temperatures, and so on - all kinds of different ones.

But human attention span is limited, and the dashboard is only so big.

So, metrics can be divided into two categories:

Ones you will look at: Golden metrics / Key metrics / Core metrics
Ones you won’t look at: Black box metrics / Cold metrics

Golden metrics are those few critical core data points that need constant attention (or have an autopilot system/alarm system maintain constant attention for you), while cold metrics are usually only looked at during troubleshooting. Troubleshooting and post-mortems require restoring the scene as much as possible, so the more black box metrics the better. It’s very frustrating when you need them but don’t have them.

Today let’s talk about PostgreSQL’s core metrics. What are the core metrics for databases?

Database Metrics

Before discussing database core metrics, let’s take a look at what metrics are available.

avg(count by (ins) ({__name__=~"pg.*"}))
avg(count by (ins) ({__name__=~"node.*"}))

Over 1000 PostgreSQL metrics, over 2000 machine metrics.

These metrics are all data treasures, and mining and visualization can extract their value.

But for daily management, only a few core metrics are needed.

With thousands of available metrics, which ones are the core metrics?

Core Metrics

Based on experience and usage frequency, continuously subtracting, we can filter out some core metrics:

Metric	Abbreviation	Level	Source	Type
Error Log Count	Error Count	SYS/DB/APP	Log System	Error
Connection-Pool Queue	Queue Clients	DB	Connection-Pool	Error
Database Load	PG Load	DB	Connection-Pool	Saturation
Database Saturation	PG Saturation	DB	Connection-Pool & Node	Saturation
Master-Slave Replication Lag	Repl Lag	DB	Database	Latency
Average Query Response Time	Query RT	DB	Connection-Pool	Latency
Active Backend Processes	Backends	DB	Database	Saturation
Database Age	Age	DB	Database	Saturation
Queries Per Second	QPS	APP	Connection-Pool	Traffic
CPU Usage	CPU Usage	SYS	Machine Node	Saturation

In emergency situations: Errors are always the first priority golden metric.

In normal situations: Application perspective golden metrics: QPS and RT

In normal situations: DBA perspective golden metrics: DB saturation (water level)

Why These?

Error Metrics

The first priority metrics are always errors - errors are often directly user-facing.

If you could only choose one metric to monitor, then choose error metrics - like the number of error log entries per second at the application, system, and DB layers might be most appropriate.

For a car, if you could only choose one function on the dashboard, what would you choose?

Choose error metrics - keep the car moving.

Error-type metrics are very important and directly reflect system anomalies, such as connection pool queuing. But the biggest problem with error-type metrics is they’re only meaningful when alerting, making them difficult to use for daily water level assessment and performance analysis. Additionally, error-type metrics are often difficult to quantify precisely and can usually only give qualitative results: problematic vs not problematic.

Furthermore, error-type metrics are difficult to quantify precisely. We can only say: when the connection pool has queuing, database load is relatively high; the longer the queue, the higher the load; when there’s no queuing, database load isn’t very high - that’s all. For daily management, this capability is definitely insufficient.

An important reason for setting metrics and building monitoring/alerting systems is to prevent system overload. If the system is already overloaded with lots of errors, then using error phenomena to define saturation in reverse is meaningless.

The purpose of metrics is to measure the system’s operating state. We also care about other aspects of system capability: throughput/traffic, response time/latency, saturation/utilization/water level. These three represent system capability, service quality, and load level respectively.

Different focus points - backends (database users) focus on system capability and service quality, DBAs (database administrators) focus more on system load level.

Traffic Metrics

Traffic-type metrics have great potential, especially metrics like QPS and TPS which are quite representative.

Traffic metrics can directly measure system capability, such as how many orders processed per second, how many requests processed per second.

This is similar to a speedometer - highway speed limits, city speed limits. Environment, load.

But traffic metrics like TPS and QPS also have problems. Queries on a database instance are often varied and diverse. A query taking 10 microseconds and one taking 10 seconds are both counted as one Q in statistics. Metrics like QPS cannot be compared horizontally and only have rough reference value. Even when query types change, they can’t be compared vertically with their own historical data. It’s also difficult to set utilization targets for metrics like QPS and TPS. The same database executing SELECT 1 can reach hundreds of thousands of QPS, but when executing complex SQL, it might only reach thousands of QPS. Different load types and machine hardware will significantly impact a database’s QPS ceiling. QPS only has reference value when queries on a database are highly homogeneous with no complex changes. Under such strict conditions, you can set a QPS water level target through stress testing.

Latency Metrics

Similar to gear levels - slow queries, low gear, slow speed. Low query tier, low TPS water level. High query tier, high TPS water level.

Latency is suitable for measuring system service quality.

Compared to QPS/TPS, metrics like RT (Response Time) actually have more reference value. Because increased response time is often a precursor to system saturation. According to empirical rules, the higher the database load, the higher the average response time for queries and transactions. An advantage of RT over QPS is that RT can have a utilization target set - for example, you can set an absolute threshold for RT: not allowing slow queries with RT over 1ms in production OLTP databases. But metrics like QPS are difficult to draw red lines for. However, RT also has its own problems. The first problem is it’s still qualitative rather than quantitative - increased latency is just a warning of system saturation but can’t be used to precisely measure system saturation. The second problem is that RT statistics available from databases and middleware are usually averages, but what really provides warning effect might be statistics like P99 and P999.

Saturation Metrics

Saturation metrics are like a car’s tachometer, fuel gauge, and temperature gauge.

Saturation metrics are suitable for measuring system load.

The load metric users expect is a saturation metric. So-called saturation is how “full” the service capacity is - usually a measure of a specific metric of the currently most limited resource in the system. Generally, 0% saturation means the system is completely idle, 100% saturation means full load. Systems will experience severe performance degradation before reaching 100% utilization, so setting metrics also needs to include a utilization target or water level red line and yellow line. When system instantaneous load exceeds the red line, it should trigger alerts; when long-term load exceeds the yellow line, it should trigger capacity expansion.

Other Optional Metrics
Transactions Per Second	TPS	APP	Connection-Pool	Traffic
Disk IO Usage	Disk Usage	SYS	Machine Node	Saturation
Memory Usage	Mem Usage	SYS	Machine Node	Saturation
Network Bandwidth Usage	Net Usage	SYS	Machine Node	Saturation
TCP Errors: Overflow/Retransmission	TCP ERROR	SYS	Machine Node	Error

8 - PostgreSQL监控系统Pigsty概述

近自己做了套数据库监控系统，搞的还可以，简单给大家介绍一下。

Pigsty is an advanced PostgreSQL monitoring systemd based on open source projects like prometheus & grafana. PIGSTY /pɪɡ staɪ/ is the abbreviation of “Postgres in Grafana Style”.

Pigsty是一个基于Grafana与Prometheus与Consul的Postgres数据库监控系统。

整体架构

TLDR: (Node/Pg/Pgbouncer) Exporter Discovered by Consul to Prometheus to Grafana

┏━━━━━━━━━━━┓     ┏━━━━━━━━━━━━━━━━━━━━┓
┃   Node    ┃ --> ┃   Node    Exporter-┃┐
┃ Pgbouncer ┃ --> ┃ Pgbouncer Exporter-┃┼--> Prometheus ---> Grafana
┃ Postgres  ┃ --> ┃ Postgres  Exporter-┃┘        ↑
┃           ┃     ┗━━━━━━━━━━━━━━━━━━━━┛  (Service Discovery)
┃  Consul   ┃ ----------------------------->   Consul
┗━━━━━━━━━━━┛

一言以蔽之：用Exporter取指标数据，通过Consul服务发现赋予身份标签与组织结构，存进Prometheus中进行预处理计算，最后使用Grafana展示

这里能看到的主要还是Grafana里的Dashboard，因此主要还是介绍脸面上的东西。\

层次组织

监控主要分为五个层次，集群（cluster），服务（service），实例（instance），数据库（database），与节点（node）。不过在本系统中，服务层次的监控指标被整合至集群级别，数据库层次的监控指标被整合至实例级别。因此实际上，只有三个核心层次的监控展示：集群，实例，节点。

集群使用cls唯一标识，名称类似于：pg-test-tt
实例使用ins唯一标识，名称类似于：pg-test-tt-0，以集群为前缀，序号为后缀。后缀为0的实例通常是集群中的主库。
节点使用ip唯一标识。

除此之外，还有一些其他层次的Dashboard：例如全局大盘概览，分片库专用的Shard Dashboard，每一个数据库具体的Database Dashboard、连接池Pool层次的Dashboard，具体到某一个库上某一个查询的PG Query Dashboard，Pgbouncer中间件专用Dashboard，等等，这些衍生或周边的Dashboard就不介绍了。

功能简介

核心功能：日常巡检，故障排查，性能优化，全知即全能。

PG全局监控
PG Shard监控
PG集群监控
PG实例监控
PG实例监控（故障排查专用视图）
PG节点监控
PG慢查询平台
Redis全局概览
Redis集群监控
Redis实例监控
PG集群健康度评估系统

首页导航概览

包含PG和Redis两部分，左侧为全局指标概览，右侧为集群导航。中间为全局报警与事件提醒。点击右上角的导航链接，或者页面中的可导航元素（Shard，集群名，实例名，IP等）可跳转至感兴趣的面板

DB监控：指标介绍

指标丰富程度

你可以不看，我不能没有。

每个实例包括了约3300个指标，其中：

数据库与连接池指标1000个，其中规则定义衍生指标250个。

节点指标约2000个，其中规则定义的衍生指标700个。

举个例子，单纯一个QPS，就可以衍生出下面近30个指标。\

这里随便挑一些重要的指标介绍一下\

指标内容

按Google SRE实践划分的四类黄金指标

错误

配置错误：关键功能是否配置正常：校验和，Numa，透明大页，同步提交等。
内存错误，TCP错误，时间漂移错误
服务宕机：机器，数据库，连接池，监控组件
数据库客户端排队，IdleInXact连接，超长事务，死锁，复制中断，大量回滚，监控报错

饱和度

PG Load, Node Load
CPU使用，内存使用，磁盘使用，网卡带宽利用率，缓存命中率，后端连接使用，连接池使用

流量

数据库直接指标：QPS，TPS，查询细分QPS
间接流量指标：连接池进出流量，WAL写入量，增删改查条数，块访问量，缓冲区访问量
节点流量：磁盘IO流量，网络IO流量，内存页面换入换出

延迟

事务平均响应时间 Xact RT
查询平均响应时间 Query RT
语句平均响应时间：Statement RT
磁盘平均响应时间：Disk R/W Latency
复制延迟（以秒或字节计算）
监控查询延迟

DB监控：PG实例

实例概览

实例身份信息：集群名，ID，所属节点，软件版本，所属集群其他成员等
实例配置信息：一些关键配置，目录，端口，配置路径等
实例健康信息，实例角色（Primary，Standby）等。
黄金指标：PG Load，复制延迟，活跃后端，排队连接，查询延迟，TPS，数据库年龄
数据库负载：实时（Load0），1分钟，5分钟，15分钟
数据库警报与提醒事件

关于PG Load，可以参考本号前一篇文章，如何给PostgreSQL定KPI

节点概览

四大基本资源：CPU，内存，磁盘，网卡的配置规格，关键功能，与核心指标
右侧是网卡详情与磁盘详情

单日统计

以最近1日为周期的统计信息（从当前时刻算起的前24小时），比如最近一天的查询总数，返回的记录总数等。上面两行是节点级别的统计，下面两行是主要是PG相关的统计指标。

对于计量计费，水位评估特别有用。

复制

当前节点的Replication配置
复制延迟：以秒计，以字节计的复制延迟，复制槽堆积量
下游节点对应的Walsender统计
各种LSN进度，综合展示集群的复制状况与持久化状态。
下游节点数量统计，可以看出复制中断的问题

事务

事务部分用于洞悉实例中的活动情况，包括TPS，响应时间，锁等。

TPS概览信息：TPS，TPS与过去两天的DoD环比。DB事务数与回滚数
回滚事务数量与回滚率
TPS详情：绿色条带为±1σ，黄色条带为±3σ，以过去30分钟作为计算标准，通常超出黄色条带可认为TPS波动过大
Xact RT，事务平均响应时间，从连接池抓取。绿色条带为±1σ，黄色条带为±3σ。
TPS与RT的偏离程度，是一个无量纲的可横向比较的值，越大表示指标抖动越厉害，计算方式为：(μ/σ)^2
按照DB细分的TPS与事务响应时间，通常一个实例只有一个DB，但少量实例有多个DB。
事务数，回滚数（TPS来自连接池，而这两个指标直接来自DB本身）
锁的数量，按模式聚合（8种表锁），按大类聚合（读锁，写锁，排他锁）

查询

大多数指标与事务中的指标类似，不过统计单位从事务变成了查询语句。查询部分可用于分析实例上的慢查询，定位性能瓶颈。

QPS 每秒查询数，与Query RT查询平均响应时间，以及这两者的波动程度，QPS的周期环比等
生产环境对查询平均响应时间有要求：1ms为红线，100ms就该约谈了。

语句

语句展示了查询中按语句细分的指标。每条语句（查询语法树抽离常量变量后如果一致，则算同一条查询）都会有一个查询ID，可以在慢查询平台中获取到具体的语句与详细指标与统计。

左侧慢查询列表是按pg_stat_statments中的平均响应时间从大到小排序的，点击查询ID会自动跳转到慢查询平台
这里列出的查询，是累计查询耗时最长的32个查询，但排除只有零星调用的长耗时单次查询与监控查询。
右侧包括了每个查询的实时QPS，平均响应时间。按照RT与总耗时的排名。

后端进程

后端进程用于显示与PG本身的连接，后端进程相关的统计指标。特别是按照各种维度进行聚合的结果，特别适合定位雪崩，慢查询，其他疑难杂症。

后端进程数按种类聚合，后端进程按状态聚合，后端进程按DB聚合，后端进程按等待事件类型聚合。
活跃状态的进程/连接，在事务中空闲的连接，长事务。

连接池

连接池部分与后端进程部分类似，但全都是从Pgbouncer中间件上获取的监控指标

连接池后端连接的状态：活跃，刚用过，空闲，测试过，登录状态。
分别按照User，按照DB，按照Pool（User:DB）聚合的前端连接，用于排查异常连接问题。
等待客户端数（重要），以及队首客户端等待的时长，用于定位连接堆积问题。
连接池可用连接使用比例。

数据库概览

Database部分主要来自pg_stat_database与pg_database，包含数据库相关的指标：

WAL Rate，标识数据库的写入负载，每秒产生的WAL字节数量。
Buffer Hit Rate，数据库 ShareBuffer 命中率，未命中的页面将从操作系统PageCache和磁盘获取。
每秒增删改查的记录条数
临时文件数量与临时文件大小，可以定位大型查询问题。

持久化

持久化主要包含数据落盘，Checkpoint，块访问相关的指标

重要的持久化参数，比如是否出现数据校验和验证失败（如果启用可以检测到数据腐坏）
数据库文件（DB，WAL，Log）的大小与增速。
检查点的数量与检查点耗时。
每秒分配的块，与每秒刷盘的块。每秒访问的块，以及每秒从磁盘中读取的块。（以字节计，注意一个Buffer Page是8192，一个Disk Block是4096）

监控Exporter

Exporter展示了监控系统组件本身的监控指标，包括：

Exporter是否存活，Uptime，Exporter每分钟被抓取的次数
每个监控查询的耗时，产生的指标数量与错误数量。

DB监控：PG集群

PG集群监控是最常用的Dashboard，因为PG以集群为单位提供服务，因此Cluster集合了最完整全面的信息。

大多数监控图都是实例级监控的泛化与上卷，即从展示单个实例内的细节，变为展现集群内每个实例的信息，以及集群和服务层次聚合后的指标。

集群概览

Cluster级别的集群概览相比实例级别多了一些东西：

时间线与领导权，当数据库发生Failover或Switchover时，时间线会步进，领导权会发生变化。
集群拓扑，集群拓扑展现了集群中的复制拓扑，以及采用的复制方式（同步/异步）。
集群负载，包括整个集群实时、1分钟、5分钟、15分钟的负载情况。以及集群中每个节点的Load1
集群报警与事件。

集群复制

Cluster级别的Dashboard与Instance级别Dashboard最重要的区别之一就是提供了整个集群的复制全景。包括：

集群中的主库与级联桥接库。集群是否启用同步提交，同步从库名称。桥接库与级联库数量，最大从库配置
成对出现的Walsender与Walreceiver列表，体现一对主从关系的复制状态
以秒和字节衡量的复制延迟（通常1秒的复制延迟对应10M~100M不等的字节延迟），复制槽堆积量。
从库视角的复制延迟
集群中从库的数量，备份或拉取从库时可以从这里看到异常。
集群的LSN进度，用于整体展示集群的复制状态与持久化状态。

节点指标

PG机器的相关指标，按照集群进行聚合。

事务与查询

与实例级别的类似，但添加了Service层次的聚合（一个集群通常提供primary与standby两种Service）。

其他指标与实例级别差别不大。\

DB监控：PG慢查询平台

显示慢查询相关的指标，上方是本实例的查询总览。鼠标悬停查询ID可以看到查询语句，点击查询ID会跳转到对应的查询细分指标页（Query Detail）。

左侧是格式化后的查询语句，右侧是查询的主要指标，包括
- 每秒查询数量：QPS
- 实时的平均响应时间（RT Realtime）
- 每次查询平均返回的行数
- 每次查询平均用于BlockIO的时长
- 响应时间的均值，标准差，最小值，最大值（自从上一次统计周期以来）
- 查询最近一天的调用次数，返回行数，总耗时。以及自重置以来的总调用次数。
下方是指定时间段的查询指标图表，是概览指标的细化。

9 - PostgreSQL's KPI

Managing databases is similar to managing people - both need KPIs (Key Performance Indicators). So what are database KPIs? This article introduces a way to measure PostgreSQL load: using a single horizontally comparable metric that is basically independent of workload type and machine type, called PG Load.

0x01 Introduction

In real production environments, there are often needs to measure database performance and load, and evaluate database utilization levels. One of the most basic forms is: can we have a single KPI-like metric that directly tells users whether their beloved database load has exceeded warning thresholds? Is the workload saturated or not?

Of course, there’s an important piece of information implied here - users expect the load metric to be a Saturation indicator. Saturation refers to how “full” the service capacity is, usually measured by a specific indicator of the most constrained resource in the system. Generally speaking, 0% saturation means the system is completely idle, 100% saturation means full load. Systems experience severe performance degradation before reaching 100% utilization, so setting indicators also requires including a utilization target, or warning thresholds (red line, yellow line). When system instantaneous load exceeds the red line, alerts should be triggered; when long-term load exceeds the yellow line, capacity expansion should be performed.

Unfortunately, defining how “saturated” a system is isn’t easy and often requires indirect indicators. Evaluating a database’s load level traditionally involves comprehensive assessment based on these types of indicators:

Traffic: Queries per second (QPS), or transactions per second (TPS)
Latency: Average query response time (Query RT), or average transaction response time (Xact RT)
Saturation: Machine load, CPU usage, disk I/O bandwidth saturation, network I/O bandwidth saturation
Errors: Database client connection queuing

These indicators all have reference value for database performance evaluation, but they also have various problems.

0x02 Problems with Common Evaluation Indicators

Let’s look at what problems these existing common indicators have.

The first to pass are error-type indicators, such as connection pool queuing. The biggest problem with error-type indicators is that when errors appear, saturation may already be meaningless. An important reason for evaluating saturation is to prevent system overload. If the system is already overloaded with many errors, using error phenomena to define saturation in reverse is meaningless. Additionally, error-type indicators are difficult to quantify precisely. We can only say: when connection pools have queuing, database load is relatively high; the longer the queue, the higher the load; when there’s no queuing, database load isn’t very high, that’s all. Such definitions certainly can’t satisfy people.

The second to pass are system-level (machine-level) indicators. Databases run on machines, and indicators like CPU usage and I/O usage are closely related to database load levels. If CPU and I/O are bottlenecks, theoretically bottleneck resource saturation indicators can directly be used as database saturation indicators. But this isn’t always true - the system bottleneck might be in the database itself. Moreover, strictly speaking, they are machine KPIs rather than DB KPIs. When evaluating database load, system-level indicators can certainly be referenced, but the DB layer should also have its own evaluation indicators. Database saturation indicators should exist first before comparing whether underlying resources or the database itself saturates first and becomes the bottleneck. This principle also applies to indicators observed at the application layer.

Traffic-type indicators have great potential, especially QPS and TPS which are quite representative. But these indicators also have problems. Queries on a database instance are often varied and diverse. A query taking 10 microseconds and one taking 10 seconds are both counted as one Q in statistics. Indicators like QPS cannot be compared horizontally and only have rough reference value. Even when query types change, they can’t be compared vertically with their own historical data. It’s also difficult to set utilization targets for QPS and TPS indicators. The same database executing SELECT 1 can achieve hundreds of thousands of QPS, but when executing complex SQL, it might only achieve thousands of QPS. Different workload types and machine hardware significantly affect database QPS limits. Only when queries on a database are highly uniform and without complex changes can QPS have reference value. Under such strict conditions, QPS watermark targets can be set through stress testing.

Compared to QPS/TPS, RT (Response Time) indicators actually have more reference value. Because increasing response time is often a precursor to system saturation. According to experience, the higher the database load, the higher the average response time for queries and transactions. One advantage of RT over QPS is that RT can have utilization targets set, such as setting an absolute threshold for RT: not allowing production OLTP databases to have slow queries with RT exceeding 1ms. But indicators like QPS are hard to draw red lines for. However, RT has its own problems. The first problem is that it’s still qualitative rather than quantitative - latency increases are warnings of system saturation but can’t precisely measure system saturation. The second problem is that RT statistics indicators usually available from databases and middleware are averages, but what truly provides warning effects might be statistics like P99, P999.

After criticizing all common indicators here, what kind of indicators are suitable as database saturation indicators?

0x03 Measuring PG Load

Let’s reference how Node Load and CPU Utilization evaluation indicators are designed.

Node Load

To see machine load levels, you can use the top command in Linux systems. The first line of top command output prominently displays the current machine’s average load levels for 1 minute, 5 minutes, and 15 minutes.

$ top -b1
top - 19:27:38 up 18:49,  1 user,  load average: 1.15, 0.72, 0.71

Here the three numbers after load average represent the system’s average load levels for the last 1 minute, 5 minutes, and 15 minutes respectively.

What do these numbers actually mean? The simple explanation is: the larger this number, the busier the machine.

In single-core CPU scenarios, Node Load (hereafter referred to as load) is a very standard saturation indicator. For single-core CPUs, when load is 0, the CPU is in a completely idle state; when load is 1 (100%), the CPU is in exactly full working state. When load exceeds 100%, the portion exceeding 100% represents tasks queuing.

Node Load also has its own utilization targets. Usually the experience is that for single cores: 0.7 (70%) is the yellow line, meaning the system has problems and needs checking soon; 1.0 (100%) is the red line, load greater than 1 means processes start accumulating and need immediate attention; 5.0 (500%) is the death line, meaning the system is basically blocked.

For multi-core CPUs, things are slightly different. Assuming there are n cores, when system load is n, all CPUs are in full working state; when system load is n/2, we can roughly consider half the CPU cores are running at full load. Thus a 48-core CPU machine has a full load of 48. Overall, if we divide machine load by the machine’s CPU core count, the resulting indicator stays consistent with single-core scenarios (0% idle, 100% full load).

CPU Utilization

Another very instructive indicator is CPU Utilization. CPU utilization is actually calculated through a simple formula. For single-core CPUs:

1 - irate(node_cpu_seconds_total{mode="idle"}[1m]

Here node_cpu_seconds_total{mode="idle"} is a counter indicator representing total time the CPU has been in idle state. The irate function derives this indicator with respect to time, yielding the time per second the CPU is in idle state, in other words, the CPU idle rate. Subtracting this value from 1 gives CPU utilization.

For multi-core CPUs, you just need to add up each CPU core’s utilization and divide by the CPU core count to get overall CPU utilization.

So what reference value do these two indicators have for PG load?

Database Load (PG Load)

Can PG load also be defined similarly to CPU utilization and machine load? Of course, and this is an excellent idea.

Let’s first consider PG load in single-process scenarios. Suppose we need an indicator where the load factor is 0 when the PG process is completely idle, and load is 1 (100%) when the process is at full capacity. Analogous to CPU utilization definition, we can use “the proportion of time a single PG process is in active state” to represent “single PG backend process utilization”.

As shown in Figure 1, within a one-second statistical period, PG is in active state (executing queries or transactions) for 0.6 seconds, so the PG load for this second is 60%. If this unique PG process is busy throughout the entire statistical period and has 0.4 seconds of tasks queuing, then PG load can be considered 140%.

For parallel scenarios, the calculation method is similar to multi-core CPU utilization. First, sum up the active time of all PG processes within the statistical period (1s), then divide by “available PG processes/connections”, or “available parallelism”, to get PG’s own utilization indicator, as shown in Figure 3. Two PG backend processes have active durations of 200ms+400ms and 800ms respectively, so overall load level is: (0.2s + 0.4s + 0.8s) / 1s / 2 = 70%

To summarize, PG load for a certain time period can be defined as:

pg_load = pg_active_seconds / time_period / parallel

pg_active_seconds is the sum of time all PG processes are in active state during this time period
time_period is the statistical period for load calculation, usually 1 minute, 5 minutes, 15 minutes, and real-time (less than 10 seconds)
parallel is PostgreSQL’s available parallelism, which will be explained in detail later

Since the quotient of the first two items is actually the total active duration per second over a period of time, this formula can be further simplified to the derivative of active duration with respect to time divided by available parallelism:

rate(pg_active_seconds[time_period]) / parallel

time_period is usually a fixed constant (1, 5, 15 minutes), so the problem becomes how to obtain the PG process total active time indicator pg_active_seconds and how to evaluate the database’s available parallelism max_parallel.

0x04 Calculating PG Load Saturation

Transaction or Query?

When we say database processes are active/idle, what exactly are we talking about? What does it mean when PG is in active state? If PG backend processes are executing queries, then certainly we can consider PG to be in busy state. But as shown in Figure 4, if PG processes are executing interactive transactions but not actually executing queries, i.e., the so-called “Idle in Transaction” state, how should we calculate “active duration”? The 200ms idle time between two queries in Figure 4 - should this time be considered “active” or “idle”?

The core issue here is how to define active state: whether database processes being in transactions count as active, or only when actually executing queries. For scenarios without interactive transactions, one query is one transaction, so either way is the same. But for multi-statement, especially interactive multi-statement transactions, there’s a clear difference. From a resource usage perspective, not executing queries means not consuming database resources. But idle transactions occupy connections preventing connection reuse, and Idle In Transaction itself should be a situation to avoid. Overall, both definition methods work; using the transaction method slightly overestimates application load but may be more suitable from a load evaluation perspective.

How to Obtain Active Duration

After deciding on the database backend process activity definition, the second question is: how to obtain database active duration over a period of time? Unfortunately, in PG, users can hardly obtain this performance indicator through the database itself. PG provides a system view: pg_stat_activity, which shows the list of currently running Postgres processes, but this is a point-in-time snapshot that can only roughly tell how many backend processes are in active vs idle states at the current moment. Counting database active time over a period becomes difficult. One solution is using Load-like calculation methods, periodically sampling the number of active processes in PG to calculate a load indicator. However, there’s a better approach here, but it requires middleware assistance.

Database middleware is very important for performance monitoring because many indicators aren’t provided by the database itself and can only be exposed through middleware. Taking Pgbouncer as an example, Pgbouncer maintains a series of statistical counters internally. Using SHOW STATS prints these indicators, such as:

total_xact_count: Total number of transactions executed
total_query_count: Total number of queries executed
total_xact_time: Total time spent on transaction execution
total_query_time: Total time spent on query execution

Here total_xact_time is the data we need - it records the total transaction time spent on a database in the Pgbouncer middleware. We just need to derive this indicator with respect to time to get the desired data: active duration proportion per second.

Using Prometheus PromQL to express the calculation logic, first derive the transaction time counter to calculate active duration per second at 1-minute, 5-minute, 15-minute, and real-time granularities (between the last two sampling points). Then roll up to sum, rolling database-level indicators up to instance-level indicators. (Connection pool SHOW STATS statistics here are per database, so when calculating instance-level total active duration, should roll up and sum, eliminating database dimension labels: sum without(datname))

- record: pg:ins:xact_time_realtime
expr: sum without (datname) (irate(pgbouncer_stat_total_xact_time{}[1m]))
- record: pg:ins:xact_time_rate1m
expr: sum without (datname) (rate(pgbouncer_stat_total_xact_time{}[1m]))
- record: pg:ins:xact_time_rate5m
expr: sum without (datname) (rate(pgbouncer_stat_total_xact_time{}[5m]))
- record: pg:ins:xact_time_rate15m
expr: sum without (datname) (rate(pgbouncer_stat_total_xact_time{}[15m]))

The resulting indicators can already be compared vertically with themselves and horizontally between instances of the same specifications. And regardless of database workload type, this indicator can be used.

However, instances of different specifications still can’t be compared using this indicator. For example, for single-core single-connection PG, active duration per second at full load might be 1 second, which is 100% utilization. For 64-core 64-connection PG, active duration per second at full load is 64 seconds, which is 6400% utilization. Therefore, normalization is needed, which brings us to another question.

How to Define Available Parallelism?

Unlike CPU utilization, PG’s available parallelism doesn’t have a clear definition and has some subtle relationships with workload types. But what can be determined is that within a certain range, maximum available parallelism has a rough linear relationship with CPU core count. Of course this conclusion assumes maximum database connections significantly exceed CPU core count. If only 30 connections are allowed on a 64-core CPU, then certainly maximum available parallelism is 30, not 64 CPU cores. Software parallelism ultimately needs hardware parallelism support, so we can simply use the instance’s CPU core count as available parallelism.

Running 64 active PG processes on 64-core CPU gives load of (6400% / 64 = 100%). Similarly, running 128 active PG processes gives load of (12800% / 64 = 200%).

Using the active duration per second indicator calculated above, we can compute instance-level PG load indices.

- record: pg:ins:load0
expr:  pg:ins:xact_time_realtime / on (ip) group_left()  node:ins:cpu_count
- record: pg:ins:load1
expr: pg:ins:xact_time_rate1m  / on (ip) group_left()  node:ins:cpu_count
- record: pg:ins:load5
expr: pg:ins:xact_time_rate5m  / on (ip) group_left()  node:ins:cpu_count
- record: pg:ins:load15
expr: pg:ins:xact_time_rate15m  / on (ip) group_left()  node:ins:cpu_count

Another Interpretation of PG LOAD

If we carefully examine the definition of PG Load, we can find that active duration per second can roughly equal: TPS x XactRT, or QPS x Query RT. This makes sense - assuming QPS is 1000 and each query RT is 1ms, then time spent on queries per second is 1000 * 1ms = 1s.

Therefore, PG Load can be viewed as a derived indicator composed of three core indicators: tps * xact_rt / cpu_count

TPS and RT each have their problems for load evaluation, but when combined through simple multiplication into a new composite indicator, they suddenly show magical power (although actually calculated through other more accurate methods).

0x05 Actual Effects of PG Load

Next, let’s look at PG Load’s performance in actual production environments.

PG Load has two most direct uses: alerting and capacity evaluation.

Case 1: Used for Alerting: Service Unavailability Due to Slow Query Accumulation

The figure below shows a production incident scene where a business deployed a slow query, instantly causing connection pools to be occupied by slow queries, leading to accumulation. We can see that both PG Load and RT reflected the fault situation promptly and accurately, while TPS appeared to drop into a pit, not particularly noticeable.

In terms of effect, PG Load1 and PG Load0 (real-time load) are quite sensitive indicators that can promptly and accurately respond to most faults related to pressure and load. So they were adopted as core alerting indicators.

PG Load utilization targets have some empirical values: yellow line is usually 50%, meaning threshold requiring attention; red line is usually 70%, meaning alert line requiring immediate action; 500% or higher usually means this instance has been overwhelmed.

Case 2: Used for Utilization Assessment and Capacity Planning

Compared to alerting, utilization assessment and capacity planning are more like PG Load’s core uses. After all, alerting needs can still be met through latency, queued connections and other indicators.

Here, the 15-minute load of PG clusters is a good reference value. Through historical averages, peaks, and other statistics of this indicator, we can easily see which clusters are in high-load states requiring expansion and which clusters are in low resource utilization states requiring downsizing.

CPU utilization is another very important capacity evaluation indicator. We can see that PG Load has a very close relationship with CPU Usage. However, compared to CPU usage, PG Load more purely reflects the database’s own load level, filtering out irrelevant loads on the machine and maintenance work (backup, cleanup, garbage collection) noise, making it smoother. Therefore, it’s very suitable for capacity evaluation.

When system load is long-term at 30%~50%, expansion should be considered.

0x06 Conclusion

This article introduces a quantitative way to measure PG load: the PG Load indicator.

This indicator can simply and intuitively reflect database instance load levels.

This indicator is very suitable for capacity evaluation and can also serve as a core alerting indicator.

This indicator can basically ignore workload type and machine type for vertical historical comparison and horizontal utilization comparison.

This indicator can be calculated through simple methods: total active time of backend processes per second divided by available concurrency.

Data required for this indicator needs to be obtained from database middleware.

PG Load’s 0 represents no load, 100% represents full load. Yellow line empirical value is 50%, red line empirical value is 70%.

PG Load is a good indicator 👍

10 - PostgreSQL监控系统概览

PostgreSQL驾驶技巧

PostgreSQL是一个很棒的数据库，但也相当复杂。上手虽简单，但想要用好不容易，想要管理好就更麻烦了。监控系统是几乎所有运维工作的基础，更亦是驾驭数据库的必备工具。用好一个监控系统，理解各种指标背后的意义并不是一件简单的事情，因此本司机决定写一系列文章，来介绍了PostgreSQL监控系统的设计，实施与使用。

0x01 PostgreSQL监控面板

为了帮助读者形成一种直觉，这里展示了在实际环境中，我所使用的监控面板。最为常用的监控面板为“单数据库实例”监控，并分为7个主要的子区域：概览，操作系统，数据库活动与会话，复制，检查点与WAL，数据库冲突，以及数据库对象统计，如下所示。

监控系统的架构\

程序员的一个基本原则就是：“不要造轮子”。所以如果有现成的开源组件能用，那是最好不过。一个比较简单的实用监控系统架构如下图所示。主要组件包括：Prometheus，Grafana，Consul，以及各个Exporter。各类Exporter从数据库，连接池，以及机器上获取指标数据库，Prometheus从Consul中发现这些Exporter，并向其拉取数据，Grafana负责展示Prometheus中的数据，而AlertManager负责报警相关的任务。

监控系统的指标

监控面板与系统架构，不过是监控系统的面子，真正的里子在于监控指标。哪些指标需要监控，每种指标如何解读，这才是真正重要的东西。毕竟知道了这些知识，即使没有监控系统，通过命令行手工检查也可以发现问题，但不了解指标背后的涵义，即使再傻瓜再漂亮的监控面板也于事无补。

下面会简单介绍单实例监控面板中涉及到的指标。

概览

概览是一些重要的核心指标，一眼扫过去就应当能大致定位数据库的主要问题。这里主要包括：

CPU使用率、平均查询时间、TPS、QPS，连接池排队数，活跃连接数：判断数据库负载水平
内存空闲率：判断是否出现进程异常（例如突然被杀）
磁盘空闲率：判断磁盘是否被写满
数据库年龄：数据库是否快出现事务标识回卷故障。
数据库大小，缓存命中率：判断异常活动

操作系统指标\

操作系统指标主要包括：

CPU与负载水平
内存使用，特别需要关注操作系统cache部分的变化。
网络与磁盘的IO吞吐量，磁盘的IOPS。

操作系统的指标能直观地反映大部分数据库活动异常，对于硬件类故障的排查尤有帮助。

数据库活动与会话

Activity与Session是数据库当前状态的直观反映，指标包括：

TPS/QPS，平均响应时间都是最直接的负载指标。
缓存命中率对数据库性能有直接的影响，注意这里只是PostgreSQL本身BufferPool的命中率，并没有计算操作系统的文件缓存。
后端连接按状态的分布，active状态的连接是很重要的负载指标，而idle in transaction的连接数则需要特别关注。
临时文件通常是由一些复杂查询，长查询，过多的临时文件容易导致性能恶化。

复制指标

很多类型的故障与主从复制滞后有关，因此，ReplicationDelay是一个重要的监控指标。

在主库上可以获取从库的复制延迟，包括LSN落后的字节数，以及滞后的时长（v10以后）。在从库上也可以获得距离主库的滞后时长，但这种方法通常不如前一种准确。

检查点与WAL

检查点会吃IO，一方面它会引发大量页面落盘，另一方面在默认full_page_write打开的情况下，每一次检查点都会导致其后的WAL量升高，吃磁盘与网络的带宽。在一些情况下可能会成为故障的原因。因此有必要监控检查点与WAL，相关指标包括：

WAL日志的生成速率
LSN增长的速度
缓冲区刷回的数目，通常缓冲区刷回主要由Checkpoint负责，如果有大量缓冲区由后端进程负责刷盘，就需要检查并调整相关参数的配置了。
数据库与WAL日志的大小。

锁与数据库冲突

锁按类型分布的数据对于判断数据库活动的类型很有帮助。

对象级统计

对象级统计能够帮助DBA快速定位故障的根源。包括：

每个表的访问次数：索引扫描/顺序扫描。频繁出现的顺序扫描往往意味着索引出现了问题。
表中元组的拉取，修改，增删改查数目，表的大小，年龄，都有助于精确定位问题的来源。
死元组的比例：死元组的比例可用于估算表的膨胀程度，对于维持性能很有帮助。（还有另外一种通过统计数据估算表膨胀率的方法）
函数的调用次数与执行时间。

监控指标的来源\

统计收集器

对于PostgreSQL而言，其监控指标主要源于自带的统计收集器。PostgreSQL提供了很多系统视图，因此能很简单地以SQL语句的形式拉取统计数据。系统视图的具体定义可以参考文档，后续文章也会依此详细介绍。其中，比较重要的视图包括：

从统计视图中收集数据有一些注意事项：

统计数据并非实时更新的，每个服务进程只有在闲置前会更新统计计数。所以正在执行的查询和事务不影响计数。
收集器本身每隔（PGSTAT_STAT_INTERVAL=500ms）才发送一次新的报告。所以除了当前进程活动track_activity之外的统计指标都不是最新的。
在事务中执行的统计查询，统计数据不会发生变化。使用pg_stat_clear_snapshot()来获取最新的快照。

要让数据库收集这些统计数据，需要在postgresql.conf中打开各种track_*选项。

系统视图

除了统计视图外，还有一些系统视图也能提供很多有价值的指标信息，例如pg_database与pg_locks，分别提供了数据库年龄尺寸，以及数据库内的锁等待信息。此外，还有很多系统指标是通过内建的函数提供的。

结语

监控是一个很大的话题，够写半本书了，因此这篇文章能做的也就是给读者留下一点直觉：监控系统应该是啥样子的。其实有了以上的信息，一个合格的架构师与PostgreSQL DBA应该能很轻松的复现该监控系统了。具体的实现细节，将在后续文章中继续介绍。

11 - Relation Bloat Monitoring and Management

Author: Vonng (@Vonng)

PostgreSQL uses MVCC as its primary concurrency control technology. While it has many benefits, it also brings other effects, such as relation bloat. Relation bloat (table and index) negatively impacts database performance and wastes disk space. To keep PostgreSQL always at optimal performance, it’s necessary to perform timely garbage collection on bloated relations and regularly rebuild excessively bloated relations.

In actual operations, garbage collection isn’t that simple. Here are a series of issues:

What causes relation bloat?
How to measure relation bloat?
How to monitor relation bloat?
How to handle relation bloat?

This article will explain these issues in detail.

Relation Bloat Overview

Suppose a relation actually occupies 100G of storage, but much space is wasted by dead tuples, fragments, and free areas. If it were compressed into a new relation, it would occupy 60G, then we can approximately consider this relation has a bloat rate of (100 - 60) / 100 = 40%.

Regular VACUUM cannot solve table bloat issues. Dead tuples themselves can be reclaimed by concurrent VACUUM mechanisms, but the fragments and holes they create cannot. For example, even after deleting many dead tuples, the table size cannot be reduced. Over time, relation files become filled with many holes, wasting substantial disk space.

The VACUUM FULL command can reclaim this space by copying live tuples from the old table file to a new table, compacting the table by rewriting the entire table. However, in actual production, this operation holds an AccessExclusiveLock on the table, blocking normal business access, making it unsuitable for non-stop services. pg_repack is a practical third-party plugin that can perform lock-free VACUUM FULL while online business continues normally.

Unfortunately, there’s no best practice for when to perform VACUUM FULL to handle bloat. DBAs need to formulate cleanup strategies for their specific business scenarios. However, regardless of the strategy adopted, the mechanisms for implementing these strategies are similar:

Monitor, detect, and measure relation bloat levels
Handle relation bloat based on bloat level, timing, and other factors

Here are some key questions: first, how to define relation bloat rate?

Measuring Relation Bloat

To measure relation bloat levels, we first need to define a metric: bloat rate.

The calculation idea for bloat rate is: estimate the space that would be occupied if the target table were in a compact state through statistical information, and the proportion of actual used space exceeding this compact space is the bloat rate. Therefore, bloat rate can be defined as 1 - (total bytes occupied by live tuples / total bytes occupied by relation).

For example, if a table actually occupies 100G of storage, but much space is wasted by dead tuples, fragments, and free areas, and if compressed into a new table it would occupy 60G, then the bloat rate is 1 - 60/100 = 40%.

Getting relation size is relatively simple and can be obtained directly from system catalogs. So the key issue is how to obtain total bytes of live tuples.

Precise Calculation of Bloat Rate

PostgreSQL comes with the pgstattuple module, which can be used to precisely calculate table bloat rates. For example, the tuple_percent field here is the percentage of actual tuple bytes to total relation size. Subtracting this value from 1 gives the bloat rate.

vonng@[local]:5432/bench# select *,
                          1.0 - tuple_len::numeric / table_len as bloat
                          from pgstattuple('pgbench_accounts');
┌─[ RECORD 1 ]───────┬────────────────────────┐
│ table_len          │ 136642560              │
│ tuple_count        │ 1000000                │
│ tuple_len          │ 121000000              │
│ tuple_percent      │ 88.55                  │
│ dead_tuple_count   │ 16418                  │
│ dead_tuple_len     │ 1986578                │
│ dead_tuple_percent │ 1.45                   │
│ free_space         │ 1674768                │
│ free_percent       │ 1.23                   │
│ bloat              │ 0.11447794889088729017 │
└────────────────────┴────────────────────────┘

pgstattuple is very useful for precisely determining table and index bloat. For specific details, refer to the official documentation: https://www.postgresql.org/docs/current/static/pgstattuple.html.

Additionally, PostgreSQL provides two built-in extensions, pg_freespacemap and pageinspect. The former can be used to examine the free space size in each page, while the latter can precisely show the physical storage content within each data page in relations. If you want to examine the internal state of relations, these two plugins are very practical. Detailed usage can be found in the official documentation:

https://www.postgresql.org/docs/current/static/pgfreespacemap.html

https://www.postgresql.org/docs/current/static/pageinspect.html

However, in most cases, we don’t care too much about the precision of bloat rates. In actual production, the requirements for bloat rates aren’t high: having the first significant digit accurate is generally sufficient. On the other hand, to know precisely the total bytes occupied by live tuples, a full scan of the entire relation is needed, which puts pressure on the online system’s I/O. If you want to monitor bloat rates for all tables, this approach isn’t suitable.

For example, a 200G relation would take approximately 5 minutes to perform precise bloat rate estimation using the pgstattuple plugin. In version 9.5 and later, the pgstattuple plugin also provides the pgstattuple_approx function, trading precision for speed. But even with estimation, it still takes seconds.

For monitoring bloat rates, the most important requirement is fast speed and low impact. Therefore, when we need to monitor many tables across many databases simultaneously, we need to perform fast estimation of bloat rates to avoid impacting business operations.

Estimating Bloat Rate

PostgreSQL maintains many statistical information for each relation. Using statistical information, we can quickly and efficiently estimate bloat rates for all tables in the database. Estimating bloat rates requires using statistical information on tables and columns. Three directly used statistical metrics are:

Average tuple width avgwidth: calculated from column-level statistical data, used to estimate space occupied in compact state
Tuple count: pg_class.reltuples: used to estimate space occupied in compact state
Page count: pg_class.relpages: used to measure actually used space

The calculation formula is also simple:

1 - (reltuples * avgwidth) / (block_size - pageheader) / relpages

Here block_size is page size, default 8182, pageheader is header overhead, default 24 bytes. Page size minus header size gives actual space available for tuple storage. Therefore, (reltuples * avgwidth) gives estimated total tuple size, and dividing by the former gives expected pages needed to compactly store all tuples. Finally, expected page count divided by actual page count gives utilization rate, and 1 minus utilization rate gives bloat rate.

Difficulties

The key here is how to use statistical information to estimate average tuple length. To achieve this, we need to overcome three difficulties:

When tuples contain null values, headers will have null bitmaps
There’s padding between headers and data sections, requiring boundary alignment consideration
Some field types also have alignment requirements

Fortunately, bloat rate itself is an estimation, so being roughly correct is sufficient.

Calculating Average Tuple Length

To understand the estimation process, we first need to understand PostgreSQL’s internal layout of data pages and tuples.

First, let’s look at tuple average length. The tuple layout in PostgreSQL is shown in the diagram below.

Space occupied by a tuple can be divided into three parts:

Fixed-length line pointer (4 bytes, strictly speaking this isn’t part of the tuple, but it corresponds one-to-one with tuples)
Variable-length header
- Fixed-length part 23 bytes
- When tuples contain null values, a null bitmap appears, with each field occupying one bit, so its length is the number of fields divided by 8
- After the null bitmap, padding is needed to MAXALIGN, usually 8
- If the table has the WITH OIDS option enabled, tuples also have a 4-byte OID, but we don’t consider this case here
Data section

Therefore, a tuple’s average length (including corresponding line pointer) can be calculated as:

avg_size_tuple = 4 + avg_size_hdr + avg_size_data

The key is finding average header length and average data section length.

Calculating Average Header Length

The main variables in average header length are null bitmap and padding alignment. To estimate average tuple header length, we need several parameters:

Average header length without null bitmap (with padding): normhdr
Average header length with null bitmap (with padding): nullhdr
Proportion of tuples with null values: nullfrac

The formula for estimating average header length is also very simple:

avg_size_hdr =  nullhdr * nullfrac + normhdr * (1 - nullfrac)

Since headers without null bitmaps are 23 bytes long, aligned to 8-byte boundaries gives 24 bytes, the above formula becomes:

avg_size_hdr =  nullhdr * nullfrac +  24 * (1 - nullfrac)

To calculate the length of a value padded to 8-byte boundaries, use this formula for efficient computation:

padding = lambda x : x + 7 >> 3 << 3

Calculating Average Data Section Length

Average data section length mainly depends on each field’s average width and null rate, plus trailing alignment.

The following SQL can calculate average tuple data section width for all tables using statistical information:

SELECT schemaname, tablename, sum((1 - null_frac) * avg_width)
FROM pg_stats GROUP BY (schemaname, tablename);

For example, this SQL can get average tuple length for table app.apple from the pg_stats system statistics view:

SELECT
  count(*),                        -- number of fields
  ceil(count(*) / 8.0),            -- bytes occupied by null bitmap
  max(null_frac),                  -- maximum null rate
  sum((1 - null_frac) * avg_width) -- average width of data section
FROM pg_stats
where schemaname = 'app' and tablename = 'apple';

-[ RECORD 1 ]-----------
count | 47
ceil  | 6
max   | 1
sum   | 1733.76873471724

Integration

Integrating the logic from the above three sections, we get the following stored procedure that returns bloat rate for a given table:

CREATE OR REPLACE FUNCTION public.pg_table_bloat(relation regclass)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
DECLARE
  _schemaname text;
  tuples      BIGINT := 0;
  pages       INTEGER := 0;
  nullheader  INTEGER:= 0;
  nullfrac    FLOAT := 0;
  datawidth   INTEGER :=0;
  avgtuplelen FLOAT :=24;
BEGIN
  SELECT
    relnamespace :: RegNamespace,
    reltuples,
    relpages
  into _schemaname, tuples, pages
  FROM pg_class
  Where oid = relation;

  SELECT
    23 + ceil(count(*) >> 3),
    max(null_frac),
    ceil(sum((1 - null_frac) * avg_width))
  into nullheader, nullfrac, datawidth
  FROM pg_stats
  where schemaname = _schemaname and tablename = relation :: text;

  SELECT (datawidth + 8 - (CASE WHEN datawidth%8=0 THEN 8 ELSE datawidth%8 END)) -- avg data len
    + (1 - nullfrac) * 24 + nullfrac * (nullheader + 8 - (CASE WHEN nullheader%8=0 THEN 8 ELSE nullheader%8 END))
    INTO avgtuplelen;

  raise notice '% %', nullfrac, datawidth;

  RETURN 1 - (ceil(tuples * avgtuplelen / 8168)) / pages;
END;
$function$

Batch Calculation

For monitoring, we often care about not just one table, but all tables in the database. Therefore, the above bloat rate calculation logic can be rewritten as a batch calculation query and defined as a view for easy use:

DROP VIEW IF EXISTS monitor.pg_bloat_indexes CASCADE;
CREATE OR REPLACE VIEW monitor.pg_bloat_indexes AS
  WITH btree_index_atts AS (
      SELECT
        pg_namespace.nspname,
        indexclass.relname                                                          AS index_name,
        indexclass.reltuples,
        indexclass.relpages,
        pg_index.indrelid,
        pg_index.indexrelid,
        indexclass.relam,
        tableclass.relname                                                          AS tablename,
        (regexp_split_to_table((pg_index.indkey) :: TEXT, ' ' :: TEXT)) :: SMALLINT AS attnum,
        pg_index.indexrelid                                                         AS index_oid
      FROM ((((pg_index
        JOIN pg_class indexclass ON ((pg_index.indexrelid = indexclass.oid)))
        JOIN pg_class tableclass ON ((pg_index.indrelid = tableclass.oid)))
        JOIN pg_namespace ON ((pg_namespace.oid = indexclass.relnamespace)))
        JOIN pg_am ON ((indexclass.relam = pg_am.oid)))
      WHERE ((pg_am.amname = 'btree' :: NAME) AND (indexclass.relpages > 0))
  ), index_item_sizes AS (
      SELECT
        ind_atts.nspname,
        ind_atts.index_name,
        ind_atts.reltuples,
        ind_atts.relpages,
        ind_atts.relam,
        ind_atts.indrelid                                    AS table_oid,
        ind_atts.index_oid,
        (current_setting('block_size' :: TEXT)) :: NUMERIC   AS bs,
        8                                                    AS maxalign,
        24                                                   AS pagehdr,
        CASE
        WHEN (max(COALESCE(pg_stats.null_frac, (0) :: REAL)) = (0) :: FLOAT)
          THEN 2
        ELSE 6
        END                                                  AS index_tuple_hdr,
        sum((((1) :: FLOAT - COALESCE(pg_stats.null_frac, (0) :: REAL)) *
             (COALESCE(pg_stats.avg_width, 1024)) :: FLOAT)) AS nulldatawidth
      FROM ((pg_attribute
        JOIN btree_index_atts ind_atts
          ON (((pg_attribute.attrelid = ind_atts.indexrelid) AND (pg_attribute.attnum = ind_atts.attnum))))
        JOIN pg_stats ON (((pg_stats.schemaname = ind_atts.nspname) AND (((pg_stats.tablename = ind_atts.tablename) AND
                                                                          ((pg_stats.attname) :: TEXT =
                                                                           pg_get_indexdef(pg_attribute.attrelid,
                                                                                           (pg_attribute.attnum) :: INTEGER,
                                                                                           TRUE))) OR
                                                                         ((pg_stats.tablename = ind_atts.index_name) AND
                                                                          (pg_stats.attname = pg_attribute.attname))))))
      WHERE (pg_attribute.attnum > 0)
      GROUP BY ind_atts.nspname, ind_atts.index_name, ind_atts.reltuples, ind_atts.relpages, ind_atts.relam,
        ind_atts.indrelid, ind_atts.index_oid, (current_setting('block_size' :: TEXT)) :: NUMERIC, 8 :: INTEGER
  ), index_aligned_est AS (
      SELECT
        index_item_sizes.maxalign,
        index_item_sizes.bs,
        index_item_sizes.nspname,
        index_item_sizes.index_name,
        index_item_sizes.reltuples,
        index_item_sizes.relpages,
        index_item_sizes.relam,
        index_item_sizes.table_oid,
        index_item_sizes.index_oid,
        COALESCE(ceil((((index_item_sizes.reltuples * ((((((((6 + index_item_sizes.maxalign) -
                                                             CASE
                                                             WHEN ((index_item_sizes.index_tuple_hdr %
                                                                    index_item_sizes.maxalign) = 0)
                                                               THEN index_item_sizes.maxalign
                                                             ELSE (index_item_sizes.index_tuple_hdr %
                                                                   index_item_sizes.maxalign)
                                                             END)) :: FLOAT + index_item_sizes.nulldatawidth)
                                                          + (index_item_sizes.maxalign) :: FLOAT) - (
                                                           CASE
                                                           WHEN (((index_item_sizes.nulldatawidth) :: INTEGER %
                                                                  index_item_sizes.maxalign) = 0)
                                                             THEN index_item_sizes.maxalign
                                                           ELSE ((index_item_sizes.nulldatawidth) :: INTEGER %
                                                                 index_item_sizes.maxalign)
                                                           END) :: FLOAT)) :: NUMERIC) :: FLOAT) /
                        ((index_item_sizes.bs - (index_item_sizes.pagehdr) :: NUMERIC)) :: FLOAT) +
                       (1) :: FLOAT)), (0) :: FLOAT) AS expected
      FROM index_item_sizes
  ), raw_bloat AS (
      SELECT
        current_database()                                                           AS dbname,
        index_aligned_est.nspname,
        pg_class.relname                                                             AS table_name,
        index_aligned_est.index_name,
        (index_aligned_est.bs * ((index_aligned_est.relpages) :: BIGINT) :: NUMERIC) AS totalbytes,
        index_aligned_est.expected,
        CASE
        WHEN ((index_aligned_est.relpages) :: FLOAT <= index_aligned_est.expected)
          THEN (0) :: NUMERIC
        ELSE (index_aligned_est.bs *
              ((((index_aligned_est.relpages) :: FLOAT - index_aligned_est.expected)) :: BIGINT) :: NUMERIC)
        END                                                                          AS wastedbytes,
        CASE
        WHEN ((index_aligned_est.relpages) :: FLOAT <= index_aligned_est.expected)
          THEN (0) :: NUMERIC
        ELSE (((index_aligned_est.bs * ((((index_aligned_est.relpages) :: FLOAT -
                                          index_aligned_est.expected)) :: BIGINT) :: NUMERIC) * (100) :: NUMERIC) /
              (index_aligned_est.bs * ((index_aligned_est.relpages) :: BIGINT) :: NUMERIC))
        END                                                                          AS realbloat,
        pg_relation_size((index_aligned_est.table_oid) :: REGCLASS)                  AS table_bytes,
        stat.idx_scan                                                                AS index_scans
      FROM ((index_aligned_est
        JOIN pg_class ON ((pg_class.oid = index_aligned_est.table_oid)))
        JOIN pg_stat_user_indexes stat ON ((index_aligned_est.index_oid = stat.indexrelid)))
  ), format_bloat AS (
      SELECT
        raw_bloat.dbname                                             AS database_name,
        raw_bloat.nspname                                            AS schema_name,
        raw_bloat.table_name,
        raw_bloat.index_name,
        round(
            raw_bloat.realbloat)                                     AS bloat_pct,
        round((raw_bloat.wastedbytes / (((1024) :: FLOAT ^
                                         (2) :: FLOAT)) :: NUMERIC)) AS bloat_mb,
        round((raw_bloat.totalbytes / (((1024) :: FLOAT ^ (2) :: FLOAT)) :: NUMERIC),
              3)                                                     AS index_mb,
        round(
            ((raw_bloat.table_bytes) :: NUMERIC / (((1024) :: FLOAT ^ (2) :: FLOAT)) :: NUMERIC),
            3)                                                       AS table_mb,
        raw_bloat.index_scans
      FROM raw_bloat
  )
  SELECT
    format_bloat.database_name                    as datname,
    format_bloat.schema_name                      as nspname,
    format_bloat.table_name                       as relname,
    format_bloat.index_name                       as idxname,
    format_bloat.index_scans                      as idx_scans,
    format_bloat.bloat_pct                        as bloat_pct,
    format_bloat.table_mb,
    format_bloat.index_mb - format_bloat.bloat_mb as actual_mb,
    format_bloat.bloat_mb,
    format_bloat.index_mb                         as total_mb
  FROM format_bloat
  ORDER BY format_bloat.bloat_mb DESC;

COMMENT ON VIEW monitor.pg_bloat_indexes IS 'index bloat monitor';

Although it looks long, querying this view to get bloat rates for all tables in the entire database (3TB) takes only 50ms of computation. And it only needs to access statistical data, not the relations themselves, consuming no instance I/O.

Handling Table Bloat

If it’s just a toy database, or the business allows long daily downtime for maintenance, then simply executing VACUUM FULL in the database would suffice. But VACUUM FULL requires exclusive read-write locks on tables. For databases that need to run continuously, we need to use pg_repack to handle table bloat.

Homepage: http://reorg.github.io/pg_repack/

pg_repack is included in PostgreSQL’s official yum repository, so it can be installed directly via yum install pg_repack.

yum install pg_repack10

Using `pg_repack`

Like most PostgreSQL client programs, pg_repack also connects to PostgreSQL servers through similar parameters.

Before using pg_repack, you need to create the pg_repack extension in the database to be reorganized:

CREATE EXTENSION pg_repack

Then you can use it normally. Several typical usage patterns:

# Complete cleanup of entire database, 5 concurrent tasks, 10 second timeout
pg_repack -d <database> -j 5 -T 10

# Clean specific table mytable in mydb, 10 second timeout
pg_repack mydb -t public.mytable -T 10

# Clean specific index myschema.myindex, must use full name with schema
pg_repack mydb -i myschema.myindex

Detailed usage can be found in the official documentation.

`pg_repack` Strategy

Usually, if business has peak and valley cycles, you can choose to perform reorganization during business valleys. pg_repack executes quickly but is resource-intensive. Running during peak periods might affect overall database performance and could cause replication lag.

For example, you can use the bloat rate monitoring views provided in the above two sections to daily select the most severely bloated tables and indexes for automatic reorganization.

#--------------------------------------------------------------#
# Name: repack_tables
# Desc: repack table via fullname
# Arg1: database_name
# Argv: list of table full name
# Deps: psql
#--------------------------------------------------------------#
# repack single table
function repack_tables(){
    local db=$1
    shift

    log_info "repack ${db} tables begin"
    log_info "repack table list: $@"

    for relname in $@
    do
        old_size=$(psql ${db} -Atqc "SELECT pg_size_pretty(pg_relation_size('${relname}'));")
        # kill_queries ${db}
        log_info "repack table ${relname} begin, old size: ${old_size}"
        pg_repack ${db} -T 10 -t ${relname}
        new_size=$(psql ${db} -Atqc "SELECT pg_size_pretty(pg_relation_size('${relname}'));")
        log_info "repack table ${relname} done , new size: ${old_size} -> ${new_size}"
    done

    log_info "repack ${db} tables done"
}

#--------------------------------------------------------------#
# Name: get_bloat_tables
# Desc: find bloat tables in given database match some condition
# Arg1: database_name
# Echo: list of full table name
# Deps: psql, monitor.pg_bloat_tables
#--------------------------------------------------------------#
function get_bloat_tables(){
    echo $(psql ${1} -Atq <<-'EOF'
    WITH bloat_tables AS (
        SELECT
          nspname || '.' || relname as relname,
          actual_mb,
          bloat_pct
        FROM monitor.pg_bloat_tables
        WHERE nspname NOT IN ('dba', 'monitor', 'trash')
        ORDER BY 2 DESC,3 DESC
    )
    -- 64 small + 16 medium + 4 large
    (SELECT relname FROM bloat_tables WHERE actual_mb < 256 AND bloat_pct > 40 ORDER BY bloat_pct DESC LIMIT 64) UNION
    (SELECT relname FROM bloat_tables WHERE actual_mb BETWEEN 256 AND 1024  AND bloat_pct > 30 ORDER BY bloat_pct DESC LIMIT 16) UNION
    (SELECT relname FROM bloat_tables WHERE actual_mb BETWEEN 1024 AND 4096  AND bloat_pct > 20 ORDER BY bloat_pct DESC  LIMIT 4);
EOF
)
}

Here, three rules are set:

From small tables < 256MB with bloat rate > 40%, select TOP64
From medium tables 256MB to 1GB with bloat rate > 40%, select TOP16
From large tables 1GB to 4GB with bloat rate > 20%, select TOP4

Select these tables for automatic reorganization during early morning valleys. Tables over 4GB are handled manually.

But when to perform reorganization still depends on specific business patterns.

`pg_repack` Principles

pg_repack’s principle is quite simple. It creates a copy for the table to be rebuilt. First, it takes a full snapshot, writes all live tuples to the new table, and synchronizes all changes to the original table to the new table through triggers. Finally, it replaces the old table with the new compact copy through renaming. For indexes, this is accomplished through PostgreSQL’s CREATE(DROP) INDEX CONCURRENTLY.

Reorganizing Tables

Create an empty table with the same schema as the original table but without indexes
Create a log table corresponding to the original table to record changes that occur on that table during pg_repack operation
Add a row trigger to the original table to record all INSERT, DELETE, UPDATE operations in the corresponding log table
Copy data from the old table to the new empty table
Create the same indexes on the new table
Apply incremental changes from the log table to the new table
Switch new and old tables through renaming
Drop the old, renamed table

Reorganizing Indexes

Use CREATE INDEX CONCURRENTLY to create a new index on the original table, maintaining the same definition as the old index
Analyze the new index, set the old index as invalid, and swap new and old indexes in the data directory
Delete the old index

`pg_repack` Considerations

Before starting reorganization, it’s best to cancel all ongoing Vacuum tasks
Before reorganizing indexes, it’s best to manually clean up queries that might be using those indexes
If abnormal situations occur (like forced exit midway), garbage might be left behind that needs manual cleanup. This might include:
- Temporary tables and temporary indexes built in the same schema as the original table/index
- Temporary table names: ${schema_name}.table_${table_oid}
- Temporary index names: ${schema_name}.index_${table_oid}}
- Related triggers might remain on the original table and need manual cleanup
When reorganizing particularly large tables, reserve at least the same amount of disk space as the table and its indexes, requiring special care and manual checking
When completing reorganization and performing renaming replacement, massive amounts of WAL will be generated, possibly causing replication delay that cannot be canceled

12 - Monitoring Table Size in PostgreSQL

Table Space Layout

In the broad sense, a Table includes two parts: the main table and TOAST table:

Main table: stores the relation’s own data, i.e., the narrow sense relation, relkind='r'.
TOAST table: corresponds one-to-one with the main table, stores oversized fields, relkind='t'.

Each table consists of main body and indexes - two Relations (for main tables, index relations may not exist):

Main relation: stores tuples.
Index relation: stores index tuples.

Each relation may have four forks:

main: the relation’s main file, numbered 0
fsm: stores information about free space in the main fork, numbered 1
vm: stores information about visibility in the main fork, numbered 2
init: used for unlogged tables and indexes, a rare special fork, numbered 3

Each fork is stored as one or more files on disk: files larger than 1GB are split into multiple segments of maximum 1GB each.

In summary, a table is not as simple as it appears - it consists of several relations:

Main table’s main relation (single)
Main table’s indexes (multiple)
TOAST table’s main relation (single)
TOAST table’s index (single)

Each relation may actually contain 1-3 forks: main (always exists), fsm, vm.

Getting Table’s Associated Relations

Use the following query to list all fork oids:

select
  nsp.nspname,
  rel.relname,
  rel.relnamespace    as nspid,
  rel.oid             as relid,
  rel.reltoastrelid   as toastid,
  toastind.indexrelid as toastindexid,
  ind.indexes
from
  pg_namespace nsp
  join pg_class rel on nsp.oid = rel.relnamespace
  , LATERAL ( select array_agg(indexrelid) as indexes from pg_index where indrelid = rel.oid) ind
  , LATERAL ( select indexrelid from pg_index where indrelid = rel.reltoastrelid) toastind
where nspname not in ('pg_catalog', 'information_schema') and rel.relkind = 'r';

 nspname |  relname   |  nspid  |  relid  | toastid | toastindexid |      indexes
---------+------------+---------+---------+---------+--------------+--------------------
 public  | aoi        | 4310872 | 4320271 | 4320274 |      4320276 | {4325606,4325605}
 public  | poi        | 4310872 | 4332324 | 4332327 |      4332329 | {4368886}

Statistical Functions

PostgreSQL provides a series of functions to determine the space occupied by various parts.

Function	Statistical Scope
`pg_total_relation_size(oid)`	Entire relation, including table, indexes, TOAST, etc.
`pg_indexes_size(oid)`	Space occupied by relation’s index portion
`pg_table_size(oid)`	Space occupied by relation excluding indexes
`pg_relation_size(oid)`	Get size of a relation’s main file part (main fork)
`pg_relation_size(oid, 'main')`	Get relation’s `main` fork size
`pg_relation_size(oid, 'fsm')`	Get relation’s `fsm` fork size
`pg_relation_size(oid, 'vm')`	Get relation’s `vm` fork size
`pg_relation_size(oid, 'init')`	Get relation’s `init` fork size

Although physically a table consists of so many files, logically we usually only care about the size of two things: table and indexes. Therefore, the main functions used here are pg_indexes_size and pg_table_size, whose sum equals pg_total_relation_size for regular tables.

The table size portion can typically be calculated as:

 pg_table_size(relid)
     = pg_relation_size(relid, 'main')
     + pg_relation_size(relid, 'fsm')
     + pg_relation_size(relid, 'vm')
     + pg_total_relation_size(reltoastrelid)

 pg_indexes_size(relid)
     = (select sum(pg_total_relation_size(indexrelid)) where indrelid = relid)

Note that TOAST tables also have their own indexes, but there is only one, so using pg_total_relation_size(reltoastrelid) can calculate the overall size of the TOAST table.

SELECT
  oid,
  relname,
  relnamespace::RegNamespace::Text               as nspname,
  relkind                                        as relkind,
  reltuples                                      as tuples,
  relpages                                       as pages,
  pg_total_relation_size(oid)                    as size
  FROM pg_class
WHERE oid = ANY(array(SELECT 16418 as id -- main
UNION ALL SELECT indexrelid FROM pg_index WHERE indrelid = 16418 -- index
UNION ALL SELECT reltoastrelid FROM pg_class WHERE oid = 16418)); -- toast

This can be wrapped as a UDTF: pg_table_size_detail, for convenient use:

CREATE OR REPLACE FUNCTION pg_table_size_detail(relation RegClass)
  RETURNS TABLE(
    id      oid,
    pid     oid,
    relname name,
    nspname text,
    relkind "char",
    tuples  bigint,
    pages   integer,
    size    bigint
  )
AS $$
BEGIN
  RETURN QUERY
  SELECT
    rel.oid,
    relation::oid,
    rel.relname,
    rel.relnamespace :: RegNamespace :: Text as nspname,
    rel.relkind                              as relkind,
    rel.reltuples::bigint                    as tuples,
    rel.relpages                             as pages,
    pg_total_relation_size(oid)              as size
  FROM pg_class rel
  WHERE oid = ANY (array(
      SELECT relation as id -- main
      UNION ALL SELECT indexrelid FROM pg_index WHERE indrelid = relation -- index
      UNION ALL SELECT reltoastrelid FROM pg_class WHERE oid = relation)); -- toast
END;
$$
LANGUAGE PlPgSQL;

SELECT * FROM pg_table_size_detail(16418);

Sample return result:

geo=# select * from  pg_table_size_detail(4325625);
   id    |   pid   |        relname        | nspname  | relkind |  tuples  |  pages  |    size
---------+---------+-----------------------+----------+---------+----------+---------+-------------
 4325628 | 4325625 | pg_toast_4325625      | pg_toast | t       |   154336 |   23012 |   192077824
 4419940 | 4325625 | idx_poi_adcode_btree  | gaode    | i       | 62685464 |  172058 |  1409499136
 4419941 | 4325625 | idx_poi_cate_id_btree | gaode    | i       | 62685464 |  172318 |  1411629056
 4419942 | 4325625 | idx_poi_lat_btree     | gaode    | i       | 62685464 |  172058 |  1409499136
 4419943 | 4325625 | idx_poi_lon_btree     | gaode    | i       | 62685464 |  172058 |  1409499136
 4419944 | 4325625 | idx_poi_name_btree    | gaode    | i       | 62685464 |  335624 |  2749431808
 4325625 | 4325625 | gaode_poi             | gaode    | r       | 62685464 | 2441923 | 33714962432
 4420005 | 4325625 | idx_poi_position_gist | gaode    | i       | 62685464 |  453374 |  3714039808
 4420044 | 4325625 | poi_position_geohash6 | gaode    | i       | 62685464 |  172058 |  1409499136

Example: Relation Size Details Summary

select
  nsp.nspname,
  rel.relname,
  rel.relnamespace    as nspid,
  rel.oid             as relid,
  rel.reltoastrelid   as toastid,
  toastind.indexrelid as toastindexid,
  pg_total_relation_size(rel.oid)  as size,
  pg_relation_size(rel.oid) + pg_relation_size(rel.oid,'fsm')
  + pg_relation_size(rel.oid,'vm') as relsize,
  pg_indexes_size(rel.oid)         as indexsize,
  pg_total_relation_size(reltoastrelid) as toastsize,
  ind.indexids,
  ind.indexnames,
  ind.indexsizes
from pg_namespace nsp
  join pg_class rel on nsp.oid = rel.relnamespace
  ,LATERAL ( select indexrelid from pg_index where indrelid = rel.reltoastrelid) toastind
  , LATERAL ( select  array_agg(indexrelid) as indexids,
                      array_agg(indexrelid::RegClass) as indexnames,
                      array_agg(pg_total_relation_size(indexrelid)) as indexsizes
              from pg_index where indrelid = rel.oid) ind
where nspname not in ('pg_catalog', 'information_schema') and rel.relkind = 'r';

13 - PostgreSQL Server Log Regular Configuration

It’s recommended to configure PostgreSQL’s log format as CSV for easy analysis, and it can be directly imported into PostgreSQL data tables.

log_destination ='csvlog'
logging_collector =on
log_directory ='log'
log_filename ='postgresql-%a.log'
log_min_duration_statement =1000
log_checkpoints =on
log_lock_waits =on
log_statement ='ddl'
log_replication_commands =on
log_timezone ='UTC'
log_autovacuum_min_duration =1000

track_io_timing =on
track_functions =all
track_activity_query_size =16384

Log Collection

If you need to collect logs from external sources, consider using filebeat.

filebeat.prospectors:

## input
- type: log
enabled: true
paths:
- /var/lib/postgresql/data/pg_log/postgresql-*.csv
document_type: db-trace
tail_files: true
multiline.pattern: '^20\d\d-\d\d-\d\d'
multiline.negate: true
multiline.match: after
multiline.max_lines: 20
max_cpus: 1

## modules
filebeat.config.modules:
path: ${path.config}/modules.d/*.yml
reload.enabled: false

## queue
queue.mem:
events: 1024
flush.min_events: 0
flush.timeout: 1s

## output
output.kafka:
hosts: ["10.10.10.10:9092","x.x.x.x:9092"]
topics:
- topic: 'log.db'

CSV Log Format

Very interesting idea - converting CSV logs into PostgreSQL tables is very convenient for analysis.

The original CSV log format definition is as follows:

Log table structure definition
create table postgresql_log
(
  log_time               timestamp,
  user_name              text,
  database_name          text,
  process_id             integer,
  connection_from        text,
  session_id             text   not null,
  session_line_num       bigint not null,
  command_tag            text,
  session_start_time     timestamp with time zone,
  virtual_transaction_id text,
  transaction_id         bigint,
  error_severity         text,
  sql_state_code         text,
  message                text,
  detail                 text,
  hint                   text,
  internal_query         text,
  internal_query_pos     integer,
  context                text,
  query                  text,
  query_pos              integer,
  location               text,
  application_name       text,
  PRIMARY KEY (session_id, session_line_num)
);

Importing Logs

Logs are well-structured CSV (CSV allows multi-line records), you can directly use the COPY command to import them.

COPY postgresql_log FROM '/var/lib/pgsql/data/pg_log/postgresql.log' CSV DELIMITER ',';

Mapping Logs

Of course, besides copying logs directly to data tables for analysis, there’s another method that allows PostgreSQL to directly map its local CSVLOG as a foreign table for SQL-based direct access.

CREATE SCHEMA IF NOT EXISTS monitor;

-- search path for su
ALTER ROLE postgres SET search_path = public, monitor;
SET search_path = public, monitor;

-- extension
CREATE EXTENSION IF NOT EXISTS file_fdw WITH SCHEMA monitor;

-- log parent table: empty
CREATE TABLE monitor.pg_log
(
  log_time               timestamp(3) with time zone,
  user_name              text,
  database_name          text,
  process_id             integer,
  connection_from        text,
  session_id             text,
  session_line_num       bigint,
  command_tag            text,
  session_start_time     timestamp with time zone,
  virtual_transaction_id text,
  transaction_id         bigint,
  error_severity         text,
  sql_state_code         text,
  message                text,
  detail                 text,
  hint                   text,
  internal_query         text,
  internal_query_pos     integer,
  context                text,
  query                  text,
  query_pos              integer,
  location               text,
  application_name       text,
  PRIMARY KEY (session_id, session_line_num)
);
COMMENT ON TABLE monitor.pg_log IS 'PostgreSQL csv log schema';
-- local file server
CREATE SERVER IF NOT EXISTS pg_log FOREIGN DATA WRAPPER file_fdw;
-- Change filename to actual path
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_mon() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Mon.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_tue() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Tue.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_wed() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Wed.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_thu() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Thu.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_fri() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Fri.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_sat() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Sat.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_sun() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Sun.csv', format 'csv');

Processing Logs

You can use the following stored procedures to further extract statement execution times from log messages:

CREATE OR REPLACE FUNCTION extract_duration(statement TEXT)
  RETURNS FLOAT AS $$
DECLARE
  found_duration BOOLEAN;
BEGIN
  SELECT position('duration' in statement) > 0
  into found_duration;
  IF found_duration
  THEN
    RETURN (SELECT regexp_matches [1] :: FLOAT
            FROM regexp_matches(statement, 'duration: (.*) ms')
            LIMIT 1);
  ELSE
    RETURN NULL;
  END IF;
END
$$
LANGUAGE plpgsql
IMMUTABLE;

CREATE OR REPLACE FUNCTION extract_statement(statement TEXT)
  RETURNS TEXT AS $$
DECLARE
  found_statement BOOLEAN;
BEGIN
  SELECT position('statement' in statement) > 0
  into found_statement;
  IF found_statement
  THEN
    RETURN (SELECT regexp_matches [1]
            FROM regexp_matches(statement, 'statement: (.*)')
            LIMIT 1);
  ELSE
    RETURN NULL;
  END IF;
END
$$
LANGUAGE plpgsql
IMMUTABLE;

CREATE OR REPLACE FUNCTION extract_ip(app_name TEXT)
  RETURNS TEXT AS $$
DECLARE
  ip TEXT;
BEGIN
  SELECT regexp_matches [1]
  into ip
  FROM regexp_matches(app_name, '(\d+\.\d+\.\d+\.\d+)')
  LIMIT 1;
  RETURN ip;
END
$$
LANGUAGE plpgsql
IMMUTABLE;

Article

1 - Victoria: The Observability Stack That Slaps the Industry

First impressions

Why Victoria?

How strong is Victoria?

Architecture

Getting started

Final thoughts

2 - PGCon.Dev 2024, The conf that shutdown PG for a week

Day Zero: Extension Summit

Day One: Brilliant Talks and Bar Social

Pushing the Boundaries of PG Extensions

Anarchy in DBMS

How PostgreSQL is Misused and Abused

PostgreSQL and the AI Ecosystem

PB-Level PostgreSQL Deployments

Highlight: When Hardware and Database Collide

WetBar Social

Day 2: Debate, Lunch, and Lighting Talks

Multi-Threading Postgres

Hallway Track

Multinational Community Lunch

Making PG Hacking More Inclusive

Lightning Talks

Day 3: Unconference

Issue: Support for LoongArch Architecture

Issue: Server-side Chinese Character Encoding

Closing

3 - PostgreSQL Macro Query Optimization with pg_stat_statements

Extension: PGSS

Raw Data

Time Derivatives

Call Count Derivatives

Percentage Metrics

Summary

References

4 - 如何用Pigsty监控现有PostgreSQL (RDS/PolarDB/自建)？

快速上手

Why Pigsty

使用 Terraform 申请 ECS 服务器

在ECS上完整安装单机版 Pigsty

配置 PolarDB / RDS PG 监控

将现有PG数据库接入监控

5 - A Methodology for Diagnosing PostgreSQL Slow Queries

Traditional tools

Why monitoring helps

Workflow (simulated incident)

1. Detection

2. Identification

3. Hypothesis

4. Fix

Summary

6 - Pigsty正式发布

Pigsty是什么？

Pigsty是监控系统

Pigsty是供给方案

Pigsty是开源软件

Pigsty可以开箱即用

Pigsty的相关站点\

7 - Golden Monitoring Metrics: Errors, Latency, Throughput, Saturation

Preface

Database Metrics

Core Metrics

Why These?

Error Metrics

Traffic Metrics

Latency Metrics

Saturation Metrics

8 - PostgreSQL监控系统Pigsty概述

整体架构

一言以蔽之：用Exporter取指标数据，通过Consul服务发现赋予身份标签与组织结构，存进Prometheus中进行预处理计算，最后使用Grafana展示

层次组织

功能简介

首页导航概览

DB监控：指标介绍

指标丰富程度

指标内容

DB监控：PG实例

实例概览

节点概览

Using `pg_repack`

`pg_repack` Strategy

`pg_repack` Principles

`pg_repack` Considerations