Feedback-ID: i76984098:Fastmail
Message-ID: <1a34eef6-9b30-46fa-bcc7-d7dc1c0602bb@aklaver.com>
Date: Sun, 8 Sep 2024 11:26:42 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Faster data load
To: pgsql-general@lists.postgresql.org
References: <CAKna9VaVsDzfOfOGu1+grStp9BBHFMKrH5DCEbbtGcQUWJ74KQ@mail.gmail.com>
 <20240908174539.teeanjwrthkjm4ti@hjp.at>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <20240908174539.teeanjwrthkjm4ti@hjp.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/1a34eef6-9b30-46fa-bcc7-d7dc1c0602bb%40aklaver.com>
Precedence: bulk

On 9/8/24 10:45, Peter J. Holzer wrote:
> On 2024-09-06 01:44:00 +0530, Lok P wrote:
>> We are having a requirement to create approx 50 billion rows in a partition
>> table(~1 billion rows per partition, 200+gb size daily partitions) for a
>> performance test. We are currently using ' insert into <target table_partition>
>> select.. From <source_table_partition> or <some transformed query>;' method .
>> We have dropped all indexes and constraints First and then doing the load.
>> Still it's taking 2-3 hours to populate one partition.
> 
> That seems quite slow. Is the table very wide or does it have a large
> number of indexes?
> 
>> Is there a faster way to achieve this?
>>
>> Few teammate suggesting to use copy command and use file load instead, which
>> will be faster.
> 
> I doubt that.
> 
> I benchmarked several strategies for populating tables 5 years ago and
> (for my test data and on our hardware at the time - YMMV) s simple
> INSERT ... SELECT was more than twice as fast as 8 parallel COPY
> operations (and about 8 times as fast as a single COPY). >
> Details will have changed since then (I should rerun that benchmark on
> a current system), but I'd be surprised if COPY became that much faster
> relative to INSERT ... SELECT.

Yeah they seem to have changed a great deal. Though you are correct in 
saying COPY is not faster then INSERT..SELECT


select version();
                                                               version 

-----------------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 16.4 (Ubuntu 16.4-1.pgdg22.04+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit


select count(*) from nyc_taxi_duckdb ;
   count
---------
  2846722

ll -h nyc_taxi.csv
-rw-rw-r-- 1 aklaver aklaver 252M Sep  8 10:54 nyc_taxi.csv

insert into nyc_duplicate select * from nyc_taxi_duckdb ;
INSERT 0 2846722
Time: 7015.072 ms (00:07.015)

truncate  nyc_duplicate ;

\copy nyc_duplicate from 'nyc_taxi.csv' with csv header
COPY 2846722
Time: 8760.197 ms (00:08.760)

copy nyc_duplicate from '/tmp/nyc_taxi.csv' with csv header;
COPY 2846722
Time: 7904.279 ms (00:07.904)

Just to see what the coming attraction offers:

select version();
                                                                version 

--------------------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 17rc1 (Ubuntu 17~rc1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit


insert into nyc_duplicate select * from nyc_taxi_duckdb ;
INSERT 0 2846722
Time: 5315.878 ms (00:05.316)

\copy nyc_duplicate from 'nyc_taxi.csv' with csv header
COPY 2846722
Time: 10042.129 ms (00:10.042)

copy nyc_duplicate from '/tmp/nyc_taxi.csv' with csv header;
COPY 2846722
Time: 8422.503 ms (00:08.423)

> 
>          hp
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com