From: Emi Lu <emilu@encs.concordia.ca>
To: Kevin Grittner <kgrittn@ymail.com>
To: 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
Date: Thu, 28 Aug 2014 10:49:08 -0400
Message-ID: <53FF4164.9070102@encs.concordia.ca> (raw)
In-Reply-To: <1409144369.45962.YahooMailNeo@web122306.mail.ne1.yahoo.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: emilu@encs.concordia.ca, kgrittn@ymail.com, alex.goncharov.usa@gmail.com
Subject: Re: autocommit (true/false) for more than 1 million records
In-Reply-To: <53FF4164.9070102@encs.concordia.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox