Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eflhW-0003eg-7Z for pgsql-performance@arkaria.postgresql.org; Sun, 28 Jan 2018 12:04:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eflhU-00060j-P7 for pgsql-performance@arkaria.postgresql.org; Sun, 28 Jan 2018 12:04:44 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eflhU-00060Y-9t for pgsql-performance@lists.postgresql.org; Sun, 28 Jan 2018 12:04:44 +0000 Received: from mail.itasnett.no ([128.39.191.25] helo=symbm03.itasdata.no) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1eflhQ-0007rx-5n for pgsql-performance@postgresql.org; Sun, 28 Jan 2018 12:04:42 +0000 X-AuditID: 8027bf19-7afff70000000a0f-9f-5a6dbc542bdc Received: from mail.nibio.no (pf-18461.bioforsk.no [128.39.184.61]) by symbm03.itasdata.no (symbm03.itasdata.no) with SMTP id CE.B9.02575.45CBD6A5; Sun, 28 Jan 2018 13:04:36 +0100 (CET) Received: from NIBIOEXCH01.int.nibio.no (10.100.32.20) by NIBIOEXCH01.int.nibio.no (10.100.32.20) with Microsoft SMTP Server (TLS) id 15.1.225.42; Sun, 28 Jan 2018 13:04:35 +0100 Received: from NIBIOEXCH01.int.nibio.no ([10.100.32.20]) by NIBIOEXCH01.int.nibio.no ([10.100.32.20]) with mapi id 15.01.0225.041; Sun, 28 Jan 2018 13:04:35 +0100 From: Lars Aksel Opsahl To: Mariel Cherkassky , PostgreSQL mailing lists Subject: SV: copy csv into partitioned table with unique index Thread-Topic: copy csv into partitioned table with unique index Thread-Index: AQHTmBgVdxJRSfTyLUmkPaCstPI4rKOJLVYqgAACVTk= Date: Sun, 28 Jan 2018 12:04:35 +0000 Message-ID: References: , In-Reply-To: Accept-Language: nb-NO, en-US Content-Language: nb-NO X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-messagesentrepresentingtype: 1 x-originating-ip: [10.100.44.6] Content-Type: multipart/alternative; boundary="_000_af5249b8499940ea801e54bdb0c85713nibiono_" MIME-Version: 1.0 X-Brightmail-Tracker: H4sIAAAAAAAAA+NgFprHIsWRmVeSWpSXmKPExsXSoL7DVjdkT26UwYWZ3Bad99azWFyfeY7Z gclj56y77B4X9k1jD2CK4rJJSc3JLEst0rdL4MqYv/A0c8H5oIrN3SdYGxjbXLsYOTkkBEwk 9h84z9jFyMUhJLCbUeL+4nfMEM42Romz63ayQDjLGCWu3l7ECNLCJqAn8X5fEzuILSJQJNGy 6whrFyMHh7CAncTlpiyIsL3Ev0VLGCFsK4muqbeYQWwWAVWJTzM+MYHYvALmEve2zwGzhQRq JE6taGcFsTkFLCS2L7vODDKSUUBWYm4TL0iYWUBcYsbROywQRwtILNlznhnCFpV4+fgfK4St I3H2+hNGCFteYvvfzUwQvfESyxd8YIFYKyhxcuYTlgmMorOQjJ2FpGwWkjKIuJ7EjalT2CBs bYllC18zQ9i6EjP+HWJBFl/AyL6KUbi4Mjcp18BYL7MksTglsSRRLy9/EyM41vZL7mD8dt3l EKMAB6MSD++NkzlRQqyJZUANhxglOJiVRHiPTc+NEuJNSaysSi3Kjy8qzUktPsQozcGiJM4r FvkjUkggPbEkNTs1tSC1CCbLxMEp1cC4T3jp/MOW/2e+F7m8gLNYpCSswdlfvrmpRuvN4k3c 76fdvcX+8penSF6x/3PF2uNHuDYHenz6du3N/Mw9jzQtvl93rzFOuViwek5QtN3aG7a8AYKv l/fu32XPsKFTtn9Sc8TMX4bLqqe8btG8KJrwNFu6PlGc9fnH+P2bpG/u2L9IJ+KZz7frSizF GYmGWsxFxYkAMxoHRrECAAA= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_af5249b8499940ea801e54bdb0c85713nibiono_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hei Sorry it's was a zero to much, it should 30-40 million weather observations= pr second. Lars ________________________________ Fra: Lars Aksel Opsahl Sendt: 28. januar 2018 12:57 Til: Mariel Cherkassky; PostgreSQL mailing lists Emne: SV: copy csv into partitioned table with unique index Hi We had the same problems with performance when testing with more than 100 b= illion weather observations. We now have a solution where we can push betwe= en 300 and 400 million weather observations pr. Second into the database. We download date from NetCDF files. The date are joined based on time and g= eolocation, so data from many different NetCDF should end up in the same ro= w and table. Doing this based on database join and table update was taking a long time a= s you also have noticed. To get high performance we ended up this solution - Uses os commands like awk,sed,join,cut,.. to prepare CSV file for the dat= abase. - Use multithreads. - Insert data directly into child tables. - No triggers, constraints and indexes on working table. - Don't update rows. - Unlogged tables. We first download NetCDF and make CSV files that fits in perfect for the co= py a command and with complete files for each child tables it's created for= , this is a time consuming operation. So before the copy in into database we just do a truncate on the selected t= able. We are then able to insert between 300 and 400 mill. weather observat= ions pr. Second. We have 11 observations pr row so it means around 35 mill = rows pr second. We have one child table for each year and month. The database we working on have 16 dual core CPU's and SSD discs. When test= ing I was running 11 threads in parallel. Indexes and constraints are added later based on needs. How can you take on chance om using something like unlogged tables? Linux system are quite stable and if we keep the a copy of the CVS files it= does not take long time to insert data after crash. You can also change your table to logged later if you need to secure your d= ata in the database. Lars ________________________________ Fra: Mariel Cherkassky Sendt: 28. januar 2018 10:11 Til: PostgreSQL mailing lists Emne: copy csv into partitioned table with unique index Hi, I configured a master table that is called "year_2018" : create table year_2018(a int,b int c date); The master table has a unique constraint on those 3 columns so that I wont = have any duplicated rows. Moreover, I configured a before insert trigger on= that table that creates a child table for each day in the year. The child = should include all the data related to that specific day. Now, every day I got a csv file that I need to load to the year table. I mu= st load the data as fast as possible but I have 2 problems : 1)I must load the data as a bulk - via the copy command. However, the copy = command fails because sometimes I have duplicated rows. 2)I tried to use the pgloader extension but it fails because I have a trigg= er before each insert. -I cant load all the data into a temp table and then run insert into year_2= 018 select * from temp because it takes too much time. Any idea ? --_000_af5249b8499940ea801e54bdb0c85713nibiono_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hei


Sorry it's was a zero to much, it should 30-40 million weather observatio= ns pr second.


Lars



Fra: Lars Aksel Opsahl <= Lars.Opsahl@nibio.no>
Sendt: 28. januar 2018 12:57
Til: Mariel Cherkassky; PostgreSQL mailing lists
Emne: SV: copy csv into partitioned table with unique index
 

Hi


We had the same problems w= ith performance when testing with more than 100 billion weather observation= s. We now have a solution where we can push between 300 and 400 million wea= ther observations pr. Second into the database.


We download date from NetC= DF files. The date are joined based on time and geolocation, so data from m= any different NetCDF should end up in the same row and table.


Doing this based on databa= se join and table update was taking a long time as you also have noticed.


To get high performance we= ended up this solution

- Uses os commands like aw= k,sed,join,cut,.. to prepare CSV file for the database.

- Use multithreads.

- Insert data directly int= o child tables.

- No triggers, constraints= and indexes on working table.

- Don’t update rows.=

- Unlogged tables.


We first download NetCDF a= nd make CSV files that fits in perfect for the copy a command and with comp= lete files for each child tables it’s created for, this is a time con= suming operation.


So before the copy in into= database we just do a truncate on the selected table. We are then able to = insert between 300 and 400 mill. weather observations pr. Second. We have 1= 1 observations pr row so it means around 35 mill rows pr second. We have one child table for each year and m= onth.


The database we working on= have 16 dual core CPU’s and SSD discs. When testing I was running 11= threads in parallel.


Indexes and constraints ar= e added later based on needs.


How can you take on chance= om using something like unlogged tables?


Linux system are quite sta= ble and if we keep the a copy of the CVS files it does not take long time t= o insert data after crash.


You can also change your t= able to logged later if you need to secure your data in the database.


Lars




Fra: Mariel Cherkassky <= mariel.cherkassky@gmail.com>
Sendt: 28. januar 2018 10:11
Til: PostgreSQL mailing lists
Emne: copy csv into partitioned table with unique index
 
Hi,
I configured a master table that is called "year_2018= " : 
create table year_2018(a int,b int c date); 

The master table has a unique constraint on those 3 column= s so that I wont have any duplicated rows. Moreover, I configured a before = insert trigger on that table that creates a child table for each day in the= year. The child should include all the data related to that specific day.

Now, every day I got a csv file that I need to load to the= year table. I must load the data as fast as possible but I have 2 problems= :
1)I must load the data as a bulk - via the copy command. H= owever, the copy command fails because sometimes I have duplicated rows.
2)I tried to use the pgloader extension but it fails becau= se I have a trigger before each insert.

-I cant load all the data into a temp table and then run i= nsert into year_2018 select * from temp because it takes too much time.

Any idea ?


--_000_af5249b8499940ea801e54bdb0c85713nibiono_--