Junior: питання на співбесіді з теми «Типи даних»

У PostgreSQL text, varchar і varchar(n) зберігаються однаково й працюють з однаковою швидкістю. Різниця лише в тому, що varchar(n) додає перевірку довжини.

• text - рядок довільної довжини.
• varchar(n) - рядок не довший за n символів (не байтів). Довший - помилка.
• varchar без довжини - те саме, що text.
• char(n) - доповнюється пробілами до n. Майже ніколи не потрібен: займає не менше місця і має дивну семантику порівнянь.

Звідки звичка до varchar(255): у MySQL та інших СУБД довжина впливає на зберігання, індекси й тимчасові таблиці. У PostgreSQL такої причини немає.

Як обирати:

• text - для більшості рядків. Обмеження довжини - якщо воно справді є правилом домену.
• Бізнес-обмеження - через CHECK, його легше змінити:

title text NOT NULL CHECK (char_length(title) <= 200)

Збільшити ліміт varchar(n) у сучасних версіях можна без переписування таблиці, але зменшення вимагає повної перевірки даних.

Що варто знати:

• Довгі значення (понад ~2 КБ) автоматично стискаються й виносяться в TOAST - таблиця не роздувається.
• Laravel $table->string('name') створює varchar(255) - на PostgreSQL це лише обмеження, а не оптимізація. Для довгих текстів - $table->text().
• Валідація довжини в застосунку все одно потрібна, щоб користувач отримав зрозуміле повідомлення, а не помилку бази.

Докладніше в документації: Символьні типи

Обидва способи створюють автоінкрементний цілочисельний ключ на основі послідовності (sequence).

serial / bigserial - старий спосіб, по суті скорочення:

id bigserial PRIMARY KEY
-- розгортається в:
-- id bigint NOT NULL DEFAULT nextval('orders_id_seq')
-- + окрема послідовність, «прив'язана» до колонки

Identity (PostgreSQL 10+, стандарт SQL):

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- або
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

Чому identity краще:

• Стандарт SQL - переноситься між СУБД.
• Послідовність - частина колонки: права, видалення таблиці, копіювання структури працюють коректно. З serial треба окремо давати права на послідовність, і CREATE TABLE ... (LIKE ...) може поділити послідовність між таблицями.
• GENERATED ALWAYS забороняє вставляти власні значення (можна лише явно з OVERRIDING SYSTEM VALUE). Захищає від ручних ID, що потім конфліктують з послідовністю.
• Керування через ALTER TABLE ... ALTER COLUMN id RESTART WITH 1000.

Спільні нюанси:

• Дірки в номерах - норма. Значення з послідовності береться до коміту й не повертається при відкаті. ID 5, 6, 8 - не баг. Для «безперервних» номерів рахунків послідовність не підходить.
• bigint, а не int: 2,1 мільярда закінчуються несподівано швидко в таблицях подій і логів, а зміна типу первинного ключа на великій таблиці - болюча операція.
• Після ручного імпорту даних з явними ID послідовність треба зсунути: SELECT setval('orders_id_seq', (SELECT max(id) FROM orders));.

Laravel $table->id() на PostgreSQL створює bigserial-ключ - для більшості застосунків різниця непомітна.

Докладніше в документації: Identity-колонки

PostgreSQL дозволяє зберігати в колонці масив значень будь-якого типу: text[], int[], uuid[].

CREATE TABLE posts (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    tags text[] NOT NULL DEFAULT '{}'
);

INSERT INTO posts (title, tags) VALUES ('Черги в Laravel', ARRAY['laravel', 'queues']);

SELECT * FROM posts WHERE 'queues' = ANY(tags);       -- містить елемент
SELECT * FROM posts WHERE tags @> ARRAY['laravel'];   -- містить усі
SELECT * FROM posts WHERE tags && ARRAY['vue', 'react'];  -- є хоча б один спільний
SELECT unnest(tags) AS tag, count(*) FROM posts GROUP BY tag;   -- розгорнути в рядки

Для швидкого пошуку - GIN-індекс: CREATE INDEX posts_tags_idx ON posts USING gin (tags); (працює з @>, &&, але не з = ANY).

Індексація з одиниці: tags[1] - перший елемент.

Коли масив доречний:

• Невеликий список простих значень, що читається й замінюється разом із рядком: теги, ролі, налаштування-прапорці.
• Значенням не потрібні власні атрибути й посилання на інші таблиці.

Коли краще окрема таблиця:

• Елементи посилаються на інші сутності - масив ID не має зовнішніх ключів, і видалення пов'язаного запису не прибере його з масиву.
• Потрібні атрибути зв'язку (хто й коли додав тег).
• Потрібно часто змінювати окремі елементи чи масив великий.
• Потрібні звичайні JOIN і агрегати - на масивах вони можливі, але громіздкіші.

Пастки: NULL і порожній масив '{}' - різні речі; = ANY(NULL) дає NULL; багатовимірні масиви мусять бути «прямокутними». Laravel не має типу масиву в схемі - колонку додають через $table->addColumn(...) чи сирий SQL, а читають з кастом.

Докладніше в документації: Масиви