{"id":2890,"date":"2025-08-21T05:46:12","date_gmt":"2025-08-21T05:46:12","guid":{"rendered":"https:\/\/bynatree.com\/?p=2890"},"modified":"2025-08-21T05:46:12","modified_gmt":"2025-08-21T05:46:12","slug":"partitioning-a-large-postgresql-table-with-minimal-downtime-using-logical-replication","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2025\/08\/21\/partitioning-a-large-postgresql-table-with-minimal-downtime-using-logical-replication\/","title":{"rendered":"Partitioning a Large PostgreSQL Table with Minimal Downtime Using Logical Replication"},"content":{"rendered":"

Background (partition migration with logical replication)<\/b><\/h2>\n

Large PostgreSQL tables eventually run into performance problems. As rows accumulate, queries slow down, indexes bloat, and retention policies become harder to enforce.<\/span><\/p>\n

In one of our projects, we had a table called <\/span>analytics.user_activity<\/strong> that logged every user action across an application. It had grown to hundreds of millions of rows, and queries filtering by time (<\/strong><\/span>event_time<\/strong>)<\/strong> or organization (<\/strong><\/span>org_id<\/strong>)<\/strong> were becoming painfully slow.<\/span><\/p>\n

Instead of archiving or purging data, we chose to <\/span>partition the table by event time<\/b>. But how could we restructure such a massive table <\/span>without major downtime<\/b>?<\/span><\/p>\n

The solution: <\/span>partition migration with logical replication<\/b>.<\/span><\/p>\n

Why Partition?<\/b><\/h2>\n

Partitioning makes sense for append-only or time-series workloads.<\/span><\/p>\n

Benefits:<\/span><\/p>\n

    \n
  • \n

    Faster queries<\/b>: PostgreSQL prunes irrelevant partitions automatically.<\/span>
    \n<\/span><\/p><\/blockquote>\n<\/li>\n

  • \n

    Simplified retention<\/b>: Drop or detach old partitions instantly.<\/span>
    \n<\/span><\/p><\/blockquote>\n<\/li>\n

  • \n

    Efficient indexing<\/b>: Smaller indexes per partition.<\/span>
    \n<\/span><\/p><\/blockquote>\n<\/li>\n

  • \n

    Scalability<\/b>: Maintenance (VACUUM, ANALYZE, index rebuilds) stays lightweight.<\/span><\/p><\/blockquote>\n<\/li>\n<\/ul>\n

    Challenge: Migrating with Minimal Downtime<\/b><\/h2>\n

    The biggest problem when retrofitting partitioning onto a large production table is <\/span>how to move existing data<\/b>.<\/span><\/p>\n

      \n
    • A <\/span>CREATE TABLE \u2026 PARTITION BY \u2026<\/span> can\u2019t just be applied to an existing table.<\/span>
      \n<\/span><\/li>\n
    • Copying all rows (<\/span>INSERT INTO new SELECT * FROM old<\/span>) can take hours and block writes.<\/span>
      \n<\/span><\/li>\n
    • If partitions are created incorrectly, new data can flow into the <\/span>default partition<\/b>, breaking retention and performance.<\/span>\n

      <\/span><\/li>\n<\/ul>\n

      That\u2019s where <\/span>logical replication<\/b> comes in. It allows you to replicate data from the old table into the new partitioned one, while keeping them in sync until cutover.<\/span><\/p>\n

      Migration Strategy<\/b><\/h2>\n
        \n
      1. Keep the old monolithic table running<\/b> while building a new partitioned version.<\/span>
        \n<\/span><\/li>\n
      2. Set up logical replication<\/b> to stream changes from old to new.<\/span>
        \n<\/span><\/li>\n
      3. Backfill existing data<\/b> into the partitioned structure.<\/span>
        \n<\/span><\/li>\n
      4. Cut over applications<\/b> to use the new table with minimal downtime.<\/span>\n

        <\/span><\/li>\n<\/ol>\n

        Step-by-Step Migration<\/b><\/h2>\n

        1. Install <\/b>pg_partman<\/b><\/h3>\n

        We use <\/span>pg_partman<\/span> to automate partition creation and retention policies.<\/span><\/p>\n

        CREATE SCHEMA partman;\nCREATE EXTENSION pg_partman SCHEMA partman;<\/pre>\n
        2. Create Partitioned Table<\/b><\/h3>\n
        CREATE TABLE analytics.user_activity (\n\u00a0\u00a0\u00a0\u00a0activity_id bigserial PRIMARY KEY,\n\u00a0\u00a0\u00a0\u00a0user_id bigint NOT NULL,\n\u00a0\u00a0\u00a0\u00a0org_id bigint NOT NULL,\n\u00a0\u00a0\u00a0\u00a0action text NOT NULL,\n\u00a0\u00a0\u00a0\u00a0event_time timestamptz NOT NULL,\n\u00a0\u00a0\u00a0\u00a0source text,\n\u00a0\u00a0\u00a0\u00a0payload jsonb,\n\u00a0\u00a0\u00a0\u00a0inserted_at timestamptz DEFAULT now()\n) PARTITION BY RANGE (event_time);<\/pre>\n
        3. Configure <\/b>pg_partman<\/b><\/h3>\n
        Find the earliest timestamp in the old table:<\/span><\/p>\n
        SELECT MIN(event_time) FROM old_user_activity;<\/pre>\n
        Start partitions at that timestamp:<\/span><\/p>\n
        SELECT partman.create_parent(\n\u00a0\u00a0\u00a0\u00a0p_parent_table\u00a0 \u00a0 := 'analytics.user_activity',\n\u00a0\u00a0\u00a0\u00a0p_control \u00a0 \u00a0 \u00a0 \u00a0 := 'event_time',\n\u00a0\u00a0\u00a0\u00a0p_type\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 := 'range',\n\u00a0\u00a0\u00a0\u00a0p_interval\u00a0 \u00a0 \u00a0 \u00a0 := '1 day',\n\u00a0\u00a0\u00a0\u00a0p_premake \u00a0 \u00a0 \u00a0 \u00a0 := 14,\n\u00a0\u00a0\u00a0\u00a0p_start_partition := '2025-06-01'\n);<\/pre>\n
        Pre-generate historical partitions:<\/span><\/p>\n
        SELECT partman.create_partition_time(\n\u00a0\u00a0\u00a0\u00a0'analytics.user_activity',\n\u00a0\u00a0\u00a0\u00a0'2025-06-01',\n\u00a0\u00a0\u00a0\u00a0(now()::date + interval '30 days')::date::text\n);<\/pre>\n
        4. Set Up Logical Replication<\/b><\/h3>\n
        On <\/span>source (old table DB)<\/b>:<\/span><\/p>\n
        CREATE PUBLICATION pub_user_activity FOR TABLE old_user_activity;<\/pre>\n
        On <\/span>target (new partitioned DB)<\/b>:<\/span><\/p>\n
        CREATE SUBSCRIPTION sub_user_activity\nCONNECTION 'host=source-db port=5432 dbname=mydb user=replicator password=xxxx'\nPUBLICATION pub_user_activity;<\/pre>\n
        Now inserts\/updates\/deletes from <\/span>old_user_activity<\/span> will stream into <\/span>analytics.user_activity<\/span>.<\/span><\/p>\n
        5. Recreate Indexes<\/b><\/h3>\n
        Indexes must be applied at the parent level, so <\/span>pg_partman<\/span> propagates them to new partitions:<\/span><\/p>\n
        CREATE INDEX idx_activity_org_time ON analytics.user_activity (org_id, event_time);\nCREATE INDEX idx_activity_user_action ON analytics.user_activity (user_id, action);\nCREATE INDEX idx_activity_payload_attr ON analytics.user_activity ((payload->>'session_id'));<\/pre>\n
        Apply them to historical partitions too:<\/span><\/p>\n
        SELECT partman.apply_indexes('analytics.user_activity');<\/pre>\n
        6. Validate<\/b><\/h3>\n
          \n
        • Compare row counts:<\/span>
          \n<\/span><\/li>\n<\/ul>\n
          SELECT (SELECT count(*) FROM old_user_activity) AS old_count, (SELECT count(*) FROM analytics.user_activity) AS new_count;<\/pre>\n
            \n
          • Check partition distribution:<\/span>
            \n<\/span><\/li>\n<\/ul>\n
            \\dt+ analytics.user_activity*<\/pre>\n
              \n
            • Test future inserts:<\/span>
              \n<\/span><\/li>\n<\/ul>\n
              INSERT INTO analytics.user_activity (user_id, org_id, action, event_time, source)\nVALUES (12345, 77, 'login', '2025-08-21 00:05:00+00', 'web');<\/pre>\n
              SELECT * FROM analytics.user_activity_p20250821;<\/pre>\n
              Retention with Detached Partitions<\/b><\/h2>\n
              Instead of dropping, we configured Partman to <\/span>detach<\/b> partitions older than 90 days. This keeps them queryable for audits but excludes them from normal queries:<\/span><\/p>\n
              UPDATE partman.part_config\n\u00a0\u00a0\u00a0SET retention = '90 days',\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0retention_keep_table = true\n\u00a0WHERE parent_table = 'analytics.user_activity';<\/pre>\n
              SELECT partman.run_maintenance('analytics.user_activity');<\/pre>\n
              Lessons Learned<\/b><\/h2>\n
                \n
              • \n
                Always check the <\/span>earliest event_time<\/b> before creating partitions.<\/span>
                \n<\/span><\/p><\/blockquote>\n<\/li>\n
              • \n
                Use logical replication to avoid downtime during migration.<\/span>
                \n<\/span><\/p><\/blockquote>\n<\/li>\n
              • \n
                Create indexes on the parent table \u2014 let <\/span>pg_partman<\/span> propagate them.<\/span>
                \n<\/span><\/p><\/blockquote>\n<\/li>\n
              • \n
                Detached partitions provide flexible retention without data loss.<\/span><\/p><\/blockquote>\n
                <\/p>\n
                <\/span><\/li>\n<\/ul>\n
                Conclusion<\/b><\/h2>\n
                By combining <\/span>logical replication<\/b> with <\/span>pg_partman<\/b>, we migrated <\/span>analytics.user_activity<\/strong> \u2014 a massive time-series style table \u2014 into a partitioned structure with <\/span>near-zero downtime<\/b>.<\/span><\/p>\n
                The result: queries sped up, retention became trivial, and operational overhead dropped significantly.<\/span><\/p>\n
                Partitioning isn\u2019t just for greenfield projects \u2014 with the right approach, you can retrofit it onto existing production workloads safely.<\/span><\/p><\/blockquote>\n
                 <\/p>\n","protected":false},"excerpt":{"rendered":"
                Background (partition migration with logical replication) Large PostgreSQL tables eventually run into performance problems. As rows accumulate, queries slow down, indexes bloat, and retention policies become harder to enforce. In one of our projects, we had a table called analytics.user_activity that logged every user action across an application. It had grown to hundreds of millions…<\/p>\n","protected":false},"author":1,"featured_media":2891,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[59,86,122,165,195,198,291],"class_list":["post-2890","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-techblog","tag-bynatreeinsights","tag-databasearchitecture","tag-infraengineering","tag-logicalreplication","tag-minimaldowntime","tag-postgresqlpartitioning","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2890","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2890"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2890\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2891"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2890"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2890"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2890"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}