Insert query performance

[sud]

Hello All,

In a version 15.4 postgres database, Is it possible that , if we have two
big range partition tables with foreign key relationships between them,
insert into the child table can cause slowness if we don't have foreign key
index present in the child table? Basically it need to make sure the new
row already added to parent partition table or not.


And if there is any possible way(example query tracing etc) to get the
underlying system queries which gets triggered as part of the main insert
query? For example in above scenario, postgres must be executing some query
to check if the incoming  row to the child table already exists in the
parent table or not?


Regards

Sud

Re: Insert query performance

[Muhammad Ikram]

Hi Sud,

Please make following change in your postgresql.conf file

log_statement = 'all'


Regards,
Ikram


On Mon, Aug 19, 2024 at 12:48 PM sud  wrote:

> Hello All,
>
> In a version 15.4 postgres database, Is it possible that , if we have two
> big range partition tables with foreign key relationships between them,
> insert into the child table can cause slowness if we don't have foreign key
> index present in the child table? Basically it need to make sure the new
> row already added to parent partition table or not.
>
>
> And if there is any possible way(example query tracing etc) to get the
> underlying system queries which gets triggered as part of the main insert
> query? For example in above scenario, postgres must be executing some query
> to check if the incoming  row to the child table already exists in the
> parent table or not?
>
>
> Regards
>
> Sud
>


-- 
Muhammad Ikram

Re: Insert query performance

[David Rowley]

On Mon, 19 Aug 2024 at 19:48, sud  wrote:
> In a version 15.4 postgres database, Is it possible that , if we have two big 
> range partition tables with foreign key relationships between them, insert 
> into the child table can cause slowness if we don't have foreign key index 
> present in the child table? Basically it need to make sure the new row 
> already added to parent partition table or not.

Having an index on the referencing columns is only useful for DELETEs
and UPDATEs affecting the foreign key column(s).  For INSERTs to the
referencing table, technically having indexes there would only slow
down inserts due to the additional overhead of having to maintain the
index, however, the overhead of having the index might be fairly
minuscule when compared to performing a CASCADE UPDATE or DELETE to
the referencing table when the DDL is performed on the referenced
table.

> And if there is any possible way(example query tracing etc) to get the 
> underlying system queries which gets triggered as part of the main insert 
> query? For example in above scenario, postgres must be executing some query 
> to check if the incoming  row to the child table already exists in the parent 
> table or not?

EXPLAIN ANALYZE will list the time it took to execute the foreign key
trigger in the "Trigger for constraint" section.

David

Re: use of postgres reg* datatypes in user tables?

[Tom Lane]

plsqlvids01 plsqlvids01  writes:
> What is the use of postgres regclass datatype in user tables? When would
> one want to use them when regular data types are available? If any tables
> have these data types, since pg_upgrade would fail, what data types should
> those be converted to?

Indeed, storing reg* types in user tables isn't particularly
recommended because of the pg_upgrade problem.  However, casting
to a reg* type is plenty useful in queries on the system catalogs.
There is more info and examples in the manual:

https://www.postgresql.org/docs/current/datatype-oid.html

regards, tom lane

WAL replication and Archive command for pgbackrest on same server conf

[KK CHN]

Hi List ,

I am trying to configure the pgbackrest  on a live server ( RHEL9, EPAS16
and Pgbacrest .2.52)  which is having a working   WAL archive
configuration  to a standby server in postgresql.conf as follows


1. archive_mod =on
2. archive_level=replica
3. archive_command = 'cp %p /data/archive/%f'


To do the pgbackrest conf on the same   archive_command   directive (So
both the existing WAL configuration as well as my new pgbackrest option
both will work smoothly.   how to add an entry  in line 3  ??

archive_command = 'pgbackrest --stanza=Demo  archive-push  cp %p
/data/archive/%f '



Please correct me If I am doing wrong in the above line..


Thank you,
Krish

Re: use of postgres reg* datatypes in user tables?

[David G. Johnston]

On Sunday, August 18, 2024, plsqlvids01 plsqlvids01 
wrote:
>
> What kind of data does these data types store - numbers or text?
>
>
https://www.postgresql.org/docs/current/datatype-oid.html

They are aliases for oid.

David J.

Re: WAL replication and Archive command for pgbackrest on same server conf

[Ruben Morais]

hi,

you can combine:

archive_command = 'pgbackrest --stanza=Demo  archive-push  %p && cp %p
/data/archive/%f '

You need to change your standby replica to consume the wals from pgbackrest
if you want instead to use both commands.

*Rúben Morais*

 *(+351) 965775713*

*ruben.gmor...@gmail.com
*


On Mon, Aug 19, 2024 at 2:10 PM KK CHN  wrote:

> Hi List ,
>
> I am trying to configure the pgbackrest  on a live server ( RHEL9, EPAS16
> and Pgbacrest .2.52)  which is having a working   WAL archive
> configuration  to a standby server in postgresql.conf as follows
>
>
> 1. archive_mod =on
> 2. archive_level=replica
> 3. archive_command = 'cp %p /data/archive/%f'
>
>
> To do the pgbackrest conf on the same   archive_command   directive (So
> both the existing WAL configuration as well as my new pgbackrest option
> both will work smoothly.   how to add an entry  in line 3  ??
>
> archive_command = 'pgbackrest --stanza=Demo  archive-push  cp %p
> /data/archive/%f '
>
>
>
> Please correct me If I am doing wrong in the above line..
>
>
> Thank you,
> Krish
>

pg 16.4, logical replication, use case = data archiving

[milist ujang]

Hi all,

I Play logical replication with row filter feature, my use case is data
archiving.
in lab env, it worked, my questions:
- during alter subscription, what about on-going DML? Can it be lost?
- any lock during alter subscription should I aware?


scenario:

# on source

tutorial=# ALTER PUBLICATION pub1 set TABLE users,likes,articles WHERE
(title <> 'del');

ALTER PUBLICATION

tutorial=# \dRp+

  Publication pub1

  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root

--++-+-+-+---+--

 postgres | f  | t   | t   | t   | t | f

Tables:

"public.articles" WHERE (title <> 'del'::text)

"public.likes"

"public.users"

wait a moment

update users set password_digest='del' where id=10;

wait a moment...

tutorial=# ALTER PUBLICATION pub1 set TABLE users WHERE (password_digest <>
'del'),likes,articles WHERE (title <> 'del');

ALTER PUBLICATION

tutorial=# \dRp+

  Publication pub1

  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root

--++-+-+-+---+--

 postgres | f  | t   | t   | t   | t | f

Tables:

"public.articles" WHERE (title <> 'del'::text)

"public.likes"

"public.users" WHERE (password_digest <> 'del'::text)


delete from users where id=10;

# source and target

select * from users;

id=10 gone at source, but exist at target



-- 
regards

ujang jaenudin | Self-Employed, DBA Consultant
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

Re: pg 16.4, logical replication, use case = data archiving

[milist ujang]

My experience with oracle streams (upto version 12.1).
there is a tag at capture, by default tag is null (if not defined).

at the session level we set DBMS_STREAMS.SET_TAG ; so whatever this session
does, will not replicate.
Maybe this kind of "tag" is planned in the upcoming logical replication
feature ?




On Tue, Aug 20, 2024 at 7:26 AM milist ujang  wrote:

> Hi all,
>
> I Play logical replication with row filter feature, my use case is data
> archiving.
> in lab env, it worked, my questions:
> - during alter subscription, what about on-going DML? Can it be lost?
> - any lock during alter subscription should I aware?
>
>
> scenario:
>
> # on source
>
> tutorial=# ALTER PUBLICATION pub1 set TABLE users,likes,articles WHERE
> (title <> 'del');
>
> ALTER PUBLICATION
>
> tutorial=# \dRp+
>
>   Publication pub1
>
>   Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
>
>
> --++-+-+-+---+--
>
>  postgres | f  | t   | t   | t   | t | f
>
> Tables:
>
> "public.articles" WHERE (title <> 'del'::text)
>
> "public.likes"
>
> "public.users"
>
> wait a moment
>
> update users set password_digest='del' where id=10;
>
> wait a moment...
>
> tutorial=# ALTER PUBLICATION pub1 set TABLE users WHERE (password_digest
> <> 'del'),likes,articles WHERE (title <> 'del');
>
> ALTER PUBLICATION
>
> tutorial=# \dRp+
>
>   Publication pub1
>
>   Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
>
>
> --++-+-+-+---+--
>
>  postgres | f  | t   | t   | t   | t | f
>
> Tables:
>
> "public.articles" WHERE (title <> 'del'::text)
>
> "public.likes"
>
> "public.users" WHERE (password_digest <> 'del'::text)
>
>
> delete from users where id=10;
>
> # source and target
>
> select * from users;
>
> id=10 gone at source, but exist at target
>
>
>
> --
> regards
>
> ujang jaenudin | Self-Employed, DBA Consultant
> http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
>


-- 
regards

ujang jaenudin | Self-Employed, DBA Consultant
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab