Обсуждение: Best practices for Large import in empty database?

Daniel Begin <jfd553@hotmail.com> writes:
> I am about to feed an empty database with a large import of data (the size
> of csv files is about 500 GB). Tables are created but I haven't added any
> constraints or indexes yet. I wonder whether the best practice is to add
> them before or after the import. Are there other good practices that would
> ease the import process?

http://www.postgresql.org/docs/9.3/static/populate.html

            regards, tom lane

On 10/1/2014 9:13 AM, Daniel Begin wrote:
> I am about to feed an empty database with a large import of data (the
> size of csv files is about 500 GB). Tables are created but I haven't
> added any constraints or indexes yet. I wonder whether the best practice
> is to add them before or after the import. Are there other good
> practices that would ease the import process?
>
> Cheers,
>
> Daniel
>

create index after, as long as you dont need it for uniqueness checking.

then:

begin;
truncate table junk;
copy ...  with freeze;
commit;

Even though the table is empty, the begin,trunc,copy lets PG skip a
bunch of extra WAL.

-Andy

Oups, right to the point! Thanks...

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: October-01-14 10:38
To: Daniel Begin
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Best practices for Large import in empty database?

Daniel Begin <jfd553@hotmail.com> writes:
> I am about to feed an empty database with a large import of data (the
> size of csv files is about 500 GB). Tables are created but I haven't
> added any constraints or indexes yet. I wonder whether the best
> practice is to add them before or after the import. Are there other
> good practices that would ease the import process?

http://www.postgresql.org/docs/9.3/static/populate.html

            regards, tom lane


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