Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 11F0E1337D35 for ; Wed, 27 Apr 2011 21:51:38 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 84682-01-2 for ; Thu, 28 Apr 2011 00:51:18 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f46.google.com (mail-ew0-f46.google.com [209.85.215.46]) by mail.postgresql.org (Postfix) with ESMTP id 236C51337F2D for ; Wed, 27 Apr 2011 21:26:15 -0300 (ADT) Received: by ewy4 with SMTP id 4so674178ewy.19 for ; Wed, 27 Apr 2011 17:26:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=QFWx3HxvExNRNuUnJRYFa11AZdQUdwn1g7ChkpNa418=; b=H7BhppZR/jZCQ8W2zuz5x6dgKl3Mh7gKaQBxuy1NLilTO54yBBSouwaWxZifW3C3Zd XzbowSHQYi9cTkO2yvKQVPgf8tANRwrt/fimBzMXX7aVyQ853oYI1fPMLT1ik4UqrEXZ rDXLnQYTwA/3p3XKVyTpjqN9tqgE6fv+hEJ5s= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=IzUDMZe08LGaXpPN9tmBgErQsoOUM6LDQCMWAn3g6t6maPc9v9N5yhZ/e9RprlNgcZ zIag3mE13TjYOUwlIPZulF6e23C6cL9fGsUldyJf4no9XruA+FeGyMfIWISOv3e1fe1C TMs8vCebscm9M2m/VTtpIRiu/ri7zfOpkodIo= MIME-Version: 1.0 Received: by 10.213.23.92 with SMTP id q28mr1346402ebb.80.1303950375543; Wed, 27 Apr 2011 17:26:15 -0700 (PDT) Received: by 10.213.9.81 with HTTP; Wed, 27 Apr 2011 17:26:14 -0700 (PDT) In-Reply-To: References: Date: Wed, 27 Apr 2011 18:26:14 -0600 Message-ID: Subject: Re: Re: best practice for moving millions of rows to child table when setting up partitioning? From: Scott Marlowe To: Mark Stosberg Cc: pgsql-admin@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.583 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RFC_ABUSE_POST=0.001, SARE_MILLIONSOF=0.315 X-Spam-Level: X-Archive-Number: 201104/248 X-Sequence-Number: 37429 I had a similar problem about a year ago, The parent table had about 1.5B rows each with a unique ID from a bigserial. My approach was to create all the child tables needed for the past and the next month or so. Then, I simple did something like: begin; insert into table select * from only table where id between 1 and 10000000; delete from only table where id between 1 and 10000000; -- first few times check to make sure it's working of course commit; begin; insert into table select * from only table where id between 10000001 and 20000000; delete from only table where id between 10000001 and 20000000; commit; and so on. New entries were already going into the child tables as they showed up, old entries were migrating 10M rows at a time. This kept the moves small enough so as not to run the machine out of any resource involved in moving 1.5B rows at once.