DB SQL Learning: Foreign Keys, Joins, Indexing, Transactions, and ACID

This week was all about going beyond just “learning SQL”. I didn’t just study PostgreSQL — I actually built with it. Here’s what I worked on: 𝗖𝗼𝗿𝗲 𝗰𝗼𝗻𝗰𝗲𝗽𝘁𝘀 • Joins (INNER, LEFT, RIGHT, FULL) • Indexing & query optimization • Transactions & ACID properties 𝗔𝗱𝘃𝗮𝗻𝗰𝗲𝗱 𝗦𝗤𝗟 • CTEs (including recursive) • Window functions (ROW_NUMBER, RANK, LAG) • CASE, COALESCE, ROLLUP Most importantly — applied learning 𝗜 𝗱𝗲𝘀𝗶𝗴𝗻𝗲𝗱 𝗿𝗲𝗮𝗹-𝘄𝗼𝗿𝗹𝗱 𝗱𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝘀𝘆𝘀𝘁𝗲𝗺𝘀: • 𝗜𝗻𝘀𝘁𝗮𝗴𝗿𝗮𝗺 𝗧𝗵𝗿𝗶𝗳𝘁 𝗦𝘁𝗼𝗿𝗲 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 • 𝗙𝗶𝘁𝗻𝗲𝘀𝘀 𝗖𝗼𝗮𝗰𝗵𝗶𝗻𝗴 𝗣𝗹𝗮𝘁𝗳𝗼𝗿𝗺 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 Worked on: • Table relationships (1-1, 1-M, M-M) • Foreign keys & constraints • Structuring data like real applications Big realization: SQL isn’t just about writing queries — it’s about thinking like a system designer. Still a long way to go, but this week felt like a solid step forward. You can also check the two DB designs in my 𝗚𝗶𝘁𝗛𝘂𝗯 𝗿𝗲𝗽𝗼: https://lnkd.in/gHsgtx4W Would love feedback on my DB designs. Thanks Hitesh Choudhary Piyush Garg Akash Kadlag Jay Kadlag Suraj Kumar Jha Chai Aur Code #SQL #PostgreSQL #DatabaseDesign #BackendDevelopment #LearningInPublic

A SQL query was freezing our system — and the root cause surprised us Two years ago, I faced a production issue that I never forgot. A critical query, used by clients to check their history, was freezing the entire system. The table had more than 2 million rows. At first, the assumption was simple: “We need to upgrade the database infrastructure.” But even after that, the problem persisted. So I sat down and started analyzing the query in detail. After some time debugging, I found the issue: The query had an ORDER BY DESC. Even with indexes and date filters (BETWEEN), this forced the database to sort a huge dataset, causing the slowdown. We removed the ORDER BY. And suddenly, the query became fast again and the system stopped freezing. --- What I learned That experience completely changed how I look at SQL performance. Sometimes: - It is not about infrastructure - It is not about scaling - It is about understanding what the database is really doing --- So I built a project to study this I recreated similar scenarios to measure the impact of: - Indexing strategies - Query structure - Filtering patterns Results: - Full table scan vs indexed query: 21.36ms → 5.10ms (76.1%) - LIKE wildcard vs exact match: 37.06ms → 1.99ms (94.6%) - JOIN slow vs optimized: 19.12ms → 14.56ms (23.8%) --- Project: https://lnkd.in/dCn6VMYq --- Final takeaway Sometimes the biggest performance issue is just a small detail in your query.

🚀 Just Published My New Blog on MySQL! As part of my Data Science journey, I recently worked on a blog explaining MySQL commands using flowcharts — and it really helped me understand concepts more clearly. Instead of memorizing queries, I focused on understanding the flow and purpose behind each SQL command category: 🔹 DDL – Structure 🔹 DML – Data operations 🔹 DCL – Access control 🔹 TCL – Transactions 🔹 DQL – Queries 📊 I also included a simple flowchart and real examples to make it beginner-friendly. Big thanks to my mentor Koduri Srihari and trainer Manohar Chary .V for their guidance and support throughout this learning process 🙌 Grateful to Innomatics Research Labs for providing such structured learning opportunities. 🔗 Read the full blog here: https://lnkd.in/gXVnDhYN Would love to hear your feedback! #MySQL #SQL #DataScience #LearningJourney #BeginnerFriendly #Database #Growth

⚡️Shipped This Week More SQL functions. Pipelines got more observable. Memory usage went down. And the Feldera community keeps showing up. Here are some highlights from this week: → Postgres CDC input connector: You can now connect Feldera directly to Postgres via logical replication. Point the connector at your database, and it handles the full table snapshot first, then switches seamlessly to continuous WAL streaming - crash-safe, so if anything goes wrong, the pipeline resumes exactly where it left off with no data loss. Built by Feldera OSS contributor Mohammed Ali (thank you!). → RANK and DENSE_RANK in SQL: Two of the most-requested SQL window functions are now in Feldera; like everything else we do, they are evaluated incrementally. → Pipeline monitoring events: Every pipeline now keeps a continuous event history of up to 5 days of status changes, queryable from the API, CLI, UI or Python SDK. → Control-plane scalability: The extremes that our customers take our software to is truly amazing sometimes. Therefore, we also improved memory usage in the control-plane. This makes for a smoother experience when you want to orchestrate lots of Feldera pipelines. All of this is live in our sandbox right now: try.feldera.com. No infrastructure or setup required.

Recently ran through LeetCode’s SQL 50 to rigorously brush up on my database logic. It’s one thing to write a query that returns the right answer; it’s another to write one that scales efficiently under the hood. Here are a few core technical insights and optimizations I found most valuable to revisit: ⚡ EXECUTION & OPTIMIZATION • Favor GROUP BY over Window Functions: Window functions compute values per row without collapsing the dataset, leading to massive memory duplication in large tables. GROUP BY efficiently compresses data into unique combinations first. • Eliminate Multi-Pass Scans: Relying on nested IN and MAX() subqueries forces the database engine to evaluate and scan the same table multiple times. • The "Aggregate-Sort-Limit" Pattern: The most computationally efficient way to find a top record is often to compute the metric, sort it, and slice the top off (GROUP BY ... ORDER BY ... LIMIT 1). You can also leverage your ORDER BY clause to handle secondary tie-breakers natively in a single pass. 🧠 LOGIC & EDGE CASES • The "Aggregate Hack" for NULLs: An empty set is not the same as a NULL value. If an API requires a 1-row NULL result instead of an empty table, wrapping the target in an aggregate function like MAX() or MIN() forces the database to return a single NULL row even if no matches are found. • Rate = Average: Calculating a binary success rate is mathematically identical to taking the average of 1s and 0s. There's rarely a need to count the numerator and denominator separately. • Know Your Ranking Functions: DENSE_RANK() is crucial when finding top unique values (like salaries) because it assigns the same rank to ties without skipping subsequent numbers (1, 1, 2). Using RANK() (1, 1, 3) or ROW_NUMBER() will break your logic on ties. A great exercise in writing cleaner, more highly optimized SQL. Next up: tackling the Advanced SQL 50 track. 📊 #SQL #DataScience #DataEngineering #LeetCode #DatabaseOptimization #PostgreSQL #SQL50

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

The first PostgreSQL 19 release notes draft just came out and it's big one. It looks like version 19 will have native graph SQL Property Graph Queries (SQL/PGQ). It's the part of the SQL:2023 ISO standard and provides the ability to efficiently represent and query graph data or the Property Graph Query language (PGQ). That mean in practice that we will ability to use declarative language to declare graph operations (from definitions, traversals, shorterest paths, etc) without having to resort to those ugly non declarative recursive CTEs. Just, you know, declare what you want from your graph data and that's it. Or at least that's what I'm reading from these notes. The development and advances in PostgreSQL are truly amazing. https://lnkd.in/dbzRKMQz

Speed is a feature. Today, I optimized my Task Management System’s data layer by implementing Advanced Database Indexing. The Performance Breakdown: Sequential vs. Index Scans: I learned how PostgreSQL searches for data and why "Sequential Scans" are the enemy of scalability. B-Tree & Composite Indexes: I moved beyond single-column indexes to "Composite Indexes," allowing the database to filter by User ID and Task Status simultaneously in milliseconds. Prisma Schema Optimization: I learned how to define indexes directly in my Prisma models, keeping my infrastructure-as-code clean and version-controlled. Query Planning: I explored using the EXPLAIN ANALYZE command to actually see the "Execution Plan" and prove that my indexes are being used. The Aha! Moment: Adding an index is like giving your database a map instead of a blindfold. It is one of the most impactful things you can do to ensure your application stays fast as your user base grows from 10 to 10,000. We are building for scale, not just for today. #PostgreSQL #Prisma #DatabaseOptimization #100DaysOfCode #BackendEngineering #SQL #SoftwarePerformance #Day94 #Theadityanandan #Adityanandan

Many developers model everything as a table in PostgreSQL without knowing there's a feature that can make queries faster and simplify the database structure in certain cases: Composite Types. The idea is simple: you define a structured data type and use it as a column in any table When it makes sense to use: - The data has no identity of its own, it only exists alongside the parent record - You'll never query that data in isolation - You don't need history, auditing or traceability - It's a fixed, immutable value that just enriches the main row When it doesn't make sense and a 1:1 table is better: - The data can be referenced by other tables - You need history (the address changed and you want to keep the previous one) - It will grow over time and get new columns - Other parts of the application need to access that data directly The main tradeoff is: Composite Type eliminates the JOIN and makes reads faster, but you give up flexibility. If requirements change and that data needs to become its own entity, the migration is a pain. *If you use Prisma, pay attention: it doesn't natively map composite types, it treats the column as `Unsupported`. To read the data, you need to unpack the fields directly in SQL via `$queryRaw`. The rule I use to decide: if the data is a value that describes something, make it a type. If the data is an entity with a life of its own, make it a table. Follow me for more technical posts like this. #PostgreSQL #SQL #Database #Backend #FullStack #DatabaseDesign #Prisma

Ever wondered what happens when a SQL query runs? Let me tell you . That “simple” SELECT isn’t simple at all. SELECT * FROM users WHERE id = 17; Looks straightforward. Under the hood in PostgreSQL, it’s a four-stage decision engine. *1. Parse* PostgreSQL breaks your SQL into an internal structure, validates syntax, and resolves table and column names. Get this wrong and the query never runs. *2. Rewrite* Before execution, PostgreSQL applies rules and view transformations. Your query might already be reshaped before the planner even sees it. *3. Plan* This is where PostgreSQL earns its reputation. It weighs multiple execution paths: Index Scan, Sequential Scan, Bitmap Heap Scan. It picks the “cheapest” option based on table stats, row estimates, and data distribution. Cheapest ≠ fastest. It’s just what the model predicts will cost least in I/O and CPU. *4. Execute with MVCC* Now it finally touches data. But it also checks visibility. A row can exist on disk and still be invisible to you if your transaction shouldn’t see it yet. That’s MVCC in action. *The part most engineers overlook:* This entire flow happens for every SELECT. Every time. So when performance tanks, it’s usually not the query. It’s the context: - Outdated statistics → wrong plan - Poor indexing → wrong scan - Skewed data → bad estimates PostgreSQL isn’t just executing your query. It’s deciding _how_ to execute it. And that decision determines whether your query runs in milliseconds or minutes. What’s the most surprising query plan you’ve seen in production? #PostgreSQL #Databases #Performance #QueryTuning