blob: bd6e4ba08bbe5cec14cec2708a7e17176cf775d4 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
### Understand the alert
This alert monitors index bloat in a PostgreSQL database table. If you receive this alert, it indicates that the index is bloated and is taking up more disk space than necessary, which can lead to performance issues.
### What does index bloat mean?
In PostgreSQL, when a row is updated or deleted, the old row data remains in the index while the new data is added. Over time, this causes the index to grow in size (bloat), leading to increased disk usage and degraded query performance. This alert measures the bloat size percentage for each index in the specified database and table.
### Troubleshoot the alert
1. Identify the bloated index in your PostgreSQL database, as mentioned in the alert's info field (e.g. `db [database] table [table] index [index]`).
2. Rebuild the bloated index:
Use the `REINDEX` command to rebuild the bloated index. This will free up the space occupied by the old row data and help optimize query performance.
```
REINDEX INDEX [index_name];
```
**Note:** `REINDEX` might lock the table for the time it takes to rebuild the index, so plan to run this command during maintenance periods or during low database usage periods.
3. Monitor the index bloat size after rebuilding:
After rebuilding the index, continue monitoring the index bloat size and performance to ensure the issue has been resolved.
You can use tools like [pg_stat_statements](https://www.postgresql.org/docs/current/pgstatstatements.html) (a built-in PostgreSQL extension) and pg_stat_indexes (user-defined database views that collect index-related statistics) to keep an eye on your database's performance and catch any bloat issues before they negatively impact your PostgreSQL setup.
### Useful resources
1. [PostgreSQL documentation: REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html)
2. [PostgreSQL documentation: pg_stat_statements](https://www.postgresql.org/docs/current/pgstatstatements.html)
3. [PostgreSQL documentation: Routine Vacuuming](https://www.postgresql.org/docs/current/routine-vacuuming.html)
|
