UPDATE table SET some_jsonb_column['person']['bio']['age'] = '99';
JSONB capabilities in Postgres are amazing, but the syntax is really annoying - for example, I'm forever mixing up `->` and `->>`. This new syntax feels far more intuitive.
Glad to this this super intuitive and familiar syntax added. Will make writing these updates a lot lot lot easier. Not even close!
For updates, it looks nice I guess.
But this is going to be a classic example of bad design. Databases are a bad place to be storing JSON, which is a good interface and a bad storage standard. It is pretty easy to see how JSON will play out: some bright young coder will use JSON because it is easier, then over the course of 12 months discover the benefits of a constrained schema, and then have a table-in-a-table JSON column.
It isn't so out there to think that ongoing calls for JSON support will lead Postgres to re-implement tables in JSON. We've already got people trying to build indexes on fields inside a JSON field.
This is needless complexity engineered by people who insist on relearning schemas from scratch, badly, rather than trusting the database people who say "you need to be explicit about the schema, do data modelling up front".
I think PostgreSQL has always been very pragmatic. It's supported JSON natively since 9.2 (Sep 2012).
> Databases are a bad place to be storing JSON
You're right that "mature" features and projects have a very good understand of the schema. But not everything is that.
Suppose I want to collect info from the Github API about a bunch of repos. I can just store the entire JSON response in a table and then query it at my leisure.
There's also something to be said for contiguous access. Joining tons of little records together has performance problems. Composite types and arrays can also fill this void, but they both have their own usability quirks.
That's why in 99% of cases, Postgresql uses jsonb as storage standard, which is binary and compressed.
> This is needless complexity engineered by people who insist on relearning schemas from scratch
No, this is the right tool for situations where schemas are polymorphic, fluid, or even completely absent (like raw third-party data). I love SQL and following normal forms, and it is the right tool for most situations, but not all.
If you've got hierarchical data and you just want to store, update and retrieve it as a whole (which is my use case), JSON is a good choice. Granted, it could be stored as a string/blob in my case. I don't really need to search within.
You don't even need JSONB to commit war crimes on a Postgres database. There's many things that Postgres can do, but probably shouldn't be done:
- Storing "foreign keys" in an array column, instead of using a join table
- Storing binary files as base64 encoded strings in text columns
- Using a table with `key` and `value` string columns instead of using redis
- Pub/sub using NOTIFY/LISTEN - Message queueing
- Other forms of IPC in general
- Storing executable code
- God tables
Even when trying to use Postgres appropriately, plenty of engineers don't get it right: unnecessary indices, missing indices, denormalised data, etc.
This isn't unique to Postgres, or relational databases in general. Any form of storage can and will be used to do things it's not designed or appropriate for. You can use as easily use S3 or Elasticsearch for message queuing, and can even find official guides to help you do so. Go back 20 years or so, and you can find implementations of message busses using SOAP over SMTP.
The problem isn't JSONB (or any other feature). It's bad engineering. Usually it's an incarnation of Maslow's Hammer: when all you have is a hammer, everything looks like a nail.
JSON makes perfect sense in a database that already supports all of: BLOB, TEXT, XML, ARRAY, and composite datatypes, including any datatype, including those on this list, for members of ARRAY and composites.
OTOH, Postgres has had XML since v8.2 (2006) and JSON since 9.2 (2012), and “tables in <supported structured serialization format>” hasn’t happened yet, even as discussion item AFAIK, so perhaps it would be bad, but even so it seems to be just fantasizing something to worry about.
The reason is with some projects/data it's hard to be explicit about the schema which is why NoSQL had it's popularity phase.
Now most applications don't have either entirely structured or entirely unstructured data, they will have a mix - so it's absolutely brilliant for one tool to do both. If they didn't support JSON I have a strong suspicion that they wouldn't have had some of the growth we have seen for Postgres across the last few years.
Do any other entrenched software projects come to mind? The only thing comparable I can think of are Git and Linux.
Last time I was responsible for setting up a HA Postgres cluster it was a garbage fire, but that was nearly 10 years ago now. I ask every so often to see if it has improved and each time, so far, the answer has been no.
Otherwise use MySQL, Oracle, MongoDB, Cassandra etc if you want to run it on your own.
Any other database that invested in a native and supported HA/clustering implementation.
Logical for streaming to read only replicas and streaming for fail-over. My client-app still needs to know try-A then try-B (via DNS or config)
I manage both a cockroachdb cluster and a few PG setups. Out postgres' have streaming replication to a standby with barman running on the standby. They are night and day.
Sure 2021 PG is way better than 2010 PG. But relative to available options, it's much worse.
And 200+ other improvements in the Postgres 14 release!
These are just some of the many improvements in the new Postgres release. You can find more on what's new in the release notes, such as:
The new predefined roles pg_read_all_data/pg_write_all_data give global read or write access
Automatic cancellation of long-running queries if the client disconnects
Vacuum now skips index vacuuming when the number of removable index entries is insignificant
Per-index information is now included in autovacuum logging output
Partitions can now be detached in a non-blocking manner with ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY
I see no mention of addressing transaction id wraparound, but these are in the release notes:
Cause vacuum operations to be aggressive if the table is near xid or multixact wraparound (Masahiko Sawada, Peter Geoghegan)
This is controlled by vacuum_failsafe_age and vacuum_multixact_failsafe_age.
Increase warning time and hard limit before transaction id and multi-transaction wraparound (Noah Misch)
This should reduce the possibility of failures that occur without having issued warnings about wraparound.
Sweet! I often screw up a query and need to cancel it with
pg_cancel_backend(pid)
The problem usually isn't that it doesn't work ever, just that it can take a very long time, especially if the query is reading some crazy amount of data. I've always found pg_cancel_backend() to be almost instant though.
Episode website: https://www.dataengineeringpodcast.com/postgresql-data-wareh...
Direct: (apple) https://podcasts.apple.com/us/podcast/data-engineering-podca...
If I have a Django + PG query that takes 1 second and I want to deeply inspect the breakdown of that entire second, where might I begin reading to learn what tools to use and how?
Instead you are likely to be forced to use a cloud hosted PostgreSQL instance in order to get HA/clustering.
The concepts behind Vitess are sufficiently general to simply apply them to PostgreSQL now that PostgreSQL has logical replication. In some ways it can be even better due to things like replication slots being a good fit for these sorts of architectures.
The work to port Vitess to PostgreSQL is quite substantial however. Here is a ticket tracking the required tasks at a high level: https://github.com/vitessio/vitess/issues/7084
I know this is not an optimized SQL. But this takes about 5 seconds in Postgre while the same command runs in milliseconds in MSSQL Server. The APCRoleTableColumn has only about 5000 records. The above query is to delete all columns not present in the schema from the APCRoleTableColumn table
I used to be a heavy MSSQL user. I do love Postgre and have switched over to using it in all my projects and am not looking back. I wish it was as performant as MSSQL. This is just one example. I can list a number of others too.
If you don't need the NOT IN weirdness around NULL values then I'd suggest you just use a NOT EXISTS. That'll allow something more efficient like a Hash Anti Join to be used during the DELETE. Something like:
Delete From "APCRoleTableColumn" Where Not EXISTS (Select 1 From information_schema.columns SC INNER JOIN "APCRoleTable" RT ON SC.table_name = RT."TableName" Where RT."TableName" = "APCRoleTableColumn"."TableName" AND SC.column_name = "APCRoleTableColumn"."ColumnName" AND SC.table_schema = 'public');
Is that faster now?
As others have said, explain analyze will show you what’s going on. I’m fairly sure this query would be fixed by flipping and / or adding an index. 5k records is nothing to pg.
In an ideal world PostgreSQL would handle infinite number of connections without a connection pool. Unlikely in practicem though.
There are good practical reasons to actually limit the number of connections:
(a) CPU efficiency (optimal number of active connections is 1-2x number of cores)
(b) allows higher memory limits
(c) lower risk of connection storms
(d) ... probably more
Some applications simply ignore this and expect rather high number of connections, with the assumption most of them will be idle. Sometimes the connections are opened/closed frequently, making it worse.
Eliminating the need for a connection pool in those cases would probably require significant changes to the architecture, so that e.g. forking a process is not needed.
But my guess is that's not going to happen. A more likely solution is having a built-in connection pool which is easier to configure / operate.
Separate connection pools (like pgbouncer) are unlikely to go away, though, because being able to run them on a separate machine is a big advantage.
- 12-10-2020: "Most regression tests are passing, but write-speeds are still low."
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)
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.
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.
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.
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.
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.
SQLite.
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.
MySQL and Oracle exist. Mercurial and perforce exist. I'm not sure it's a terrible stretch to compare git and postures.
There's almost no reason to pick MySQL for a new project.
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.
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.
Clearly it doesn't eliminate the possibility of wraparound failure entirely. Say for example you had a leaked replication slot that blocks cleanup by VACUUM for days or months. It'll also block freezing completely, and so a wraparound failure (where the system won't accept writes) becomes almost inevitable. This is a scenario where the failsafe mechanism won't make any difference at all, since it's just as inevitable (in the absence of DBA intervention).
A more interesting question is how much of a reduction in risk there is if you make certain modest assumptions about the running system, such as assuming that VACUUM can freeze the tuples that need to be frozen to avert wraparound. Then it becomes a question of VACUUM keeping up with the ongoing consumption of XIDs by the system -- the ability of VACUUM to freeze tuples and advance the relfrozenxid for the "oldest" table before XID consumption makes the relfrozenxid dangerously far in the past. It's very hard to model that and make any generalizations, but I believe in practice that the failsafe makes a huge difference, because it stops VACUUM from performing further index vacuuming.
In cases at real risk of wraparound failure, the risk tends to come from the variability in how long index vacuuming takes -- index vacuuming has a pretty non-linear cost, whereas all the other overheads are much more linear and therefore much more predictable. Having the ability to just drop those steps if and only if the situation visibly starts to get out of hand is therefore something I expect to be very useful in practice. Though it's hard to prove it.
Long term, the way to fix this is to come up with a design that doesn't need to freeze at all. But that's much harder.
It's a pity this wasn't listed in the announcement as I think alot of people are interested in this issue.
>> Long term, the way to fix this is to come up with a design that doesn't need to freeze at all.
Do you know if anyone is turning their attention to this or is it not currently being tackled by anyone?
In general I find postgres "just works" a lot more than MySQL. MySQL has a really bad habit of sticking with bad defaults for a long period, while having better configuration available. On the other hand postgres devs actively remove/change defaults so you're always getting the best it has to offer.
If you pick one, and you don't like it there are plenty of tools to change between them. If you're curious you could even deploy both of them.
IMO, the biggest shock from the MSSQL/MYSQL to PostgreSQL migration was not having 1 or 2 specific files per database, specially if you used to backup the files instead of doing a formal database backup.
Postgres by itself doesn't have a great horitzontal scaling strategy as of now I think. You need Citus or somt like that on top, maybe your friend was referencing that?
[1,2] https://www.postgresql.org/docs/13/routine-vacuuming.html https://www.postgresql.org/docs/current/planner-stats.html
The main issue we get is the 1 connection = 1 process issue although there are ways to mitigate that (namely pgbouncer).
Both are a degree more difficult than NoSQL. The main issue is maintaining schema's
nowadays postgres in the cloud does all of this for you.
And don't take my word for it, see for yourself here:
https://en.wikipedia.org/wiki/Comparison_of_relational_datab...
And MySQL is an Oracle product these days, go with MariaDB instead as this one is a MySQL fork made by the original papa of MySQL.
Take the result of this and paste it into https://explain.depesz.com/
which will make it human readable.
Understanding this is sometimes very easy, but if you want to understand what they _really_ mean, you can read depesz.com
It covers all major databases and is a good start to dive into database interna and how to interpret output from query analyzers.
Other than that, I highly recommend joining the mailing list and IRC (#postgresql on libera.chat).
Lots of valuable tricks being shared there by people with decades of experience.
It can also be used with Django Rest Framework via the browsable api.
May be parent is looking for deeper insight than this but it is useful to do quick visual query inspection.
django debug toolbar (or similar) should be the first thing you go to because these tools understand the django ORM well.
the other thing that comes to mind is enabling query timing in your django shell. i believe you might need an extension for this.
then you can look at the postgres itself. but i would keep it at the django layer at first because it might reveal something about the ORM.
Note that this isn't valid SQL, just an approximation, because Django doesn't generate a single SQL string, but uses the underlying library's parameterization. So you'll have to fiddle with quotes and such to get SQL you can run the EXPLAIN on that's mentioned in the other replies.
Very bad idea for a base table, sure. OTOH, potentially great idea for a (possibly materialized) view (as might eagerly storing an array of row values instead of keys.)
> Storing binary files as base64 encoded strings in text columns
That might not be a bad idea depending on size, and depending on how often you needed the binary vs. a base64 encoded string: if most of the use of the binary is in a context where it will be sent as base64 encoded, storing it that way might be a great idea.
> Using a table with `key` and `value` string columns instead of using redis
If you need an in-memory cache, sure. Otherwise...you could use redis, but I’m not sure why it would always be preferred.
> Pub/sub using NOTIFY/LISTEN - Message queueing
NOTIFY/LISTEN are a pub/sub mechanism. You shouldn’t use them alone as an application level message queueing system, but you definitely can build such a system on PG and might well use NOTIFY/LISTEN in the implementation.
> - Storing executable code
There are probably problems that involve specific instances of doing this, but this is at best to general to describe a thing yoi shouldn’t do.
We did switch to using binary queries though (by passing result_format as 1), and at that point we did see a speedup from using bytea columns - but it’s quite a bit of work to get the type mapping correct then so probably not worth it in most cases
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.
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.
They aren’t constraints (except in the case of “INTEGER PRIMARY KEY”), but they also aren’t “completely ignored”, because of type affinity.
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).
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.
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.
I see the names of a few people that also work on Postgres on this thread. We're not all that hard to get a hold of if you're a user that has some kind of feedback or question, for what it's worth. The culture is very open in that sense.
> Do you know if anyone is turning their attention to this or is it not currently being tackled by anyone?
This is one of the goals of the zheap project. I myself have some very tentative ideas for tackling it within the standard table access method, heapam. I have not specifically committed to working on it on any timeframe. I haven't completely convinced myself that the approach I'm thinking of is truly robust and practicable. It's pretty complicated, especially because I cannot really know what will break and need to be fixed until I spend significant effort on the implementation.
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.
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.
I still would never use mysql, just because of vitess.
It shouldn't matter so much, but when you don't use one language as much as you do other languages, it becomes that much harder to remember unfamiliar syntaxes and grammars, and easier to confuse similar looking operations with each other.
Is there even a function that would convert JSON encoded "string" to text it represents in postgresql? I didn't find it.
So all you can do is `SELECT json_col['a'] = some_text_col::jsonb;` and hope for the best (that string encodings will match) or use the old syntax with ->> or #>>.
Oddly, no, there's no specific function for taking a root-level scalar JSON term (like '"foo"'::jsonb), and extracting said scalar to its equivalent native Postgres type.
You can still do it (for extracting to text, at least), but you have to use a 'vacuous' path-navigation to accomplish it, so it's extremely clumsy, and wastes the potential of the new syntax:
SELECT '"foo"'::jsonb #>> (ARRAY[]::text[]);ETA: no, actually, I was wrong — #>> takes text[], so you can already pass it an ARRAY[] literal containing expressions. It's just all the examples in the PG docs that use the IO syntax to represent the path, and then rely on an implicit cast to text[].
Personally if I were choosing the tech stack for my company I'd still go for Postgres though
We see no noticeable drop in performance with increased idle connection with our workload.
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.
- 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.
Especially if an index goes down and you need to kick it to continue indexing.
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.
Mongo, on the other hand, has definitely always had the racist/ablist slur as the first connotation for me.
In the UK that would be "mong", for us Mongo is the planet Ming The Merciless is from.
EDIT: I see, it would have session state restore, not just DISCARD like pgbouncer.
Also it looks like this is not new https://github.com/docker-library/postgres/issues/37
Debian even has special custom helper scripts for doing it. So just build your container with both postgres versions?
And Julia uses BLAS which is written in Fortan.
https://raw.githubusercontent.com/JuliaLinearAlgebra/Octavia...
There’s a pl/pgsql reimplementation of temporal tables specifically for that use case.
having stuff baked in is often a win.
SELECT '"foo"'::jsonb #>>'{}';
SELECT jsonb_col['prop1']#>>'{}' FROM ...;
Anyway, for fun:
create function deref_jsonb(jsonb) returns text as $$ begin return $1#>>'{}'; end $$ language plpgsql;
CREATE OPERATOR # ( leftarg = jsonb, function = deref_jsonb );
select '"sdfasdf"'::jsonb #;
select jsonb_col['a']# FROM somewhere;
-- equivalent to deref_jsonb('"sdfasdf"'::jsonb)
select ('"sdfasdf"'::jsonb).deref_jsonb;
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!
"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.)
Notsomuch for production. I know... folks do it. But I wouldn't run anything on it that I wouldn't do w/ sqlite.
Also, in general, that persistent storage is (generally) not locally attached, which means that (some) file system interactions may have unexpected semantics. Now, with a DB, you're probably only going to have the PV attached on a single worker node, so most of that will actually just work.
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.