У вас есть уникальное поле, значит по нему удобно сделать индекс. По скольку UNIQUE может быть много, то создаётся некоастерный индекс

тоесть задав ограничение UNIQUE какому то полю, по нему сразу создается индекс автоматом?

Наткнулся на статью на хабре как обрабатывать обновление базы на примере КЛАДР (пункт 3.1 - алгоритм полной синхронизации): https://habr.com/ru/company/tensor/blog/492464/

Суть в том, что есть большая таблица на миллионы строк и нужно обновлять в ней данные, а в обновлении очень мало строк меняется, поэтому данные заливаются в temp таблицу и силами PG делаются апдейты существующих строк, удаляются отсутствующие в новой выгрузке и добавляются недостающие в старой таблице.

У меня ровно такая же задача, в таблице 150 млн строк, пытался делать через вычитку и обновление силами python, заоптимизировался так, что уже некуда (redis для кеша, copy на вставку, fsync = off, synchronous_commit = off, full_page_writes = off, unlogged table и тд) - и всё равно обновление проходит больше чем 24 часа, что неприемлимо. Хочу пробовать кейс из этого примера, поэтому возникла пара вопросов, может быть кто-то делает что-то подобное и может подсказать:

1. На сколько вообще в предложенном примере оптимально написаны запросы для обработки (фактически построчной вычитки / объединения через антиджоин) 2х таблиц по 150 млн строк?
2. Что бы вы подкрутили в настройках pg, чтоб эти запросы выполнялись быстрее (вначале добавление данных через COPY, а потом апдейты/инсерты)? Памяти на сервере 4ГБ, 3ГБ могу отдать целиком PG.

если что - речь идёт вот про эти запросы из поста (заскринил): https://i.imgur.com/Z1N1nZs.png

а если из этого выкинуть питон и сделать через sql запросы? 150млн, ну вроде бы не очень то и много, а сколько при этом строк обновляется, запись в таблицу параллельно не идёт?

обновляться (апдейты и добавление новых строк), думаю, ежедневно будет где-то 100-500 тысяч строк
параллельной записи в этот момент в таблицу 100% не будет, при желании можно и чтение выключать на час-два (ночью), то есть отдать вообще все ресусры на обновление таблицы

и да, я и хочу выкинуть питон и сделать как в примере запросов на скрине )
точнее из питона посылать эти запросы, да заливать данные через COPY, но НЕ как сейчас - всё выгружать в память питона/redis и делать там проверки, потому многочисленные тесты показали что не успеваю к следующему апдейту обновить предыдущее))

памяти бы 32 гб, ну да ладно. я бы делал итеративный апдейт по 500-1500 строк за итерацию и занимался бы оптимзацией по факту получения значений на итерацию, а потом 150M/итерацию и смотрел бы на результат. Одним из шагов было бы партиционирование (возможно, временное) по стабильному индексу из входных данных, если это возможно и в таком случае запускал бы параллельные итерации

таблица индексирована, при обновлении можно использовать индекс?

32ГБ памяти нет, если было бы столько, то я бы обновление делал прям в RAM апки 🙁
А о каком итеративном апдейте идет речь?

Партицирование сейчас есть, 36 партиций используются, думал к слову что как раз не всю базу обновлять, а по партициям, чтоб меньше данных вертеть.

Таблица индексирована, вроде даже сейчас все поля в индексах (может даже это и плохо в моем случае, но тут отдельно разбираться надо)

все поля в индексах, скорее всего в этом дело. есть ли уникальное индексное поле по которому можно обновлять? и да, покожите где-нибудь на пастбине структуру таблицы/индексов.

структура: https://pastebin.com/fyYWRdg8

уникальное индексное поле есть, это последнее - domain_name VARCHAR(70), но по хорошему бы там ещё первичный ключ сменить на (tld_id, domain), хотя можно отказаться от этого если такой pk тяжелее для pg (я не в курсе) и сделать только по домену (писать целиком весь домен в поле, не обрезая доменную зону)

у Вас скорее всего проблема с архитектурой базы, а не с питоном или ещё чем-то и в первую очередь нужно разобраться с ней, думаю завтра Ярослав подскажет точнее.  я бы сделал пк по id, ибо не понятно зачем нужен пк по текстовому полю, какие ещё индексы есть?

ну тут ещё от запросов зависит, update по id возможен?

надеюсь удалений у Вас из этой таблицы нет?

btree индексы на все поля (кроме name_servers), партицирование по полю letter, gin индекс на поле name_servers

апдейт по id не возможен, там он "съедет" в последующей выгрузке

удалений как раз не хочу делать, хочу апдейтить поле deleted_at

ок, это правильно, по остальному лучше дождаться Ярослава :)

к слову, о структуре - у меня сейчас все name_servers хранятся в отдельной таблице, а в этой таблице массив их id integer[], это сделано чтоб использовать intarray.
отчасти - самая большая боль и оказалась в этом, поэтому сейчас буду пробовать делать string[] и убирать ту таблицу, но не ясно как это скажется потом на запросах <@

спасибо, буду рад любым замечаниям и советам как и что подкрутить 🙂

Hello All. This query of mine is very slow. Do you know the proper syntax for a index creation?