я и говорю что если постгрес в фиксированном размере хранит это на диске то это не сильно отличается от перевода цифрового поля в текстовое

тогда я не понимаю ваши аргументы, при чем здесь цифровое поле?

это был пример когда структура данных на диске отличается и нужно пересобирать таблицу

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

да я согласен что на практике текст практичнее

просто говорю что в принципе варчар мог бы оптимальнее хранить данные на диске и тогда был бы смысл.

Ну так вставляйте из /dev/zero, да и всё. ;)

> Т.е. это просто данные из одной системы переливаются в другую

Понятно. А что у Вас в этом ETL запросы какие-то странные, что по синтаксису, что по сути?
К примеру, вот этот:

--просто помечаем удаленным, но физически не удаляем

В нём есть

SET name_servers = '{}'

Вы же данные о name_servers так фактически удаляете, или важно наличие "пустой" записи почему-то?
И зачем там этот странный синтаксис для anti join, да и вообще лишний self join, на первый взгляд?
Ну и т.д.

Я бы его сначала переписал так, чтобы он был правильным.
Оптимизация некорректных запросов — чистая потеря времени, я вот к чему.

А каким образом? Вы можете предложить решение, которое было бы более эффективным на практике?

хранить короткие строки фиксированного размера на диске

И сразу нет. ;)
varchar(20) — это строка длины от 0 до 20.
Вы предлагаете хранить их все с фиксированным размером 20, и считаете, что на практике (для "обычных" данных, у которых в подобных случаях длина почти всегда меньше максимума), это будет эффективнее?

да это будет эффективнее потому что насколько я понимаю сейчас пг для текстовых строк лезет в отдельную внутреннюю таблицу, нет?

а так он прям при выборке читая один файл сразу все получит

О_о

я могу нести фигню, извините. Но пока выглядит логично.

--просто помечаем удаленным, но физически не удаляем

SET name_servers = '{}'

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

> Вы же данные о name_servers так фактически удаляете, или важно наличие "пустой" записи почему-то?
я физически строки не хочу удалять, по факту можно отказаться от того чтоб менять поле name_servers, когда я проставляю поле deleted_at, но мне почему-то показалось что так pg подчистит за собой в этом месте, чтоб каждый день уделенные и ненужные домены не занимали лишнее место на ns сервера )

> И зачем там этот странный синтаксис для anti join, да и вообще лишний self join, на первый взгляд?
я не смог его по-другому переписать, чтоб он работал также 😄

> Я бы его сначала переписа
А что тут можно изменить, чтоб запрос не поломался? Там ведь вся соль в том, что у нас есть таблица t1 с 150 млн строк со вчерашнего дня (да и вообще - с прошлых дней), я в новую t2 заливаю новые данные (где может быть от силы обновится всего 150 тысяч строк из 150 млн), потом через эти апдейты с антиджоинами / инсеры делаю следующее:

1. вначале помечаю в t1 те строки, что отсутствуют в t2 (удаляю фактически, но без физического удаления)
2. обновляю в t1 измененные в t2 строки
3. те что были удалены в t1 ранее, но снова появились в t2 - тоже меняю, чтоб сменить у них crated_at
4. вставляю все строки из t2, которые отсутствуют в t1

вот такая логика там, чтоб таблица t1 содеражала всё что было в предыдущие дни, но новые/обновленные данные из t2 там тоже присутствовали, то есть состояние базы всегда должно быть актуальным 🙂

UPD: Полный пример для воспроизведения выложил тут: https://pastebin.com/2bpcmqAT

Он "лезет" в эту таблицу (TOAST) только для слишком длинных строк (на самом деле, rows; обычно, размером более 2 kB).
Поэтому нет, это не будет эффективнее, насколько я вижу.

а с варчаром он на диске как хранит? строки по 2кб прям на месте?

Что varchar, что char, что text хранятся одинаково. Если размер row с этими полями не превышает 2 Кб, то прямо в самой таблице, да.

т.е. строки не фиксированного размера в файле?