On Tue, Sep 09, 2025 at 08:41:02PM -0400, Justin wrote: > 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 relevant to the next part: > 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. When a service is written in such a way as to minimize the memory footprint of each request/client then it can process more of them assuming it's only memory-bound. Why? Because less memory per thing == less bandwidth use, and also less thrashing of caches and higher cache hit ratios. Minimizing request/client state means not spreading any of it on the stack, thus not requiring a stack per-client. This means not thread-per-client (green or otherwise) or process-per-client. It means essentially some flavor of continuation passing style (CPS). For a query plan executor that's really: the query plan, all the in-flight I/O requests, all cached data still needed to continue processing the plan. If you have a Duff's device style / CPS style implementation, then nothing on the stack needs to be preserved while waiting for I/Os, and the state of the query plan is effectively minimized. But for a database with storage I/O costs the memory footprint doesn't matter quite so much because most likely it will be I/O bound rather than CPU- or memory-bound. > "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. I would really like out-of-band hints. These would be hints not specified in the SQL itself but to be sent separately and which address table sources or joins by name, like this: psql> SELECT .. FROM x x1 JOIN y y1 ON .. JOIN y y2 ON .. WHERE ..; ...> \hint y1 indexed by .. ...> \hint y2 indexed by .. ...> ; > 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..... I agree it's not really important. Moreover interleaving multiple queries over one TCP connection will lead to having to manage how much bandwidth each query consumes so as not to drown out the others. > No application packaging. This Oracle thing that firebird has duplicated > at some level. we can simulate this with namespace/schemas. And extensions. > XID being 32 bit This is a huge problem. > Temporary tables are a pain and cause issues for big databases Yes. PG badly needs GLOBAL TEMP. Another thing that would be nice is if PG could have tables that are not heaps. Nico --
