Sequential scan faster than index

I'm trying to figure out why a sequential scan is out performing. I've tried psql 13.7, psql14.6 and REINDEX. The REINDEX didn't help. This is on an RDS instance that's a db.m5.large (2 * vCPU, 8 GB memory) with 200 storage on io1 with 1 provisioned IOPS. I restarted the RDS instance in bet

Hi All,

Currently I'm working on upgrading a postgresql version from 9.3 to 12. The database size is around 700G. I finished the pg_upgrade process but when I ran 'analyze_new_cluster.sh' it stucked at the first log 'vacuumdb: processing database "otdb": Generating minimal optimizer statistics (1 target)'.

Need Detailed to build real time CDC Data Pipeline

Hi Team, We have use case to build a real time data pipeline from PostgresSQL to sync data with other DB. We have created logical slots to read WAL logs. We are exploting some markets tools as well like ‘Debezium’, still want to have one option without relying on any other open source tool. We

Please upload patroni 3.0.1 package to postgresql repo

Hello guys, patroni 3.0.1 fixed some bugs of 3.0.0, could you please upload patroni 3.0.1 package to postgresql repo? https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-7.9-x86_64/ Thank you! Best regards Dennis

Re: Sequential scan faster than index

On Tue, Feb 21, 2023 at 10:30 AM Arthur Ramsey wrote: > > I'm trying to figure out why a sequential scan is out performing. I've tried > psql 13.7, psql14.6 and REINDEX. The REINDEX didn't help. This is on an RDS > instance that's a db.m5.large (2 * vCPU, 8 GB memory) with 200 storage on io1

Re: Sequential scan faster than index

On Tue, 21 Feb 2023 at 22:30, Arthur Ramsey wrote: > > I'm trying to figure out why a sequential scan is out performing. I've tried > psql 13.7, psql14.6 and REINDEX. The REINDEX didn't help. This is on an RDS > instance that's a db.m5.large (2 * vCPU, 8 GB memory) with 200 storage on io1 >

Aw: Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

> >> b...@yugabyte.com wrote: > >> > >> I’ve no idea how I might have found this without human help. > > > > x...@thebuild.com wrote: > > > > That sounds like an excellent documentation patch! > > Well, it’s already documented clearly enough. The question is how to find > it—especially if you

[no subject]

Good morning to all. I'd need some advice. I'm trying to install PgAdmin4 from its repository, on Debian 11, by the instructions in the concerning page in www.postgresql.org. The installation fails, as the packages cannot be verified. What shoud I do? I tried using deb [trusted=yes], but under

Re:

Hi Alberto, AFAIK, it's described here: (not using postgresql https://www.pgadmin.org/download/pgadmin-4-apt/ Follow the instructions in the gray box below the package list. Carsten Am 21.02.2023 um 13:44 schrieb Alberto García Fumero: Good morning to all. I'd need some advice. I'm tryin

Re: can't get psql authentication against Active Directory working

Hi Stephen, first: thanks a lot for replying! On 20.02.23 15:17, Stephen Frost wrote: * Tomas Pospisek (t...@sourcepole.ch) wrote: so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via Active Directory. Looks like you're trying to do a bit more than that as you're u

Re: Hi All,

Ryan MYJ writes: > Currently I'm working on upgrading a postgresql version from 9.3 to 12. The > database size is around 700G. > I finished the pg_upgrade process but when I ran 'analyze_new_cluster.sh' > it stucked at the first log 'vacuumdb: processing database "otdb": > Generating minimal optim

Re: can't get psql authentication against Active Directory working

Greetings, * Tomas Pospisek (t...@sourcepole.ch) wrote: > On 20.02.23 15:17, Stephen Frost wrote: > > * Tomas Pospisek (t...@sourcepole.ch) wrote: > > > so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via > > > Active Directory. > > > > Looks like you're trying to do a bit

Re: Is Autovacuum running?

On Mon, Feb 20, 2023 at 1:42 PM Brad White wrote: > I'm concerned that Autovacuum may not be running based on the results of > this query. > > SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; > gives 211 rows like this... > *relname| last_vacuum | last_au

Re: Is Autovacuum running?

> On Feb 21, 2023, at 09:54, Brad White wrote: > Any suggestions on how to proceed? First, look at pg_stat_user_tables to see how many inserts etc. have occurred on the tables that are not showing an autovacuum; they may have simply not reached the threshold yet. If they have, do a VACUUM V

Re: Is Autovacuum running?

On Tue, Feb 21, 2023 at 11:58 AM Christophe Pettus wrote: > > > > On Feb 21, 2023, at 09:54, Brad White wrote: > > Any suggestions on how to proceed? > > First, look at pg_stat_user_tables to see how many inserts etc. have > occurred on the tables that are not showing an autovacuum; they may hav

transaction_isolation vs. default_transaction_isolation

I found a discussion with the same title as this emails’s subject here: https://postgrespro.com/list/thread-id/1741835 It dates from 2009. But it seems to be unresolved. The current PG doc here: 20.11. Client Connection Defaults https://www.postgresql.org/docs/15/runtime-config-client.html has

Re: Is Autovacuum running?

> On Feb 21, 2023, at 10:48, Brad White wrote: > > Running the table_bloat_check query from here > https://github.com/pgexperts/pgx_scripts/blob/master/bloat/table_bloat_check.sql > > shows some tables with over 20MB and over 20% bloat while my threshold is set > to 0.1. Apples-to-oranges

Re: transaction_isolation vs. default_transaction_isolation

On Tue, Feb 21, 2023 at 12:32 PM Bryn Llewellyn wrote: > I found a discussion with the same title as this emails’s subject here: > > https://postgrespro.com/list/thread-id/1741835 > > It dates from 2009. But it seems to be unresolved. The current PG doc here: > > 20.11. Client Connection Defaults

Re: Is Autovacuum running?

On Tue, 21 Feb 2023 at 08:42, Brad White wrote: > > I'm concerned that Autovacuum may not be running based on the results of this > query. > > SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; > gives 211 rows like this... > relname| last_vacuum | last_aut

Re: Is Autovacuum running?

On Tue, Feb 21, 2023 at 2:56 PM David Rowley wrote: > On Tue, 21 Feb 2023 at 08:42, Brad White wrote: > > > > I'm concerned that Autovacuum may not be running based on the results of > this query. > > > > SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; > > gives 211 rows l

Debugging postgres on Windows - could not open directory "/lib"

To whom it may concern, I'm trying to build and debug postgresql on Windows. After building successfully, a file named pgsql.sln was generated. I opened it in Visual Studio 2022, and set 'postgres' as the single startup project. But when I started debugging it, the error came out: 'FATAL:

Re: Debugging postgres on Windows - could not open directory "/lib"

On 2/21/23 14:12, Cathy Xie wrote: To whom it may concern, I'm trying to build and debug postgresql on Windows.  After building successfully, a file named pgsql.sln was generated.  I opened it in Visual Studio 2022, and set 'postgres' as the single startup project. But when I started debugg

Re: Is Autovacuum running?

On Wed, 22 Feb 2023 at 11:28, Brad White wrote: > datname | stats_reset| now > DB | 2023-02-17 14:28:27-06 | 2023-02-21 16:16:34-06 > > I heard that the system was running slowly on Friday. They may have cycled > the service in an attempt to resolve that. You'll probably want to

Re: transaction_isolation vs. default_transaction_isolation

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> I found a discussion with the same title as this emails’s subject here: >> >> https://postgrespro.com/list/thread-id/1741835 >> >> It dates from 2009. But it seems to be unresolved. The current PG doc here: >> >> 20.11. Cl

RLS without leakproof restrictions?

Hi all I'm currently researching different strategies for retrofitting some multi-tenant functionality into our existing Postgres-backed application. One of the options is using RLS policies to do row filtering. This is quite attractive as I dread the maintenance and auditing burden of adding filt

Re: RLS without leakproof restrictions?

>> On Tue, Feb 21, 2023 at 5:57 PM Tom Dunstan wrote: > Hi all > > I'm currently researching different strategies for retrofitting some > multi-tenant functionality into our existing Postgres-backed application. >> > One of the options is using RLS policies to do row filtering. This is > quite at

Re: RLS without leakproof restrictions?

Hi Martin On Wed, 22 Feb 2023 at 13:12, Martin L. Buchanan wrote: > > Anyway, Tom if it is feasible to put each tenant into its own database on > the same server instance, that is what I recommend. > It is not, unfortunately. For brevity I skipped over some details - the "tenants" are possibly

Re: RLS without leakproof restrictions?

Tom Dunstan writes: > I'm currently researching different strategies for retrofitting some > multi-tenant functionality into our existing Postgres-backed application. > One of the options is using RLS policies to do row filtering. This is quite > attractive as I dread the maintenance and auditing

Re: RLS without leakproof restrictions?

Hi Tom! On Wed, 22 Feb 2023 at 14:16, Tom Lane wrote: > If you're happy allowing the application to decide if the filters will > be enforced, maybe just create some views embodying those filters, and > query those views when you want restrictions? > Yeah, thanks very much for the suggestion. It