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 1uwMlk-000mGi-0O for pgsql-general@arkaria.postgresql.org; Wed, 10 Sep 2025 15:30:00 +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 1uwMlg-0022xi-Vh for pgsql-general@arkaria.postgresql.org; Wed, 10 Sep 2025 15:29:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uwMlg-0022xa-Je for pgsql-general@lists.postgresql.org; Wed, 10 Sep 2025 15:29:57 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uwMld-0001sl-0k for pgsql-general@postgresql.org; Wed, 10 Sep 2025 15:29:56 +0000 Received: by mail-ej1-x62d.google.com with SMTP id a640c23a62f3a-b04ba58a84fso652962666b.2 for ; Wed, 10 Sep 2025 08:29:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1757518193; x=1758122993; 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=UQxhaHZmjHG1eG5BOKYaz8qUHF8tFxFkvgtykOYNDpM=; b=H3x6QFhEoFuZkwn3cLeNHR+WYVnwuqtn+sQwbO3SWkYgVMPxMIOlMwTe/6x5r2mKXX dxtrMboxnz9d0u6/Zhv12/K5CQYT5bmU4y05feZMnTKbPIGxe1shKfi8jeQOJ2SLsC5J tavRL5z1Mucm2Klkrl6Eu5l/SE6ubY2DllcmfbrtnIohmDWYFK1DK9pogbo4NBhl+qPP zQMkYglNtZ0ceL80+pBf6DVq0EMNFHawoH9mRFOFGBF83F8sl01Im0Dsur7ueUdjcrOy riMxcQwqBQUCW9kIyiSB7JYJGan2tzkKPnG6zjXk7TCIQltCVM4LGWxJtwS6tu5X+o2O Q+OQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1757518193; x=1758122993; 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=UQxhaHZmjHG1eG5BOKYaz8qUHF8tFxFkvgtykOYNDpM=; b=kvCwPU/ylVUMphKcwunD2osP7vSAz0m9GhH3pYfY+ZZ1IBCnwQa9+QBNehuvS2EXFt v5bERCHAF+NsHrYih1/ZsdoH2O3NHYxZSZ/SyYwEeURZFfoIvB4Yy3cKt6ZN6fuX4YK9 WQseO+rdgRe5cfTOYCbvSbG6eBbBRPojJSpwccEQrdcD+5ILoSxe6jxiq9UQrbLIPOj5 AzXHxvUNh2DLEpbO2LaEZgL8xAYQtpFAHUmUDIodMsEdMGedYmk6XPJqcheORmtoSlSe uWwi5JiLBkcs2BSgKVy5RWfMrzqQUczuy1MLEVttSPn77Xwv0SOat2MCmi7LQS1y7Dw7 qm8g== X-Forwarded-Encrypted: i=1; AJvYcCWfSsivir1cG5yx6B4xWztFnfG6e264wlhSzOEUV1WC1kSXVi5b1l5T3Z8ISyDh/5cGLbDPfqSqzLkftBoq@postgresql.org X-Gm-Message-State: AOJu0YwBTcOp/9/xpG+zuVqItX+BFtcpr0fhppPgUkYUJyBcfcDAwTyp t0/UUCtwbnBBmTDi6z3EG8uuFWaMsNMqzDxB0u180UW1Aupq9i1X6zraspFTGZeADYnNGIlX08a j8w1VviL48sIVijUNfNRG4bH+ECwgHgzLLRIn X-Gm-Gg: ASbGncsVnCaLkl9Z169skXk2Pzpl792fkzJUfYRIL0T50k4PNGLGeDJ9n1otgnNAIeO dLNV2PoFoZ1KM17zKgE+ADBm8Ym9U+V3iIM2sejNYsZp5UuV9/nr21EhHLk5DUe7uOBvU/bdLkb h9IbpI/q2Pv/MnNiZTqqUtS/+Y6g3JEx/kC6boBmaojn/QyAGCboPGpyfjNH0kFuV2UuASOmsY2 7meUHMrZw== X-Google-Smtp-Source: AGHT+IHQWOtuQBUNoQjss41Wml9chARsPSZL2j0zSDRixKbHryrdoJeJJpm1eIpRxuGIdXgZ40jAhOVLdVzfyZHEHbg= X-Received: by 2002:a17:907:9414:b0:b04:a852:4f41 with SMTP id a640c23a62f3a-b04b1687cd0mr1615901866b.31.1757518193136; Wed, 10 Sep 2025 08:29:53 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Justin Date: Wed, 10 Sep 2025 11:29:41 -0400 X-Gm-Features: AS18NWDQdkPYR1QvsUwqMpzJQug42Hx3f9M7fs8lhNL4Nh1OhD4mNivTSU0-PXM Message-ID: Subject: Re: MVCC and all that... To: Ellen Allhatatlan Cc: Merlin Moncure , pgsql-general Content-Type: multipart/alternative; boundary="000000000000969950063e741707" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000969950063e741707 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Sep 10, 2025 at 3:41=E2=80=AFAM Ellen Allhatatlan < ellenallhatatlan@gmail.com> wrote: > Hi, and thanks for your input, > > Just before I reply - if you (at least here in Ireland - Google's > answers vary per location, unlike Duckduckgo's) search for "firebird > mvcc mechanism" the "AI assistant" tells me twice that FB's MVCC > implementation is "like PostgreSQL's"... I'll investigate further and > report back. Igor Rogov's book looks like a good place to start! > > > 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 o= r > worked around. > > Pity - I took the guy at his word when he said that PostgreSQL was > just different, not better or worse. > > > 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. > > OK - I'm going to run the benchmarks myself and see what happens - but > I"m sure he didn't pick that test for nothing - come to think of it, > the table stable structure is bizarre! > My point here is not to criticize that article as being wrong, PG will bloat the table and be slower than firebird PG. My criticism is this argument/example is not reflective of the real world. What DBA/Programmer would not be in trouble locking 25% of a table for a prolonged period of time doing an update. This approach would all but guarantee an outage or other issues across the database. This argument is bogus and very much against best practices updating large amounts of data in a single transaction creates blockers for other sessions. > > > 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. > > This is where things become interesting. Firebird actually has 3 > process/threading models - and they manage to maintain these with a > team that is *_much_* smaller than the PostgreSQL one - FB is a minnow > compared to PG! > > AIUI, Michael Stonebraker suggested that the process model > would/should be "upgraded" to a threaded one at some point in the > system's developement? > I am going to need a source on this. Process vs Threads: pro and cons are very well documented and proven today. > > > > 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. > > OK again. I'm just wondering if the single file per database isn't a > fundamental architectural flaw in itself? AIUI, you could have > mulitple files (back in 32-bit land) "chained" - but (again AIUI) the > same table could be spread over x files - all "intermingled"... weird. > Single file approach vs multiple file approach. This boils down to preference; there is no clear plus and minus to either approach. the Path to the actual data has to go through several Maps. Multiple Files. Disk Internal Structure, =3D> File System( ZFS EXT FAT etc..), =3D> Table mapped to a File or group of Files,=3D> DataPage mapped = to data type and columns, Single File Disk Internal Structure =3D> File System( ZFS EXT FAT etc..), Single File =3D> Mapping Tables to locations in the single file =3D> DataPa= ge mapped to data type and columns. Single file has the advantage removing IO context switching at the OS level, however it moves the Context switch to the database side jumping around in the single file itself to find the necessary data. This does not reduce IO > > > "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 plannin= g > stage... Query Hints that have been discussed endlessly. Adding hints > adds its own problems and has become a big mess for databases that suppor= t > it. > > My criticism here "The claim postgresql query planner is simple" is not backed up with actual examples and data. Looks at a specific case which does not even involve the query planner. > > > 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 idea - I'll take your word for it! > Making a logical argument here, the statement this feature is a plus without any examples to backup the claim. I'm trying to show a counter example why would this be a plus when we can do the same thing just differently. It ignores the obvious issue of moving the transaction logic into the app has big drawbacks. > > Accepted now - thanks for your input. > > > -- > > El! > --000000000000969950063e741707 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Wed, Sep 10,= 2025 at 3:41=E2=80=AFAM Ellen Allhatatlan <ellenallhatatlan@gmail.com> wrote:
Hi, and thanks for your input,<= br>
Just before I reply - if you (at least here in Ireland - Google's
answers vary per location, unlike Duckduckgo's) search for "firebi= rd
mvcc mechanism" the "AI assistant" tells me twice that FB= 9;s MVCC
implementation is "like PostgreSQL's"... I'll investigate= further and
report back. Igor Rogov's book looks like a good place to start!

> I read through the article its click bait/flame war just waiting to ha= ppen.
> Article is a list of cherry picked PG drawbacks that can be mitigated = or worked around.

Pity - I took the guy at his word when he said that PostgreSQL was
just different, not better or worse.

> 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 proble= ms in a production database with a high TPS rate.

OK - I'm going to run the benchmarks myself and see what happens - but<= br> I"m sure he didn't pick that test for nothing - come to think of i= t,
the table stable structure is bizarre!

= My point here is not to criticize that article as being wrong, PG will=C2= =A0 bloat the table and be slower than firebird PG.=C2=A0 My criticism is t= his argument/example is not reflective of the real world.=C2=A0 What DBA/Pr= ogrammer would not be in trouble locking 25% of a table for a prolonged=C2= =A0period of time doing an update.=C2=A0 This approach would all but guaran= tee an outage or other issues across the database.=C2=A0 This argument is b= ogus and very much against best practices=C2=A0 updating large amounts of d= ata in a single transaction creates blockers for other sessions.=C2=A0
=C2=A0

> 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.<= br>
This is where things become interesting. Firebird actually has 3
process/threading models - and they manage to maintain these with a
team that is *_much_* smaller than the PostgreSQL one - FB is a minnow
compared to PG!

AIUI, Michael Stonebraker suggested that the process model
would/should be "upgraded" to a threaded one at some point in the=
system's developement?

I am going t= o need a source on this.=C2=A0 Process vs Threads:=C2=A0pro and cons are ve= ry well documented and proven today.=C2=A0
=C2=A0


> Backup argument is cherry picking and not discussing pgBackrest and ot= her solutions=C2=A0 or the use of tablespaces to isolate databases in a clu= ster at the disk layer=C2=A0 or disk snapshots.

OK again. I'm just wondering if the single file per database isn't = a
fundamental architectural flaw in itself? AIUI, you could have
mulitple files (back in 32-bit land) "chained" - but (again AIUI)= the
same table could be spread over x files - all "intermingled"... w= eird.

Single file approach vs multiple = file approach.=C2=A0 This boils down to preference; there is no clear plus = and minus to either approach.=C2=A0 the Path to the actual=C2=A0 data has t= o go through several Maps.=C2=A0 =C2=A0
Multiple Files.=C2=A0 Disk Inter= nal Structure, =3D> File System( ZFS EXT FAT etc..), =3D> Table mappe= d to a File or group of Files,=3D> DataPage mapped to data type and colu= mns,=C2=A0 =C2=A0
Single File=C2=A0 Disk Internal Structure =3D&g= t;=C2=A0File System( ZFS EXT FAT etc..), Single File =3D> Mapping Tables= to locations in the single file =3D> DataPage mapped to data type and c= olumns.

Single file has the advantage removing IO context switching = at the OS level,=C2=A0 however it moves the Context switch to the database = side jumping=C2=A0around in the single file itself to find the necessary da= ta.=C2=A0=C2=A0

This does not reduce IO=C2=A0=C2= =A0
=C2=A0

> "PostgreSQL has a relatively simple, but fast query planning algo= rithm"=C2=A0 Compared to what....=C2=A0 What feature is PG missing the= se 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 query= planner thing but the execution layer saying to itself=C2=A0 I am taking t= oo long maybe go back to the planning stage...=C2=A0 Query Hints that have = been discussed endlessly.=C2=A0 Adding hints adds its own problems and has = become a big mess for databases that support it.

<= br>
My criticism here "The claim postgresql query planner is= simple" is not backed up with actual examples and data.=C2=A0 Looks a= t a specific case which does not even involve the query planner.=C2=A0
=C2=A0

> Multiple transactions per connection.=C2=A0 I am asking WHY is that a = feature.=C2=A0 when one can have multiple sessions, what is the difference?= =C2=A0 running multiple transactions in single or multiple sessions means m= oving=C2=A0 part of transaction logic into the application space. What do w= e gain here.....

No idea - I'll take your word for it!

Making a logical argument here,=C2=A0 the statement this=C2=A0 feature i= s a plus without any examples to backup the claim.=C2=A0 I'm trying to = show a counter example why would=C2=A0this be a plus when we can do the sam= e thing just differently.=C2=A0 It ignores the obvious=C2=A0issue of moving= the transaction logic into the app has big drawbacks.=C2=A0=C2=A0
=C2=A0

Accepted now - thanks for your input.


--

El!
--000000000000969950063e741707--