Daniel L.’s Post

I used to think indexes were the 𝗲𝗮𝘀𝗶𝗲𝘀𝘁 way to 𝗳𝗶𝘅 𝘀𝗹𝗼𝘄 𝗾𝘂𝗲𝗿𝗶𝗲𝘀. Query slow? 👉 Add an index. Another query slow? 👉 Add another index. For a while, it actually works. ⚡ Queries become faster. 📊 Dashboards load quickly. Everyone is happy. But something interesting starts happening later. 🐢 Writes begin to slow down. INSERT, UPDATE, and DELETE operations take longer than expected. And the reason is simple: Every time data changes, the database must also update every related index. So if a table has too many indexes, each write operation becomes heavier. ⚖️ That’s the 𝘁𝗿𝗮𝗱𝗲-𝗼𝗳𝗳 many developers discover a bit late. Indexes are powerful, but creating them blindly can introduce new problems. Some common side effects: ✅ 𝗣𝗿𝗼𝘀 of adding indexes 🔎 Faster search and filtering (WHERE) 🔗 Faster joins between tables 📈 Better performance for sorting and grouping 🗂️ Large datasets become manageable ⚠️ 𝗖𝗼𝗻𝘀 of adding indexes 𝗯𝗹𝗶𝗻𝗱𝗹𝘆 🐌 Slower inserts, updates, and deletes 💾 Extra disk space for each index ⚙️ More work for the database to maintain them ❓ Some indexes may never even get used That’s why indexing is less about adding more, and 𝘮𝘰𝘳𝘦 𝘢𝘣𝘰𝘶𝘵 𝘢𝘥𝘥𝘪𝘯𝘨 𝘵𝘩𝘦 𝘳𝘪𝘨𝘩𝘵 𝘰𝘯𝘦𝘴. 𝘼 𝙜𝙤𝙤𝙙 𝙞𝙣𝙙𝙚𝙭 𝙪𝙨𝙪𝙖𝙡𝙡𝙮 𝙘𝙤𝙢𝙚𝙨 𝙛𝙧𝙤𝙢 𝙪𝙣𝙙𝙚𝙧𝙨𝙩𝙖𝙣𝙙𝙞𝙣𝙜 𝙝𝙤𝙬 𝙩𝙝𝙚 𝙙𝙖𝙩𝙖 𝙞𝙨 𝙖𝙘𝙩𝙪𝙖𝙡𝙡𝙮 𝙦𝙪𝙚𝙧𝙞𝙚𝙙. 🧠 Databases reward thoughtful design. Blind optimization rarely stays optimal for long. #realMoneyLearnings #Databases #MySQL #SQL #DatabasePerformance #BackendEngineering #SoftwareEngineering #SystemDesign #PerformanceOptimization #DatabaseIndexes #LearningInPublic

I was debugging a slow query. Turns out… the database was just taking the scenic route 🌳 Everything looked fine. Indexes? Present. Query? Clean. Still… latency said “not today.” Went one layer deeper. Not the query. Not the data. 👉 The tree underneath it. Databases like MySQL and MongoDB don’t magically fetch data. They walk a path. And if that path is messy… your query is basically sightseeing before doing real work. 💡 What actually fixed it: Not rewriting the query 10 times. Instead: Rebuilding / optimizing indexes (hello B-Trees 👀) Fixing index choice based on access patterns Dropping useless indexes that were bloating traversal Checking if data distribution was skewing the tree ⚙️ The shift: I stopped asking: ❌ “Is my query efficient?” and started asking: ✅ “Is my data structure forcing extra work?” Because in production: Bad query → obvious problem Bad tree → silent performance killer And yeah… Sometimes the fix isn’t clever code. It’s just admitting: 👉 your tree grew wild and needs pruning. Next time your query is slow, don’t just optimize SQL. Trim the tree—you might save milliseconds (and your weekend). #BackendEngineering #SystemDesign #PerformanceEngineering #Databases #DSA #SoftwareEngineering

How Do You Optimize a Query with Multiple Joins, Filters, and Pagination? 🤔 If you think pagination will optimize DB performance, then bro you are 𝘄𝗿𝗼𝗻𝗴 — because pagination is only applied at the end of the filtration and joins. That does not really optimize the query. It only optimizes the DB network bandwidth, nothing else. 📡 In this case, we need to carefully analyze the query and database structure. 🧠 If we join all tables first and then apply filters and pagination, let’s see what the database actually does.   • First, it joins the tables and creates a temporary table in memory. 🗂️   • Then it applies filters. 🔎   • And at the end, it applies pagination. 📄 All the memory and CPU utilization happens during joins and filters. ⚙️ If there is a GROUP BY clause, it will require even more processing power. 📊 If the dataset is too large, our DB processor can 𝗲𝗮𝘀𝗶𝗹𝘆 𝘀𝗽𝗶𝗸𝗲. 📈 Now the question is: 𝗵𝗼𝘄 𝗱𝗼 𝘄𝗲 𝗼𝗽𝘁𝗶𝗺𝗶𝘇𝗲 𝗶𝘁? 🤔 If our DB design and query allow step-by-step filtering, like:   • First apply filters on the user table   • Then apply filters on another table   • And so on... Then we can reduce the join data stage by stage by using the 𝗪𝗜𝗧𝗛 clause, which is known as a 𝗖𝗼𝗺𝗺𝗼𝗻 𝗧𝗮𝗯𝗹𝗲 𝗘𝘅𝗽𝗿𝗲𝘀𝘀𝗶𝗼𝗻 (𝗖𝗧𝗘). 🧩 CTEs help reduce the temporary table size during joins, and our filters can be applied faster. ⚡ Then the next stage joins only with the already filtered data. Finally, we can apply pagination. 📄 If a join is only required for data viewing and not for filtering, we can also apply that 𝗷𝗼𝗶𝗻 𝗮𝗳𝘁𝗲𝗿 𝗽𝗮𝗴𝗶𝗻𝗮𝘁𝗶𝗼𝗻, when the dataset is already very small. This also helps optimize our query. 🚀 I have been using the WITH clause (CTE) in many of my large queries, and it has helped me a lot in improving query performance. 💡 #realMoneyLearnings #Databases #SQL #MySQL #DatabasePerformance #QueryOptimization #BackendEngineering #SoftwareEngineering #SystemDesign #TechLearning #LearningInPublic

A question for every dev who's ever designed a database table: Did you design it for 10,000 rows or 10 million? Because when schemas are designed for the demo. For the MVP. For "let's just ship it and optimize later." And "later" arrives as a 3 AM P1 page, 18 months down the road, when the table that "works fine" has grown 1000x and suddenly nothing works fine. We call these the haunting patterns. Schema decisions that feel harmless at small scale and become structural nightmares at large scale. Data Drop #6 covers the big three: → UUIDs as primary keys — Random values fragment your B-tree indexes. At 500M rows, your index is bloated, your writes scatter across random pages, and your cache hit ratio craters. Sequential IDs exist for a reason. → "Just make it nullable" — The path of least resistance at design time. The source of a thousand bugs at query time. NULL doesn't equal NULL. Your aggregations silently skip rows. Your joins produce unexpected results. Nullable should be a conscious choice, not a default. → The EAV trap — Entity-Attribute-Value: the schema pattern that says "I don't want to commit to a data model." Congratulations, you now have a key-value store with the performance of a relational database and the flexibility of neither. Design for the table size you're going to have. Not the one you have today. Data Drop #6. Day 6 of 23. #AprilDataDrops #PostgreSQL #DataDrop6 #SchemaDesign #Database #Performance #OpenSourceDB OpenSource DB | Lahari Giddi

Everyone tells you to use DB indexes to speed up your queries. Nobody tells you how they can quietly ruin your database performance. 📉 Let’s talk about the dark side of Clustered and Non-Clustered Indexes, and when you absolutely should NOT use them. 👇 📕 The Clustered Index Trap (Reminder: This physically sorts your data on the disk). 🚫 When NOT to use it: • On a GUID/UUID: Since GUIDs are random, inserting new rows causes "Page Splits." The database constantly has to shuffle physical data around to make room. It destroys write performance and fragments your disk. • On frequently updated columns: If you update a clustered key, the database physically moves the entire row to a new location. Ouch. • On "wide" columns (like VARCHAR(500)): Every Non-Clustered index uses the Clustered key as its "pointer." If your Clustered key is massive, ALL your other indexes become massive too. 📘 The Non-Clustered Index Trap (Reminder: This is a separate lookup list, like a book's index). 🚫 When NOT to use it: • The "Index Everything" approach: Indexes speed up SELECT queries, but they punish INSERT/UPDATE/DELETE queries. If a table has 15 non-clustered indexes, every single time you insert a row, the database has to update 16 different places. • On Low-Cardinality columns: Don't put an index on a boolean column like IsActive or Gender. The SQL Query Optimizer will look at it, realize it's faster to just scan the whole table, and completely ignore your index. You're just wasting disk space! 💰 The Golden Rule: Indexes are like salt. 🧂 The right amount makes everything better. Too much ruins the whole dish. #SQL #DatabaseOptimization #SoftwareEngineering #TechTips #Backend

Most engineers think table partitioning is an "enterprise" feature. In reality, it's often the simplest fix for your slow time-series queries. If you’re logging events or anything time-based in PostgreSQL and everything lives in one giant table, you’re forcing every query to fight through data it doesn’t care about. Even with indexes. In the example from the video, the base table is `events` with: - `id` (generated) - `created_at` (timestamp) - `user_id` - `event_id` The only difference from a normal table definition is this line: ```sql PARTITION BY RANGE (created_at) ``` From there, you create monthly partitions: ```sql CREATE TABLE events_2024_01 PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'); CREATE TABLE events_2024_02 PARTITION OF events FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'); ``` When you insert 20,000+ rows per month and query with a `WHERE created_at BETWEEN ...`, PostgreSQL can do partition pruning: it only touches the relevant child tables instead of scanning the entire dataset. The key takeaway: if your queries are almost always time-bounded, a properly partitioned table can give you a bigger win than another round of index tweaking. I walk through the exact SQL and show how partition pruning changes what PostgreSQL actually scans here: https://lnkd.in/ekhNRkss

A single index can turn a 5-second query into 50ms. Let that sink in. Most of us focus on writing better queries… but the real magic often happens under the hood — in how your database finds the data. Here’s the simple truth: 👉 An index is like a shortcut. Instead of scanning an entire table, the database jumps straight to what it needs. Now let’s take it one step further: 🔹 Composite Indexes (the real power move) Instead of indexing just one column, you index multiple columns together. Think of it like sorting a book first by genre, then by author. If your query filters by both — boom, lightning fast results. Example: If your query often does: WHERE country = 'BD' AND status = 'active' A composite index on (country, status) can make a huge difference. ⚡ Why this improves speed so much Without an index: → Database scans every row (slow 😴) With the right index: → Database narrows down instantly (fast ⚡) And with composite indexes: → Even fewer rows to scan → Even faster execution But here’s the catch most people miss: Indexes are not “set and forget.” Too many indexes can actually slow down writes (INSERT/UPDATE). The key is balance — index what you query often, not everything. 💡 Final thought: If your app feels slow, don’t just blame the query. Look at your indexes — they might be the hidden lever you’re missing. #Database #Backend #Performance #SQL #EngineeringInsights

𝙄 𝙪𝙨𝙚𝙙 𝙩𝙤 𝙩𝙝𝙞𝙣𝙠 𝙞𝙣𝙙𝙚𝙭𝙚𝙨 𝙞𝙣 𝙙𝙖𝙩𝙖𝙗𝙖𝙨𝙚𝙨 𝙬𝙚𝙧𝙚 𝙨𝙞𝙢𝙥𝙡𝙚: 👉 "𝙏𝙝𝙚𝙮 𝙞𝙢𝙥𝙧𝙤𝙫𝙚 𝙧𝙚𝙖𝙙 𝙥𝙚𝙧𝙛𝙤𝙧𝙢𝙖𝙣𝙘𝙚" 👉 "𝙏𝙤𝙤 𝙢𝙖𝙣𝙮 𝙞𝙣𝙙𝙚𝙭𝙚𝙨 𝙨𝙡𝙤𝙬 𝙙𝙤𝙬𝙣 𝙬𝙧𝙞𝙩𝙚𝙨" That’s it… or so I thought. While working on a recent feature, I realized there’s much more depth to how indexes actually work — especially *how you define them*. One key thing I learned from my lead is that: The order of columns in an index matters A LOT. Example: CREATE INDEX idx_abc  ON table(col_a, col_b, col_c); This works efficiently for: ✔ WHERE col_a = ? ✔ WHERE col_a = ? AND col_b = ? ✔ WHERE col_a = ? AND col_b = ? AND col_c = ? But surprisingly: ❌ WHERE col_b = ? ❌ WHERE col_c = ? 👉 Because indexes follow a left-to-right (prefix) rule. --- Another mistake I used to overlook: If most queries are like: WHERE col_a = ? AND col_b = ? But your index is: (col_z, col_a, col_b) 👉 That index might not be useful at all. --- 💡 Big takeaway for me: Indexes are not just about adding them. They’re about designing them based on query patterns. - What columns are filtered most? - In what order are they used? - How selective are they? --- Also learned: Every index comes with a tradeoff. More indexes = ⬆ Faster reads ⬇ Slower writes (INSERT / UPDATE / DELETE need to maintain them) --- Still learning, but this completely changed how I think about performance. #Database #SQL #Indexes #QueryOptimization #Backend #SoftwareEngineering #LearningInPublic #TechJourney

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