PostgreSQL Production Monitoring Queries

Kumpulan query untuk monitoring PostgreSQL 15 production.

Server Reference

Parameter	Value
PostgreSQL	15
RAM	16 GB
Current max_connections	100
Target max_connections	200
Swap	0
Config	/etc/postgresql/15/main/postgresql.conf

---

1. Connection Monitoring

1.1 Total Connections by State

SELECT
    state,
    count(*) AS connections
FROM pg_stat_activity
GROUP BY state
ORDER BY connections DESC;

Status yang perlu diperhatikan:

• active — sedang menjalankan query
• idle — connection tersedia tetapi sedang tidak menjalankan query
• idle in transaction — transaction terbuka tetapi tidak sedang menjalankan query
• NULL — biasanya proses background PostgreSQL

---

2. Connection Usage

2.1 Total Connection vs Max Connection

SELECT
    current_setting('max_connections')::int AS max_connections,
    count(*) AS current_connections,
    round(
        100.0 * count(*) /
        current_setting('max_connections')::int,
        2
    ) AS usage_percent
FROM pg_stat_activity;

Recommended Threshold

Usage	Status
< 60%	Normal
60–80%	Monitor
80–90%	Warning
90–95%	Critical
> 95%	Emergency

Dengan max_connections = 200:

• 80% = 160 connections
• 90% = 180 connections
• 95% = 190 connections

---

3. Connections by User

SELECT
    usename,
    count(*) AS connections
FROM pg_stat_activity
GROUP BY usename
ORDER BY connections DESC;

Berguna untuk mengetahui PostgreSQL user mana yang paling banyak menggunakan connection.

---

4. Connections by Database

SELECT
    datname,
    count(*) AS connections
FROM pg_stat_activity
GROUP BY datname
ORDER BY connections DESC;

---

5. Connections by Application

SELECT
    application_name,
    count(*) AS connections
FROM pg_stat_activity
GROUP BY application_name
ORDER BY connections DESC;

---

6. Detailed Connection Distribution

SELECT
    usename,
    application_name,
    client_addr,
    state,
    count(*) AS connections
FROM pg_stat_activity
GROUP BY
    usename,
    application_name,
    client_addr,
    state
ORDER BY connections DESC;

Ini merupakan query utama untuk investigasi TooManyConnections.

---

7. Active Connections

SELECT
    count(*) AS active_connections
FROM pg_stat_activity
WHERE state = 'active';

---

8. Active Queries

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    now() - query_start AS duration,
    state,
    wait_event_type,
    wait_event,
    LEFT(query, 300) AS query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

---

9. Long Running Queries

Menampilkan query yang berjalan lebih dari 5 detik.

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    now() - query_start AS duration,
    wait_event_type,
    wait_event,
    LEFT(query, 500) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 seconds'
ORDER BY query_start;

Recommended thresholds:

• 5 seconds → investigate

• 30 seconds → warning

• 60 seconds → critical

---

10. Idle in Transaction

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    now() - xact_start AS transaction_age,
    now() - state_change AS idle_duration,
    LEFT(query, 300) AS query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Jika terdapat idle in transaction selama beberapa menit atau lebih, lakukan investigasi.

Potensi dampak:

• lock
• bloat
• VACUUM tertahan
• penggunaan resource meningkat

---

11. Long Idle Connections

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    backend_start,
    state_change,
    now() - state_change AS idle_for
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY state_change ASC;

---

12. Connection Age

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    now() - backend_start AS connection_age
FROM pg_stat_activity
ORDER BY backend_start;

---

13. Blocking / Lock Monitoring

SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    now() - blocked.query_start AS blocked_duration,
    LEFT(blocked.query, 200) AS blocked_query,
    LEFT(blocking.query, 200) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
ORDER BY blocked.query_start;

Jika hasilnya 0 rows, tidak ada blocking pada saat query dijalankan.

---

14. Database Size

SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

---

15. Largest Tables

Jalankan pada database yang ingin diperiksa.

SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

• total_size — table + index
• table_size — ukuran data table
• index_size — ukuran index

---

16. Cache Hit Ratio

SELECT
    sum(blks_hit) AS cache_hits,
    sum(blks_read) AS disk_reads,
    round(
        100.0 * sum(blks_hit) /
        NULLIF(sum(blks_hit) + sum(blks_read), 0),
        2
    ) AS cache_hit_ratio
FROM pg_stat_database;

Untuk workload OLTP, cache hit ratio yang tinggi umumnya diharapkan.

---

17. Transaction Statistics

SELECT
    datname,
    xact_commit,
    xact_rollback,
    tup_inserted,
    tup_updated,
    tup_deleted,
    tup_returned,
    tup_fetched
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY xact_commit DESC;

---

18. Deadlocks

SELECT
    datname,
    deadlocks,
    conflicts,
    temp_files,
    temp_bytes
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY deadlocks DESC;

Perhatikan terutama jika nilai deadlocks terus bertambah.

---

19. PostgreSQL Configuration

19.1 Max Connections

SHOW max_connections;

19.2 Important Memory Settings

SELECT
    name,
    setting,
    unit
FROM pg_settings
WHERE name IN (
    'max_connections',
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem'
);

---

20. PostgreSQL Server Configuration

SHOW config_file;

Contoh:

/etc/postgresql/15/main/postgresql.conf

---

21. Quick Production Health Check

Connection

SELECT
    current_setting('max_connections')::int AS max_connections,
    count(*) AS current_connections,
    round(
        100.0 * count(*) /
        current_setting('max_connections')::int,
        2
    ) AS usage_percent
FROM pg_stat_activity;

Connection Distribution

SELECT
    state,
    count(*) AS connections
FROM pg_stat_activity
GROUP BY state
ORDER BY connections DESC;

Connection by User

SELECT
    usename,
    count(*) AS connections
FROM pg_stat_activity
GROUP BY usename
ORDER BY connections DESC;

Active Query

SELECT
    pid,
    usename,
    application_name,
    now() - query_start AS duration,
    wait_event_type,
    wait_event,
    LEFT(query, 300) AS query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

Blocking

SELECT
    blocked.pid AS blocked_pid,
    blocking.pid AS blocking_pid,
    now() - blocked.query_start AS blocked_duration,
    LEFT(blocked.query, 200) AS blocked_query,
    LEFT(blocking.query, 200) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
ORDER BY blocked.query_start;

Idle in Transaction

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    now() - xact_start AS transaction_age,
    LEFT(query, 300) AS query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

---

22. Recommended Monitoring for This Server

Server reference:

RAM             : 16 GB
PostgreSQL      : 15
max_connections : 200
Swap            : 0

Priority	Metric	Warning
High	Connection usage	> 80%
High	Idle in transaction	> 1–5 min
High	Blocking query	Any
Medium	Long query	> 5 sec
Medium	Deadlock	Increasing
Medium	Cache hit ratio	Decreasing
Medium	Database size	Rapid growth
Medium	Table/index size	Rapid growth

---

23. Connection Budget

Dengan:

max_connections = 200

gunakan prinsip berikut:

Connections	Status
0–120	Normal
120–160	Monitor
160–180	Warning
180–190	Critical
190–200	Emergency

Jangan menargetkan penggunaan hingga 200.

Sediakan connection reserve untuk:

• PostgreSQL administration
• monitoring
• maintenance
• unexpected traffic spike
• application reconnect
• troubleshooting

---

24. Useful PostgreSQL Commands

PostgreSQL Status

sudo systemctl status postgresql --no-pager

PostgreSQL Version

sudo -u postgres psql -c "SELECT version();"

PostgreSQL Config

sudo -u postgres psql -c "SHOW config_file;"

Max Connections

sudo -u postgres psql -c "SHOW max_connections;"

Current Connections

sudo -u postgres psql -c \
"SELECT count(*) AS connections FROM pg_stat_activity;"

Memory

free -m

CPU

nproc

Disk

df -h

---

25. Emergency Connection Check

Jika aplikasi mendapatkan:

P2037
TooManyConnections
remaining connection slots are reserved

jalankan:

SELECT
    current_setting('max_connections')::int AS max_connections,
    count(*) AS current_connections,
    round(
        100.0 * count(*) /
        current_setting('max_connections')::int,
        2
    ) AS usage_percent
FROM pg_stat_activity;

Kemudian:

SELECT
    usename,
    application_name,
    client_addr,
    state,
    count(*) AS connections
FROM pg_stat_activity
GROUP BY
    usename,
    application_name,
    client_addr,
    state
ORDER BY connections DESC;

Kemudian:

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    now() - state_change AS idle_for,
    LEFT(query, 200) AS query
FROM pg_stat_activity
ORDER BY state_change;

Jangan langsung melakukan pg_terminate_backend() terhadap semua connection. Identifikasi service terlebih dahulu.