Ещё можно на TimescaleDB посмотреть

топовой экстеншн посгри, я последнее время напарываюсь на проекты с огромными данными и когда выбираю вместо таймскейла ванильный постгри - грущу ) Там прям дофига фич для комфортной жизни.

Кстати, а подскажите кто шарит, как лучше всего удалять дубликаты? У меня тут запрос после 2ух дней работы лег, я приуныл слегка... Разбил данные на 300 кусков (по 100к строк) и в 15 потоков запустил. Харды еле шуршат, может посоветуете как более оптимально написать запрос?

Я выбрал дубли в отдельную таблицу и запилил удаление:

egrip=# explain delete from egrip_versions v USING dups_ip2 where dups_ip2.ogrn = v.ogrn and dups_ip2.checksum = v.checksum and v.version < dups_ip2.ver and v.ogrn >= 0 and v.ogrn <= 10;
Delete on egrip_versions v  (cost=1.13..17.30 rows=1 width=12)
  ->  Nested Loop  (cost=1.13..17.30 rows=1 width=12)
        ->  Index Scan using egrip_versions_ogrn_version_idx on egrip_versions v  (cost=0.57..8.59 rows=1 width=51)
              Index Cond: ((ogrn >= 0) AND (ogrn <= 10))
        ->  Index Scan using dups_ip2_ogrn_idx on dups_ip2  (cost=0.56..8.70 rows=1 width=51)
              Index Cond: (ogrn = v.ogrn)
              Filter: ((v.version < ver) AND (v.checksum = checksum))
(7 rows)

Но у меня 100к строк удаляются где-то по часу (на 100к - 140к дублей)

Не объединить (это слово — обычный перевод термина UNION), а соединить (так обычно переводят JOIN).
ON — это условие соединения (только те пары из декартового произведения таблиц, для которых условие возвращает true, будут в результате запроса).
А вообще — всё это базовый SQL, т.е. почитали бы Вы какой-то tutorial. ;)

iotop выглядит как-то так

дробить на кусочки, которые за 2-5 минут отрабатывают (10к, скажем) и в несколько потоков

Странный план какой-то, на первый взгляд (оценки странные; и похоже, что индексы тут не оптимальные).
Вы делали VACUUM ANALYZE каждой из этих таблиц?
А, ещё:

> Разбил данные на 300 кусков (по 100к строк) и в 15 потоков запустил.

Какие данные / как "разбили" (мне, например, непонятно)?

я where ogrn > от фонаря написал, ща план пересмотрю с реальными цифрами, сек

explain delete from egrip_versions v USING dups_ip2 where dups_ip2.ogrn = v.ogrn and dups_ip2.checksum = v.checksum and v.version < dups_ip2.ver and v.ogrn >= 304525720400062 and v.ogrn <= 304560905800126;
                                                          QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Delete on egrip_versions v  (cost=8876.01..5945257.27 rows=1 width=12)
  ->  Merge Join  (cost=8876.01..5945257.27 rows=1 width=12)
        Merge Cond: (v.ogrn = dups_ip2.ogrn)
        Join Filter: ((v.version < dups_ip2.ver) AND (v.checksum = dups_ip2.checksum))
        ->  Index Scan using egrip_versions_ogrn_version_idx on egrip_versions v  (cost=0.57..4434164.46 rows=1153398 width=51)
              Index Cond: ((ogrn >= '304525720400062'::bigint) AND (ogrn <= '304560905800126'::bigint))
        ->  Index Scan using dups_ip2_ogrn_idx on dups_ip2  (cost=0.56..1368752.04 rows=37720232 width=51)
JIT:
  Functions: 10
  Options: Inlining true, Optimization true, Expressions true, Deforming true
(10 rows)

мда, есть разница ))

данные разбил следующим образом:
1. сделал select .. group by ogrn, checksum having count(*) > 1
2. свалил его в csv на диск
3. разбил его через split по 100к строк
4. запустил delete с запросом выше через xargs в 15 потоков

интервалы выбрал из файлов через head/tail

Хмм... а зачем такие сложности?
Сколько там получалось дубликатов к удалению во всей таблице?
И какие размеры таблиц, кстати?
И что насчёт "был ли VACUUM ANALYZE"?

может перелить в новую таблицу с уникальным индексом по ним и on conflict do nothing?

Размер:

public    | egrip_versions     | table | egrip    | 260 GB  |

дубликатов на 100к ogrn (где-то 140к)

всего строк:
approximate_row_count
-----------------------
        1.1142952e+08

я думал об этом, наверное это наиболее быстрое решение, места свалить копию хватает, может получилось бы быстрее, но че-то решил так сделать...

да и от фрагментации избавит и вакуум не нужен

в данном случае да, прокатило бы. Хотя я сталкивался с аналогичной ситуацией, правда таблица была где-то на 900гиг и места под копию не хватило бы, видимо по той привычке не стал копию делать...

так с запросом все норм? Все же меня смущает его производительность

кто может обьяснить значение второго параметра array_length(anyarray, int) ?
https://www.postgresql.org/docs/8.4/functions-array.html