Re: MVCC and all that...

On Tue, Sep 9, 2025 at 8:41 PM Justin <zzzzz(dot)graf(at)gmail(dot)com> wrote:

> 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
>

Would converting them to 64 bits require changing the on-disk structure of
database files?

> anonymous functions in PG have several limitation not just input
> arguments (not sure i see the need for that)
>

Aren't transience and "ad hockery" the whole point of anonymous
procedures? Thus, I don't see the point of passing them parameters, either.

(When I *do* need something similar, I build the DO block as a bash string
variable with environment variables as "parameters", and then execute it
via psql -c "$sql" More like a template, TBH. It's great for purging old
data from tables, since I can bypass records who's DELETE statements fail
due to a FK constraint.

> Temporary tables are a pain and cause issues for big databases
>

I'd like to see GLOBAL TEMPORARY tables. Each connection gets its own
private copy of the table, so that applications don't need to carry around
CREATE TEMPORARY TABLE code with them.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!