I have a table in Postgres database which has a lot of records (30,00,000 ).

I want to delete all records which are older than an year but I see that there is no timestamp or date column in this table.

How can I delete the old records in this case? (first I want to get the count of records which are older than 1 year)

Also, will deleting huge number of records in a single SQL query cause performance issues while the deletion is in progress?

CodePudding user response：

A row in a PostgreSQL table has no age unless you explicitly store it with the data, so there is no way to do that. You have to use a condition based on the data.

Deleting many data can take a long time, even if all foreign keys are indexed. The king's way to speedy mass deletions is table partitioning.