Nitin Rawat’s Post

"Built a complete Grocery Delivery DB in PostgreSQL — 6 tables, real data, 25 SQL queries from basic to advanced. Sharing for anyone learning SQL! Honestly? I don't know everything yet. Some queries I wrote myself. Some I struggled with. Some I took help to understand. But that's exactly where I am right now — learning, practicing, and being consistent. This document has all 25 queries sorted Easy → Hard, with the schema and everything clean. #SQL #PostgreSQL #DataAnalytics #LearningInPublic #SQLPractice #DataAnalyst

Find the query consuming the most total time on your database. SELECT left(query, 80) AS query_preview, calls, round(total_exec_time::numeric / 1000, 2) AS total_seconds, round(mean_exec_time::numeric, 2) AS avg_ms, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; The key insight: sort by total_exec_time, not mean_exec_time. A query averaging 2ms but called 5 million times consumes 10,000 seconds of database time. A query averaging 500ms called twice consumes 1 second. The first query is 10,000x worse for your database, even though it looks fast. This is the single most common mistake I see in query monitoring. People optimise the slowest individual query when they should be optimising the query with the highest total cost. If pg_stat_statements isn't enabled yet: shared_preload_libraries = 'pg_stat_statements' Requires a restart, but it's the single most impactful thing you can do for PostgreSQL observability. Every production database should have it. Run this query. The top result will probably surprise you. #PostgreSQL #Database #QueryOptimization #Performance #SQL

𝗗𝗮𝘆 𝟭𝟬 - 𝗧𝗢𝗣 / 𝗟𝗜𝗠𝗜𝗧 𝐄𝐯𝐞𝐫𝐲 𝐝𝐚𝐭𝐚 𝐩𝐫𝐨𝐟𝐞𝐬𝐬𝐢𝐨𝐧𝐚𝐥 𝐞𝐯𝐞𝐧𝐭𝐮𝐚𝐥𝐥𝐲 𝐦𝐞𝐞𝐭𝐬 𝐨𝐧𝐞 𝐥𝐚𝐧𝐠𝐮𝐚𝐠𝐞: 𝐒𝐐𝐋 TOP (SQL Server) or LIMIT (MySQL, PostgreSQL) is used to control how many rows a query returns. It’s helpful when you want to preview a small portion of large datasets, check the first few records, or quickly inspect your data without fetching everything. #SQL #DataSkills #DataAnalytics #LearningInPublic #THE_Africa_Idealist #30DaysOfSQL #30DaysSQLCards

Partial Index in PostgreSQL. The main mistake with partial indexes is simple: 👉 You create the index… but your queries don’t match it. 👉 PostgreSQL will ignore a partial index if it can’t guarantee that all required rows are inside it. For example. If your index is: WHERE is_active = true then your query must also include: AND is_active = true It’s also important to understand: Conditions like: WHERE is_active != true or WHERE is_active = false Will NOT use this index. Because they target a different subset of data. Otherwise, PostgreSQL will ignore the index. That’s why even a “correct” index can behave like it doesn’t exist. Think of a partial index as a filtered dataset. It’s not a general-purpose index - it’s optimized for a specific access pattern. If your query doesn’t follow that pattern - no performance gain. When to use it: - large table - small, frequently accessed subset - consistent query pattern (the filter condition is always present and rarely changes) A partial index is only effective when your queries follow the same condition it was built on❗ #PostgreSQL #SQL #DatabaseOptimization #Performance #Indexing #Backend #SoftwareEngineering

💡 PostgreSQL column limits — let’s talk real experience PostgreSQL tables have a column limit — usually somewhere between ~250 and 1600 columns, depending on the data types. But honestly… most well-designed tables shouldn’t get anywhere close to that 👀 Huge tables can mean: Poor schema design Harder maintenance Performance headaches Now I’m curious 👇 What’s the highest number of columns you’ve ever had in a table? Was it intentional… or did things just get out of hand? 😅 Drop your experience 👇 let’s compare notes. #PostgreSQL #DatabaseDesign #Backend #SoftwareEngineering #DataModeling

🚀 PostgreSQL — WAL & Crash Recovery 🔥 Today was a game-changer. I moved beyond basics and got hands-on with one of the core pillars of PostgreSQL — Write-Ahead Logging (WAL). 💡 What I learned: ✅ How PostgreSQL writes data safely using WAL 👉 First writes to WAL → then to actual data files ✅ WAL segments (16MB each) and how they grow under load 👉 Generated multiple WAL files by inserting large data ✅ Real-time WAL observation from pg_wal directory ✅ Forced WAL switch using: SELECT pg_switch_wal(); ✅ Most importantly — simulated a real crash 😈 👉 Used kill -9 to abruptly stop PostgreSQL And guess what? 🔥 PostgreSQL recovered EVERYTHING using WAL 💥 This is called Crash Recovery (REDO process) 📊 Key Takeaways: WAL ensures data durability Crash does NOT mean data loss PostgreSQL replays WAL to restore consistency Old sessions may drop after crash (normal behavior) 🎯 This felt like real production-level DBA work, not just theory. 📌 Learning by doing hits different. Every command, every failure, every recovery = real understanding. #PostgreSQL #DBA #Database #LearningInPublic #CrashRecovery #WAL #Backend #TechJourney

Relational databases are the foundation of SQL systems. Understanding Primary Keys and Foreign Keys is essential for structuring and analyzing data. Swipe through this carousel to see how these concepts work in PostgreSQL and BigQuery. #SQL #RelationalDatabases #DataAnalytics

PostgreSQL Composite Index. Your composite index is NOT slow. Your query is. Most developers create composite indexes and expect magic. But one small mistake — and the database stops using it the way you think. This is what actually happens: 👉 The index is ordered in one direction, it can efficiently handle only one range condition everything after that becomes filtering work. 👉 Add a second independent range — and you break the index. The database will: 1. Scan more data. 2. Filter rows again and hit the table in random order. Result: 🫠 slower queries, higher load, wasted performance. Composite indexes don’t make queries fast. Correct queries do. 🔥 #SQL #Databases #Performance #BackendDevelopment #SoftwareEngineering #mistakes #resolve

SQL Fundamentals Series (PostgreSQL Edition) — Part 8 When working with grouped data, filtering becomes slightly different. Earlier, we used the WHERE clause to filter rows. But once you introduce GROUP BY, filtering must happen after aggregation. This is where the HAVING clause comes in. In SQL, HAVING is used to filter grouped results. Example: select name as categoryname from category group by name having count(*) <5; This query: • groups category by name • counts the number of name in each category • returns only name with less than 5 appeared Key difference: WHERE filters rows before grouping HAVING filters groups after aggregation This distinction is critical when analyzing data in systems like PostgreSQL. Understanding when to use WHERE vs HAVING is what allows you to write accurate analytical queries. #SQL #PostgreSQL #DataEngineering #DataAnalytics