Message-ID: <53FF4164.9070102@encs.concordia.ca>
Date: Thu, 28 Aug 2014 10:49:08 -0400
From: Emi Lu <emilu@encs.concordia.ca>
Reply-To: emilu@encs.concordia.ca
User-Agent: Mozilla/5.0 (X11; Linux i686 on x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.7.0
MIME-Version: 1.0
To: Kevin Grittner <kgrittn@ymail.com>,
 Alex Goncharov <alex.goncharov.usa@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: autocommit (true/false) for more than 1 million records
References: <53F7ACF4.1010604@encs.concordia.ca>
	<CAMkU=1xWQdCs0nbZSXrkdK4Y7moLKWV8X60MSdDSw-oR27L7yw@mail.gmail.com>
	<CAOnt2=NUbNPNvy3x-TcwCm-vLcNT7jZvX=Z5Ja6jAoX22j3RuA@mail.gmail.com>
	<1409092428.41025.YahooMailNeo@web122306.mail.ne1.yahoo.com>
 <CAOnt2=MG4L3Prb=AQ3Etqs3kOZbPDw7Y-ap0MHn75GR0LfV2Fg@mail.gmail.com>
 <1409144369.45962.YahooMailNeo@web122306.mail.ne1.yahoo.com>
In-Reply-To: <1409144369.45962.YahooMailNeo@web122306.mail.ne1.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hello All,

I learned a lot by inputs from all of you. To share one more thing about 
java_JDBC bypassing autocommit that I tried:
(1) Read/save source data into f1.csv, f2.csv, ......
(2) Copy/load into dest psql.DB
     CopyManager  cm           = null;
     FileReader       fileReader = null;
     cm            = new CopyManager((BaseConnection) conn_psql);
     fileReader = new FileReader("f1.csv");
     cm.copyIn("COPY table_name FROM STDIN WITH DELIMITER '|'", 
fileReader);
     fileReader.close();

Emi

On 08/27/2014 08:59 AM, Kevin Grittner wrote:
> Alex Goncharov <alex.goncharov.usa@gmail.com> wrote:
>> Kevin Grittner <kgrittn@ymail.com> wrote:
>>> The rows will all be in the table, but not visible to any other
>>> transaction.
>> How much data can I fit there while doing COPY?  Not 1 TB?
> As has already been said, why not?  This is not some special
> section of the table -- the data is written to the table.  Period.
> Commit or rollback just tells new transactions whether data flagged
> with that transaction number is visible.
>
> Nobody can tell you how much space that will take -- it depends on
> many factors, including how many columns of what kind of data, how
> compressible it is, and how it is indexed.  But the point is, we
> are not talking about any separate space from what is needed to
> store the data in the database.
>
> FWIW, I think the largest single COPY statement I ever ran was
> generated by pg_dump and piped directly to psql for a major release
> upgrade (before pg_upgrade was available), and it was somewhere in
> the 2TB to 3TB range.  It took a long time, but it "just worked".
> That should be true for 10TB or 100TB, as long as you have sized
> the machine correctly and are loading clean data.  Whether you have
> that covered, and how you want to "hedge your bets" based on your
> degree of confidence in those things is a judgment call.  When I'm
> in the position of needing to make such a call, I like to do some
> tests.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance