Привет) не могу придумать как эффективно решить задачу: есть таблица (5

млрд) с полями key varchar, value varchar,
В большинстве value лежит IP-адрес, но есть плохие данные, где там фигня вместо IP.
Вопрос: как можно эффективно убрать плохие данные, оставив value где только IP?
Пробовал выбирать с regexp (~) - очень долго
При insert в таблицу с value inet - ошибка
Может какой-то индекс нужно построить? или как-то иначе...

Тип поля value какой?

сейчас - обычный varchar

И в каком виде там лежит ip address? 127.0.0.1 ?

да, все так :)

Печально. Надо писать курсор и бежать по таблице, выбрасывать плохие записи, по одной в транзакции. Запустить на недельку работать, когда-нибудь разберёт это дело

то есть это реально полный скан?

Ну да

Вам в итоге нужно получить таблицу с value inet или просто зачистить то, что не проходит валидацию? В табличку в это время кто-то пишет? Если нужно тип сменить, то create table xxx as select key, value::inet from таблица where value ~ регексп. Если менять тип не надо и в неё кто-то пишет - создавайте not valid констрейнт по условию корректности адреса, затем удаляйте кривые строки и делайте validate constraint (это, вроде, можно без блокировки всей таблицы сделать). В остальных случаях тупо delete по условию. Да, медленно, но тут ничего не поделаешь.

Не, delete не вариант. Большая таблица

а если с условием по id (если он есть) и через циклик ? )

можно оба варианта... таблица временная, никто не пишет кроме меня value ~ regexp - работает оооооочень долго, непредсказуемо долго

@rrrrad , 5 не миллионов, и миллиардов, какой ещё delete?

Хмм... и почему же?

Да потому что он никогда не закончится

А какой regexp? В общем, показали бы Вы то, что у Вас есть — \d и \dt таблицы, и запрос, которым удаляете.

Закончится, куда он денется. А альтернатива, кстати, какая?

regexp поставил '[a-z]' (строки с буквой в любом месте)

Курсором бежать по записям, проверять валидность, удалять по одной плохие строки (либо что-то ещё делать с ними). Так хоть прогресс гарантирован. Ну и это конечно не неделю работать будет, месяца два...

Я попросил три вещи показать, нет?

на телефоне доступа нет к данным, извините... )

И это будет тупо гораздо дольше, больше ничего. Вот из какой СУБД у Вас такие привычки, где разработчики умудрились так накосить, что у них курсор работает быстрее простого DELETE FROM, а? ;)

Ещё хорошо бы сохранять периодически обработанный диапазон id, чтобы при сбое можно было бы вернуться к началу этого диапазона, а не с нуля все

Я не говорил что будет быстрее.

Так покажите, когда будет. Там вполне может быть полезная информация, которой Вы пока не показали.

хорошо) спасибо за помощь

Ну так тогда этим есть смысл заниматься только для защиты от отката в случае "падения" сервера.

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

Именно для этого

Хорошо хоть "я не знаю, меня друг попросил узнать"

> Если что не так - он встанет, Постоит и пойдёт дальше (когда транзакция, которая его блокировала, завершится), ничего страшного. > а что он сделал - откатиться (это ещё неделя). А эти опасения у Вас из какой СУБД? ;) ROLLBACK в PostgreSQL почти всегда происходит мгновенно, причём независимо от выполненного в транзакции объёма работы, just FYI.

Я имел в виду ошибку

Второе - да, может неправ.

Триграммный индекс не пробовали?

здесь нет, но думал про него. Он обычно долго строится и не сильно ускоряет запросы вида '%term%' (по моему скромному опыту)

Нормально написанный DELETE может прервать разве что deadlock (и т.п.), а если к этой таблице никто больше не обращается, это не проблема. Вот "падение" / перезапуск сервера PostgreSQL (по несвязанным причинам) в это время — это риск, да.

При таком объеме, вероятно возможная причина падения - нехватка дискового пространства. Вот об этом надо будет позаботиться (чтобы место было столько, сколько занимает таблица + wal-ы)

Мне показалось, что "кривых" записей там единицы, так что и в WAL будет записано немного. Но причина может любой, да.

Хм... да, на вашей регулярке (и других коротких) без индекса может оказаться раза в 3 быстрее. У меня, на 13 символах (~ '52\:18\:0060131') GIN ускоряет с ~2300 мсек до ~60 мсек, а GIST выступает намного хуже.