Skip to content

[POSTGRESQL] PostgreSQL Production Monitoring Queries

Jump to bottom
fourslickz edited this page Sep 23, 2026 · 1 revision

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.

Clone this wiki locally