PostgreSQL: Deleting all records from a table, except for the first and last ones.

Deleting all records from a table containing a high number of rows, except for the first and last ones, is a non-trivial task due to the volume of data and the potential impact on performance.

In PostgreSQL, you can use subqueries and window functions to identify which rows are the first and last based on certain criteria (such as a unique identifier or date).

Step 1: Identifying the First and Last Rows

First, we need to define which rows are considered the first and last. For instance, if we have an id column that uniquely identifies each record and represents a serial number, we can assume that the smallest and largest id correspond to the first and last rows, respectively.

Step 2: The Deletion Query

The query to delete all records except for the first and last would look like this:

DELETE FROM your_table
WHERE id NOT IN (
 (SELECT MIN(id) FROM your_table),
 (SELECT MAX(id) FROM your_table)
);

This query first calculates MIN(id) and MAX(id), which are the identifiers for the first and last rows, respectively. Then, the DELETE operation is applied to all rows whose identifiers do not match these two values.

Important Considerations

Performance:

Deleting a large number of rows can significantly affect database performance. This can cause long lock times and slow down the system. It is recommended to perform such operations during low-activity periods or in stages.

Transactions:

Such bulk delete operations should be performed within a transaction to enable rollback in case of any issues.

Backup:

Always create backups of data before performing bulk deletions, especially when dealing with billions of rows.

Phased Deletion:

If the table is very large, consider deleting data in phases to minimize the load on the system.

WITH rows_to_delete AS (
 SELECT id FROM your_table
 WHERE id NOT IN (
 (SELECT MIN(id) FROM your_table),
 (SELECT MAX(id) FROM your_table)
 )
 LIMIT 100000 - delete a limited number of rows at a time
)
DELETE FROM your_table
WHERE id IN (SELECT id FROM rows_to_delete);

Using this method, you can gradually reduce the load by performing deletions in small batches.