Re: optimizing import of large CSV file into partitioned table? - Mailing list pgsql-general

hi,

i would recommend to convert the input using some scripts into pg_dump format and use small temporary tables
without indexes to import into - and after all data in the db you could partition it...

you can pre-partition your data using simple grep, this way you can import the data directly into a partitioned scheme

kirk

Thom Brown wrote:
> On 28 March 2010 18:33, Rick Casey <caseyrick@gmail.com
> <mailto:caseyrick@gmail.com>> wrote:
>
>     After careful research, I would to post the following problem I'm
>     having with the importing of a large (16Gb) CSV file. Here is brief
>     synopsis:
>     - this is running on Postgres (PG) version: PostgreSQL 8.3.9 on
>     i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu
>     4.3.2-1ubuntu11) 4.3.2
>     - it is running on a Ubuntu (small) server instance at Amazon Web
>     Services (AWS), with a 320Gb volume mounted for the PG data directory
>     - the database was created using the partition example in the
>     documentation, with an insert trigger and a function to direct which
>     table where records get inserted.
>     (see below for code on my table and trigger creation)
>
>     After some days of attempting to import the full 16Gb CSV file, I
>     decided to split the thing up, using the split utility in Linux.
>     This seemed to improve things; once I had split the CSV files into
>     about 10Mb size files, I finally got my first successful import of
>     about 257,000 recs. However, this is going to be a rather labor
>     intensive process to import the full 16Gb file, if I have to
>     manually split it up, and import each smaller file separately.
>
>     So, I am wondering if there is any to optimize this process? I have
>     been using Postgres for several years, but have never had to
>     partition or optimize it for files of this size until now.
>     Any comments or suggestions would be most welcomed from this
>     excellent forum.
>
>     (I might add that I spend several weeks prior to this trying to get
>     this to work in MySQL, which I finally had to abandon.)
>
>     Sincerely,
>     Rick
>
>     Details of the code follow:
>
>     Here is the basic COPY command, which I run as the postgres user, to
>     import the CSV files:
>     <begin>
>     COPY allcalls FROM '/data/allcalls-csv/sub3ab' WITH CSV;
>
>     Here is what some sample data look like in the files:
>     3153371867,2008-02-04 16:11:00,1009,1,40
>     2125673062,2008-02-04 16:11:00,1009,1,41
>     5183562377,2008-02-04 16:11:00,1009,1,50
>     ...
>
>     Here are the basic scripts that created the partition table and
>     insert trigger:
>     CREATE TABLE allcalls (
>     phonenum bigint,
>     callstarted timestamp without time zone,
>     status int,
>     attempts int,
>     duration int
>     );
>     CREATE TABLE allcalls_0 (
>         CHECK ( phonenum < 1000000000 )
>     ) INHERITS (allcalls);
>     ...(repeat this 9 more times, for 10 subpartition tables)
>
>     CREATE INDEX allcalls_0_phonenum ON allcalls_0 (phonenum);
>     ..(repeat this 9 more times, for indexes on the 10 subpartition tables)
>     CREATE OR REPLACE FUNCTION allcalls_insert_trigger()
>     RETURNS TRIGGER AS $$
>     BEGIN
>     IF ( NEW.phonenum  < 1000000000 ) THEN
>             INSERT INTO allcalls_0 VALUES (NEW.*);
>         ELSIF ( NEW.phonenum >= 1000000000 AND NEW.phonenum < 2000000000
>     ) THEN
>             INSERT INTO allcalls_1 VALUES (NEW.*);
>     ...(again, repeat for rest of the parition tables)
>
>     CREATE TRIGGER insert_phonenum_trigger
>         BEFORE INSERT ON allcalls
>         FOR EACH ROW EXECUTE PROCEDURE allcalls_insert_trigger();
>
>     <end>
>
>
> The problem here is that you appear to require an index update, trigger
> firing and constraint check for every single row.  First thing I'd
> suggest is remove the indexes.  Apply that after your import, otherwise
> it'll have to update the index for every single entry.  And the trigger
> won't help either.  Import into a single table and split it out into
> further tables after if required.  And finally the constraint should
> probably be applied after too, so cull any violating rows after importing.
>
> Thom


--
Nagy Zoltan (kirk) <kirk@bteam.hu>