Re: Insert with Jsonb column hangs
> On Mar 9, 2024, at 9:01 AM, kuldeep singh wrote: > > > Copy may not work in our scenario since we need to join data from multiple > tables & then convert it to json using row_to_json . This json data > eventually needs to be stored in a target table . >> Wait. You're getting the data from the database, converting that into json and writing that back to the database? Can you not feed the json consumer from those sources?
Re: Insert with Jsonb column hangs
Thanks Adrian & Hector .
I will try the copy approach & post the result here.
On Sat, Mar 9, 2024 at 9:57 PM hector vass wrote:
>
>
> On Sat, Mar 9, 2024 at 4:10 PM Adrian Klaver
> wrote:
>
>> On 3/9/24 08:00, kuldeep singh wrote:
>> > Copy may not work in our scenario since we need to join data from
>> > multiple tables & then convert it to json using row_to_json . This
>> > json data eventually needs to be stored in a target table .
>>
>> Per:
>>
>> https://www.postgresql.org/docs/current/sql-copy.html
>>
>> "
>> COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
>>
>> <...>
>>
>> query
>>
>> A SELECT, VALUES, INSERT, UPDATE, or DELETE command whose results
>> are to be copied. Note that parentheses are required around the query.
>>
>> For INSERT, UPDATE and DELETE queries a RETURNING clause must be
>> provided, and the target relation must not have a conditional rule, nor
>> an ALSO rule, nor an INSTEAD rule that expands to multiple statements.
>> "
>>
>> >
>> > Will it be better if we break the process into batches of like 10,000
>> > rows & insert the data in its individual transactions? Or any other
>> > better solution available ?
>> >
>> > On Sat, Mar 9, 2024 at 9:01 PM hector vass > > > wrote:
>> >
>> >
>> >
>> > On Sat, Mar 9, 2024 at 3:02 PM kuldeep singh
>> > mailto:kuldeeparor...@gmail.com>> wrote:
>> >
>> > Hi,
>> >
>> > We are inserting data close to 1M record & having a single Jsonb
>> > column but query is getting stuck.
>> >
>> > We are using insert into select * .. , so all the operations are
>> > within the DB.
>> >
>> > If we are running select query individually then it is returning
>> > the data in 40 sec for all rows but with insert it is getting
>> stuck.
>> >
>> > PG Version - 15.
>> >
>> > What could be the problem here ?
>> >
>> > Regards,
>> > KD
>> >
>> >
>> > insert 1M rows especially JSON that can be large, variable in size
>> > and stored as blobs and indexed is not perhaps the correct way to do
>> > this
>> > insert performance will also depend on your tuning. Supporting
>> > transactions, users or bulk processing are 3x sides of a compromise.
>> > you should perhaps consider that insert is for inserting a few rows
>> > into live tables ... you might be better using copy or \copy,
>> > pg_dump if you are just trying to replicate a large table
>> >
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>
>
> What Adrian Klaver said ^
> discovered even this works...
>
>
> create view myview as (select row_to_json from mytable);
>
> create table newtable as select * from myview where 1=0;
>
> copy myview to program 'psql mydb postgres -c ''copy newtable from stdin''
> ';
>
>
>
>
>
Re: Insert with Jsonb column hangs
On Sat, Mar 9, 2024 at 4:10 PM Adrian Klaver
wrote:
> On 3/9/24 08:00, kuldeep singh wrote:
> > Copy may not work in our scenario since we need to join data from
> > multiple tables & then convert it to json using row_to_json . This
> > json data eventually needs to be stored in a target table .
>
> Per:
>
> https://www.postgresql.org/docs/current/sql-copy.html
>
> "
> COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
>
> <...>
>
> query
>
> A SELECT, VALUES, INSERT, UPDATE, or DELETE command whose results
> are to be copied. Note that parentheses are required around the query.
>
> For INSERT, UPDATE and DELETE queries a RETURNING clause must be
> provided, and the target relation must not have a conditional rule, nor
> an ALSO rule, nor an INSTEAD rule that expands to multiple statements.
> "
>
> >
> > Will it be better if we break the process into batches of like 10,000
> > rows & insert the data in its individual transactions? Or any other
> > better solution available ?
> >
> > On Sat, Mar 9, 2024 at 9:01 PM hector vass > > wrote:
> >
> >
> >
> > On Sat, Mar 9, 2024 at 3:02 PM kuldeep singh
> > mailto:kuldeeparor...@gmail.com>> wrote:
> >
> > Hi,
> >
> > We are inserting data close to 1M record & having a single Jsonb
> > column but query is getting stuck.
> >
> > We are using insert into select * .. , so all the operations are
> > within the DB.
> >
> > If we are running select query individually then it is returning
> > the data in 40 sec for all rows but with insert it is getting
> stuck.
> >
> > PG Version - 15.
> >
> > What could be the problem here ?
> >
> > Regards,
> > KD
> >
> >
> > insert 1M rows especially JSON that can be large, variable in size
> > and stored as blobs and indexed is not perhaps the correct way to do
> > this
> > insert performance will also depend on your tuning. Supporting
> > transactions, users or bulk processing are 3x sides of a compromise.
> > you should perhaps consider that insert is for inserting a few rows
> > into live tables ... you might be better using copy or \copy,
> > pg_dump if you are just trying to replicate a large table
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
What Adrian Klaver said ^
discovered even this works...
create view myview as (select row_to_json from mytable);
create table newtable as select * from myview where 1=0;
copy myview to program 'psql mydb postgres -c ''copy newtable from stdin'' '
;
Re: Insert with Jsonb column hangs
copy syntax can include any valid select statement COPY (any valid select statement joining tables and converting it row_to_json) TO 'some_dump_file' or can copy a view CREATE VIEW myview (any valid select statement joining tables and converting it row_to_json); COPY myview TO 'some_dump_file' Regards Hector Vass 07773 352559 On Sat, Mar 9, 2024 at 4:01 PM kuldeep singh wrote: > Copy may not work in our scenario since we need to join data from multiple > tables & then convert it to json using row_to_json . This json data > eventually needs to be stored in a target table . > > Will it be better if we break the process into batches of like 10,000 rows > & insert the data in its individual transactions? Or any other better > solution available ? > > On Sat, Mar 9, 2024 at 9:01 PM hector vass wrote: > >> >> >> On Sat, Mar 9, 2024 at 3:02 PM kuldeep singh >> wrote: >> >>> Hi, >>> >>> We are inserting data close to 1M record & having a single Jsonb column >>> but query is getting stuck. >>> >>> We are using insert into select * .. , so all the operations are within >>> the DB. >>> >>> If we are running select query individually then it is returning the >>> data in 40 sec for all rows but with insert it is getting stuck. >>> >>> PG Version - 15. >>> >>> What could be the problem here ? >>> >>> Regards, >>> KD >>> >> >> insert 1M rows especially JSON that can be large, variable in size and >> stored as blobs and indexed is not perhaps the correct way to do this >> insert performance will also depend on your tuning. Supporting >> transactions, users or bulk processing are 3x sides of a compromise. >> you should perhaps consider that insert is for inserting a few rows into >> live tables ... you might be better using copy or \copy, pg_dump if you are >> just trying to replicate a large table >> >>
Re: Insert with Jsonb column hangs
On 3/9/24 08:00, kuldeep singh wrote:
Copy may not work in our scenario since we need to join data from
multiple tables & then convert it to json using row_to_json . This
json data eventually needs to be stored in a target table .
Per:
https://www.postgresql.org/docs/current/sql-copy.html
"
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
<...>
query
A SELECT, VALUES, INSERT, UPDATE, or DELETE command whose results
are to be copied. Note that parentheses are required around the query.
For INSERT, UPDATE and DELETE queries a RETURNING clause must be
provided, and the target relation must not have a conditional rule, nor
an ALSO rule, nor an INSTEAD rule that expands to multiple statements.
"
Will it be better if we break the process into batches of like 10,000
rows & insert the data in its individual transactions? Or any other
better solution available ?
On Sat, Mar 9, 2024 at 9:01 PM hector vass > wrote:
On Sat, Mar 9, 2024 at 3:02 PM kuldeep singh
mailto:kuldeeparor...@gmail.com>> wrote:
Hi,
We are inserting data close to 1M record & having a single Jsonb
column but query is getting stuck.
We are using insert into select * .. , so all the operations are
within the DB.
If we are running select query individually then it is returning
the data in 40 sec for all rows but with insert it is getting stuck.
PG Version - 15.
What could be the problem here ?
Regards,
KD
insert 1M rows especially JSON that can be large, variable in size
and stored as blobs and indexed is not perhaps the correct way to do
this
insert performance will also depend on your tuning. Supporting
transactions, users or bulk processing are 3x sides of a compromise.
you should perhaps consider that insert is for inserting a few rows
into live tables ... you might be better using copy or \copy,
pg_dump if you are just trying to replicate a large table
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Insert with Jsonb column hangs
Copy may not work in our scenario since we need to join data from multiple tables & then convert it to json using row_to_json . This json data eventually needs to be stored in a target table . Will it be better if we break the process into batches of like 10,000 rows & insert the data in its individual transactions? Or any other better solution available ? On Sat, Mar 9, 2024 at 9:01 PM hector vass wrote: > > > On Sat, Mar 9, 2024 at 3:02 PM kuldeep singh > wrote: > >> Hi, >> >> We are inserting data close to 1M record & having a single Jsonb column >> but query is getting stuck. >> >> We are using insert into select * .. , so all the operations are within >> the DB. >> >> If we are running select query individually then it is returning the data >> in 40 sec for all rows but with insert it is getting stuck. >> >> PG Version - 15. >> >> What could be the problem here ? >> >> Regards, >> KD >> > > insert 1M rows especially JSON that can be large, variable in size and > stored as blobs and indexed is not perhaps the correct way to do this > insert performance will also depend on your tuning. Supporting > transactions, users or bulk processing are 3x sides of a compromise. > you should perhaps consider that insert is for inserting a few rows into > live tables ... you might be better using copy or \copy, pg_dump if you are > just trying to replicate a large table > >
Re: Insert with Jsonb column hangs
On Sat, Mar 9, 2024 at 3:02 PM kuldeep singh wrote: > Hi, > > We are inserting data close to 1M record & having a single Jsonb column > but query is getting stuck. > > We are using insert into select * .. , so all the operations are within > the DB. > > If we are running select query individually then it is returning the data > in 40 sec for all rows but with insert it is getting stuck. > > PG Version - 15. > > What could be the problem here ? > > Regards, > KD > insert 1M rows especially JSON that can be large, variable in size and stored as blobs and indexed is not perhaps the correct way to do this insert performance will also depend on your tuning. Supporting transactions, users or bulk processing are 3x sides of a compromise. you should perhaps consider that insert is for inserting a few rows into live tables ... you might be better using copy or \copy, pg_dump if you are just trying to replicate a large table
