Most query optimisation guides assume fibre-fast infrastructure. Here's how to tune PostgreSQL for the latency, bandwidth, and cost realities of building SaaS in Africa.

Optimising PostgreSQL Query Performance for African SaaS Apps

A query that returns in 12ms on a Frankfurt server can take 340ms when your users are in Accra, Lagos, or Nairobi — and that gap is not just a network problem. It is a design problem. Most PostgreSQL optimisation guides are written for engineers who can assume sub-millisecond internal network hops, always-on gigabit connections, and cloud regions sitting next door to their users. African SaaS teams do not have that luxury. What they do have is PostgreSQL, a world-class database engine, and the need to squeeze every last millisecond out of it deliberately.

This article is a practical reframe of indexing, connection pooling, and query planning for apps running on African cloud infrastructure — or on global cloud with African user bases.

Why African Infrastructure Context Changes Everything

Cloud region availability on the continent has improved significantly. AWS Cape Town (af-south-1), Azure South Africa North, and Google Cloud's Johannesburg region are solid options. But several realities remain constant:

Cross-region traffic is expensive. Many teams still run databases in European or American regions to avoid data egress fees or because their vendor has no African presence.
Mobile-first users on variable connections mean your app backend absorbs more retry logic, longer session holds, and bursty traffic patterns.
Smaller engineering teams have less time for ongoing DBA work, so optimisations must be durable and low-maintenance.

These constraints make query efficiency not just a performance concern — it is a cost and reliability concern.

Indexing: Stop Guessing, Start Reading EXPLAIN

The most common mistake on constrained infrastructure is over-indexing based on instinct rather than evidence. Every index you add is a write-time tax. On a server with limited IOPS — common on entry-level cloud VMs — this tax compounds quickly.

The correct workflow is ruthlessly diagnostic:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = $1 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

Look at three things in the output: Seq Scan vs Index Scan, actual rows vs estimated rows, and Buffers: hit vs read. A high Buffers: read count on a slow disk means your working set is not fitting in shared_buffers — a configuration problem, not an indexing problem.

For African SaaS apps with multi-tenant data models (where customer_id or org_id is almost always in the WHERE clause), partial indexes and composite indexes are the highest-leverage tools:

-- Composite index for a common filtered + sorted query
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

-- Partial index for only rows that matter at query time
CREATE INDEX idx_pending_orders
ON orders (customer_id, created_at DESC)
WHERE status = 'pending';

Partial indexes are especially powerful when a small fraction of rows drive the majority of your live queries — which is almost always true in transactional SaaS apps.

Connection Pooling: The Silent Killer of Performance at Scale

PostgreSQL was not designed to handle thousands of direct connections. Each connection consumes roughly 5–10 MB of RAM and a backend process. On a 4 GB cloud instance — a realistic budget tier for an early-stage African SaaS — 200 direct connections will bring the database to its knees before your query optimisation work matters at all.

PgBouncer is the standard solution and should be non-negotiable in your stack. Run it in transaction pooling mode for APIs and background workers:

Set pool_size to roughly 10–25 per application server, depending on your workload.
Keep max_client_conn well above your app's peak connection demand so clients queue rather than fail.
Disable statement_timeout at the PgBouncer level; enforce it at the application level instead.

One nuance specific to mobile-heavy African apps: connection spikes from retry storms are more common due to intermittent connectivity. Configure your application's connection pool (max_idle, connection_timeout) conservatively. Failing fast on the application side is better than holding stale connections open at the database.

Query Planning: Give the Planner What It Needs

PostgreSQL's query planner is excellent — but only if its statistics are accurate. On databases that grow quickly (as SaaS apps tend to during traction phases), the autovacuum and autoanalyze defaults are often too conservative.

Tune these for faster-moving tables:

ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_scale_factor = 0.02
);

This tells Postgres to run ANALYZE after 1% of the table changes, not the default 20%. On a 500,000-row orders table, that means the planner gets fresh statistics after 5,000 row changes instead of 100,000.

Also watch for N+1 query patterns — these are disproportionately damaging when each round trip carries real latency. A well-optimised ORM that issues 1 query instead of 51 is worth more than any index on a 80ms-latency connection.

Caching Strategy: Reduce What You Ask the Database to Do

Before any query reaches Postgres, ask whether it needs to. For African SaaS apps where server costs are a genuine concern, a lightweight caching layer is not premature optimisation — it is budget management.

Redis (or DragonflyDB for lower memory overhead) in front of expensive aggregate queries — dashboards, reports, leaderboard counts — can reduce database CPU load by 60–80% on read-heavy workloads. Cache at the application layer with short TTLs (30–120 seconds) for near-real-time data, and longer TTLs for historical reports.

Combine this with materialised views in Postgres for complex aggregations that run on a schedule:

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_summary;

The CONCURRENTLY option avoids locking the view during refresh — critical for apps that cannot afford downtime.

Putting It Together: A Performance Audit Checklist

Before pushing a new query-heavy feature, run through this list:

Run EXPLAIN (ANALYZE, BUFFERS) on every query touching tables above 10,000 rows.
Verify PgBouncer is in front of all application database connections.
Check pg_stat_user_tables for tables with high n_live_tup and low last_autoanalyze frequency.
Profile N+1 patterns using query logging (log_min_duration_statement = 100) or an APM tool.
Review indexes monthly using pg_stat_user_indexes — drop any with zero scans.

Why This Matters for Your Project

If you are building or scaling a SaaS product on the African market, database performance is not a backend nicety — it is a direct driver of user retention and infrastructure cost. A well-tuned PostgreSQL setup running on a modest cloud instance can comfortably serve tens of thousands of users. The gap between a tuned and an untuned database on constrained infrastructure is not 10% — it is often an order of magnitude. Build the discipline into your engineering culture early, and it compounds just as reliably as your user growth.