From: 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: Wed, 27 Aug 2014 05:59:29 -0700
Message-ID: <1409144369.45962.YahooMailNeo@web122306.mail.ne1.yahoo.com> (raw)
In-Reply-To: <CAOnt2=MG4L3Prb=AQ3Etqs3kOZbPDw7Y-ap0MHn75GR0LfV2Fg@mail.gmail.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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: kgrittn@ymail.com, alex.goncharov.usa@gmail.com
Subject: Re: autocommit (true/false) for more than 1 million records
In-Reply-To: <1409144369.45962.YahooMailNeo@web122306.mail.ne1.yahoo.com>
* 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