Doug Ortiz’s Post

🚀 Database Indexing (Part 2): Real Problems, Trade-offs & Best Practices In Part 1, we covered the basics and types of indexing. Now let’s talk about what really matters in production systems 👇 🔻 Real-World Problems It Solves 🔸 Slow APIs due to full table scans 🔸 High latency in large datasets 🔸 Inefficient JOIN operations 🔸 Poor performance in reporting queries ⚠️ Trade-offs / Cons ❌ Increased storage usage ❌ Slower writes (INSERT, UPDATE, DELETE) ❌ Index maintenance overhead ❌ Too many indexes = performance degradation 🔹 Clustered vs Non-Clustered ✔ Clustered Index Defines physical data order Only one per table ✔ Non-Clustered Index Separate structure Multiple allowed 🔥 Common Mistakes ❌ Indexing low-cardinality columns ❌ Ignoring query patterns ❌ Over-indexing ❌ Not indexing JOIN columns 🔹 When to Use Indexing ✔ WHERE clause ✔ JOIN conditions ✔ ORDER BY / GROUP BY ✔ Frequently queried fields 🔹 When NOT to Use ❌ Small tables ❌ Write-heavy systems with too many indexes 🔹 Best Practices ✔ Use composite indexes wisely ✔ Follow left-prefix rule ✔ Analyze queries using: EXPLAIN ANALYZE ✔ Remove unused indexes 🎯 Final Thought Indexing is the foundation of database performance. Without it, scaling strategies like caching or partitioning won’t help much. 👉 Optimize first. Scale later. 💬 What indexing mistake cost you the most in production? #Database #SystemDesign #Performance #SQL #BackendDevelopment #Optimization #Microservices

I thought LIMIT makes queries fast… until it didn’t I used to think: If I add LIMIT 10, the query should be fast. But in one case, even with LIMIT, the query was still slow. So I checked EXPLAIN ANALYZE. What I saw was something like: Limit → Sort → Join → Seq Scan Which basically means the database was: * scanning a large dataset * doing joins * sorting everything * and only then applying LIMIT So LIMIT was just reducing the output, not the actual work. That’s when it clicked for me: LIMIT helps only when it’s applied early. Something like: Index Scan → Limit Here: * database reads already sorted/indexed data * stops early * avoids unnecessary processing In my case, the fix was pretty straightforward: * added index on ORDER BY column * adjusted the query so sorting could use the index * reduced the amount of data before sorting After that, the plan changed and performance improved. One small learning from this: LIMIT doesn’t make a query fast by default it depends on how the query is executed Curious if you’ve seen similar cases where LIMIT didn’t help at all. #PostgreSQL #BackendEngineering #DatabasePerformance #SystemDesign #PerformanceOptimization #QueryOptimization

🚀 𝗨𝗻𝗱𝗲𝗿𝘀𝘁𝗮𝗻𝗱𝗶𝗻𝗴 𝗗𝗮𝘁𝗮 𝗦𝘁𝗼𝗿𝗮𝗴𝗲 𝗶𝗻 𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗦𝗤𝗟 𝗦𝗲𝗿𝘃𝗲𝗿 Data storage is the backbone of any data-driven application. In MS SQL Server, data is stored in a structured and optimized way to ensure performance, scalability, and reliability. 🔹 1. 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗙𝗶𝗹𝗲𝘀 SQL Server stores data in three main file types: Primary Data File (.mdf) – Stores core database objects Secondary Data File (.ndf) – Optional, used for spreading data across disks Log File (.ldf) – Tracks transactions for recovery 🔹 2. 𝗣𝗮𝗴𝗲𝘀 & 𝗘𝘅𝘁𝗲𝗻𝘁𝘀 Data is stored in 8 KB pages 8 pages = 1 extent (64 KB) Efficient allocation improves query performance 🔹 3. 𝗧𝗮𝗯𝗹𝗲𝘀 & 𝗜𝗻𝗱𝗲𝘅𝗲𝘀 Data is organized in tables (rows & columns) Clustered Index → Defines physical storage order Non-Clustered Index → Improves query speed 🔹 4. 𝗦𝘁𝗼𝗿𝗮𝗴𝗲 𝗘𝗻𝗴𝗶𝗻𝗲 Responsible for reading/writing data to disk Works with buffer cache to optimize performance 🔹 5. 𝗙𝗶𝗹𝗲𝗴𝗿𝗼𝘂𝗽𝘀 Logical grouping of files Helps in performance tuning and backup strategies 🔹 6. 𝗧𝗿𝗮𝗻𝘀𝗮𝗰𝘁𝗶𝗼𝗻 𝗟𝗼𝗴 Ensures ACID properties Supports recovery models: Full, Bulk-Logged, Simple 💡 𝗪𝗵𝘆 𝗶𝘁 𝗺𝗮𝘁𝘁𝗲𝗿𝘀? Understanding storage internals helps in: ✔ Performance tuning ✔ Query optimization ✔ Efficient database design ✔ Troubleshooting issues 📌 Mastering these concepts is essential for every Data Engineer & DBA working with SQL Server. #SQLServer #DataEngineering #Database #DataStorage #ETL #Azure #PerformanceTuning #DataAnalytics

Day 25: Execution Plans — Reading the Database’s Mind 🧠 "Your SQL query is just a suggestion. The database decides how to actually do the work." When you hit 'Execute,' the database doesn't just start running. It hands your code to a Query Optimizer, which looks at your indexes, table sizes, and join types to create a "map" of the fastest way to get your results. This map is called an Execution Plan. If your query is slow, the Execution Plan is where the "crime scene" evidence is hidden. Think of an Execution Plan like a GPS Navigation App 🗺️: The Destination: Your SELECT statement (what you want). The Possible Routes: The database could use an Index (The Highway) or a Full Table Scan (The Side Streets). The Final Plan: The database chooses the route with the "lowest cost" (least amount of CPU and memory usage). Why you should care about "Cost": In an execution plan, every step has a "Cost %." If you see one step taking up 90% of the effort, you’ve found your bottleneck. Usually, it's a missing index or a "Nested Loop" that is spiraling out of control. SQL: -- For PostgreSQL or MySQL EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date > '2024-01-01'; #30DaysOfSQL #LearningInPublic #DataChallenge #DataAnalysis #CareerDevelopment #DataCommunity #innovation #technology #creativity #Future #futurism. #DataAnalytics #DataScience #DataEngineering #BusinessIntelligence

Most developers blame slow queries on missing indexes. The real culprit is usually hidden inside the execution plan. After years of tuning SQL Server workloads, I have learned that reading execution plans is the single highest-leverage skill a data engineer can develop. It tells you exactly where SQL Server is spending its time — no guessing required. Here is what I look for first when a query is underperforming: 1. Thick arrows between operators — wide data flows signal excessive row estimates and memory pressure 2. Key Lookups — these often mean a nonclustered index is missing one or two covering columns 3. Hash Matches on large tables — usually a sign of outdated statistics or a missing join index 4. Parallelism warnings — CXPACKET waits visible in the plan indicate skewed data distribution or MAXDOP misconfiguration 5. Estimated vs actual row counts — a significant gap almost always points to stale statistics or parameter sniffing Once you identify the bottleneck operator, the fix is usually surgical. Update statistics, add a covering index, rewrite the predicate, or force a plan hint where justified. You rarely need to rewrite the entire query. Execution plan analysis is not reserved for DBAs. Every engineer who writes T-SQL should be comfortable opening an actual execution plan before escalating a performance issue. Build that habit early and you will resolve most slow query tickets in under thirty minutes. #SQLServer #QueryOptimization #DataEngineering #PerformanceTuning #DatabaseAdministration

Production Query Plans Without Production Data 🚀 Testing queries in development often gives us false confidence. While your CI database might have a few thousand rows, your production environment is likely managing millions. Because the PostgreSQL planner relies on table statistics to choose join strategies and index scans, it will make completely different decisions based on the data volume. Running an EXPLAIN command in your CI pipeline tells you almost nothing about how that query will behave in the real world. The Game Changer: Portable Statistics 🛠️ Modern PostgreSQL features (starting with version 18) now allow for "Portable Statistics". This allows you to: 🔹Export statistics directly from your production environment. 🔹Inject them into a test database or local debugging tool. 🔹Replicate the production planner’s decision-making without moving massive datasets. This approach is the core premise behind tools like RegreSQL, allowing you to catch query plan regressions in CI by forcing the planner to "see" production-scale statistics, even when the underlying data is small. Why This Matters for Infrastructure 📈 🔹Faster Upgrades: Major version upgrades are significantly faster because statistics can transfer automatically instead of waiting hours for a manual ANALYZE. 🔹Minimized Downtime: Ensures your database is ready for production traffic with an efficient optimizer from minute one. 🔹Proactive Resilience: Catch performance outages before they happen during critical transitions. Special thanks to Radim Marek for the deep dive into this topic! Read the full breakdown at Data Engineering Weekly: 🔗 https://lnkd.in/gFdjPmH9 #DataEngineering #PostgreSQL #ProductionEngineering #DatabaseOptimization #SRE #SQL #DataEngineeringWeekly

🚨 Database Series #18 — Execution Plans Ever wondered: “Why is my query slow… even though it looks correct?” 🤔 The problem is not your SQL syntax. It’s how SQL Server executes it. That’s where Execution Plans come in. 🧠 Core Concept An Execution Plan shows how SQL Server processes your query step by step. Think of it as: 🗺 A roadmap that reveals how your query actually runs Not what you wrote… but what the engine decides to do. 💻 Code Example SELECT Name FROM Employees WHERE Salary > 5000; This looks simple… But SQL Server may choose: ❌ Scan entire table or ✅ Use an index to jump directly Execution Plan tells you which one happened. 📊 Estimated vs Actual Plan 🔹 Estimated Plan Before execution Based on statistics Prediction 🧠 🔹 Actual Plan After execution Real metrics What actually happened ⚙️ Think: Estimated → “What SQL Server expects” Actual → “What really happened” ⚡ Table Scan vs Index Seek ❌ Table Scan Reads every row Slow on large tables ✅ Index Seek Navigates directly using index Fast and efficient Visualize: Scan → 📋 read everything Seek → 🎯 go straight to target 📈 Query Cost Analysis Execution plans show cost percentages. Example: 🔹 Table Scan → 80% cost 🔹 Index Seek → 20% cost This helps you: 🔍 Identify bottlenecks ⚡ Optimize slow operations Important: Cost is relative within the query — not absolute time. ⚠️ Common Mistake Ignoring execution plans completely ❌ Many developers: Write query → run it → move on Without checking: ❌ Hidden scans ❌ Missing indexes ❌ Inefficient joins 🎯 Practical Takeaway Always check the execution plan when: ✅ Query is slow ✅ Working with large data ✅ Optimizing performance Focus on: 🔍 Scans vs Seeks 📊 High-cost operations ⚙️ Inefficient steps 💬 Question for developers Do you regularly check execution plans… or only when things break? 👀 ➡️ Next in the series: Transactions & Isolation Levels — controlling consistency and concurrency 🔒

𝐃𝐚𝐭𝐚𝐛𝐚𝐬𝐞 𝐏𝐞𝐫𝐟𝐨𝐫𝐦𝐚𝐧𝐜𝐞 𝐒𝐭𝐫𝐚𝐭𝐞𝐠𝐢𝐞𝐬 𝐚𝐧𝐝 𝐭𝐡𝐞 𝐇𝐢𝐝𝐝𝐞𝐧 𝐂𝐨𝐬𝐭𝐬 𝐁𝐞𝐡𝐢𝐧𝐝 𝐓𝐡𝐞𝐦 A query that performs perfectly today can become a major bottleneck six months later. What works on 10,000 rows may fail on 5 million. In many cases, the first fix seems obvious: ✅ Add an index ✅ Improve read speed ✅ Reduce latency But then the side effects appear: ⚠️ Slower writes ⚠️ Longer imports ⚠️ More complex updates ⚠️ Risk of stale cached data That’s the reality of database performance engineering: Every optimization solves one problem, but often introduces another. Some common examples: Indexes improve reads but can slow writes Caching reduces DB load, but can introduce stale data Denormalisation speeds up queries but makes data maintenance harder The real skill is not just knowing these techniques It’s understanding their trade-offs and choosing what your system can realistically afford. Performance is never free. It’s always a design decision. #Database #PerformanceOptimization #BackendDevelopment #SoftwareEngineering #SystemDesign #Scalability #SQL #Engineering

Query Optimization Mistakes (Final Synthesis) Most database performance problems are self-inflicted. Not because databases are slow. But because queries are poorly designed. After working with production systems, the same mistakes appear repeatedly. ❌ Fetching more data than needed SELECT * everywhere ❌ Missing or wrong indexes ❌ Ignoring execution plans ❌ N+1 query patterns ❌ Using OFFSET pagination at scale ❌ Long-running transactions Individually, each mistake seems small. Combined, they destroy performance. Real scenario. An API with: • inefficient queries • no indexing strategy • excessive joins Works fine in development. Fails under production load. Here’s the truth: Databases don’t get slower. Workloads get heavier. Optimization is not about tricks. It’s about: • reducing I/O • minimizing round trips • understanding execution plans The biggest shift happens when you stop asking: “Why is this query slow?” And start asking: “What unnecessary work is happening?” That’s where real performance gains come from. #Databases #SQL #Performance #BackendEngineering #SystemDesign

🚀 𝗗𝗮𝘆 𝟯𝟯 — 𝗦𝗤𝗟 𝗝𝗼𝘂𝗿𝗻𝗲𝘆 Moving beyond writing queries, I started looking at how databases are actually designed and used in real systems. What stood out is that there’s no one-size-fits-all approach — database decisions depend heavily on business needs and system goals. 🔹 𝗛𝗼𝘄 𝘀𝘆𝘀𝘁𝗲𝗺𝘀 𝗱𝗶𝗳𝗳𝗲𝗿 Different environments prioritize different things: • Some systems focus on 𝗳𝗮𝘀𝘁 𝗱𝗲𝘃𝗲𝗹𝗼𝗽𝗺𝗲𝗻𝘁 𝗮𝗻𝗱 𝗹𝗼𝘄 𝗼𝘃𝗲𝗿𝗵𝗲𝗮𝗱 • Others are built for 𝗵𝗶𝗴𝗵 𝘁𝗿𝗮𝗳𝗳𝗶𝗰 𝗮𝗻𝗱 𝘀𝗰𝗮𝗹𝗲 • Some require 𝗮𝗱𝗮𝗽𝘁𝗮𝗯𝗶𝗹𝗶𝘁𝘆 𝗮𝗰𝗿𝗼𝘀𝘀 𝗺𝘂𝗹𝘁𝗶𝗽𝗹𝗲 𝘂𝘀𝗲 𝗰𝗮𝘀𝗲𝘀 🔹 𝗧𝘄𝗼 𝘄𝗮𝘆𝘀 𝗱𝗮𝘁𝗮 𝗶𝘀 𝘂𝘀𝗲𝗱 • 𝗢𝗟𝗧𝗣 𝘀𝘆𝘀𝘁𝗲𝗺𝘀 handle continuous operations where speed and accuracy matter • 𝗢𝗟𝗔𝗣 𝘀𝘆𝘀𝘁𝗲𝗺𝘀 focus on analyzing large volumes of data to generate insights Understanding this difference changes how you think about query design and data storage. 🔹 𝗣𝗲𝗿𝗳𝗼𝗿𝗺𝗮𝗻𝗰𝗲 𝗯𝗲𝗵𝗶𝗻𝗱 𝘁𝗵𝗲 𝘀𝗰𝗲𝗻𝗲𝘀 A key factor in optimization is 𝗶𝗻𝗱𝗲𝘅𝗶𝗻𝗴. • Makes data retrieval significantly faster • Helps reduce query execution time • But excessive indexing can impact 𝗶𝗻𝘀𝗲𝗿𝘁/𝘂𝗽𝗱𝗮𝘁𝗲 𝗼𝗽𝗲𝗿𝗮𝘁𝗶𝗼𝗻𝘀 💡 𝗕𝗶𝗴𝗴𝗲𝗿 𝗥𝗲𝗮𝗹𝗶𝘇𝗮𝘁𝗶𝗼𝗻: Working with databases isn’t just about writing SQL — it’s about making 𝗱𝗲𝘀𝗶𝗴𝗻 𝗰𝗵𝗼𝗶𝗰𝗲𝘀 that balance 𝗽𝗲𝗿𝗳𝗼𝗿𝗺𝗮𝗻𝗰𝗲, 𝘀𝗰𝗮𝗹𝗮𝗯𝗶𝗹𝗶𝘁𝘆, 𝗮𝗻𝗱 𝗲𝗳𝗳𝗶𝗰𝗶𝗲𝗻𝗰𝘆. Slowly starting to see the bigger picture of how data systems actually work in the real world 📊🚀 #SQL #DataAnalysis #BusinessAnalysis #SQLJourney #SQLLearning #DataSystems #OLTP #OLAP #Indexing #TechLearning #LearningJourney