Строковые типы в PostgreSQL: text, varchar и char

← назад к разделу

Если вы переходите на PostgreSQL с MySQL или Oracle, первое, что удивляет — здесь почти всегда пишут просто text, без цифры в скобках. Разбираемся почему.

Откуда взялся varchar(255)

В MySQL и старых версиях Oracle строковые типы действительно различались по устройству хранения. varchar(255) физически занимал другой объём на диске, чем text. Поэтому разработчики привыкли ставить длину «на всякий случай».

В PostgreSQL так не работает. Здесь text и varchar(n) хранятся одинаково — через один и тот же внутренний механизм. varchar(255) — это просто text с дополнительной проверкой «не длиннее 255 символов» перед каждой записью. Считает она именно символы, а не байты: девять кириллических букв — это девять символов и восемнадцать байт, и в varchar(10) они пройдут. Скорость одинакова, место на диске одинаково.

А вот char(n) из этой пары выпадает: он дополняет строку пробелами до полной длины и хранит их вместе со значением. Проверить можно прямо запросом — pg_column_size показывает, сколько байт занимает значение:

живой пример

SELECT pg_column_size('Иванов'::text)         AS text_bytes,
       pg_column_size('Иванов'::varchar(255)) AS varchar_bytes,
       pg_column_size('Иванов'::char(20))     AS char_bytes;

Ответ — 16 | 16 | 30. Первые две колонки совпадают байт в байт, а char(20) добил шесть букв пробелами до двадцати символов и таскает их с собой.

Одна тонкость, из-за которой эти числа потом не сходятся с расчётами. Шесть кириллических букв — это 12 байт в UTF-8, откуда же 16? Четыре байта сверху — служебный заголовок с длиной значения. В запросе выше значение измерялось «на весу», само по себе. А у значения, уже уложенного в колонку таблицы, короткие строки (до 126 байт) получают заголовок всего в один байт вместо четырёх: тот же pg_column_size, но по колонке таблицы, покажет 13 | 13 | 27. Когда считаете место под будущую схему, опирайтесь на вторые числа.

По умолчанию — text

Для большинства строковых полей правильный тип — text:

CREATE TABLE customer (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name text NOT NULL,
    email     text NOT NULL,
    bio       text
);

Почему text лучше, чем varchar(255):

• Если завтра понадобится хранить строку длиннее 255 символов — не нужна миграция таблицы.
• В старых версиях PostgreSQL расширение varchar(n) переписывало всю таблицу. С text такой проблемы нет.
• Явно показывает: длина не регламентирована доменным правилом.

Когда длину указывать всё-таки нужно

Длину в колонке имеет смысл ставить, когда она продиктована реальным стандартом, а не ощущением «тут не должно быть больше тысячи символов».

Примеры обоснованных ограничений:

phone_e164   varchar(15)  NOT NULL,  -- E.164: максимум 15 цифр
country_code char(2)      NOT NULL,  -- ISO 3166-1 alpha-2: ровно 2 буквы
currency     char(3)      NOT NULL,  -- ISO 4217: ровно 3 буквы
inn          varchar(12)  NOT NULL,  -- ИНН: 10 цифр (юрлицо) или 12 (физлицо)

Антипример — когда длина взята «из головы»:

full_name    varchar(255),   -- почему 255? какой стандарт?
description  varchar(1000)   -- почему 1000?

Для full_name и description правильно взять text, а ограничение по длине вынести в код приложения, где его легко изменить и протестировать:

// Jakarta Validation
public record CreateCustomerCommand(
    @NotBlank @Size(max = 200) String fullName,
    @Size(max = 5000)          String description
) {}

Так бизнес-ограничение остаётся там, где ему место — в логике приложения. Если бизнес решит, что теперь можно 300 символов, меняется одна строчка в коде, а не схема базы данных.

И сразу про границу ответственности. Аннотация валидации в коде (@Size, @Column(length = …)) проверяет то, что проходит через это приложение. Она не мешает записать более длинную строку миграцией, скриптом поддержки, другим сервисом над той же базой или ручным INSERT из клиента. Если длина — настоящее ограничение предметной области, а не подсказка форме, она должна стоять в схеме: varchar(200) или text с CHECK (length(title) <= 200). Второе удобнее тем, что меняется без перезаписи таблицы. Аннотация при этом остаётся — она даёт понятное сообщение пользователю до похода в базу, — но источником правды не является.

Отдельное прикладное решение той же темы — пустая строка против NULL. Это два разных значения: '' означает «известно, что пусто», NULL — «неизвестно», и путать их дорого, потому что ведут себя они по-разному во всём. Сравнение = '' находит пустые строки, IS NULL — только пустоту; length('') равен нулю, length(NULL) равен NULL; склейка с пустой строкой ничего не меняет, а склейка с NULL уничтожает всю строку. Самая же неприятная разница — в UNIQUE: две пустые строки считаются дублями и второй INSERT отвергается, а два NULL спокойно уживаются. Практическое правило: для необязательного текстового поля выбирают что-то одно и записывают выбор в схеме — либо NOT NULL DEFAULT '', либо разрешённый NULL и запрет пустой строки через CHECK (title <> ''). Худший вариант — когда в одной колонке живут оба, и каждый запрос приходится писать через coalesce.

char(n) — для фиксированных стандартов

char(n) — тип с фиксированной длиной. Если строка короче n, PostgreSQL дополняет её пробелами справа. Самое неприятное здесь не сами пробелы, а то, что одни операции их прячут, а другие — нет:

живой пример

SELECT '[' || 'AB'::char(5) || ']' AS concat,
       length('AB'::char(5))       AS len,
       octet_length('AB'::char(5)) AS bytes,
       'AB'::char(5) = 'AB'        AS eq,
       'AB'::char(5) LIKE 'AB'     AS like_ab,
       to_jsonb('AB'::char(5))     AS as_json;

Ответ: [AB] | 2 | 5 | t | f | "AB ". На диске лежат пять байт, но length насчитает два — хвостовые пробелы он не видит. Склейка через || приводит значение к text и пробелы отрезает, сравнение через = их тоже игнорирует. А LIKE не игнорирует: LIKE 'AB' вернёт false там, где = 'AB' вернул true. И в JSON значение уедет целиком — "AB ", пять символов вместо двух, которые приложение записывало.

Ещё одна несимметричность: приведение 'ABCDEFG'::char(5) молча обрежет строку до ABCDE, а вставка того же значения в колонку char(5) упадёт с ошибкой value too long. Колонка строже, чем приведение типа.

Когда char(n) уместен:

• char(2) — код страны по ISO 3166-1 (всегда ровно 2 буквы).
• char(3) — код валюты по ISO 4217 (всегда ровно 3 буквы).

Во всех остальных случаях — text или varchar(n).

Поиск без учёта регистра

Допустим, нужно хранить email и искать по нему без учёта регистра — IVAN@EXAMPLE.COM и ivan@example.com должны находить одну запись.

Само по себе PostgreSQL этого не сделает: text сравнивается посимвольно, и регистр для него значим.

живой пример

SELECT 'IVAN@example.com' = 'ivan@example.com'               AS as_is,
       lower('IVAN@example.com') = lower('ivan@example.com') AS after_lower;

Первое сравнение даёт false, второе — true. Но обычный индекс по email запросу с LOWER(email) = LOWER(...) не поможет: индекс построен по исходному значению, а сравнивается вычисленное, и получится сканирование всей таблицы.

Есть два рабочих подхода.

Подход 1: расширение citext

citext — это специальный тип PostgreSQL, который при сравнении автоматически игнорирует регистр:

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE account (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email citext NOT NULL UNIQUE
);

INSERT INTO account (email) VALUES ('ivan@example.com');
SELECT * FROM account WHERE email = 'IVAN@EXAMPLE.COM';  -- найдёт

Плюс: код чище, индекс работает автоматически. Минус: некоторые драйверы не умеют маппить citext и возвращают его как text — нужно проверять поведение конкретного драйвера.

Подход 2: text + функциональный индекс

Поле остаётся text, но индекс строится по результату lower():

CREATE TABLE account (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL
);

CREATE UNIQUE INDEX uk_account_email_lower ON account (lower(email));

Тогда запрос нужно писать явно:

SELECT * FROM account WHERE lower(email) = lower('A.VOLKOVA@EXAMPLE.COM');

Плюс: работает без расширений, портируемо. Минус: каждый запрос обязан использовать lower() — если забыть, индекс не задействуется.

Дальше точного равенства: ILIKE, триграммы, полнотекст

Приведение обеих сторон к нижнему регистру решает задачу «найти по точному значению без учёта регистра» и больше ничего. Как только нужен поиск по части строки, инструменты другие.

ILIKE — это LIKE без учёта регистра: WHERE title ILIKE '%кофе%'. Пишется коротко, работает всегда и не использует обычный индекс, если шаблон начинается с процента: B-дерево ищет по началу значения, а начало здесь неизвестно. На маленькой таблице это незаметно, на большой — полный перебор при каждом нажатии в поисковой строке.

Для подстроки заводят индекс по триграммам, трёхбуквенным кусочкам слова. Расширение pg_trgm плюс GIN-индекс (CREATE INDEX ... USING gin (title gin_trgm_ops)) отвечает и на LIKE '%кофе%', и на ILIKE, и на поиск с опечатками через похожесть. Это правильный инструмент для коротких полей: название, артикул, имя, адрес.

Для текста, где ищут по словам, берут полнотекстовый поиск: он приводит слова к основе, поэтому «кофемолка» находится по запросу «кофемолки», и умеет искать несколько слов сразу с ранжированием. Подробно, с весами и подсветкой, — в статье про полнотекстовый поиск.

Про citext: почему им не стоит обзаводиться сегодня

Расширение citext даёт колонку, которая сама сравнивается без учёта регистра, — удобно для почты и логина. Три оговорки, из-за которых новые схемы на нём стараются не строить.

Первая: это расширение, а не тип ядра, и на управляемых базах его может просто не быть — миграция встанет на CREATE EXTENSION. Вторая: с PostgreSQL 12 ту же задачу решают нечувствительные к регистру правила сравнения (ICU-коллации с deterministic = false), и они считаются основным путём — citext живёт ради обратной совместимости. Третья, и самая коварная: нечувствительность к регистру не равна нечувствительности к форме записи. Один и тот же символ в Unicode может быть записан по-разному (буква с диакритикой одним символом или базовой буквой плюс знаком), и для базы это разные строки, как их ни сравнивай. Поэтому UNIQUE по citext не гарантирует, что вторая регистрация «на ту же почту» не пройдёт: если нужна настоящая уникальность, строку приводят к одной форме (нормализуют) на входе в систему.

Кодировка UTF8

Кодировка принадлежит не серверу целиком, а каждой базе по отдельности — её задают при CREATE DATABASE. В одном кластере спокойно уживаются база в UTF8 и база в WIN1251. Посмотреть кодировку текущей базы:

живой пример

SHOW server_encoding;

Если она не UTF8 (например, SQL_ASCII или WIN1251), возникают проблемы с многоязычным контентом и эмодзи. Такое встречается на старых инсталляциях, созданных по устаревшим инструкциям.

Рядом живёт второй параметр, client_encoding — кодировка, в которой сервер разговаривает с конкретным соединением, перекодируя данные на лету. Кракозябры в приложении чаще приходят отсюда, а не из базы: сама база в порядке, а соединение представилось не той кодировкой. Посмотреть — SHOW client_encoding;.

UTF8 — единственная правильная кодировка для новой базы, и она же — разумное значение для соединения.

TOAST: длинные строки хранятся автоматически

Если в таблице есть поле с длинным текстом — статья, описание товара, биография — не нужно выносить его в отдельную таблицу руками.

PostgreSQL автоматически делает это сам через механизм TOAST: значения длиннее ~2 КБ физически хранятся отдельно от основной строки таблицы. При запросе, который не обращается к большому полю, PostgreSQL его не читает — и это ускоряет работу.

CREATE TABLE article (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug    text NOT NULL UNIQUE,
    title   text NOT NULL,
    body    text NOT NULL    -- длинный текст — TOAST справится сам
);

Разделять на article + article_body имеет смысл, только если измерения показывают реальное узкое место. Без измерений — доверьтесь TOAST.

Два следствия, о которых стоит знать, хотя TOAST и работает сам.

Первое: SELECT * тянет длинные значения всегда. Если в таблице есть колонка с описанием на несколько килобайт, звёздочка в запросе означает, что база сходит за этим описанием в отдельное хранилище и разожмёт его — даже если в интерфейсе показывается только название. На выдаче в тысячу строк это превращается в тысячу лишних походов. Перечисляйте колонки явно — на таблицах с длинным текстом это не стилистика, а разница во времени ответа.

Второе: по длинному тексту нельзя построить обычный индекс. Попытка заканчивается ошибкой index row size … exceeds btree version 4 maximum 2704 for index …: в страницу B-дерева запись просто не помещается. Выходов два. Индексировать не само значение, а его хеш или префикс: CREATE INDEX ... ON documents ((md5(body))) для поиска точного совпадения, CREATE INDEX ... ON documents (left(title, 100)) для поиска по началу. Или брать индекс, рассчитанный на такие данные, — GIN по триграммам или по полнотекстовому вектору, как в разделе про поиск выше.

Глубже: порядок строк и text_pattern_opsрасширенное

Индекс по текстовой колонке есть, WHERE email LIKE 'ivan%' его не берёт. Причина в правилах сравнения строк. Порядок «меньше-больше» для текста задаёт локаль базы, например ru_RU.UTF-8: в ней сравнение идёт по правилам языка, регистр и диакритика учитываются хитро, и «диапазон строк, начинающихся с ivan» не является непрерывным куском индекса. Поэтому планировщик обычный B-tree для LIKE с известным началом не использует.

CREATE INDEX ix_customer_email_pattern ON customer (email text_pattern_ops);

Класс операторов text_pattern_ops строит индекс с побайтовым сравнением, и LIKE 'ivan%', ~ '^ivan' начинают им пользоваться. Этот же индекс не годится для ORDER BY email по правилам языка, для сортировки нужен обычный; иногда держат оба. Второй способ, локаль C для колонки: COLLATE "C" сравнивает байты, и тогда обычный индекс работает с LIKE, а сортировка идёт по кодам символов, для русских строк это «все заглавные, потом все строчные».

Про сортировку русского текста: ORDER BY name даёт ожидаемый алфавит только при локали с русскими правилами; в базе с локалью C «Ярославль» окажется раньше «арбуза». Локаль выбирают при создании базы, менять потом дорого, и это стоит проверить в первый день: SHOW lc_collate. С PostgreSQL 15 доступна локаль ICU, und-x-icu, с одинаковыми правилами на любой ОС, и для новых баз её берут чаще.

Коротко

• text и varchar(n) в PostgreSQL хранятся одинаково и работают с одинаковой скоростью: varchar(255) — тот же text с проверкой длины перед каждой записью.
• varchar(255) «на всякий случай» — привычка из MySQL и Oracle; по умолчанию пишите text, а предел длины держите в коде приложения, где его легко поменять.
• Длину в колонке ставьте, когда её диктует стандарт: E.164, ISO 3166-1, ISO 4217.
• char(n) дополняет строку пробелами и хранит их: length, || и = их прячут, а LIKE и вывод в JSON — нет. Берите только для строго фиксированных кодов.
• Поиск без учёта регистра: citext или уникальный индекс по lower(field); LOWER(field) в WHERE без такого индекса — сканирование всей таблицы.
• Кодировка кластера — UTF8; длинные тексты TOAST выносит в отдельное хранилище сам, руками разносить их по таблицам не нужно.
• LIKE 'абв%' не берёт обычный индекс из-за правил локали: нужен индекс с text_pattern_ops или COLLATE "C"; порядок русских строк задаёт локаль базы, проверьте SHOW lc_collate.
• Поиск по подстроке ILIKE '%…%' обычный индекс не использует: для коротких полей берут GIN по триграммам (pg_trgm), для текста — полнотекстовый поиск.
• citext в новых схемах заменяют ICU-коллацией без учёта регистра, и уникальность по нему не спасает от разных форм записи одного символа.
• SELECT * всегда тянет длинные значения из TOAST, а обычный индекс по длинному тексту не строится — индексируют хеш, префикс или берут GIN.

Что почитать дальше

• Числа и точность — bigint, numeric, float.
• Время и таймзоны — timestamptz.
• UUID и идентификаторы — тип uuid, не char(36).
• JSONB — когда оправдан, когда нет — для структурированных данных.