Technical Analysis of Efficient Duplicate Row Deletion in PostgreSQL Using ctid

Problem Background and Challenges

In database management, handling duplicate data is a common requirement, especially in table structures lacking primary keys or unique constraints. In PostgreSQL version 8.3.8, users face a typical scenario: a table contains multiple rows with identical values, and a single SQL command is needed to delete all duplicates, keeping only one copy per unique key value. The key column "key" is used to identify duplicates, ensuring that each "key" value corresponds to only one row.

Core Solution: The ctid System Column Method

PostgreSQL provides the ctid system column, a hidden physical row identifier composed of a page number and row index, formatted as (page_number, row_index). Using ctid allows unique identification of each row in a table, even without a defined primary key. The following is the SQL implementation of the best solution:

DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);

This command works as follows: for each row in the table "dupes" (aliased as a), the subquery finds all rows with the same "key" value (aliased as b) and returns the minimum ctid value (i.e., the row with the earliest physical location). Then, the main query deletes rows whose ctid is not equal to this minimum, thereby retaining the first record in each "key" value group.

Technical Details and Advantages

The core advantage of this method lies in its simplicity and efficiency. It avoids complex JOIN operations, implementing the deletion logic directly through subqueries and comparison operators. The use of ctid ensures differentiation even among identical rows, as ctid is unique. Additionally, this approach is suitable for small tables where speed is not a primary concern, but the logic is clear and easy to maintain.

As supplements, other answers provide variant implementations. For example, a method using the USING clause:

DELETE FROM dups a USING (
    SELECT MIN(ctid) as ctid, key
    FROM dups 
    GROUP BY key HAVING COUNT(*) > 1
) b
WHERE a.key = b.key 
AND a.ctid <> b.ctid

This method explicitly identifies duplicate groups via GROUP BY and HAVING clauses but may be slightly more complex. Another variant uses a self-join:

DELETE FROM dupes T1
    USING   dupes T2
WHERE   T1.ctid < T2.ctid
    AND T1.key  = T2.key;

It deletes older versions by comparing ctid sizes, but consistency in logic must be considered.

Practical Application and Example

Consider an example table "people" with name and age columns, without a primary key. Assume the data is as follows:

CREATE TABLE people (
    name    varchar(50) NOT NULL,
    surname varchar(50) NOT NULL,
    age     integer NOT NULL
);

INSERT INTO people (name, surname, age) VALUES 
    ('A.', 'Tom', 30),
    ('A.', 'Tom', 10),
    ('B.', 'Tom', 20),
    ('B', 'Chris', 20);

Apply the above solution to delete duplicate rows based on (name, surname):

DELETE FROM people a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   people b
                 WHERE  a.name = b.name AND a.surname = b.surname);

After execution, the table will retain three rows: ('A.', 'Tom', 30), ('B.', 'Tom', 20), and ('B', 'Chris', 20), deleting the duplicate ('A.', 'Tom', 10).

Considerations and Best Practices

When using this method, note the following: ctid is a physical identifier and may change with VACUUM operations, so it is advisable to perform deletions in a stable state. For large tables, consider performance optimizations such as adding temporary indexes. Additionally, this method does not control which row is kept (always the minimum ctid); if selection based on other criteria (e.g., timestamp) is needed, adjust the subquery logic.

In summary, the ctid-based method for deleting duplicate rows is a powerful and efficient technique in PostgreSQL, particularly suitable for cleaning tables without constraints. By deeply understanding ctid and subquery mechanisms, developers can flexibly address various data deduplication scenarios.