From c21c3b0befeb46a51b6bf3758ffa30813bea0ff0 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 9 Mar 2024 14:19:22 +0100 Subject: Adding upstream version 1.44.3. Signed-off-by: Daniel Baumann --- .../postgres/postgres_table_bloat_size_perc.md | 58 ++++++++++++++++++++++ 1 file changed, 58 insertions(+) create mode 100644 health/guides/postgres/postgres_table_bloat_size_perc.md (limited to 'health/guides/postgres/postgres_table_bloat_size_perc.md') diff --git a/health/guides/postgres/postgres_table_bloat_size_perc.md b/health/guides/postgres/postgres_table_bloat_size_perc.md new file mode 100644 index 000000000..0edc21bb1 --- /dev/null +++ b/health/guides/postgres/postgres_table_bloat_size_perc.md @@ -0,0 +1,58 @@ +### Understand the alert + +The `postgres_table_bloat_size_perc` alert measures the bloat size percentage in a PostgreSQL database table. If you receive this alert, it means that the bloat size in a particular table in your PostgreSQL database has crossed the warning or critical threshold. + +### What is bloat size? + +In PostgreSQL, bloat size refers to the wasted storage space caused by dead rows and unused space that accumulates in database tables over time. It is a result of frequent database operations (inserts, updates, and deletes), impacting database performance and storage footprint. + +### Troubleshoot the alert + +- Investigate the bloat size and impacted table + +To get a detailed report on bloated tables in your PostgreSQL database, use the [`pgstattuple`](https://www.postgresql.org/docs/current/pgstattuple.html) extension. First, install the extension if it isn't already installed: + + ``` + CREATE EXTENSION pgstattuple; + ``` + +Then, run the following query to find the bloated tables: + + ```sql + SELECT + schemaname, tablename, + pg_size_pretty(bloat_size) AS bloat_size, + round(bloat_ratio::numeric, 2) AS bloat_ratio + FROM ( + SELECT + schemaname, tablename, + bloat_size, table_size, (bloat_size / table_size) * 100 as bloat_ratio + FROM pgstattuple.schema_bloat + ) sub_query + WHERE bloat_ratio > 10 + ORDER BY bloat_ratio DESC; + ``` + +- Reclaim storage space + +Reducing the bloat size in PostgreSQL tables involves reclaiming wasted storage space. Here are two approaches: + + 1. **VACUUM**: The `VACUUM` command helps clean up dead rows and compact the space used by the table. Use the following command to clean up the impacted table: + + ``` + VACUUM VERBOSE ANALYZE .; + ``` + + 2. **REINDEX**: If the issue persists after using `VACUUM`, consider REINDEXing the table. This command rebuilds the table's indexes, which can improve query performance and reduce bloat. It can be more intrusive than `VACUUM`, be sure you understand its implications before running: + + ``` + REINDEX TABLE .; + ``` + +- Monitor the bloat size + +Continue monitoring the bloat size in your PostgreSQL tables by regularly checking the `postgres_table_bloat_size_perc` alert on Netdata. + +### Useful resources + +1. [How to monitor and fix Database bloats in PostgreSQL?](https://blog.netdata.cloud/postgresql-database-bloat/) -- cgit v1.2.3