PostgreSQL Setup for Mobile App

Setting Up PostgreSQL for Mobile App

Mobile app doesn't connect to PostgreSQL directly. Direct database connection from client — antipattern: database credentials in app code, missing auth layer, SQL injection vulnerability through client code. PostgreSQL in mobile context — is the backend, which app accesses via API. Task here — setup stack properly, ensure type-safe queries, avoid N+1 problems.

Interaction Architecture

Mobile App → REST/GraphQL API → Backend (Node/Go/Laravel/etc.) → PostgreSQL

Backend — mandatory layer. ORM choice and driver on server side indirectly but noticeably affect mobile app performance: bad queries = slow responses = poor UX.

Typical Backend Problems Affecting Mobile Client

N+1 queries. GET /api/products returns 100 products. Each product needs category — 100 separate SELECTs. Total 101 queries instead of one JOIN. Mobile client waits 2 seconds instead of 100 ms.

On Node.js with Prisma — explicit eager loading via include:

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
})

Missing indexes. SELECT on non-indexed field on 1M row table — sequential scan. On small data invisible, in production — timeout.

-- Indexes for typical mobile queries
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);

-- For full-text search
CREATE INDEX idx_products_search
    ON products USING gin(to_tsvector('russian', title || ' ' || description));

CONCURRENTLY — index created without table lock. In production this is mandatory.

Heavy responses. API returns complete objects with all fields. Mobile client gets 50 KB JSON where 5 KB needed. Explicit SELECT projections instead of SELECT *:

// Only needed fields for list
const productsListDto = await prisma.product.findMany({
    select: {
        id: true,
        title: true,
        priceCents: true,
        thumbnailUrl: true,
        averageRating: true,
        reviewsCount: true
        // NOT: description (long text), rawData, adminNotes
    }
})

Pagination

Offset pagination (LIMIT 20 OFFSET 200) degrades on large volumes — PostgreSQL still reads 220 rows. Cursor-based pagination faster:

-- Cursor pagination by (created_at, id)
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;

On mobile client — infinite scroll via Paging 3 (Android) or UICollectionView DiffableDataSource (iOS), receiving cursor from previous response.

Real-time Updates

PostgreSQL LISTEN/NOTIFY + WebSocket/SSE on backend → push updates to mobile. For chats, live notifications, real-time dashboards.

// Backend: subscribe to PostgreSQL NOTIFY
const client = await pool.connect()
await client.query('LISTEN product_updates')
client.on('notification', (msg) => {
    const payload = JSON.parse(msg.payload)
    // Broadcast via WebSocket to subscribed clients
    broadcastToSubscribers(payload.categoryId, payload)
})

// PostgreSQL trigger
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;

On mobile client — WebSocket via Starscream (iOS) or OkHttp WebSocket (Android), updates local Room/SQLite cache on event.

Connection Pooling

Mobile apps make many short requests. Without pooling, each request creates new PostgreSQL connection — expensive. PgBouncer before PostgreSQL or built-in pool in ORM (Prisma $connectionPoolSize, Sequelize pool.max):

Mobile clients → API servers (N instances) → PgBouncer → PostgreSQL
                                             transaction mode, 25 connections per server

In transaction mode PgBouncer connection occupied only during transaction — dozens of API servers work through small pool of real connections.

Setting up backend with PostgreSQL, indexes, pagination and pooling for mobile API: 1–2 weeks. Cost calculated individually.