Що ви опануєте
Навчитеся пояснювати аномалії зміни даних, обирати індекси за планом запиту, визначати межі транзакції та розділяти ролі доступу. Якість бази включає коректну модель, узгоджені зміни, достатню продуктивність і контроль прав. Ці властивості потрібно перевіряти окремими сценаріями.
Нормалізація та функціональні залежності
Функціональна залежність X → Y означає, що однаковому X відповідає одне значення Y у всіх допустимих станах. Наприклад, student_id визначає ім’я студента. Якщо у таблиці реєстрацій кожного разу повторювати ім’я, зміна імені потребуватиме оновлення багатьох рядків. Часткове оновлення створить суперечливі записи.
Аномалія вставлення виникає, коли студента неможливо записати без реєстрації. Аномалія видалення — коли остання реєстрація забирає і відомості про студента. Аномалія оновлення — коли повторений факт змінюється лише частково. Нормалізація організує залежності, щоб зменшити такі ризики.
Перша нормальна форма передбачає відсутність повторюваних груп і значення в межах визначеного домену. Друга усуває залежність неключових атрибутів лише від частини кандидатного складеного ключа. Третя, формально, вимагає для кожної нетривіальної залежності X → A, щоб X був суперключем або A — ключовим атрибутом. У звичайній навчальній моделі це допомагає розділити залежності student_id → name та seminar_id → title від факту членства пари.
Індекси та вартість запису
Індекс — додаткова структура доступу до рядків. B-tree підтримує багато запитів рівності, діапазону й сортування. Він займає місце та оновлюється при змінах. Корисність залежить від кількості рядків, вибірковості умови, потрібних полів і статистики. Вибірковість описує частку записів, які проходять фільтр.
CREATE SCHEMA lecture_quality;
SET search_path TO lecture_quality;
CREATE TABLE event (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
category integer NOT NULL,
occurred_at timestamptz NOT NULL
);
INSERT INTO event (category, occurred_at)
SELECT n % 1000, TIMESTAMPTZ '2027-01-01 00:00:00+00' + n * INTERVAL '1 minute'
FROM generate_series(1, 20000) AS g(n);
ANALYZE event;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM event WHERE category = 17;
CREATE INDEX event_category_idx ON event(category);
ANALYZE event;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM event WHERE category = 17;
Generate_series створює відтворюваний набір. ANALYZE оновлює статистику. EXPLAIN показує план; ANALYZE у його параметрах фактично виконує запит. Умові відповідають 20 рядків. Після індексу можливий Bitmap або Index Scan, але конкретний план і час залежать від середовища. Порівнюйте фактичні рядки, оцінки та буфери; один короткий замір не є універсальним доказом прискорення.
EXPLAIN ANALYZE для UPDATE або DELETE виконає зміну. У навчальній базі такий експеримент можна обгорнути BEGIN і ROLLBACK, враховуючи, що зовнішні побічні ефекти або значення sequences не завжди скасовуються так само. У виробничій базі потрібен погоджений процес вимірювання.
Транзакції та конкуренція
Транзакція — група операцій із завершенням COMMIT або скасуванням ROLLBACK. Атомарність означає застосування змін як єдиного цілого. Узгодженість спирається на обмеження та коректність операцій. Ізоляція визначає спостереження паралельних транзакцій. Довговічність забезпечує збереження підтвердженого результату відповідно до гарантій налаштувань СКБД.
Для обліку місць використаємо атомарне умовне оновлення:
CREATE TABLE seat_pool (
id integer PRIMARY KEY,
remaining integer NOT NULL CHECK (remaining >= 0)
);
INSERT INTO seat_pool VALUES (1, 1);
BEGIN;
UPDATE seat_pool SET remaining = remaining - 1
WHERE id = 1 AND remaining > 0 RETURNING remaining;
COMMIT;
Один рядок результату означає успішне зменшення, нуль — відсутність доступного місця. На стандартному READ COMMITTED PostgreSQL узгоджує конкуруючі оновлення одного рядка та повторно перевіряє умову після очікування. Якщо створення реєстрації відбувається окремою командою, його треба включити у ту саму транзакцію, а при невдачі скасувати обидві дії.
Альтернатива — SELECT … FOR UPDATE, який блокує прочитані рядки до завершення транзакції. Він корисний для складних послідовностей перевірок. Транзакції слід тримати короткими: очікування зовнішнього HTTP у межах блокування збільшує затримки. Для кількох ресурсів погоджений порядок блокування зменшує ризик взаємного очікування. Помилки deadlock та serialization можуть вимагати контрольованого повтору всієї операції.
Безпека та Supabase
Роль бази визначає дозволи. Застосунок зазвичай має лише права потрібного процесу; адміністратор міграцій — окрему роль. Пароль і серверний секрет зберігають у захищених змінних середовища. Параметризовані запити допомагають запобігати ін’єкції, але не замінюють перевірку повноважень користувача.
Supabase надає PostgreSQL та API доступу. Row Level Security, або RLS, перевіряє політики доступу для конкретних рядків. У таблицях, відкритих через Data API, потрібно ввімкнути RLS та визначити політики відповідно до ролей і власника. Без політики ввімкнений RLS зазвичай забороняє доступ звичайним ролям; власник таблиці й ролі з BYPASSRLS мають окремі правила.
Публічний клієнтський ключ використовується разом з авторизацією й RLS. Секретний ключ або service_role може обходити RLS і належить лише серверу. Його не можна вставляти у статичну сторінку, репозиторій або мобільний застосунок. SQL Editor часто працює з підвищеними правами, тому успішний SELECT там не підтверджує правильність політики для клієнта.
Практичний сценарій та перевірки
Для останнього місця запустіть два навчальні клієнти й виконайте умовне UPDATE. Сумарно має успішно змінитися один рядок. Для індексу збережіть плани до й після та повторіть кілька разів, пояснивши вплив кешу. Для RLS перевірте доступ двох різних користувачів і неавторизованого клієнта.
Типові помилки: індекс на кожному стовпці без вимірювання, перевірка залишку поза транзакцією, довгі блокування та тест доступу лише адміністратором. Професійна практика — документувати інваріанти, межі транзакцій, модель прав і відтворювані експерименти. Резервна копія має практичну цінність після перевірки відновлення у відокремленому середовищі.
Перевірка доступу та відновлення
Матриця доступу зіставляє ролі й операції: читання каталогу, редагування власного запису, підтвердження видачі та адміністрування схеми. Для кожної ролі задайте мінімальні потрібні права. Перевірка повинна включати дозволену дію та відмову для чужого рядка. Авторизація API й права PostgreSQL мають узгоджуватися.
RLS-політика може використовувати ідентифікатор автентифікованого користувача та owner_id рядка. Перед написанням визначте, хто може задавати owner_id при вставленні та чи дозволено його змінювати. Якщо клієнт здатний переписати власника довільно, навіть правильний SELECT-фільтр може не забезпечити очікувану модель. Для INSERT і UPDATE потрібні відповідні перевірки нового стану.
Тестуйте політики з реальним користувацьким токеном у навчальному середовищі. Підвищені права SQL Editor або серверний service_role можуть обійти RLS й приховати відсутню політику. Збережіть два тестових облікові записи та дані, що належать кожному. Публічні приклади не повинні містити справжні токени.
Окреме завдання експлуатації — відновлення. Резервна копія потрібна разом із перевіркою, що її можна відновити й отримати узгоджені таблиці та права. У відокремленій навчальній базі перевірте число рядків, зв’язки, контрольні запити та доступ. Це відрізняється від ROLLBACK поточної транзакції: резервування захищає від інших класів втрати. У документації зазначте ціль відновлення, потрібний час і відповідальність за перевірку.
Схема процесу
Перевіряємо залежності фактів.
Самоперевірка
Як обґрунтувати відповідь своїми словами?
Підсумок
Нормалізація зменшує аномалії, індекси підтримують конкретні запити, транзакції узгоджують зміни. Політики доступу потрібно перевіряти з реальною роллю клієнта. Кожне рішення потребує відтворюваної перевірки.