Difference between Bulk Load (Multiple inserts or single inserts) and COPY

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: mayank(dot)l(dot)patel90(at)gmail(dot)com
Subject: Difference between Bulk Load (Multiple inserts or single inserts) and COPY
Date: 2019-11-19 18:55:23
Message-ID: 157418972366.28426.16885409770050714771@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/sql-copy.html
Description:

Hello,

Myself Mayank. I am a Ph.D. student.

I experimented with Bulk load and COPY.
Loading in COPY was very fast.
However, after COPYing data from a CSV file to PostgreSQL Table. The query
execution took lot of time for 1 of the first 4 queries.
Only this slow query was taking so much time, that even if I had used normal
bulk load, it would have been faster in total.
Then all other Query executions took equal time as it took while querying a
table after the Bulk data load method.

So, I want to know the exact reason what's the issue with COPY.
How exactly they differ? The only thing from the document I could identify
was row security.
But it did not mention anything about indexing. Like, in Bulk load, do
indices(or constraint checks) are created with data loading?
& in COPY it's done after? so when indices are being created that query
slows down??

Please reply soon with more details or send a link where I can read it in
depth.
Thanks.
Mayank.
mayank(dot)l(dot)patel90(at)gmail(dot)com

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Tom Lane 2019-11-19 19:47:16 Re: documentation for WITH RECURSIVE
Previous Message Tom Lane 2019-11-19 18:21:37 Re: Role membership and DROP