Re: Questions about document "Concurrenry control" section

[David G. Johnston]

On Friday, October 11, 2024, iseki zero  wrote:

> So, should I use the [reply to all] button? I'm afraid the receipt list
> will growing to have too many address.
>
>
Yes, just use reply-all.  The to list only grows when people reply, and it
is a mailing list - all these people are getting the email anyway…this just
lets them filter on things they have replied to.

David J.

Re: Prune or Purge data stored on Postgres 14.13

[Erik Wienhold]

On 2024-10-11 20:43 +0200, Kaushal Shriyan wrote:
> I am running PostgreSQL 14.13 on RHEL 8.10 OS. Is there a way to Prune or
> Purge data stored on PostgreSQL 14.13 ?
> 
> For example I have Analytics data stored in PostgreSQL 14.13 server for
> last 1 year (1st September 2023 till date)
> 
> Is there a way to prune analytics data from Analytics Data stored on
> PostgreSQL 14.13 Database server starting from 1st September 2023 till
> February 29, 2024 and archive it to a tape drive or network file storage?
> (Six months analytics data). This is to make sure we do not run into an out
> of disk/storage space situation. Once we carry out the prune operation, we
> only have 6 months of data in the PG Database server and the older data
> beyond six months will be on tape drive or network file storage.

Use tablespaces[1] to store data on external storage.  Is the table
already partitioned?  If yes, than you can use ALTER TABLE to move the
partitions that end before 2024-03-01 to a tablespace on external
storage.

If the table is not partitioned[2] then you should think about doing
that because it simplifies the pruning/archiving process if it's going
to be a regular task.  But existing tables cannot be partitioned.  You'd
have to create a new table and attach partitions.  But the existing
table can also be attached as one partition and you can wait 6 more
months when you probably want to purge data again.  Let's say you create
those partitions now (October 2024), then the current data will go into
a partition P until 2024-11-01 and newer data will go into the next
partition.  After 2025-05-01 you can move partition P to another
tablespace.  This of course depends on how much disk space you can still
afford in that time.

But this could also work without partitioning if the archived data is
not required to be available alongside the live data.  Every 6 months
you could create a new archive table with a tablespace on external
storage and insert the rows of the last 6 months into that new archive
table.  Then delete those rows from the live table and VACUUM it.  The
live table will still use the same amount of disk space (unless you use
VACUUM FULL which will, however, use extra disk space while writing a
new copy of the table), but new rows will occupy the disk space that was
previously occupied by the now-deleted rows and reclaimed by VACUUM.

Hope that helps.

[1] https://www.postgresql.org/docs/14/manage-ag-tablespaces.html
[2] https://www.postgresql.org/docs/14/ddl-partitioning.html

-- 
Erik

Re: Question on indexes

[Greg Sabino Mullane]

(please start a new thread in the future rather than replying to an
existing one)

You cannot query on b and use an index on (a,b) as you observed. However,
you can have two indexes:

index1(a)
index2(b)

Postgres will be able to combine those when needed in the case where your
WHERE clause needs to filter by both columns. So then you no longer need
the two-column index.

Cheers,
Greg

Re: PGBouncer - Load balancing options

[Christophe Pettus]

Hello,

> On Oct 10, 2024, at 03:02, Ravi Varma Addala  wrote:
> 
> Hello Team,I wanted to reach out to provide some insight into a request from 
> my customer regarding their multiple PostgreSQL Flex instances with 
> PGbouncer. Currently, they are utilizing an Azure Load Balancer which 
> distributes connections using the Round-robin mechanism. However, they are 
> now seeking a solution that allows for load balancing based on the number of 
> connections, meaning the Load Balancer should route requests to the instance 
> with the least number of connections.Unfortunately, Azure Standard Load 
> Balancer does not support this capability. However, Application Gateway does 
> offer this functionality - Microsoft.Network/applicationGateways - Bicep, ARM 
> template & Terraform AzAPI reference | Microsoft Learn
>  ASK:
> 
> • Has anyone implemented this kind of approach and is it possible?
> • Can we do health probe with PostgreSQL to get no of connections metric?

Even though you mention pgbouncer in the subject:, your questions tare about 
proprietary products that your employer provides and supports.  It's probably 
best to ask internally about their functionality; the community almost 
certainly does not have any better insight into them.

Prune or Purge data stored on Postgres 14.13

[Kaushal Shriyan]

Hi,

I am running PostgreSQL 14.13 on RHEL 8.10 OS. Is there a way to Prune or
Purge data stored on PostgreSQL 14.13 ?

For example I have Analytics data stored in PostgreSQL 14.13 server for
last 1 year (1st September 2023 till date)

Is there a way to prune analytics data from Analytics Data stored on
PostgreSQL 14.13 Database server starting from 1st September 2023 till
February 29, 2024 and archive it to a tape drive or network file storage?
(Six months analytics data). This is to make sure we do not run into an out
of disk/storage space situation. Once we carry out the prune operation, we
only have 6 months of data in the PG Database server and the older data
beyond six months will be on tape drive or network file storage.

Please guide me. Thanks in advance.

Best Regards,

Kaushal

Re: Questions about document "Concurrenry control" section

[Tom Lane]

Erik Wienhold  writes:
> On 2024-10-11 15:00 +0200, iseki wrote:
>> But why the mailing list haven't use the "Reply-To" header specify where to
>> reply? Because the mail is you send to me directly?

> The message author sets Reply-To, if necessary, according to RFC 5322.
> Are there mailing lists that actually set this header?

It's been done in the past, but current practice is strongly against
it.  For example, in your own message that I'm replying to, there's
a DKIM signature (RFC 6376):

DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001;
t=1728654419;
h=from:from:reply-to:subject:subject:date:date:message-id:message-id:
 to:to:cc:cc:mime-version:mime-version:content-type:content-type:
 in-reply-to:in-reply-to:references:references;
bh=7w7CHebdKaXtlhCMxa/jNIcoEy1tFhZmzOD+dT7nP8k=;
b=K3s//HzYXU+chTDeY5p/wGwd5eglESiaugVSpGWo49ryL9ajLdimYMD3uIc3rr8PglZizV
nk2wDiMsHnLR0EAgKsGzNvtxt4N9hHxMk7UI3F4XOVYqnemk95YVRNpFpEFww833uUqA+9
RPypj/ezsKbi2vBzzXIoZ+Tf3t6XfuZYf6poq1J+ud0X278yQMnA1XbZNsenQkCWoYPiXb
yklxY2Nbp9NyiDHCDQ0KPYAT6/0ttzbprNhgSzhN7LND6ehUvLlsmKG3rwqby2LmFRwliQ
RDZ4MAxEzZKfOr8HGnubQf8FdQPGw6jlAn3U8199c+3QcIAWJ99Wrtb5rQPdEw==

That lists "reply-to" as one of the signed header fields.  So if the
mailing list were to modify Reply-To (including inserting one where
there was none before) a DKIM-verifying recipient would regard the
message as a forgery.

regards, tom lane

Re: Question on indexes

[Greg Sabino Mullane]

>
> if we have any column with large string/text values and we want it to be
> indexed then there is no choice but to go for a hash index. Please correct
> me if I'm wrong.
>

There are other strategies / solutions, but we would need to learn more
about your use case.

Cheers,
Greg

Re: Question on indexes

[Durgamahesh Manne]

On Fri, Oct 11, 2024 at 6:18 PM Greg Sabino Mullane 
wrote:

> (please start a new thread in the future rather than replying to an
> existing one)
>
> You cannot query on b and use an index on (a,b) as you observed. However,
> you can have two indexes:
>
> index1(a)
> index2(b)
>
> Postgres will be able to combine those when needed in the case where your
> WHERE clause needs to filter by both columns. So then you no longer need
> the two-column index.
>
> Cheers,
> Greg
>


Hi greg

Mail sent you with a new thread. composite key is on partitioned table

Regards,
Durga Mahesh

Re: Questions about document "Concurrenry control" section

[Erik Wienhold]

On 2024-10-11 15:00 +0200, iseki wrote:
> Oh sorry, I should use [reply to all] instead of [reply]. I don't have much
> experience with mailing lists...
> 
> But why the mailing list haven't use the "Reply-To" header specify where to
> reply? Because the mail is you send to me directly?

The message author sets Reply-To, if necessary, according to RFC 5322.
Are there mailing lists that actually set this header?

-- 
Erik

Re: Question on indexes

[Durgamahesh Manne]

On Fri, Oct 11, 2024 at 5:00 PM Greg Sabino Mullane 
wrote:

> if we have any column with large string/text values and we want it to be
>> indexed then there is no choice but to go for a hash index. Please correct
>> me if I'm wrong.
>>
>
> There are other strategies / solutions, but we would need to learn more
> about your use case.
>
> Cheers,
> Greg
>
>

Hi Respected Team

How do we enforce the secondary column of composite index to index scan on
concurrent activity in postgres?
Second column of composite index not in use effectively with index scan
when using second column at where clause

I have composite index on (placedon,id) of test
When querying  select * from test where id = '4234';
Value of id changes and during concurrent activity and cpu utilization
increased too much  that i have observed which means query plan changed why

I could see index scan with explain for it  on singal call or double calls

Is there any way to keep an index scan for it during concurrency rather
than a separate index on the second column of the composite index ?


Regards,
Durga Mahesh

Re: Questions about document "Concurrenry control" section

[iseki]

Oh sorry, I should use [reply to all] instead of [reply]. I don't have 
much experience with mailing lists...


But why the mailing list haven't use the "Reply-To" header specify where 
to reply? Because the mail is you send to me directly?


Thank you.

On 2024/10/11 14:53, Laurenz Albe wrote:

On Fri, 2024-10-11 at 11:04 +0800, admin@iseki.space wrote:

Maybe add the whole example or the URL to the document is better?

You shouldn't address that reply to me personally...

Yours,
Laurenz Albe

Re: Inefficient use of index scan on 2nd column of composite index during concurrent activity

[Durgamahesh Manne]

On Fri, Oct 11, 2024 at 9:57 PM Greg Sabino Mullane 
wrote:

> On Fri, Oct 11, 2024 at 9:28 AM Durgamahesh Manne <
> maheshpostgr...@gmail.com> wrote:
>
>> composite key (placedon,id)
>> In concurrent mode if i use id at where clause then query plan for that
>> id column changes
>>
>> How to mitigate it rather than use seperate index for id to continue
>> without change in query plan (index scan) during concurrent activity
>>
>
> Why the focus on "concurrent mode"? Perhaps explain what you mean by that.
>
> Speaking of explain, it might help if you show us the explain plans and
> how they are not coming out how you want. Also the table definitions, but
> feel free to not show columns unrelated to the problem.
>
> Cheers,
> Greg
>
> Hi Greg
Thanks for your quick response

 Partitioned table "test"
   Column|   Type   | Collation | Nullable |
Default | Storage  | Compression | Stats target | Description
-+--+---+--+-+--+-+--+-
 id   | bigint   |   | not null |
  | plain| |  |
 externalbetid   | text |   |  |
  | extended | |  |
 externalsystem  | text |   |  |
  | extended | |  |
 placedon| timestamp with time zone |   | not null |
  | plain| |  |
 txnstep | integer  |   |  |
  | plain| |  |
 txnstage| text |   |  |
  | extended | |  |
 txnstatus   | text |   |  |
  | extended | |  |
 "pmk_test" PRIMARY KEY, btree (id, placedon) REPLICA IDENTITY
if use this  (id,placedon) when running select query then no issues bez
select picks up first column of composite key
select * from test where id = '4234';
 Append  (cost=0.14..42.14 rows=19 width=1355) (actual time=0.177..0.186
rows=1 loops=1)
   ->  Index Scan using test_p2023_07_id_idx on test_p2023_07 test_1
 (cost=0.14..2.38 rows=1 width=1874) (actual time=0.009..0.009 rows=0
loops=1)
 Index Cond: (id = '4234'::text)
   ->  Index Scan using test_p2023_08_id_idx on test_p2023_08 test_2
 (cost=0.14..2.38 rows=1 width=1848) (actual time=0.005..0.005 rows=0
loops=1)
 Index Cond: (id = '4234'::text)
 Planning Time: 0.100 ms
 Execution Time: 0.40 ms

>if i change constraint order (placedon,id) then  in this case

I could see same index scan with explain analyze  for 1 call or 2 calls

Here concurrent mode means you are already aware (no of calls increases
concurrently)
Sudden cpu spike i have observed which is unusual(more than needed)   when
no of calls increased concurrently on that query

Based on that info  i suspected that query plan changed hence raised
question here  this is what i faced with mentioned columns order related to
problem


Example for better understanding to you
in oracle
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
SELECT /*+ INDEX(orders idx_orders_customer_date) */ *
FROM orders
WHERE order_date = '2024-01-01';
I am not sure how this works . this is the example gathered for you

I hope you can understand  . Sorry i can't explain more than this much


Regards,
Durga Mahesh

Re: Inefficient use of index scan on 2nd column of composite index during concurrent activity

[Greg Sabino Mullane]

On Fri, Oct 11, 2024 at 9:28 AM Durgamahesh Manne 
wrote:

> composite key (placedon,id)
> In concurrent mode if i use id at where clause then query plan for that id
> column changes
>
> How to mitigate it rather than use seperate index for id to continue
> without change in query plan (index scan) during concurrent activity
>

Why the focus on "concurrent mode"? Perhaps explain what you mean by that.

Speaking of explain, it might help if you show us the explain plans and how
they are not coming out how you want. Also the table definitions, but feel
free to not show columns unrelated to the problem.

Cheers,
Greg

Re: Connection between PostgreSQL and SAP HANA database

[Adrian Klaver]

On 10/11/24 01:23, Thürmann, Andreas wrote:

Please do not post. The convention on this list is to inline or bottom 
post. That makes it easier to follow the conversation flow.


See:
https://en.wikipedia.org/wiki/Posting_style



Yes I run it under Windows, I also have a Linux server for testing.
Under Windows I know that the driver works because i use it already in another 
project (MS SQL Server with LinkedServer).
In Linux I can test the HDBODBC driver via command line and the ISQL command, 
this works fine.


Works fine as in you can connect to the SAP HANA database with the 
HDBODBC driver?




I'm desperate about the PostgreSQL configuration. I have no idea how to configure 
it. I doubt the path (HDBODBC (SAP-driver) –> SAP HANA database) myself, but i 
cant connect to the HDBODBC driver directly via an extension.
What are the settings/commands you are using for the various parts of 
this connection chain:


1) HDBODBC driver.

2) ISQL command.

3) odbc_fdw.

4) Foreign server creation statements.



My previous assumption was that i need a extension for the foreign data 
wrapper, the foreign server, foreign table and so on...


Yeah that is shown here:

https://github.com/CartoDB/odbc_fdw

In the Example section.



How can I use the system dsn in PostgreSQL?





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Foreign Data Wrapper behavior?

[Adrian Klaver]

On 10/11/24 04:08, Koen De Groote wrote:

In the release notes for postgres 17 I'm reading:

 > The PostgreSQL foreign data wrapper (postgres_fdw), used to execute 
queries on remote PostgreSQL instances, can now push EXISTS and IN 
subqueries to the remote server for more efficient processing.


I'm confused as to what this means. In older versions, are parts of 
queries not sent to the foreign server? Or is this change meant to imply 
the sending of only the subqueries, the result of which is then directly 
used in pushing the entire query?


Or am I still wrong as to what this means?

I looked at the documentation and there doesn't seem to be any 
indication of particular queries not being pushed to the foreign server, 
so this wording that "can now push EXISTS and IN subqueries to the 
remote server" is confusing.


What am I missing?


Read:

https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-REMOTE-QUERY-OPTIMIZATION

F.36.5. Remote Query Optimization


As to the change in the Release Note see the --hackers discussion:

https://www.postgresql.org/message-id/c9e2a757cf3ac2333714eaf83a9cc184%40postgrespro.ru



Regards,
Koen De Groote


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Connection between PostgreSQL and SAP HANA database

[Adrian Klaver]

On 10/11/24 09:16, Adrian Klaver wrote:

On 10/11/24 01:23, Thürmann, Andreas wrote:

Please do not post. The convention on this list is to inline or bottom


That should have been do not top post.


post. That makes it easier to follow the conversation flow.

See:



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Questions about document "Concurrenry control" section

[Adrian Klaver]

On 10/11/24 20:10, admin@iseki.space wrote:
I found. Maybe we should reply to the mailing list only. Otherwise we'll 
receive multiple copies of the emails.





Not if you go here:

https://lists.postgresql.org/manage/

and check:

Don't receive an extra copy of mails when listed in To or CC fields

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Questions about document "Concurrenry control" section

[admin]

I found. Maybe we should reply to the mailing list only. Otherwise we'll 
receive multiple copies of the emails.

Re: Questions about document "Concurrenry control" section

[iseki zero]

So, should I use the [reply to all] button? I'm afraid the receipt list 
will growing to have too many address.


BTW, I'm using Thunderbird. If you have better software, tell me please.

Sorry for my poor English.

iseki zero.

在 2024/10/11 22:19, Tom Lane 写道:

Erik Wienhold writes:

On 2024-10-11 15:00 +0200, iseki wrote:

But why the mailing list haven't use the "Reply-To" header specify where to
reply? Because the mail is you send to me directly?

The message author sets Reply-To, if necessary, according to RFC 5322.
Are there mailing lists that actually set this header?

It's been done in the past, but current practice is strongly against
it.  For example, in your own message that I'm replying to, there's
a DKIM signature (RFC 6376):

DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001;
t=1728654419;
h=from:from:reply-to:subject:subject:date:date:message-id:message-id:
 to:to:cc:cc:mime-version:mime-version:content-type:content-type:
 in-reply-to:in-reply-to:references:references;
bh=7w7CHebdKaXtlhCMxa/jNIcoEy1tFhZmzOD+dT7nP8k=;
b=K3s//HzYXU+chTDeY5p/wGwd5eglESiaugVSpGWo49ryL9ajLdimYMD3uIc3rr8PglZizV
nk2wDiMsHnLR0EAgKsGzNvtxt4N9hHxMk7UI3F4XOVYqnemk95YVRNpFpEFww833uUqA+9
RPypj/ezsKbi2vBzzXIoZ+Tf3t6XfuZYf6poq1J+ud0X278yQMnA1XbZNsenQkCWoYPiXb
yklxY2Nbp9NyiDHCDQ0KPYAT6/0ttzbprNhgSzhN7LND6ehUvLlsmKG3rwqby2LmFRwliQ
RDZ4MAxEzZKfOr8HGnubQf8FdQPGw6jlAn3U8199c+3QcIAWJ99Wrtb5rQPdEw==

That lists "reply-to" as one of the signed header fields.  So if the
mailing list were to modify Reply-To (including inserting one where
there was none before) a DKIM-verifying recipient would regard the
message as a forgery.

regards, tom lane

PGBouncer - Load balancing options

[Ravi Varma Addala]

Hello Team,
I wanted to reach out to provide some insight into a request from my customer 
regarding their multiple PostgreSQL Flex instances with PGbouncer. Currently, 
they are utilizing an Azure Load Balancer which distributes connections using 
the Round-robin mechanism. However, they are now seeking a solution that allows 
for load balancing based on the number of connections, meaning the Load 
Balancer should route requests to the instance with the least number of 
connections.
Unfortunately, Azure Standard Load Balancer does not support this capability. 
However, Application Gateway does offer this functionality - 
Microsoft.Network/applicationGateways - Bicep, ARM template & Terraform AzAPI 
reference | Microsoft 
Learn

ASK:


  1.  Has anyone implemented this kind of approach and is it possible?
  2.  Can we do health probe with PostgreSQL to get no of connections metric?

  1.
Kind Regards,
Ravi Varma Addala
Cloud Solution Architect
ravivarma.add...@microsoft.com

[Microsoft]

Fwd: Inefficient use of index scan on 2nd column of composite index during concurrent activity

[Durgamahesh Manne]

-- Forwarded message -
From: Durgamahesh Manne 
Date: Mon, Oct 7, 2024 at 10:01 AM
Subject: Inefficient use of index scan on 2nd column of composite index
during concurrent activity
To: 


Hi team

Second column of composite index not in use effectively with index scan
when using second column at where clause

I have composite index on (placedon,id) of test
When quering  select * from test where id = '4234';
Value of id changes and during concurrent activity and cpu utilization
increased toomuch  that i have observed which means query plan changed why

I could see index scan with explain for it

Is there any way to keep index scan for it during even on concurrency
rather than seperate index on second column of composite index ?

Hope everyone understand this

Regards,
Durga Mahesh


Hi Greg

you mentioned that below

(please start a new thread in the future rather than replying to an
existing one)

You cannot query on b and use an index on (a,b) as you observed. However,
you can have two indexes:

index1(a)
index2(b)

Postgres will be able to combine those when needed in the case where your
WHERE clause needs to filter by both columns. So then you no longer need
the two-column index.



Hi Greg ,

Here not using composite index on ordinary table.
Composite index that i use on partitioned table is mandatory for use to
replicate data to target using pglogical  (sorry this is not mentioned
earlier)

composite key (placedon,id)
In concurrent mode if i use id at where clause then query plan for that id
column changes

How to mitigate it rather than use seperate index for id to continue
without change in query plan (index scan) during concurrent activity

I hope you understand this

Regards,
Durga Mahesh





Cheers,
Greg

Foreign Data Wrapper behavior?

[Koen De Groote]

In the release notes for postgres 17 I'm reading:

> The PostgreSQL foreign data wrapper (postgres_fdw), used to execute
queries on remote PostgreSQL instances, can now push EXISTS and IN
subqueries to the remote server for more efficient processing.

I'm confused as to what this means. In older versions, are parts of queries
not sent to the foreign server? Or is this change meant to imply the
sending of only the subqueries, the result of which is then directly used
in pushing the entire query?

Or am I still wrong as to what this means?

I looked at the documentation and there doesn't seem to be any indication
of particular queries not being pushed to the foreign server, so this
wording that "can now push EXISTS and IN subqueries to the remote server"
is confusing.

What am I missing?

Regards,
Koen De Groote