Monitoring in PostgreSQL
PostgreSQL provides advanced database activity tracking tools and features to maintain optimum performance, guarantee availability, and understand system behaviour. DBeaver, a universal database management tool, improves these built-in capabilities and makes monitoring easier and more attractive with its user-friendly graphical interfaces and specialist functions.
Runtime Statistics and Statistics Monitoring
Fundamental to PostgreSQL’s monitoring capabilities is its cumulative statistics system. Critical data regarding server operation is automatically gathered and reported by this system. This includes full records of disc block and row-by-row accesses to tables and indexes, the total number of rows in each table, and metrics pertaining to ANALYSE and VACUUM operations. Calls to user-defined functions and the total amount of time spent on each are also tracked by the system, if enabled.
The following important postgresql.conf configuration options give administrators control over the extent of statistics collection:
Track_activities: Makes it possible to keep track of the ongoing command for every session, including its unique ID and the moment it started. Superusers, roles with pg_read_all_stats rights, and the user of the session are usually the only ones who can see this information.
Track_counts: The autovacuum daemon needs track_counts to function properly, thus it initiates the gathering of general database activity information.
Track_functions: Regulates how many calls and execution times are tracked for user-defined functions. All (including SQL and C language functions), pl (just procedural language functions), and none (disabled) are the available options.
Track_io_timing: Makes it possible to measure the I/O call times for fsync, block read, write, and extend operations. In a similar vein, track_wal_io_timing just keeps an eye on the write and fsync times for WAL (Write-Ahead Log). These settings can be changed for specific sessions using the SET command or set generally in postgresql.conf.
Data and system status are displayed in several PostgreSQL views:
Pg_stat_activity: Individual records per server process include state (‘active’, ‘idle’, ‘idle in transaction’), PID, database, user, and query. This view shows the query or session start time and wait events like Lock or LWLock. People can manage these sessions with DBeaver.
Pg_stat_database: In addition to committed and rolled-back transactions, pg_stat_database counts disk block read and buffer hits, added, updated, and removed rows.
Pg_stat_all_tables: Live and dead rows, row modifications (inserts, updates, and deletes), sequential scans, and index scans are counted in pg_stat_all_tables. Finding tables that need maintenance or index optimisation can be facilitated by this.
Pg_stat_io: Granular I/O statistics for various backend types, contexts, and target objects are displayed by pg_stat_io. This information breaks down read, write, and extend operations as well as buffer hits and evictions. Additionally, it reports the duration of various I/O operations and fsync calls (if track_io_timing is set).
Pg_stat_bgwriter: Global statistics about the background writer process’s activities are displayed by pg_stat_bgwriter. These statistics include checkpoint counts, the total number of buffers allocated, and the number of buffers written by the background writer and other backends.
Pg_stat_wal: Offers stats on WAL activity, including the number of disc writes, full page images, WAL records, and total bytes sent. If you enable track_wal_io_timing, the amount of time spent in WAL writes and syncs is reported.
ANALYSE is an essential command for statistics monitoring. In order for the query planner to produce effective execution plans, it gathers statistics regarding table accesses and the quantity of rows in tables and columns. A planner can avoid bad execution decisions based on outdated data distributions by executing ANALYSE (or VACUUM ANALYSE) periodically. Autovacuum daemon runs ANALYSE if table content changes enough.
Numerous metadata types from multiple databases can be seen and managed with DBeaver’s comprehensive support. The Database Navigator contributes to a thorough monitoring environment by serving as a central location for viewing these metadata elements.
Session Management and Connection States
For management and the detection of possible problems such as connection leaks or lengthy transactions, database sessions must be monitored and managed. Several background worker processes (such as background writer and WAL writer) are managed by a postmaster process in PostgreSQL, which also forks backend server processes for every client connection.
Key tools for session monitoring and management:
Pg_stat_activity The pg_stat_activity view is essential for real-time monitoring of active and idle sessions’ queries and statuses. Long-running queries that harm performance and inactive connections that store locks or utilise memory can be found by administrators. Use the psql command \watch to monitor query output and connection traffic using pg_stat_activity at predetermined intervals.
Pg_locks: To help administrators resolve deadlocks, the pg_locks system table tracks database client locks.
Terminating Sessions: Administrators can kill backend processes with PostgreSQL pg_cancel_backend(pid). This can fix unresponsive sessions.
Max_connections: This critical server configuration setting sets the maximum concurrent client connections. By exceeding this limit, new connections may be prevented, so it must be monitored and updated as needed, which requires server restart.
DBeaver’s integration for session management:
- Using unique icons (such as connected and non-active), the Database Navigator graphically displays the status of database connections.
- We support “Keep-Alive Interval” and “Close idle connections after” since inactive database connections consume.
- Schedule shell programs before or after connect/disconnect to automate session setup and cleanup.
DBeaver: Dashboards, Creating Charts, and Transaction Log
With its robust visualisation and logging capabilities, DBeaver greatly improves database monitoring.
Dashboards: Two kinds of dashboards are available in DBeaver to display database metrics and performance:
Connection Dashboards: Database connections are linked to connection dashboards, which offer real-time tracking of performance, disc space, and connection counts for that particular database. Only the current session is affected by changes made to a connection dashboard.
Project Dashboards: A more permanent and shared monitoring configuration is provided via project dashboards, which are stored inside the ongoing DBeaver project and may include web-based dashboards. Active SQL queries and dynamically created charts are included in the aggregated real-time presentations offered by both kinds of dashboards. For well-known databases, pre-made chart sets that display key performance indicators (KPIs) such as CPU use, transactions per second, and server sessions are available. The charts on the dashboard will be blank by default if there isn’t an active connection.
Creating Charts to Visualize KPIs: Users have the option of using pre-existing templates or creating new charts from scratch. Chart configurations define a chart’s name, description, update times (milliseconds, seconds, minutes, and hours), maximum number of objects, and type (bar, line, pie, and time series). This flexibility lets you visualize massive statistics and analyze trends and performance issues faster and more accurately. The visualisation possibilities are further increased by DBeaver’s support for integrating Tableau charts and web-based charts from any available URL.
Transaction Log: All ‘transaction’ type queries (such as INSERT, DELETE, and UPDATE) carried out during the current DBeaver session are specifically displayed in the Transaction Log of DBeaver. Rolling-back transactions are orange or red, successfully committed transactions are green, and in-progress/pending transactions are uncoloured. Users can inspect all the current session’s transactions and non-transactional requests to gain a comprehensive overview. You may simply access this log by clicking on a statistics box adjacent to the commit mode selection or by using a toolbar button.
Query Manager: DBeaver’s Query Manager logs all SQL queries that are run, including the execution time, duration, rows that are impacted, and any problems that may have occurred. This goes beyond transaction logging. Sorting queries by type, time range, connection, catalogue, or schema is made easier with its filtering options. The query history can be automatically or manually deleted and saved in a file.
Query Execution Plan: One of the features in the SQL Editor is the ability to see query execution plans. A sophisticated graphical (tree) visualisation, which is very helpful in locating performance bottlenecks, or a straightforward text output can be used to display this. Users may better grasp how the query planner handles SQL statements by examining the visualisation, which shows the most costly nodes in the plan and offers comprehensive information. Outdated table statistics often cause disparities between the planner’s predicted number of rows and the actual number handled. EXPLAIN (ANALYSE, BUFFERS) shows I/O usage and caching efficiency.
Code Example:
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (id SERIAL PRIMARY KEY, product_name VARCHAR(50), quantity INT, price NUMERIC(10,2), sale_date TIMESTAMP DEFAULT NOW());
INSERT INTO sales (product_name, quantity, price) VALUES
('Laptop',5,1200.50), ('Phone',10,699.99), ('Tablet',3,400.00), ('Headphones',15,50.00);
BEGIN;
INSERT INTO sales (product_name, quantity, price) VALUES ('Monitor',2,300.00);
UPDATE sales SET quantity = quantity + 5 WHERE product_name = 'Laptop';
COMMIT;
SELECT product_name, SUM(quantity*price) AS total_sales FROM sales GROUP BY product_name ORDER BY total_sales DESC;
SELECT DATE(sale_date) AS day, SUM(quantity*price) AS total_sales FROM sales GROUP BY day ORDER BY day;
SELECT product_name, SUM(quantity) AS total_quantity FROM sales GROUP BY product_name;Output:
DROP TABLE
CREATE TABLE
INSERT 0 4
BEGIN
INSERT 0 1
UPDATE 1
COMMIT
product_name | total_sales
--------------+-------------
Laptop | 12005.00
Phone | 6999.90
Tablet | 1200.00
Headphones | 750.00
Monitor | 600.00
(5 rows)
day | total_sales
------------+-------------
2025-09-10 | 21554.90
(1 row)
product_name | total_quantity
--------------+----------------
Tablet | 3
Phone | 10
Monitor | 2
Laptop | 10
Headphones | 15
(5 rows)These broad monitoring features and DBeaver’s powerful visualisations help administrators understand PostgreSQL performance and operational health.