<?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 Mbhatt on Medium]]></title>
        <description><![CDATA[Stories by Mbhatt on Medium]]></description>
        <link>https://medium.com/@mbhatt2018?source=rss-800296c4f3ee------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*dmbNkD5D-u45r44go_cf0g.png</url>
            <title>Stories by Mbhatt on Medium</title>
            <link>https://medium.com/@mbhatt2018?source=rss-800296c4f3ee------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 17:15:54 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@mbhatt2018/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[How We Supercharged Our Snapshot Table with PostgreSQL Partitioning & Saved Big on Infrastructure]]></title>
            <link>https://medium.com/@mbhatt2018/how-we-supercharged-our-snapshot-table-with-postgresql-partitioning-saved-big-on-infrastructure-2d9c10d23254?source=rss-800296c4f3ee------2</link>
            <guid isPermaLink="false">https://medium.com/p/2d9c10d23254</guid>
            <category><![CDATA[architecture-optimization]]></category>
            <category><![CDATA[database-performance]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[aws-cost-optimization]]></category>
            <category><![CDATA[postgresql-partitioning]]></category>
            <dc:creator><![CDATA[Mbhatt]]></dc:creator>
            <pubDate>Sat, 09 Aug 2025 17:17:42 GMT</pubDate>
            <atom:updated>2025-08-09T17:17:42.301Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>By Manoj Bhatt</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yVG4P4uvgq66EAzQBbRX9w.png" /></figure><h3>The Challenge</h3><p>Our Snapshot table was becoming a serious bottleneck.<br> It was:</p><ul><li>Growing rapidly with millions of records.</li><li>Slowing down query performance due to sequential scans.</li><li>Increasing EC2 costs since we needed high-spec instances to handle peak loads.</li><li>Consuming unnecessary proxy bandwidth because of duplicate URL crawling.</li></ul><p>We were running on an Amazon EC2 c5.9xlarge instance — great performance, but expensive for something that was mainly a database issue.</p><h3>The Solution</h3><p>We tackled the problem in two big steps.</p><h3>1️⃣ Time-Based Partitioning</h3><p>We switched snapshot to range partitioning by created_on.<br> Instead of one massive table, we now have daily partitions.</p><p><strong>Benefits:</strong></p><ul><li>Query speed improved — indexes are smaller &amp; more focused.</li><li>Dropping old data is instant (DROP TABLE partition instead of deleting millions of rows).</li><li>PostgreSQL statistics are accurate for each partition.</li><li>Reduce index bloat.</li></ul><p><strong>DDL Example:</strong></p><pre>CREATE TABLE snapshot (<br>    id BIGSERIAL,<br>    rss_feed_id BIGINT NOT NULL,<br>    url TEXT NOT NULL,<br>    created_on DATE NOT NULL,<br>    PRIMARY KEY (id, created_on)<br>) PARTITION BY RANGE (created_on);</pre><p><strong>Daily partition creation:</strong></p><pre>CREATE TABLE snapshot_2025_08_08<br>    PARTITION OF snapshot<br>    FOR VALUES FROM (&#39;2025-08-08&#39;) TO (&#39;2025-08-09&#39;);</pre><h3>2️⃣ Separate URL Uniqueness Table</h3><p>Duplicate crawling was a silent cost drain.<br> Checking for duplicates in the massive snapshot table was slow, so we introduced:</p><p>snapshot_url_lookup Table</p><ul><li>Stores only unique URLs.</li><li>Has a UNIQUE(url) constraint.</li><li>Enables O(1) duplicate checks.</li><li>Reduced proxy usage by 30% — no more wasted crawling.</li></ul><p><strong>DDL Example:</strong></p><pre>CREATE TABLE snapshot_url_lookup (<br>    url TEXT PRIMARY KEY,<br>    created_on TIMESTAMP WITH TIME ZONE DEFAULT NOW()<br>);</pre><h3>The Impact</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*I8q3sia_bnnh9dHR1bq8xA.jpeg" /></figure><p><strong>Before &amp; After Query Plan</strong></p><p><strong>Before:</strong></p><p>Seq Scan on snapshot (cost=…)</p><p>Rows Removed by Filter: millions</p><p>Execution Time: 3.2s</p><p><strong>After:</strong></p><p>Index Scan using snapshot_2025_08_08_idx on snapshot_2025_08_08</p><p>Execution Time: 12ms</p><h3>Key Takeaways</h3><ul><li>Partitioning + a separate uniqueness table = faster queries + cheaper infrastructure.</li><li>Stop redundant work — it saves money and improves speed.</li><li>Scaling smart beats scaling big.</li></ul><h3>Lessons learned (so you don’t repeat our mistakes)</h3><ul><li>Parent indexes ≠ partition indexes. You must apply indexes to child partitions or script generation for new partitions.</li><li>CREATE INDEX CONCURRENTLY cannot run inside a transaction block or PL/pgSQL function. Run via shell loop (psql -c) or run non-concurrently during maintenance windows.</li><li>Beware of lock contention &amp; deadlocks. Use CTEs / materialize lists of targets before updates.</li><li>Test on staging with production-scale data; partitioning behavior and planner choices can be surprising when you cross a threshold.</li><li>Monitor autovacuum &amp; bloat after migration and use tools like pg_repack if needed.</li><li>Keep a partition creation/maintenance automation (cron job or tooling like pg_partman) so new partitions and their indexes are created predictably.</li></ul><p>Use appropriate indexes: e.g., (created_on, status) for time-based grouping, and trigram GIN when you need %like% queries.</p><h3>Pitfalls to watch for during migration</h3><ul><li>Dump/restore compatibility: Use matching pg_dump/pg_restore versions (we resolved a header-version issue during our process).</li><li>Session-level changes require privileges: SET session_replication_role requires superuser to temporarily disable triggers for bulk loads.</li><li>Unique constraints on partitioned tables: To enforce uniqueness across partitions you either need the partition key included in the unique key or maintain a separate global lookup table (what we did).</li></ul><h3>Final thoughts</h3><p>This was an engineering effort with cross-cutting benefits: cost down, performance up, operations simpler. Partitioning alone doesn’t always solve everything, but combined with data model normalization (the global URL table) and process changes (stop duplicate crawling), it became a much more cost-effective architecture.</p><p>If you’re tackling a similar problem — happy to share scripts, index lists, or a runbook (send me a DM). I’ll also publish the full migration steps, scripts, and real benchmarks in a follow-up post — follow me for the deep<strong> dive.</strong></p><p><strong>💬 What’s your biggest PostgreSQL performance bottleneck?<br> Let’s discuss in the comments.</strong></p><p><strong>#PostgreSQL #DatabaseOptimization #Partitioning #PerformanceEngineering</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2d9c10d23254" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>