Re: Bulk Inserts

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>