There's been a lot of exciting work in this area over the last decade or so. Andy Pavlo's classes are great surveys of the latest work: https://15721.courses.cs.cmu.edu/spring2020/
CosmosDB is an example of a relational (multi paradigm properly) database with a quite different architecture vs the classic design, that's moved into production status quite rapidly.
FaunaDB and CockroachDB are moving with solid momentum too.
- scaling is non-trivial (you can't just add a node and have PostgreSQL automagically Do The Right Thing™)
- you can only have so many connections open to the database, causing issues with things such as AWS Lambda
- I don't remember if this was changed, but I got the impression a while ago that having dynamic DB users was a bit cumbersome to set up (plug PostgreSQL to AD/LDAP)
There are projects to automate the syncing of LDAP users to postgres but it would be nice if this was built-in.
However I get the impression that part of the reason these features aren't in the box is to limit scope creep in the main project.
All of these require a different architecture so expect to see newer databases push things even further.
Even Croach would be a massive branding improvement.
This is similar to how gimp is a terrible brand.
Mongo, on the other hand, has definitely always had the racist/ablist slur as the first connotation for me.
"Almost all" does sound like a bit of a surprise, but thinking back on it the only one I know for a fact I heard for the first time outside of a corrective context was my elder uncles friends who enjoyed self-depreciating jokes, usually with slurs for eastern Europeans in them. I first heard those as a child and only realized years later they were offensive. Most others, I think, I honestly have no idea when i was first exposed to them.
[0] https://en.wikipedia.org/wiki/Mongols (If it's used descriptively on Wikipedia, it's probably not considered offensive.)
My pet theory is that the database is named after a character in "Blazing Saddles", and I want to be able to store candy in it.
In the UK that would be "mong", for us Mongo is the planet Ming The Merciless is from.
The machine being used for this benchmark has 96 vCPUs, 192G of RAM, and costs $3k/mo.
My business runs just fine on a 3.75G, 1 vCPU instance. But idle connections eat up a huge amount of RAM and I sometimes find myself hitting the limits when a load spike spins up extra frontend instances.
Sure I could probably setup pgbouncer and some other tools but that's a lot of headache. I'm acutely aware that MySQL (which I dislike because no transactional DDL) does not suffer from this issue. I also don't see this being solved without a major rewrite, which seems unlikely.
So Postgres has at least one very serious fault that makes room in the marketplace. The poor replication story is another.
As for the per-connection memory usage, the big question is whether there really is a problem (and perhaps if there's a reasonable workaround). It's not quite clear to me why you think the issues in your case are are due to idle connections, but OK.
There are two things to consider:
1) The fixed per-connection memory (tracking state, locks, ..., a couple kBs or so). You'll pay this even for unused connections.
2) Per-process memory (each connection is handled by a separate thread).
It's difficult to significantly reduce (1) because that state would no matter what the architecture is, mostly. Dealing with (2) would probably require abandoning the current architecture (process per connection) and switching to threads. IMO that's unlikely to happen, because:
(a) the process isolation actually a nice thing from the developer perspective (less locking, fewer data races, ...)
(b) processes work quite fine for reasonable number of long-lived connections, and for connection pools address a lot of the other cases
(c) PostgreSQL supports a lot of platforms, some of which may not may not have very good multi-threading support (and supporting both architectures would be quite a burden)
But that's just my assessment, of course.
Reducing this amount would also run Postgres in more constrained environments.
At the OS level, we can't really do much, I'm afraid :-( I don't think we're wasting too much memory there, exactly because a lot of the memory is shared between processes. Which also makes it difficult to determine how much memory is actually used by the processes (the sharing makes the various metrics in ps/top are rather tricky to interpret).
As for the internal memory, it's a bit more complicated. We need a little bit of "per process" memory (per-backend entries in various internal data structures, etc.) - a couple dozen/hundred kBs, perhaps. It's hard to give a clear figure, because it depends on max_locks_per_transaction etc. This is unlikely to go away even if we switched to threads, because it's really "per session" state.
But then there are the various caches the processes keep, memory used to run queries etc. Those may be arbitrarily large, of course. The caches (with metadata about relations, indexes etc.) are usually a couple MBs at most, but yes, we might share them between threads and save some of this memory. The price for that would be the need for additional synchronization / locking, etc. The memory used to run queries (i.e. work_mem) is impossible to share between threads, of course.
There's a blog post by Andres Freund with more details: https://www.citusdata.com/blog/2020/10/08/analyzing-connecti...
Overall, there's very little chance PostgreSQL switch to threads (difficulty of such project, various drawbacks, ...). But I do agree having to run a separate connection pool may be cumbersome, etc. There was a proposal to implement integrated connection pool, which would address at least some of those problems, and I wouldn't be surprised if it happened in foreseeable future.
Your definition of "reasonable number of long-lived connections" is anything but reasonable. Then "connection pools address a lot of the other cases", when a connection pool/bouncer is unwanted, unwarranted, and just adds another point of failure that needs to be deployed and maintained.
Firstly, it's not the goal of the PostgreSQL project to overtake MySQL or other databases, but to serve the existing/new users. This also means we're investing the development effort in a the highest benefit / effort ratio. Even if switching from process-based to thread-based model improved the per-connection overhead, the amount of work needed is so huge the benefit / effort ratio is so utterly awful no one is going to do it. There are always better ways to invest the time / effort. Especially when there are practical solution / workarounds like connection pools.
Secondly, every architecture has pros/cons, and switching from processes to threads might help in this respect but there are other consequences where the process model is superior (some of which were already mentioned). Focusing on just this particular bit while ignoring the other trade-offs is rather misleading.
And no, the arguments did not really disappear. To some extent this is about the programming model (locking etc.), and that did not really change over time. Also, PostgreSQL supports platforms, some of which may not have particularly great threading support.
I'm not claiming there are no workloads / systems that actually need that many long-lived connections without a connection pool. In my experience it's usually "We don't want to change the app, you have to change the DB!" but fine - then maybe PostgreSQL is not the right match for that application.
Then other products will emerge and overtake some of PostreSQL's marketshare in the long run. It's already happening in fact. Just like more efficient and easier to configure webservers like nginx and caddy are gaining marketshare over Apache httpd.
I love PostgreSQL and don't want to see it becoming the next Apache httpd, slowly but surely fading. Perhaps FAANGs could fund such refactor.
Perhaps a cheaper solution was to incorporate pgBouncer inside PostgreSQL so it would naturally sit in front of PostreSQL in the default installation without extra configuration.
Maybe, we'll see.
It however assumes the other (thread-based) architecture is somewhat universally better, and I doubt that's how it works. It might help the workloads actually requiring many connections to some extent, but it's also likely to hurt other workloads for which the current architecture works just fine.
But let's assume we decide to do that - such change would be a massive shift in programming paradigm (both internally and for extensions developed by 3rd parties) and would probably require multiple years. That's a huge investment of time/effort, with a lot of complexity, risks and very limited benefits until it's done. I'd bet there'll always be a feature with better cost/benefit ratio.
So reworking the other architecture might actually gain us some users but loose others, and drain insane amount of development resources.
> Perhaps a cheaper solution was to incorporate pgBouncer inside PostgreSQL so it would naturally sit in front of PostreSQL in the default installation without extra configuration.
Yes, I already mentioned that's quite likely to happen. There has already been a patch / project to do exactly that, but it didn't make it into PG14.
That's great to hear!
There are three pooling modes:
- Session pooling. Doesn't help with this issue since it doesn't reduce the total number of required connections.
- Transaction pooling / statement pooling. Breaks too many things to be usable. (eg. prepared statements...)
See the table at https://www.pgbouncer.org/features.html for what features cannot be used with transaction pooling.
And it works fine on low-resourced machines that I tend to use for everything.
I follow the mailing list because I'm interested in this exact issue. Konstantin Knizhnik sent a patch implementing a built-in connection pooler in early 2019 that uses a similar approach to what I just described. The work on that has continued to this day, and I'm hopeful that it will eventually be merged.
EDIT: I see, it would have session state restore, not just DISCARD like pgbouncer.
We see no noticeable drop in performance with increased idle connection with our workload.
This comes with the advantes for Pg developers (and us!) that they don't need to deal with tons of data races issues, but the trade off is that memory wise, a process takes way more memory than a thread.
Elasticsearch is underrated here, IMO. Yes, there are alternatives for simple fulltext search. But there’s a lot more it can do (adhoc aggregations incorporating complex fulltext searches, with custom scripted components; geospatial; index lifecycle management) and if you’re using those features, there’s nothing else comparable.
It’s pretty stable, too, once you’ve got the cluster configured. We don’t have outages due to problems with Elasticsearch.
I’m currently evaluating typesense vs ES for a fts project and typesense is winning so far by simply be “not painful” to deal with.
Sure, so use something else. I do need (most all of) that at my work (plus the horizontal scaling), and there's no competition. I know we're not the only ones.
Also, there's nothing bizarre about the scripting feature. There are several options for scripting, it's very flexible, and it suits implementing custom logic when you need it.
And, I'm not saying ES is perfect! I'm saying that there's a set of use-cases that only ES (to my knowledge) can fulfil, and that's complex aggregations also involving complex full-text search, over tera/petabytes of data. Clickhouse can do aggregations, but doesn't have anything close to the search chops (again, to my knowledge).
They are great in some cases and terrible in others, and over time, use cases push database systems into their worst cases. Use cases rarely stay in the sweet spot of a special-purpose system.
That being said, if the integration is great, and/or the special system is a secondary one (fed from a general-purpose system), then it's often fine.
I certainly wouldn’t say ES is the former. We use it for some conplex things that (AFAIK) no other (publicly available; I don’t what eg Twitter or Google has going on) system could provide at the scale we need. Everything we’re doing is well within the realm of what ES is built for, and it’s the only system built for it. It’s not perfect, but most of our performance issues could be solved by scaling out, where query or index optimization isn’t tractable.
It’s totally understandable that you’d need developers to have expertise in patterns and anti-patterns, as well as needing an expert to set things up in the first place, but you shouldn’t have to have a dedicated ES monitoring / tuning / babysitting team like Oracle DBAs of yore. That you do, means it isn’t there yet as a product.
In our case for a small website serving the general public (a few tens of thousands of requests per day) it just worked OOTB with hardly any tuning or maintenance at all.
Especially if an index goes down and you need to kick it to continue indexing.
SQLite.
They aren’t constraints (except in the case of “INTEGER PRIMARY KEY”), but they also aren’t “completely ignored”, because of type affinity.
But saying "so much better" is too strong.
https://www.postgresql.org/docs/devel/btree-implementation.h...
One benchmark involving a mix of queue-like inserts, updates, and deletes showed that it was practically 100% effective at controlling index bloat:
https://www.postgresql.org/message-id/CAGnEbogATZS1mWMVX8FzZ...
The Postgres 13 baseline for the benchmark/test case (actually HEAD before the patch was committed, but close enough to 13) showed that certain indexes grew by 20% - 60% over several hours. That went down to 0.5% growth over the same period. The index growth much more predictable in that it matches what you'd expect for this workload if you thought about it from first principles. In other words, you'd expect about the same low amount of index growth if you were using a traditional two-phase locking database that doesn't use MVCC at all.
Full disclosure: I am the author of this feature.
I forgot to mention that the test case had constant long-running transactions, each lasting 5 minutes. Over a 4 hour period for each tested configuration.
This level of improvement was possible by adding a relatively simple mechanism because the costs are incredibly nonlinear once you think about them holistically, and consider how things change over time. The general idea behind bottom-up index deletion is that we let the workload figure out what cleanup is required on its own, in an incremental fashion.
Another interesting detail is that there is synergy with the deduplication stuff -- again, very nonlinear behavior. Kind of organic, even. Deduplication was a feature that I coauthored with Anastasia Lubennikova that appeared in Postgres 13.
MySQL and Oracle exist. Mercurial and perforce exist. I'm not sure it's a terrible stretch to compare git and postures.
https://smalldatum.blogspot.com/2021/01/sysbench-postgres-vs...
I'm not claiming that this benchmark justifies the claim that Postgres broadly performs better than MySQL/InnoDB these days -- that would be highly simplistic. Just as it would be simplistic to claim that MySQL is clearly well ahead with OLTP stuff in some kind of broad and entrenched way. It's highly dependent on workload.
Note that Postgres really comes out ahead on a test called "update-index", which involves updates that modify indexed columns -- the write amplification is much worse on MySQL there. This is precisely the opposite of what most commentators would have predicted. Including (and perhaps even especially) Postgres community people.
The former makes it possible to have MVCC (and thus gives you snapshot isolation and serializability) and makes secondary indexes perform faster, at the cost of vacuum or Oracle-style redo/undo/rollback segments with associated "Snapshot too old" issues.
The latter pretty much forces use of locking even for read so queries block each other (but don't require vacuum or something), makes clustering key selective queries perform faster than secondary index ones and makes you think really hard about the clustering key.
It's not really a feature you would have, but a complicated design tradeoff.
There's almost no reason to pick MySQL for a new project.
There’s a pl/pgsql reimplementation of temporal tables specifically for that use case.
having stuff baked in is often a win.
I still would never use mysql, just because of vitess.
Excel for business spreadsheets.
Java for enterprise server software.
Not an expert, but it is my understanding that Julia is becoming an ever more serious competitor day by day.
> Excel for business spreadsheets.
Honest question, what does LibreOffice miss compared to Excel? In any case, (again not an expert) spreadsheets seem quite inferior to a combination of Julia, CSV and Vega (Lite); although there are certainly more people that are familiar with operating Excel.
And Julia uses BLAS which is written in Fortan.
https://raw.githubusercontent.com/JuliaLinearAlgebra/Octavia...
Big corporations are horribly inefficient and Enterprise Software necessarily so from that...if you're saying Java is terrible by nature of it being the goto for enterprise, then that makes sense. It took 20 years for it to swap places with COBOL and I expect it will be something else in 20 more.
- appreciation of backwards-compatibility (here it wins with Python);
- great debuggers and performance tools (e.g. Java Flight Recorder or Eclipse Memory Analyzer);
- easy deployment - you can just give someone a fat JAR (here it wins with all scripting languages, so Python, Ruby, PHP, or any other flavour of the month);
- industry-grade garbage collectors;
- publicly-available standard spec (here it wins with all the defined-by-implementation languages such as Python, PHP, Rust, basically most languages, and with languages which are standardized, but their specs aren't public: C, C++, Ruby);
- kind of like the previous point, but anyway: multiple implementations to choose from;
- I've been told it has good performance. I've never seen a real-world Java application which felt fast, but I've heard people put it at the pedestal and the Debian programming languages benchmarks game seems to corroborate that story;
Besides, the question wasn't about which technologies we like, but which we believe are entrenched so much, they aren't going to go away for a very long time. I don't see Java going away for another 100 years, no matter how much I would or wouldn't like to work with it.
Now I personally don't like Java - it feels crusty vs C# - but the libraries are amazing.
You can also use something nice like Kotlin and you have all of the platform benefits with non of the crusty language issues.
After using Python, Go, PHP, and C++ it's easy to see why Java is the go-to language for server development.
Personally if I were choosing the tech stack for my company I'd still go for Postgres though
Do any other entrenched software projects come to mind? The only thing comparable I can think of are Git and Linux.