PostgreSQL is enough
gist.github.com
gist.github.com
But inevitably, as an application grows in complexity, you start to realize _why_ there's a stack, rather than just a single technology to rule them all. Trying to cram everything into Postgres (or lambdas, or S3, or firebase, or whatever other tech you're trying to consolidate on) starts to get really uncomfortable.
That said, sometimes stretching your existing tech is better than adding another layer to the stack. E.g. using postgres as a message queue has worked very well for me, and is much easier to maintain than having a totally separate message queue.
I think the main takeaway here is that postgres is wildly extensible as databases go, which makes it a really fun technology to build on.
Say I'm on your team, and you're an application developer, and you need a queue. If you're taking the "we're small, this queue is small, just do it in PG for now and see if we ever grow out of that" — that's fine. "Let's use SQS, it's a well-established thing for this and we're already in AWS" — that's fine, I know SQS too. I've seen both of these decisions get made. (And both worked: the PG queue was never grown out of, and generally SQS was easy to work with & reliable.)
But what I've also seen is "Let's introduce bespoke tech that nobody on the team, including the person introducing it, has experience in, for a queue that isn't even the main focus of what we're building" — this I'm less fine with. There needs to be a solid reason why we're doing that, and that we're going to get some real benefit, vs. something that the team does have experience in, like SQS or PG. Instead, this … thing … crashes on the regular, uses its own bespoke terminology, and you find out the documentation is … very empty. This does not make for a happy SRE.
Rabbit MQ and Elastic Search for a public facing site. The dedicated queue for workers to denormalize and push updates. To elastic. Why, because the $10k/month RDBMS servers couldn't handle the search load and were overly normalized. Definitely a hard sell.
I've also seen literally hundreds of lambda functions connecting to dozens of dynamo databases.
I'm firmly in the camp of use an RDBMS (PostgreSQL my first choice) for most things in most apps. A lot of times you can simply apply the lessons from other databases at scale in pg rather than something completely different.
I'm also more than okay leveraging a cloud's own MQ option, it's usually easy enough to swap out as/if needed.
You just need a little bit of appropriate index selection and ability to read the output of EXPLAIN ANALYZE to do so.
There are probably use cases where this doesn't hold, but I found in general that it is beneficial to stick to Postgres for this, especially if you want some ability to query using relations.
So over the years these juniors have repeatedly chosen different tech for their applications. Now the team maintains like 15-20 different apps and among them there's react, Vue, angular, svelte, jQuery, nextjs and more for frontends alone. Most use Episerver/Optimizely for backend but of course some genius wanted to try Sanity so now that's in the mix as well.
And it all reads like juniors built it. One app has an integration with a public api, they built a fairly large integration app with an integration db. This app is like 20k lines of code, much of which is dead code, and it gets data from the public api twice a day whereas the actual app using the data updates once a day and saves the result in its own Episerver db. So the entire thing results in more api traffic rather than less, the app itself could have just queried the api directly.
But they don't want me to do that, they just want me to fix the redundant integration thing when it breaks instead. Glad I'm not on that team any more.
I always start a professional project with technologies I am intimately familiar with - have used myself, or have theoretical knowledge of and access to someone with real experience.
There has never been a new shiny library/technology that would have saved more than 10% of the project time, in retrospect. But there have been many who would have cost 100% more.
But I do have projects which I finance myself (with myself as customer), and which do not have a real deadline. I can experiment on those. Call them "hobby" projects if you insist.
> Applied consistently, this logic would seem to preclude becoming familiar with anything.
Well, project requirements always rank higher, and many projects require some piece I am unfamiliar with (a new DB - e.g. MSSQL; a new programming language; etc). That means one does get familiar on a need basis , even applying this approach robotically.
If a project requires building the whole thing around a new shiny technology with few users and no successful examples I can intimately learn from ... I usually decline taking it.
That is the point of DDD,SoA,Clean, Hexagonal patterns.
Make a point to put structures and processes in place that encourage persistence ignorance in your business logic as the default and only violate that ideal where you have to.
That way if you outgrow SQL as a message bus you can change.
This mindset also works for adding functionality to legacy systems or breaking apart monoliths.
Choosing a default product to optimize for delivery is fine, claiming that one product fits all needs is not.
Psql does have limits when being used as a message or event bus, but it can be low risk if you prepare the system to change if/when you hit those limits.
Letting ACID concepts leak into the code is what tends to back organisations into a corner that is hard to get out of.
Obviously that isn't the Kool aid this site is selling. With this advice being particularly destructive unless you are intentionally building a monolith.
"Simplify: move code into database functions"
At least for any system that needs to grow.
>>> "Applied consistently, this logic would seem to preclude becoming familiar with anything."
As a general principle.
The last part in my parent comment is more of a "it was chucked over the fence, and it is now crashing, and nobody, not even the devs that chose it, know why".
I do have examples of what you describe, too: a dev I worked with introduced a geospatial DB to solve issues with geospatial queries being hard & slow in our then-database (RDS did not, at the time, support such queries) — so we went with the new thing. It used Redis's protocol, and was thus easy to get working with¹. But the dev that introduced it to the system was capable of explaining it, dealing with issues with it — to the extent of "upstream bugs that we encounter and produce workarounds", and otherwise being a lead for it. That new tech, managed in that way by a senior eng., was successful in what it sought to do.
The problematic parts/components/new introductions of new tech … never seem to have that. That's probably partly the problem: it's such an inherently non-technical issue at its heart. The exact thing almost doesn't matter.
> as long as it's one thing at a time
IME it's not. When there are problems, it's never just one new thing at a time.
> a rewrite will solve all problems
And the particular system I had in my mind while writing the parent post was, in fact, in the category of "a rewrite will solve all problems".
Some parts of the rewrite are doing alright, but especially compared to the prior system, there are just so. many. new. components. 2 new queue systems, new databases, etc. etc. So it's then hard to learn one, particularly without someone championing its success. It's another to self-learn and self-bootstrap on 6 or 8 new services.
¹(Tile38)
If you use a stack meant for a huge userbase (with all the tradeoffs that comes with it) but you're still trying to find market fit, you're in for a disappointment
Similarly, if you use a stack meant for smaller projects while having thousands of users relying on you, you're also in for a disappointment.
It's OK to make a choice in the beginning based on the current context and environment, and then change when it no longer makes sense. Doesn't even have to be "technical debt", just "the right choice at that moment".
Yep. And Postgres is a really good choice to start with. Plenty of people won't outgrow it. Those who do find it's not meeting some need will, by the time they need to replace it, have a really good understanding of what that replacement looks like in detail, rather than just some hand-wavy "web scale".
Still need to understand the data model and effects on queues though.
There are good reasons to go OLAP or graph for certain kinds of problems, but think carefully before adding more services and technologies because stuff has a tendency to go in easily but nothing ever leaves a project and you will inevitably end up with a bloated juggernaut that nobody can tame. And it's usually those people pushing the hardest for new technologies that are jumping into new projects when shit starts hitting the fan.
If a company survives long enough (or cough government), a substantial and ever increasing amount of time, money and sec/ops effort will go into those dependencies and complexity cruft.
Most systems are still going to need Redis involved just as a coordinator for other pub/sub related work unless you're using a stack that can handle it some other way (looking at BEAM here).
But there are always going to be scenarios as an application grows where you'll find a need to scale specific pieces. Otherwise though, PostgreSQL by itself can get you very, very far.
It's also worth noting that by using PG as a message queue, you can do something that's nearly impossible with other queues - transactionally enqueue tasks with your database operations. This can dramatically simplify failure logic.
On the other hand, it also means replacing your message queue with something more scalable is no longer a simple drop-in solution. But that's work you might never have to do.
It subscribes to the Postgres WAL and let you do the same sort of thing you can do with listen/notify, but without the drawbacks like need for triggers or character limits.
(To be clear I do see other downsides to listen/notify and I think WalEx makes a lot of sense, I just don't understand this particular example.)
And I should have mentioned it before, but we have an open call for speakers for the Carolina Code Conference. This would make for an interesting talk I think.
I've ready a lot of reports (on here) that it comes with several unexpected footguns if you really lean on it though.
Some applications never grow that much
See, now that we're profitable, we're gonna become a _scale up_, go international, hire 20 developers, turn everything into microservices, rewrite the UI our customers love, _not_ hire more customer service, get more investors, get pressured by investors, hire extra c-levels, lay-off 25 developers and the remaining customer service, write a wonderful journey post.
The future is so bright!
Building an app with no third party dependencies seems impossible nowadays. At least if you plan to compete.
Niches where you can get away with that are limited, not just by technical challenges but because large parts of the social ecosystem of IT won't like that. But they do exist. There's also still things that aren't webapps _at all_, there's software that has to run without internet access. It's all far apart and often requires specialized knowledge, but it exists.
Don't use it anymore than you have to for your application. Other than network IO it's the slowest part of your stack.
Take any computation you can do in SQL like "select sum(..) ...". Should you do that in the database, or move each item over the network and sum them in the backend?
Summing in the database uses a lot less resources FOR THE DB than the additional load the DB would get from "offloading" this to backend.
More complex operations would typically also use 10x-100x less resources if you operate on sets and amortize the B-tree lookups over 1000 items.
The answer is "it depends" and "understand what you are doing"; nothing about it is "inevitable".
Trying to avoid computing in the DB is a nice way of thinking you maxed out the DB ...on 10% of what it should be capable of.
Rendering html, caching, parsing api responses, sending emails, background jobs: Nope.
Basically, use the database for what it’s good at, no more.
Architecturally, there are other cases besides message queues where there's no reason for introducing another layer in the stack, once you have a database, other than just because SQL isn't anybody's favorite programming language. And that's the real reason there's a stack.
Out of the widely underrated AWS services include SNS and SES and they are not a bad choice even if you're not using AWS for compute and storage.
Using SQS for a queue rather than my already-existing Postgres means that I have to:
- Write a whole bunch of IaC, figuring out the correct access policies - Set up monitoring: figure out how to monitor, write some more IaC - Worry about access control: I just increased the attack surface of my application - Wire it up in my application so that I can connect to SQS - Understand how SQS works, how to use its API
It's often worth it, but adding an additional moving piece into your infra is always a lot of added cognitive load.
From there, Postgres ended up being our relational storage for the platform. It is a wonderful combination of supporting teams by being somewhat strict (in a flexible way) as well as supporting a large variety of use cases. And after some grumbling (because some teams had to migrate off of SQL Server, or off of MariaDB, and data migrations were a bit spicy), agreement is growing that it's a good decision to commit on a DB like this.
We as the DB-Operators are accumulating a lot of experience running this lady and supporting the more demanding teams. And a lot of other teams can benefit from this, because many of the smaller applications either don't cause enough load on the Postgres Clusters to be even noticeable or we and the trailblazer teams have seen many of their problems already and can offer internally proven and understood solutions.
And like this, we offer a relational storage, file storage, object storage and queues and that seems to be enough for a lot of applications. We're only now adding in Opensearch after a few years as a service now for search, vector storage and similar use cases.
For the majority of apps, just doing basic CRUD with a handful of data types, is it that hard to just move to another DB? Especially if you're in framework land with an ORM that abstracts some of the differences, since your app code will largely stay the same.
Sometimes a flexible tool fits the bill well. Sometimes a specialized tool does. It's all about finding that balance.
Thank you for coming to my TED talk.
The problem is, at scale, Postgres isn't the answer to everything. Each of the workloads one can put in Postgres start to grow into very specific requirements, you need to isolate systems to get independent scaling and resilience, etc. At this point, you need a stack of specialized solutions for each requirement, and that's where Postgres starts to no longer be enough.
There is a movement to build a Postgres version of most components on the stack (we are a part of it), and that might be a world where you can use Postgres at scale for everything. But really, each solution becomes quite a bit more than Postgres, and I doubt there will be a Postgres-based solution for every component of the stack.
Is there an equivalent for postgres?
If Michael Jackson rose from the dead to host the Olympics opening ceremony and there were 2B tweets/second about it, then postgres on a single server isn't going to scale.
A crud app with 5-digit requests/second? It can do that. I'm sure it can do a lot more, but I've only ever played with performance tuning on weak hardware.
Visa is apparently capable of a 5-digit transaction throughput ("more than 65,000")[0] for a sense of what kind of system reaches even that scale. Their average throughput is more like 9k transctions/second[1].
[0] https://usa.visa.com/solutions/crypto/deep-dive-on-solana.ht...
[1] PDF. 276.3B/year ~ 8.8k/s: https://usa.visa.com/dam/VCOM/global/about-visa/documents/ab...
(still, modern postgresql can easily scale to 10,000s (plural) of TPS on a single big server, especially if you setup read replicas for reporting)
For similar scale comparisons, reddit gets ~200 comments/second peak. Wikimedia gets ~20 edits/second and 1-200k pageviews/second (their grafana is public, but I won't link it since it's probably rude to drive traffic to it).
interesting re reddit, that's really tiny! but again, I'm even more curious about how many underlying TPS this turns into, net of rules firing, notifications and of course bots that read and analyze this comment, etc. Still, this isn't a scaling issue because all of this stuff can be done async on read replicas, which means approx unlimited scale in a single-database-under-management (e.g. here's this particular comment ID, wait for it)
Slack experiences 300K write QPS: https://slack.engineering/scaling-datastores-at-slack-with-v...
I agree with others that a good simplification is "how far can you get with the biggest single AWS instance"? And the answer is really far, for many common values of the above variables.
That being said, if your work load is more OLAP than OLTP, and especially if your workload needs to be real-time, Postgres will begin to give you suboptimal performance without maxing-out i/o and memory usage. Hence, "it really depends on your workload", and hence why you see it's common to "pair" Postgres with technologies like Clickhouse (OLAP, immutable, real-time), RabbitMQ/Kafka/Redis (real-time, write-heavy, persistence secondary to throughput).
I don't know that there is a canonical solution for scaling Postgres data for a single database across an arbitrary number of servers.
I know there is CockroachDB which scales almost limitlessly, and supports Postgres client protocol, so you can call it from any language that has a Postgres client library.
In principle, seems like it should work to allow large scale distribution across many servers. But the actual management of replicas and deciding which servers to place partitions, redistributing when new servers are added, etc. could lead to a massive amount of operational overhead.
Main downside is that you either have to either self-manage the deployment in AWS EC2 or use Azure's AWS-RDS-equivalent (CitusData was acquired by MS years ago).
FWIW, I've heard that people using Azure's solution are pretty satisfied with it, but if you're 100% on AWS going outside that fold at all might be a con for you.
If you are interested in partitioning in an OLAP scenario, this will soon be coming to pg_analytics, and some other Postgres OLAP providers like Timescale offer it already
I've gone far out of my way not to use Elasticsearch and push Postgres as far as as I can in my SaaS because I don't want the operational overhead.
I don't understand this. PostgreSQL is ALSO dead simple to get going, either locally or in production. Why not just start off at 90%?
I mean, I get there are a lot of use cases where sqlite is the better choice (and I've used sqlite multiple times over the years, including in my most recent gig), but why in general?
It's obviously a lot simpler to just have a file, than to have a server that needs to be connected to, as long as we're still talking about running things on regular computers.
They difference in setup time is negligible so I'm not sure why people keep bringing it up as a reason to choose sqlite over PostgreSQL.
For instance, "deployable inside a customer application" is an actual requirement that would make me loath to pick PostgreSQL.
"Needs to be accessible, with redundancy, across multiple AWS zones" would make me very reluctant to pick sqlite.
Neither of these decisions involve how easy it is to set up.
It's like choosing between a sportbike and dump truck and focusing on how easy the sportbike is to haul around in the back of a pickup truck.
But postgres setup, at least the package managers on Linux, will by default, create a user called postgres, and lock out anyone else who isn't this user from doing anything. Yeah you can sudo to get psql etc. easily, but that doesn't help your programs which are running as different users. You have to edit a config file to get to work, and I never figured out how to get to work with domain sockets and not TCP
You just have to call the basic "createuser" CLI (or equivalent CREATE ROLE SQL) out of the postgres superuser account to create database users that match local Linux usernames. Then the ident-based authentication matches the client process username to the database role of the same name.
How many projects start with these requirements?
But most projects don’t even have customers when they start, let alone large quantities of their data and legal requirements for guaranteed availability.
In a world fueled by cheap money and expensive dreams, you'd be surprised.
I'm not saying it's hard to set up Postgres locally, but sqlite is a single binary with almost no dependencies and no config, easily buildable from source for every platform you can think of. You can grab a single file from sqlite.org, and you're all set. Setting up Postgres is much more complicated in comparison (while still pretty simple in absolute terms - but starting with a relatively simpler tool doesn't seem like a bad strategy.)
Replacing the DB before it gets any actual data inserted into it solves this problem. You just switch to Postgres before you go anywhere beyond staging, at the latest - in practice, you need Postgres-exclusive functionality sooner than that in many cases, anyway. Even when that happens, you might still prefer having SQLite around as an in-memory DB in unit tests. The Postgres-specific methods are pretty rare, and you can mock them out, enjoying 100x faster setup and teardown in tests that don't need those methods (with big test suites, this quickly becomes important).
Unless you really want to use Postgres for everything like the Gist here suggests, the DB is just a normal component, and some degree of flexibility in which kind of component you use for specific purposes is convenient.
It's also good practice for designing reasonably cross-database compatible schemas.
I already hear you saying that you know of a library that provides a perfect abstraction to hide all those details and complexities, making the choice between Postgres and SQLite just a flip of a switch away. Great! But then what does Postgres bring to the table for you to choose it over SQLite? If you truly prove a need for it in the future for whatever reason, all you need to do is update the configuration.
> In a client/server database, each SQL statement requires a message round-trip from the application to the database server and back to the application. Doing over 200 round-trip messages, sequentially, can be a serious performance drag.
While the above is true on its own, this is _not_ the typical definition of n+1. The n+1 problem is caused by poor schema design, badly-written queries, ORM, or a combination of these. If you have two tables with N rows, and your queries consist of "SELECT id FROM foo; SELECT * FROM bar WHERE id = foo.id_1...", that is not the fault of the DB, that is the fault of you (or perhaps your ORM) for not writing a JOIN.
It's your fault for not writing a join if you need a join. But that's not where the n+1 problem comes into play.
Often in the real world you need tree-like structures, which are fundamentally not able to be represented by a table/relation. No amount of joining can produce anything other than a table/relation. The n+1 problem is introduced when you try to build those types of structures from tables/relations.
A join is part of one possible hack to workaround to the problem, but not the mathematically ideal solution. Given an idealized database, many queries is the proper solution to the problem. Of course, an idealized database doesn't exist, so we have to deal with the constraints of reality. This, in the case of Postgres, means moving database logic into the application. But that complicates the application significantly, having to take on the role that the database should be playing.
But as far as SQLite goes, for all practical purposes you can think of it as an ideal database as it pertains to this particular issue. This means you don't have to move that database logic into your application, simplifying things greatly.
Of course, SQLite certainly isn't ideal in every way. Tradeoffs, as always. But as far as picking the tradeoffs you are willing to accept for the typical "MVP", SQLite chooses some pretty good defaults.
I don't know how precisely strict you expect a tree to be in RDBMS, but this [0] is as close as I can get. It has a hierarchy of product --> entity --> category --> item, with leafs along the way. In this example, I added two bands (Dream Theater [with their additional early name of Majesty], and Tool), along with their members (correctly assigning artists to the eras), and selected three albums: Tool's Undertow, with both CD and Vinyl releases, and Dream Theater's Train of Thought, and A Dramatic Turn of Events.
The included query in the gist returns all available information about the albums present in a single query. No n+1.
The inserts could likely be improved (for example, if you were doing these from an application, you could save IDs and then immediately reuse them; technically you could do that in pl/pgsql, but ugh), but they do work.
This is also set up to model books in much the same way, but I didn't add any.
> A join is part of one possible hack to workaround to the problem, but not the mathematically ideal solution.
Joins are not a "hack," they are an integral part of the relational model.
[0]: https://gist.github.com/stephanGarland/ec2d0f0bb54161898df66...
Yes, joins are an essential part of the relational model, but we're clearly not talking about the relational model. The n+1 problem rears its ugly head when you don't have a relational model – when you have a tree-like model instead.
> The included query in the gist returns all available information about the albums present in a single query. No n+1.
No n+1, but then you're stuck with tables/relations, which are decidedly not in a tree-like shape.
You can move database logic into your application to turn tables into trees, but then you have a whole lot of extra complexity to contend with. Needlessly so in the typical case since you can just use SQLite instead... Unless you have a really strong case otherwise, it's best to leave database work for databases. After all, if you want your application to do the database work, what do you need SQLite or Postgres for?
Of course, as always, tradeoffs have to be made. Sometimes it is better to put database logic in your application to make gains elsewhere. But for the typical greenfield application that hasn't even proven that users want to use it yet, added complexity in the application layer is probably not a good trade. At least not in the typical case.
You can easily get hierarchical output format from Postgres with its JSON or XML aggregate functions.
You can have almost all benefits of an embedded database by embedding your application in the database.
Just change perspective and stop treating Postgres (or any other advanced RDBMS) as a dumb data store — start using it as a computing platform instead.
It's a very important distinction because, as you say, there are problem domains where you can't just "join the problem away".
If you additionally need the result of that query to be hierarchical itself, then you can easily have PG generate JSON for you.
A trivial amount of lateral joins plus JSON aggregates will give you a relation with on record, containing a nested JSON value with a perfectly adequate tree structure, with perfectly adequate performance, in databases that support these operations.
There are solutions to these problems. One only needs to willingness to accept them.
?
N+1 Queries Are Not A Problem With SQLite
https://www.sqlite.org/np1queryprob.html#:~:text=N%2B1%20Que....
Even with a WAL or some kind of homegrown spooling, you're going to be limited by the rate at which one thread can ingest that data into the database.
One could always shard across multiple SQLite databases, but are you going to scale the number of shards with the number of concurrent write requests? If not, SQLite won't work. And if you do plan on this, you're in for a world of headaches instead of using a database that does concurrency on its own.
Don't get me wrong; SQLite is great for a lot of things. And I know it's nice to not have to deal with the "state" of an actual database application that needs to be running, especially if you're not an "infrastructure" team, but there's good reasons they're ubiquitous and so highly regarded.
That will carry most early stage applications really far.
It really feels like the DB industry has taken a huge step backward from the promise of SQL. Switching from Postgres to SQLite is easy because the underlying queries are at least similar. But as soon as you introduce embeddings, every system is totally different (and often changing rapidly).
Specialized vector indexes become important when you have a large number of vectors, but the reality of software is that it is unlikely that your application will ever be used at all, let alone reach a scale where you start to hurt. Computers are really fast. You can go a long way with not-perfectly-optimized solutions.
Once you have proven that users actually want to use your product and see growth on the horizon to where optimization becomes necessary, then you can swap in a dedicated vector solution as needed, which may include using a vector plugin for SQLite. The vector databases you want to use may or may not use SQL, but the APIs are never that much different. Instead of one line of SQL to support a different implementation you might have to update 5 lines of code to use their API, but we're not exactly climbing mountains here.
Know your problem inside and out before making any technical choices, of course.
There are plenty of proprietary databases and APIs, but now you're taking on a dependency and assuming a certain amount of risk.
Be the change you want to see, I suppose. No doubt convergence will come, but it is still early days. Six months ago, most developers didn't even know what a vector database is, let alone consider it something to add to their stack.
It took SQL well into the 1990s to fully solidify itself as "the standard" for relational querying. Even PostgreSQL itself was started under the name POSTGRES and was designed to use QUEL, only moving over to SQL much later in life when it was clear that was the way things were going. These things can take time.
IBM had SQL in their database product in 1981, Oracle had it by v4 in 1984, ANSI picked SQL as its standard that same year, and completed the first version by 1986.
Some time scientists say that the 1980s occurred before "well into the 1990s" but I mean, who can really say, right?
Of course, if there's a shortcoming of sqlite that I know I need right out of the gate, that would be a situation where I start with postgres.
https://supabase.com/docs/guides/database/extensions/pgvecto...
This is the new kid in town so you would see soon all major SQL dbs will support vector. However, any serious user, O(10M) vectors or above, would still require a dedicated vector db for performance reasons.
I come from an industry that heavily uses custom binary file formats. And I'm still bewildered by the world of databases. They seem to solve many issues on the surface, but not really in pratice. The heavy limitations on data types, the update disasters, the incompatibility between different SQL engines etc all make it seem like an awful idea. I get the interop benefits, and maybe with extreme data volumes. But for anything else, what's the point? Genuinely asking
I believe you haven't had a chance to work on problems that require an actual database. Multi-user access, ACID support, unified API (odbc/jdbc), common query language... all of these would require many man-years to set properly with a custom solution.
> the update disasters, the incompatibility between different SQL engines etc all make it seem like an awful idea
What update disasters? If you meant by updating database versions, these aren't things you do frequently because the database is expected to be running 24/7. But Postgres and Mysql are already rock solid here. Wrt SQL engine incompatibilities, you usually set with a single database vendor in practice. If you suddenly start to switch databases in the middle of the project, something needs to be fixed with the process design, not database.
But the updating I would have expected to go more smoothly. If you make a point of using a software dedicated to managing data, I sure as hell would expect an update to go so smooth that I don't have to worry about or even notice it. In reality updates more often than not seem to come with undocumented errors. That is a constant source of frustration for me.
You are using it :) Reboot the server where the database runs or suddenly cut off the connection. Unless you have ACID-compatible storage, you'll have malformed data.
Plan for the future and use a database from the start. When your project/company expands and starts to use multiple applications/services (and that inevitably happens), you'll see (one of) the benefits of the database.
> But the updating I would have expected to go more smoothly
I'm not sure what you are talking about. Database updates are one of the smoothest (critical) software updates you'll find, assuming the database has a good track record.
You mention further down a "mystery box of performance", but if you understand what data structures it's using and how it uses them, then it's generally pretty straightforward. Mostly you can reason about what indices are available and how trees work (and e.g. whether it can walk two trees side-by-side to join data) to know what query plan it should make, and you can ask it to tell you what plan it makes and which indices it used. Likewise, if you have a query plan you want it to run (loop over this table, then use this column to look up in this table, etc.), you'll know what indices are needed to support that plan.
If people struggle with using a database correctly, they're really going to struggle with using something like a b-tree in a way where you don't corrupt your data in the event of a power loss or crash, or in a way where multiple threads don't clobber each other's updates or create weird in-between states (or you just use a global lock, but then you lose performance).
If you learn about the internals of a thing, especially when your background is in lower level dev like C++, then the use-cases are more obvious: you use the thing whenever you would've done what it does internally, but it gives you that functionality off-the-shelf and wrapped up in a way where you can write business logic without getting bogged down in details of tree-traversal and stuff. Once you get comfortable with it, you expand that to using it when you might not have done things exactly that way, but eh it's close enough and lower effort.
Sometimes truly equivalent SQL statements will be faster just because the optimizer is not perfect. e.g. I've had cases where I had a templated query with a GROUP BY some id, and then other code added on a HAVING for that same id, and I know it should be algebraically valid to push the HAVING into a WHERE so it runs before the GROUP BY (and filtering before the aggregation would be much cheaper), but mysql just didn't have that optimization. Dealing with this kind of thing can be annoying.
Other times you might have something like a compound index, and you might add a WHERE that you know for business reasons is redundant because the thing you're trying to filter on is not the first column in the index. Understanding why that works comes down to understanding what a compound index "looks like" as a tree. One thing that I imagine a database from the future could do is let you define logical implications like that (e.g. StateOrProvince = California implies Country = United States, or maybe deleted_at >= modified_at >= created_at, or a.id > b.id implies a.created_at > b.created_at) that it could use for query planning.
But in general, if you learn how it works, and then think of it as a way to not have to write that functionality yourself (but understand that the trade-off is some rigidity in your ability to customize it), it will make more sense, and you'll be able to become one of those wizards that just knows how to rewrite a query to something that ought to be the same, but is for some reason much faster.
Another key problem they solve is separating the logical representation from the on disk representation. You can evolve and and optimize storage without breaking anything.
The other problem with files is that you have a disconnect between in memory and on disk. You have to constantly serialize and deserialize. Sqlite has quite a bit of info on this: https://www.sqlite.org/appfileformat.html
The general idea is that its such a PITA to deal with all that so just let a database do it, and of course the consequence is an often leaky abstraction because computers suck.
Because having hundreds or thousands of concurrent file handles across a data center is kind of hard.
Still then, a database will improve your data access, validate your data, and provide interop in case you need to use the file with different software.
It's also a very high quality serialization library, that replaces one of the most vulnerability-enabling layers of your software. (But then, I've just read you use Oracle, so maybe forget that one.)
Only a fool would put more trust in Oracle.
If you don’t mind a bit of unsolicited advice: run.
You have to be really good at understanding how storage works, have a lot of time and resources to develop your own program to beat something like PostgreSQL. They have zillions of man-hours on you when you start, experience and knowledge. It's not impossible that you could find a case where a bespoke format would beat an established storage product, but over a range of cases, you most likely won't.
And, of course, there's a convenience aspect. Outside of niche technologies, SQL offers the richest language for data description and operations on data.
As for the inconsistencies between SQL implementations: in practice, it matters very little: most programs will never migrate between different SQL implementations anyways.
As for upgrades: it's a doubly-edged sword. You get a very expressive data format and it's not surprising that it's hard to upgrade. But, try to match the abilities of SQL in your own format, and you'll probably find out that it's hard to have generic tools for upgrading it too.
None of this means that you cannot do better than SQL databases. It'd be ridiculous to think there could be a way to prove that SQL is somehow the best we can get. It's just that it's very hard to do better. Especially if you want a universal tool
For example, when making a bank transfer, the database guarantees that the update doesn't happen twice by accident.
You can check these links:
About data types, what limitations are you referring to? jsonb is well supported in many dbs and throwing random large binary blobs in the middle with your normal data is a bad idea with or without a relational database.
I can see the appeal to replace noSQL solutions though, a lot of people are using S3 (and other storage solutions) as a makeshift database lately
I would happily work on query engines, it's just I don't recommend using bespoke ones unless absolutely necessary.
I think you'll come around eventually.
Excerpt from the motivaion of "PostgreSQL HTTP Client":
> Wouldn't it be nice to be able to write a trigger that called a web service?
Rather you than me.
> Rather you than me.
Yes, don't do that. Instead consume notification streams or logical replication streams and act on those -- sure, you'll now have an eventually consistent system, but you won't have a web service keeping your transactions from making progress. You don't want to engage in blocking I/O in your triggers.
Migrations?
There are commands in most postgres clients, even psql, to view the _currently_ defined functions... but when you go to debug those you will have to look through migrations to see how the function came into it's current state... bisecting through history here is not very useful since each change to the function is a new file. I think this can be fixed though and made much easier, it's just not there yet.
In general I don't think the developer tooling is up to par to push very much of your application logic into postgres itself. I recommend using triggers for consistency and validation or table-local updates (ie: timestamps, audit logs) but keep process-oriented behaviour (iow: when this happens, then that, else this, wait for call and insert here, etc) in the application layer (chasing a cascade of triggers is not fun and quite annoying).
... all that being said, you can do unit testing in Postgres. And there is decent support for languages other than pgSQL (ie: javascript, ocaml, haskell, python, etc). It's possible to build dev tooling that would be suitable to make Postgres itself an application development platform. I'm not aware of anyone who has done it yet.
https://www.jetbrains.com/help/idea/database-tool-window.htm...
Popular tooling like Phoenix, Hasura, etc have good built in migration stories.
https://www.bytebase.com looks really promising.
Hover, I do struggle with one big issue: changing database logic (views, functions, etc) that has other logic dependent on it. This seems like a solvable problem.
[1] https://github.com/flyway/flyway
[2] https://java.testcontainers.org/modules/databases/postgres/
Basically, you store each migration in a file, and you "squish" the migration history down to a table definition once you've decided you're happy with the change and it's been affected across all the different deployment environments.
It's not perfect, but it works reasonably well.
Start after college and backend web dev was fully in scripting language, Python or Ruby, and ORMs that completely fogged where any of the data was stored. Rails and ActiveRecord is so good at shrouding the database to the point where you type commands that create databases and you never see them. Classes are written to describe what we want the data to look like and poof! SQL commands are created to build the schema that we never need to see. On this end of the spectrum, the scripting language will stay the same, but we want to be agnostic to where the data is stored.
On on the other end of the spectrum, Postgres is enough. More than enough. Like in the link, it can do all the tasks you ever care about. The code you're writing for the backend / data is about data, not about the script. We care where it's stored, that it's clear the structure, the reads and updates are efficient. We can write all statements in SQL to create tables, functions, trigger, queues, and efficient read queries with indexes to make the data come back to the scripting language in the exact form that's wanted. On this end, we know and optimize how the data is stored, agnostic to the scripting language that uses the data.
I went from the first end of the spectrum to the second. Everything can be done in Postgres. Audibility, clarity, efficiency is much better there than in Python, is my position. The only thing holding it back is that people don't see development from the data side yet, and if you're deciding on tech, it's not easy to use a tech that people don't have as good of development ability yet. There are no Postgres bootcamps right now.
But There's more and more adoption of this I'm seeing, and the money and development of Postgres leads me to trust that it'll be around a very long time, only getting better. Posts about the power of databases, Postgres and some SQLite for example are becoming more and more common. It's a cool change to follow and watch grow.
Not on my bubble, it has been fully in .NET and Java since 2001, with exception of a couple of services written in C++.
Postgres, Redis, S3
Hasn't steered my wrong yet. Every once in a while I'm tempted to try to use Postgres for Pub/Sub but then I realize that I need Redis for caching and sidekiq anyways, and Redis is amazing too, so why bother.
Ran a single postgres instance with multi-tenant SaaS product that crossed 4B records in a few tables, even with partitions and all the optimization in the world, it still hurts to have one massive database.
We still got bought tho, so I will agree its enough
Separate per customer has a lot of advantages (especially around customer security and things like deletion) but you can also shard by customer right from the beginning; customer #1 in the "odd" shard, customer #2 in the "even" shard etc. Switching to database per customer if that is working well is relatively easy so you're future proofed both ways.
I feel like this is going to solve a lot of saas businesses problems.
Using postgres made me realize that many of the people I work with had no idea what an ambiguous group by is, because unlike mysql, postgres will never, ever let you do an ambiguous group by. Running into many different things like that over the years has really made me realize how nice postgres is.
You can emulate it in Postgres with DISTINCT ON if desired, and as long as you also ORDER BY something that makes sense for your query, it should work similarly.
The biggest problem with depending on Postgres is it's a large complex monolith. Any problem you have only has two possible solutions: 1) spend a ton of time trying to twist yourself into a more pretzely shape to get it to do what you want, or 2) replace that thing you wanted with some external thing.
Both of these waste valuable time on something that isn't providing any business value. They also are entirely preventable/avoidable, by simply not putting all your eggs into one basket.
Basically the whole "just use Postgres" philosophy boils down to: I don't want to learn new things, and I just like making things from scratch with custom code. That's great if you're an engineer and you want job security. But it's bad strategy, engineering, and use of time and resources. Anyone who approves of using Postgres in this way should not be in charge of engineering decisions.
I've learned a bunch of new things (running all the things - not fun, putting biz logic in the application layer - slow).
Putting them in the database is simply simpler and more performant. And it requires less code and time to develop (and maintain).
It also has the benefit that it's a stable solution that has been around for decades, and will likely be around for just as long. Meaning that you won't have to constantly rewrite and update things to keep up with whatever the developers of your favorite framework decided is the Right Way to do things this year.
> The Solution: Roll your own: For us the solution was fairly simple: don’t use a managed database services and roll our own infrastructure on EC2.
I've done the same running PostgreSQL on EC2 instances with NVMe local raid storage. It's very fast, but then you're responsible for its uptime, updates, backups, etc. That setup was used for performance sensitive but less critical data.
The only important thing though for me is making sure I'd spend some time on writing a nice API wrapper on my end, and only call that. When I really need to use Redis, all that should be needed is changing the implementation inside your wrappers, and testing the migration very well.
People are surprised how long you can delay to make a technical decision, until it becomes obvious.
the relational ACID model is overkill for mostly-read data warehouse and verticality helps; streaming is different; graph dbs are different.
Postgres may not be 'all you need', it will take you pretty far though, maybe it's 'all you need most of the time'. 60%+ of the time it works every time.
And with Google searches the vast majority of results are going the opposite way: creating a GraphQL API from a real SQL schema.
That being said, I do fully agree that things like Redis, ElasticSearch, RabbitMQ, and Kafka are probably unnecessary in a large number of projects that decide to add them to the stack if those projects are also already using PostgreSQL as a database.
Hasura, a similar tool - has really nice migration tooling inspired by Rails.
But your point is well taken, even with their nice migrations, I find myself struggling with changes to objects with dependencies (you have to drop all dependents and recreate which is a pita).
The way I set everything up, there's one schema containing just the bare tables and data, and then everything else (stuff that PostGraphile generates and all of our stored procedures with business logic) are in a separate schema. That way the migration process is a shell script that essentially 1. drops the entire non-table schema, 2. runs through all of the SQL files that define the application (there's some custom logic to control dependencies by looking for a `-- PRIORITY N` comment on the first line of the source files), 3. attach all the triggers to the tables (since they live in the schema that was dropped). It means we didn't have to worry about figuring out what all needs to be dropped to make a change because literally everything is dropped and recreated from scratch each time we deploy a new version.
I'm speaking in present tense because this application still exists, we've just (more or less) frozen the code base and are slowly migrating all its functionality piecemeal into a ASP.NET Core WebAPI project.
I think TimescaleDB works well up to the single-digit terabyte range (according to various sources). If you need a solution in the multi-digit terabyte or petabyte range, then you probably need something like a distributed VictoriaMetrics setup.
Of course! The question is only in your requirements. Keeping a simple counter with limited cardinality should work just great. But nowadays monitoring is much more serious than that. For monitoring k8s clusters the average ingestion rate of metrics per second varies from 100K to 2Mil. I don't know if, resource-wise, it would be a right decision to use Postgres for storing this.
So when requirements are high, and they are for real-time infrastructure and applications monitoring, it is better to consider something like ClickHouse (for people familiar with Postgres) or VictoriaMetrics (for people familiar with Prometheus).
Redis is great. This gist is more about how it's up to you how much you centralize on postgres. But overall being able to offload from the database has value, so "PostgreSQL is Enough" should not read as "You shouldn't need more than PostgreSQL"
Metrics in an unlogged table could be great if you want to query those metrics against existing data. It depends
A working set raises another set of issues: how do the pieces relate? What is the proper role of each technology?
This gist is extremely useful to me both in helping me to understand more of what I need to know. The article https://sive.rs/pg was very helpful as one way to think about how postgresql could and should fit in.
For vector retrieval, going with a database like Milvus, designed for vectors, is usually more efficient and cost-effective. Similar principles apply across domains. Is there any vectordb under PG format?
What if we've got a deeply customized distributed vector search service on PostgreSQL, that's impressive!
I haven't gotten a chance to try out Latern[1] yet, but have heard some good things[2].
[0] https://github.com/pgvector/pgvector
[1] https://github.com/lanterndata/lantern
[2] https://tembo.io/blog/postgres-vector-search-pgvector-and-la...
My instinct is that the output won't be simpler at all, but a big twisty web of varying parts and competing use cases, all extremely hard to discover. To me, having two things that do something distinct is simpler, and ironically, Rich Hickey says this exact thing in Simplicity Matters, which is quoted in the article.
I completely agree. Inside the DB, it's hard to argue about performance benefits in a lot of situations. That said, debugging, testing, and developing are so much harder. I love to have a well written bit of code that I can integration test as well as mock up and use for unit tests with other components of my application. It's much trickier to do that in the DB. Sometimes it is a lot more performant, and that's a situation you may want to consider moving towards, but I would try to stay away from it for as long as the performance gain isn't huge.
Not that major database versions are any simpler even if you just stick to CRUD for databases.
I mean, even LTS versions have EOL dates, so even at the least frequent, you'll need to do upgrades around every 5 years or so.
If you can run your own infra – at least on an EC2 level – you can do things like Citus [0] for Postgres, which is about as close to "just add database nodes" as you'll get.
Ultimately using something like Postgres in 2024 is just an on-ramp for expensive managed cloud database services, which is probably why it's promoted so much.
I think what you're actually observing is simply that Postgres is by far the most vendor-neutral DBMS (/API) available, and therefore the volume of conversation & marketing around it stacks up very disproportionately.
In contrast, asides from MySQL all other DBMS options require getting invested in ~one company and relying entirely on the whims & fortunes of their commercial support organisation.
A relevant article and comment thread: https://news.ycombinator.com/item?id=31425872
It's a fork of postgresql with distributed architecture, so you can add and remove nodes as you wish. And it's free if you self-host.
If anyone has experience with YugabyteDB (or any other multi-master PostgreSQL like DBs please let me know!)
To give a concrete example on the first item in the gist: if i need periodic jobs - and all the operational headaches that go with (rerunning, ordering, dependencies, logging, yada yada...) - is postgres the right tool for the job?
It CAN be, but for most people in most circumstances, it's probably not.
We have both Mongo and PG. PG is much simpler and I would love to ditch Mongo for simplicity.
Only thing I would need is a "dumb" compressed jsonb column. No updates, no queries, only insert, select, delete.
And the same 80-90% compression without maintenance as Mongo on highly repetitive json keys.
For example, when using PG pub/sub, you will run out of connections quick.
Generally, all DBMS needs a smart self adjusting query killer. Without it, one bad query will ruin it for everyone.
Supavisor connection pooler: https://github.com/supabase/supavisor
Maybe for databases queried directly by users who also need to mix in API responses, and you're wrapping this all up in triggers or some other stored procedure cause you really want to use Postgres without some controller written in Python or JS.
https://github.com/Logflare/logflare/blob/main/lib/logflare/...
Need to make a lib out of this!!
Actually you should be using an embedded database.
Half-joking because there's like, tons of infrastructure, both literal and theoretical, that you're gonna miss out, and because I'm not sure we have on-disk standards (so less "good software" than "popular software") other than sqlite and libdb, both of which have some issues that make me hesitate before saying "use this."
But often the reason you're ignoring 90% of your RDBMS is because you for some (good or bad) reason want it elsewhere, and the built-in stuff can even get in the way a bit. And this approach means you no longer have to worry about how to version the stored procedures or whatever.
In a way, this is the same take as that gist, but inside out - instead of putting all your code inside Postgres, put Postgres inside your code.
This is how this article looks like to me.
It's truly a workhorse, it's comparatively a pleasure to work with, and its extensibility makes it useful in places some other DBs aren't suitable for.
But a relational DB isn't right for every workload.
While sometimes true, I'll counter that it's more common that the application was not truly designed for a relational DB, and instead was designed for reading and storing JSON.
JSON performance (or JSONB, it really doesn't matter) is abysmal compared to more traditional column types, especially in Postgres due to TOAST.
Properly normalized tables (ideally out to 5NF, but at least 3NF) are what RDBMS were designed to deal with, and are how you don't wind up with shit performance and referential integrity issues.
But that's a good point regarding column order, sometimes I find myself wanting to do that.