Postgres Vertical Scaling Limitations and Signs of Overload

I've run into this antipattern several times over the years. It's not a problem to have a big database or a busy database, but things start to go wrong when you combine the two, especially when doing it with multiple databases per cluster. I figured it was finally time to talk about it.

🚀 What happens to your data when a new server is added? In most systems… everything breaks. Unless you use: Consistent Hashing Day 7 of System Design Series — Consistent Hashing 👉 Problem Statement Design a system that distributes data efficiently across servers. 👉 Problem with Normal Hashing - Adding/removing servers reshuffles all data 👉 Solution: Consistent Hashing - Uses a hash ring - Maps data to nearest server 👉 Key Concepts - Hash ring - Virtual nodes 👉 Tech Usage Redis Cluster, Cassandra 👉 Challenges - Uneven distribution - Hotspots 👉 Final Thought “Good systems scale. Great systems scale smoothly.” #SystemDesign #Java #DistributedSystems #Backend #InterviewPrep

Every PostgreSQL connection is an OS process consuming 5-10 MB of memory. At 500 connections, that's 5 GB of RAM consumed by connection infrastructure before a single query runs. The common reaction to "too many connections" is to increase max_connections. This works until you hit the second wall: memory exhaustion. PostgreSQL has so little RAM left for shared_buffers, work_mem, and the OS page cache that every query slows down. Connection pooling is the real fix. Here's what I've learned from deploying it across different environments: 1. The optimal pool size has nothing to do with your application's concurrency. The formula is (CPU cores x 2) + number of disks. For an 8-core server with SSD, that's about 17 connections. This seems tiny, but PostgreSQL can only truly parallelize as many queries as it has cores. Beyond that, connections compete for CPU and context switching reduces throughput. 2. Transaction mode pooling should be your default. PgBouncer in transaction mode assigns a database connection when a transaction begins and returns it when it commits. A pool of 20 connections can serve hundreds of application connections because most are idle at any moment. Only switch to session mode if you specifically need prepared statements (with PgBouncer), LISTEN/NOTIFY, or temporary tables. 3. The deadliest connection pattern is idle-in-transaction. A connection that opened a transaction, ran a query, and then waited -- holding locks, holding a snapshot, holding a pool slot hostage. Set idle_in_transaction_session_timeout = 60s to automatically terminate these. PgBouncer is the most battle-tested pooler. Supavisor (by Supabase) is better for multi-tenant and cloud-native environments and supports prepared statements in transaction mode. Both are dramatically cheaper than increasing max_connections and hoping for the best. Monitor connection utilization continuously. A leak adds one connection per hour. A new microservice starts at 5 connections, someone bumps it to 20 during a load test, and never reverts it. Full guide with PgBouncer configuration, Supavisor setup, and sizing formulas: https://lnkd.in/eB_MctB3 #PostgreSQL #ConnectionPooling #PgBouncer #DatabasePerformance #DevOps #SRE

Thundering Herd Problem (When Everything Breaks at Once):- A caching layer to reduce database load for frequently accessed data. --- Problem I faced: Everything worked well… until cache expired. Suddenly: Huge spike in database queries CPU usage shot up API latency increased System became unstable All at the same moment. --- How I fixed it:- This was the Thundering Herd Problem. When cache expired, multiple requests tried to fetch fresh data simultaneously. Fixes applied: Added cache locking (single-flight) so only one request refreshes data Introduced randomized cache expiry (TTL jitter) to avoid simultaneous expiration Used stale-while-revalidate approach for smoother refresh Now: Only one request hits DB Others wait or get cached response System stays stable. --- What I learned:-- Caching reduces load… but poorly managed caching can create bigger spikes than no cache at all. --- Question? Have you ever seen your system fail not because of traffic… but because many requests did the same thing at the same time? #Java #SpringBoot #Programming #SoftwareDevelopment #Cloud #AI #Coding #Learning #Tech #Technology #WebDevelopment #Microservices #API #Database #SpringFramework #Hibernate #MySQL #BackendDevelopment #CareerGrowth #ProfessionalDevelopment #RDBMS #PostgreSQL #backend

When you've used EXPLAIN ANALYZE in #Postgres before, you know that the measurement itself has overhead, and can skew compared to the actual runtime. This is especially pronounced with a query like "EXPLAIN ANALYZE SELECT COUNT(*) FROM my_table", when lots of rows are iterated over sequentially to be counted. But some of that overhead is unnecessary, and can be improved on. For the upcoming release of Postgres 19, Andres Freund, David Geier and myself changed how Postgres captures timing for EXPLAIN ANALYZE. Instead of using the system clock (clock_gettime), Postgres now supports directly executing the RDTSC instruction on x86-64 CPUs. This is similar to the instruction already in use on modern Linux (RDTSCP), but avoids going through the vDSO abstraction, and most importantly, for EXPLAIN ANALYZE, allows out-of-order execution by using RDTSC instead of RDTSCP, drastically changing the overhead of timing. In practice that means for the extreme case of a COUNT(*) query for 50 million rows (which does two timing measurements per row), where the actual runtime is 270ms, you now get about 20-30% overhead (350ms) with timing collected, vs > 200% overhead (800ms) on current Postgres versions. For production queries that usually evens out to much less overhead on average, making it more practical to capture timing more often, e.g. with auto_explain.log_timing. Learn all the details in today's 5mins of Postgres episode: https://lnkd.in/gEA_KDFV

Debugging a real production slowdown Recently, I ran into a performance issue where a production homepage was taking 4–5 seconds just to respond. At first glance, everything looked fine: → CPU usage was low → Memory was stable → No obvious bottlenecks But the experience clearly said otherwise. What I discovered: → The application was running inside a Dockerized environment → During each page load, the database container CPU spiked to ~95% → The slowdown wasn’t constant — only triggered by specific queries This pointed to a deeper issue: inefficient query execution under certain conditions What I did: → Investigated live query behavior and request timing → Identified repeated heavy lookups during page load → Optimized the database by introducing proper indexing for frequent access patterns Result: → Database CPU usage dropped significantly → Response time improved from ~4–5s → ~0.5s → Overall system became much more stable under load Key takeaway: → Not all performance issues require scaling infrastructure. → Sometimes, the real problem is hidden in how data is accessed. #Docker #Laravel #MySQL #PerformanceOptimization #BackendEngineering #Debugging #SoftwareDevelopment

Our Grafana PVC dashboard was blank. That blank screen saved our production database. Weekly infra audit. PVC panels empty. No metrics at all. Dug in and found: Kubernetes 1.34 regression (#133847) silently disabled kubelet_volume_stats_* metrics. Our 5-node MySQL InnoDB cluster (1 primary, 4 secondary, quorum of 3) was at 98% disk. Zero alerts fired because our alert policy treated "no data" as OK to avoid false positives. Fixed: → Upgraded K8s cluster → Metrics restored → Switched alert policy: no data for 5+ mins = alert The dashboard being empty was the alert. Always audit your observability, not just your alerts. #Kubernetes #SRE #DevOps #GadaTech #Observability #MySQL #InnoDBCluster

Deployed a Node.js + PostgreSQL stack on Kubernetes using Minikube. What’s in place: → Multi-pod architecture with 2 replicas for high availability → PostgreSQL with PVC-backed storage for persistence → pgAdmin UI wired in for direct database visibility → Structured Kubernetes manifests covering ConfigMaps, Secrets, Deployments, Services, Ingress, and PVCs → Secure credential handling with Secrets + ConfigMaps separation → Internal-only DB exposure + external NodePort access for app and pgAdmin What this unlocks: → Self-healing workloads with rolling updates baked in → Stable service discovery via cluster DNS (db:5432) → Clean separation between internal services and external access points → Everything runs locally on Minikube, but the architecture translates directly to managed Kubernetes clusters. The system is live end-to-end. Resume uploads are processed, stored in PostgreSQL, and query results flowing back through the app without friction. If you're building containerized apps and still skipping orchestration depth, this is the baseline approach to begin with. For more: https://lnkd.in/edhcK6nw #Docker #Kubernetes #FullStack #Backend #Database #TechInnovation

𝐎𝐮𝐫 𝐂𝐏𝐔 𝐡𝐢𝐭 𝟗𝟗% 𝐚𝐧𝐝 𝐧𝐨𝐭𝐡𝐢𝐧𝐠 𝐥𝐨𝐨𝐤𝐞𝐝 𝐰𝐫𝐨𝐧𝐠 We had rising CPU, slow APIs, and zero complex queries. Just plain UPDATEs. 𝐖𝐡𝐚𝐭 𝐰𝐞 𝐦𝐢𝐬𝐬𝐞𝐝 In PostgreSQL, UPDATE does not overwrite a row. It creates a new tuple and keeps the old one. Every update = more data. 𝐖𝐡𝐚𝐭 𝐡𝐚𝐩𝐩𝐞𝐧𝐞𝐝 𝐢𝐧 𝐩𝐫𝐨𝐝 • Same rows updated again and again • Dead tuples kept increasing • Tables silently bloated • Queries got slower over time 𝐓𝐡𝐞 𝐫𝐞𝐚𝐥 𝐢𝐬𝐬𝐮𝐞 Not bad queries. Not bad indexes. Just misunderstood database behavior. 𝐖𝐡𝐚𝐭 𝐟𝐢𝐱𝐞𝐝 𝐢𝐭 • Reduced unnecessary updates • Shortened transactions • Let vacuum catch up 𝐓𝐚𝐤𝐞𝐚𝐰𝐚𝐲 If your system updates the same rows frequently, you are not just updating data. You are creating more of it. And that adds up fast. #PostgreSQL #BackendEngineering #SystemDesign #DatabaseInternals #PerformanceOptimization #Scalability #SoftwareEngineering #MVCC #TechLearning #Java #SpringBoot

In 2016, our university's file infrastructure had a problem that was only going to get worse. Every file across all institutional systems lived inside PostgreSQL — fragmented into 50KB compressed chunks that the application server had to reassemble on every single request. The most accessed endpoint in the entire system was photo retrieval. And it was doing all that work, millions of times a month, on a database machine shared across three different systems. Under load testing with 1,000 simultaneous users, the error rate hit 22%. We evaluated our options. Object storage solutions existed, but our infrastructure at the time was constrained — we didn't have the capacity to operate and maintain that kind of tooling with the reliability and operational requirements that a production environment demands. So we designed something that fit what we actually had, and built it to last. The new architecture introduced a dedicated content server, fully decoupled from the database, with two-stage encrypted file upload, persistent connection pooling to avoid handshake overhead, and Sköld — a dedicated security layer that authenticates, audits, and logs every file access in real time, integrated into our observability stack. One requirement shaped the whole design: files are immutable by year. If you upload a profile photo in 2025 and change it in 2026, a new file is created — the 2025 version is never touched. Historical snapshots stay intact, backups are clean per-year, and audit trails are unambiguous. The migration ran for months in hybrid mode. A Python daemon synced files from the database to disk every minute, so by the time we switched each application over, the content server was already in sync. Not a single file was lost. Not a single request failed at cutover. The server has an active replica with 3-minute sync, full yearly backups, and daily incrementals. Nearly ten years later: → Zero unplanned downtime → Zero data loss → 16.2 million files, 18.5 TB under management → 2.7 million monthly access requests → 37.4 million audit operations logged since June 2025 alone → Storage expansion underway from 18 TB to 23 TB The machine running all of this: 8 cores, 16GB RAM. The hardest part wasn't the code. It was designing a migration that tens of thousands of users would never notice — and holding the line on doing it right. #SoftwareArchitecture #SystemDesign #Migration #Java #PublicSector #Engineering