SELECT * FROM pg_stat_activity
. If there are many idle connections, it’s recommended to investigate a connection pooling solution, such as PgBouncer, which can reduce connection overhead and maintaining a manageable connection count.
shared_buffers
) to the total number of reads from disk and cache combined. Monitoring the ratio is crucial to analyzing the data reads and maintaining the proper ratio. Ideally, the cache hit rate should be above 99%, meaning at least 99% of database reads should be from cache and at most 1% from the disk.
EXPLAIN
parameter with queries to display the query execution plan that is being used to run the query statement. Additionally adding ANALYZE
along with the EXPLAIN
parameter helps identify slower queries as it provides finer, more accurate details such as the total time spent on the query execution, the time required to finish a sort program, etc. It is also a suggested practice to maintain an appropriate index for each table and keep reviewing them periodically.
pg_stat_statements
extension installed and enabled. PgHero uses it for query stats.
$HYDRA_DB
with your Hydra database connection string.
5432
.