Right query to get the current number of connections in a PostgreSQL DB
Those two requires aren't equivalent. The equivalent version of the first one would be:
SELECT sum(numbackends) FROM pg_stat_database;
In that case, I would expect that version to be slightly faster than the second one, simply because it has fewer rows to count. But you are not likely going to be able to measure a difference.
Both queries are based on exactly the same data, so they will be equally accurate.
How to list active connections on PostgreSQL?
Oh, I just found that command on PostgreSQL forum:
SELECT * FROM pg_stat_activity;
Tool to find the number of open postgresql connection in the application
Try this
select * from pg_stat_activity;
How to increase the max connections in postgres?
Just increasing max_connections
is bad idea. You need to increase shared_buffers
and kernel.shmmax
as well.
Considerations
max_connections
determines the maximum number of concurrent connections to the database server. The default is typically 100 connections.
Before increasing your connection count you might need to scale up your deployment. But before that, you should consider whether you really need an increased connection limit.
Each PostgreSQL connection consumes RAM for managing the connection or the client using it. The more connections you have, the more RAM you will be using that could instead be used to run the database.
A well-written app typically doesn't need a large number of connections. If you have an app that does need a large number of connections then consider using a tool such as pg_bouncer which can pool connections for you. As each connection consumes RAM, you should be looking to minimize their use.
How to increase max connections
1. Increase max_connection
and shared_buffers
in /var/lib/pgsql/{version_number}/data/postgresql.conf
change
max_connections = 100
shared_buffers = 24MB
to
max_connections = 300
shared_buffers = 80MB
The shared_buffers
configuration parameter determines how much memory is dedicated to PostgreSQL to use for caching data.
- If you have a system with 1GB or more of RAM, a reasonable starting
value for shared_buffers is 1/4 of the memory in your system. - it's unlikely you'll find using more than 40% of RAM to work better
than a smaller amount (like 25%) - Be aware that if your system or PostgreSQL build is 32-bit, it might
not be practical to set shared_buffers above 2 ~ 2.5GB. - Note that on Windows, large values for shared_buffers aren't as
effective, and you may find better results keeping it relatively low
and using the OS cache more instead. On Windows the useful range is
64MB to 512MB.
2. Change kernel.shmmax
You would need to increase kernel max segment size to be slightly larger
than the shared_buffers
.
In file /etc/sysctl.conf
set the parameter as shown below. It will take effect when postgresql
reboots (The following line makes the kernel max to 96Mb
)
kernel.shmmax=100663296
References
Postgres Max Connections And Shared Buffers
Tuning Your PostgreSQL Server
Get DB stats (number of updates/deletes/inserts) over a period of time - PostgreSQL
You will have to sum up the table statistics for all tables in the database, as there are no such statistics collected on the database level.
Where to define the maximum number of connections in Postgres?
max_connections
minussuperuser_reserved_connections
is the maximum for the sum of all non-superuser connections to all databases in the cluster.If you want to limit the number of users per database, setting the limit on the database seems like the obvious choice, right?
If you end up setting
max_connections
to a high value, consider using a connection pool instead.
Get ID and name of each customer and the total sum (sum of quantity of products purchased)
If you just want the sum of quantity of products:
SELECT c.client_id, c.first_name, SUM(o.units_sold)
FROM
client c
INNER JOIN orders o ON o.client_id = c.client_id
GROUP BY c.client_id, c.first_name;
But if you also want to see the price:
SELECT c.client_id, c.first_name, SUM(o.units_sold),
SUM(o.units_sold*p.price)
FROM
client c
INNER JOIN orders o ON o.client_id = c.client_id
INNER JOIN products p ON o.product_id = p.product_id
GROUP BY c.client_id, c.first_name;
Related Topics
Differencein These Two Queries as Getting Two Different Result Set
Calculating Days to Excluding Weekends (Monday to Friday) in SQL Server
Generate Nested Nth Level JSON SQL Server Using Recursive Cte
Join Two Dataframes on Common Column
Multiple Self-Join Based on Group by Results
Combine Two Tables into a New One So That Select Rows from the Other One Are Ignored
Bind a Column Default Value to a Function in SQL 2005
How to Convert Timestamp with Milliseconds to Date in Oracle
Oracle Autoincrement with Sequence and Trigger Is Not Working Correctly
How to Get the Latest 2 Items Per Category in One Select (With MySQL)
How to Find the Number of Occurrences of a Particular Character in a String Using SQL
Remove Blank-Padding from To_Char() Output