Tachegnon Christian Kpanou’s Post

Why UPDATE feels heavier than INSERT in PostgreSQL, and why it matters more than you think. At first glance, UPDATE and INSERT may seem similar, as both involve writing data. However, they are fundamentally different. In PostgreSQL, an UPDATE is not an in-place change; it operates through Multi-Version Concurrency Control (MVCC), which significantly alters the process. Here’s what actually happens when you execute an UPDATE: - PostgreSQL creates a brand new version of the row. - The old version is marked as dead but not removed immediately. - Indexes may need to be updated again. - Extra Write Ahead Log (WAL) is generated. So effectively: UPDATE = INSERT (new row) + leave old row behind + clean it later. In contrast, an INSERT simply involves: - Writing the new row. - Updating indexes once. - Completing the operation. This process is clean, predictable, and cheaper. Why does this become a significant issue at scale? If your system frequently performs updates—such as counters, user states, or last_seen timestamps—dead rows begin to accumulate, leading to table bloat. This results in: - Increased workload for autovacuum to clean up dead rows. - More disk I/O and memory pressure. - Potential increases in replication lag due to heavier WAL. Over time, this issue transcends database concerns and evolves into a system design problem. A crucial insight is that if you design your system under the assumption that UPDATE operations are “cheap,” you will eventually encounter performance limitations. However, if you treat UPDATE as: “write + overhead + delayed cleanup,” you can make more informed decisions: - Avoid unnecessary updates. - Be cautious with indexed columns. - Consider append-only or event-based models. Final thought: INSERT writes data, while UPDATE rewrites history. This subtle difference is precisely why UPDATE incurs higher costs. #systemdesign #distributedsystem #interview. #HLD. #scaling.

You decided to design SQL database schema for a backend, The first thing is obviously "primary key" 🙂 Rule is simple, if its a single write DB, using an auto-incremeted integer (BIGINT) is best for simplicity. But what if the application scales massively, and you need distributed systems, multiple write DBs, or you need to shard the already existing DBs ? UUIDs seem like the obvious answer. They’re globally unique, easy to generate, and remove coordination between services. But again if we go to the internals of how databases store data in B-Tree indexes and how disk locality affects writes, it turns out this isn’t optimal as UUIDs are random primary keys, and databases love sequential writes. The optimised solution at this scale is timestamp-prefixed UUIDs ( generally called UUIDv7 ). They have starting bits (prefix) made of timestamp ( creation time ), this way both randomness and sequential writes are preserved 😇 . Postgres v17/18 have started support of UUIDv7. Databases love order. Distributed systems love randomness. UUIDv7 is where both finally agree 🙂 #SystemDesign

🚀 PostgreSQL Extensions Every DBA Should Know (But Many Don’t Use) PostgreSQL is powerful out of the box. But the real magic? ✅ Extensions They can turn PostgreSQL into a : • Monitoring system • Time-series database • Query analysis tool • Scalable data engine And most teams barely use them properly. 🔧 Here are PostgreSQL extensions every DBA should know: 1️⃣ pg_stat_statements Tracks real query performance across your database. 2️⃣ PostGIS Adds geospatial capabilities for location-based queries. 3️⃣ TimescaleDB Optimized for time-series workloads like logs and metrics. 4️⃣ pg_partman Automates table partitioning for large datasets. 5️⃣ pg_trgm Improves fuzzy search and text similarity queries. 🧠 Real-World Scenario (What Most DBAs Actually Face) A common situation in production: An application table grows steadily over time (10M → 20M → 50M rows) Read queries start slowing down. Autovacuum struggles to keep up. Index bloat increases. CPU and I/O usage spike during peak hours. No sudden failure. Just gradual performance degradation. What typically works in this situation: ✔️ Use pg_stat_statements to identify high-frequency and slow queries ✔️ Introduce partitioning using pg_partman to split large tables into manageable chunks ✔️ Rebuild or optimize indexes based on actual query patterns ✔️ (Optional) Move time-based data to TimescaleDB if workload is heavily time-series 💡 Outcome (What DBAs usually observe) More predictable query performance. Reduced index and table scan overhead. Better autovacuum efficiency. Improved stability under load. Not magic Just good engineering. ⚡ Takeaway Most PostgreSQL performance issues aren’t because PostgreSQL is weak. They happen because: ⚠️ We don’t use the tools PostgreSQL already gives us. Extensions are part of the system not an add-on. If you're working with PostgreSQL Which extension has saved you the most in production? Comment below 👇🏻 #PostgreSQL #DBA #DatabasePerformance #DataEngineering #Backend #OpenSource #Tech

🚧 SQL Server → PostgreSQL Migration: 2 Critical Challenges I Solved During migration, the toughest part was handling stored procedures behavior differences while ensuring zero backend changes. 🔴 Challenge 1: IN/OUT Parameters SQL Server: ·      OUT parameters are optional ·      Procedures return values without strict definition -- SQL Server CREATE PROCEDURE GetData @Id INT AS BEGIN    SELECT * FROM Table1 WHERE Id = @Id END  PostgreSQL:   ·      OUT parameters must be defined ·      Execution pattern differs 🔴 Challenge 2: Multiple Result Sets SQL Server: ·      One procedure → multiple result sets SELECT * FROM ClientMaster; SELECT * FROM BankMaster; Backend consumes both outputs directly. PostgreSQL: ·      Cannot return multiple result sets directly ⚡ Combined Solution ✔ Converted Stored Procedures → PostgreSQL Functions ✔ Used **JSON/JSONB** to handle: ·      Multiple result sets ·      Output structure -- PostgreSQL (Concept) SELECT jsonb_build_object(  'clients', (SELECT json_agg(c) FROM client_master c),  'banks', (SELECT json_agg(b) FROM bank_master b) ); ✔ Maintained: ·      Same business logic ·      Same execution behavior ·      No backend code changes 🧠 Approach SQL Server Behavior ↓ Analyze Output Pattern ↓ Design Compatible Structure (JSON) ↓ Implement in PostgreSQL Function ↓ Validate with Backend  📊 Result ✅ Multiple datasets handled in single response ✅ No backend impact ✅ Clean and scalable approach 💡 Key Learning: When migrating across databases, feature parity is not guaranteed — designing the right abstraction (like JSON) is the real solution. #PostgreSQL #SQLServer #DatabaseMigration #JSON #DataEngineering #SQL

Database migrations don't have to be a war of attrition. We just published a detailed case study on migrating Microsoft's WideWorldImporters OLTP database from SQL Server to PostgreSQL 15 using AI agents. The schema wasn't trivial: 26 sequences, 21 tables, 47 stored procedures, and all the edge cases that come with real enterprise workloads, including temporal tables, computed columns, and complex JSON update patterns. Instead of manual conversion, we built a toolchain of Claude Code slash commands, each handling a discrete stage of the migration pipeline. The result was repeatability at a level manual migrations simply can't match. Every transformation decision was captured in companion audit files. Smoke tests validated each function within minutes against live PostgreSQL containers. And the pipeline scales to hundreds of objects without accumulating technical debt. 🔹 Dependency analysis and DDL conversion handled by purpose-built slash commands ⚡ 45 stored procedures translated from MSSQL OPENJSON patterns to PostgreSQL jsonb_to_record 🧠 Temporal tables, computed columns, and PostGIS types converted with documented semantic decisions 🚀 Container-based smoke tests validated every function before any code was committed ✅ Full audit trail: every decision captured in reviewable markdown files #AgenticAI #EnterpriseArchitecture #PostgreSQL #DatabaseMigration Tagging some brilliant minds in this space: Rajagopal Nair Arvind Mehrotra Dr. Anil Kumar P Pradeep Chandran Rashid Siddiqui Ancy Paul

📊 **Leveling Up My Database Skills with PostgreSQL!** Today, I worked on structuring and managing user data in PostgreSQL. Creating clean, well-organized tables is a foundational step toward building reliable applications and data-driven systems. 🔍 **What this table represents:** * User profiles with name, email, age, and city * Consistent formatting and data types * A scalable structure ready for queries, filters, and analytics Every dataset—no matter how small—is an opportunity to practice data modeling, enhance query performance, and strengthen backend skills. 💡 *Small steps in SQL lead to big wins in development.* #PostgreSQL #SQL #DatabaseDesign #BackendDevelopment #DataEngineering #LearningJourney #TechSkills #Productivity

I love it when a complex topic is explained in a simple way! The link below will tell and show what database transactions, read anomalies, and isolation levels are. Examples, animations, simple language - all great and beginner-friendly! If you already know the concept, you may like the introduction to how transactions are implemented in Postgres and MySQL. https://lnkd.in/dM-E-6f6

Day 6: The Brain of the Backend — Database Deep Dive 🧠 Today’s session was a massive shift toward data persistence. It’s one thing to route a request, but it’s another to store it safely, efficiently, and in a way that can scale i.e Database Management Systems(DBMS) management. Why even use a DBMS?: It’s not just a "folder for data." A DBMS provides structure, handles concurrency (multiple people writing at once), and ensures you don't need to manually parse files every time you need information. Relational (SQL) vs. Non-Relational (NoSQL): SQL (Postgres/MySQL): Uses predefined schemas, tables, and rows. It’s built for Data Integrity. I'm focusing on Postgres because it’s open-source, extensible, and has great JSON compatibility. NoSQL (MongoDB): Uses collections and documents with a flexible schema—perfect for rapidly changing data structures. Database Migrations: This was a huge takeaway. Think of migrations as Version Control for your DB schema. Instead of manually running SQL commands, we write migration files (Up migrations for new changes, Down migrations to revert) to keep the DB structure consistent across the whole team. Seeding: Learned how to create "seed" files to pump test data into the DB for development environments so I'm not working with an empty screen. The "Not Null" Rule: More than 70% of table fields should ideally have a NOT NULL constraint to keep the database state consistent and avoid "ghost" data issues. Security (Parameterized Queries): NEVER pass raw user input into a SQL string. I learned how to use Parameterized Queries to pass inputs as "escaped" arguments, which is the primary defense against SQL Injection attacks. Naming Standards: Keeping it clean with small case and snake_case for all table and field names. The backend is only as strong as its database. Understanding migrations and security constraints today makes me feel a lot more confident about building production-ready systems. #BackendDevelopment #SQL #PostgreSQL #DatabaseDesign #SoftwareEngineering #LearningInPublic #WebDev #SystemDesign

🚀 PostgreSQL Schema Design & Database Fundamentals 📌 1. Schema Design in PostgreSQL A schema is a logical namespace to organize database objects (tables, views, functions). Why it matters: ✔ Clean structure ✔ Avoid naming conflicts ✔ Better security (role-based access) 📌 2. Relationships & Keys Database relationships ensure data integrity: 🔹 One-to-One → User ↔ Profile 🔹 One-to-Many → Department → Employees 🔹 Many-to-Many → Student ↔ Course (via junction table) 📌 3. Data Types = Performance Choosing the right data type directly impacts: ⚡ Storage ⚡ Query speed ⚡ Network bandwidth 📌 4. Normalization (Data Optimization) Goal: Reduce redundancy & improve consistency ✔ 1NF → Atomic values ✔ 2NF → No partial dependency ✔ 3NF → No transitive dependency ✔ BCNF → Stronger consistency rule 📌 5. Transactions & ACID Transactions ensure reliability: ✔ Atomicity ✔ Consistency ✔ Isolation ✔ Durability 📌 6. Query Optimization Tips ✔ Prefer JOIN over subqueries ✔ Avoid SELECT * ✔ Minimize round-trip ✔ Use indexes wisely (read ↑, write ↓) #PostgreSQL #DatabaseDesign #BackendDevelopment #SystemDesign #SoftwareEngineering

A query runs fine for months. Then PostgreSQL runs ANALYZE, statistics shift, and the planner decides a hash join is cheaper than the index scan it used yesterday. Suddenly the query is 50x slower. No code changed. No schema changed. The planner just picked a different strategy. Plan regressions are one of the most insidious performance problems in PostgreSQL because they appear out of nowhere. The application did not change — the database's internal cost model simply re-evaluated and chose differently. Here is what makes them so difficult: 1. Standard tools are blind to plan changes. pg_stat_statements tracks aggregate performance — total time, call count, mean time — but says nothing about the execution plan. You can see that a query doubled in mean time, but not that it switched from an index scan to a sequential scan. 2. The triggers are subtle and varied. ANALYZE updates statistics. A table grows past a planner threshold. Index bloat tips the cost calculation. A new data distribution pattern skews selectivity estimates. Any of these can cause the planner to switch strategies silently. 3. Detection requires infrastructure most teams lack. To catch a plan regression, you need to fingerprint every EXPLAIN plan, store them over time, and compare structures — not just raw text, but the semantic plan: join types, scan methods, join order. Cosmetic changes (slightly different cost estimates) should not trigger alerts. Strategy changes should. The fix is continuous plan fingerprinting. When a query changes from an index scan to a hash join, you see it within minutes — with a side-by-side comparison showing the before and after plans and the performance impact. That is the difference between catching a regression proactively and explaining a production incident to stakeholders. Full article with SQL examples: https://lnkd.in/e6-qVrUP #PostgreSQL #DatabasePerformance #DatabaseMonitoring #DevOps