Best practices for Large import in empty database?

[Daniel Begin]

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

[Tom Lane]

Post by Daniel Begin
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

[Daniel Begin]

Oups, right to the point! Thanks...

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

Post by Daniel Begin
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-***@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Andy Colson]

Post by Daniel Begin
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