Postgres Troubleshooting: Fixing Duplicate Primary Key Rows
Greg Sabino Mullane
7 min readMore by this author
Someone recently asked on the Postgres mailing lists about how to remove unwanted duplicate rows from their table. They are “unwanted” in that sense that the same value appears more than once in a column designated as a primary key. We’ve seen an uptick in this problem since glibc was kind enough to change the way they sorted things. This can lead to invalid indexes when one upgrades their OS and modifies the underlying glibc library.
One of the main effects of a corrupted unique index is allowing rows to get added which should be caught by the primary key. In other words, for a table with a primary key on a column named “id”, you might observe things like this:
-- Can you spot the problem?
SELECT id FROM mytable ORDER BY id LIMIT 5;
id
----
1
2
3
3
4
(5 rows)
Without knowing anything else about the problem, what’s the first step to solving it? If you said take a backup, you are correct! Make a fresh backup anytime you think something is wrong with your database, or before any attempt to fix such a problem.
Aside: can we simply reindex? No - a unique index cannot be created (or recreated) as long as there are duplicate rows. Postgres will simply refuse to do so, as it violates the uniqueness we are trying to enforce. So we must delete the rows from the table.
Here is one careful recipe for fixing the problem of duplicated primary key entries in your Postgres table. Obviously you need to adjust this as needed for your situation, but just walk through it slowly and make sure you understand each step. Especially if this happens in production (spoiler: it almost always happens in production).
1. Debugging aids
The first thing we want to do may look a little strange:
-- Encourage not using indexes:
set enable_indexscan = 0;
set enable_bitmapscan = 0;
set enable_indexonlyscan = 0;
Because bad indexes are the primary way bad rows like this get into our database, we cannot trust them. These low-level debug aids are a way of telling the Postgres planner to prioritize other ways of getting the data. In our case, that means hitting the table directly and not looking things up in the indexes.
2. Run a quick sanity check
-- Sanity check. This should return a number greater than 1. If not, stop.
set search_path = public;
select count(*) from mytable where id = 3;
Before we begin, we want to make sure we have the correct table. The search_path is a safety measure, as is the lookup on the table. Since the id is the primary key of the table, it should return a count of 1 for each value. In our case, we know this table has multiple entries for id “3”, so this is mostly a sanity check that we are about to operate on the correct patient. We expect to get back a number greater than “1”. For a working primary key, the only values ever returned should be 0 or 1.
3. Create a backup (always 😉)
-- Make a backup:
create table mytable_backup as select * from mytable;
Before we begin, we want to copy all of the existing rows from the table into a new backup table. Again, this does not take the place of a full and complete backup of your entire database (always step 0), but it’s another good safety feature.
4. Make a test table
-- Test out the process on a subset of the data:
create table test_mytable as select * from mytable where id < 30;
create table test_mytable_duperows_20250317 (like mytable);
It’s always a good idea to test things first on a test table. In our case, in a smaller version of the actual table. Because we know that there are problematic rows at id 3, we created a new table that contains those rows. We also create a new empty table called test_mytable_20250317 which is going to hold the duplicate rows that get removed. The date at the end tells future viewers when the table was created.
5. Start the cleanup in a replica session
From here on out, we are going to start the actual cleanup. We start a transaction, and then set our session_replication_role to replica, which is an advanced (and dangerous) command that disables all triggers and rules. Normally this is not a good practice, but we want to do this in case there are any foreign keys that may prevent us from removing the bad rows. In addition, we do this as SET LOCAL instead of just SET which ensures that this setting goes back to normal at the next COMMIT or ROLLBACK.
begin;
set local session_replication_role = 'replica';
Because we just created this test table, we know that it has no triggers and is not linked to any other tables via foreign keys, but we want to make this test as close to possible as the actual table modification, so we leave the session_replication_role modification in place.
6. Cleanup duplicate rows with a function
begin;
set local session_replication_role = 'replica';
with goodrows as (
select min(ctid) from TEST_mytable group by id
)
,mydelete as (
delete from TEST_mytable
where not exists (select 1 from goodrows where min=ctid)
returning *
)
insert into TEST_mytable_duperows_20250317 select * from mydelete;
reset session_replication_role;
commit;
So we issue a begin, set the session_replication_role, run a single SQL statement, reset the session_replication_role, and finally commit. That SQL statement is doing some heavy lifting, so let’s break it down.
select min(ctid) from TEST_mytable group by id The first thing we need to do is figure out a way to ferret out which rows are duplicated. As the id column is supposed to be unique (as all primary key columns are), we know that any ids appearing more than once need a tie-breaker. Each row in Postgres has a hidden column named “ctid” that stands for column tuple identifier, and is basically a pointer to exactly where the actual physical row is located. Therefore, it is always unique. If we group by the id column, we can pull a single ctid for each unique id by asking for the “lowest” ctid (it doesn’t really matter if we use min() or max() or something else, just as long as we pick only one).
We are going to store that information and use it to help with the delete, so we start a cte via the WITH command, and name this one as goodrows .
delete from TEST_mytable
where not exists (select 1 from goodrows where min=ctid)
returning *
The next step is to delete all duplicate rows that do NOT come from our goodrows list which was just created. So each of the duplicated rows will have distinct ctids, and we are going to delete all but one of them for each id. The final RETURNING * bit tells delete to send back complete information about every row that was deleted.
insert into test_mytable_duperows_20250317 select * from mydelete;
Finally, we take the output of the delete and store it into our table. In this way, the rows are deleted, but we still have a complete list of which rows were removed, for debugging and forensics.
At this point, the duplicated rows should be removed, and inside the “duperows” tables. It is probably best to examine both this table and the test_mytable one, to make sure this all worked as expected.
7. Run the function on the live table
When ready, you can re-run the same code, but replace the test table with the actual one:
create table mytable_duperows_20250317 (like mytable);
begin;
set local session_replication_role = 'replica';
with goodrows as (
select min(ctid) from mytable group by id
)
,mydelete as (
delete from mytable
where not exists (select 1 from goodrows where min=ctid)
returning *
)
insert into mytable_duperows_20250317 select * from mydelete;
reset session_replication_role;
commit;
8. Reindex
As a final step, we want to rebuild those suspect indexes. Even though the duplicated rows are gone, the index may still have wrong information. The REINDEX command is basically a drop and recreate, so we do that for all indexes that may exist in our table:
reindex table mytable;
Now we can also return Postgres to normal plan settings with enable_indexscan, enable_bitmapscan, and enable_indexonlyscan all set to 1.
That’s all the steps! When in doubt, reach out to your local Postgres expert if you are not 100% sure of your steps, as corruption is not something to take lightly.