Hello! In this article we will cover how to check active sessions in PostgreSQL topic.
What is a Session?
A session is a connection. When you connect to your database using psql command line it is a session, or using dbeaver it is a session.
Pooled Connection Sources
I would like to put a separate title for pooled connection sources. If you are familiar with the enterprise software ecosystem you would know C# and Java are the most popular languages.
Both Java and C# manages connections via pools.
What is Connection Pooling?
Opening and closing a connection have a cost. Especially opening a new connection consumes time.
Application servers solves that problem via pooling connection. For example, initially, application server opens 10 connection, increases it to 50 over time on demand.
When a request reaches server, application asks to application server for a connection, and application server chooses a connection from pool and assigns it to use.
What should that mean to a database administrator?
Total number of connections = Application Server Count X Pool Size
That is why knowing how to check active sessions in PostgreSQL is important.
How to Check Active Sessions in Postgresql?
pg_stat_activity is our key view again. You may want to check out my article about how to check running queries in Postgres.
select * from pg_catalog.pg_stat_activity where state != 'idle';
Select statement above will show not idle sessions.
But for some cases, you might need to have a query that gives you more insights about the statement.
select current_timestamp - query_start as runtime, datname, usename, state, query from pg_stat_activity where state != 'idle' order by 1 desc;
Runtime column will contain the amount of time that query took.
Relation With Operating System
Let’s add pid to our query:
select current_timestamp - query_start as runtime, pid, datname, usename, state, query from pg_stat_activity where state != 'idle' order by 1 desc;
As you can see, value of the pid is 418. If you check process id’s on the operating system level, you will find the same process id, which is 418 in our case.
Each session on PostgreSQL spawns an OS process. That means, to handle higher amount of connections, tuning PostgreSQL is not enough, you should tune operating system.
Summary
Have you ever wondered how to effectively manage active sessions in PostgreSQL? Understanding the significance of monitoring active sessions and their relation with the operating system can greatly impact a database administrator’s decision-making. By utilizing key views such as pg_stat_activity and gaining important insights into query runtimes, database administrators can ensure optimal performance and resource allocation. Do you have any questions about checking active sessions in PostgreSQL, or any insights to share about your own experiences with session management? Leave a comment below and feel free to share this article with others who might benefit from this information!
Hi! I’m an IT Specialist
I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.
Get in touch with me.
Leave a Reply