I’m having an SQL table (Postgres):

CREATE TABLE (
  "id" BIGINT NOT NULL PRIMARY KEY,
  "changedAt" TIMESTAMP NOT NULL,
  "familyId" VARCHAR(12) NOT NULL,
  ... (other columnds left out for clarity)
)

We are getting a lot of updates (records) into this table and want to keep only the ones that are not older that 90 days. The trick is if a “familyId” didn’t have any update since 90 days, we need to keep the most recent of the old ones. This can apply also if there were updates within 90 days.

So for the following data:

1, 2024-04-12 13:20:23, "Test 1", ...
2, 2024-03-12 13:20:23, "Test 1", ...
3, 2024-01-01 13:20:23, "Test 1", ...
4, 2022-01-12 13:20:23, "Test 1", ...

3 and 4 are both older than 90 days, but I want to have the 3rd kept to have a full coverage of data for the last 90 days.

How can I construct a DELETE query to achieve this?
Which indices could help that query to be fast?

I don’t know how to handle this situation on my own.