I often go down rabbit holes like this, trying to collapse and simplify the application stack.
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.
The same argument of UNIX design patterns (Single responsibility, well defined interfaces and communication protocals) vs Monolithic design patterns comes up a lot. I think that its mainly because both are effective at producing products, its just that they both have downsides.
I read a meme yesterday about how you can just interject "it's all about finding that balance" into any meeting and people will just agree with you. I'm gonna say it here.
Sometimes a flexible tool fits the bill well. Sometimes a specialized tool does. It's all about finding that balance.
Just noting that sometimes one can do both: seperate postgres DBs/clusters for different use-case, seperate instances of a web server for TLS termination, caching, routing/rewriting, Ed:static asset serving. Benefit is orderly architecture, and fewer different dependencies.
I think a lot of the industry struggles with the idea that maybe there is no "one size fits all", and what makes sense when you're a one person company with 100 customer probably doesn't make sense when you're a 1000 people company with millions of customers.
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".
> 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.
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".
True enough and with modern hardware that barrier is relatively high. IIRC Stack overflow was handling several million users in a single database server over a decade ago... We've got over 8x the compute power and memory now.
Still need to understand the data model and effects on queues though.
Because it's incorrect. If you have any non-postgres side-effect, you can't have exactly-once (unless you do 2PC or something like that). There isn't any technology that gives you "exactly once" in the general case.
Anyone who has ever selected queue service/product will understand it like that.
Because thats one of the most prominent features that gets highlighted by those products:
SQS Standard queues support at-least-once message delivery.
pgmq (which is linked on this gist) provides an api to this functionality. It can be 0 seconds, or 10 years if you want. It's not a row lock in, which can be expensive. In pgmq, its build into the design of the visibility timeout. FOR UPDATE SKIP LOCKED is there to ensure that only a single consumer gets any message, and then the visibility timeout lets consumer determine how long it should continue to remain unavailable to other consumers.
You get exactly once when you consume with pgmq and run your queue operations inside transactions in your postgres database. I can't think of an easy way to get some equivalent on SQS without building something like an outbox.
The problem is rarely cost, it's operational overhead.
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.
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.
Honestly, that's one of the reasons I never want to monetize my work and stay miles away from the software industry.
Modern world is all web apps that require you to subscribe to 20 different 3rd party services to even build your app. So you rack up bills before your product is even remotely lucrative...
Building an app with no third party dependencies seems impossible nowadays. At least if you plan to compete.
I mean, you _can_ host your staging environment on a Minisforum PC hidden in your closet and then deploy to Hetzner, and probably save a _ton_ unless your service benefits from things like, say, autoscaling or global low-latency access.
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.
On top of that, a lot of discourse seems to happen with an assumption that you only make the tech/stack choice once.
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.
PG works really well as a message queue and there's several excellent implementations on top of it.
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 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.
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.
What's the drawback to a trigger? I would think that any overhead you recouped by avoiding a trigger would be offset by the overhead of sending the entire WAL to your listener, rather than the minimized subset of events that listener is interested in.
(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.)
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.
...but by trying to avoid the bottleneck and moving things to backend, you make things 10x worse resource wise for the DB. So it is not a easy tradeoff.
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.
Well, it is file IO, plus processing on top. But it's not that simple, since if your data is small enough it can all be loaded into memory, allowing you to sidestep any file IO. But you still have the processing part...
Handling business logic in the database is often going to be an order of magnitude faster than the application layer of some of the popular language stacks (looking at you, Rails, Node, etc). It also will outlive whatever webstack of the day (and acquisition which of en requires a re-write of the application layer but keeps general database structure - been there done that).
Maybe faster... but I've met very few developers that are good DBAs (that understand procedures, cursors, permissions etc.) Database schema versioning / consistency is a whole other level of pain too.
I have certain experience with some technologies, e.g., SQS and Postgres.
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.
This desire can sometimes be so strong that people insist on truly wacky decisions. I have before demonstrated that Postgres performs perfectly well (and in fact exceeds) compared with a niche graph database, and heard some very strange reasons for why this approach should be avoided. A lot of the time you hear that it's engineers who chase shiny technology, but I've seen first hand what can happen when it's leadership.
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.
It is easy to represent a graph in Postgres using edge and node tables. For the use case we have, it is more performant to query such a setup for many millions of relationships vs using the big names in graph databases.
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.
IIRC, the biggest (AFAIK) graph DB in the world, TAO from Facebook, is based on an underlying MySQL. There must be a good reason why FB prefers a SQL DB over a dedicated graph DB.
Ok. I get that. But to play devil's advocate: with that mentality we'd never learn a new technology and still be stuck on punch cards. And I don't have the time anymore for hobby projects. I'd say it's ok to introduce something new as long as it's one thing at a time and not an entire new stack in the "a rewrite will solve all problems" projects
To me this argument sounds like “I don’t have time for hobby projects, so I’m going to treat this professional one as a hobby”.
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.
I take your point but you don’t explain how you came to be intimately familiar with those technologies in the first place. Applied consistently, this logic would seem to preclude becoming familiar with anything.
For projects where I have a paying customer, this rule is absolute; I do not experiment on my client's time (and dime) unless they specifically request it.
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.
I'm okay with new technology, actually, but the person introducing it has to be able to champion it & do the work of debugging issues and answering questions about its interactions with the rest of the system. I.e., they have to be responsible for it.
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.
I worked in a team that did this. It was mostly staffed by juniors, and the team leader wasn't very interested in the technical aspects, they just went to a page, checked that the new feature worked alright and gave the green light.
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.
> you start to realize _why_ there's a stack, rather than just a single technology to rule them all
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.
That's a nicely balanced view. I've been working on the intersection between dev, sec and ops for many, many years and one of the most important lessons has been that every dependency is a liability. That liability is either complexity, availability, security, wasting resources or projects or key people disappearing. Do anything to avoid adding more service, library or technology dependencies; if necessary, let people have their side projects and technological playgrounds to distil future stacks out of.
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.
This is very much the way I'm pushing in our internal development platform: I want to offer as little middlewares as possible, but as many as necessary. And ideally these systems are boring, established tech covering a lot of use cases.
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.
The more I do fullstack work the more I see an obesity crisis. I under the need to modularize (I dearly think I do) but god you have relational model, reimplemented in your framework, reencoded as a middleware to handle url parsing, the one more layer to help integrate things client side. I find that insane. And Postgrest was a refreshing idea.
Seriously. There's like 7000 duplicates of the very same data layer in a single stack: database, back-end ORM/data mapper, front end and various caching things in between. Things like PostgREST and Hasura area great pared with fluent clients.
And then there's the failed microservice case.. what some people describe a distributed monolith where data has to be passed around through every layer, with domain logic replicated here and there.
Seems like it's less than a week ago that someone (rightly) posted that it isn't [1]. Postgres is a relational database, and it does this really well. It's not a convection oven.
That is specifically about AWS/RDS and relates to EBS/IOPS moreso than PostgreSQL. In fact their solution was to run the db but not as an RDS instance.
> 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.
Love postgres and use it extensively. However, there's always an issue when I start doing the more advanced stuff: how do I combine that with all my years of experience with version control, code reviews, types, tests, static analysis and all the niceties of coding in general?
It's not a great DX for sure. Once you start stuffing a ton of application logic in Postgres triggers (or worse split logic between your application layer and triggers) your system will explode in complexity really fast. Developers will insert a row in an inconspicuous table and things will break in ways that seem mysterious because it's not obvious, looking at the application code, why the system is misbehaving.
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.
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.
RDBMS's are still very much an 80s thing. Great at dynamic queries, horrible at managing changes in an immutable, atomic, versioned way. The structure and relationship of data is so brittle it's kind of crazy it's still used. Probably it's because so many people have grown dependent on its particular flaws; the incumbent just lumbers on.
There’s always a trade off though. Append-only storage models are great for use cases where they are truly necessary, but they also store substantially more data than mutable databases do, and designing for something like Datomic carries a heavier cognitive cost than a simple RDBMS.
You might be curious to look up how Alembic and Django accomplish migrations. Even if Python's not your jam, the concepts/semantics are language/framework agnostic.
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.
There is a bit of tooling needed but is already around. For Java for example I had very good experience with a combination of flyway [1] for migrations, testcontainers [2] for making integration tests as easy as unit tests and querydsl [3] for a query and mapping layer.
Aspiring to "use the right tool for the job" leads you into conflict with some of the ideas posted on the gist.
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.
If the purpose is to do database specific things then pg cron can be useful for e.g. periodic vacuums. However for running application logic, maybe less so.
On the [Simplify: move code into databases](https://sive.rs/pg), has anyone actually tried this?
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.
> To me, having two things that do something distinct is simpler
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.
One caveat is when you have to upgrade across major database versions. The more your application utilises these features, the more you're likely to hit when next major version upgrade has to happen.
Not that major database versions are any simpler even if you just stick to CRUD for databases.
Postgres is enough as long as you have a good multi-tenant setup e.g. a separate database per customer.
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
Worked at a company that suffered from this too. Compounded by the feature that let customers share data with other customers, so single DB was a decent architecture to begin with. And we were on postgis v1, which had no straightforward upgrade path to 2.0, nor was it supported by later versions of Postgres. Restoring a backup became a multi-day affair.
> Postgres is enough as long as you have a good multi-tenant setup e.g. a separate database per customer.
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.
Obligatory "depends". I'm aware of more than one company that uses postgres for huge datasets serving workflows with high throughput and importance.
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.
> 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.
Yes. I don't mind the occasional JSON[B] column when it makes sense, but it should not be used as an excuse to not design a good schema. In general, if you find yourself trying to fit a non-scalar value into RDBMS, you should reconsider the data model.
I'm very aware of this fact. The issue is that by definition, JSON (or any other non-scalar) cannot be in normalized form, and so what you end up with is devs using RDBMS as a K/V store that has a few joins thrown in.
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.
Thank you for this post. I've been looking high and low for weeks trying to find a way to connect Looker to a GraphQL API unsuccessfully and apparently "PostgreSQL emulators" are called FDWs as I learned from this link. I had searched every term I could think of for how to simulate or proxy a SQL database into an API but calling it a foreign data wrapper would have never occurred to me.
Did you try describing the problem and the thing you're looking for to chatgpt to see if it knows of a name? I find that making connections like this is one of the things it actually does well
I actually did, repeatedly. I threw away 3 or 4 chat sessions with it about this very subject. It kept hallucinating features into libraries and naming products that don't actually support this translation direction.
And with Google searches the vast majority of results are going the opposite way: creating a GraphQL API from a real SQL schema.
It depends on your definitions, but if you took psql and Postgres and compared it to something like an OS on a 1980s microcomputer, you're kinda there. You can run arbitrary commands, you can program it, you can debug those programs, you can run programs, and you can access storage. If you're talking more like Linux or Windows, you have.. a bit more work to do ;-)
I've thought about writing a general-purpose programming language that uses Postgres as its runtime, with the twist that it can save checkpoints to survive system reboots and also interop closely with SQL. Postgres already has a lot of nice built-in types and functions.
This makes a strong case, but I've decided to start every new project with sqlite and not switch until absolutely necessary. If Postgres is the 90% case, then sqlite is the 80% case and is also dead simple to get going and genuinely performant. So when vertical scaling finally fails me, I know I'll be at a wonderful place with what I'm building.
I’m with you in general, but what about vector search?
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).
Vector similarity searches can be done with simple SQL statements, and while the performance will not match a vector db, it's often good enough. For a db like SQLite that can run in-memory, I suspect they would be reasonably fast.
There are vector search solutions for sqlite that basically work, so if my project doesn't rely on that 100% then I'm willing to use those as stop-gaps until it does.
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.
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.
You can of course use a vanilla database, read every row and just roll your own vector distance function, but it's just frustrating that there isn't a standardized pattern for this.
There are plenty of proprietary databases and APIs, but now you're taking on a dependency and assuming a certain amount of risk.
> it's just frustrating that there isn't a standardized pattern for this.
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.
> It took SQL well into the 1990s to fully solidify itself as "the standard" for relational querying.
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?
> [...] sqlite is the 80% case and is also dead simple to get going and genuinely performant.
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?
I think "dead simple" is not doing anyone any favors when it is being used to try to equate the simplicity of things.
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.
But most projects don’t even have customers when they start, let alone large quantities of their data and legal requirements for guaranteed availability.
I think it's reasonable for a business, even a freshly starting one, to expect to grow to the point where it does have enough customers to outgrow SQLite fairly soon. Between that and PG offering more features in general (which often simplifies app code, because you can do more with queries), it's still not clear why not start with PG in the first place.
PG, on the other hand, can scale enough to cover foreseeable future needs for most businesses, so aiming for something more complex than that is almost always premature.
I'm not sure its neglibile, I suppose once you know what you're doing.
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
That's interesting... my experience (almost all on RHEL/CentOS/Fedora) is that it is trivial to have unix domain socket with local Postgres clients and a pain to setup any remote clients.
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.
Practically, because sqlite is good enough for one machine and compatible-enough with postgresql that you can use either pretty easily. One thing I wrote was an exactly-once stream processor that fetched events from a lot of remote systems for processing. Transaction-based queue in the DB to achieve exactly-once with recovery (remote systems accepted time-stamp resyncing of the stream of events). It works fine at small scale on a single machine for design and testing (local integration tests with short startup time are very valuable) but trivially scales to hundreds of workers if pointed at a postgres instance. The work to allow sqlite vs postgres was a single factory that returned a DB connection in Go based on runtime configuration.
It's also good practice for designing reasonably cross-database compatible schemas.
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.)
Except for when your data is in it. Migrating data on a running app is one of the worst things to deal with. I can understand using something simple and cut down for other things, but the DB is not the place I'd want to do that. Postgres isn't exactly hard to get going with, and will grow with you easily, so why trade that off for saving an hour or two at the start of the project?
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.
Postgres complicates the application in several ways. In particular, Postgres suffers from the n+1 problem, while SQLite does not. That requires a significant amount of added complexity in the application to hack around. Why over engineer the application before it has proven itself as something anyone even wants to use? Let's face it, the large majority of software written gets thrown away soon after it is created.
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.
Meaning insertion sort does not suffer from the problems that made us construct quicksort because we optimized the insertion a lot (at a tradeoff that we will not specify).
This is a misunderstanding of the n+1 problem, which is exacerbated by SQLite's deceptive phrasing of the issue:
> 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.
> 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.
Are we? "Suffer" was the word used right from the beginning for good reason. Computers aren't magic. I find no importance in pointing out that fact. Presumably everyone here already knows that. And if it turns out that they don't, who cares? That's their problem. Makes no difference to me.
I agree that suffer is the right word, but unclear. You are getting down voted because a lot of people are interpreting to mean you are saying applications using sqlite don't have n+1 queries.
1. At time of writing, there has been one downvote in the first comment, followed by one upvote in the subsequent comment. Not only does that not translate to "a lot of people", it was quite likely just one person. And unless that person was you, it is impossible to know what their intent was. I'm not sure what are you trying to add here.
2. Who gives a shit? If the "computers truly are magic" camp don't understand what I wrote, great! It wasn't written for them in the first place. If that compels them to use their time pressing a button instead of learning about how computers work, great! Not my problem. I'm not sure what you are trying to add here.
Well, let's hope the "computers are magic" riff-raff never show up at the design reviews. Especially if they expect someone to explain to them the basics of computing without any reasonable offer of compensation in return.
If those people show up here and put on a tantrum by pressing random buttons or whatever it was that you were trying to point out... Oh well? I wouldn't have even noticed if you didn't bring it up.
What value you found in calling attention to their pointless tantrums is an interesting mystery, though!
> 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.
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.
> 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.
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.
> Joins are not a "hack," they are an integral part of the relational model.
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.
n+1 can show up any time you have poorly modeled schema or queries. It’s quite possible to have a relational model that is sub-optimal; reference the fact that there are 5 levels of normalization (plus a couple extra) before you get into absurdity.
I still would like to know how SQLite does not suffer from the same problems as any other RDBMS. Do you have an example schema?
> I still would like to know how SQLite does not suffer from the same problems as any other RDBMS.
That's simple: Not being an RDMBS, only an engine, is how it avoids the suffering.
The n+1 problem is the result of slow execution. Of course, an idealize database has no time constraints, but the real world is not so kind. While SQLite has not figured out how to defy the laws of physics, it is able to reduce the time to run a query to imperceptible levels under typical usage by embedding itself in the application. Each query is just a function call, which are fast.
Postgres' engine can be just as fast, but because it hides the engine behind the system layer, you don't interact with the engine directly. That means you need to resort to hacks to try and poke at the engine where the system tries to stand in the way. The hacks work... but at the cost of more complexity in the application.
Compare and contrast the query execution stages of PostgreSQL and SQLite. How exactly do they work? Please be as precise as possible. Try to avoid imprecise terms like "simple", "suffering", "system layer", and "hack."
Engineers and scientists are paid professionals. They are financially incentivized to help other people. Maybe you somehow managed to not notice, but I am but a person on an Internet forum. There is no incentive offered for me to do anything for anyone else. To have the gall to even ask for me to work for you without any compensation in kind is astounding.
Of what difference does it make if anyone takes me seriously or not? That's the most meaningless attribute imaginable. Oh noes, a random nobody on the internet doesn't believe me! It's the end of the world as we know it... How droll.
Perhaps it is that you do not understand what value means? Or how did you manage to write all those words and not come up with any suggestion of value whatsoever?
> 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 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.
Of course. I know reading is hard, but nobody has suggested there aren't solutions. Obviously there are, else Postgres would be pretty much unusable. But the question remains: Why resort to hacks that require extra complexity in the application when you can just as easily use the database as it was designed to be used? What are you getting in return?
One use case where SQLite is a good option is for embedding as a local database in an app. Starting local-only with SQLite allows you to defer a lot of the backend effort while testing an MVP.
If dead simple involves me babysitting a service process then not it is not. SQLite has embedded version that requires no service out of process. That's what my definition of dead simple.
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.
I love postgres - although there are some things I dislike about it - mainly due to me using mysql for a years. All dislikes are mainly syntactical things, like having USING in deletes instead of JOINs. I also liked the output of EXPLAIN more in mysql.
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.
While I love MySQL for a variety of reasons (and think it's legitimately better for certain features – tangential to your point, being able to ORDER BY in a DELETE is great), its lax GROUP BY option isn't really a great idea in terms of correctness.
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.
One cool thing if you are running off Postgres - DDL is transactional so you get clean deploy of your new code version (stored procedures) with precise switchover for free.
Was talking to coworker yesterday about a spectrum of where code lives, and the differences from where I started to where I am now in understanding.
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.
This was similar to my journey as well. I'm a self-taught developer and was so green when I learned Rails I didn't even understand that there was such a thing as SQL behind the ORM. Took some grey beard .net folks to share the and power of the database.
> > Wouldn't it be nice to be able to write a trigger that called a web service?
> 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.
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.
If you're open to Elixir (you'll like it coming from Ruby) then you don't even need Redis. Oban + Postgres for jobs, WalEx for database events, Nebulex for distributed caching. It simplifies things so much (and is cheaper to run).
I keep hearing about it, just never got around to actually doing anything with it...but I might have that itch now. I'll look into these things thanks.
Our company has spend a lot of resources optimizing these kinds of queries in our application. It's been a bit of a struggle since we also use GraphQL, but we've been doing alright. Partial indexes and liberal use of jsonb have been doing a lot of heavy lifting for us. Our application is extremely read heavy, so the performance hit from updating lots of indexes hasn't been an issue for us (yet).
How about metrics? Would it be possible to do this in Postgres as well? I'm talking e.g. about simple counters that are not directly vital to business logic. I'm currently using Redis for those things as I'm thinking that Postgres seems to be the wrong choice for this kind of data, would anyone disagree?
So, having worked on a managed postgres service, we had an extension pushing metrics from the database, but we also used redis to keep service metrics
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
You can use the TimescaleDB PostgreSQL extension. It works really well and has a high compression rate for time series data, such as metrics. It can also downsample data. I have also tried InfluxDB, but you have to rewire your mental model to fit InfluxDB (it has its own query syntax and internal mechanism). They have versions v1, v2, and v3, and it's not clear which one to use, probably v2 at this point in time. It's kind of confusing and the state of development is very much in flux.
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.
> Would it be possible to do this in Postgres as well?
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).
It is possible, if you pay for it. You can do Multi-AZ Clustered Instances in RDS, where you get the benefits of Multi-AZ failover with traffic sharing.
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.
Very clunky compared with things like CockroachDB.
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.
322 comments
[ 3.0 ms ] story [ 281 ms ] threadBut 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.
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.
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.
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.
SQS Standard queues support at-least-once message delivery.
NATS offers "at-most-once" delivery
etc.
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.
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.
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.
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.
https://www.postgresql.org/docs/current/sql-notify.html
I've ready a lot of reports (on here) that it comes with several unexpected footguns if you really lean on it though.
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.
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.
(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.)
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.
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.
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.
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)
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.
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.
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.
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.
[1] https://news.ycombinator.com/item?id=39243655
> 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.
Migrations?
https://www.jetbrains.com/help/idea/database-tool-window.htm...
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.
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.
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.
[1] https://github.com/flyway/flyway
[2] https://java.testcontainers.org/modules/databases/postgres/
[3] https://github.com/querydsl/querydsl
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.
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.
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.
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.
And with Google searches the vast majority of results are going the opposite way: creating a GraphQL API from a real SQL schema.
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).
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.
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?
We're done here.
https://supabase.com/docs/guides/database/extensions/pgvecto...
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.
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.
PG, on the other hand, can scale enough to cover foreseeable future needs for most businesses, so aiming for something more complex than that is almost always premature.
That would be the result of either vastly overestimating their business plan or vastly underestimating SQLite.
In a world fueled by cheap money and expensive dreams, you'd be surprised.
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.
It's also good practice for designing reasonably cross-database compatible schemas.
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.
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.
?
N+1 Queries Are Not A Problem With SQLite
https://www.sqlite.org/np1queryprob.html#:~:text=N%2B1%20Que....
> 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.
It's a very important distinction because, as you say, there are problem domains where you can't just "join the problem away".
2. Who gives a shit? If the "computers truly are magic" camp don't understand what I wrote, great! It wasn't written for them in the first place. If that compels them to use their time pressing a button instead of learning about how computers work, great! Not my problem. I'm not sure what you are trying to add here.
I guess nothing. You must be fun at design reviews.
If those people show up here and put on a tantrum by pressing random buttons or whatever it was that you were trying to point out... Oh well? I wouldn't have even noticed if you didn't bring it up.
What value you found in calling attention to their pointless tantrums is an interesting mystery, though!
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.
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.
I still would like to know how SQLite does not suffer from the same problems as any other RDBMS. Do you have an example schema?
That's simple: Not being an RDMBS, only an engine, is how it avoids the suffering.
The n+1 problem is the result of slow execution. Of course, an idealize database has no time constraints, but the real world is not so kind. While SQLite has not figured out how to defy the laws of physics, it is able to reduce the time to run a query to imperceptible levels under typical usage by embedding itself in the application. Each query is just a function call, which are fast.
Postgres' engine can be just as fast, but because it hides the engine behind the system layer, you don't interact with the engine directly. That means you need to resort to hacks to try and poke at the engine where the system tries to stand in the way. The hacks work... but at the cost of more complexity in the application.
Engineers and Scientists do that; they don’t hide behind airy and vague language – that is the realm of conmen.
You stated that you cannot implement a tree-like structure with tables, so I set about proving you wrong, and posted a gist that does so.
Until you can back up your claims with data, your words are meaningless, and no one here is going to take you seriously.
Engineers and scientists are paid professionals. They are financially incentivized to help other people. Maybe you somehow managed to not notice, but I am but a person on an Internet forum. There is no incentive offered for me to do anything for anyone else. To have the gall to even ask for me to work for you without any compensation in kind is astounding.
Of what difference does it make if anyone takes me seriously or not? That's the most meaningless attribute imaginable. Oh noes, a random nobody on the internet doesn't believe me! It's the end of the world as we know it... How droll.
Perhaps it is that you do not understand what value means? Or how did you manage to write all those words and not come up with any suggestion of value whatsoever?
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.
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.
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.
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++.
https://www.postgresql.org/docs/current/pgcrypto.html
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.
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.
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
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.
https://github.com/timescale/timescaledb
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).
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.
[0]: https://www.citusdata.com/
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!)