Useful PostgreSQL Commands/Queries
In this article, we will briefly discuss some queries and their uses, specifically in the case of both monitoring and debugging PostgreSQL-related issues.
Join the DZone community and get the full member experience.Join For Free
There are many types of queries and of course, all are important, but when it comes to monitoring and debugging, we are generally in a hurry and do not remember what to query to check for a specific problem/issue. In this blog, we will briefly discuss some queries and their uses, specifically in the case of monitoring and debugging PostgreSQL-related issues.
In the case of monitoring and debugging, we generally have the following categorical issues: size-related and performance-related. Sometimes these issues are interlinked.
Here are some basic Postgres commands/queries which will be very helpful in monitoring or debugging size-related issues:
- Checking the Size of the Database: There are many times where we need to check which database is the culprit of an issue i.e… which database is consuming major space . Following is a query to check the size of a database in decreasing order according to size consumed.
- After that, we may need to find which table/index is consuming the most space; for this, we have the following query:
There are many times where there are no records in the table, but due to dead tuples, the size of a table/index may increase. To check which table has the highest dead tuples, use the following query:
From the above output, we can also determine if the auto vacuum is running properly or not i.e... When the last auto vacuum ran on any particular table whose dead tuples are high.
Over time, due to MVCC, your table will grow in size (called table bloat)—this is why regular VACUUM is needed. This query will show you a list of tables and indexes with the most bloats. The value represents the number of “wasted bytes," or the difference between what is actually used by the table and index, and what we compute that it should be.
The way it works is it estimates the optimized size of the table/index by a calculation from each row sizes times total rows and compares that against the actual table size. Do note that this is an estimate, not an actual figure.
Query extracted but rewritten from checkpostgres
You should be looking at:
- tbloat: Table bloat, the ratio between what it currently is, and what it can be optimized to.
- wastedbytes: The number of bytes wasted.
- ibloat & wastedibytes: The same as above, but for indexes.
When you see a table with high bloats, then consider running VACUUM ANALYZE on it.
Performance Related issues
For performance-related monitoring, we need to run the following queries, though it may be too much in a day-to-day scenario.
- Get Running Queries (And Lock statuses) in PostgreSQL
The above query may help if some particular query is running for hours and has taken some lock, due to other queries getting stuck on lock. It also shows the database name on which query is running. We can also determine if some connection is open and no query is running so there may be some session leak in your database (for more for about session leak read the following article)
- To kill a particular query that is causing the issue, use this query:
This will send a SIGINT to the current process.
I hope this article helps all of you with at least some of your time spent debugging. Please comment and follow me if you have any questions or want to discuss!
Published at DZone with permission of Sahil Aggarwal. See the original article here.
Opinions expressed by DZone contributors are their own.