PostgreSQL : 12 optimisations qui ont divisé le temps de réponse par 10

Le problème : API qui timeout

Scénario réel : API REST Next.js pour une app fintech suisse

Symptômes

• Requêtes /transactions : 8-12 secondes
• Timeouts réguliers en production
• Serveurs surchargés (CPU 85%)

Après optimisations

• Requêtes : 400-600ms (-94%)
• 0 timeout
• CPU serveurs : 15%

Voici les 12 optimisations appliquées.

Optimisation #1 : Index composites

Problème : table transactions (2.4M rows)

-- Requête lente (12s)
SELECT * FROM transactions
WHERE user_id = 123
  AND status = 'completed'
  AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN ANALYZE

Seq Scan on transactions  (cost=0.00..125432.50 rows=142 width=356) (actual time=12245.234..12245.236)
  Filter: (user_id = 123 AND status = 'completed' AND created_at > '2024-01-01')
  Rows Removed by Filter: 2400000

Problème : Seq Scan (scan complet de la table)

Solution : index composite

-- Créer l'index dans le bon ordre
CREATE INDEX idx_transactions_user_status_date 
ON transactions (user_id, status, created_at DESC);

Ordre des colonnes : égalité d’abord → range après

Résultat

Index Scan using idx_transactions_user_status_date  (cost=0.43..8.45 rows=20) (actual time=0.035..0.042)
  Index Cond: (user_id = 123 AND status = 'completed' AND created_at > '2024-01-01')

Gain : 12s → 40ms

Optimisation #2 : EXPLAIN ANALYZE est votre ami

Commandes essentielles

-- Analyser une requête
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

-- Avec buffers (I/O stats)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM transactions WHERE amount > 1000;

-- Format JSON (pour outils)
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT ...;

Lire EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM transactions WHERE user_id = 123;

Seq Scan on transactions  (cost=0.00..45231.50 rows=140 width=256) (actual time=0.012..245.123)
  ^            ^                 ^                  ^                        ^
  |            |                 |                  |                        |
  Type        Table            Estimated cost    Estimated rows         Actual time

Termes importants

• Seq Scan : full table scan (mauvais si table > 10k rows)
• Index Scan : utilise un index (bon)
• Index Only Scan : encore mieux (données dans l’index)
• Bitmap Heap Scan : combine plusieurs index
• cost : estimation, pas le temps réel
• actual time : temps réel en ms

Optimisation #3 : N+1 queries

Problème

// API route Next.js
export async function GET() {"{"}
  const users = await db.user.findMany({"{"} take: 10 {"}"});
  
  // N+1 : 1 requête users + 10 requêtes posts
  for (const user of users) {"{"}
    user.posts = await db.post.findMany({"{"}
      where: {"{"} userId: user.id {"}"}
    {"}"});
  {"}"}
  
  return Response.json(users);
{"}"}
// 11 requêtes SQL

Solution : JOIN ou eager loading

Option 1 : JOIN manuel

SELECT 
  u.id, u.name, u.email,
  json_agg(json_build_object('id', p.id, 'title', p.title)) as posts
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.id IN (1,2,3,4,5)
GROUP BY u.id;

Option 2 : Prisma include

const users = await db.user.findMany({"{"}
  take: 10,
  include: {"{"}
    posts: true
  {"}"}
{"}"});
// 1 seule requête SQL avec LEFT JOIN

Gain : 11 requêtes → 1 requête

Optimisation #4 : Partial indexes

Cas d’usage : filtrer souvent sur status = ‘active’

-- Index complet (inutile pour les deleted)
CREATE INDEX idx_users_full ON users (status);

-- Index partiel (mieux)
CREATE INDEX idx_users_active ON users (email)
WHERE status = 'active';

Avantages

• Index plus petit (faster)
• Moins de maintenance (insert/update)
• Uniquement pour les cas fréquents

Autre exemple : soft deletes

CREATE INDEX idx_posts_not_deleted ON posts (created_at)
WHERE deleted_at IS NULL;

Optimisation #5 : Analyser les stats de la table

-- Mettre à jour les statistiques
ANALYZE transactions;

-- Stats détaillées
SELECT 
  schemaname,
  tablename,
  n_tup_ins,     -- insertions
  n_tup_upd,     -- updates
  n_tup_del,     -- deletes
  n_live_tup,    -- rows actuelles
  n_dead_tup,    -- rows mortes (VACUUM)
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE tablename = 'transactions';

Si n_dead_tup > 20% de n_live_tup → VACUUM

VACUUM ANALYZE transactions;

Optimisation #6 : Connection pooling

Problème : trop de connexions

Serverless (Vercel, AWS Lambda) : chaque requête ouvre une connexion DB

Limite PostgreSQL : 100 connexions par défaut

500 requêtes/s × 200ms = 100 connexions → limite atteinte

Solution : PgBouncer ou Prisma Accelerate

docker-compose.yml

services:
  pgbouncer:
    image: pgbouncer/pgbouncer:latest
    environment:
      DATABASES_HOST: postgres
      DATABASES_PORT: 5432
      DATABASES_USER: myuser
      DATABASES_PASSWORD: mypass
      DATABASES_DBNAME: mydb
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 1000
      DEFAULT_POOL_SIZE: 25
    ports:
      - "6432:6432"

Connection string

DATABASE_URL="postgresql://user:pass@pgbouncer:6432/mydb"

Gain : support de 1000+ connexions avec seulement 25 connexions DB réelles

Optimisation #7 : SELECT uniquement les colonnes nécessaires

❌ Mauvais

// Récupère TOUT (y compris blob de 2MB)
const users = await db.user.findMany();

✅ Bon

// Uniquement ce dont on a besoin
const users = await db.user.findMany({"{"}
  select: {"{"}
    id: true,
    name: true,
    email: true
  {"}"}
{"}"});

Gain : payload API -85%, mémoire -70%

Optimisation #8 : Pagination avec cursors

❌ OFFSET/LIMIT (lent sur grandes tables)

SELECT * FROM transactions
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;
-- Doit scanner les 10,020 premières rows

✅ Cursor-based pagination (rapide)

SELECT * FROM transactions
WHERE created_at < '2024-01-15 12:34:56'
ORDER BY created_at DESC
LIMIT 20;
-- Index scan direct

Implémentation

async function getTransactions(cursor) {"{"}
  const transactions = await db.transaction.findMany({"{"}
    take: 20,
    ...(cursor && {"{"}
      cursor: {"{"} id: cursor {"}"},
      skip: 1
    {"}"}),
    orderBy: {"{"} createdAt: 'desc' {"}"}
  {"}"});
  
  return {"{"}
    transactions,
    nextCursor: transactions[19]?.id
  {"}"};
{"}"}

Optimisation #9 : Caching stratégique

Pattern : cache par couches

import Redis from 'ioredis';

const redis = new Redis(process.env.REDIS_URL);

async function getUser(userId) {"{"}
  // L1 : cache mémoire (in-process)
  const cached = cache.get(`user:${"$"}{"{"}userId{"}"}`);
  if (cached) return cached;
  
  // L2 : Redis
  const redisData = await redis.get(`user:${"$"}{"{"}userId{"}"}`);
  if (redisData) {"{"}
    const user = JSON.parse(redisData);
    cache.set(`user:${"$"}{"{"}userId{"}"}`, user);
    return user;
  {"}"}
  
  // L3 : Database
  const user = await db.user.findUnique({"{"} where: {"{"} id: userId {"}"} {"}"});
  
  // Store in caches
  await redis.setex(`user:${"$"}{"{"}userId{"}"}`, 300, JSON.stringify(user));
  cache.set(`user:${"$"}{"{"}userId{"}"}`, user);
  
  return user;
{"}"}

TTL recommandés

• Données statiques (ex: pays) : 1 jour
• Profils users : 5-15 minutes
• Transactions : pas de cache (ou très court)

Optimisation #10 : Materialized views

Cas d’usage : requête complexe utilisée souvent

-- Requête lente (3s)
SELECT 
  u.id,
  u.name,
  COUNT(DISTINCT t.id) as transaction_count,
  SUM(t.amount) as total_amount,
  AVG(t.amount) as avg_amount
FROM users u
LEFT JOIN transactions t ON t.user_id = u.id
WHERE t.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id;

Solution : materialized view

-- Créer la vue matérialisée
CREATE MATERIALIZED VIEW user_stats_30d AS
SELECT 
  u.id,
  u.name,
  COUNT(DISTINCT t.id) as transaction_count,
  SUM(t.amount) as total_amount,
  AVG(t.amount) as avg_amount
FROM users u
LEFT JOIN transactions t ON t.user_id = u.id
WHERE t.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id;

-- Index pour les lookups
CREATE INDEX idx_user_stats_user_id ON user_stats_30d (id);

-- Refresh périodique (cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats_30d;

Utilisation

-- Instantané (< 10ms)
SELECT * FROM user_stats_30d WHERE id = 123;

Refresh automatique (pg_cron)

CREATE EXTENSION pg_cron;

SELECT cron.schedule('refresh-user-stats', '0 */6 * * *', 
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats_30d$$
);

Optimisation #11 : Query timeout

Protection contre les requêtes infinies

-- Global
ALTER DATABASE mydb SET statement_timeout = '30s';

-- Par session
SET statement_timeout = '5s';

Dans Prisma

const prisma = new PrismaClient({"{"}
  datasources: {"{"}
    db: {"{"}
      url: `${"$"}{"{"}process.env.DATABASE_URL{"}"}?statement_timeout=5000ms`
    {"}"}
  {"}"}
{"}"});

Optimisation #12 : Monitoring continu

pg_stat_statements

-- Enable extension
CREATE EXTENSION pg_stat_statements;

-- Top 10 requêtes les plus lentes
SELECT 
  substring(query, 1, 80) as query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Requêtes avec le plus de cache miss
SELECT 
  substring(query, 1, 80),
  blk_read_time,
  blk_write_time
FROM pg_stat_statements
WHERE blk_read_time > 0
ORDER BY blk_read_time DESC
LIMIT 10;

Outils

• pgAdmin : GUI pour PostgreSQL
• pgHero : monitoring automatique
• Datadog : APM avec intégration PostgreSQL
• New Relic : query analysis

Checklist d’optimisation

Analyse
[ ] Identifier les requêtes lentes (logs, APM)
[ ] EXPLAIN ANALYZE sur chaque requête
[ ] Vérifier les index existants

Index
[ ] Index sur FK
[ ] Index composites pour WHERE + ORDER BY
[ ] Partial indexes si applicable
[ ] Analyser coverage des index

Requêtes
[ ] Éviter SELECT *
[ ] Résoudre N+1
[ ] Pagination avec cursors
[ ] Query timeout configuré

Infrastructure
[ ] Connection pooling (PgBouncer)
[ ] Caching (Redis)
[ ] Monitoring (pg_stat_statements)

Maintenance
[ ] VACUUM régulier
[ ] ANALYZE après gros INSERT/UPDATE
[ ] Backup testés

Résultats réels

App fintech (2.4M transactions)

Avant

• Endpoint /transactions : 12s
• CPU DB : 85%
• 15 timeouts/jour

Après

• Endpoint /transactions : 450ms (-96%)
• CPU DB : 12%
• 0 timeout

ROI : 2 jours d’opti, -$400/mois de coûts serveur

---

Besoin d’un audit PostgreSQL ? Contactez-moi pour analyser vos requêtes lentes.