Re: Pgpool with high availability

[Muhammad Imtiaz]

Hi,

Can you please provide the Pgpool logs? Additionally, in a distributed
environment, ensure that Pgpool is properly configured. You can follow
these documented steps to set up the Pgpool configurations
pgpool.conf,pcp.conf and pool_hba.conf .

Regards,
Muhammad Imtiaz

On Tue, 28 May 2024 at 23:01, Adrian Klaver 
wrote:

>
>
> On 5/28/24 1:31 AM, vijay patil wrote:
> >
> > HI Team,
> >
> > "I'm encountering challenges while configuring Pgpool with high
> > availability. The initial setup is completed, and Pgpool is operational
> > on a single node, functioning without issues. However, upon attempting
> > to start Pgpool on any additional nodes, particularly node 2, it becomes
> > immediately unreachable.
>
> And how we are supposed to arrive at an answer with essentially no
> information provided?
>
> Need:
>
> 1) Configuration for initial setup.
>
> 2) A more detailed explanation of what "... upon attempting
> to start Pgpool on any additional nodes" means? Include configuration
> changes.
>
> 3) The error messages.
>
> 4) Where the nodes are located?
>
>
> >
> > I'm seeking assistance to address this issue. My setup consists of three
> > nodes, each hosting both PostgreSQL and Pgpool services."
> >
> >
> > Thanks
> >
> > Vijay
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>

Re: tds_fdw >> Install Foreign data Wrapper on EDB Postgres to connect to SQL server database

[Muhammad Imtiaz]

Hi ,

You need to install the  LLVM toolset.

1)Check that if it is installed or not :

yum list installed | grep llvm-toolset

2)If it is not installed, you can install it using the following command.
sudo yum install llvm-toolset-7

Regards,
Muhammad Imtiaz



On Wed, 29 May 2024 at 05:27, Ian Lawrence Barwick 
wrote:

> 2024年5月29日(水) 6:10 Sumit Kochar :
> >
> > Installation of Foreign data Wrapper on EDB Postgres to connect to SQL
> server database is not working.
> >
> >
> >
> > https://github.com/tds-fdw/tds_fdw/issues/357
> >
> >
> >
> > Please advise if this has been encountered or a workaround is available.
> (...)
>
> > [root@hostip tds_fdw-2.0.3]# make USE_PGXS=1 install
> >
> > /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes
> -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2
> -I./include/ -fvisibility=hidden  -I. -I./ -I/usr/edb/as13/include/server
> -I/usr/edb/as13/include/internal -I/usr/libexec/edb-icu66/include/
> -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm
> -c -o src/tds_fdw.bc src/tds_fdw.c
> >
> > make: /opt/rh/llvm-toolset-7/root/usr/bin/clang: Command not found
>
> IIRC you'll need to find and install the SCLO package for Oracle Linux
> 7, assuming it's
> available. For CentOS it's this repository:
> http://mirror.centos.org/centos/7/sclo/ .
>
> Regards
>
> Ian Barwick
>
>
>

Re: Use of inefficient index in the presence of dead tuples

[Tom Lane]

Alexander Staubo  writes:
> (2) Set up schema. It's important to create the index before insertion, in 
> order to provoke a
> situation where the indexes have dead tuples:
> ...
> (4) Then ensure all tuples are dead except one:

> DELETE FROM outbox_batches;
> INSERT INTO outbox_batches (receiver, id) VALUES ('dummy', 'test');

> (5) Analyze:

> ANALYZE outbox_batches;

So the problem here is that the ANALYZE didn't see any of the dead rows
and thus there is no way to know that they all match 'dummy'.  The cost
estimation is based on the conclusion that there is exactly one row
that will pass the index condition in each case, and thus the "right"
index doesn't look any cheaper than the "wrong" one --- in fact, it
looks a little worse because of the extra access to the visibility
map that will be incurred by an index-only scan.

I'm unpersuaded by the idea that ANALYZE should count dead tuples.
Since those are going to go away pretty soon, we would risk
estimating on the basis of no-longer-relevant stats and thus
creating problems worse than the one we solve.

What is interesting here is that had you done ANALYZE *before*
the delete-and-insert, you'd have been fine.  So it seems like
somewhat out-of-date stats would have benefited you.

It would be interesting to see a non-artificial example that took
into account when the last auto-vacuum and auto-analyze really
happened, so we could see if there's any less-fragile way of
dealing with this situation.

regards, tom lane

Re: tds_fdw >> Install Foreign data Wrapper on EDB Postgres to connect to SQL server database

[Ian Lawrence Barwick]

2024年5月29日(水) 6:10 Sumit Kochar :
>
> Installation of Foreign data Wrapper on EDB Postgres to connect to SQL server 
> database is not working.
>
>
>
> https://github.com/tds-fdw/tds_fdw/issues/357
>
>
>
> Please advise if this has been encountered or a workaround is available.
(...)

> [root@hostip tds_fdw-2.0.3]# make USE_PGXS=1 install
>
> /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes 
> -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  
> -I./include/ -fvisibility=hidden  -I. -I./ -I/usr/edb/as13/include/server 
> -I/usr/edb/as13/include/internal -I/usr/libexec/edb-icu66/include/ 
> -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c 
> -o src/tds_fdw.bc src/tds_fdw.c
>
> make: /opt/rh/llvm-toolset-7/root/usr/bin/clang: Command not found

IIRC you'll need to find and install the SCLO package for Oracle Linux
7, assuming it's
available. For CentOS it's this repository:
http://mirror.centos.org/centos/7/sclo/ .

Regards

Ian Barwick

tds_fdw >> Install Foreign data Wrapper on EDB Postgres to connect to SQL server database

[Sumit Kochar]

Installation of Foreign data Wrapper on EDB Postgres to connect to SQL server 
database is not working.

https://github.com/tds-fdw/tds_fdw/issues/357

Please advise if this has been encountered or a workaround is available.

OS:

NAME="Oracle Linux Server"
VERSION="7.9"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.9"
PRETTY_NAME="Oracle Linux Server 7.9"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:9:server"
HOME_URL=https://linux.oracle.com/
BUG_REPORT_URL=https://github.com/oracle/oracle-linux

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.9
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.9


Version of tds_fdw:

postgres=# \dx

  List of installed extensions

   Name   | Version |   Schema   | Description

--+-++--

 edb_dblink_libpq | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper 
for PostgreSQL

 edb_dblink_oci   | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper 
for Oracle

 edbspl   | 1.0 | pg_catalog | EDB-SPL procedural language

 pldbgapi | 1.1 | pg_catalog | server-side support for debugging 
PL/pgSQL functions

 plpgsql  | 1.0 | pg_catalog | PL/pgSQL procedural language

(5 rows)


PostgreSQL Version:


postgres=# SELECT version();

 version

--

 PostgreSQL 13.13 (EnterpriseDB Advanced Server 13.13.19) on 
x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 
64-bit

(1 row)


Issue Details:


tds_fdw install steps performed:





yum install install edb-as13-server edb-as13-server-libs edb-as13-server-devel

yum install -y gcc make wget epel-release freetds freetds-devel

dnf install redhat-rpm-config

subscription-manager repos --enable rhel-7-server-optional-rpms \

--enable rhel-server-rhscl-7-rpms \

--enable rhel-7-server-devtools-rpms

yum install llvm-toolset-7-clang-analyzer llvm-toolset-7-clang-tools-extra 
llvm-toolset-7 llvm-toolset-7-clang llvm-toolset-7-clang-libs  
llvm-toolset-7-llvm-libs





export TDS_FDW_VERSION="2.0.3"

wget https://github.com/tds-fdw/tds_fdw/archive/refs/tags/v2.0.3.tar.gz -O 
tds_fdw-${TDS_FDW_VERSION}.tar.gz

tar -xvzf tds_fdw-${TDS_FDW_VERSION}.tar.gz

cd tds_fdw-${TDS_FDW_VERSION}

PATH=/usr/edb/as13/bin:$PATH

make USE_PGXS=1

PATH=/usr/edb/as13/bin:$PATH

make USE_PGXS=1 install





Logs:





[root@hostip ~]# yum install install edb-as13-server edb-as13-server-libs 
edb-as13-server-devel\

Loaded plugins: rhnplugin\

This system is not registered with Spacewalk server.\

You can use rhn_register to register.\

Spacewalk support will be disabled.\

Repository mc-ol7-base-x86_64 is listed more than once in the configuration\

Repository mc-ol7-layered-x86_64 is listed more than once in the configuration\

Repository mc-ol7_tsm-8.1.13_x86_64 is listed more than once in the 
configuration\

Repository mc-ol7-chef_client_15-x86_64 is listed more than once in the 
configuration\

No package install available.\

Package edb-as13-server-13.13.19-1.el7.x86_64 already installed and latest 
version\

Package edb-as13-server-libs-13.13.19-1.el7.x86_64 already installed and latest 
version\

Package edb-as13-server-devel-13.13.19-1.el7.x86_64 already installed and 
latest version\

Nothing to do\

[root@hostip ~]#\

[root@hostip ~]# yum install -y gcc make wget epel-release freetds 
freetds-devel\

Loaded plugins: rhnplugin\

This system is not registered with Spacewalk server.\

You can use rhn_register to register.\

Spacewalk support will be disabled.\

Repository mc-ol7-base-x86_64 is listed more than once in the configuration\

Repository mc-ol7-layered-x86_64 is listed more than once in the configuration\

Repository mc-ol7_tsm-8.1.13_x86_64 is listed more than once in the 
configuration\

Repository mc-ol7-chef_client_15-x86_64 is listed more than once in the 
configuration\

Package gcc-4.8.5-44.0.3.el7.x86_64 already installed and latest version\

Package 1:make-3.82-24.el7.x86_64 already installed and latest version\

Package wget-1.14-18.el7_6.1.x86_64 already installed and latest version\

Package epel-release-7-14.noarch already installed and latest version\

Package freetds-1.3.3-1.el7.x86_64 already installed and latest version\

Package freetds-devel-1.3.3-1.el7.x86_64 already installed and latest version\

Nothing to do\

[root@hostip ~]#\

[root@hostip ~]# dnf install redhat-rpm-config\

-bash: dnf: command not found\

[root@hostip ~]#\

[root@hostip ~]# subscription-manager repos --enable 
rhel-7-server-optional-rpms \ --enable rhel-server-rhscl-7-rpms \ --enable 
rhel-7-server-devtools-rpms\

-bash: 

Re: Long running query causing XID limit breach

[yudhi s]

On Mon, May 27, 2024 at 2:50 PM Laurenz Albe 
wrote:

>
> > > But again for HA , in case primary down we should not be in big lag
> for the standby
> > > and thus we want the standby also with minimal lag. And as you
> mentioned there will
> > > never be incorrect results but at amx it will be query cancellation,
> so I was thinking,
> > > if it's fine to just keep the "hot_feedback_standby" as OFF and let the
> > > max_standby_streaming_delay set as it is like 14 sec. Let me know your
> thoughts.
> >
> You cannot have it.
> Let me repeat: you cannot have it.
>
> The only way you can have no delay in replication AND no canceled queries
> is
> if you use two different standby servers with different settings for
> "max_standby_streaming_delay".  One of the server is for HA, the other for
> your long-running queries.
>
> When you suggest having different max_standby_streaming_delay for first
replica (say 10 sec for High availability) and second replica(say -1 for
long running queries). Do you also suggest  keeping "hot_feedback_standby"
as "OFF" for all the three instances i.e. master and both the replicas?

Also OP has added a few other parameters as below, do you think these
should be needed?
 I think the master and first replica should have the same set up because
in case of any disaster to master the first replica should be able to take
the place of master.

Master/Primary First Replica/Standby for High Availability Second Replica
for Reporting
hot_standby_feedback=ON hot_standby_feedback=ON hot_standby_feedback=OFF
max_standby_streaming_delay=10 sec max_standby_streaming_delay=10 sec
max_standby_streaming_delay=-1
(Infinite)
statement_timeout = "2hrs" statement_timeout="2hrs" No statement_timeout
i.e. infinite
idle_in_transaction_session_timeout=10minutes
idle_in_transaction_session_timeout=10minutes No
idle_in_transaction_session_timeout i.e. infinite
autovacuum_freeze_max_age=100M autovacuum_freeze_max_age=100M
autovacuum_freeze_max_age=100M
Log_autovacuum_min_duration=0 Log_autovacuum_min_duration=0
Log_autovacuum_min_duration=0

Re: Pgpool with high availability

[Adrian Klaver]

On 5/28/24 1:31 AM, vijay patil wrote:


HI Team,

"I'm encountering challenges while configuring Pgpool with high 
availability. The initial setup is completed, and Pgpool is operational 
on a single node, functioning without issues. However, upon attempting 
to start Pgpool on any additional nodes, particularly node 2, it becomes 
immediately unreachable.


And how we are supposed to arrive at an answer with essentially no 
information provided?


Need:

1) Configuration for initial setup.

2) A more detailed explanation of what "... upon attempting
to start Pgpool on any additional nodes" means? Include configuration 
changes.


3) The error messages.

4) Where the nodes are located?




I'm seeking assistance to address this issue. My setup consists of three 
nodes, each hosting both PostgreSQL and Pgpool services."



Thanks

Vijay



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

Re: Memory issues with PostgreSQL 15

[Francisco Olarte]

Hi Christian:

On Tue, 28 May 2024 at 18:40, Christian Schröder
 wrote:

> <2024-05-21 11:34:46 CEST - mailprocessor> ERROR:  could not resize shared 
> memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on 
> device

This hints at some shm function getting an ENOSPC: Coupled with...

> I thought this could all be related to our "shared_buffers" setting, so I 
> increased it to 8 GB. This almost immediately (after a few minutes) gave me 
> these errors:

A faster fail when increasing it I would start by checking your IPC
shared memory limits are ok, especially if you upgraded something in
the OS when going from 9 to 15, which seems likely.

IIRC in linux you can read them in /proc/sys/kernel/shm*, and they
were configured via sysctl.

Francisco Olarte.

Re: Pgpool with high availability

[Kashif Zeeshan]

Hi Vijay

Can you please share the error log, as from the description its hard to
figure out the issue causing this problem.

Regards
Kashif Zeeshan
Bitnine Global

On Tue, May 28, 2024 at 9:42 PM vijay patil 
wrote:

>
> HI Team,
>
> "I'm encountering challenges while configuring Pgpool with high
> availability. The initial setup is completed, and Pgpool is operational on
> a single node, functioning without issues. However, upon attempting to
> start Pgpool on any additional nodes, particularly node 2, it becomes
> immediately unreachable.
>
> I'm seeking assistance to address this issue. My setup consists of three
> nodes, each hosting both PostgreSQL and Pgpool services."
>
>
> Thanks
>
> Vijay
>

Pgpool with high availability

[vijay patil]

HI Team,

"I'm encountering challenges while configuring Pgpool with high
availability. The initial setup is completed, and Pgpool is operational on
a single node, functioning without issues. However, upon attempting to
start Pgpool on any additional nodes, particularly node 2, it becomes
immediately unreachable.

I'm seeking assistance to address this issue. My setup consists of three
nodes, each hosting both PostgreSQL and Pgpool services."


Thanks

Vijay

Memory issues with PostgreSQL 15

[Christian Schröder]

Hi all,
We migrated from PostgreSQL 9.4 to PostgreSQL 15 a while ago. Since then, we 
have a lot of memory issues in our QA environment (which is a bit tense in 
resources). We did not have these problems before the migration, and we do not 
have them in our production environment, which has a lot more memory. So, it is 
not super critical for us, but I would still like to understand better how we 
can improve our configuration.

Our PostgreSQL version is "PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by 
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit". The database server is a 
dedicated server with 15 GB RAM (and 4 cores, if this matters).
We used the following settings:
shared_buffers = 4GB
work_mem = 4MB

After a while, we saw the following error in the logs:

<2024-05-20 12:01:03 CEST - > LOG:  could not fork autovacuum worker process: 
Cannot allocate memory

However, according to "free", a lot of memory was available:

# free -m
  totalusedfree  shared  buff/cache   available
Mem:  158824992 4634195   104276365
Swap:  1999 2711728

Our Grafana charts showed a slow increase in memory consumption until it 
plateaus at 4.66 GB.
We also found the following error:

<2024-05-21 11:34:46 CEST - mailprocessor> ERROR:  could not resize shared 
memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on device

I thought this could all be related to our "shared_buffers" setting, so I 
increased it to 8 GB. This almost immediately (after a few minutes) gave me 
these errors:

<2024-05-27 11:45:59 CEST - > ERROR:  out of memory
<2024-05-27 11:45:59 CEST - > DETAIL:  Failed on request of size 201088574 in 
memory context "TopTransactionContext".
...
<2024-05-27 11:58:02 CEST - > ERROR:  out of memory
<2024-05-27 11:58:02 CEST - > DETAIL:  Failed while creating memory context 
"dynahash".
<2024-05-27 11:58:02 CEST - > LOG:  background worker "parallel worker" (PID 
21480) exited with exit code 1
...
<2024-05-27 12:01:02 CEST - > LOG:  could not fork new process for connection: 
Cannot allocate memory
<2024-05-27 12:01:03 CEST - > LOG:  could not fork autovacuum worker process: 
Cannot allocate memory
<2024-05-27 12:02:02 CEST - > LOG:  could not fork new process for connection: 
Cannot allocate memory

Since this seemed worse than before, I changed the setting back to 4 GB. I 
noticed that "free" now reports even more available memory:

# free -m
  totalusedfree  shared  buff/cache   available
Mem:  15882 621 3202256   14940   12674
Swap:  1999 1991800

So, does the "shared_buffers" setting have the opposite effect than I though? 
If I correctly remember similar discussions years ago, the database needs both 
"normal" and shared memory. By increasing the "shared_buffers" to 8 GB, I may 
have deprived it of "normal" memory. On the other hand, I would have expected 
the remaining 7 GB to still be enough.

At this point, I am out of ideas. I clearly seem to misunderstand how the 
database manages its memory. This may have changed between 9.4 and 15, so my 
prior knowledge may be useless. I definitely need some help. ☹

Thanks in advance,
Christian


--
SUPPORT:
For any issues, inquiries, or assistance, please contact our support team at 
supp...@wsd.com. Our dedicated team is available to help you and provide prompt 
assistance.

CONFIDENTIALITY NOTICE:
This email and any attachments are confidential and intended solely for the use 
of the individual or entity to whom it is addressed. If you have received this 
email in error, please notify the sender immediately and delete it from your 
system.

Re: Use of inefficient index in the presence of dead tuples

[David G. Johnston]

On Tue, May 28, 2024, 07:21 Alexander Staubo  wrote:

>
>
> I did explore a solution which is my “plan B” — adding a “done” column,
> then using “UPDATE … SET done = true” rather than deleting the rows. This
> causes dead tuples, of course, but then adding a new index with a “… WHERE
> NOT done” filter fixes the problem by forcing the query to use the right
> index. However, with this solution, rows will still have to be deleted
> *sometime*, so this just delays the problem. But it would allow a “batch
> cleanup”: “DELETE … WHERE done; VACUUM” in one fell swoop.
>

If you incorporate partitions into this, the final removal of the soft
deleted rows becomes and truncate or a drop instead of a delete.

David J.

Re: Use of inefficient index in the presence of dead tuples

[Alexander Staubo]

On 28 May 2024, at 13:02, Laurenz Albe  wrote:
> ANALYZE considers only the live rows, so PostgreSQL knows that the query will
> return only few results.  So it chooses the smaller index rather than the one
> that matches the WHERE condition perfectly.
> 
> Unfortunately, it has to wade through all the deleted rows, which is slow.

Sounds like the planner _should_ take the dead tuples into account. I’m 
surprised there are no parameters to tweak to make the planner understand that 
one index is more selective even though it is technically larger.

> But try to execute the query a second time, and it will be much faster.
> PostgreSQL marks the index entries as "dead" during the first execution, so 
> the
> second execution won't have to look at the heap any more.

Of course. It’s still not _free_; it’s still trawling through many megabytes of 
dead data, and going through the shared buffer cache and therefore competing 
with other queries that need shared buffers. 

> I understand your pain, but your use case is somewhat unusual.

I don’t think rapidly updated tables is an unusual use of Postgres, nor is the 
problem of long-running transaction preventing dead tuple vacuuming.

> What I would consider in your place is
> a) running an explicit VACUUM after you delete lots of rows or

The rows are deleted individually. It’s just that there are many transactions 
doing it concurrently.

> b) using partitioning to get rid of old data

Partitioning will generate dead tuples in the original partition when tuples 
are moved to the other partition, so I’m not sure how that would help?

I did explore a solution which is my “plan B” — adding a “done” column, then 
using “UPDATE … SET done = true” rather than deleting the rows. This causes 
dead tuples, of course, but then adding a new index with a “… WHERE NOT done” 
filter fixes the problem by forcing the query to use the right index. However, 
with this solution, rows will still have to be deleted *sometime*, so this just 
delays the problem. But it would allow a “batch cleanup”: “DELETE … WHERE done; 
VACUUM” in one fell swoop.

Re: Use of inefficient index in the presence of dead tuples

[Laurenz Albe]

On Tue, 2024-05-28 at 10:00 +0200, Alexander Staubo wrote:
> I am encountering an odd problem where Postgres will use the wrong index, 
> particularly if the table
> has some dead tuples. The database affected is running 12.6, but I can also 
> reproduce with 16.3.
> 
> To reproduce:
> [create a table with a larger index on "id" and "receiver" and a smaller on
>  "receiver" and "created_at", then delete all but one row and ANALYZE]
> 
> (7) Try the following query:
> 
> EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS, TIMING, SETTINGS, SUMMARY)
> SELECT id FROM outbox_batches
> WHERE receiver = 'dummy'
> AND id = 'test';
> 
> Here's the query plan:
> 
> Index Scan using outbox_batches_on_receiver_and_created_at on 
> public.outbox_batches  (cost=0.38..8.39 rows=1 width=5) (actual 
> time=0.426..984.038 rows=1 loops=1)
> Output: id
> Index Cond: (outbox_batches.receiver = 'dummy'::text)
> Filter: (outbox_batches.id = 'test'::text)
> Buffers: shared hit=3948 read=60742 dirtied=60741 written=30209
> Settings: work_mem = '32MB'
> Query Identifier: -2232653838283363139
> Planning:
> Buffers: shared hit=18 read=3
> Planning Time: 1.599 ms
> Execution Time: 984.082 ms
> 
> This query is reading 60K buffers even though it only needs to read a single 
> row. Notice in particular the
> use of the index outbox_batches_on_receiver_and_created_at, even though 
> outbox_batches_pkey would be
> a much better choice. We know this because if we drop the first index:
> 
> Index Only Scan using outbox_batches_pkey on public.outbox_batches  
> (cost=0.50..8.52 rows=1 width=5) (actual time=2.067..2.070 rows=1 loops=1)
> Output: id
> Index Cond: ((outbox_batches.receiver = 'dummy'::text) AND 
> (outbox_batches.id = 'test'::text))
> Heap Fetches: 1
> Buffers: shared hit=1 read=4
> Settings: work_mem = '32MB'
> Query Identifier: -2232653838283363139
> Planning:
> Buffers: shared hit=5 dirtied=1
> Planning Time: 0.354 ms
> Execution Time: 2.115 ms
> 
> This is also the index that's used in the normal case when there are no dead 
> tuples at all.
> 
> Interestingly, the cost of an index only scan on outbox_batches_pkey is 8.52, 
> whereas the other is
> 8.39. Is this because it considers the number of index pages? I've tried 
> adjusting the various cost
> and memory settings, but they have no effect.

ANALYZE considers only the live rows, so PostgreSQL knows that the query will
return only few results.  So it chooses the smaller index rather than the one
that matches the WHERE condition perfectly.

Unfortunately, it has to wade through all the deleted rows, which is slow.

But try to execute the query a second time, and it will be much faster.
PostgreSQL marks the index entries as "dead" during the first execution, so the
second execution won't have to look at the heap any more.

See https://www.cybertec-postgresql.com/en/killed-index-tuples/

> In this test, we created 5M dead tuples. However, for me it also reproduces 
> with just 1,000 rows.
> For such a small table, the performance degradation is minimal, but it 
> increases as more and more
> tuples are deleted.
> 
> In a production environment, we have rows being constantly deleted at a high 
> rate, leaving a table
> that often has very few live tuples, and often 500K+ dead tuples before 
> autovacuum can kick in. Here
> I am consistently seeing the wrong index used, leading to poor performance.
> 
> The autovacuum settings ar aggressive, but for whatever reason it is not 
> keeping up. We also have
> long-running transactions that sometimes cause the xmin to hang back for a 
> while, preventing
> vacuums from helping.
> 
> All of that said, I would rather Postgres choose the right index than spend a 
> lot of time optimizing
> vacuums.

I understand your pain, but your use case is somewhat unusual.

What I would consider in your place is
a) running an explicit VACUUM after you delete lots of rows or
b) using partitioning to get rid of old data

I don't know how the PostgreSQL optimizer could be improved to take dead rows 
into account.

Yours,
Laurenz Albe

Use of inefficient index in the presence of dead tuples

[Alexander Staubo]

I am encountering an odd problem where Postgres will use the wrong index, 
particularly if the table
has some dead tuples. The database affected is running 12.6, but I can also 
reproduce with 16.3.

To reproduce:

(1) Disable autovacuum. This is just so we can induce a scenario where there 
are lots of dead tuples.

(2) Set up schema. It's important to create the index before insertion, in 
order to provoke a
situation where the indexes have dead tuples:

CREATE TABLE outbox_batches (
id textNOT NULL,
receiver   textNOT NULL,
created_at timestamp without time zone DEFAULT now() NOT NULL,
PRIMARY KEY (receiver, id)
);
CREATE INDEX outbox_batches_on_receiver_and_created_at
ON outbox_batches (receiver, created_at DESC);

(3) Insert 5M rows of dummy data. Note that we are using UUIDs here for the 
purposes of testing; in
my real database, I use much shorter unique IDs.

INSERT INTO outbox_batches (receiver, id)
SELECT 'dummy', uuid_generate_v4()
FROM (SELECT * FROM generate_series(1, 500, 1)) AS foo;

(4) Then ensure all tuples are dead except one:

DELETE FROM outbox_batches;
INSERT INTO outbox_batches (receiver, id) VALUES ('dummy', 'test');

(5) Analyze:

ANALYZE outbox_batches;

(6) You should now have 5m dead rows and 1 live row:

SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 
'outbox_batches';
┌┬┐
│ n_live_tup │ n_dead_tup │
├┼┤
│  1 │500 │
└┴┘

We also observe that the outbox_batches_pkey index is 454 MB, and the
outbox_batches_on_receiver_and_created_at is 31 MB.

(7) Try the following query:

EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS, TIMING, SETTINGS, SUMMARY)
SELECT id FROM outbox_batches
WHERE receiver = 'dummy'
AND id = 'test';

Here's the query plan:

Index Scan using outbox_batches_on_receiver_and_created_at on 
public.outbox_batches  (cost=0.38..8.39 rows=1 width=5) (actual 
time=0.426..984.038 rows=1 loops=1)
Output: id
Index Cond: (outbox_batches.receiver = 'dummy'::text)
Filter: (outbox_batches.id = 'test'::text)
Buffers: shared hit=3948 read=60742 dirtied=60741 written=30209
Settings: work_mem = '32MB'
Query Identifier: -2232653838283363139
Planning:
Buffers: shared hit=18 read=3
Planning Time: 1.599 ms
Execution Time: 984.082 ms

This query is reading 60K buffers even though it only needs to read a single 
row. Notice in particular the
use of the index outbox_batches_on_receiver_and_created_at, even though 
outbox_batches_pkey would be
a much better choice. We know this because if we drop the first index:

Index Only Scan using outbox_batches_pkey on public.outbox_batches  
(cost=0.50..8.52 rows=1 width=5) (actual time=2.067..2.070 rows=1 loops=1)
Output: id
Index Cond: ((outbox_batches.receiver = 'dummy'::text) AND 
(outbox_batches.id = 'test'::text))
Heap Fetches: 1
Buffers: shared hit=1 read=4
Settings: work_mem = '32MB'
Query Identifier: -2232653838283363139
Planning:
Buffers: shared hit=5 dirtied=1
Planning Time: 0.354 ms
Execution Time: 2.115 ms

This is also the index that's used in the normal case when there are no dead 
tuples at all.

Interestingly, the cost of an index only scan on outbox_batches_pkey is 8.52, 
whereas the other is
8.39. Is this because it considers the number of index pages? I've tried 
adjusting the various cost
and memory settings, but they have no effect.

In this test, we created 5M dead tuples. However, for me it also reproduces 
with just 1,000 rows.
For such a small table, the performance degradation is minimal, but it 
increases as more and more
tuples are deleted.

In a production environment, we have rows being constantly deleted at a high 
rate, leaving a table
that often has very few live tuples, and often 500K+ dead tuples before 
autovacuum can kick in. Here
I am consistently seeing the wrong index used, leading to poor performance.

The autovacuum settings ar aggressive, but for whatever reason it is not 
keeping up. We also have
long-running transactions that sometimes cause the xmin to hang back for a 
while, preventing
vacuums from helping.

All of that said, I would rather Postgres choose the right index than spend a 
lot of time optimizing
vacuums.

Here's my full server config: 
https://gist.github.com/atombender/54207d473e415fab26fc59751a22feca.

Re: prevent users from SELECT-ing from pg_roles/pg_database

[Andreas Joseph Krogh]

På tirsdag 28. mai 2024 kl. 01:48:17, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>:
Laurenz Albe  writes:
> On Mon, 2024-05-27 at 09:33 +0200, Andreas Joseph Krogh wrote:
>> I tried:
>> REVOKE SELECT ON pg_catalog.pg_database FROM public;
>> But that doesn't prevent a normal user from querying pg_database it seems…

> It works here.

Works for me too, although you'd have to do it over in each
database where you want it to be effective. (Although
pg_database is a shared catalog, the metadata about it
is not shared.)

regards, tom lane


Ah, that's what I was missing. Thanks for pointing that out, it's working as 
expected now.






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com