Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uw8to-006VCS-FO for pgsql-general@arkaria.postgresql.org; Wed, 10 Sep 2025 00:41:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uw8tn-00Flp1-5E for pgsql-general@arkaria.postgresql.org; Wed, 10 Sep 2025 00:41:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uw8tm-00Flot-IZ for pgsql-general@lists.postgresql.org; Wed, 10 Sep 2025 00:41:23 +0000 Received: from mail-ed1-x529.google.com ([2a00:1450:4864:20::529]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uw8tk-001ZDA-2T for pgsql-general@postgresql.org; Wed, 10 Sep 2025 00:41:21 +0000 Received: by mail-ed1-x529.google.com with SMTP id 4fb4d7f45d1cf-62733e779bbso3620506a12.1 for ; Tue, 09 Sep 2025 17:41:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1757464874; x=1758069674; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=LL6XPO8q/BpWElTp4J7rt3uqTDw9yTivGZvvINI3U90=; b=Kkw1RVTESpWiLK1vrqZHR4FDRvG6NRTEcbDY7vBaY4RD47stTd93n2KjNkDDZQHsCV 5sU9FLzBZnV8iNgqEZkda+h2TbX6wL/a8USZq6+9/ecnU17FW9uchGr8qBS3Oo3uvfLt ui8v+gn/PliQh6n+NubO39pjQ6Vynh7PFkjlej0PFfhjmYyqG25k9iekziAf4ofwlC7t pGM6T8x4lD8prhMok3SUa87vOCqsznvCD7IlL9vdVEbdi6BvpBqS+RewikzWAsZGH/rQ EAlXiny9qiB2msRCNYdZQTeulbnxO/R4b6JeQkyTvO+LDRIlel1gs7nB7ZMV9Xw328// WglA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1757464874; x=1758069674; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=LL6XPO8q/BpWElTp4J7rt3uqTDw9yTivGZvvINI3U90=; b=tLcnB+sA1vtDs6Hvy2JPlL9qrcVL70FAS67gtnmArW+jX6gYEbrmD3XX++T9xg8GhL Zp1o5a/zom1uY7GgcPluBlcat7SXcDbY968xklRUKBLgzQ5GBhIr0990WkbE2oByGUXc rINwGIyUwOlEiaK1iZ4S/ZKvWhjLuJdAGoEnocWN7c0oA8Xq/kF5jrRwaLsKkMyjv+hR iSDS55zALo8KYmDJYbjKmhMXn3zprZS+On/wSwX3BbXMQACESMdoOUXBpUTQ6sCqqeJ+ rXRs/pptoj004ppZVtWJd197QNOXB/YfEH79fmC4pJ7SGPBxrDiTJNuN7lD6EyuMqBJ7 nC9Q== X-Forwarded-Encrypted: i=1; AJvYcCUd7g+TwKakFsLRH1kbc5TwlFVhdiZV0gymKUdVET9P81DAhdkPUtAiE+oyY6fd/KXgI6KcWd1iV8hsL8zX@postgresql.org X-Gm-Message-State: AOJu0YwZ+a+0F148ykzGFVqMqKezcFwb/zmUf2gLReXe8Jj9p/ucuff1 PTN4oM2nTPpQ6kHKHSgMZ++VB1rZWmP1tlTLkP+TwNsACMLkqVKW/AdePNThftUSwRXaEZd24Ey l/q1POiVsx0xelC4Yz9GuCl/HwZy3YaWVuRVcvpM= X-Gm-Gg: ASbGncsC15PomEsJcvBKDSGuoQaA8p7H/BVEFNLbx1FxVk/pdGeCDQkx7awD9SlHP2c 9IVJwflrHQMXvYeFNdlwXBXxUL1QjWTnSa9WInf1GWTMkCfwJ/bQdDSomT7tXKmgmEROVoJyb/V OO+CFnTVW4hRwJcwkft13vzy7nukHzW5tOJKgRyQMRXHghH6oqwAB6gZj9Gb/A7Tm4FCmxjwJSV ukS1KgSg3/sY/hbsXyY X-Google-Smtp-Source: AGHT+IFKAFJ8RzO2aQ4RjVL7Uah9wmE9ZifK9n54XJXfh+WsJ5uDB0+5LLbVfBMAOGSGpJ39b/omLhuCAIEJblLDcq4= X-Received: by 2002:a05:6402:1d50:b0:61d:1d36:a7d5 with SMTP id 4fb4d7f45d1cf-6237ebc9354mr13068551a12.19.1757464873856; Tue, 09 Sep 2025 17:41:13 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Justin Date: Tue, 9 Sep 2025 20:41:02 -0400 X-Gm-Features: AS18NWBg2CqxDy8FA5wdOtjJrY6JREV5oHgjsn1PTANvS-WuxqzATXyUNDqONaQ Message-ID: Subject: Re: MVCC and all that... To: Merlin Moncure Cc: Ellen Allhatatlan , pgsql-general Content-Type: multipart/alternative; boundary="00000000000082f323063e67ad57" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000082f323063e67ad57 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I read through the article its click bait/flame war just waiting to happen. Article is a list of cherry picked PG drawbacks that can be mitigated or worked around. On the bulk updating. I'm shaking my finger at any one that locks up 25% of a table with an update or delete. That is asking for problems in a production database with a high TPS rate. The author brings up threaded vs multi-process. That's an old old old old old conversation that has been shown there is no clear better way. Number of open connections. so firebird can do 1000 open sessions with a smaller memory footprint, still can not have 1000 simultaneous running sessions unless we have 1000 CPU's. Where is the win here?? We should be managing resources better on the application side, not opening thousands of connections that sit idle doing nothing. On autonomous transactions we have procedures now that allow transactions inside of transactions that can be committed and rollbacked. that has been around for several years now. Backup argument is cherry picking and not discussing pgBackrest and other solutions or the use of tablespaces to isolate databases in a cluster at the disk layer or disk snapshots. "PostgreSQL has a relatively simple, but fast query planning algorithm" Compared to what.... What feature is PG missing these days... the only thing I know it can't do is change the plan in the middle of the execution stage. Which is not a query planner thing but the execution layer saying to itself I am taking too long maybe go back to the planning stage... Query Hints that have been discussed endlessly. Adding hints adds its own problems and has become a big mess for databases that support it. Multiple transactions per connection. I am asking WHY is that a feature. when one can have multiple sessions, what is the difference? running multiple transactions in single or multiple sessions means moving part of transaction logic into the application space. What do we gain here..... No application packaging. This Oracle thing that firebird has duplicated at some level. we can simulate this with namespace/schemas. I can keep going on here. There are litigmate points here Compression, not being able to return partials result sets from functions XID being 32 bit anonymous functions in PG have several limitation not just input arguments (not sure i see the need for that) Temporary tables are a pain and cause issues for big databases The article is unfair in many places.. On Tue, Sep 9, 2025 at 6:55=E2=80=AFPM Merlin Moncure = wrote: > On Tue, Sep 9, 2025 at 11:57=E2=80=AFAM Ellen Allhatatlan < > ellenallhatatlan@gmail.com> wrote: > >> > Note: your link is wrong, corrected here: >> >> Extra hyphen - sorry about and thanks for pointing it out! >> >> > What the article is driving at is that postgres does not use rollback >> logs to handle updated records in the MVCC implementation. There are >> absolutely performance tradeoffs in that decision and, if you do a lot o= f >> development against postgresql, those tradeoffs should influence how you >> design databases. The author then cherry picked the 'worst case' case, >> large unconstrained updates. >> >> Hmm... I was wondering about that - even though he stressed that there >> was (paraphrasing) no right or wrong - just different design >> decisions! >> >> > The article is a bit of a cheezy dig on postgres. Another example is >> the complaint about autonomous transactions with another cherry picked >> example to make postgres look back. In the real world, these would not >> matter much, and can be worked around (if you want to see my take on how= to >> deal with it, see here: https://github.com/leaselock/pgasync). >> >> OK - so, I was wrong in my original assumption that somehow (and it >> wasn't simply because of the phraseology - sweep vs vacuum) I thought >> that PG and FB had a similar MVCC implementation vs. Oracle and MySQL >> (InnoDB) (and OrioleDB). I'll do a deep dive into their docco and see >> what they actually do! I'm actually very interested in the >> benchmarking side of database technology - but I do know the old adage >> - there are lies, damned lies, statistics and *_then_* there are >> database benchmarks (as seen with the link I posted!). >> > > Sure. I think you'll find that postgres approach to MVCC is somewhat > unusual relative to other players in this space, which is to write 'old' > records or changes in a rollback log; if the transaction commits it is > discarded but if it rolls back, the rollback log is written back to the > heap. This makes rollbacks potentially very painful. I suspect the > postgres approach can also do better in cases of highly contended records= , > but that's just a guess. > > For small (one or a small number of records) updates, the approach doesn'= t > make a whole lot of difference especially if you are aware of and exploit > HOT. For very large updates however, it absolutely does, and one might t= ry > to avoid them using various strategies. I very much appreciate fast > rollbacks though. > > merlin > > > > > > merlin > --00000000000082f323063e67ad57 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I read through the article its=C2=A0click bait/flame war j= ust waiting to happen.

Article is a list of cherry picked=C2=A0PG dr= awbacks that can be mitigated or worked around.=C2=A0 =C2=A0

On the= bulk updating.=C2=A0 I'm shaking my finger at any one that locks up 25= % of a table with an update or delete. That is asking for problems in a pro= duction database with a high TPS rate.

The author brings up threaded= vs multi-process. That's an old old old old old conversation that has = been shown there is no clear better way.=C2=A0

Number of= open connections.=C2=A0 so firebird can do 1000=C2=A0 open sessions with a= smaller memory footprint,=C2=A0 still can not have 1000 simultaneous runni= ng sessions unless we have 1000 CPU's. Where is the win here??=C2=A0 We= should be managing resources better on the application=C2=A0side, not open= ing=C2=A0thousands of connections that sit idle doing nothing.=C2=A0=C2=A0<= br>=C2=A0=C2=A0
On=C2=A0autonomous transactions we have procedures now t= hat allow transactions inside of transactions that can be committed and rol= lbacked.=C2=A0 that has been around for several years now.

Backup ar= gument is=C2=A0cherry=C2=A0picking and not discussing pgBackrest and other = solutions=C2=A0 or the use of tablespaces to isolate databases in a cluster= at the disk layer=C2=A0 or disk snapshots.=C2=A0 =C2=A0

"PostgreSQL has a relatively simple, but fast query planning al= gorithm"=C2=A0 Compared to what....=C2=A0 What feature is PG missing t= hese days...=C2=A0 the only thing I know it can't do is change the=C2= =A0 plan=C2=A0 in the middle of the execution stage.=C2=A0 Which is not a q= uery planner thing but the execution layer saying to itself=C2=A0 I am taki= ng too long maybe go back to the planning stage...=C2=A0 Query Hints that h= ave been discussed endlessly.=C2=A0 Adding=C2=A0hints adds its own problems= and has become a big mess for databases that support it.=C2=A0=C2=A0
Multiple transactions per connection.=C2=A0 I am asking WHY is that a fea= ture.=C2=A0 when one can have multiple sessions, what=C2=A0is the differenc= e?=C2=A0 running multiple transactions in single=C2=A0or multiple sessions = means moving=C2=A0 part of transaction logic into the application space. Wh= at do we gain here.....

No application packaging.= =C2=A0 This Oracle thing that=C2=A0 firebird has=C2=A0duplicated at some le= vel.=C2=A0 we can simulate this with namespace/schemas.

=C2=A0=C2=A0=
I can keep going on here.=C2=A0=C2=A0
=C2=A0
There are litigmate = points here
Compression,
not being able to return partials result s= ets from functions
XID being 32 bit=C2=A0
anonymous functions in PG h= ave several limitation not just input arguments=C2=A0(not sure i see the ne= ed for that)=C2=A0
Temporary tables are a pain and cause issues for big = databases

The=C2=A0article is unfair in many places..

<= /div>
On Tue, Sep 9, 2025 at 6:55=E2=80=AFPM Merlin Moncure = <mmoncure@gmail.com> wrote:=
On Tue, Sep 9, 2025 at 11:57=E2=80=AFAM Ellen Allhatatla= n <ellen= allhatatlan@gmail.com> wrote:
> Note: your link is wrong, = corrected here:

Extra hyphen - sorry about and thanks for pointing it out!

> What the article is driving at is that postgres does not use rollback = logs to handle updated records in the MVCC implementation.=C2=A0 There are = absolutely performance tradeoffs in that decision and, if you do a lot of d= evelopment against postgresql, those tradeoffs should influence how you des= ign databases.=C2=A0 The author then cherry picked the 'worst case'= case, large unconstrained updates.

Hmm... I was wondering about that - even though he stressed that there
was (paraphrasing) no right or wrong - just different design
decisions!

> The article is a bit of a cheezy dig on postgres.=C2=A0 Another exampl= e is the complaint about autonomous transactions with another cherry picked= example to make postgres look back. In the real world, these would not mat= ter much, and can be worked around (if you want to see my take on how to de= al with it, see here: https://github.com/leaselock/pgasync)= .

OK - so, I was wrong in my original assumption that somehow (and it
wasn't simply because of the phraseology - sweep vs vacuum) I thought that PG and FB had a similar MVCC implementation vs. Oracle and MySQL
(InnoDB) (and OrioleDB). I'll do a deep dive into their docco and see what they actually do! I'm actually very interested in the
benchmarking side of database technology - but I do know the old adage
- there are lies, damned lies, statistics and *_then_* there are
database benchmarks (as seen with the link I posted!).

Sure. I think you'll find that postgres approach to MVC= C is somewhat unusual relative to other players in this space, which is to = write 'old' records or changes in a rollback log; if the transactio= n commits it is discarded but if it rolls back, the rollback log is written= back to the heap.=C2=A0 This makes rollbacks potentially very painful.=C2= =A0 I suspect the postgres approach can also do better in cases of highly c= ontended records, but that's just a guess.

For= small (one or a small number of records) updates, the approach doesn't= make a whole lot of difference especially if you are aware of and exploit = HOT.=C2=A0 For very large updates however, it absolutely does, and one migh= t try to avoid them using various strategies.=C2=A0 I very much appreciate = fast rollbacks though.

merlin

=




merlin=C2= =A0
--00000000000082f323063e67ad57--