<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by MOHAMMAD SAMSAD HUSSAIN on Medium]]></title>
        <description><![CDATA[Stories by MOHAMMAD SAMSAD HUSSAIN on Medium]]></description>
        <link>https://medium.com/@its-me-sam?source=rss-33c85adaf303------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*UymcMxoQ2STjmfBsFlagkQ.jpeg</url>
            <title>Stories by MOHAMMAD SAMSAD HUSSAIN on Medium</title>
            <link>https://medium.com/@its-me-sam?source=rss-33c85adaf303------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 15:01:28 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@its-me-sam/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[MVCC Bloat in PostgreSQL: Why Your Database Size Keeps Growing Even After Deleting Data: Part 1]]></title>
            <link>https://its-me-sam.medium.com/mvcc-bloat-postgresql-database-size-growing-e38b27a703a4?source=rss-33c85adaf303------2</link>
            <guid isPermaLink="false">https://medium.com/p/e38b27a703a4</guid>
            <category><![CDATA[pgs]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[MOHAMMAD SAMSAD HUSSAIN]]></dc:creator>
            <pubDate>Wed, 18 Feb 2026 11:30:47 GMT</pubDate>
            <atom:updated>2026-02-18T11:30:47.164Z</atom:updated>
            <content:encoded><![CDATA[<p>It’s 2:00 AM.<br>Your monitoring system suddenly screams: <strong><em>“Disk usage: 95% full.”</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*67FAYgjC4mtknQU6DvKs_A.png" /><figcaption>The Elephant in the Room: When your database stores more ‘history’ than ‘data’.</figcaption></figure><p>You check your largest PostgreSQL table:</p><blockquote>● <strong><em>“Row count: 100 million”</em></strong></blockquote><blockquote>● <strong><em>“Expected size: ~500 GB”</em></strong></blockquote><blockquote>● <strong><em>“Actual disk usage: 5 TB”</em></strong></blockquote><p>You didn’t import terabytes of data.<br>You didn’t run massive batch jobs.</p><p><strong><em>So… where did the extra 4.5 TB come from?</em></strong></p><p><strong>Welcome to one of the most misunderstood realities of PostgreSQL: <em>Your database is not just storing data — it is storing the </em>history of your changes<em>.</em></strong></p><ul><li>ˋˏ✄┈┈┈┈ <em>This invisible history is what we call </em><strong><em>MVCC Bloat</em></strong><em>.<br></em>-ˋˏ✄┈┈┈┈ <em>Or in simpler terms: </em><strong><em>The Ghost </em></strong>💀<strong><em> of Deleted Data.</em></strong></li></ul><h3><em>What is MVCC?</em></h3><p>MVCC (Multi-Version Concurrency Control) allows PostgreSQL to handle multiple transactions safely by keeping old row versions instead of overwriting them immediately.</p><p>In simple terms, PostgreSQL uses something called <strong>MVCC (Multi-Version Concurrency Control)</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FQkTkWM6im2gu0DHYEmqgg.png" /></figure><p><strong>₊˚🌈*ੈ✩‧₊ Imagine</strong> a notebook where you never erase mistakes.<br> You just cross out the old sentence and write a new one below it.</p><p>⏰ Over time:</p><ul><li>The notebook becomes thicker</li><li>Even though the actual useful content is small</li></ul><p>That crossed-out content = <strong>dead tuples (Ghost </strong>💀 <strong>data)</strong>.</p><h3>📉 Why High‑Churn Systems Suffer the Most</h3><p>High-churn systems include: Audit logs, Event tracking platforms, IoT ingestion systems, Financial transaction systems, Microservices with frequent updates</p><p><strong>Assume a workload:</strong></p><p>After one day, 1000 insert/sec × 86400s = 86.4 million new rows; 200 del/s = 17.3 million dead rows per day. Over a month, that’s over half a billion dead rows lurking in the table.</p><p>Even though rows are “deleted,” the space is NOT immediately freed on disk.</p><p>Instead:</p><ul><li>Old row versions remain inside table pages</li><li>New rows keep getting appended</li><li>File size keeps growing</li></ul><p><strong>Result: </strong><em>Table slowly becomes a graveyard of invisible rows that still consume disk space — even though your application thinks they are deleted.</em></p><p><em>“This is where </em><em>VACUUM comes in – VACUUM does not shrink the physical table file. It only marks dead space as reusable inside the same file.”</em></p><h3><em>How to Detect MVCC Bloat</em></h3><p>This calculates the percentage of dead tuples out of total rows (live + dead).</p><pre>SELECT<br>    schemaname,<br>    relname AS table_name,<br>    n_live_tup,<br>    n_dead_tup,<br>    ROUND(<br>        n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2<br>    ) AS dead_tuple_percentage<br>FROM pg_stat_user_tables<br>ORDER BY n_dead_tup DESC;</pre><p>Think of <strong>n_dead_tup</strong> as ghost rows.<br><strong><em>“</em></strong><strong><em>n_dead_tup </em></strong><em>is the number of dead tuples since the last vacuum – a consistently high or increasing value indicates that vacuum isn’t keeping up.</em><strong><em>”</em></strong></p><h3>𝒘𝒉𝒚 𝑨𝒖𝒕𝒐𝒗𝒂𝒄𝒖𝒖𝒎 𝑨𝒍𝒐𝒏𝒆 𝑬𝒗𝒆𝒏𝒕𝒖𝒂𝒍𝒍𝒚 𝑭𝒂𝒊𝒍𝒔</h3><h4>𝙑𝙖𝙘𝙪𝙪𝙢 𝘾𝙡𝙚𝙖𝙣𝙨 𝙎𝙥𝙖𝙘𝙚, 𝘽𝙪𝙩 𝘿𝙤𝙚𝙨𝙣’𝙩 𝙎𝙝𝙧𝙞𝙣𝙠 𝙁𝙞𝙡𝙚𝙨</h4><p>Autovacuum removes dead rows internally, but:</p><ul><li>It does NOT reduce the table file size</li><li>It only marks space as reusable</li></ul><p>This is called the <strong>High Water Mark problem</strong>.</p><h4>𝙋𝙖𝙜𝙚-𝙇𝙚𝙫𝙚𝙡 𝙁𝙧𝙖𝙜𝙢𝙚𝙣𝙩𝙖𝙩𝙞𝙤𝙣</h4><p>PostgreSQL stores data in 8KB pages. “If you insert 100 rows, then delete 50, and update 30…”</p><p>As a consequence:</p><ul><li>Fragmented pages</li><li>Scattered free space</li><li>Poor disk locality</li></ul><p>Even after vacuum runs, the file remains fragmented.</p><h4>𝙏𝙝𝙚 “𝙄/𝙊 𝙏𝙖𝙭&quot;</h4><p>Most people care about disk space, but the <strong>hidden cost is I/O</strong>. When the table is bloated to 5TB but only has 500GB of data, a full table scan still reads all 5TB. You are paying a 10x ‘I/O Tax’ on every query because Postgres has to scan the ‘ghosts’ too.</p><h4>𝙏𝙝𝙚 “𝙁𝙞𝙡𝙡𝙛𝙖𝙘𝙩𝙤𝙧&quot;</h4><p>If the table is update-heavy, setting a <strong>FILLFACTOR</strong> of 80-90 can prevent bloat by keeping new row versions on the same page as the old ones (HOT updates), allowing vacuum to work much more efficiently. By leaving 10–20% free space in each page, PostgreSQL can place updated rows on the same page (HOT updates), reducing index bloat and improving vacuum efficiency.</p><h4>𝙄𝙣𝙙𝙚𝙭 𝘽𝙡𝙤𝙖𝙩</h4><p>Bloat is not limited to tables. Every UPDATE creates:</p><ul><li>A new row version</li><li>New index entries</li></ul><p><em>Old index entries remain until vacuum cleanup.</em></p><p><strong>Over time:</strong></p><ul><li><em>Index size can become larger than the table.</em></li><li><em>Queries slow down due to extra logical I/O</em></li></ul><p>This is why bloated indexes make SELECT queries slower even if CPU is low.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/512/1*UzOfQdi6NmGzCGdTHsm1BQ.png" /></figure><blockquote>𝘌𝘹𝘤𝘦𝘴𝘴𝘪𝘷𝘦 𝘣𝘭𝘰𝘢𝘵 𝘥𝘰𝘦𝘴𝘯’𝘵 𝘫𝘶𝘴𝘵 𝘸𝘢𝘴𝘵𝘦 𝘴𝘱𝘢𝘤𝘦 — 𝘪𝘵 𝘧𝘰𝘳𝘤𝘦𝘴 𝘢𝘶𝘵𝘰𝘷𝘢𝘤𝘶𝘶𝘮 𝘵𝘰 𝘸𝘰𝘳𝘬 𝘩𝘢𝘳𝘥𝘦𝘳. 𝘐𝘧 𝘪𝘵 𝘧𝘢𝘭𝘭𝘴 𝘵𝘰𝘰 𝘧𝘢𝘳 𝘣𝘦𝘩𝘪𝘯𝘥, 𝘺𝘰𝘶𝘳 𝘥𝘢𝘵𝘢𝘣𝘢𝘴𝘦 𝘳𝘪𝘴𝘬𝘴 𝘢 𝘛𝘳𝘢𝘯𝘴𝘢𝘤𝘵𝘪𝘰𝘯 𝘐𝘋 (𝘟𝘐𝘋) 𝘞𝘳𝘢𝘱𝘢𝘳𝘰𝘶𝘯𝘥, 𝘸𝘩𝘪𝘤𝘩 𝘤𝘢𝘯 𝘧𝘰𝘳𝘤𝘦 𝘵𝘩𝘦 𝘦𝘯𝘵𝘪𝘳𝘦 𝘥𝘢𝘵𝘢𝘣𝘢𝘴𝘦 𝘪𝘯𝘵𝘰 𝘳𝘦𝘢𝘥-𝘰𝘯𝘭𝘺 𝘮𝘰𝘥𝘦 𝘵𝘰 𝘱𝘳𝘦𝘷𝘦𝘯𝘵 𝘥𝘢𝘵𝘢 𝘭𝘰𝘴𝘴.</blockquote><h3>Final Thought: Storage in PostgreSQL Is Alive</h3><p>In PostgreSQL, deleting data does not mean removing it instantly. The database is constantly managing multiple versions of rows behind the scenes. If autovacuum cannot keep up, your storage will silently grow, performance will degrade, and queries will pay the hidden I/O tax.</p><p>Understanding MVCC bloat is not just a DBA skill — it is a survival skill for any system running PostgreSQL at scale.</p><p><em>“</em>🅸🅽 🆃🅷🅴 🅽🅴🆇🆃 🅱🅻🅾🅶,<em>, we will measure the “Ghost Population” (bloat), fix severe bloat without downtime using </em><strong><em>pg_repack</em></strong><em> and </em><strong><em>pg_squeeze</em></strong><em>, and explore the fillfactor strategy.”</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e38b27a703a4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How PostgreSQL Materialized Views Power Fast Food Delivery Apps]]></title>
            <link>https://its-me-sam.medium.com/postgresql-materialized-views-performance-3330dc8a42bf?source=rss-33c85adaf303------2</link>
            <guid isPermaLink="false">https://medium.com/p/3330dc8a42bf</guid>
            <category><![CDATA[postgresql-database]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[MOHAMMAD SAMSAD HUSSAIN]]></dc:creator>
            <pubDate>Wed, 11 Feb 2026 06:59:16 GMT</pubDate>
            <atom:updated>2026-02-11T06:59:16.945Z</atom:updated>
            <content:encoded><![CDATA[<h3>How (Food Delivery App) Uses PostgreSQL Materialized Views to Serve Your Favorite Meals Instantly</h3><h3>🍕 Imagine Ordering Food… Instantly</h3><blockquote>Your dashboard loads in 3 seconds.<br> Your users expect it in 300 milliseconds.<br> Your database is not slow.<br> It’s just doing too much work.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CKUoJYd6IUjg39a-ssFgkw.jpeg" /></figure><blockquote>The query? , The index?. The hardware?……………………………?</blockquote><p>The real question isn’t <strong><em>“How do I speed this query up?”</em></strong><br> It’s <strong><em>“Why am I recomputing the same thing again and again?”</em></strong></p><p>In many real-world systems, the real solution isn’t faster hardware or rewriting everything.</p><p><strong><em>{ It’s using PostgreSQL Materialized Views strategically }</em></strong></p><h3>🧐 Materialized Views Explained</h3><p>Let’s imagine your database is a busy restaurant kitchen:</p><p>A <strong>regular database query</strong> is like cooking each meal from scratch every time an order comes in. Fresh? Yes. Efficient? Not when you have 100 hungry customers waiting.</p><blockquote><strong>“Hold on! </strong>I need to check every restaurant in Mumbai, read all their reviews from the past month, calculate average ratings, check current delivery times, sort them by distance from you, and then show you the top 10. This might take 3–5 seconds… for each of you 1,000 people.<strong>”</strong></blockquote><pre>REGULAR QUERY TIMELINE (The Slow Way)<br><br>┌─────────────┐    ┌─────────────┐    ┌─────────────┐    ┌─────────────┐<br>│  Customer   │    │ Database    │    │ Database    │    │  Customer   │<br>│   Orders    │───▶│   Starts    │───▶│   Finishes  │───▶│   Gets      │<br>│   Biryani   │    │  Cooking    │    │  Cooking    │    │   Biryani   │<br>└─────────────┘    └─────────────┘    └─────────────┘    └─────────────┘<br>      │                    │                    │                    │<br>      └────────────────────┴────────────────────┴────────────────────┘<br>                              ⏱️ 20 MINUTES WAIT</pre><p>A <strong>materialized view</strong> is like preparing today’s special biryani in large batches during off-peak hours, storing it ready to serve, and refreshing the batch every hour. When orders pour in, you’re ready.</p><blockquote><strong>“Good news!</strong> At 7:45 PM, I already prepared today’s ‘Top 10 Biryani in Mumbai’ list. Let me grab that ready-made list from the fridge and… here you go!<strong>”</strong></blockquote><pre>MATERIALIZED VIEW TIMELINE (The Fast Way)<br><br>┌─────────────┐                    ┌─────────────┐<br>│  Database   │                    │  Customer   │<br>│ Pre-cooks   │                    │   Orders    │<br>│  Biryani    │───────────────────▶│   Biryani   │<br>│ at 3:00 PM  │                    │ at 6:30 PM  │<br>└─────────────┘                    └─────────────┘<br>           │                               │<br>           └─────────────┬─────────────────┘<br>                         ▼<br>                 ┌─────────────┐<br>                 │  Customer   │<br>                 │   Gets      │<br>                 │   Biryani   │<br>                 └─────────────┘<br>                         │<br>                  ⏱️ 20 SECONDS WAIT</pre><h3>In Database Terms:</h3><p>‏𖦹 MV store query results on disk (like saving a completed report)</p><p>‏𖦹 They serve results instantly (no recalculations needed)</p><p>‏𖦹 You refresh them periodically (to keep data fresh)</p><p>Think of it as caching your most popular database queries.</p><h3>🍛 Food deliver App Example: Top 10 Biryani Spots in Mumbai</h3><p>Let’s say Food Delivery App wants to show users the best biryani places nearby.</p><h3>❌ Without Materialized Views</h3><p>Every time you open the app, PostgreSQL runs this complex operation:</p><ol><li>Scan millions of restaurant records</li><li>Join with ratings, reviews, and order history</li><li>Calculate average ratings and delivery times</li><li>Filter by location and cuisine</li><li>Sort and rank the results</li><li>Return top 10</li></ol><p>Do this for <em>every user</em>, <em>every request</em>? That’s like asking your chef to harvest rice, grind spices, and marinate chicken from scratch for each order. The kitchen would collapse during dinner rush!</p><h3>✅ <strong>With a materialized view:</strong></h3><p>Food Delivery App creates a materialized view once:</p><pre>CREATE MATERIALIZED VIEW top_biryani_mumbai AS<br>SELECT <br>    r.restaurant_id,<br>    r.name,<br>    AVG(re.rating) AS avg_rating,<br>    COUNT(o.order_id) AS orders_last_hour,<br>    r.delivery_time_estimate<br>FROM restaurants r<br>JOIN reviews re USING (restaurant_id)<br>LEFT JOIN orders o ON o.restaurant_id = r.restaurant_id <br>                 AND o.order_time &gt; NOW() - INTERVAL &#39;1 hour&#39;<br>WHERE r.city = &#39;Mumbai&#39; <br>  AND r.cuisine = &#39;Biryani&#39;<br>  AND r.is_active = true<br>GROUP BY r.restaurant_id, r.name, r.delivery_time_estimate<br>ORDER BY avg_rating DESC, orders_last_hour DESC<br>LIMIT 10;</pre><p>What happens next?</p><p>🎯 The complex query runs once and saves the results</p><p>🎯 Every user gets instant results from the saved “snapshot”</p><p>🎯 Food Delivery App refreshes this every 30 minutes (or whenever needed)</p><p>🎯 Millions of users get fast responses with minimal database load</p><h3>⚡ When to Use Materialized Views</h3><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fdatawrapper.dwcdn.net%2F1rKYH%2F1%2F&amp;display_name=Datawrapper&amp;url=https%3A%2F%2Fdatawrapper.dwcdn.net%2F1rKYH%2F1%2F&amp;image=https%3A%2F%2Fdatawrapper.dwcdn.net%2F1rKYH%2Fplain-s.png%3Fv%3D1&amp;type=text%2Fhtml&amp;schema=dwcdn" width="739" height="363" frameborder="0" scrolling="no"><a href="https://medium.com/media/00215cda2b53228250e42876e95e4ad9/href">https://medium.com/media/00215cda2b53228250e42876e95e4ad9/href</a></iframe><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fdatawrapper.dwcdn.net%2FvbVSQ%2F1%2F&amp;display_name=Datawrapper&amp;url=https%3A%2F%2Fdatawrapper.dwcdn.net%2FvbVSQ%2F1%2F&amp;image=https%3A%2F%2Fdatawrapper.dwcdn.net%2FvbVSQ%2Fplain-s.png%3Fv%3D1&amp;type=text%2Fhtml&amp;schema=dwcdn" width="1065" height="306" frameborder="0" scrolling="no"><a href="https://medium.com/media/bb0342d60f183847803d4582fbd686cc/href">https://medium.com/media/bb0342d60f183847803d4582fbd686cc/href</a></iframe><h3>🚀 Getting Started with Materialized Views</h3><p>Based on our test (1,000 restaurants, 200K orders, 150 queries):</p><h3><strong>❶ Identify Slow, Repeated Queries :</strong></h3><p>Pick a query that:</p><blockquote>Runs more than 100 times/day</blockquote><blockquote>Takes &gt;200ms</blockquote><blockquote>Returns aggregated/summary data</blockquote><blockquote>Doesn’t need sub-second freshness</blockquote><pre>-- Find your slowest, most frequent queries <br><br>SELECT <br>    query,<br>    calls,<br>    total_exec_time,<br>    mean_exec_time,<br>    ROUND((total_exec_time / calls)::numeric, 2) as time_per_call<br>FROM pg_stat_statements <br>WHERE calls &gt; 50  -- Frequently run<br>  AND mean_exec_time &gt; 100  -- Slow (&gt;100ms)<br>ORDER BY total_exec_time DESC <br>LIMIT 10;</pre><h3><strong>🧠 Is This Query a Good Materialized View Candidate?</strong></h3><p>Not every slow query needs a materialized view. The best candidates usually share common characteristics.</p><p><strong><em>Does this query run frequently?</em></strong></p><p><strong><em>Is it aggregation-heavy (COUNT, SUM, AVG)?</em></strong></p><p><strong><em>Is it scanning a large dataset?</em></strong></p><p><strong><em>Does it power dashboards, analytics, or reports?</em></strong></p><p><strong><em>Can it tolerate slightly stale data?</em></strong></p><pre>-- This 220ms query was perfect for materialization:<br><br>SELECT                                   <br>    r.name, r.city, r.cuisine_type,<br>    COUNT(DISTINCT o.order_id) as total_orders,<br>    AVG(o.total_amount) as avg_order_value<br>FROM restaurants r<br>LEFT JOIN orders o ON r.restaurant_id = o.restaurant_id <br>    AND o.status = &#39;delivered&#39;<br>    AND o.created_at &gt;= NOW() - INTERVAL &#39;180 days&#39;<br>WHERE r.city = &#39;Mumbai&#39;<br>GROUP BY r.restaurant_id, r.name, r.city, r.cuisine_type<br>HAVING COUNT(DISTINCT o.order_id) &gt; 10;</pre><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fdatawrapper.dwcdn.net%2FKCBvA%2F2%2F&amp;display_name=Datawrapper&amp;url=https%3A%2F%2Fdatawrapper.dwcdn.net%2FKCBvA%2F1%2F&amp;image=https%3A%2F%2Fdatawrapper.dwcdn.net%2FKCBvA%2Fplain-s.png%3Fv%3D1&amp;type=text%2Fhtml&amp;schema=dwcdn" width="1312" height="818" frameborder="0" scrolling="no"><a href="https://medium.com/media/ac11fd099655b9c65c6c8a24c74e7a92/href">https://medium.com/media/ac11fd099655b9c65c6c8a24c74e7a92/href</a></iframe><h3><strong>❷ Test Creating a View :</strong></h3><p>Start small, test, then scale. Here’s our proven process</p><p>▶ <strong>Create Your Materialized View</strong></p><pre>-- Create the materialized view with immediate data population<br>CREATE MATERIALIZED VIEW restaurant_performance_mv AS<br>SELECT <br>    r.restaurant_id,<br>    r.name,<br>    r.city,<br>    r.cuisine_type,<br>    COUNT(DISTINCT o.order_id) as total_orders,<br>    AVG(o.total_amount) as avg_order_value,<br>    AVG(rv.rating) as avg_rating,<br>    NOW() as snapshot_time  -- Track freshness<br>FROM restaurants r<br>LEFT JOIN orders o ON r.restaurant_id = o.restaurant_id <br>    AND o.status = &#39;delivered&#39;<br>    AND o.created_at &gt;= NOW() - INTERVAL &#39;180 days&#39;<br>LEFT JOIN reviews rv ON r.restaurant_id = rv.restaurant_id<br>GROUP BY r.restaurant_id, r.name, r.city, r.cuisine_type<br>HAVING COUNT(DISTINCT o.order_id) &gt; 10<br>WITH DATA;  -- This ensures data is populated immediately<br><br>-- Create an index for concurrent refresh<br>CREATE UNIQUE INDEX idx_restaurant_performance_mv_id <br>ON restaurant_performance_mv(restaurant_id);<br><br>-- Add additional indexes for common query patterns<br>CREATE INDEX idx_restaurant_performance_mv_city <br>ON restaurant_performance_mv(city);<br><br>CREATE INDEX idx_restaurant_performance_mv_cuisine <br>ON restaurant_performance_mv(cuisine_type);<br><br>CREATE INDEX idx_restaurant_performance_mv_orders <br>ON restaurant_performance_mv(total_orders DESC);</pre><p>▶ <strong>Performance Test without Materialized View</strong></p><h4>(Execution🏃 Time without MV: 219.416 ms)</h4><pre>\timing on<br><br>-- Original query (219ms in our test)<br>SELECT * FROM (<br>    -- Original complex query with multiple joins and aggregations<br>    SELECT <br>        r.restaurant_id,<br>        r.name,<br>        r.city,<br>        r.cuisine_type,<br>        COUNT(DISTINCT o.order_id) as total_orders,<br>        AVG(o.total_amount) as avg_order_value,<br>        AVG(rv.rating) as avg_rating,<br>        NOW() as snapshot_time<br>    FROM restaurants r<br>    LEFT JOIN orders o ON r.restaurant_id = o.restaurant_id <br>        AND o.status = &#39;delivered&#39;<br>        AND o.created_at &gt;= NOW() - INTERVAL &#39;180 days&#39;<br>    LEFT JOIN reviews rv ON r.restaurant_id = rv.restaurant_id<br>    GROUP BY r.restaurant_id, r.name, r.city, r.cuisine_type<br>    HAVING COUNT(DISTINCT o.order_id) &gt; 10<br>) as original_query<br>LIMIT 100;<br><br>\timing off<br></pre><p>▶ <strong>Performance Test with Materialized View</strong></p><h4>(Execution🏃 Time with MV: 0.563 ms)</h4><pre>\timing on<br>SELECT * FROM restaurant_performance_mv LIMIT 100;<br>\timing off</pre><h3><strong>❸ Schedule Refreshes :</strong></h3><p>Choose the right refresh strategy based on your data’s freshness needs:</p><blockquote>Option A: Manual Refresh (For Development)</blockquote><blockquote>Option B: Scheduled Refresh (For Production)</blockquote><blockquote>Option C: Event-Driven Refresh</blockquote><h3>Performance Improvement Analysis</h3><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fdatawrapper.dwcdn.net%2Fwbm4n%2F2%2F&amp;display_name=Datawrapper&amp;url=https%3A%2F%2Fdatawrapper.dwcdn.net%2Fwbm4n%2F2%2F&amp;image=https%3A%2F%2Fdatawrapper.dwcdn.net%2Fwbm4n%2Fplain-s.png%3Fv%3D2&amp;type=text%2Fhtml&amp;schema=dwcdn" width="600" height="249" frameborder="0" scrolling="no"><a href="https://medium.com/media/b324337a5e68bc01522dcb8196c56a42/href">https://medium.com/media/b324337a5e68bc01522dcb8196c56a42/href</a></iframe><h3>📈 Why Materialized Views Matter for Food delivery Apps</h3><p>Materialized views are like a <strong>secret speed engine</strong>:</p><ul><li>✅ Reduce load on the database</li><li>✅ Precompute results for repeated queries</li><li>✅ Improve user experience with <strong>instant results</strong></li></ul><p>Without them, apps would have to <strong>recompute every query from scratch</strong>, making users wait longer for the data they want.</p><blockquote><em>“By implementing a materialized view, we transformed a 219ms analytical query into a sub-millisecond operation. While this introduces data staleness, for most analytics dashboards and reporting systems, the trade-off is well worth it. The key is to establish an appropriate refresh schedule that balances performance with data freshness requirements.”</em></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3330dc8a42bf" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Do Indexes Eliminate PostgreSQL Sort Costs? (Part 4)]]></title>
            <link>https://its-me-sam.medium.com/do-indexes-eliminate-postgresql-sort-costs-part-4-940046fea31e?source=rss-33c85adaf303------2</link>
            <guid isPermaLink="false">https://medium.com/p/940046fea31e</guid>
            <category><![CDATA[postgresql-database]]></category>
            <category><![CDATA[pgadmin]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[post-gr]]></category>
            <dc:creator><![CDATA[MOHAMMAD SAMSAD HUSSAIN]]></dc:creator>
            <pubDate>Fri, 23 Jan 2026 03:52:57 GMT</pubDate>
            <atom:updated>2026-01-23T03:52:57.639Z</atom:updated>
            <content:encoded><![CDATA[<h4>Group D — Final Experiment: Index-Driven Ordering</h4><figure><img alt="can indexes eliminate short cost?" src="https://cdn-images-1.medium.com/max/1024/1*pSBGGQzf0aHh5VPG0avovw.png" /></figure><p>In the first three parts of this series, we explored PostgreSQL sorting from every memory angle:</p><ul><li><a href="https://medium.com/@its-me-sam/why-doubling-ram-didnt-speed-up-postgresql-1ab66a557563"><strong>Part 1</strong></a><strong> (Group A):</strong> Doubling system RAM didn’t change sort behavior</li><li><a href="https://medium.com/@its-me-sam/why-increasing-shared-buffers-didnt-fix-my-postgresql-sort-part-2-7d3da8bf9067"><strong>Part 2</strong></a><strong> (Group B):</strong> Increasing shared_buffers didn’t stop disk-based sorts</li><li><a href="https://medium.com/@its-me-sam/why-work-mem-is-the-only-setting-that-actually-changes-postgresql-sorting-part-3-72ba164af526"><strong>Part 3</strong></a><strong> (Group C):</strong> Only work_mem actually changed how sorting worked</li></ul><p>That leads to the final and most important question:</p><blockquote><strong><em>What if PostgreSQL doesn’t need to sort at all?</em></strong></blockquote><p>That’s exactly what <strong>Group D</strong> tests.</p><h3>🧠 Group D — When Indexes Remove Sorting Entirely</h3><h3>Scenario D1, D2 &amp; D3</h3><p>In these scenarios, only <strong>one variable changed</strong>:</p><p>👉 <strong>the presence of a covering index</strong>.</p><p>All other factors (<strong>system RAM, shared_buffers, query shape, dataset size</strong>) were intentionally kept comparable to earlier groups.</p><pre>| Item           | Scenario D1     | Scenario D2      | Scenario D3        |<br>| -------------- | --------------- | ---------------- | ------------------ |<br>| System RAM     | 10 GB           | 20 GB            | 20 GB              |<br>| shared_buffers | 2.5 GB          | 5 GB             | 5 GB               |<br>| work_mem       | 4 MB            | 4 MB             | 64 MB              |<br>| Index          | ✅ Yes          | ✅ Yes            | ✅ Yes             |<br>| Query          | Same            | Same             | Same               |<br>| Dataset size   | ~6.9 GB         | ~6.9 GB          | ~6.9 GB            |</pre><h3>🎯 What This Scenario Is Testing</h3><p>At this point in the series, we’ve already ruled out:</p><p>≡ 󠀠󠀠󠀠⠀<strong>System RAM alone</strong> ❌<br>≡ ⠀<strong>Bigger </strong><strong>shared_buffers</strong> ❌<br>≡ ⠀<strong>Larger </strong><strong>work_mem</strong> ❌</p><p>Those knobs were fully explored in <strong>Groups A, B, and C</strong>.</p><p>So <strong>Group D isolates one final question</strong>:</p><p><strong>What happens when PostgreSQL doesn’t need to sort at all?</strong></p><h3>✏️ What Changed in Group D (And Only This)</h3><p>Unlike previous groups, <strong>no memory parameter is the focus here</strong>.</p><p>The only meaningful change is:</p><blockquote><em>✅ </em><strong><em>An index that satisfies </em></strong><strong><em>ORDER BY payload</em></strong></blockquote><p>Everything else remains intentionally comparable to earlier tests.</p><h3>🔍 Query Used for All Tests</h3><p>To keep results consistent with <strong>Part 1, </strong><a href="https://medium.com/@its-me-sam/why-increasing-shared-buffers-didnt-fix-my-postgresql-sort-part-2-7d3da8bf9067"><strong>Part 2</strong></a><strong>, and Part 3</strong>, the <strong>exact same query</strong> was used:</p><pre>EXPLAIN (ANALYZE, BUFFERS)<br>SELECT id, created_at, category, amount,payload FROM sb_big ORDER BY payload;</pre><h3>⏱️ Execution Time — Group D</h3><p>At first glance, execution time across Group D scenarios looks <strong>remarkably stable</strong>.</p><p>Whether running on <strong>10 GB</strong> or <strong>20 GB</strong> of system RAM, and regardless of work_mem, the query consistently completed in the <strong>~22–24 second range</strong>.</p><pre>┌─────────┬──────────┬───────────────────┬──────────┬─────────┐ <br>│ Scenario│ System   │ shared_      │ work_    │ Exec    │ <br>│         │ RAM      │ buffers      │ mem      │ Time    │ <br>├─────────┼──────────┼──────────────┼──────────┼─────────┤ <br>│  D1     │ 10 GB    │ 2.5 GB       │ 4 MB     │ ~21.9 s │ <br>│  D2     │ 20 GB    │ 5 GB         │ 4 MB     │ ~23.9 s │ <br>│  D3     │ 20 GB    │ 5 GB         │ 64 MB    │ ~23.2 s │ <br>└─────────┴──────────┴──────────────┴──────────┴─────────┘ </pre><p>✅ <strong>The key observation:</strong><br> Execution time did <strong>not</strong> meaningfully depend on memory tuning.</p><p>That’s because the <strong>execution strategy itself changed</strong>.</p><h3>💾 Sorting Behavior — Group D</h3><p>This is where Group D diverges completely from every previous group.</p><p>Across <strong>all three scenarios</strong>, PostgreSQL chose:</p><pre>Index Only Scan using idx_sb_big_payload_covering</pre><p>And most importantly:</p><p>🚫 <strong>No Sort node exists in the plan</strong></p><p>There is:</p><ul><li>No Sort Method</li><li>No external merge</li><li>No memory threshold to cross</li></ul><p>PostgreSQL never enters a sorting phase at all — because the index already provides rows in the required order.</p><blockquote><strong><em>When an index satisfies </em></strong><strong><em>ORDER BY, sorting is not optimized — it is eliminated.</em></strong></blockquote><p>This is fundamentally different from Groups A (<a href="https://medium.com/@its-me-sam/why-doubling-ram-didnt-speed-up-postgresql-1ab66a557563">part 1</a>), B (<a href="https://medium.com/@its-me-sam/why-increasing-shared-buffers-didnt-fix-my-postgresql-sort-part-2-7d3da8bf9067">Part 2</a>), and C (<a href="https://medium.com/@its-me-sam/why-work-mem-is-the-only-setting-that-actually-changes-postgresql-sorting-part-3-72ba164af526">Part 3</a>), where PostgreSQL had no choice but to sort.</p><h3>🚧 Temp File Usage — Group D</h3><p>With no sort comes an equally important outcome:</p><pre>temp read=0<br>temp written=0</pre><p>This is true for <strong>every Group D scenario</strong>.</p><pre>| Scenario | temp read | temp written |<br>| -------- | --------- | ------------ |<br>|   D1     | 0         | 0            |<br>|   D2     | 0         | 0            |<br>|   D3     | 0         | 0            |</pre><p>✅ No disk spill<br> ✅ No external merge<br> ✅ No temp file churn</p><p>Memory pressure disappears — not because memory increased, but because <strong>the execution plan no longer needs it</strong>.</p><h3>🧠 Final Takeaway — What Actually Controls PostgreSQL Sorting</h3><p>Over four controlled experiments, we tested every tuning lever people usually reach for when ORDER BY is slow.</p><p>Here’s what the data <em>actually</em> shows:</p><p><strong>Myth #1 : “More system RAM makes PostgreSQL faster”</strong></p><p>Doubling RAM changed neither the execution plan nor the sort behavior.</p><blockquote><em>PostgreSQL does not automatically convert extra RAM into faster sorting.</em></blockquote><p><strong>Myth #2 : “Increasing shared_buffers fixes disk-based sorts”</strong></p><p>Larger shared_buffers improved read caching slightly —<br> but <strong>sorts still spilled to disk</strong>.</p><blockquote><em>shared_buffers helps PostgreSQL </em><strong><em>read data faster</em></strong><em>, not </em><strong><em>reorder data differently</em></strong><em>.</em></blockquote><p><strong>Reality #1 : </strong><strong>work_mem controls sort behavior</strong></p><ul><li>work_mem is allocated <strong>per sort</strong></li><li><strong>Per worker</strong></li><li><strong>Per query node</strong></li></ul><p>Sorting only stays in memory when:</p><pre>work_mem ≥ data_per_sort_per_worker</pre><p>Anything less → disk spill is inevitable.</p><p><strong>Reality #2 :Indexes eliminate the problem entirely</strong></p><ul><li>PostgreSQL <strong>removes the sort node</strong></li><li>work_mem becomes irrelevant</li><li>Temp files disappear</li><li>Performance becomes predictable</li></ul><blockquote><em>Indexes don’t optimize sorting — </em><strong><em>they remove sorting from the plan.</em></strong></blockquote><h3>🏁 Final Verdict</h3><p><strong><em>PostgreSQL performance is not about “more memory”.</em></strong></p><p>It’s about:</p><ul><li><strong>Understanding execution plans</strong></li><li><strong>Sizing memory at the correct level</strong></li><li><strong>Designing indexes that eliminate work</strong></li></ul><p>If a query spills to disk during sorting, the tuning order should be:</p><pre>Index first → work_mem second → shared_buffers last.</pre><p>Hardware upgrades should only be considered after execution plans are fixed.</p><blockquote>Hardware scales costs.<br><strong>Good design removes work.</strong></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=940046fea31e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why work_mem Is the Only Setting That Actually Changes PostgreSQL Sorting : Part 3]]></title>
            <link>https://its-me-sam.medium.com/why-work-mem-is-the-only-setting-that-actually-changes-postgresql-sorting-part-3-72ba164af526?source=rss-33c85adaf303------2</link>
            <guid isPermaLink="false">https://medium.com/p/72ba164af526</guid>
            <category><![CDATA[pgsql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[postgresql-database]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[MOHAMMAD SAMSAD HUSSAIN]]></dc:creator>
            <pubDate>Thu, 15 Jan 2026 07:37:16 GMT</pubDate>
            <atom:updated>2026-01-15T07:37:16.439Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong><em>Why </em></strong>work_mem<strong><em> Is the Only Setting That Actually Changes PostgreSQL Sorting : Part 3</em></strong></h3><p>🍀 In <a href="https://medium.com/@its-me-sam/why-doubling-ram-didnt-speed-up-postgresql-1ab66a557563"><strong>Part 1</strong></a>, we learned that doubling system RAM barely changed PostgreSQL’s behavior.<br>🍀 In <a href="https://medium.com/@its-me-sam/why-increasing-shared-buffers-didnt-fix-my-postgresql-sort-part-2-7d3da8bf9067"><strong>Part 2</strong></a>, we killed another myth: increasing shared_buffers didn’t stop disk-based sorting either.</p><p>🆆🅴🅻🅲🅾🅼🅴 to <strong>Group C</strong> — where work_mem finally steps into the spotlight.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BccNSqCcVG8_JF_4B50Vhg.png" /></figure><h3>🧠 Group C — When work_mem Changes Everything</h3><h3>Scenario C1, C2 &amp; C3</h3><p>In this Scenario, only one variable changed: work_mem.<br>All other factors (system RAM, shared_buffers, query, dataset) remained identical to Group B.”</p><pre>| Item           | Scenario C1    | Scenario C2         | Scenario C3     |<br>| -------------- | ---------------| ------------------- | --------------- |<br>| System RAM     | 20 GB          | 20 GB               | 20 GB           |<br>| shared_buffers | 5 GB           | 5 GB                | 5 GB            |<br>| work_mem       | 64 MB  🟡      | 1 GB  🟠             | 2 GB  🟢        |<br>| Index          | ❌ No          | ❌ No                | ❌ No           |<br>| Query          | Same           | Same                | Same            |<br>| Dataset size   | ~6.9 GB        | ~6.9 GB             | ~6.9 GB         |</pre><h3>🎯 What This Scenario Is Testing</h3><p>At this point, we’ve already ruled out:</p><p>☰ System RAM alone ❌</p><p>☰ Bigger shared_buffers ❌</p><p>☰ OS cache effects ❌</p><p>So this experiment isolates one question:</p><blockquote><strong><em>Can increasing </em></strong><strong><em>work_mem eliminate disk-based sorting</em></strong><em>�</em></blockquote><h3>🔍 Query Used for All Tests</h3><p>To keep results comparable with Part 1 &amp; <a href="https://medium.com/@its-me-sam/why-increasing-shared-buffers-didnt-fix-my-postgresql-sort-part-2-7d3da8bf9067">Part 2</a>, <strong>the exact same query</strong> was used:</p><pre>EXPLAIN (ANALYZE, BUFFERS)<br>SELECT id, created_at, category, amount,payload FROM sb_big ORDER BY payload;</pre><h3>⏱️ Execution Time</h3><p>At first glance, increasing work_mem <em>should</em> dramatically reduce execution time.</p><p>After all, we’re moving from disk-based sorting toward in-memory operations.</p><p>But once again, the raw numbers tell a more nuanced story.</p><blockquote><em>“If the sort is finally in memory, shouldn’t the query be much faster?”</em></blockquote><p>📊 Let’s look at the results side by side.</p><pre>           Execution Time Comparison - Group C<br><br>| Scenario | work_mem | Sort Method       | Execution Time |<br>| -------- | -------- | ----------------- | -------------- |<br>| C1       | 64 MB    | External merge    | ~73 sec        |<br>| C2       | 1 GB     | External merge    | ~71 sec        |<br>| C3       | 2 GB     | Quicksort (RAM)   | ~77 sec        |</pre><p><strong>🤔 “Wait… Why Didn’t Execution Time Drop?”</strong></p><p>This is the same trap we encountered in <strong>Group A (</strong><a href="https://medium.com/@its-me-sam/why-doubling-ram-didnt-speed-up-postgresql-1ab66a557563"><strong>part 1</strong></a><strong>) </strong>and <strong>Group B (</strong><a href="https://medium.com/@its-me-sam/why-increasing-shared-buffers-didnt-fix-my-postgresql-sort-part-2-7d3da8bf9067"><strong>part 2</strong></a><strong>)</strong>.</p><p>At first glance, the numbers seem disappointing.</p><blockquote><em>Execution time here is dominated by sequential scanning and tuple processing — not sorting alone.<br>Eliminating disk-based sorting changes behavior, not necessarily wall-clock time.</em></blockquote><blockquote><em>execution time ≠ execution strategy</em></blockquote><p>But execution time alone doesn’t tell the full story.</p><h3>💾 Sorting Behavior — Group C</h3><p>By now, a pattern should feel familiar.</p><p><strong>Unlike Groups A and B — where every scenario used external merge sorting — Group C is the first time PostgreSQL switches algorithms.</strong></p><p>🧩 In <strong>Group A (</strong><a href="https://medium.com/@its-me-sam/why-doubling-ram-didnt-speed-up-postgresql-1ab66a557563"><strong>Part 1</strong></a><strong>)</strong>, more system RAM didn’t change sorting behavior.<br>🧩 In <strong>Group B (</strong><a href="https://medium.com/@its-me-sam/why-increasing-shared-buffers-didnt-fix-my-postgresql-sort-part-2-7d3da8bf9067"><strong>Part 2</strong></a><strong>)</strong>, larger shared_buffers didn’t either.</p><p>So the question for Group C was simple:</p><blockquote><strong>Can increasing </strong><strong>work_mem finally eliminate disk-based sorting?</strong></blockquote><p>Let’s look at <strong>all Group C scenarios together</strong>.</p><pre>| Scenario| work_mem | Sort Method     | Disk Usage per Worker| Temp Files |<br>| --------| -------- | ----------------| ---------------------| ---------- |<br>| C1      | 64 MB    | External merge  | ~865–1178 MB         | ✅ Yes     |<br>| C2      | 1 GB     | External merge  | ~960 MB              | ✅ Yes     |<br>| C3      | 2 GB     | Quicksort (RAM) | 0 MB                 | ❌ No      |</pre><h3>🚧 Temp File Usage</h3><p>In Group A and Group B, temp file usage told a consistent story:</p><blockquote>If a sort spills to disk, PostgreSQL creates temp files — regardless of how much RAM or shared_buffers you add.</blockquote><p>Group C finally tests whether increasing work_mem changes that behavior.</p><p>Let’s look at all three scenarios together…</p><pre>| Scenario | work_mem | temp read | temp written | Temp Files |<br>| -------- | -------- | --------- | ------------ | ---------- |<br>| C1       | 64 MB    | ~1,040,000| ~1,041,000   | ✅ Yes     |<br>| C2       | 1 GB     | ~1,020,000| ~1,021,000   | ✅ Yes     |<br>| C3       | 2 GB     | 0         | 0            | ❌ No      |</pre><p><strong>🔍 Observation from the Temp File Usage</strong></p><p>ⓘ 󠀠 In scenario <strong>C1</strong> and <strong>C2</strong>, increasing work_mem reduced temp I/O <em>slightly</em>:</p><p>⠀ ⠀ ⠀ ⠀ • ㅤFewer temp blocks were written</p><p>⠀ ⠀ ⠀ ⠀ • ㅤLess temp data was read back</p><p>⠀ ⠀ ⠀ ⠀ • ㅤBut temp files were still created</p><h3><strong>This confirms an important point:</strong>🦾</h3><blockquote><strong><em>“Reducing temp I/O is not the same as eliminating temp files — PostgreSQL only stops writing temp files when the entire sort fits inside </em></strong><strong><em>work_mem.”</em></strong></blockquote><p>As long as the sort does not fully fit in memory, PostgreSQL continues to use disk-based external merge sorting.</p><h3>✅ Scenario C1 (64 MB)</h3><p>Increasing work_mem helps reduce temp I/O, but it does <em>not</em> eliminate disk sorting unless it exceeds the memory required by a single sort node.</p><h3>⚠️ Scenario C2 (1 GB)</h3><p>work_mem is a <em>hard per-node limit</em>.<br>Even if total available memory is sufficient, <strong>a single sort node exceeding </strong><strong>work_mem by a small margin will spill to disk</strong>.</p><h3>✅ The Turning Point: Scenario C3</h3><p>Scenario <strong>C3</strong> is where temp file activity disappears completely:</p><pre>temp read = 0<br>temp written = 0</pre><p>This is the first scenario across all the three (<a href="https://medium.com/@its-me-sam/why-doubling-ram-didnt-speed-up-postgresql-1ab66a557563">Part 1</a>, <a href="https://medium.com/@its-me-sam/why-increasing-shared-buffers-didnt-fix-my-postgresql-sort-part-2-7d3da8bf9067">Part 2</a> &amp; Part 3) blog where:</p><ul><li>No temp files were created</li><li>No disk I/O occurred during sorting</li><li>The sort executed entirely in memory</li></ul><blockquote><em>This confirms that PostgreSQL’s memory accounting is conservative and predictable — once </em><em>work_mem comfortably exceeds the real per-sort requirement, disk spills disappear entirely.</em></blockquote><p>This aligns perfectly with what we saw in the execution plan:</p><pre>Sort Method: quicksort</pre><h3>🕵 The Proven Rule (Now With Evidence)</h3><blockquote><strong><em>“Temp files are not controlled by total system RAM or </em></strong><strong><em>shared_buffers — they are controlled solely by whether the operation fits inside </em></strong><strong><em>work_mem.”</em></strong></blockquote><p>Once the sort crosses that threshold, temp files disappear — instantly and completely.</p><pre>    🔗 How This Differs From Groups A &amp; B<br><br>| Group | What Changed   | Temp Files         |<br>| ----- | -------------- | -----------------  |<br>| A     | System RAM     | ❌ No change       |<br>| B     | shared_buffers | ❌ No change       |<br>| C     | work_mem       | ✅ Eliminated (C3) |</pre><p>Group C is the <strong>first time</strong> PostgreSQL fundamentally changed its behavior instead of just running a little faster.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oTKnDLjzY-GSeKQ0ZC8x5A.png" /></figure><h3>Conclusión</h3><p>Across three experiments, the conclusion is now unambiguous:</p><ul><li><a href="https://medium.com/@its-me-sam/why-doubling-ram-didnt-speed-up-postgresql-1ab66a557563"><strong>Part 1</strong></a> , showed that more RAM doesn’t change PostgreSQL behavior</li><li><a href="https://medium.com/@its-me-sam/why-increasing-shared-buffers-didnt-fix-my-postgresql-sort-part-2-7d3da8bf9067"><strong>Part 2</strong></a><strong> , </strong>showed that shared_buffers doesn’t control sorting</li><li><strong>Part 3</strong> , proves that <strong>work_mem is the only setting that actually changes how sorting happens</strong></li></ul><p>So we changed the one setting that PostgreSQL actually uses for sorting: <strong>work_mem</strong>.</p><p>And that leads directly to the next question🤔❓:</p><p>What if PostgreSQL doesn’t need to sort at all?</p><p>That’s <strong>Group D (Part 4 — the conclusion of this series)</strong> — where a properly designed <strong>index removes sorting from the execution plan entirely</strong>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=72ba164af526" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why Increasing shared_buffers Didn’t Fix My PostgreSQL Sort : Part 2]]></title>
            <link>https://its-me-sam.medium.com/why-increasing-shared-buffers-didnt-fix-my-postgresql-sort-part-2-7d3da8bf9067?source=rss-33c85adaf303------2</link>
            <guid isPermaLink="false">https://medium.com/p/7d3da8bf9067</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[shared-buffers]]></category>
            <dc:creator><![CDATA[MOHAMMAD SAMSAD HUSSAIN]]></dc:creator>
            <pubDate>Tue, 13 Jan 2026 03:12:32 GMT</pubDate>
            <atom:updated>2026-01-15T07:55:24.169Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Why Increasing </strong><strong>shared_buffers Didn’t Fix My PostgreSQL Sort: Part 2</strong></h3><p><em>Disk spills, external merge sorts, and the biggest caching myth in PostgreSQL</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hTv9AOrWJRCgF1oJGyqE6A.png" /></figure><h3>A Quick Recap</h3><p><a href="https://its-me-sam.medium.com/why-doubling-ram-didnt-speed-up-postgresql-1ab66a557563"><strong>(From Blog 1)</strong></a></p><p>In the previous article, I doubled my server RAM.</p><p>PostgreSQL barely got faster.</p><p>Even with more memory available at the OS level, the execution plan didn’t change. The ORDER BY still spilled to disk, and performance gains were marginal at best.</p><blockquote><em>⁉️ That result naturally raised the next question: if OS memory didn’t help, what about PostgreSQL’s own cache?</em></blockquote><blockquote><em>❌ </em><strong><em>More system RAM alone does not fix PostgreSQL sort performance</em></strong></blockquote><h3>“The next Move: Increase shared_buffers&quot;</h3><p>If PostgreSQL is slow, most tuning guides point to one parameter:</p><p>⠀ ⠀ ⠀ ⠀ ⠀ ⠀⠀󠀠󠀠󠀠 ⠀⠀ ⠀ ⠀ ⠀ ⠀ ⠀❝<strong><em> </em>shared_buffers</strong>❞⠀ ⠀ ⠀ 󠀠⠀ ⠀</p><p>The assumption is simple: if PostgreSQL can cache more data internally, expensive operations like large ORDER BY sorts should stop hitting disk.</p><h3>Group B Experiment — Bigger shared_buffers</h3><h3>Scenario B1</h3><p>In Scenario B1, only one variable changed (shared_buffers). Every other factor remained identical to Group A.</p><pre><strong>| Item           | Scenario A1 | Scenario A2 | Scenario B1  |<br>| -------------- | ----------- | ----------- | -----------  |</strong><br>| System RAM     | 10 GB       | 20 GB       |  20 GB       |<br>| shared_buffers | 2560 MB     | 2560 MB     |   <strong>5 GB </strong> 🟢   |<br>| work_mem       | 4 MB        | 4 MB        |   4 MB       |<br>| Index          | ❌ No       | ❌ No        |  ❌ No       |<br>| Query          | Same        | Same        |   Same       |<br>| Dataset size   | ~6.9 GB     | ~6.9 GB     | ~6.9 GB      |<br><br><br><strong>Now in Scenario B1, only one thing changed:<br><br>╰┈➤ shared_buffers doubled from 2.5 GB → 5 GB</strong></pre><h3>🎯 What This Scenario Is Testing</h3><p>This scenario <strong>isolates one question</strong>:</p><blockquote><em>If PostgreSQL is given more internal cache, will a large </em><em>ORDER BY stop spilling to disk — </em>without changing sort memory or query shape<em>?</em></blockquote><p>We are <strong>not</strong>:</p><p>☰ Changing work_mem</p><p>☰ Adding indexes</p><p>☰ Changing query shape</p><p>So any behavioral change must come from <strong>shared_buffers </strong>alone.</p><h3>🔍 Query Used for All Tests</h3><p>To keep results comparable with Blog 1, <strong>the exact same query</strong> was used:</p><pre>EXPLAIN (ANALYZE, BUFFERS)<br>SELECT id, created_at, category, amount,payload FROM sb_big ORDER BY payload;</pre><h3>⏱️ Execution Time</h3><p>At first glance, the execution time in Scenario B1 looked… <em>encouraging</em>.</p><blockquote>“It got faster — so shared_buffers worked?”</blockquote><p>With shared_buffers doubled from <strong>2.5 GB → 5 GB</strong>, the query completed slightly faster than in Group A.</p><p>But execution time alone doesn’t tell the full story.</p><pre>| Scenario | System RAM   | shared_buffers | Execution Time |<br>| -------- | ------------ | -------------- | -------------- |<br>|     A1   |   10 GB      |   2.5 GB       | ~90 sec        |  <br>|     A2   |   20 GB      |   2.5 GB       | ~69 sec        | <br><strong>|     B1   |   20 GB      |     5 GB       | ~72 sec  🟢    |</strong><br></pre><blockquote><em>✅ While execution time changed slightly, the </em>execution plan and sort behavior did not<em> — which matters far more than raw runtime.</em></blockquote><blockquote>— — —</blockquote><blockquote><em>This is the same pattern we saw in Group A: small runtime gains without any change in execution strategy.</em></blockquote><h3>💾 Sorting Behavior — Still Disk-Based</h3><pre>Sort Method: external merge<br>Disk: ~968 MB per worker</pre><p>Despite:</p><ul><li>20 GB system RAM</li><li>5 GB PostgreSQL shared memory</li></ul><p>The sort <strong>still spills to disk</strong>.</p><blockquote>shared_buffers <em>has </em><strong><em>zero influence</em></strong><em> on whether a sort fits in memory.</em></blockquote><blockquote><em>shared_buffers helps PostgreSQL </em>read data faster<em>, not </em>reorder data differently<em>.</em></blockquote><h3>🚧 Temp File Usage — No Meaningful Reduction</h3><pre>+----------+------------+----------------+-------------+--------------+<br>| Scenario | System RAM | shared_buffers | temp read   | temp written |<br>+----------+------------+----------------+-------------+--------------+<br>| A1       | 10 GB      | 2.5 GB         | ~1,090,000  | ~1,090,000   |<br>| A2       | 20 GB      | 2.5 GB         | ~1,090,000  | ~1,090,000   |<br><strong>| B1       | 20 GB      | 5 GB           | 1,089,595   | 1,090,601    |<br></strong>+----------+------------+----------------+-------------+--------------+</pre><p><strong>🔍 Observation from the Temp File Usage</strong></p><p>ㅤㅤⓘ 󠀠 Temp file usage remained <strong>identical</strong> across all three scenarios</p><p>⠀ ⠀ⓘ 󠀠 Doubling system RAM <strong>did not reduce</strong> temp I/O</p><p>ㅤㅤⓘ 󠀠 Despite Doubling shared_buffers , <strong>did not reduce</strong> temp I/O</p><p>⠀ ⠀ⓘ 󠀠 PostgreSQL still had to write <strong>~1 GB of sort data per worker</strong> to disk</p><p>⚠️ The sort <strong>never moved into memory</strong>.</p><blockquote>Temp files are not a side effect — they are the <strong>core symptom</strong> of disk-based sorting.</blockquote><p>As we see:</p><pre>Sort Method: external merge<br>temp read ≈ temp written ≈ millions of blocks</pre><p>It means:</p><p>PostgreSQL is still bottlenecked by work_mem, not by cache size. No amount of extra caching can change this behavior.</p><h3><strong>Conclusión</strong></h3><ul><li>If temp file usage doesn’t drop, the sort problem is still unsolved.</li><li>Group B proves that increasing shared_buffers improves caching efficiency — but it does <strong>nothing</strong> to prevent disk-based sorting. For ORDER BY, the bottleneck isn’t cache size; it’s sort memory.</li></ul><p>And that leads directly to the next question🤔❓:</p><p>Which PostgreSQL memory setting actually controls sort behaviour?And that leads directly to <a href="https://its-me-sam.medium.com/why-work-mem-is-the-only-setting-that-actually-changes-postgresql-sorting-part-3-72ba164af526"><strong>Part 3</strong></a><strong> of this series</strong>:</p><p><strong>Group C (</strong><a href="https://its-me-sam.medium.com/why-work-mem-is-the-only-setting-that-actually-changes-postgresql-sorting-part-3-72ba164af526"><strong>Part 3</strong></a><strong>)— Which PostgreSQL memory setting actually controls sort behaviour?</strong></p><p>In the next blog, we isolate work_mem — and for the first time in this series, PostgreSQL’s sort behaviour truly changes.</p><p>#PostgreSQL #Postgres #work_mem #shared_buffers #Database #SQL</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7d3da8bf9067" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why Doubling RAM Didn’t Speed Up PostgreSQL — and One Setting That Did]]></title>
            <link>https://its-me-sam.medium.com/why-doubling-ram-didnt-speed-up-postgresql-1ab66a557563?source=rss-33c85adaf303------2</link>
            <guid isPermaLink="false">https://medium.com/p/1ab66a557563</guid>
            <category><![CDATA[postgresql-database]]></category>
            <category><![CDATA[posgresql]]></category>
            <category><![CDATA[database-tuning]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[MOHAMMAD SAMSAD HUSSAIN]]></dc:creator>
            <pubDate>Mon, 05 Jan 2026 09:25:34 GMT</pubDate>
            <atom:updated>2026-01-13T03:19:50.605Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Why Doubling RAM Didn’t Speed Up PostgreSQL — and One Setting That Did : Part 1</strong></h3><p><strong>I doubled the RAM on my PostgreSQL server. 🟥🟪🟦🟩🟨🟧</strong></p><p>The query got… barely faster. 𓆉</p><p>Then I changed a single PostgreSQL setting from <strong>4MB to 2GB</strong>, and something far more important than runtime changed — the <strong>execution plan itself</strong>.</p><blockquote><em>If you’ve ever scaled hardware and wondered why PostgreSQL ignored it, this will save you hours of guesswork.</em></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Iviu_V-SUXgaD2PyYQ3CEg.png" /></figure><p>In this article, I’ll show:</p><blockquote>🤔 why adding more system memory didn’t help,</blockquote><blockquote>🤔 which PostgreSQL memory setting actually controlled the bottleneck,</blockquote><blockquote>🤔 and why a well-chosen index eliminated the problem entirely.</blockquote><p>All results are backed by real EXPLAIN (ANALYZE, BUFFERS) output from a <strong>30-million-row table</strong>, not theory or tuning myths.</p><h3>“The Mental Model Most PostgreSQL Memory Tuning Gets Wrong”</h3><p>When people say “PostgreSQL needs more memory,” they usually mean one thing:</p><p><strong>➕ *Give the server more RAM*</strong></p><p>But PostgreSQL doesn’t use memory as a single pool.</p><p>In fact, most performance problems come from **confusing three very different layers of memory**:</p><p>- OS page cache<br>- `shared_buffers`<br>- per-operation memory like `work_mem`</p><pre>PostgreSQL&#39;s Memory Kitchen<br>┌─────────────────────────────────────────────────────────────┐<br>│                    SHARED KITCHEN (shared_buffers)          │<br>│  ┌──────────────────────────────────────────────────────┐   │<br>│  │  🍲 Buffer Cache (Clock-sweep)                       │   │<br>│  │  • Frequently-used table/index pages live here       │   │<br>│  │  • Hit = Instant meal | Miss = Go shopping (disk I/O)│   │<br>│  └──────────────────────────────────────────────────────┘   │<br>└───────────────────────────┬─────────────────────────────────┘<br>                            │<br>┌───────────────────────────▼────────────────────────────────┐<br>│                    PRIVATE COUNTERS (work_mem)             │<br>│  ┌──────────────────────────────────────────────────────┐  │<br>│  │  🥣 Per-Operation Workspace                          │  │<br>│  │  • Sorts, Hashes, Merges                             │  │<br>│  │  • Too small = Spills to disk (messy counter)        │  │<br>│  │  • Too large = Wastes space, starves others          │  │<br>│  └──────────────────────────────────────────────────────┘  │<br>└────────────────────────────────────────────────────────────┘</pre><p>Adding more RAM mostly helps **only one** of these — and in this case, it wasn’t the one limiting the query.</p><p><strong>Before changing any PostgreSQL settings, let’s examine the baseline </strong><strong>EXPLAIN (ANALYZE, BUFFERS) output for the query.</strong></p><h3>🧪 Group A — Does More System RAM Automatically Improve Performance?</h3><h3>Scenario A1 vs Scenario A2</h3><p>Before tuning any PostgreSQL parameters, I started with the most common assumption in database performance:</p><blockquote>“If I double the server RAM, PostgreSQL should automatically get faster.”</blockquote><p>So I designed <strong>Group A</strong> to test exactly that — <strong>and nothing else</strong>.</p><pre><strong>| Item           | Scenario A1 | Scenario A2 |</strong><br>| -------------- | ----------- | ----------- |<br>| System RAM     | 10 GB       | 20 GB       |<br>| shared_buffers | 2560 MB     | 2560 MB     |<br>| work_mem       | 4 MB        | 4 MB        |<br>| Index          | ❌ No       | ❌ No        |<br>| Query          | Same        | Same        |<br>| Dataset size   | ~6.9 GB     | ~6.9 GB     |<br><br><strong>👉 The only difference between the two runs was system RAM.</strong></pre><h3>🔍 Query Used for All Tests</h3><pre>EXPLAIN (ANALYZE, BUFFERS)<br>SELECT id, created_at, category, amount,payload FROM sb_big ORDER BY payload;</pre><h3><strong>⏱️ Execution Time</strong></h3><blockquote><em>✅ About 23% faster after doubling RAM — </em><strong><em>without PostgreSQL changing its behavior at all.</em></strong></blockquote><p>At first glance, this looks like a win.</p><p>But <strong>runtime alone doesn’t tell the truth</strong> — the execution plan does.</p><pre><strong>| Scenario       | Execution Time |</strong><br>| -------------- | -------------- |<br>| A1 — 10 GB RAM | ~90.4 seconds  |<br>| A2 — 20 GB RAM | ~69.4 seconds  |</pre><h3>🧠 shared_buffers Behavior (Critical Insight)</h3><p>Both scenarios (A1 &amp; A2 ) reported almost identical buffer usage:</p><pre>Buffers: shared hit=74 read=882353</pre><p>What this tells us:</p><ul><li>Almost <strong>all table pages were read from disk</strong></li><li>Very little came from PostgreSQL’s buffer cache</li><li>shared_buffers (2.5 GB) was <strong>far smaller</strong> than the table (~6.9 GB)</li></ul><h3>💾 Sorting Behavior — Still Disk-Based</h3><p>In <strong>both A1 and A2</strong>, the sort looked like this:</p><pre>Sort Method: external merge<br>Disk: ~970 MB<br>temp read/write: ~1.09M blocks</pre><p>Meaning:</p><ul><li>work_mem = 4MB was far too small</li><li>PostgreSQL <strong>spilled the sort to disk</strong></li><li>The execution plan was <strong>identical</strong></li></ul><p>👉 PostgreSQL’s behavior <strong>did not change at all</strong>.</p><h3><strong>⁉️ So Where Did the Speedup Come From?</strong></h3><blockquote><strong><em>The performance improvement was 100% OS-driven(</em>Linux page cache</strong>)<strong><em> and 0% PostgreSQL-tuning-driven.</em></strong></blockquote><pre>| 🧩 Aspect                | 🟥 PostgreSQL — **Not Improved** | 🟩 Operating System — **Improved** |<br>| ------------------------ | -------------------------------- | ---------------------------------- |<br>| 🧠 Memory configuration  | ❌ `shared_buffers` unchanged     | ✅ More RAM available to OS         |<br>| 🛠️ Query working memory | ❌ `work_mem` unchanged           | ✅ OS cached more data              |<br>| 🔃 Sort method           | ❌ External merge sort            | ✅ Temp files cached in RAM         |<br>| 📦 Table data handling   | ❌ No extra buffering             | ✅ Table pages cached in page cache |<br>| 💽 Disk I/O volume       | ❌ Same read/write count          | ✅ Faster re-reads from memory      |<br>| 👀 Awareness of RAM      | ❌ PostgreSQL unaware             | ✅ OS fully utilized extra RAM      |<br>| ⚙️ Tuning effect         | ❌ No direct PG tuning benefit    | ✅ Indirect performance boost       |<br>| 🚀 Final outcome         | ➖ PostgreSQL behavior unchanged  | ⚡ Queries completed faster         |</pre><p><strong>Most tuning advice stops at: </strong><em>“Add more RAM”</em></p><p><strong>This experiment shows the uncomfortable truth: </strong><em>Without tuning PostgreSQL itself, RAM delivers diminishing returns.</em></p><p>That’s not theory — it’s visible in <strong><em>EXPLAIN (ANALYZE, BUFFERS)</em></strong>.</p><h3>🔜 What Comes Next</h3><p>More RAM didn’t change the plan. The next obvious question is:</p><p>🤔❓<strong>Does increasing </strong><strong>shared_buffers actually fix sorting?</strong></p><p><strong>Part 2 (</strong><a href="https://medium.com/@its-me-sam/why-increasing-shared-buffers-didnt-fix-my-postgresql-sort-part-2-7d3da8bf9067"><strong>Group B</strong></a><strong> )answers that question — coming up in the next blog.</strong></p><p>#PostgreSQL #Postgres #work_mem #shared_buffers #Database #SQL</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1ab66a557563" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PostgreSQL Architecture: A Journey from Your Query to the Disk]]></title>
            <link>https://its-me-sam.medium.com/postgresql-architecture-a-journey-from-your-query-to-the-disk-e865e036817f?source=rss-33c85adaf303------2</link>
            <guid isPermaLink="false">https://medium.com/p/e865e036817f</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgresql-architecture]]></category>
            <dc:creator><![CDATA[MOHAMMAD SAMSAD HUSSAIN]]></dc:creator>
            <pubDate>Tue, 23 Dec 2025 04:27:11 GMT</pubDate>
            <atom:updated>2025-12-23T05:31:11.644Z</atom:updated>
            <content:encoded><![CDATA[<blockquote><strong><em>PostgreSQL power emerges not from a single component, but from the synergy of its processes, memory, and storage; an architecture engineered for reliable, concurrent, and high-speed data operations</em></strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2Q6cgHqm2uTxilclNQOP9w.png" /></figure><p><strong>A query like SELECT * FROM users is a direct ticket into PostgreSQL’s engine </strong>⚙️🛠️<strong> room. It’s a journey through an integrated architecture where processes, memory, and storage work in concert to guarantee reliable, concurrent, and fast operations.</strong></p><h3>🚪<strong>Front Door : Client-Server and the </strong>👮<strong>Postmaster</strong></h3><p>Like most modern databases, PostgreSQL is built on a client-server model. Your application — whether a website backend, a data analytics tool, or the psql command line — is the client. The PostgreSQL engine running on a server is the server.</p><p>The welcoming committee at the server is a special process called the Postmaster (or the PostgreSQL server process). Its core duties 💼 are:</p><pre>╔═══════════════════════════════════════════════════════════════════╗<br>║                    The PostgreSQL Postmaster                      ║<br>║                      Your Database&#39;s Concierge                    ║<br>╠═══════════════════╦═══════════════════╦══════════════════════════╣<br>║    🚀 BOOTING     ║    👂 LISTENING    ║    👔 ASSIGNING          ║<br>║    THE SYSTEM     ║    FOR GUESTS     ║    A DEDICATED BUTLER    ║<br>╠═══════════════════╬═══════════════════╬══════════════════════════╣<br>║ • Starts shared   ║ • Listens on port ║ • Creates new Backend    ║<br>║   memory pools    ║   5432 for        ║   Process via fork()     ║<br>║ • Launches        ║   connections     ║ • Each client gets       ║<br>║   background      ║ • Waits for       ║   exclusive process      ║<br>║   workers         ║   client requests ║ • Process handles all    ║<br>║ • Prepares the    ║ • PostgreSQL&#39;s    ║   queries for that       ║<br>║   database engine ║   front door      ║   session                ║<br>║                   ║                   ║ • Core architecture      ║<br>║                   ║                   ║   feature                ║<br>╚═══════════════════╩═══════════════════╩══════════════════════════╝</pre><p>This “process-per-connection” model is a key differentiator. Unlike some databases that use lightweight threads within a single process, PostgreSQL spawns a full operating system process for each connection.</p><blockquote><strong>“Once the Postmaster assigns you a dedicated backend process (your personal chef), your query moves into the kitchen’s core workspace: memory and concurrent processing.”</strong></blockquote><h3>The Kitchen: Processes and Memory at Work</h3><p>The dedicated backend process now has query. To execute it, it needs resources and help from specialized staff working in shared spaces.</p><h3><strong>1. The Specialized Staff: Background Workers</strong></h3><pre>┌─────────────────────────────────────────────────────────────────────────────┐<br>│                 THE SPECIALIZED STAFF: BACKGROUND WORKERS                   │<br>├────────────────┬──────────────────┬──────────────────┬──────────────────────┤<br>│   PROCESS      │   PRIMARY ROLE   │  KEY OPERATION   │   SYSTEM IMPACT      │<br>├────────────────┼──────────────────┼──────────────────┼──────────────────────┤<br>│                │                  │                  │                      │<br>│  <strong>✓📍</strong>           │  Data Persistence│  Flushes dirty   │  • Ensures ACID      │<br>│  Checkpointer  │  &amp; Recovery      │  buffers to disk │    compliance        │<br>│   &amp;            │  Management      │  at checkpoints  │  • Creates recovery  │<br>│  Background    │                  │  • Manages WAL   │    points            │<br>│   Writer       │                  │    recycling     │  • Controls I/O      │<br>│                │                  │                  │    spikes            │<br>│                │                  │                  │                      │<br>├────────────────┼──────────────────┼──────────────────┼──────────────────────┤<br>│                │                  │                  │                      │<br>│   📝           │  Transaction     │  Writes WAL      │  • Guarantees        │<br>│  WAL Writer    │  Durability &amp;    │  before data     │    durability        │<br>│                │  Crash Recovery  │  • Sequential    │  • Enables point-in- │<br>│                │                  │    append-only   │    time recovery     │<br>│                │                  │  • Supports      │  • Powers replication│<br>│                │                  │    replication   │                      │<br>│                │                  │                  │                      │<br>├────────────────┼──────────────────┼──────────────────┼──────────────────────┤<br>│                │                  │                  │                      │<br>│   🧹           │  Storage         │  Removes dead    │  • Prevents table    │<br>│  Autovacuum    │  Optimization &amp;  │  tuples from     │    bloat             │<br>│   Launcher     │  MVCC Cleanup    │  MVCC            │  • Updates table     │<br>│                │                  │  • Updates       │    statistics        | <br>│                │                  │    statistics    │  • Reclaims disk     │<br>│                │                  │  • Prevents XID  │    space             │<br>│                │                  │    wraparound    │                      │<br>│                │                  │                  │                      │<br>└────────────────┴──────────────────┴──────────────────┴──────────────────────┘</pre><h3><strong>2. The Shared and Private Workspaces: Memory</strong></h3><p>Memory is where the action happens, divided into shared and private areas:</p><ul><li>Shared Memory is the communal workspace for all processes.</li><li>Local Memory is the private workspace allocated to each backend process for its specific task.</li></ul><pre>                  THE SHARED &amp; PRIVATE WORSPACE: MEMORY<br>┌────────────────────────────────────────────────────────────────────────────┐<br>│                                                                            │<br>│  SHARED MEMORY (Communal Kitchen)       LOCAL MEMORY (Personal Prep Space) │<br>│  ─────────────────────────────────       ────────────────────────────────  │<br>│                                                                            │<br>│  ┌──────────────────────────────┐        ┌─────────────────────────────┐   │<br>│  │  <strong> SHARED BUFFERS </strong>            │        │  <strong> work_mem  </strong>                │   │<br>│  │  <strong>(Pantry &amp; Refrigerator) </strong>    │        │  <strong>(Personal Mixing Bowl) </strong>    │   │<br>│  │                              │        │                             │   │<br>│  │  • Everyone accesses         │        │  • Your private space       │   │<br>│  │  • Common ingredients        │        │  • Size depends on your     │   │<br>│  │  • Frequently used items     │        │    task complexity          │   │<br>│  │  • Restocked from market     │        │  • Too small = spill to     │   │<br>│  │    (disk) when empty         │        │    counter (disk)           │   │<br>│  │                              │        │  • Too large = waste space  │   │<br>│  └──────────────────────────────┘        └─────────────────────────────┘   │<br>│            │                                         │                     │<br>│  ┌──────────────────────────────┐        ┌─────────────────────────────┐   │<br>│  │   <strong>WAL BUFFERS </strong>               │        │   <strong>maintenance_work_mem </strong>     │   │<br>│  │  <strong>(Order Tickets Clipboard)</strong>   │        │  <strong>(Large Cleaning Bucket)</strong>    │   │<br>│  │                              │        │                             │   │<br>│  │  • Temporary order notes     │        │  • Deep cleaning tasks      │   │<br>│  │  • Batch processing          │        │  • Kitchen reorganization   │   │<br>│  │  • Before entering official  │        │  • Occasional big projects  │   │<br>│  │    order book (WAL files)    │        │  • Not for daily cooking    │   │<br>│  │  • Smooths kitchen workflow  │        │                             │   │<br>│  └──────────────────────────────┘        └─────────────────────────────┘   │<br>│                                                                            │<br>│  <strong>Key Principle</strong>: Shared resources for efficiency, private space for         │<br>│  isolated work without contention. Balance is critical for optimal         │<br>│  performance with multiple chefs (concurrent connections).                 │<br>│                                                                            │<br>└────────────────────────────────────────────────────────────────────────────┘</pre><h3><strong>3. FROM QUERY TO PLAN: THE EXECUTION PIPELINE</strong></h3><p>Client simple SELECT now enters a multi-stage pipeline within backend process:</p><pre>QUERY → PARSE → REWRITE → PLAN → EXECUTE → RESULTS<br>   │        │        │        │        │        │<br>   └────────┴────────┴────────┴────────┴────────┘<br>   From your SQL text         To your data<br>   to executable steps        in milliseconds</pre><p><strong><em>STAGE 1: PARSE (The Order Taker)</em></strong></p><ul><li>Breaks down your SQL text into understandable pieces</li><li>Checks basic grammar and syntax</li><li>Converts SELECT * FROM users into structured tokens</li></ul><p><em>“Think of it as the server writing down your order clearly before passing it to the kitchen.”</em></p><p><strong><em>STAGE 2: </em>REWRITE <em>(The Menu Translator)</em></strong></p><ul><li>Applies rules and expands shortcuts</li><li>Converts views into their underlying tables</li><li>Transforms: SELECT * FROM active_users → SELECT * FROM users WHERE status=&#39;active&#39;</li></ul><p><em>“Like translating “the usual” into your actual regular order details”</em></p><p><strong><em>STAGE 3: PLAN/OPTIMIZE (The Head Chef’s Strategy Session)</em></strong></p><ul><li>Evaluates thousands of possible execution paths</li><li>Decides: Index scan or table scan? Hash join or nested loop?</li><li>Chooses the fastest, most efficient approach using cost-based calculations</li></ul><p><em>“The chef decide whether to use pre-prepped ingredients or cook fresh, which stations to use, and in what order.”</em></p><p><strong><em>STAGE 4: EXECUTE (The Cooking &amp; Plating)</em></strong></p><ul><li>Runs the chosen plan step by step</li><li>Fetches data from shared buffers (RAM cache)</li><li>Performs calculations, joins, sorts, and filters</li><li>Streams results back to you immediately</li></ul><blockquote><strong>“After the Executor fetches data from memory, where does that data permanently live? Let’s step into the restaurant’s archives and cold storage.”</strong></blockquote><h3><strong>4. THE VAULT </strong>🗄 <strong>: WHERE DATA LIVES ON DISK </strong>⛁</h3><p>When data isn’t in memory, it resides in a structured but logical layout on disk. Understanding this helps demystify database operations.</p><p><strong>THE POSTGRESQL STORAGE VAULT (The Restaurant’s Cold Storage &amp; Archive System)</strong></p><ol><li><strong>CLUSTER vs. DATABASE<br><em>(The Restaurant vs. Different Menu Categories)</em></strong></li></ol><ul><li>Cluster = The entire restaurant with all its kitchens</li><li>Contains multiple Databases = Different menu sections (Appetizers, Main Courses, Desserts)</li><li>Default databases created when you open a restaurant:</li></ul><pre>postgres = Main dining menu<br>template1 = Customizable menu template<br>template0 = Pristine, never-changed master recipe book</pre><ul><li>One restaurant (cluster) can serve different types of cuisine (databases) all using the same kitchen infrastructure</li></ul><p><strong>2. TABLESPACES<br><em>(Different Storage Rooms in the Restaurant)</em></strong></p><ul><li>Physical storage locations on different shelves or rooms</li><li>pg_default = Main walk-in refrigerator (customer orders)</li><li>pg_global = Chef&#39;s private spice rack (kitchen system ingredients)</li><li>Custom tablespaces = Additional freezers, dry storage, or off-site cold storage</li><li><em>Like storing meats in one freezer, vegetables in another, and dry goods in the pantry — all for organization and performance</em></li></ul><p><strong>3. FILES AND PAGES<br><em>(Storage Containers with Organized Compartments)</em></strong></p><ul><li>Each table/index = A specific storage container (file)</li><li>Files divided into 8KB Pages = Standardized kitchen storage trays</li><li>Each page/tray contains:</li></ul><pre>Header = Tray label and expiration date<br>Item IDs = Map to specific ingredient slots<br>Row Data = Actual food ingredients</pre><ul><li>MVCC updates = Put fresh ingredients in empty slot, mark old ones for disposal</li><li>Like a prep cook’s mise en place tray with compartments for each ingredient, where old vegetables get replaced with fresh ones</li></ul><p><strong>4. WRITE-AHEAD LOGGING (WAL)<br><em>(The Kitchen’s Prep Log &amp; Recipe Journal)</em></strong></p><ul><li>Every ingredient change logged BEFORE actual cooking begins</li><li>WAL Files = Permanent kitchen preparation journal</li><li>If kitchen accident occurs: Re-read the prep log to reconstruct all completed dishes</li><li>Enables special features:</li></ul><pre>Point-in-time recovery = &quot;Recook all dishes&quot;<br>Replication = Live copy of prep log at another restaurant location</pre><ul><li><em>Like writing down every ingredient measurement in the recipe book before actually mixing anything — ensures nothing is lost</em></li></ul><h3>Conclusion: An Architecture Built for Reliability</h3><p>From the welcoming Postmaster (concierge) to the specialized Background Workers (kitchen staff), the efficient Shared and Local Memory (communal and private workspaces), the intelligent Query Pipeline (head chef’s strategy), and the durable Storage Vault (organized cold storage with its unbreakable recipe journal), every component of PostgreSQL is engineered for one overarching goal: data reliability.</p><p>This tour from your SELECT * FROM users to the disk and back shows that PostgreSQL&#39;s power doesn&#39;t come from a single feature, but from the seamless synergy of its parts—all ensuring your data is served correctly, consistently, and safely, no matter what. It&#39;s not just a database; it&#39;s a meticulously architected system for trust.</p><p>#PostgreSQL #PostgreSQL Architecture #postgresql #postgres #database Architecture</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e865e036817f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Leveraging AI: Friend or Foe to Your Job?]]></title>
            <link>https://its-me-sam.medium.com/leveraging-ai-friend-or-foe-to-your-job-a0cb8899b027?source=rss-33c85adaf303------2</link>
            <guid isPermaLink="false">https://medium.com/p/a0cb8899b027</guid>
            <dc:creator><![CDATA[MOHAMMAD SAMSAD HUSSAIN]]></dc:creator>
            <pubDate>Fri, 12 Apr 2024 11:26:03 GMT</pubDate>
            <atom:updated>2024-04-12T11:26:03.152Z</atom:updated>
            <content:encoded><![CDATA[<p>Artificial intelligence (AI) is quickly changing our world, and the workplace is not an exception at all. Although the media often highlights AI displacing jobs, the actual situation is more complex. AI has the potential to enhance our abilities rather than simply taking over them. Let’s explore the effects of AI on employment through real-world instances and consider ways to navigate this changing scenario.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U0fAC_uynC5IyBOUWa-FSA.png" /></figure><h3>Disruption:</h3><p>AI has started changing job markets globally by being incorporated into different sectors. Tasks that used to be done only by people are now being automated, causing worries about job loss and lack of employment. Jobs that follow a routine and repetitive pattern in fields such as manufacturing, administration, and customer service are at a high risk of being automated.</p><p>Furthermore, AI-driven algorithms are revolutionizing sectors like finance, healthcare, and transportation. From using predictive analytics in finance to implementing personalized medicine in healthcare, AI is transforming procedures, improving effectiveness, and altering the skillsets needed to succeed in these sectors.</p><p><strong>From a Loan Officer to an AI Analyst :</strong> Banks are more and more turning to AI to streamline loan requests. Although certain loan officer roles may be impacted, artificial intelligence enables human specialists to focus on intricate loan evaluations and client support.</p><p><strong>Self-driving : </strong>Self-driving trucks have the capability to shake up the long-haul trucking sector. Nonetheless, AI-driven trucks may still need supervision from humans and maintenance workers, leading to the emergence of new employment prospects.</p><p><strong>AI Powered Editing: </strong>The use of AI-powered editing is increasing among journalists and content creators for tasks such as grammar checks, plagiarism detection, and content optimization. These tools do not take the place of writers, but enhance productivity and enable them to concentrate on creative elements.</p><ul><li><strong>What would AI possibly write in its diary entry about the day it took over jobs? 📓😂</strong></li><li><strong>If AI was acting as your personal assistant for a day, what task would you assign to it first, and for what reason? Can you rely on it to excel in your math assignments or make the perfect cup of coffee? ☕🤖</strong></li><li><strong>Consider AI as a companion who takes your belongings and gives them back improved. 🤖💼</strong></li><li><strong>What do you believe your ideal position would be if AI were to design a unique job specifically for you? Head Robot Whisperer? Specialist in AI Memes? What unique job title do you have? 🌟</strong></li><li><strong>What skill would you choose to acquire in a future world dominated by AI? Do you want to master coding like a wizard or excel in data science like a sage? 🧙‍♂️💻</strong></li><li><strong>What do you believe would be the most challenging lesson for AI to grasp if it attended an ethics course? Would it have difficulty grasping human emotions, or the ideas of morality in a binary world?</strong></li><li><strong>What motivational message do you believe AI would create regarding the future of employment? Remain calm and continue coding. Could it be, “When confronted with automation, think creatively as if tomorrow doesn’t exist”?</strong></li></ul><p>Conclusion:</p><p>It is probable that the future work environment will experience a change in the direction of collaboration between humans and artificial intelligence. AI will take care of tasks that involve data and are repetitive, allowing people to concentrate on creative activities, solving problems, and interacting with others. This will necessitate a workforce that can easily adapt, constantly learn, and feel at ease collaborating with smart machines.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a0cb8899b027" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>