Re: Bulk Inserts - Mailing list pgsql-general

On Aug 10, 2019, at 8:47 PM, Souvik Bhattacherjee <kivuosb@gmail.com> wrote:

Hi Adrian,

Thanks for the response.

> Yes, but you will some code via client or function that batches the 
> inserts for you.

Could you please elaborate a bit on how EXP 1 could be performed such that it uses bulk inserts?

Best,

-SB

On Fri, Aug 9, 2019 at 7:26 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 8/9/19 3:06 PM, Souvik Bhattacherjee wrote:
> Hi,
>
> I'm trying to measure the performance of the following: Multiple txns
> inserting tuples into a table concurrently vs single txn doing the whole
> insertion.
>
> *new table created as:*
> create table tab2 (
> id serial,
> attr1 integer not null,
> attr2 integer not null,
> primary key(id)
> );
>
> *EXP 1: inserts with multiple txn:*
> insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where
> attr2 = 10);
> insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where
> attr2 = 20);
>
> note: attr2 has only two values 10 and 20
>
> *EXP 2: inserts with a single txn:*
> insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1);
>
> I also performed another experiment as follows:
> *EXP 3:* select attr1, attr2 into tab2 from tab1;
>
> The observation here is EXP 3  is much faster than EXP 2 probably due to
> bulk inserts used by Postgres. However I could not find a way to insert
> id values in tab2 using EXP 3. Also select .. into .. from .. throws an
> error if we create a table first and then populate the tuples using the
> command.

Yes as SELECT INTO is functionally the same as CREATE TABLE AS:

https://www.postgresql.org/docs/11/sql-selectinto.html

>
> I have the following questions:
> 1. Is it possible to have an id column in tab2 and perform a bulk insert
> using select .. into .. from .. or using some other means?

Not using SELECT INTO for reasons given above.
Though it is possible to SELECT INTO as you show in EXP 3 and then:
        alter table tab2 add column id serial primary key;
EXP 2 shows the other means.

> 2. If a table is already created, is it possible to do bulk inserts via
> multiple txns inserting into the same table (EXP 3)?

Yes, but you will some code via client or function that batches the
inserts for you.

>
> Best,
> -SB


--
Adrian Klaver
adrian.klaver@aklaver.com

Top-posting (i.e. putting your reply at the top is discouraged here)

Does this appeal to you:

COPY (SELECT * FROM relation) TO ... (https://www.postgresql.org/docs/10/sql-copy.html)