Database Performance Tuning

Your database has 50 indexes. And it's SLOWER than having 5. Here's what most engineers get wrong about indexing: We treat indexes like free performance boosts. But every index you add is a hidden contract: - Every INSERT now updates N+1 data structures - Every UPDATE potentially rewrites multiple B-trees - The query planner gets confused with too many choices - Your working set no longer fits in memory I learned this the hard way at scale. We had a table with 34 indexes. Reads were fast. Writes were dying. P99 latency on inserts hit 1.2 seconds. The fix? We dropped 28 indexes. But here's the part nobody talks about: We replaced them with 3 composite indexes that covered 94% of our query patterns. The trick was analyzing pg_stat_user_indexes. Most of our indexes had ZERO scans in 30 days. They were dead weight burning I/O on every write. Here's the framework I now use: 1. Audit index usage monthly (pg_stat_user_indexes) 2. Every index must justify its write amplification cost 3. Composite indexes > single-column indexes (almost always) 4. Covering indexes eliminate heap lookups entirely 5. Partial indexes for queries that filter on a constant The result after cleanup: • Write latency dropped 73% • Storage shrank by 40% • Read performance stayed identical The best performance optimization isn't adding something new. It's removing what shouldn't be there. 💬 What's the worst index bloat you've seen? #SystemDesign #DatabaseEngineering #SoftwareEngineering #PostgreSQL #Performance

𝗛𝗼𝘄 𝘁𝗼 𝗶𝗺𝗽𝗿𝗼𝘃𝗲 𝗱𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗽𝗲𝗿𝗳𝗼𝗿𝗺𝗮𝗻𝗰𝗲? Here are the most important ways to improve your database performance: 𝟭. 𝗜𝗻𝗱𝗲𝘅𝗶𝗻𝗴 Add indexes to columns you frequently search, filter, or join. Think of indexes as the book's table of contents - they help the database find information without scanning every record. But remember: too many indexes slow down write operations. 💡 𝗕𝗼𝗻𝘂𝘀 𝘁𝗶𝗽: Regularly drop unused indexes. They waste space and slow down writing without providing any benefit. 𝟮. 𝗠𝗮𝘁𝗲𝗿𝗶𝗮𝗹𝗶𝘇𝗲𝗱 𝗩𝗶𝗲𝘄𝘀 Pre-compute and store complex query results. This saves processing time when users need the data again. Schedule regular refreshes to keep the data current. 𝟯. 𝗩𝗲𝗿𝘁𝗶𝗰𝗮𝗹 𝗦𝗰𝗮𝗹𝗶𝗻𝗴 Add more CPU, RAM, or faster storage to your database server. This is the most straightforward approach, but has physical and cost limitations. 𝟰. 𝗗𝗲𝗻𝗼𝗿𝗺𝗮𝗹𝗶𝘇𝗮𝘁𝗶𝗼𝗻 Duplicate some data to reduce joins. This technique trades storage space for speed and works well when reads outnumber writes significantly. 𝟱. 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗖𝗮𝗰𝗵𝗶𝗻𝗴 Store frequently accessed data in memory. This reduces disk I/O and dramatically speeds up read operations. Popular options include Redis and Memcached. 𝟲. 𝗥𝗲𝗽𝗹𝗶𝗰𝗮𝘁𝗶𝗼𝗻 Create copies of your database to distribute read operations. This works well for read-heavy workloads but requires managing data consistency. 𝟳. 𝗦𝗵𝗮𝗿𝗱𝗶𝗻𝗴 Split your database horizontally across multiple servers. Each shard contains a subset of your data based on a key like user_id or geography. This distributes both read and write loads. 𝟴. 𝗣𝗮𝗿𝘁𝗶𝘁𝗶𝗼𝗻𝗶𝗻𝗴 Divide large tables into smaller, more manageable pieces within the same database. This improves query and maintenance operations on huge tables. 🎁 𝗕𝗼𝗻𝘂𝘀: 🔹 𝗔𝗻𝗮𝗹𝘆𝘇𝗲 𝗲𝘅𝗲𝗰𝘂𝘁𝗶𝗼𝗻 𝗽𝗹𝗮𝗻𝘀. Use EXPLAIN ANALYZE to see precisely how your database executes queries. This reveals hidden bottlenecks and helps you target optimization efforts where they matter most. 🔹 𝗔𝘃𝗼𝗶𝗱 𝗰𝗼𝗿𝗿𝗲𝗹𝗮𝘁𝗲𝗱 𝘀𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀. These run once for every row the outer query returns, creating a performance nightmare. Rewrite them as JOINs for dramatic speed improvements. 🔹 𝗖𝗵𝗼𝗼𝘀𝗲 𝗮𝗽𝗽𝗿𝗼𝗽𝗿𝗶𝗮𝘁𝗲 𝗱𝗮𝘁𝗮 𝘁𝘆𝗽𝗲𝘀. Using VARCHAR(4000) when VARCHAR(40) would work wastes space and slows performance. Right-size your data types to match what you're storing. #technology #systemdesign #databases #sql #programming

Had an interesting session with a client this week who was facing serious SQL Server performance issues. Long-running queries, CPU spikes, and timeouts during peak hours. We started by reviewing their execution plans and found a couple of red flags—missing indexes and suboptimal join patterns. 🔧 What we did: Tuned two critical server-level configurations (one related to MAXDOP, the other to cost threshold for parallelism). Added two well-targeted nonclustered indexes to reduce key lookups and improve seek performance. Made three precise query changes—including replacing scalar UDFs with inline logic and optimizing WHERE clause filters. 🚀 The outcome? The same workload that took minutes now completes in seconds. CPU utilization dropped significantly, and users noticed the difference right away. No hardware upgrade. No magic—just smart tuning. Performance tuning isn’t about throwing everything at the wall. Sometimes, just five well-placed changes can turn a system around. #SQLServer #PerformanceTuning #QueryOptimization #IndexingMatters #DatabaseEngineering #RealWorldSQL

Most systems do not fail because of bad code. They fail because we expect them to scale, without a strategy. Here is a simple, real-world cheat sheet to scale your database in production: ✅ 𝐈𝐧𝐝𝐞𝐱𝐢𝐧𝐠: Indexes make lookups faster - like using a table of contents in a book. Without it, the DB has to scan every row. ��𝐱𝐚𝐦𝐩𝐥𝐞: Searching users by email? Add an index on the '𝐞𝐦𝐚𝐢𝐥' column. ✅ 𝐂𝐚𝐜𝐡𝐢𝐧𝐠: Store frequently accessed data in memory (Redis, Memcached). Reduces repeated DB hits and speeds up responses. 𝐄𝐱𝐚𝐦𝐩𝐥𝐞: Caching product prices or user sessions instead of hitting DB every time. ✅ 𝐒𝐡𝐚𝐫𝐝𝐢𝐧𝐠: Split your DB into smaller chunks based on a key (like user ID or region). Reduces load and improves parallelism. 𝐄𝐱𝐚𝐦𝐩𝐥𝐞: A multi-country app can shard data by country code. ✅ 𝐑𝐞𝐩𝐥𝐢𝐜𝐚𝐭𝐢𝐨𝐧: Make read-only copies (replicas) of your DB to spread out read load. Improves availability and performance. 𝐄𝐱𝐚𝐦𝐩𝐥𝐞: Use replicas to serve user dashboards while the main DB handles writes. ✅ 𝐕𝐞𝐫𝐭𝐢𝐜𝐚𝐥 𝐒𝐜𝐚𝐥𝐢𝐧𝐠: Upgrade the server - more RAM, CPU, or SSD. Quick to implement, but has physical limits. 𝐄𝐱𝐚𝐦𝐩𝐥𝐞: Moving from a 2-core machine to an 8-core one to handle load spikes. ✅ 𝐐𝐮𝐞𝐫𝐲 𝐎𝐩𝐭𝐢𝐦𝐢𝐳𝐚𝐭𝐢𝐨𝐧: Fine-tune your SQL to avoid expensive operations. 𝐄𝐱𝐚𝐦𝐩𝐥𝐞: * Avoid '𝐒𝐄𝐋𝐄𝐂𝐓 *', * Use '𝐣𝐨𝐢𝐧𝐬' wisely, * Use '𝐄𝐗𝐏𝐋𝐀𝐈𝐍' to analyse slow queries. ✅ 𝐂𝐨𝐧𝐧𝐞𝐜𝐭𝐢𝐨𝐧 𝐏𝐨𝐨𝐥𝐢𝐧𝐠: Controls the number of active DB connections. Prevents overload and improves efficiency. 𝐄𝐱𝐚𝐦𝐩𝐥𝐞: Use PgBouncer with PostgreSQL to manage thousands of user requests. ✅ 𝐕𝐞𝐫𝐭𝐢𝐜𝐚𝐥 𝐏𝐚𝐫𝐭𝐢𝐭𝐢𝐨𝐧𝐢𝐧𝐠: Split one wide table into multiple narrow ones based on column usage. Improves query performance. 𝐄𝐱𝐚𝐦𝐩𝐥𝐞: Separate user profile info and login logs into two tables. ✅ 𝐃𝐞𝐧𝐨𝐫𝐦𝐚𝐥𝐢𝐬𝐚𝐭𝐢𝐨𝐧 Duplicate data to reduce joins and speed up reads. Yes, it adds complexity - but it works at scale. 𝐄𝐱𝐚𝐦𝐩𝐥𝐞: Store user name in multiple tables so you do not have to join every time. ✅ 𝐌𝐚𝐭𝐞𝐫𝐢𝐚𝐥𝐢𝐳𝐞𝐝 𝐕𝐢𝐞𝐰𝐬 Store the result of a complex query and refresh it periodically. Great for analytics and dashboards. 𝐄𝐱𝐚𝐦𝐩𝐥𝐞: A daily sales summary view for reporting, precomputed overnight. Scaling is not about fancy tools. It is about understanding trade-offs and planning for growth - before things break. #DatabaseScaling #SystemDesign #BackendEngineering #TechLeadership #InfraTips #PerformanceMatters #EngineeringExcellence

What are the most common performance bugs developers encounter when using databases? I like this paper because it carefully studies what sorts of database performance problems real developers encounter in the real world. The authors analyze several popular open-source web applications (including OpenStreetMap and Gitlab) to see where database performance falters and how to fix it. Here’s what they found: - ORM-related inefficiencies are everywhere. This won’t be surprising to most experienced developers, but by hiding the underlying SQL, ORMs make it easy to write very slow code. Frequently, ORM-generated code performs unnecessary sorts or even full-table scans, or takes multiple queries to do the job of one. Lesson: Don’t blindly trust your ORM–for important queries, check if the SQL it generates makes sense. - Many queries are completely unnecessary. For example, many programs run the exact same database query in every iteration of a loop. Other programs load far too much data that they don’t need. These issues are exacerbated by ORMs, which don’t make it obvious that your code contains expensive database queries. Lesson: Look at where your queries are coming from, and see if everything they’re doing is necessary. - Figuring out whether data should be eagerly or lazily loaded is tricky. One common problem is loading data too lazily–loading 50 rows from A then for each loading 1 row from B (51 queries total) instead of loading 50 rows from A join B (one query total). But an equally common problem is loading data too eagerly–loading all of A, and also everything you can join A with, when in reality all the user wanted was the first 50 rows of A. Lesson: When designing a feature that retrieves a lot of data, retrieve critical data as efficiently as possible, but defer retrieving other data until needed. - Database schema design is critical for performance. The single most common and impactful performance problem identified is missing database indexes. Without an index, queries often have to do full table scans, which are ruinously slow. Another common problem is missing fields, where an application expensively recomputes a dependent value that could have just been stored as a database column. Lesson: Check that you have the right indexes. Then double-check. Interestingly, although these issues could cause massive performance degradation, they’re not too hard to fix–many can be fixed in just 1-5 lines of code, and few require rewriting more than a single function. The hard part is understanding what problems you have in the first place. If you know what your database is really doing, you can make it fast!

One Small Datatype Mistake = 500x More Reads ✍ I ran a simple test in SSMS. Table: dbo.Products Column: ProductID VARCHAR(8) Index on ProductID 200,000 rows Now look at this. 👇 Test 1 - Datatype Mismatch DECLARE @ID INT = 150000; SELECT * FROM dbo.Products WHERE ProductID = @ID; Execution plan result: Clustered Index Scan Implicit conversion warning Higher logical reads Why? Because SQL Server performs an internal conversion: CONVERT_IMPLICIT(...) The column gets wrapped in a function. That makes the predicate non-SARGable. Index Seek disappears. Full Scan happens instead. Same value. Same table. Same index. Different datatype. Test 2 - Matching Datatype DECLARE @ID VARCHAR(8) = '150000'; SELECT * FROM dbo.Products WHERE ProductID = @ID; Execution plan result: Index Seek Lower logical reads Cleaner plan Only one change: the datatype now matches the column definition. Real DBA takeaway: Performance tuning is not just about adding indexes. It is about: • Data type alignment • Reviewing execution plan warnings • Checking for CONVERT_IMPLICIT • Validating Seek vs Scan behavior Small datatype mismatch. Large performance impact. Measure → Correlate → Diagnose → Optimize → Validate. #SQLServer #PerformanceTuning #DBA #ExecutionPlan

Looking at a recent 3-week SQL Server performance project that shows why maintenance matters. Client had a database that was deployed and forgotten. No supervision. Missing indexes that existed on other machines. Poor initial configuration. Here are some before and after numbers after we got stuck into it: - CPU time: 20+ million dropped to 2.5 million (87.5% reduction) - Logical reads: Massive reduction across all query patterns - Duration: Significant improvement in response times - Execution count: Stayed stable (same workload, better performance) Here's what happened week by week: 𝗪𝗲𝗲𝗸 1-2: Standard performance tuning targeting top resource-consuming queries. But changes kept getting rolled back overnight. Indexes disappeared. Everything reverted to original state after application republishing. 𝗔𝘂𝗴𝘂𝘀𝘁 6-7𝘁𝗵: Fixed the persistence issue. Changes stuck permanently. 𝗪𝗲𝗲𝗸 3: Found the root cause was missing non-clustered index replication in the transactional replication setup. The replication was only copying data while skipping all non-clustered indexes, meaning the target environment lacked the critical performance improvements that existed on the publisher (source system). After fixing that foundation, we identified another optimization layer that delivered 100-200% additional improvement beyond the initial gains (approx one week later). Technical approach: - Identified top queries by resource consumption - Worked through them systematically, one by one - Fixed logical reads bottlenecks (storage subsystem is typically the biggest constraint in SQL Server) - Ensured persistent deployment of optimizations The result? Client can either handle way more concurrent users or move to a cheaper server and cut cloud costs. This is what happens when you treat SQL Server like infrastructure instead of abandoning it after deployment. Your database needs the same attention you give your application code. Performance tuning works when the fundamentals are solid first.

A few years back, I was working on a 𝐫𝐞𝐩𝐨𝐫𝐭𝐢𝐧𝐠 𝐩𝐢𝐩𝐞𝐥𝐢𝐧𝐞. One query was taking over 𝟐 𝐡𝐨𝐮𝐫𝐬 𝐭𝐨 𝐫𝐮𝐧. The team had accepted it as “normal.” But I couldn’t stop thinking: Why should a simple report take so long? So I dug in. 🔍 I looked at the execution plan → full table scans everywhere. ⚡ Replaced SELECT * with only the required columns. ⚡ Added the right indexes on JOIN and WHERE columns. ⚡ Rewrote subqueries into CTEs. ⚡ Limited rows early instead of filtering late. The result? ⏱ From 𝟐 𝐡𝐨𝐮𝐫𝐬 → 𝟑 𝐦𝐢𝐧𝐮𝐭𝐞𝐬. That day I learned an important lesson: 👉 Writing SQL is easy, but writing 𝐨𝐩𝐭𝐢𝐦𝐢𝐳𝐞𝐝 𝐒𝐐𝐋 is an art. Every millisecond saved matters—especially when your queries run millions of times a day. So next time you face a slow query, don’t accept it. Analyze. Tune. Optimize. Because in SQL, small changes can create a massive impact. ⏩ 𝐉𝐨𝐢𝐧 𝐭𝐨 𝐥𝐞𝐚𝐫𝐧 𝐃𝐚𝐭𝐚 𝐒𝐜𝐢𝐞𝐧𝐜𝐞 & 𝐀𝐧𝐚𝐥𝐲𝐭𝐢𝐜𝐬: https://t.me/LK_Data_world 💬 If you found this PDF useful, like, save, and repost it to help others in the community! 🔄 📢 Connect with Lovee Kumar 🔔 for more content on Data Engineering, Analytics, and Big Data. #SQL #DataEngineering #Optimization #DatabasePerformance #BigData

So much of database performance boils down to how a system handles temporal and spatial locality. It’s hard to get both right for OLTP, analytics, and time-series workloads simultaneously. Thus, we have many purpose-built database systems. Temporal locality: When data is accessed, it’s likely to be accessed again the near future. Spatial locality: When data is accessed, it’s likely that “nearby” records will be accessed as well. Databases cache values in-memory when read. When the data is larger than RAM, we can’t values this in RAM forever, so the eviction algorithm is critical to optimize temporal locality. We want to keep it long enough to make repeated reads fast, but if we keep it too long it could hurt performance elsewhere. The next layer is spatial locality. Reading a row is often a predictor of future row-reads. If rows are frequently scanned in sequences, reading row N likely requires reading row N+1. If rows in table A are often joined with a rows in table B, we use this as a predictor to keep the related rows in-memory. We can’t build one system with a single data layout that optimizes for all workloads, so we need specialized databases for each. Or one really bloated system, like Postgres + 10 supporting extensions.

𝐌𝐢𝐬𝐜𝐨𝐧𝐜𝐞𝐩𝐭𝐢𝐨𝐧 𝐢𝐧 𝐒𝐐𝐋 - 𝐏𝐚𝐫𝐭 5 𝐈𝐧𝐝𝐞𝐱𝐞𝐬 𝐚𝐥𝐰𝐚𝐲𝐬 𝐡𝐞𝐥𝐩𝐟𝐮𝐥 𝐟𝐨𝐫 𝐩𝐞𝐫𝐟𝐨𝐫𝐦𝐚𝐧𝐜𝐞 𝐭𝐮𝐧𝐢𝐧𝐠. Indexes can significantly improve performance, but they're not always beneficial in every situation. 1. Indexes Can Hurt Write Performance 2. Indexes Consume Storage Space 3. Indexes need maintainace Indexes Need to Be Used Strategically. There are 4 fundamental Principles that you need to bear in mind: 1. The intent of indexing is to find the data you need with the minimum use of critical resources (which may be disk I/Os for relatively small systems, CPU for large systems). 2. High precision is important – the ideal use of indexes is to avoid visiting table blocks that don’t hold useful data. 3. Creating precise indexes for every query requirement leads to a lot of real-time maintenance when you modify data so you need to balance the resources needed for DML against the resources for queries. 4. Oracle offers many ways to minimise the work you need to do to use and to maintain indexes. Other than that as a database designer, we have to take care of few points like below: 𝐐𝐮𝐞𝐫𝐲 𝐏𝐚𝐭𝐭𝐞𝐫𝐧𝐬: Analyze the types of queries that your application frequently executes. Identify the most common search criteria, join conditions, and sorting requirements. Create indexes that support these query patterns to improve query performance. 𝐒𝐞𝐥𝐞𝐜𝐭𝐢𝐯𝐢𝐭𝐲: Choose index columns that have high selectivity, meaning they have many distinct values. High selectivity columns help the query optimizer to narrow down the search space efficiently and reduce the number of rows to scan. 𝐂𝐚𝐫𝐝𝐢𝐧𝐚𝐥𝐢𝐭𝐲: Consider the cardinality of index columns, which refers to the number of distinct values in a column. Columns with high cardinality are good candidates for indexing because they provide more granularity and discrimination for query optimization. 𝐃𝐚𝐭𝐚 𝐌𝐨𝐝𝐢𝐟𝐢𝐜𝐚𝐭𝐢𝐨𝐧 𝐎𝐯𝐞𝐫𝐡𝐞𝐚𝐝: Be mindful of the overhead associated with maintaining indexes during data modification operations (e.g., INSERT, UPDATE, DELETE). Each index incurs overhead during data modifications, so avoid creating unnecessary indexes that could degrade performance. 𝐈𝐧𝐝𝐞𝐱 𝐂𝐨𝐦𝐩𝐨𝐬𝐢𝐭𝐢𝐨𝐧: Choose the right combination of index columns based on the queries your application executes. Composite indexes can be more efficient than single-column indexes for queries that involve multiple columns in the WHERE clause, join conditions, or ORDER BY clauses. 𝐈𝐧𝐝𝐞𝐱 𝐌𝐚𝐢𝐧𝐭𝐞𝐧𝐚𝐧𝐜𝐞: Regularly monitor and maintain indexes to ensure optimal performance. Evaluate index usage, identify unused or redundant indexes, and consider adjusting or removing them as needed. Rebuild or reorganize indexes periodically to reclaim space and optimize index performance. #database #dataanalyst #learningsql Join our Free whatsapp group for learning: https://lnkd.in/eb3GxPN3 Follow Vishal Jaiswal, PMP®