The performance issues caused by upgrading PostgreSQL to version 16.3.

Dear PostgreSQL Community, I hope this message finds you well. I am reaching out to seek your technical assistance regarding a performance issue we encountered after upgrading our PostgreSQL version from 12.19 to 16.3. We have noticed a significant performance problem with a specific SQL query o

Re: Wasteful nested loop join when there is `limit` in the query

WU Yan <4wu...@gmail.com> writes: > Hello everyone, I am still learning postgres planner and performance > optimization, so please kindly point out if I missed something obvious. An index on employee.name would likely help here. Even if we had an optimization for pushing LIMIT down through a join

Wasteful nested loop join when there is `limit` in the query

Hello everyone, I am still learning postgres planner and performance optimization, so please kindly point out if I missed something obvious. I've noticed that postgres joins all rows in two tables, even though there's a `limit` in the query. It means lots of joined rows are discarded eventually, a

Re: Best Approach for Swapping a Table with its Copy

On Sun, Feb 16, 2025 at 5:58 PM Marcelo Fernandes wrote: > - The foreign keys are not being updated to point to the new table. > You started out okay with your test script, but the pg_depend bit needs work. I would recommend examining that table closely until you have a really good understanding

Re: Best Approach for Swapping a Table with its Copy

On Sat, Feb 15, 2025 at 4:12 AM Greg Sabino Mullane wrote: > The pg_repack link posted earlier has the details on how it is done. But > messing with system catalogs like this is highly discouraged, for good > reasons. Still, if you need to go that route, test heavily and post the > solutions he

Re: Question on Alerts

On 2/16/25 12:31, sud wrote: On Sun, Feb 16, 2025 at 10:05 PM Guillaume Lelarge mailto:guillaume.lela...@dalibo.com>> wrote: You should probably look at check_postgres and check_pgactivity. Their source code contain numerous SQL queries, that could help you write your own.

Re: Question on Alerts

> On Feb 16, 2025, at 12:31, sud wrote: > where exactly I can get the source code for check_postgres and > check_pgactivity? https://github.com/bucardo/check_postgres https://github.com/OPMDG/check_pgactivity While the list is happy to help, I should note that I found these by searching fo

Re: Question on Alerts

On Sun, Feb 16, 2025 at 10:05 PM Guillaume Lelarge < guillaume.lela...@dalibo.com> wrote: > > You should probably look at check_postgres and check_pgactivity. Their > source code contain numerous SQL queries, that could help you write your > own. > > Regards. > > Thank you very much. I am a bit ne

Re: Help in vetting outcome of "vacuumdb --analyze-in-stages" - during DB Upgrade from EC2- PGS - Community Edn ver 13.X to 14.X

On 2/16/25 09:43, Bharani SV-forum wrote: Adrian Post pg_upgrade command (success ) from DB ver 13.X to 14.X, I had used following command vacuumdb --all --analyze-in-stages You are done then, move on. Need your best suggestion to avoid any unknown headache Keep on moving forward. 'unknow

Re: Help in vetting outcome of "vacuumdb --analyze-in-stages" - during DB Upgrade from EC2- PGS - Community Edn ver 13.X to 14.X

On 2/16/25 08:27, Y_Bharani_mbsv wrote: Adrian TQ for the instant reply. post DB migration to Ver 14.X (successfully) and Post executing the "vacuumdb --analyze-in-stages", i noticed "read me option" and the caveat on it. Did you do: vacuumdb --analyze-in-stages or vacuumdb --all --analyze

Re: Question on Alerts

Hi, On 16/02/2025 14:29, sud wrote: Hi, We are asked to have key monitoring or alerting added to our postgres database. And I am thinking of metrics like blocked transactions, Max used transaction Ids,  Max Active session threshold, Deadlock, Long running query,  replica lag, buffer cache hit

Re: Question on Alerts

On 2/16/25 05:29, sud wrote: Hi, We are asked to have key monitoring or alerting added to our postgres database. And I am thinking of metrics like blocked transactions, Max used transaction Ids,  Max Active session threshold, Deadlock, Long running query,  replica lag, buffer cache hit ratio,

Re: Help in vetting outcome of "vacuumdb --analyze-in-stages" - during DB Upgrade from EC2- PGS - Community Edn ver 13.X to 14.X

On 2/16/25 05:13, Y_Bharani_mbsv wrote: Team Good Morning. As part of DB upgrade from EC2 - PGS - community Edn Ver 13.X to 14.X I followed steps of "pg_upgrade" and had executed the last step (post successful db migration) vacuumdb --analyze-in-stages and later noticed an caveat url = https:

Re: Help in vetting outcome of "vacuumdb --analyze-in-stages" - during DB Upgrade from EC2- PGS - Community Edn ver 13.X to 14.X

On Sun, Feb 16, 2025 at 8:13 AM Y_Bharani_mbsv wrote: > Team > Good Morning. > As part of DB upgrade from EC2 - PGS - community Edn Ver 13.X to 14.X > I followed steps of "pg_upgrade" and had executed the last step (post > successful db migration) > > vacuumdb --analyze-in-stages > > and later no

Question on Alerts

Hi, We are asked to have key monitoring or alerting added to our postgres database. And I am thinking of metrics like blocked transactions, Max used transaction Ids, Max Active session threshold, Deadlock, Long running query, replica lag, buffer cache hit ratio, read/write IOPS or latency etc. I