PostgreSQL 15 · OVH Dedicated Server · 50M Users · 10M Products · 100K Sellers
PostgreSQL does not auto-create indexes on FK columns. Tables like buyer_profiles, password_history, product_images, order_items, payments, disputes, settlements, kyc_documents, cashback_records, and all seller_org child tables had unindexed FK columns causing full table scans on every JOIN.
Added 21 CONCURRENTLY indexes covering all FK columns.
audit_logs, login_history, platform_audit_logs, seller_activity_logs, and wallet_transactions will grow unboundedly. At 50M users with 10M products, audit_logs alone will exceed 500M rows/year. Full table scans on these tables will degrade to minutes.
Implemented monthly range partitioning via pg_partman with automated partition creation.
Orders, notifications, login history, and audit logs have no archiving. Delivered/cancelled orders from 2+ years ago remain in the hot orders table, inflating index sizes and slowing current order queries.
Created archive schema with 6 archive tables and 4 automated archive functions scheduled via pg_cron.
The most frequent query pattern — browse products by category + status + price — had no composite index. Each browse request triggered a multi-column filter scan across 10M+ product rows.
Added idx_products_category_status_price, idx_products_status_rating_sold, and 6 other composite product indexes.
Product title search using LIKE or ILIKE without a trigram index causes full sequential scans. At 10M products this is catastrophic for search latency.
Added GIN trigram index (pg_trgm) on products.title and GIN index on products.tags array.
audit_logs, login_history, orders, and seller_activity_logs are queried heavily by date range. B-tree indexes on created_at/placed_at are expensive to maintain at scale. BRIN indexes are 100–1000× smaller and ideal for append-only time-series data.
Added BRIN indexes on all time-series tables with pages_per_range=128.
Seller dashboard stats, category product counts, and platform daily metrics are computed on-the-fly from large tables. These queries run on every page load and involve expensive GROUP BY + COUNT + SUM across millions of rows.
Created 3 materialized views (mv_seller_stats, mv_category_stats, mv_platform_daily_metrics) with concurrent refresh schedules.
Prisma direct connections at 100K concurrent users would exhaust PostgreSQL max_connections (default 100). Each NestJS instance opens its own pool without PgBouncer, causing connection exhaustion under load.
Documented PgBouncer transaction-mode configuration with max_client_conn=1000, pool_size=25 per database.
otp_codes, refresh_tokens, and user_sessions accumulate expired rows indefinitely. At 50M users with frequent OTP usage, these tables will have millions of stale rows degrading lookup performance.
Created 5 cleanup functions scheduled daily via pg_cron.
Default PostgreSQL config (shared_buffers=128MB, work_mem=4MB) is designed for minimal hardware. On a 32GB OVH dedicated server, this leaves 97% of RAM unused by the database engine.
Provided tuned postgresql.conf recommendations: shared_buffers=8GB, effective_cache_size=24GB, work_mem=64MB, maintenance_work_mem=2GB.