PostgreSQL для мобільного застосунку: бекенд, індекси, пагінація

Які проблеми вирішує налаштування PostgreSQL?

Уявіть: мобільний застосунок з 50 000 DAU, кожне відкриття списку товарів генерує 101 запит до БД замість одного — time out. Час відгуку API — 5 секунд, користувачі йдуть. Ми стикалися з таким на проєкті для великого рітейлера. Рішення — грамотне налаштування PostgreSQL з правильними індексами та пагінацією. У підсумку відгук знизився до 80 мс, а навантаження на базу впало на 70%. Мобільний застосунок не підключається до PostgreSQL напряму: це антипатерн через облікові дані в коді та вразливість до SQL-ін'єкцій. Ми проєктуємо та налаштовуємо повноцінний бекенд: від схеми бази до деплою з пулінгом та real-time сповіщеннями. За 5 років оптимізували PostgreSQL для 30+ мобільних проєктів — забезпечуємо відгук API під 100 мс навіть при 10 000 одночасно підключених клієнтів.

Як усунути N+1 запити в API?

GET /api/products — 100 продуктів, кожен потребує категорію. Без eager loading — 101 запит замість одного JOIN. Мобільний клієнт чекає 2 секунди. Рішення — явне завантаження пов'язаних даних. На Node.js з Prisma це виглядає так:

const products = await prisma.product.findMany({
  where: { categoryId, isActive: true },
  include: {
    category: { select: { id: true, name: true, slug: true } },
    images: { take: 1, orderBy: { sortOrder: 'asc' } },
    _count: { select: { reviews: true } }
  },
  orderBy: { createdAt: 'desc' },
  take: 20,
  skip: offset
})

Чому повільні запити без індексів?

SELECT по неіндексованому полю на 1 млн рядків — sequential scan. У production це призводить до timeout. Додаємо індекси з урахуванням фільтрів та сортування. CONCURRENTLY — без блокування таблиці, обов'язково для production:

-- Індекси
CREATE INDEX CONCURRENTLY idx_products_category_active ON products(category_id, is_active) WHERE is_active = true;
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders(user_id, created_at DESC);
CREATE INDEX idx_products_search ON products USING gin(to_tsvector('russian', title || ' ' || description));
-- Пагінація
SELECT * FROM products WHERE (created_at, id) < ($last_created_at, $last_id) AND category_id = $category_id AND is_active = true ORDER BY created_at DESC, id DESC LIMIT 20;

Чому пагінація на курсорі швидша?

Offset pagination (LIMIT 20 OFFSET 200) все одно читає 220 рядків. Cursor-based пагінація використовує умову (created_at, id), що працює за O(log N). На мобільному клієнті — нескінченний скрол через Paging 3 або UICollectionView DiffableDataSource, який отримує cursor з відповіді API.

Як налаштувати real-time оновлення?

PostgreSQL LISTEN/NOTIFY + WebSocket/SSE на бекенді → push на мобільний клієнт. Використовуємо для чатів, сповіщень, живих дашбордів. Мобільний клієнт отримує подію через WebSocket (Starscream, OkHttp) та оновлює локальний кеш (Room/SQLite).

Деталі реалізації:

// Бекенд: підписка на NOTIFY
const client = await pool.connect()
await client.query('LISTEN product_updates')
client.on('notification', (msg) => {
  const payload = JSON.parse(msg.payload)
  broadcastToSubscribers(payload.categoryId, payload)
})

Тригер на PostgreSQL:

CREATE FUNCTION notify_product_change() RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('product_updates', json_build_object('id', NEW.id, 'categoryId', NEW.category_id)::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Більш детально про команду LISTEN/NOTIFY читайте в офіційній документації.

Connection pooling: навіщо потрібен PgBouncer

Мобільні застосунки створюють багато коротких з'єднань. Без пулу кожен запит = нове підключення. PgBouncer у транзакційному режимі тримає фіксований пул і видає з'єднання тільки на час транзакції. Типова схема: Mobile clients → API servers (N екземплярів) → PgBouncer (25 connections) → PostgreSQL.

Покрокове налаштування PostgreSQL для мобільного застосунку

1. Проєктування схеми БД з урахуванням типових запитів клієнта.
2. Створення індексів та матеріалізованих представлень для частих фільтрів.
3. Реалізація cursor-based пагінації та eager loading.
4. Налаштування PgBouncer та конфігурація пулу для ORM.
5. Деплой LISTEN/NOTIFY для real-time функцій.
6. Документація по ендпоінтах та схемах.

Приклад конфігурації PgBouncer:

[databases]
* = host=localhost port=5432 auth_user=admin

[pgbouncer]
listen_addr = 0.0.0.0:6432
pool_mode = transaction
default_pool_size = 25
max_client_conn = 200

Що входить в роботу

• Аналіз поточної схеми та навантаження.
• Проєктування оптимальної схеми з індексами.
• Реалізація API з eager loading та cursor-пагінацією.
• Налаштування PgBouncer та конфігурація пулу.
• Впровадження real-time сповіщень через LISTEN/NOTIFY.
• Документація всіх ендпоінтів та схем.
• Передача доступів та навчання команди.
• Підтримка протягом місяця після деплою.

Процес роботи

Аналітика → Проєктування → Реалізація → Тест → Деплой. Ми надаємо проміжні результати та документацію. Строки: від 1 до 2 тижнів залежно від складності. Вартість розраховується індивідуально. Замовте консультацію — ми оцінимо ваш проєкт.

Типові помилки при налаштуванні PostgreSQL для мобільного застосунку

• Невикористання проекцій: повернення SELECT * замість тільки потрібних полів — збільшує розмір відповіді в 10 разів.
• Відсутність моніторингу повільних запитів: без pg_stat_statements ви не побачите гальмівні JOIN.
• Ігнорування кешування: часті запити до одних даних без Redis або локального кешу створюють надмірне навантаження на БД.

Наш досвід більше 5 років та 30+ проєктів гарантує, що ви не припуститеся цих помилок. Економія на інфраструктурі може скласти до 40%, а окупність інвестицій — 1–2 місяці. Звертайтеся за деталями — ми підготуємо індивідуальну пропозицію. Зв'яжіться з нами для консультації.