Postgres DB Disk Filling Up Due to pg_largeobject

Completed PostgreSQL Summary Stats and Window Functions (4 hours). Key concepts: Window functions (ROW_NUMBER, RANK, etc.) Partitioning datasets Running totals and advanced aggregations Analytical query patterns Window functions significantly expand analytical capabilities within SQL itself. #PostgreSQL #WindowFunctions #DataAnalytics

What developers think happens during an INSERT vs what actually happens in Postgres Most developers imagine this flow: Start transaction → run INSERT → commit → data is written to disk In reality, Postgres does something far more sophisticated Actual flow in Postgres: The inserted row is written to shared_buffers. At this point, the data page is marked dirty, not persisted A WAL (Write-Ahead Log) record is generated describing the exact changes made to the data pages The WAL record is appended to WAL files On commit, Postgres calls fsync to ensure the WAL is flushed to stable storage Once WAL is safely on disk, Postgres confirms the commit, even though the actual data pages are still only in memory Later, background writer and checkpointer processes flush dirty data pages to disk in a controlled and optimized way #database #postgres

PostgreSQL tip: pg_export_snapshot pg_export_snapshot lets you share the same transaction snapshot across multiple sessions, so they all see exactly the same data state. Why it matters 👇 • Guarantees consistent reads across connections • Useful for logical replication, backups, and coordinated exports • Built on PostgreSQL’s MVCC — powerful, but often overlooked More about pg_export_snapshot can be found in my latest article, link in comments. #postgres #postgresql #sql #dba #dataops

PostgreSQL For Cleaning Audible Dataset Used PostgreSQL to clean this dataset. ⭐ Standardized the data. ⭐ Split some columns. ⭐ Changed data types of some columns. ⭐ Checked for duplicates (no duplicates were found) ⭐ Edited the columns for author and narrator names and added spaces in them. Used pgAdmin 4 for writing PostgreSQL Queries on Desktop. #SQL #PostgreSQL #DataCleaning #Audible #DataAnalytics #DataAnalysis #Data #data #Dataset #dataset #AudibleDataset #StructuredQueryLanguage #PSQL #pgAdmin4 #pgAdmin #SQLDataCleaning

𝐒𝐭𝐨𝐩 𝐔𝐬𝐢𝐧𝐠 𝐎𝐑𝐃𝐄𝐑 𝐁𝐘 𝐢𝐧 𝐒𝐐𝐋 (𝐔𝐧𝐥𝐞𝐬𝐬 𝐘𝐨𝐮 𝐖𝐚𝐧𝐭 𝐒𝐥𝐨𝐰 𝐃𝐚𝐭𝐚𝐛𝐚𝐬𝐞 𝐏𝐞𝐫𝐟𝐨𝐫𝐦𝐚𝐧𝐜𝐞) Is ORDER BY quietly destroying your database servers' performance? In this short video, I explain why one of SQL’s most commonly used clauses may be the hidden bottleneck in your application, and what you can actually do about it. We’ll look at the real cost of sorting inside the database engine, how it impacts query plans, concurrency, and scalability, and why moving sorting responsibilities to other application tiers can dramatically improve performance. This isn’t about banning ORDER BY entirely, it’s about understanding when it makes sense and when it becomes an expensive habit. If you work with SQL, PostgreSQL, or relational databases in high-scale systems, this is a perspective you don’t want to miss. Let me know your thoughts and experiences in the comments. #database #databases #sql #sqlserver #postgresql #oracle #mysql #relationaldatabases https://lnkd.in/gKs84kpP

In PostgreSQL, running a massive DELETE command does not immediately free up disk space. In fact, an UPDATE is just an INSERT in disguise. Understanding MVCC is the key to solving database bloat. Here is how it works. Postgres uses Multi-Version Concurrency Control. The goal is to allow readers to access data without being blocked by writers modification of the same rows. To achieve this, Postgres never modifies data in place. When you run an UPDATE, the engine actually performs two distinct actions. 1. It performs an INSERT of the new version of the row. 2. It marks the old version as a 'dead tuple' (essentially a soft delete). The old data is still sitting physically on the disk. It is just invisible to new transactions. This leads to a specific problem. If you have a write-heavy application, these dead tuples pile up fast. This is table bloat. Your queries get slower because the database engine has to scan through all the junk to find the live data. This is why the Auto-Vacuum process is mandatory. It is the janitor. It runs in the background to clean up these dead tuples and mark that storage space as reusable for future inserts. If your storage usage seems high after a cleanup, don't panic. Check if your vacuum process is keeping up. #Database #SoftwareEngineering #DataEngineering #PostgreSQL #SQL #DatabasePerformance #MVCC #SystemDesign

🚀 SQL Date Functions Cheat Sheet MySQL vs PostgreSQL – Comparison Made Simple Working with dates in SQL can be confusing — especially when switching between MySQL and PostgreSQL. So I created a quick comparison cheat sheet covering: ✅ Current Date & Timestamp ✅ Extracting Year, Month, Quarter ✅ Date Formatting ✅ Date Arithmetic (Add/Subtract) ✅ Difference & Age Calculation ✅ Data Types Comparison This helps understand how similar operations are written differently in both databases. Small syntax differences can create big confusion — so I made this to make switching between them easier. 📊 If you work with SQL regularly, this might save you some time ⏳ #SQL #MySQL #PostgreSQL #DataAnalytics #DataAnalyst #Database #LearnSQL #Analytics #SQLTips #CheatSheet

Ever wondered why two databases running the same query can behave so differently? Query performance often comes down to one thing we don’t talk about enough: how indexes actually store your data. MySQL and PostgreSQL both use B-Tree indexes, but the way they organize leaf nodes, store row pointers, and fetch records is not the same — and those small internal differences can shape the performance of your lookups, range scans, and even disk I/O. I wrote a simple, visual breakdown that explains how MySQL vs PostgreSQL store indexed data differently, with diagrams showing B-Tree leaf nodes, row references, and heap pointers. #DatabasePerformance #MySQL #PostgreSQL #Indexes #BTree #QueryOptimization #SQL #BackendDevelopment #DataEngineering #PerformanceEngineering

🚀 How I improved query performance with a simple indexing trick I recently optimized a database query and managed to significantly reduce the work the database has to do by tweaking the index. Here’s the strategy: - The query filtered on a key column but also needed a few additional columns. - Initially, the database had to do extra lookups to fetch those columns, increasing workload. - I created a covering index by including those extra columns directly in the index. Result: - Queries touch fewer pages in memory - Faster execution - Better performance under load Why this works A covering index allows the database to read everything it needs directly from the index, avoiding extra lookups and reducing unnecessary work. Things to watch out for - Slightly slower writes (insert/update/delete) because the index is bigger - Larger index size → more memory usage - Best for read-heavy queries, not every table In short: If your queries filter on one column but select extra columns, a covering index is a small change with a big performance impact. #Database #SQL #PostgreSQL #MySQL #SQLServer #Indexing #PerformanceOptimization #DatabasePerformance #TechTips #BackendDevelopment #SoftwareEngineering

Tired of database management feeling like a relic from the past? 🕰️ Serverless SQL is here to make your life easier, but with a growing landscape, choosing the right one can be tricky. We break down the top serverless SQL databases for developers in 2026, comparing their strengths, vibes, and catches. Discover which one fits your project best – from workflow kings to edge speedsters. #ServerlessSQL #DatabaseManagement #DeveloperTools #TechComparison #BackendDevelopment #CloudComputing #PostgreSQL #MySQL #SQLite #NeonDB #Supabase #Turso #PlanetScale #TiDB #Prisma