RE: Performance degradation after upgrading from 9.5 to 14

Same issue and took us ages to work out that is was JIT! The default is on, and setting off solves the problem. I have seen several blogs reporting the same and so wonder why this default is on? Z From: Олександр Янін Sent: Monday, April 22, 2024 8:01 PM To: Johnathan Tiamoh Cc: pgsql-general

RE: Postgres database encryption

>On Tue, Mar 19, 2024 at 9:22 AM Johnathan Tiamoh > wrote: >Hello,  > > >Good morning. Please I wish to find out the best way of encrypting a Postgres >database.  > >This could either be encrypting a whole database ( I don’t if this is >possible), a specific ta

RE: Mat Views and Conflicts

-From: David G. Johnston -Sent: Tuesday, February 20, 2024 1:20 PM -To: Zahir Lalani -Cc: Adrian Klaver ; pgsql-general@lists.postgresql.org -Subject: Re: Mat Views and Conflicts - - -On Tuesday, February 20, 2024, Zahir Lalani <mailto:ZahirLalani@oliver.agency> wrote: - -This suggest

RE: Mat Views and Conflicts

> -Original Message- > From: Adrian Klaver > Sent: Monday, February 19, 2024 7:36 PM > To: Zahir Lalani ; pgsql- > gene...@lists.postgresql.org > Subject: Re: Mat Views and Conflicts > > On 2/19/24 09:52, Zahir Lalani wrote: > > Hi All > > > > My

Mat Views and Conflicts

Hi All My understanding and hope was that Mat Views cache their data and that is how they are so fast. But we are experience "cancelling statement due to conflict with recovery" errors on MV's with large data sets and I thought that this could only happen if we ran the underlying query, not the

RE: Odd Shortcut behaviour in PG14

> -Original Message- > From: Tom Lane > Sent: Friday, November 24, 2023 6:44 PM > To: Zahir Lalani > Cc: Ron Johnson ; pgsql- > generallists.postgresql.org > Subject: Re: Odd Shortcut behaviour in PG14 > > OK, so if this is a plpgsql function and ekey

RE: Odd Shortcut behaviour in PG14

> -Original Message- > From: Tom Lane > Sent: Friday, November 24, 2023 3:35 PM > To: Zahir Lalani > Cc: Ron Johnson ; pgsql- > generallists.postgresql.org > Subject: Re: Odd Shortcut behaviour in PG14 > You *still* haven't defined what you mean b

RE: Odd Shortcut behaviour in PG14

> -Original Message- > From: Tom Lane > Sent: Thursday, November 23, 2023 7:45 PM > To: Ron Johnson > Cc: pgsql-generallists.postgresql.org > Subject: Re: Odd Shortcut behaviour in PG14 > > Ron Johnson writes: > > Out of curiosity, what is the point of adding the "true" predicate no

Odd Shortcut behaviour in PG14

) enc ON (ekey > 0) and true This should theoretically be no different - but it solves the issue 100%. Any guidance on why this would be the case? Zahir Lalani Director of Enterprise Systems [signature_2000159722] 151 Rosebery Avenue London EC1R 4AB t: +44 (0)7956 455 168 e: zahirlalani@

RE: regex failing

> -Original Message- > > > > Hi All > > > > > > > > Got a weird one. I am using the regex below to case match numeric only > values. > > > > > > > > '^([0-9]+[.]?[0-9]*)$' > > > > > > > > This works well by and large but not for a value like “1234:567”. This seems > to match positive and th

regex failing

Hi All Got a weird one. I am using the regex below to case match numeric only values. '^([0-9]+[.]?[0-9]*)$' This works well by and large but not for a value like “1234:567”. This seems to match positive and then fails due to not being an actual number in the subsequent cast. Any ideas? (PG13

From Clause Conditional

Confidential Hello All We are testing a upgrade from pg11 to pg14 and have some issues to overcome. One of these is that we have upgraded pgsodium to the latest and there is a functional change - this question is not about sodium BTW. So here is a sample bit of code that I will use to explain

RE: Best Strategy for Large Number of Images

Confidential We have a large B2B application used in marketing management – heavily focused on images/video – no binaries ever stored in DB – we have all the metadata in the DB and binaries in scalable folder structure. Serving up images from the DB is not very scalable. I am not sure what you

RE: Multi-master replication

route for write heavy applications. Z > -Original Message- > From: Laura Smith > Sent: 13 August 2021 10:47 > To: Zahir Lalani > Cc: pgsql-general@lists.postgresql.org > Subject: Re: Multi-master replication > > ‐‐‐ Original Message ‐‐‐ > > On Friday

Multi-master replication

Confidential Hello all Does anyone on the list have production experience of using PG in a multi-master setup - if so using which tools? Thx Zahir

RE: Framework for 0 downtime deploys

Confidential Thank you Nik That’s very useful and will be checking out the migration test tool! Z From: Nikolay Samokhvalov Sent: 22 May 2021 13:32 To: Zahir Lalani Cc: pgsql-general@lists.postgresql.org Subject: Re: Framework for 0 downtime deploys No silver bullets exist (yet). A couple

Framework for 0 downtime deploys

Confidential Hello All I wonder if I could garner some of the wealth of experience on this group: Our current application deployments (every 3 weeks) require about 30min downtime. We are now tasked of making this 0 downtime. >From all the reading I have done, we have solutions for the infrastru

RE: Window functions speed

Confidential From: Michael Lewis Sent: 19 August 2020 20:53 To: Zahir Lalani Cc: pgsql-gene...@postgresql.org Subject: Re: Window functions speed Where's the query? Are you able to run explain analyze so we can see estimates vs actual counts? What version are you using? Can you share ex

Window functions speed

Confidential Hello I have spent the last day optimising a critical query which suddenly started behaving very inefficiently. There were issues with the query which are now sorted. The base query is now working in a timeframe that is far better. However, as soon as I add a window count function

RE: Application Level Encryption

From: Michel Pelletier Sent: 05 July 2020 23:32 To: Sam Gendler Cc: Zahir Lalani ; pgsql-gene...@postgresql.org Subject: Re: Application Level Encryption On Sun, Jul 5, 2020 at 3:23 PM Sam Gendler mailto:sgend...@ideasculptor.com>> wrote: On Sun, Jul 5, 2020 at 11:41 AM Michel Pel

RE: Application Level Encryption

From: Michel Pelletier Sent: 05 July 2020 17:00 To: Zahir Lalani Cc: pgsql-gene...@postgresql.org Subject: Re: Application Level Encryption Hi Zahir, pgsodium is a new-ish encyption extension built around the libsodium encryption API. https://github.com/michelp/pgsodium It supports

Application Level Encryption

Hello Does anyone have any serious experience of implementing app level encryption with strong key management? If so would like to arrange an offline chat please Z

RE: Should I use JSON?

We make lots of use JSON – but in specific contexts. If we need to pull data out for listing view – always raw fields. If these are detail view only and we need dynamic content depending on record types, JSON is a life saver Z From: pabloa98 Sent: 21 May 2020 20:28 Cc: pgsql-gene...@postgresq

RE: Need a DB layout gui

Navicat - its costs money but always found it very useful for managing various DBs Z -Original Message- From: Tim Clarke Sent: 24 June 2019 23:56 To: pgsql-general@lists.postgresql.org Subject: Re: Need a DB layout gui I've been enjoying dbeaver for this

Re: PG10 upgrade issue

Ah no we did not! Will give that a go next tim Tom Thank you Z On 3 Jun 2019 00:18, Tom Lane wrote: Zahir Lalani writes: > We have done pg10 upgrades from 9.6 on all our environments successfully. We > have an automation script that does this for us. One environment is a near > r

PG10 upgrade issue

Hello We have done pg10 upgrades from 9.6 on all our environments successfully. We have an automation script that does this for us. One environment is a near replica of our production setup and all works fine. We have now made 2 attempts at doing this on production - both ending up with rollba

RE: Possible corrupt index?

>Any changes occur between the time it worked and the time it did not? >Say a crash, change in schema, new data added and so on. Not as far as we can tell - one of my DB team noticed it day before yesterday - there were no tell tale signs of any issues but we have seen issues yesterday with cer

RE: Possible corrupt index?

>I am not following above. >What is running version 10? Production is 9.6, others are 10 (as we are testing). Prod has always been fine, its been running for 2 years without a hitch. This issue has only occurred recently >Did you restore a dump from a version 10 onto a >version 9.6 machine? No

RE: Possible corrupt index?

Hi >Is this the same for the other environments? We have recently upgraded others to 10 and are in testing before we upgrade production. However we still some 9.6 dev servers which are fine. >What does: >SHOW lc_collate; en_US.UTF-8 >Are you doing the below through Navicat or psql? Using Na

RE: Possible corrupt index?

>Which version? What are the queries you are running which give unexpected >behavior? Have your run explain analyze on those to check >what plan is being >used? Have your reindexed all or only the one you suspect? Hi Michael Version: PostgreSQL 9.6.12 on x86_64-pc-linux-gnu, compiled by gcc (G

Possible corrupt index?

Hi All New on this list! We have an existing postgres deployment which is showing some odd behaviour on Live. We use Navicat to manage it, and when we open a specific table via navicat on live, it says we don't have a primary index - although the design view does show it. We have auto deployme