This later comment in the thread (
https://www.postgresql.org/message-id/flat/7663dfec-e46a-401...), by Merlin Moncure, describes a set-up that most applications have, and I agree with his assessment of it:
Taking a step back, from the outside, it looks like uber:
*) has a very thick middleware, very thin database with respect to
logic and complexity
*) has a very high priority on quick and cheap (in terms of bandwidth)
replication
*) has decided the database needs to be interchangeable
*) is not afraid to make weak or erroneous technical justifications as
a basis of stack selection (the futex vs ipc argument I felt was
particularly awful -- it ignored the fact we use spinlocks)
The very fact that they swapped it out so easily suggests that they
were not utilizing the database as they could have, and a different
technical team might have come to a different result. Postgres is a
very general system and rewards deep knowledge such that it can
outperform even specialty systems in the hands of a capable developer . . .
It's all the rage to use an object-relational wrapper to abstract away the database brand, so that the database can be "interchangeable." In my 11 years experience with web apps and databases, particularly Postgres, this is a false economy:
- When you use an object-relational wrapper, you are trading one kind of lock-in for another. Instead of locking yourself into Postgres, you are locking yourself into PHP. That seems a good trade to most developers, who know their middleware language better than SQL.
- However I have found that moving your application code from the middle layer, to SQL, results in shorter code and much faster execution. I don't mean moving the Python code into Postgres functions written in PL/Python (which you can do). I mean porting them to SQL. The simplest example is if your first version of your application just used SQL to fetch all the rows from the database and using a Python if-statement to find the rows you need: rewriting the Python if-statement as an SQL where-clause will be much faster. I'm sure few of you are doing something like that, but there are many, more complex examples like that, which I have learned and slowly replaced over the years.
- Furthermore SQL is just like any other language in that there are many ways to do the same thing, and some ways are a thousand times faster than others. People who only know basic SQL likely are writing inefficient apps. I have often rewritten queries to use a fraction of the memory and time they were using.
Moral of the story: Learn more SQL, instead of learning some avant-garde storage engine. Stepping deeper into PostgreSQL (and thereby locking yourself more and more into it) is often better than locking yourself deeper into Python, PHP, or whatever your middle language is. Who knows, you may want to switch out your middle language before you want to switch out Postgres!
I try to make Postgres my "application": put all your business logic in there. It may have an arcane "user-input interface" (SELECT . . . FROM sometable WHERE . . .) and a primitive "user-output interface" (plain tables) but that's where the middleware finally comes in, to cover the queries with checkboxes and buttons, and to decorate the output into various boxes, color, layout, and type, and maybe some nice images, graphs, and so on.