Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VxkOk-0000wO-2J for pgsql-performance@arkaria.postgresql.org; Mon, 30 Dec 2013 21:29:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VxkOj-0007Cw-DS for pgsql-performance@arkaria.postgresql.org; Mon, 30 Dec 2013 21:29:17 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VxkOi-0007Cq-Fh for pgsql-performance@postgresql.org; Mon, 30 Dec 2013 21:29:16 +0000 Received: from smtprelay0211.b.hostedemail.com ([64.98.42.211] helo=smtprelay.b.hostedemail.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VxkOf-0005eb-1D for pgsql-performance@postgresql.org; Mon, 30 Dec 2013 21:29:15 +0000 Received: from filter.hostedemail.com (b-bigip1 [10.5.19.254]) by smtprelay01.b.hostedemail.com (Postfix) with ESMTP id 21AFE2D2A1E; Mon, 30 Dec 2013 21:29:12 +0000 (UTC) X-Session-Marker: 616C76686572726540616C76682E6E6F2D69702E6F7267 X-Spam-Summary: 10, 1, 0, , d41d8cd98f00b204, alvherre@alvh.no-ip.org, :::::::::, RULES_HIT:41:355:379:599:800:960:967:973:988:989:1260:1277:1311:1312:1313:1314:1345:1359:1431:1437:1515:1516:1518:1519:1534:1540:1593:1594:1595:1596:1711:1730:1747:1777:1792:2197:2199:2393:2525:2553:2560:2563:2682:2685:2828:2859:2907:2933:2937:2939:2942:2945:2947:2951:2954:3022:3138:3139:3140:3141:3142:3352:3865:3866:3867:3870:3871:3872:3873:3874:3934:3936:3938:3941:3944:3947:3950:3953:3956:3959:4250:4605:4659:5007:6117:6119:6261:7903:9025:9121:10007:10400:10848:11232:11256:11257:11658:11914:12295:12517:12519:13069:13311:13357:13894:13895, 0, RBL:none, CacheIP:none, Bayesian:0.5, 0.5, 0.5, Netcheck:none, DomainCache:0, MSF:not bulk, SPF:fn, MSBL:0, DNSBL:none, Custom_rules:0:1:0 X-HE-Tag: alarm45_8a6d5892dd70e X-Filterd-Recvd-Size: 2351 Received: from eldon.alvh.no-ip.org (pc-211-245-160-190.cm.vtr.net [190.160.245.211]) (Authenticated sender: alvherre@alvh.no-ip.org) by omf10.b.hostedemail.com (Postfix) with ESMTPA; Mon, 30 Dec 2013 21:29:11 +0000 (UTC) Received: by eldon.alvh.no-ip.org (Postfix, from userid 1000) id A2A7D1FC9; Mon, 30 Dec 2013 18:29:07 -0300 (CLST) Date: Mon, 30 Dec 2013 18:29:07 -0300 From: Alvaro Herrera To: Sergey Konoplev Cc: Dave Johansen , Scott Marlowe , Jeff Janes , pgsql-performance Subject: Re: Recommendations for partitioning? Message-ID: <20131230212907.GT22570@eldon.alvh.no-ip.org> References: <20131220155954.GB22570@eldon.alvh.no-ip.org> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.21 (2010-09-15) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Sergey Konoplev escribió: > On Fri, Dec 20, 2013 at 7:59 AM, Alvaro Herrera > wrote: > > Eh. Why can't you just do something like > > > > WITH moved AS ( > > DELETE FROM src WHERE .. > > RETURNING * > > ) INSERT INTO dst SELECT * FROM moved; > > Avero, I think it could be cheaper to do this like it is shown below, correct? > > psql dbname -c 'copy src to stdout' | \ > psql dbname -c 'copy dst from stdin; truncate src;' Yes, if you can get rid of the old records by removing or emptying a partition (or de-inheriting it, as suggested elsewhere in the thread), that's better than DELETE because that way you don't create dead rows to vacuum later. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance