Junior: питання на співбесіді з теми «Функції, тригери й розширення»

Представлення (view) - збережений запит з іменем. Його можна читати як таблицю, але дані не зберігаються: при кожному зверненні PostgreSQL виконує запит, що лежить в основі.

CREATE VIEW active_customers AS
SELECT u.id, u.email, count(o.id) AS orders_count, max(o.created_at) AS last_order_at
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.email;

SELECT * FROM active_customers WHERE orders_count > 5;

Планувальник підставляє запит представлення в зовнішній запит і оптимізує їх разом - умова orders_count > 5 не означає, що спершу рахуються всі клієнти.

Навіщо представлення:

• повторно використовувати складний запит - один JOIN з агрегатами замість копій у кількох місцях;
• стабільний інтерфейс для звітів, BI-інструментів, сторонніх систем: таблиці можна змінювати, а представлення лишити сумісним;
• обмеження доступу: надати роль лише на представлення без чутливих колонок, а не на всю таблицю.

Звичайне проти матеріалізованого:

Оновлювані представлення: просте представлення з однієї таблиці без агрегатів PostgreSQL дозволяє оновлювати через INSERT/UPDATE/DELETE. WITH CHECK OPTION забороняє вставку рядків, які не пройдуть умову WHERE представлення.

У Laravel представлення створюють у міграції через DB::statement('CREATE VIEW ...'), а читають звичайною моделлю:

class ActiveCustomer extends Model
{
    protected $table = 'active_customers';
}

Підводні камені:

• зміна таблиць: видалити колонку, яку використовує представлення, не можна без CASCADE - а CASCADE видалить і представлення. Міграції мають перестворювати представлення;
• SELECT * у визначенні фіксує список колонок на момент створення - нові колонки таблиці в представленні не з'являться;
• вкладені представлення (представлення над представленням над представленням) ховають складність і погіршують плани запитів.

Докладніше в документації: CREATE VIEW

Розширення додають у базу нові типи, функції, оператори й індекси без зміни самого PostgreSQL. Встановлюються командою в конкретній базі:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT extname, extversion FROM pg_extension;    -- що вже встановлено
SELECT name, default_version FROM pg_available_extensions;   -- що доступно на сервері

Корисні розширення:

pg_stat_statements - особливий: його потрібно завантажити при старті сервера:

shared_preload_libraries = 'pg_stat_statements'

SELECT calls, round(mean_exec_time::numeric, 1) AS avg_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

У Laravel розширення вмикають у міграції:

public function up(): void
{
    DB::statement('CREATE EXTENSION IF NOT EXISTS pg_trgm');
}

Що врахувати:

• права: створювати більшість розширень може лише суперкористувач чи власник бази; користувач застосунку з мінімальними правами міграцію не виконає - розширення встановлює адміністратор;
• керовані бази (RDS, Cloud SQL, Neon, Supabase) дозволяють лише розширення зі свого списку - перевірте до того, як будувати на ньому архітектуру;
• тестова база теж має мати розширення, інакше міграції в CI впадуть;
• оновлення PostgreSQL: розширення мають бути доступні й у новій версії, а ALTER EXTENSION ... UPDATE - частина плану оновлення.

Докладніше в документації: CREATE EXTENSION