Performance Issue with Hash Partition Query Execution in PostgreSQL 16

[Ramakrishna m]

Hi Team,

One of the queries, which retrieves a single record from a table with 16
hash partitions, is taking more than 10 seconds to execute. In contrast,
when we run the same query manually, it completes within milliseconds. This
issue is causing exhaustion of the application pools. Do we have any bugs
in postgrs16 hash partitions? Please find the attached log, table, and
execution plan.

size of the each partitions : 300GB
Index Size : 12GB

Postgres Version : 16.x
Shared Buffers : 75 GB
Effective_cache :  175 GB
Work _mem : 4MB
Max_connections : 3000

OS  : Ubuntu 22.04
Ram : 384 GB
CPU : 64

Please let us know if you need any further information or if there are
additional details required.


Regards,
Ram.


Postgres_hash_partion_issue.sql
Description: Binary data

Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16

[Laurenz Albe]

On Wed, 2024-11-06 at 22:43 +0530, Ramakrishna m wrote:
> One of the queries, which retrieves a single record from a table with 16 hash 
> partitions,
> is taking more than 10 seconds to execute. In contrast, when we run the same 
> query manually,
> it completes within milliseconds.

If I read your file right, the slow execution has an additional "LIMIT 55".
That can make a difference.

In general, you can use auto_explain to get the execution plan of the slow 
query.

Yours,
Laurenz Albe

About the stability of COPY BINARY data

[Dominique Devienne]

>From https://www.postgresql.org/docs/current/sql-copy.html:
|> binary-format file is less portable across machine architectures
and PostgreSQL versions

In my experience, the binary encoding of binding/resultset/copy is
endian neutral (network byte order), so what is the less portable
across machine architectures that warning about?

Also, does the code for per-type _send() and _recv() functions really change
across versions of PostgreSQL? How common are instances of such
changes across versions? Any examples of such backward-incompatible
changes, in the past?

The binary data contains OIDs, but if sticking to built-in types,
which OIDs are unlikely to change across versions?

I'm obviously storing COPY BINARY data (we have lots of bytea
columns), and I wonder how bad it is long term, and across PostgreSQL
versions.

Thanks for any insights, --DD

Re: Index Partition Size Double of its Table Partition?

[Don Seiler]

On Thu, Oct 31, 2024 at 11:13 AM Rob Sargent  wrote:

> Whats the fill factor?
>

No fill factor is specified, so I'm assuming it's the default 90% for
indexes.

FYI we did a REINDEX for the index in question tonight. Since the index was
for last month, there are no more writes to it so we didn't use
CONCURRENTLY either. The size went from 83GB to 48GB, which also happens to
be the size of the table partition.

-- 
Don Seiler
www.seiler.us

Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16

[David Mullineux]

It might be worth eliminating the use of cached plans here. Is your app
using prepared statements at all?
Point is that if the optimizer sees the same prepared query , 5 times, the
it locks the plan that it found at that time. This is a good trade off as
it avoids costly planning-time for repetitive queries. But if you are
manually querying, the  a custom plan will be generated  anew.
A quick analyze of the table should reset the stats and invalidate any
cached plans.
This may not be your problem  just worth eliminating it from the list of
potential causes.

On Wed, 6 Nov 2024, 17:14 Ramakrishna m,  wrote:

> Hi Team,
>
> One of the queries, which retrieves a single record from a table with 16
> hash partitions, is taking more than 10 seconds to execute. In contrast,
> when we run the same query manually, it completes within milliseconds. This
> issue is causing exhaustion of the application pools. Do we have any bugs
> in postgrs16 hash partitions? Please find the attached log, table, and
> execution plan.
>
> size of the each partitions : 300GB
> Index Size : 12GB
>
> Postgres Version : 16.x
> Shared Buffers : 75 GB
> Effective_cache :  175 GB
> Work _mem : 4MB
> Max_connections : 3000
>
> OS  : Ubuntu 22.04
> Ram : 384 GB
> CPU : 64
>
> Please let us know if you need any further information or if there are
> additional details required.
>
>
> Regards,
> Ram.
>

Trouble using pg_rewind to undo standby promotion

[Craig McIlwee]

I have a primary -> standby 1 -> standby 2 setup with all servers running
PG 13.8 (this effort is part of getting on to a newer version, but I think
those details aren't relevant to this problem).  The first standby uses
streaming replication from the primary and the second standby is using a
WAL archive with a restore_command.  To make this standby chain work,
standby 1 is set to archive_mode = always with a command that populates the
WAL archive.

I would like to be able to promote standby 2 (hereon referred to just as
'standby'), perform some writes, then rewind it back to the point before
promotion so it can become a standby again.  The documentation for
pg_rewind says that this is supported and it seems like it should be
straightforward, but I'm not having any luck getting this to work so I'm
hoping someone can point out what I'm doing wrong.  Here's what I did:

First, observe that WAL is properly being applied from the archive.  Note
that we are currently on timeline 1.

2024-11-06 09:51:23.286 EST [5438] LOG:  restored log file
"0001771100F9" from archive
2024-11-06 09:51:23.434 EST [5438] LOG:  restored log file
"0001771100FA" from archive
/data/wal_archive/restore_operational.sh: line 2:
/data/wal_archive/operational/0001771100FB.gz: No such file or
directory
/data/wal_archive/restore_operational.sh: line 2:
/data/wal_archive/operational/0002.history.gz: No such file or directory

Next, stop postgres, set wal_log_hints = on as required by pg_rewind, and
restart postgres.  I also make a copy of the data directory while the
postgres is not running so I can repeat my test, which works fine on a
small test database but won't be possible for the multi TB database that I
will eventually be doing this on.

Now promote the standby using "select pg_promote()" and see that it
switches to a new timeline.  You can also see that the last WAL applied
from the archive is 7718/BF.

2024-11-06 12:10:10.831 EST [4336] LOG:  restored log file
"0001771800BD" from archive
2024-11-06 12:10:10.996 EST [4336] LOG:  restored log file
"0001771800BE" from archive
/data/wal_archive/restore_operational.sh: line 2:
/data/wal_archive/operational/0001771800BF.gz: No such file or
directory
/data/wal_archive/restore_operational.sh: line 2:
/data/wal_archive/operational/0002.history.gz: No such file or directory
2024-11-06 12:10:15.384 EST [4336] LOG:  restored log file
"0001771800BF" from archive
/data/wal_archive/restore_operational.sh: line 2:
/data/wal_archive/operational/0001771800C0.gz: No such file or
directory
2024-11-06 12:10:15.457 EST [4336] LOG:  received promote request
2024-11-06 12:10:15.457 EST [4336] LOG:  redo done at 7718/BF30
2024-11-06 12:10:15.457 EST [4336] LOG:  last completed transaction was at
log time 2024-11-06 12:10:22.627074-05
2024-11-06 12:10:15.593 EST [4336] LOG:  restored log file
"0001771800BF" from archive
/data/wal_archive/restore_operational.sh: line 2:
/data/wal_archive/operational/0002.history.gz: No such file or directory
2024-11-06 12:10:15.611 EST [4336] LOG:  selected new timeline ID: 2
2024-11-06 12:10:15.640 EST [4336] LOG:  archive recovery complete
/data/wal_archive/restore_operational.sh: line 2:
/data/wal_archive/operational/0001.history.gz: No such file or directory
2024-11-06 12:10:17.028 EST [4329] LOG:  database system is ready to accept
connections

Next, insert a record into just to make some changes that I want to
rollback later.  (What I will eventually be doing is creating a publication
to ship data to a newer version, but again, that's not what's important
here.)

Finally, shutdown postgres and attempt a rewind.  The address used in the
--source-server connection string is the address of the primary.

2024-11-06 12:11:11.139 EST [4329] LOG:  received fast shutdown request
2024-11-06 12:11:11.143 EST [4329] LOG:  aborting any active transactions
2024-11-06 12:11:11.144 EST [4329] LOG:  background worker "logical
replication launcher" (PID 5923) exited with exit code 1
2024-11-06 12:11:40.933 EST [4342] LOG:  shutting down
2024-11-06 12:11:41.753 EST [4329] LOG:  database system is shut down

/usr/pgsql-13/bin/pg_rewind --target-pgdata=/data/pgsql/operational
--source-server="host=x.x.x.x dbname=postgres user=xxx password=xxx"
--dry-run --progress --restore-target-wal

pg_rewind: connected to server
pg_rewind: servers diverged at WAL location 7718/BFE8 on timeline 1
/data/wal_archive/restore_operational.sh: line 2:
/data/wal_archive/operational/0002771800BF.gz: No such file or
directory
pg_rewind: error: could not restore file "0002771800BF" from
archive
pg_rewind: fatal: could not find previous WAL record at 7718/BFE8

pg_rewind shows the point of divergence as 7718/BF on timeline 1, but when
it tries to replay WAL using the restore command it is trying to find WAL
from timeline 2 rather than picking back up on tim