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 місяці. Звертайтеся за деталями — ми підготуємо індивідуальну пропозицію. Зв'яжіться з нами для консультації.

Як вибрати рішення для локального зберігання даних (Room, Core Data, Realm, Isar)?

Ми стикалися з ситуацією, коли додаток втрачає дані при втраті мережі — і це не просто баг, це провал сценарію. Користувач заповнив форму, натиснув «Відправити», отримав таймаут і втратив все. Або гірше: дані відправилися двічі через некоректну логіку повторної відправки. Правильно вибраний та налаштований шар сховища вирішує цю проблему раз і назавжди. Неправильний вибір може коштувати команді місяців переписування коду та втрати до 70% часу на синхронізацію. Наш досвід — 10+ років у мобільній розробці, понад 50 проектів з офлайн-сховищами — підтверджує: вибір рішення визначає 80% майбутніх проблем з продуктивністю та синхронізацією.

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

Room (Android) — обгортка над SQLite з compile-time верифікацією SQL-запитів. Якщо запит невалідний, збірка падає — це краще, ніж SQLiteException в рантаймі. Room добре інтегрується з Kotlin Flow та LiveData, що робить реактивні UI-оновлення прямолінійними. Основна складність — міграції схеми. @Database(version = N, exportSchema = true) з файлами міграцій в assets/databases/ — обов'язкова практика, інакше при оновленні додатка fallbackToDestructiveMigration() просто зітре дані користувача.

Core Data (iOS) — не база даних, а фреймворк управління графом об'єктів поверх SQLite (або XML, або in-memory). NSPersistentContainer з viewContext для читання на main thread та newBackgroundContext() для запису — базова схема. Проблема починається, коли розробник робить save() в viewContext з фонового потоку: EXC_BAD_ACCESS в рандомний момент, відтворюється раз на тиждень, в крешлозі майже нічого корисного. Потрібно використовувати performAndWait або perform для кожного контексту строго в своєму потоці. Apple Core Data Programming Guide рекомендує саме такий підхід.

Realm виграє там, де потрібна швидкість роботи з великими наборами об'єктів та вбудована реактивність через Results + observe(). Realm зберігає об'єкти напряму, без маппінгу ORM, тому читання не потребує десеріалізації. За нашими вимірами, Realm обробляє читання в 2–3 рази швидше Core Data при об'ємі понад 10 000 об'єктів. На Flutter Realm SDK (ex-MongoDB Realm) підтримує Device Sync — але це вже managed-сервіс з окремою інфраструктурою.

Hive та Isar — Flutter-специфічні рішення. Hive — key-value сховище, швидко, просто, підходить для налаштувань та кешів. Isar — повноцінна документо-орієнтована БД з індексами, написана на Rust, компілюється в нативний код. Для Flutter-додатків з офлайн-функціональністю Isar зараз переважніше: вбудований query builder з типобезпечними фільтрами, транзакції, watchObject/watchQuery для реактивності.

Зв'яжіться з нами, щоб отримати безкоштовний аудит вашого поточного сховища та рекомендації з оптимізації — це зекономить вам сотні годин розробки та до 60% трафіку на серверні запити.

Чому офлайн-синхронізація — найскладніша частина?

Локальне сховище саме по собі нескладне. Складність — у синхронізації з сервером при наявності конфліктів.

Найчастіший патерн — optimistic updates з rollback. Користувач редагує запис, UI відображає зміну миттєво, фоновий запит йде на сервер. Якщо сервер повертає помилку — відкочуємо локальний стейт. Виглядає просто. На практиці: якщо користувач встиг піти з екрану і повернутися, а відкат відбувся через 3 секунди — UX зламаний. Потрібна явна черга операцій зі станом (PENDING, SYNCED, FAILED) в окремій таблиці.

На Android для фонової синхронізації використовуємо WorkManager з Constraints.Builder().setRequiredNetworkType(NetworkType.CONNECTED). Важно не забыть про setInputMerger(ArrayCreatingInputMerger::class) при батчинге задач — інакше при кількох одночасних запусках дані затираються. Типова реалізація черги операцій:

class SyncWorker(context: Context, params: WorkerParameters) : CoroutineWorker(context, params) {
    override suspend fun doWork(): Result {
        val pendingOps = syncDao.getPendingOperations()
        for (op in pendingOps) {
            try {
                apiClient.send(op.payload)
                syncDao.markSynced(op.id)
            } catch (e: Exception) {
                syncDao.markFailed(op.id, e.message)
                return Result.retry()
            }
        }
        return Result.success()
    }
}

На iOS аналог — BGTaskScheduler з BGProcessingTaskRequest. Обмеження iOS на фоновий час виконання (~30 секунд для refresh tasks) означають, що синхронізація повинна бути інкрементальною: не «синхронізувати все», а «синхронізувати наступні N записів, зберегти курсор».

Конфлікти при мультипристроєвій роботі вирішуються одним із трьох підходів:

• Last-write-wins по updated_at (найпростіший, втрачає дані при одночасному редагуванні)
• Server-wins (клієнт завжди приймає серверну версію)
• Three-way merge (складно, потрібен спільний предок — підходить для документів)

У більшості B2C-додатків достатньо last-write-wins з вектором часу на рівні користувача, але при спільному редагуванні потрібен CRDTs-підхід — тоді дивимося на Automerge або Yjs з мобільними біндингами.

Як ми будуємо шар сховища

Репозиторний патерн — не опціональний, а обов'язковий. UserRepository не знає, звідки дані: з Room, Realm чи мережі. ViewModel викликає repository.getUser(id), отримує Flow/Stream, відображає дані. Логіка кешування — всередині репозиторія.

Для Flutter типова архітектура: Isar для персистентності, Riverpod для управління стейтом, ConnectivityPlus для визначення стану мережі, кастомний SyncService з чергою операцій. Riverpod AsyncNotifier зручно покриває логіку «показати кеш, оновити з мережі, показати нові дані». Приклад репозиторія з кешуванням:

class UserRepository {
  final Isar isar;
  final ApiClient api;

  Future<User> getUser(String id) async {
    // 1. спробувати з локального сховища
    final cached = await isar.user.where().idEqualTo(id).findFirst();
    if (cached != null) return cached;
    // 2. інакше з мережі
    final remote = await api.fetchUser(id);
    // 3. зберегти локально
    await isar.writeTxn(() => isar.user.put(remote));
    return remote;
  }
}

Окрема тема — шифрування. Якщо додаток зберігає медичні дані, платіжні картки або корпоративні документи, SQLCipher (Android) та NSFileProtection (iOS) — не опція. Realm підтримує шифрування нативно через ключ у 64 байти, який потрібно зберігати в Keychain/Keystore, а не в SharedPreferences. Економія на безпеці може обійтися у витік даних з гучними наслідками.

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

Ми гарантуємо прозорий процес і фіксуємо кожен етап:

Бажаєте уникнути типових помилок при проектуванні сховища? Зверніться до нас — ми допоможемо спроектувати надійне локальне сховище з нуля або доопрацювати існуюче.

Які етапи роботи?

Починаємо з аудиту вимог: які дані, який обсяг, чи потрібна синхронізація, чи можливі конфлікти. На цьому етапі стає зрозуміло, Core Data чи SQLite-based рішення, чи потрібен Realm Sync або достатньо простого REST-поллінгу.

Далі — проектування схеми з урахуванням міграцій. Схему змінюють у будь-якому проекті — питання не «чи будуть міграції», а «наскільки болісно вони пройдуть». Експортуємо схему в JSON, зберігаємо в репозиторії, пишемо тести на міграцію кожної версії.

Розробка йде з покриттям репозиторного шару юніт-тестами: моки мережевого шару, реальна in-memory база для тестування запитів. Перед релізом — профілювання запитів через Android Profiler (вкладка Database Inspector) або Core Data debug флаги (-com.apple.CoreData.SQLDebug 1).

Термін реалізації шару сховища з базовою офлайн-синхронізацією — від 2 до 6 тижнів залежно від складності схеми та вимог до конфлікт-резолюції. Вартість розраховується індивідуально після аудиту вашого проекту. Замовте розробку під ключ — отримайте консультацію з вибору оптимального стеку та міграціям.