PostgreSQL for Mobile Apps: Backend, Indexes, Pagination

Solving PostgreSQL Performance Problems in Mobile Backends

Imagine a mobile app with 50,000 DAU: every product list view triggers 101 queries instead of one—time out. API response time is 5 seconds, users leave. We encountered this on a project for a large retailer. The solution was proper PostgreSQL optimization for mobile app backends: fixing N+1 queries, implementing correct PostgreSQL indexes, and using cursor-based pagination. Ultimately, response dropped from 5 seconds to 80 ms (a 98% reduction), and database load fell by 70%. Typical project cost ranges from $2,000 to $5,000 depending on complexity, with infrastructure savings up to $10,000 per year. For example, a retail client saved $8,000 per year after our optimization.

A mobile app should never connect to PostgreSQL directly—that's an anti-pattern because credentials are exposed in the code and it's vulnerable to SQL injections. We design and configure a complete backend: from database schema to deployment with connection pooling and real-time notifications. Over 5 years we've optimized PostgreSQL for 30+ mobile projects—ensuring API response under 100 ms even with 10,000 concurrent clients. We guarantee a 40% reduction in database costs within 2 months of deployment, backed by our team's PostgreSQL certifications.

How to Eliminate N+1 Queries in the API

GET /api/products — 100 products, each needing a category. Without eager loading, that's 101 queries instead of a single JOIN. The mobile client waits 2 seconds. The solution is explicit loading of related data. On Node.js with Prisma it looks like this:

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

Eager loading eliminates N+1 queries efficiently. This approach is 100 times faster than lazy loading on datasets with many relations.

Why Are Indexes Essential for Query Speed?

A SELECT on a non-indexed column over 1 million rows causes a sequential scan. In production, this leads to timeouts. We add indexes considering filters and sorting. Using CONCURRENTLY creates the index without locking the table—essential for production.

-- Indexes 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)); -- Pagination 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; 

Cursor Pagination for Faster API Response

Offset pagination (LIMIT 20 OFFSET 200) still reads 220 rows. Cursor-based pagination uses the (created_at, id) condition, which operates in O(log N). On the mobile client, infinite scroll via Paging 3 or UICollectionView DiffableDataSource receives the cursor from the API response. Cursor pagination is 10x faster than offset pagination on large datasets with 1 million rows.

Setting Up Real-Time Updates with LISTEN/NOTIFY

PostgreSQL LISTEN/NOTIFY + WebSocket/SSE on the backend → push to the mobile client. We use this for chats, notifications, live dashboards. The mobile client receives the event via WebSocket (Starscream, OkHttp) and updates the local cache (Room/SQLite).

Implementation details:

// Backend: subscribe to 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) }) 

Trigger on 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; 

Read more about LISTEN/NOTIFY in the official documentation.

Connection Pooling with PgBouncer for Mobile Apps

Mobile apps create many short-lived connections. Without a pool, each request opens a new connection. PgBouncer in transaction mode maintains a fixed pool and hands out a connection only for the duration of a transaction. Typical architecture: Mobile clients → API servers (N instances) → PgBouncer (25 connections) → PostgreSQL. We have handled 10,000 concurrent mobile clients with only 25 database connections via PgBouncer. PgBouncer reduces database connections by 98% compared to direct connections.

Step-by-Step PostgreSQL Setup for a Mobile App

1. Design the database schema based on typical client queries.
2. Create indexes and materialized views for frequent filters.
3. Implement cursor-based pagination and eager loading.
4. Configure PgBouncer and set up pool settings for the ORM.
5. Deploy LISTEN/NOTIFY for real-time features.
6. Document endpoints and schemas.

Each step includes configuration examples and best practices. Effective PostgreSQL optimization ensures fast mobile app backend performance.

Example PgBouncer configuration:

[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 

What's Included in the Work

• Analysis of current schema and load.
• Designing an optimal schema with indexes.
• Implementing API with eager loading and cursor pagination.
• Configuring PgBouncer and pool settings.
• Integrating real-time notifications via LISTEN/NOTIFY.
• Documenting all endpoints and schemas.
• Handover of credentials and team training.
• One month of support after deployment.

Process

Analysis → Design → Implementation → Testing → Deployment. We provide incremental results and documentation. Timeline: 1 to 2 weeks depending on complexity. Cost is calculated individually. For a retail app with 200,000 products and 50 categories, we designed indexes that reduced query time from 2.3 seconds to 12 ms. Request a consultation—we'll evaluate your project.

Common PostgreSQL Configuration Mistakes for Mobile Apps

• Not using projections: returning SELECT * instead of only required fields inflates response size tenfold.
• No slow query monitoring: without pg_stat_statements you won't spot slow JOINs.
• Ignoring caching: frequent identical queries without Redis or local cache create unnecessary database load.

Our experience of over 5 years and 30+ projects ensures you avoid these errors. Infrastructure savings can reach 40%, and ROI within 1-2 months. Contact us for details—we'll prepare a custom proposal. Reach out for a consultation.