How to Fix PostgreSQL Error: "FATAL: sorry, too many clients already"

 If you are seeing the error FATAL: sorry, too many clients already or FATAL: too many connections for role "username", your PostgreSQL instance has hit its limit of concurrent connections.

This usually happens when:

• Your application isn't closing database connections properly.

• You have a sudden spike in traffic.

• A connection pooler (like PgBouncer) isn't configured.

Step 1: Check Current Connection Usage

Before changing any settings, you need to see who is using the connections. Run this query to get a breakdown of active vs. idle sessions:

SELECT count(*), state 

FROM pg_stat_activity 

GROUP BY state;

If you see a high number of "idle" connections, your application is likely "leaking" connections (opening them but never closing them).

Step 2: Emergency Fix (Kill Idle Connections)

If your production site is down because of this error, you can manually terminate idle sessions to free up slots immediately:

-- This kills all idle connections older than 5 minutes

SELECT pg_terminate_backend(pid)

FROM pg_stat_activity

WHERE state = 'idle' 

AND state_change < current_timestamp - interval '5 minutes';

Step 3: Increase max_connections (The Configuration Fix)

The default limit in PostgreSQL is often 100. If your hardware has enough RAM, you can increase this.

1. Find your config file: SHOW config_file;

2. Open postgresql.conf and find the max_connections setting.

3. Change it to a higher value (e.g., 200 or 500).

4. Restart PostgreSQL for changes to take effect.

Warning: Every connection consumes memory (roughly 5-10MB). If you set this too high, you might run the entire server out of RAM (OOM).

Step 4: The Professional Solution (Connection Pooling)

Increasing max_connections is a temporary fix. For a production-grade setup, you should use PgBouncer.

Instead of your application connecting directly to Postgres, it connects to PgBouncer. PgBouncer keeps a small pool of real connections open to the database and rotates them among hundreds of incoming requests.

Sample pgbouncer.ini configuration:

[databases]

mydatabase = host=127.0.0.1 port=5432 dbname=mydatabase

[pgbouncer]

listen_port = 6432

auth_type = md5

pool_mode = transaction

max_client_conn = 1000

default_pool_size = 20

Summary Checklist

• Audit your code: Ensure every db.connect() has a corresponding db.close().

• Monitor: Set up alerts for when connections exceed 80% of max_connections.

• Scale: Use a connection pooler like PgBouncer or pg_pool if you have more than 100 active users.

SQL query to find duplicate records

Introduction

These days data is growing massively and since the data is growing the amount of bad data is also growing. Today we'll talk about one form of bad data which we can call duplicate data. We use few database management systems to store and process the data ie sql server, postgresql, oracle etc. We store lot of data in these systems but sometimes we get lot duplicate data as well in our systems which can impact the true data and reporting on which lot of businesses and important decisions are dependent.

  So for us working on data, it's very important to identify the duplicates and remove them from the database which help businesses towards better reporting. 

Let's get into the practical to understand the duplicates. First we need to create a table with few columns.

In this tutorial I am using postgresql. 

CREATE TABLE duplicate_data_test(

id int,

name text

)

now let's insert some data with duplicates.

insert into duplicate_data_test values(1,'One');

insert into duplicate_data_test values(1,'One');

insert into duplicate_data_test values(2,'two');

insert into duplicate_data_test values(3,'three');

insert into duplicate_data_test values(4,'four');

insert into duplicate_data_test values(5,'three');

Query the data to see results:

select * from duplicate_data_test;

Let's find the duplicates for entire row.

select id,name,count(*) as count from duplicate_data_test

group by id,name having count(*) > 1;

or

Using CTE as well to get the duplicates.

with cte(id,name)

as

(select id,name,row_number() over (partition by id,name order by id) as rn

from duplicate_data_test)

select * from cte;

Above query will give you the count. You can remove the count in select list if you want and then you will get duplicate record without count number. 

Sometimes we want to find duplicates based on one column. Sometime we don't have entire row is duplicate rather just one column have some duplicate values. 

So we can definitely use partition by clause like below.

select * from (

select id,name,rank() over (partition by name order by id) as rnk

from duplicate_data_test

) as dt where dt.rnk > 1

Conclusion

That was it about duplicates. Always test above code in lower environment before implementing in Production.

What is SQL and the history of SQL?

What is SQL

SQL stands for structured query language. It is used to query the relational databases. Since the name is saying the structured means it handles structured data. 

What is structured data

Here structured data means a data which is in tabular format. And the data that have some relations between data is called relational data. 

You can query structured data residing inside relational database management system. Relational database management system is a management system which stores and handles the data. Data volume could be too much like in GB, TB,PB. RDMS(Relational database management system) can handle this much of volume.

There are lot of RDBMS in the market like Oracle, SQL server, MYSQL and Postgres etc. Here MYSQL and Postgres both are open source.

Previously SQL was called structured english query language (SEQUEL) . Later it was renamed as structured query language(SQL). Many people call it SEQUEL and many call it SQL. So it's up to you what you want to call it.

A brief History of SQL

• • 1970 − Dr. E. F. Codd of IBM is known as the father of relational databases. He described a relational model for databases.

  • 1974 − Structured Query Language appeared.

  • 1978 − IBM worked to develop Codd's ideas and released a product named System/R.

  • 1986 − IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software which later came to be known as Oracle.

How to write SQL and how to fetch data

First you need to decide which RDBMS you are going to install on your system. I would recommend using postgres which is open source or SQL server which is not open source but you can download it's free version. Microsoft is developer of SQL server.

Now I am assuming that you have downloaded the RDBMS. If not then you can practice on any online platform which provide you sql editor.

Suppose I have table EMPLOYEE, which has three columns name, age, city. 

Name	Age	City
Suraj	25	Banglore
Mohit	26	Chennai
Rahul	27	Lucknow

I want to fetch all records inside this table, so I will write below query:

SELECT * FROM EMPLOYEE;

Here you are query data then you need to write SELECT , * means all columns (Name,Age,City) ,write from keyword then table name. In this case table name is EMPLOYEE.

So above query will give you all records from the table. If I want to select only name then I need write only Name column in my query like Below:

SELECT Name FROM EMPLOYEE;

Similar for Age and City as well:

SELECT Age FROM EMPLOYEE;

SELECT City FROM EMPLOYEE; 

Suppose I want to display all details about Suraj then I'll write below query:

SELECT * FROM EMPLOYEE WHERE NAME = 'Suraj' 

Name	Age	City
Suraj	25	Banglore

 You can use a wild card for search but there is a lot to cover on SQL. I suggest to read more on basics of SQL to get started.

•