PostgreSQL System Metrics

Imagine your PostgreSQL server is a restaurant, and we're the inspectors. For the restaurant to run smoothly, we need to keep track of how many ingredients (CPU, memory, disk) are being used, how often they run out, and who's consuming them all. If we miss this, our "restaurant" might either take on too many orders and crash, or just collapse in the middle of the day. That's why understanding system metrics is absolutely crucial.

Key metrics to monitor in PostgreSQL:

1. CPU: shows how much processor time is spent running queries.
2. Memory (RAM): shows how PostgreSQL uses RAM, including query caching.
3. Disk space: probably the most obvious one — you can't store more data than your disk allows.

Our goal is to learn how to check and interpret PostgreSQL system metrics to avoid performance issues and running out of resources.

Monitoring CPU Usage

The CPU is the heart of your server. PostgreSQL can eat up CPU both for running heavy queries and for background tasks like auto-analyze and autovacuum. If your database is acting like a bottomless eater, greedily "devouring" CPU, it's time to step in.

1. Using system tools.
   First, figure out how much CPU time PostgreSQL is using at the system level. On Linux, you can use top or htop.

Find the PostgreSQL process (it usually has your database name in it). For example, postgres: postgres [your_query].

Check out the %CPU column. If it's always maxed out, that's a red flag.

2. Analyzing CPU load from inside PostgreSQL.

PostgreSQL has built-in views for monitoring load. The most useful one here is pg_stat_activity, which shows active queries.

Sample query:

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

What's important here?

• state = 'active' will only show queries that are currently running.
• duration shows how long the queries have been loading the CPU.

Pro tip:

If you see a long-running query that shouldn't take that much time, double-check which indexes are being used. You can also kill the problematic process with pg_terminate_backend.

Sample termination:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active' AND duration > interval '10 minutes';

Monitoring Memory Usage

Memory is the second most important resource for PostgreSQL, especially when it comes to caching data. PostgreSQL actively uses RAM (via work_mem and shared_buffers) to speed things up.

1. Main PostgreSQL memory parameters:

• shared_buffers: This is the main chunk of memory allocated for PostgreSQL. It's usually 25-40% of your server's total RAM.
• work_mem: Memory for sorting and hashing operations in a single query. The bigger the value, the more temp operations can be done in RAM (instead of on disk).

2. Checking current memory settings.

To see what memory settings are active for PostgreSQL, run:

SHOW shared_buffers;
SHOW work_mem;

Sample output:

1GB
4MB

This means the server has 1 GB allocated for shared_buffers and 4 MB for each sort/hash operation.

3. Monitoring memory with pg_stat_activity

You can check how much memory is being used by current connections. Here's a handy query:

SELECT pid, usename, state, backend_start, pg_size_pretty(pg_backend_memory_contexts_size(pid)) AS memory_used
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY memory_used DESC;

This query shows how much RAM is being used by each active connection.

Tip: if one of the connections is eating up too much memory, check if the query has sorting or aggregation operations that could be optimized.

Monitoring Disk Space

Disk is the final resource reservoir for PostgreSQL. Even if you have enough memory and CPU, PostgreSQL needs disk space for storing data, transaction logs (WAL), and temp files.

1. Checking database size.

Let's start with the basics: figuring out your database size.

SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;

What does this query do?

It shows the total size of the current database in a nice format (MB, GB).

2. Checking table and index sizes.

To find the "heavyweights" in your database, use:

SELECT relname AS table_name, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Sample output:

3. Monitoring WAL (transaction logs).

If you have an active database, transaction logs can grow fast. Check their size like this:

SELECT pg_size_pretty(pg_xlog_location_diff(pg_current_wal_lsn(), '0/0')) AS wal_size;

Takeaways

Now you've got the tools and know-how to keep an eye on PostgreSQL system metrics:

1. Use htop or pg_stat_activity to monitor CPU load.
2. Tweak shared_buffers and work_mem for optimal memory usage.
3. Regularly check your database, table, and index sizes to avoid running out of disk space.

These skills will help you dodge unexpected crashes and keep your PostgreSQL server in top shape. When thinking about your server, remember: well-organized resources will make your product the most popular among your clients (users).