This isn't just theory either, for example: Revolut is a bank that does all its event persistence and streaming on top of postgres. No traditional message queues/brokers in their stack.
As SRE dealing with this at current company, a benefit of using well known software like Kafka is a lot of problems you will run into have solutions/guidance already available vs you having to explore solutions which a lot of time end with “Kafka could easily do this. “
There are two sizes of companies: those that can afford '1+ dedicated ____-person' and those that can't. Which should filter through to technology choices more than it does.
One specialist and a group of generalists is often enough. Especially if you are allowed to contact the specialist outside normal working hours in rare emergencies.
Or, you can do like one of my former bosses, and just rattle off a list of 60+ major projects which would require a team of 10 to make any reasonable progress on in the near future, then pin it on one underqualified person, refuse to provide a proper budget, and continually press them about why targeted deadlines are being missed.
I mean, with this custom thing, you have that question as well with downside is you cannot pick up the knowledge from off the street.
I became the Kafka guy at my current company, it took me about a week of reading and every time I had further question, I didn't have to bother anyone, I could Google and get data I needed.
When it's some NIH thing, you have to bother coworkers and knowledge is whatever is in YOUR company knowledge base with no ability to get knowledge from outside the company.
EDIT: You could also leverage contractors or outside support if not homegrown software.
Yup, and every time it breaks, you've got to pester someone whose job probably isn't maintaining that thing actively.
I worked at a startup with massive NIH syndrome, once. We even used our own in-house programming language, because it was "better than anything else out there on the market." It did have a lot of nifty features that others don't have: a pretty novel type system, programmatic macros, a built-in build system and other fun bells and whistles -- but also not-so-fun ones like having no syntax highlighter, LSP, or debugger, and having to constantly shuffle around your code to avoid ICEs in the compiler.
The compiler wasn't the product, but we found ourselves fighting that thing more actively than any of the real problems our custom programming language was supposed to solve. The CTO found himself spending all his nights and weekends mostly trying to get the compiler to not explode.
A few years later, after I had long left (for that reason, among many) I heard they switched to Python. Can't imagine how long it took them to get that all rewritten.
> e even used our own in-house programming language, because it was "better than anything else out there on the market." It did have a lot of nifty features that others don't have: a pretty novel type system, programmatic macros, a built-in build system and other fun bells and whistles
A DSL can work, but not for the features you list. Those features you already get from existing languages anyway!
If you need general programming language features like excellent type system, programmatic macros, a build system (doesn't need to be built into the language), etc... then use a general purpose programming language.
I have a DSL for backend/endpoints, and exactly none of those are in my feature list. What it has are things like easy way to specify access-control directives[1], the SQL query to execute, mapping request variables to SQL parameters, mapping SQL results-sets to response fields, etc.
I have another DSL for a test program. Both of those DSLs have specs that's literally 2x screens of bullet points and examples. LLMs can output those DSL programs because the spec for the DSL is so small.
For general purpose programming stuff (while loops, conditionals, etc) my DSLs break out to Python.
A good indicator that you shouldn't be creating a new language for production is when you find yourself implementing conditionals, loops, etc.
===========================
[1] Limit endpoint to specific roles, or members of the same team, or both, or even to the user itself - someone calling `/user/profile/update` should only be allowed if the profile they are updating is theirs, for example.
Ah, but you see, it was a general purpose programming language. A general purpose, functional, optionally-typed programming language, with its own optimizing compiler and build system. In fairness, it wasn't originally developed to be our in-house language, but it was the creation of the CTO.
It was fun while it lasted and I had a lot of fun working on it. But it was really not a good business fit. The programmatic macro system was supposed to allow us to build customer-facing DSLs on top of it, but everybody just wanted Python anyways.
This problem doesn't go away with postgres. It's totally anecdotal but this is one thing that I've noticed different in mysql shops and postgres shops - with mysql there is usually at least one person on staff who knows MySQL DBA and scaling pretty well, with postgres it's rarely the case to have someone who knows the internals well - like you said, the person capable of maintaining it when it goes wrong.
You could argue it's because postgres requires less poking though I would say you don't need the DBA for when things go right.
Of course most people are just handing the management off to the cloud and that's potentially why, but it doesn't cover everything
MySQL will generally run fairly well with default tuning, assuming you've sized the buffer pool well relative to the amount of RAM you have (cloud providers do this automatically, but it's also not that hard to calculate). There are some knobs you can turn to eke out more performance in certain situations, and there are some defaults that are truly terrible (lock_wait_timeout is set to 1 year...), but all in all, it doesn't take a lot of care and feeding to run reasonably well.
Postgres, on the other hand, has a million knobs, many of them interact, you'll find conflicting advice for some of them, and it can rapidly fall over if you aren't keeping a close eye on long-running transactions. It's also more performant than MySQL in _most_ situations (hello, clustered index), if you've tuned it correctly. It also of course has far more extensibility out of the box, with tons of index types that are extremely helpful, if you know how and when to use them.
This difference is why I'm always frustrated when people parrot "just use Postgres" as though that solves all problems. It's an extremely powerful tool that can replace most of your stack, yes, but it also would really, really like you to RTFM. Not random Medium blog posts, the canonical documentation.
If you start here, with the "Postgres will take you wherever you need to go" meme, without thinking extremely deeply about your schema and how you expect to evolve it in the future, you can easily paint yourself into a very difficult and expensive corner.
It's easy to use Postgres poorly in ways that result in painful centralized bottlenecks.
(Obviously this is largely true for anything, but I think that in 2026, where there's also a lot of more-specialized/less-fleible but much-easier-to-scale well-supported mature alternatives, you should be VERY wary of making everything have a single central SPOF. What are your users going to expect in terms of maintenance windows, etc.)
I'd be cautious with articles that say things like "All cloud providers allow you to run (and scale!) PostgreSQL by clicking a single button." with no mention of how long that will take and what options should be set to make it faster, or the costs of those things.
It doesn't take very long (because compute and storage are separate in most of them) but good lord does it get expensive. Every time you click that upgrade button you are doubling your cost. It's really painful when you have a spiky workload that is performing fine like 95% of the time but you are watching the p99 and need to double the cost of a very expensive infra component, only to improve the experience of the heaviest 4% of your workload. This is to say nothing of the gambit you then have to play with reservations/prepays.
I haven't seen a way to get guarantees of upscaling operations under like 30 seconds (with Multi-AZ RDS) with well-supported RDS stuff (leaving out active-active setups with logical replication because that's a whole other can of worms).
If you know you're gonna be ok with that for a long time, go nuts. I'm just saying: think about it in advance!
The cost pain for spikes is also a thing - some of Aurora's billing models look potentially promising but I haven't used them in practice - though it's also somethings that's harder to avoid with alternatives. Distributed DBs aren't generally super friendly to dynamic scaling IME.
Having done that, e.g. used rabbitmq plus postgres, honestly I wish I had just used postgresql for both messages and data. It would have been easier to manage by an order of magnitude, especially at scale and needing to satisfy enterprise requirements. Also the flexibility of postgres would have solved problems that we ran into because of limitations of rabbitmq.
Without knowing any specifics of your uses, my usual starting point on that sort of design is that "messages AND data" is it's own special little way of ending up with a hard-to-debug-and-operate system. ;)
It's very hard to best-of-both worlds event-driven system + RDBMS-storage, it's very easy to end up with worst-of-both-worlds. Hello distributed transactions!
Again, you just should think about all the ways you want to use it and the maintenance/uptime requirements your users are going to have in advance.
I think messages + database are extremely common in any sort of large application where you have data processing nodes. Postgresql actually has very good mechanisms to support message style communication, and as long as you design your message tables independently you shouldn't have horrid issues around locking and transactions. Message queues don't save you from thinking about that anyways, they just replace transactions with acknowledgements.
Trying to manage a highly available and durable rabbitmq or other message system that can also be recovered from backup to an offsite mirror infrastructure in the worst case is actually incredibly difficult. Usually these systems are designed with the assumption that you can just regenerate messages based on database state anyways in worst case scenarios.
In this use case your database already is highly available and can recover on an offsite backup if you have suitable wall shipping going on. So you've done all the hard work once, may as well reuse it unless you truly have some mind bogglingly large message throughput needs.
Finally, we had a need of a queue that was more than just first in first out. We wanted to fairly balance workloads across users and tenants. Whenever you have such a need postgresql lets you design this type of queue far easier than trying to do some elaborate multi-queue setup with a traditional queue.
A counter anecdata. We transitioned from a postgres job queue to Rabbit. We had never ending problems after that, many of them were misunderstandings, some where just wrong-fit. We migrated because we had some time on our hands and thought we would alleviate some high pressure jobs. Not only did it not solve the problem, but having written all the code that decides when to pull the next message and what to do with it, and how to dead-letter it - just worked great for us on Postgres. It was so easy to understand and doing things like reprocessing just using a standard postgres DB interface was much easier.
Ultimate the entire processing got removed from our team and no longer needs to do these deployments (acquisition transitions)...
I tried very hard to use postgres as a queue, it was robust but slow once I started to push from more than a few processes/servers. Moving to zeromq initially and sqs after solved all my perf issues, and was still solid.
I think that’s the way to go. Start with Postgres and only if there are problems, then think about something more specialized. Same for microservices. Start simple and introduce a service when really needed.
I hate it when people already start out with 10 or more different systems/services for a few messages per second.
I think that using the right tool for the job is important and saves a lot of time in the long run. There are expensive headaches that we have to resolve.
Sometimes to scale is "continue to satisfy SLAs as service usage increases" and other times to scale is "successfully evolve functional capabilities over time."
While the GP may have been referencing the former, embracing "PostgreSQL for Everything" often prohibits the latter.
It *can* do that. I wouldn't say it's necessarily "often". Again, it's hard to predict the future.
I think that the "use Postgres for everything" messaging was a necessity, even if it is overstated. Use it until you can demonstrate it doesn't meet your near term needs. When that happens, shift. It wasn't that many years ago when I'd enter situations where people were knee deep in FAANG level infrastructure when postgres on a relatively small instance would have more than been sufficient. I'd suggest they look at converting to postgres to save money & all the energy they spend maintaining their soup. "It won't scale the way we need it!". Sometimes they were demonstrably wrong. Other times they were half-right, in that the real problems was terrible decisions made at the software layer, leading to a situation that required heavier duty infra. Almost never were they actually right* though.
Might they have been right 5 years later? Perhaps. But I know for a fact that none of the ones I encountered were.</i>
> If you start here, with the "Postgres will take you wherever you need to go" meme, without thinking extremely deeply about your schema and how you expect to evolve it in the future, you can easily paint yourself into a very difficult and expensive corner.
Yeah, backwards compatibility is not a thing for Java, Rust, C++, etc. :eye-roll:
Meanwhile in SQL if you need to make a backwards-incompatible change to your schema you can always use VIEWs and INSTEAD OF triggers to maintain backwards compatibility for code you've not fixed yet.
I once needed to maintain an application written in everything Oracle. If I ever encounter the original author of that product: I have things to say to him.
We quickly replaced part by part by easier, less costly parts.
Software development is not just writing code; I think all HN users know that.
During pgConf.eu in 2016(-ish, could have been one or two years later; I don't remember too well), a representative of payment processor Adyen told the audience that they were, essentially, one big postgres cluster in their backend, too ("cluster" used as per the postgres-native meaning of the term, as in, an installation on a single host with a data directory containing any number of databases).
My general rule of thumb is "Use Postgres until you've discovered why you can't use Postgres."
Anything you introduce is another moving part you have to operate and maintain, and in the beginning, Postgres can probably handle it. Wait for load, see where its failing, and then you'll have a better idea if adding another tool is worth the cost.
In a lot of cases using SQLite means you write queries incompatible with RDBMS. No need to worry about race conditions or the amount of queries you make, when 100 selects are uber fast.
Yes, for very small or embedded, single-purpose systems sqlite is usually a good choice. It's very well tested, and there's nothing extra to run or manage. But be careful if the system starts growing beyond that, you'll want a real RDBMS before you abuse sqlite too much.
I would probably do an event-source architecture, where you record events on the client and then push them to the server when you're reconnected. It has a lot of benefits, for instance, trivial auditing and free serialization.
You can use a trigger with INSTEAD OF to update a view:
CREATE TRIGGER update_customer_emails_trigger
INSTEAD OF UPDATE ON customer_emails
BEGIN
UPDATE customers
SET email = new.email, name = new.name
WHERE id = old.id;
END;
Maybe that's not as flexible as you need.
SQLites process for table updates can be pretty onerous if their ALTER TABLE lacks support. Its a 12 step process the docs have the temerity to call "simple" instead of "tedious and risky" - https://www.sqlite.org/lang_altertable.html#otheralter
I used this approach to drive entire app, and it works. Nearly all data are fetched from SQLite. User can select a database, which can change app views, and the data. In my experience it is quite fast.
i don't think that's an issue. it's still a perfectly good rule.
use what you're familiar with, until it stops working. then use something else. postgres just goes a lot further than a lot of other tools before you get to the "use something else" phase. and postgres is the database a lot of people are familiar with.
My rule is "use a single database for as many of you persistence needs as possible". Often Postgres is a good choice for that database, but many other general purpose DBMSs will work just as well.
In my previous company we used MongoDB, for almost everything, including text logs, request logs, job-queues and small template files. We added S3 since storing terabytes of files in a database is expensive. Now, I wouldn't choose MongoDB for a new app, since the data model and query language suck, but it performed reasonably across many use-cases.
For my next application I will use Postgres as primary database. Probably will integrate S3 before going live, to avoid the necessary data migration later, but haven't decided yet if that's a premature optimization.
This kind of post (Postgres! It's all you need!) is getting pretty tiresome. Postgres does not even come close to a full replacement for Elastic, and that's just the first bullet.
Looking down the list it is pretty easy to go: Yes, postgres can be used instead of that for extremely basic use cases, but it all goes out the window you actually need any of the power of these other tools.
I'm partial to Typesense, especially for smaller data sets, since it runs primarily in memory, is easy to use and is hella fast. For bigger data sets, I hear good things about Meilisearch.
Fwiw, I, as someone who has worked on Postgres for a long time, also find it quite tiresome. Like there's plenty stuff I wouldn't use Postgres for, and I can probably get get more out of it than most.
Exactly - these recommendations often come with no context or scale provisions.
Yes Postgres can work in the small for a lot of things, it can even work at surprising scale if you use it according to its strengths.
But if you use it for things it doesn't shine at, at inappropropriate scale - you'll almost certainly run into issues. And resolving those can often be a bigger challenge than choosing a more suitable solution in the first place. But often I think younger/less experienced engineers just have to burn themselves, thus why this never seems to die.
The point is in general for people to just consider it, often people start out on their side projects or internal company projects and commission Elastic, Redis, Postgres, Kafka before even getting started. In reality they could fit it all into Postgres for a very long time.
Nobody is saying that a huge ecommerce store with complicated filtered search logic should throw away their Elasticsearch cluster and switch to Postgres.
If you actually start looking into these things, you often start looking at custom pg extensions, which means you just made the decision to "simplify" your stack by maintaining your own postgres cluster with custom extensions. This is just papering over the fact that you're increasing the complexity and saying "well it's still just postgres!" as you do it.
Not really, most popular extensions are available on GCP/AWS (https://docs.cloud.google.com/sql/docs/postgres/extensions) out of the box and there's many things that you can easily run in Postgres without extensions (Queue, KV store).
Installing & maintaining a Postgres extension is vastly, vastly simpler than running Elasticsearch and Kafka. Like, how could you even compare these things if you know what you are talking about?
Or maybe you are looking at it from "just swipe your credit card at AWS" perspective, in which case "just use Postgres" articles are for a different audience.
I have a lot of troubles with a small private instance of Rocket chat, all due to MongoDb stuff, versions, migrations and backups. I bet almost all private instances of Rocket chat would be perfectly served with Postgres.
Posts like this can be tiresome, yet the general consensus among developers seems to be "yeah, Postgre/SQLite is ok for 99% of the cases, but MY case is going to be in the 1%, because I am going to be the next Facebook".
Sure, best not to overcomplicate early if you don't need it.
PG is great and I work with it daily, but it's also not a problem to think about scale early and at least have a notional plan for what to and how to know when scale is becoming an issue in your system as you're designing it. Even PG is overkill and sqlite is more than enough for some of my projects.
There are a lot of specialized tools available, but you definitely don't need to put every one in your toolbox. Experience and observation help you make those edits -- and of course there's almost always room for improvement, but "good enough" definitely exists (until it doesn't anymore :D).
This is the way. Notionally building a space/path to scale into architecture early, but delaying implementation of that scaling component until actually needed.
Then a system gets most of the benefits of not accidentally making it torturous to rearchitect for scale, without paying the headcount / complexity cost until it's needed.
I think it would be helpful if some of these posts included scale. There are almost always two groups talking past each other
- I run my B2B application, Postgres only, and it is perfect for my 50k MAU. No complaints, sleeping soundly with the low complexity and a two man team.
- I work at FAANG, where we have 1 billion DAU, and this is a joke. Would fall over immediately. The dedicated ops teams for Kubernetes, Elastic, and Redis have never complained about scaling issues.
Agreed, but many of the criticisms I am reading here are assuming high scaling requirements and invalidating the approach entirely. When there are many business domains that will comfortably fit within a modestly specced database instance.
Even just B2B vs B2C is a huge split. Plenty of specialized ERPs in the world that have relatively few "butts in seats" users but have a lot of data to work through.
Size of the data, read/write ratio, number of "requests"... lots of axes that change how much pain or not you're in by just pointing to pg
I broadly agree, but would tweak those numbers a little for small B2B apps: I ran a small B2B application on a cheap VPS using PostgreSQL as a primitive messaging interface, and even on a small VPS 50k DAU won't even cause the machine to break a sweat.
You're right that vanilla Postgres doesn't come close to replacing Elastic. There are efforts to resolve this, though, like ParadeDB: https://github.com/paradedb/paradedb (disclaimer: I work for ParadeDB)
I see Tantivy mentioned in your readme but AFAICT there is no PG-Tantivy sync. I also see "native vector support is coming to our search index soon". Could you clarify?
What do you suggest for a language like Malayalam which has no native support, preferably with low RAM requirements?
> Postgres does not even come close to a full replacement for Elastic
Size matters!
For most of the application out there elastic (or kafka or any other specialized tool) is just too much(and too costly). They can do fine with postgres or mysql. Actually, I'd argue that in a lot of cases even postgres is too much, probably sqlite is enough.
Yes, especially for a web app where there’s realistically only a need for one VM/server. By the time you outgrow that approach, a very straightforward migration to Postgres is probably the least complex problem you face.
It's the other way around for me: as 99% of the stuff I develop is .NET (and I use EF Core for database stuff), I can get away with SQLite for local development, prototyping (and even staging), and then just "flip a switch" for it to run on production PostgreSQL.
Yeah this really isn't so true anymore. I was a diehard Dapper fan for a long time, but the performance of EF Core is comparable to Dapper now. Dropping down to SQL in EF Core is super easy and parameterized. I just don't even bother with Dapper anymore. I have an app with ~200k users with the slowest query being ~6ms and of 1000s of queries only 3 are hand rolled complicated SQL written for perf executed via EF Core.
if you knew how to cook it, you could use make older EF Core (and original EF) go brrr. you sound like you do know. my problem is that it is hard to find out what exactly is the best without dropping down several abstraction layers, sometimes, as you noted, to raw SQL.
why not just construct SQL queries in a type-safe DSL, like, say, JetBrains Exposed does? you are writing what's basically SQL that your compiler understands and your existing tooling checks for free. (granted, C# may need an additional Roslyn analyzer, but it's still simpler than either guessing what transaction LINQ will make or writing SQL in strings.)
I can't recommend this "switch". If you are not testing locally with the same relational database as in production, you can miss mistakes and bugs. This is not just theoretical. One example where I thought I will be fine using SQLite was with a small Django project. But time and time again I ran into limitations of either SQLite or Django's database adapter for SQLite, when it came to dealing with many to many relationships in the model and through tables, requiring me to work around the limitations. There is no guarantee, that these workarounds in turn will work the same in PostgreSQL in production.
Anyway, it is a basic practice of keeping test and dev environment as close as feasible to production, to avoid missing issues and wrong assumptions.
> Anyway, it is a basic practice of keeping test and dev environment as close as feasible to production, to avoid missing issues and wrong assumptions.
Containers are great for this during development.
Testcontainers are great for tests in particular when you don't want to use some mocked in-memory DB because those have the same issues as using a different DB during development: https://testcontainers.com/
Agreed, and it is easy to have a Postgres container for local tests or in CI as well. I don't really see much need to avoid Postgres in testing. Also if production runs in containers, testing can and probably should just run in containers as well. Also makes for a cleaner test setup usually.
That's kind of risky considering the radical differences between types in SQLite and Postgres. There are plenty of situations where EF will need to be configured differently in order to map your .Net types correctly to each DB. Or subtle differences in behavior due to storage differences (particularly SQLite's predilection for storing things as strings). It's so trivially easy to run Postgres in Docker that I don't really see the advantage of using SQLite for local dev.
> Events, queues and persistent logs are getting more and more important in today’s software systems. Systems like Kafka, RabbitMQ, SQS and others provide that functionality. But maintaining them is annoying, custom and you need the skillset.
In tech stack choices, I prefer staying simple as long as feasible. That said, you also need to know and understand concepts at a thorough level.
The above comment from the article suggests PG as a central server that simplifies event architecture. As if the "annoying, custom and needed skillset" around those specific alternatives are unnecessary baggage.
If you know anything about queues, scaling, availability, access semantics, message formats and concepts such as delivery guarantees, you find out very quickly that the server which stores a queued message is not the high-order bit in that equation.
To me, your concluding sentence cuts the opposite direction. The server is not the most important thing, and each new kind of server that exists in the system is an appreciable increase in maintenance burden. To me, taken together, this is an argument for waiting until you have a very concrete forcing function to introduce that new kind of server for this purpose.
I think a good way to think of this is: What empirical metric will the introduction of kafka (or whatever you choose) move in a positive direction? Latency? Oncall burden? The amount of code you have to maintain? Do you have correctness or data integrity metrics that this change would register on? etc.
This isn't at all intended as an unanswerable question. Many or most organizations will easily say "yes" that they expect some improvement on some metrics by adopting the "right" system for the job. But lots of other organizations are cargo culting "well we know this is the right way, so we should do it this way" long before any metrics they care about would demonstrate the improvement.
Heartily agree with the metrics for analysis, but this seems anchored around a focus on introduction of "something else" without regard firstly to correctness.
> The server is not the most important thing, and each new kind of server that exists in the system is an appreciable increase in maintenance burden.
Sure, but compared to what? It's right to consider complexity, but understanding tradeoffs requires depth. The OP's original premise was that learning all the things about specific queuing services was unnecessary chafe; that INSERTs, SELECTs and UPDATEs are all anyone needs.
The maintenance burden sits with your producers & consumers (or publishers/subscribers, whatever your nomenclature...), whether you want it there or not.
My learned experience is that as soon as you start moving messages that are beyond trivial and carry different operational characteristics, you're going to have to understand those deeper concepts anyway.
I agree with you about being willing and unafraid of getting the depth in the concepts that matter. But that's also why I agree with you that the question of the server technology is the less important thing. I just think that leads to the conclusion "maybe stick with whatever you're already using for oltp for longer?" more so than to "you may as well introduce kafka".
I think both your questions, about correctness and about the comparison point, are answered by thinking through the empirical metrics you're trying to improve.
I often feel a bit out of step with other engineers on this point, but to me, "correctness" is not a binary yes/no, it is also a continuum. It's one of the metrics you probably want to be pushing to a large number of 9s, but trying to get to actually 100% has low ROI for what most people work on. (Not for everything! But for most things.) If you can already get to five 9s with postgres, but more 9s with kafka, is that worth it? Maybe, but maybe not. If you're missing way more messages than that and corrupting data because of it or some other bad thing, and this is the culprit, then that's certainly a good time to compare the trade offs of fixing your implementation or switching to a "proper" system for this.
And to "compared to what?", the answer is, compared to the current values of the metrics you've decided you care about. One of those might be the complexity of the implementation and the necessity to maintain specific expertise in a bespoke implementation of non-trivial complexity. This, to me, is usually the compelling reason to "buy" rather than build. But I think it is too often left too implicit and vibes-y instead of making it explicit and seeking to quantify the impact.
I tend to agree with quite a few points in the article, but some topics warrant some careful scrutiny.
* As a message queue:
Only if your required features are very basic, like if you need cluster communication and run your own coordination protocol on top.
* High Volume Time Series:
TimeScale works, but composes badly with other workloads on the same DB server ( from an operational perspective at scale )
* Vector Database:
The same issues as with TimeScale.. PgVector for example lives in its own seperate "world" and the query planner sees it as a very opaque thing. Forget about adding vector storage to an existing high volume db, that must server other complex queries.. PGVector will either trash your caches, or take over your cpu so that workloads that used to work fine stall. This is IMO not a pgvector problem itself ( Kudos to those guys ) but rather that postgresql extension apis are not very good at exposing custom costs and tradeoffs to the system as a whole.
* Raw Data:
Works for small files... why anyone would want to store large amounts of data in it would be a mystery, where it shines is accessing LOTS of small files where internal caching etc help a lot compared to raw filesystem access ( also a bit dependent on the filesystem and its tuning though )
* Microservice:
If your service is ONLY exposing json data from some database model, then it should not exist at all IMO. Create a view and be done with it.
I like to consider Postgres the starting point for all of these things, that can be outgrown and replaced when appropriate. I do love just shoving everything in Postgres and seeing that I only end up needing a few additional dedicated services as the product groups. Redis is usually the next pickup for me.
Sure, thats a good way of working.. I just have the experience when handing over a project ( consulting ) anything i have put in place will never get replaced or kept for too long outgrowing its capacity by far, and offset with huge expenses in hardware or operations. Technically not my problem anymore ( except when it breaks on a maintenance contract ) but i still like to avoid it early if i can
Also to note: (Not a fault of PGVector again just a limit of our algorithmic knowledge)
PGVector does HSNW or IVFlat indices ... (there is nothing better persistent) however it breaks down with high latency at LARGE amounts of vectors ( 100MIO+ ) that seems like a high ceiling, but when designing production RAG systems, you tend to do per chunk embeddings, or even visual patch embeddings... e.g one page of a document becomes 1024 vectors in itself (for visual patch embeddings ) ... so you hit those limits at 100000 pages already.. something larger organizations definitly have.
> Microservice: If your service is ONLY exposing json data from some database model, then it should not exist at all IMO. Create a view and be done with it.
Yeah, this has nothing to do with Postgres. If the service is accessing a database that isn't internal to the service, then that database is already a standalone service in itself.
These types of articles needed to be written because we have gone way too much in the other direction. The issue is that people use too many tools prematurely when they are not needed at their stage. So yea, in most cases, you are probably better off just with Postgres. I m a culprit of this myself so I wouldn't say that I know better. It is just too tempting to setup too many tools to feel cooler or feeling that "we must use elasticsearch as no one does search in db".
This article seems like a reaction to DuckDB's surge in popularity. Having multiple DB engines to choose from is good and it often doesn't matter which one you use. One could make the same argument about DuckDB. Many database engines are multi-purpose. Though of course there are specific use cases where a different DB may be more appropriate...
Anyway databases nowadays are a commodity. A sticky commodity but nonetheless they are replaceable; increasingly so in the age of AI where data migrations are easier than ever.
> After some performance checks it became clear that PostgreSQL was even faster than reading from the file system for our use-case. PostgreSQL uses the file system very efficiently for its data - and it adds a lot of caching and efficient reading and writing strategies that can outperform writing and reading raw data on a file system.
This goes against conventional knowledge. I've always heard (and followed best practice) to avoid storing binary data in BYTEA columns that should otherwise be put on a filesystem or an object storage like S3.
I'd like to find out more about this, because in many cases it would be very convenient indeed to store it in the database itself.
The primary reason to avoid doing so is avoiding thrashing your buffers, along with increased size of backups, WAL bloat, etc.
Can you? Yes. Should you? Not at anything beyond a toy scale, unless you want to pay for more RAM to ensure that your normal OLTP queries don’t take a performance hit.
I had a system that has ~600gb of blob data in bytea that could have easily been an S3 bucket + db reference. It made backups way more of a pain than necessary.
It was intentional in the design, because I wanted total consistency with a single backup for the system. It worked great for years. But as we got more and more clients, it really should have been migrated to the above design to make sure our backups could be taken / restored faster.
So the original advice actually still stands. You can still start off with Posgres, store it in BYTEA columns, and then work on a plan to use an object storage as you grow. S3 may not be possible and you will be evaluating other options like Minio
You know what, I agree...original advice stands. I should have known that the data I was storing would eventually grow larger than reasonable, so in this case it would have been smarter to design it properly from the start. Hindsight.
But there are still plenty of cases I would say BYTEA is perfectly reasonable choice.
I guess I just don’t see the point for most applications. If you’re using a DBaaS, as most are, object storage from the same provider is almost certainly going to be cheaper, and it’s a trivial amount of code to handle the separate push / pull code.
In our multi-tenant e-commerce application we handled it like this:
* Metadata for each file is stored in the database
* The application accesses files through an abstraction based on that metadata, and doesn't care if the actual file is stored in S3 or the database
* File types which are small and few, are stored in the database. For example letterheads, logos, terms-and-conditions. (a few gigabytes total)
* File types which are big (e.g. CSV-reports) or many (e.g. invoice-PDFs) are stored in S3 (several terabytes total)
* Development and test systems often use the database for everything and don't have an associated S3 bucket.
* Most of the application remains usable without S3 (access to invoice PDFs and CSV-reports is not critical)
In our case the DB was Mongo, but I expect Postgres to work the same.
We actually stored all files in the database originally, and only migrated after it reached several terabytes. It worked perfectly fine, but was rather expensive. So next time I'd go for S3 for the start.
I've done that before and the code was a mess. It works at the beginning but APIs do much more than piping data from the database. When you start dealing with ACL, external calls, code reuse, etc. It's just nice to have all the tools available to you from something like Python or Go.
The main caveat as someone who works on mostly average web CRUD apps, is that "Use PG/SQLite for everything" usually falls flat when the tools I use day to day don't support that use case super well or have rougher edges.
If your framework/ORM/whatever of choice doesn't support the full feature set of that driver compared to Redis/ES/Whatever you're replacing, you'll find yourself going down rabbit holes doing workarounds instead of staying with the "happy path" and just using separate tech for what it's specialised in.
If you already are doing most of these sorts of features by yourself instead of with frameworks, maybe it's fine, but this does start to feel like a time-to-release hindrance if you don't want to fiddle with the minutia.
> PostgreSQL allowed us to use a fulltext search plugin to do everything in one system. No need to sync any data. No need to maintain and run two systems. It just worked and made us smile (after some tweaks of course). Simplicity.
I found MariaDB to be wonderfully simple to use for somewhat casual use cases: https://mariadb.com/docs/server/ha-and-performance/optimizat... and still reach for it in some personal projects, however the whole growing MySQL incompatibility is a big issue if the tech you use only officially supports MySQL and you can't (easily) get MariaDB specific DB drivers.
Personally, one of the best things about PostgreSQL is transactional DDL, every DB should support it. Also they handle JSON pretty nicely (though I'd prefer not to store data like that unless necessary) alongside excellent plugins like pgvector and PostGIS.
On the other hand, for things like queues, or even any sort of blob storage, I'd look at things like RabbitMQ or Garage (S3 compatible). Sometimes specialized software is nice for keeping things logically separated. I maintain that it's good to be able to divide your stack up by mechanisms/concerns (rather than business domain necessarily).
as someone who loves postgres, this take is getting pretty old. yes we can do quite a bit with extensions but extensions often need to interface with external systems and even then managed providers don't consistently support all extensions. some gaps: bm25 indexes, olap support, also extensions also run into licensing restrictions.
The repo includes also plpgsql_bm25rrf.sql : PL/pgSQL function for hybrid search ( plpgsql_bm25 + pgvector ) with Reciprocal Rank Fusion; and Jupyter notebook examples.
For full text search you could also use pg_search (Tantivy) which looks very cool.
But at scale you probably don't want to manage a bunch of mission critical systems that were jacked into your database server. The database is slow? How do we monitor that?
So I would definitely begin like this, but you need to have a plan to break all of these out sooner or later.
Bona Fides: I learned c on with the K&R book on an Amiga (and transitioned to enterprise software engineering from there).
This seems like one more "When all you have is a hammer, everything looks like a nail" take. I agree with the other commenters who advocate for best-of-breed (e.g. Kafka, etc. for a message queue). PS I freakin love PostgreSQL as a relational (or even a time-series or OLAP) DB.
At Comper we have a very hot key-value store for annotating git data. We maintain a parallel git-blame data structure so we can do incremental "git blame -w -M -C -C". Typically a very expensive operation, but if you make it incremental, you can make it very cheap when new commits need to be analyzed. However, building the git blame tree is still pretty intensive for large repos.
We currently use rocksdb with storage on the same node, and hit rocksdb 1000s of times per second during our analysis. About 20% writes, 80% reads. The issue is that we need to start scaling horizontally, for burstable workers and zero-downtime deployment. So we're thinking to offload to an external kv service instead of a local rocksdb.
TiKV seems a good replacement, about 3-4x slower, but very scalable. Reading this article, I think a separate postgres cluster with unlogged tables might be a good idea. If anyone has some experience to share, let me know!
I love postgres and use it heavily, but I still don't fully understand how it overlook MySQL. Maybe because of Heroku adopting it.
MySQL was generally faster, and while MyISAM was a bit limited Innodb was pretty powerful, and you had the choice. It was also simpler (imo) and avoided a lot of the xid/vacuum issues.
That said, still love Postgres. But at the time it started eclipsing MySQL, MySQL felt better positioned.
MySQL was a proven solution back in the day, i.e late 2000s. Github/Twitter/Heroku etc were using it. In the past 10-15 years, Postgres has come a long way.
MySQL was always behind in terms of features. In early 2000s it had very limited constraints. Most people were running in ISAM backend and did not even have transaction support. It was being used by people who did not understand how advanced relational DBs were being used. What changed is Postgres overtook its actual competition, which were Oracle, SQL Server and Sybase.
MySQL caught up as well as far as I know, but it still may have some poor defaults that are widely used.
In the early days, PostgreSQL was so much more awkward to deal with. Crash-prone at first, and then there was the whole business around having to drop the db during upgrades. It didn't really match MySQL operationally until around 2002.
During the dot com era it was common to develop and launch on MySQL with the intention of migrating to something else if they became successful (though your typical LAMP stack developer regarded Oracle and SQL Server as being deeply 'weird', so many were willing to stick with MySQL despite the well-known limitations of MyISAM).
From where I'm standing, it seems that PostgreSQL became clearly preferable for new projects from the mid 2000s onwards, but it was only the Oracle acquisition that began to push existing users off MySQL.
I would agree with that, although I personally found it preferable in 2001-2002, since it matched the feature set of SQL Server in all the important ways that MySQL didn't. I think it may have been late 2000s before it was clearly preferred for new projects and even later before it was the thing you had to explain why you weren't using it.
edit to add: MySQL did have operational advantages even later than mid 2000s since it had master/master replication from very early (I don't know how sound it was, I doubt it was perfect). That was a real reason to choose it. We still don't have it in Postgres without extensions and even there citus is not really the same.
Oh yeah, we were early adopters of MySQL's master/master replication at an online retailer. Certainly an "interesting" experience, and I remember becoming very well acquainted with the vagaries of the binlog!
(I remember it being heavily touted in the first edition of the O'Reilly "High Performance MySQL" book. The second edition was about twice the length, with most of the additional pagecount going into detailed explanations of why you should actually be very careful and do lots of testing before deciding to rely on master/master!)
Actually, I remember that one of the drivers away from MySQL before Oracle came along was the 4.x and 5.x period, when there were various performance regressions. And even when those were sorted out, the introduction of InnoDB made people realise that MySQL's apparent speed advantage was really just down to MyISAM lacking referential integrity and transactions.
Certainly, there were people clinging on to MySQL 3.23 for read-heavy data warehousing applications for a very long time.
> And even when those were sorted out, the introduction of InnoDB made people realise that MySQL's apparent speed advantage was really just down to MyISAM lacking referential integrity and transactions.
Yes we saw the same thing play out with MongoDB. Ack without fsync is indeed very fast.
In the times when everyone was installing Apache + PHP + "Some database" stack, the easy path was to use MySQL for a very simple reason: it had ready to use MS Windows installer.
Another thing: those were times when web applications were practically 99% reads, and not so great ACID was a non-issue.
Postgres is OK, but it has really a lot of quirks that are not that obvious.
MySQL had some problematic design decisions initially. They might be fixed now, but the impression remained. And later there was the added complication that they were bought by Oracle, so you didn't really know how this would turn out in the end.
PostgreSQL also had more features back then, e.g. the JSON support is very nice if you need to do anything that doesn't neatly fit into the relational model.
Not only where the problematic design decisions, but large numbers of people said for years "nobody will never need/want that anyway so stop talking about it". That is they didn't even attempt to talk about trade offs, you were just wrong if you suggested anything else.
Then people who knew something got involved (or likely were involved all along - but I never followed MySQL so I'm not sure) and fixed those because they matter and suddenly the crowd shut up.
Back in the day, the sentiment was the MySQL was more-performant but the criticized tradeoff of having "cut corners". I still remember when their transaction support InnoDB table engine came out. Anyway Postgres was viewed as slower but more standards compliant - so mature architects preferred that. MySQL, in my opinion, fell into default usage among LAMP stacks and PHP-using kiddies. Postgres took the crown over time.
I've not interacted with mysql a whole lot, but when i did I was regularly surprised that it didn't have stuff I was missing from Postgres. Off the top of my mind:
- Query planner is much worse (just yesterday I had to USE INDEX to sped up a query by 300x, I'm near-certain postgres would just have gotten it right)
- Indexes are much more limited: no GIST, no GIN
- No transactional lock (`pg_advisory_xact_lock` in postgres). This one was very surprising, it's a really useful thing and I had to implement it myself as a lock table
At least you have access to USE INDEX on MySQL. On Postgres it's not rare to have a query suddenly perform awful in production because some switch flipped in the planner and now it's picking some random index
Good news is that they just added a plan stability feature in pg19. It's actually full planner hints, so you can edit the plan to whatever you want if you have no fear, but the main motivation is exactly index stability.
Recent MySQL comes with a rewritten optimizer (which they call "hypergraph") that will soon become the default, but you can already use if you want to.
One application I'm working on (CRUD, but with fairly complicated business logic) is seeing large performance improvements, especially on reports which join many rows from 10+ tables.
I regularly test the same dataset on both PostgreSQL and MySQL -- we support both, and this new optimizer puts them pretty close.
The current default optimizer has some pathological cases where reports run 8-10 times slower on MySQL compared to PostgreSQL. Not with this one.
Maybe things have changed, but my memory of MySQL ~15 years ago was that it was so... hacky. Basically, the (non-strict) JavaScript of the RDBMS world.
I always thought Postgres was the one that did correctness first, then performance, while MySQL was the inverse. I also enjoyed Postgres documentation. But in practice I have very little experience with MySQL, but I do recall it liked to silently coerce invalid dates and its UTF-8 wasn't quite UTF-8.
And MySQL apparently still doesn't support transactional DDL (i.e. BEGIN, ALTER, ALTER, UPDATE, COMMIT), which is quite nice for db schema version migrations.
It's more "what one tool can do everything", not that its ideal. Like why people use Microsoft Teams even though its terrible.
The relational model and sql force us to simplify our data models too much by eliminating relationships or just not dealing with them.
Think about a nested json blob from some web service api and storing it in SQL in normalized tables. No one is going to do that. Everything just becomes a denormalized mess and everything is hacked around it.
Instead of modeling things in the proper way, most of the world's data is modeled in a way so that we don't have join explosions in sql queries because they look scary. Data pipelines become these scary batch transformations where data is dumped somewhere else without anyway to trace back where it came from.
I encounter so many end-user applications and systems where you wonder: "why couldn't they allow a list of items here instead of a single box" or "why can't this reference this other thing".
"Work with" != "work well." GIN indices aren't the same as B+tree, and even then, you'll have to decide / know about jsonb_path_ops vs. the default operator class. Or you just accept sub-optimal performance, I suppose.
The lack of a rigid schema makes it super fun as well. Does this attribute exist in this row? Who knows! Maybe there's a long-forgotten version lurking, waiting to be retrieved, that will utterly bork the calling app.
I use a SQL databases as needed. I've used Postgres, Sqlite, Duckdb, Json files with AWS Athena, Oracle enterprise for ERP systems (a multitude of schemas and objects with interoperability), and others.
I'm currently, deploying Duckdb with AWS S3 Tables (Iceberg) to see how it fits for a use case I have.
IT is great and always changing. Keep trying new things.
My go-to has been replicas for each use case, with well defined semantics for replication lags. I'm working on something that does most of this seamlessly (transactional,search, columnar & time series, vectors and queues) without having to bother about extension management, replication setup or tuning.
Hopefully there is some value in this - one click multipurpose postgres fleet.
> Timescale lately released the pgvector extension, that turns your PostgreSQL into a vector database.
I don't think this is accurate and smells like an LLM hallucination to me.
From the Timescale/Tiger Data _pgvectorscale_ project's README:
> pgvectorscale builds on pgvector with higher performance embedding search and cost-efficient storage for AI applications.
I think this is where the confusion originates. I believe pgvector is primarily Andrew Kane (@ankane) and a cadre of OSS contributors.
As an aside, I've used Timescale/Tiger Data products and was very happy with them and their support. Their team was very engaged and responsive to all of our questions. They also fixed a pretty gnarly indexing bug I uncovered in pgvectorscale in an impressively short amount of time.
Postgres is great, but I certainly don't think it's great for everything. For instance, while you can in theory implement OLAP aggregation you're going to be hand-rolling a bunch of stuff that something like Clickhouse gives you for free declaratively.
I don't think the point is that PostgreSQL is great for everything.
But you may get by with a single piece of infrastructure instead of 7.
In most of the applications we build or maintain we use PostgreSQL + cloud storage. That's it. And it works very well, also for: storing JSON, full text search, as a queue, as a vector database. Other software may be better at providing those features, but I'm extremely happy we only need to understand & manage PostgreSQL.
The article says verbatim “PostgreSQL Replaces Clickhouse”.
Coming from storing billions of rows in Clickhouse and performing dozens of materialized operations I shudder to think about what that would look like in a DB that doesn’t even support declarative IVM.
The article suggested using TimescaleDB which has its own concept of IVM: continuos aggregates. And compared to the approach by ClickHouse it can also update the materialized views when you update/delete old raw data
Yes but that is just one use case. Columnar OLAP engines operating on object storage can do all kinds of stuff so much better than Postgres that it may as well be a completely different capability. That said - the point is that you can get a lot further with just Postgres than many people think, and now we also have options like pg_lake. But I wish I'd changed analytics platforms A LOT sooner than I did.
I use Postgres for a lot of things where textbooks say not to, but not caching. I'm not going to do it with triggers. Maybe if it supported TTL properly, even then, probably don't want to think about whether caching will bog down the rest of the DB.
This is exactly how tightly coupled, unmaintainable software is constructed.
By picking the tools before understanding the model and building bespoke architecture.
You pick the tools that the business model requires. It might be a relational data store. It might not be. You might want an event store. You might want to reduce costs with lambdas and DynamoDB. You may need a pub/sub event broker.
The OP clearly loves Postgres. Cool. They also have limited experience with complex systems architectures because if they had that experience, they would have never written this article.
> This is exactly how tightly coupled, unmaintainable software is constructed.
No. If you're struggling to build software against a DB and then abstract parts to use Redis or ES or whatever in the future, that's kinda a skill issue you or your team have with building poor software to begin with. Nothing to do with using a DB for multiple things like a Queue/Search etc.
I spent about a year as a consultant in the AWS space, visited about ~15 clients of varying sizes.
More often than not there's a single pg aurora instance responsible for 50%+ of the bill. Even worse are the serverless aurora offenders.
All the indexes and guarantees of PG don't come cheaply and dynamodb pricing is not cheap but comparatively reasonable. It really is a good product if you know how to use it.
Why write a fanboy text with unfair comparisons that hide the Postgres limitations?
For instance, for many simple needs MySQL is simpler than Postgres, with similar performance and consistency.
* No need for a connection pool, while many use cases with Postgres require PgBouncer and Co.
* Easy sort (and basic search) of multilingual text, because MySQL has case insensitive UTF8 collations.
* No need to VACUUM, which can be a hard problem (it was, the last time I used Postgres).
For full text search, I once worked on a project that considered several alternatives for this, including Postgres. Manticore Search was finally chosen because it was more performant, with better search results.
If you run a single application, or a few instances of the same application, you don't need an external pool and most frameworks have an internal connection pool anyway.
Not sure if I'm missing anything here, but if I want case-insensitive search I simply create an index on lower(column) and use that to query.
VACUUM is something you need to pay attention to at scale. And at that point you need to know your DB anyway and tune it. For smaller applications (and I don't mean only toy applications) it usually isn't an issue.
> * No need for a connection pool, while many use cases with Postgres require PgBouncer and Co.
is there a strong evidence you even need client side connection pool at all? What is the purpose?
The limitation is that you have many clients with connection pools, they hold internal PG connection without allowing it to be reused by other clients..
Tbf you can also achieve this in Postgres, it's just not present by default. From the docs [0]: CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
Ive built data warehouses and job queues on postgres. The DW got replaced with bigquery when we started doing more tracking. We still run postgres as an app-facing cache of the aggregated data from bigquery though.
The job queue runs on the cache db, scheduling jobs to move data from bigquery into postgres. It’s pretty neat.
Now we’ve run into near-real-time requirements so clickhouse is getting thrown into the mix.
It’s pretty funny the lengths we go to to implement user facing analytics that’s basically just “you are visitor number X” from 1995.
Funny. I was just this joking this morning with a colleague about using Postgres for everything.
Considering adding mongo for unstructured data? Just use postgres jsonb.
Building a search index? Postgres is fine too.
Considering using redis for fragment caching? Just use an unlogged table in postgres with key value columns. Need pub/sub? Well just use postgres listen/notify.
Using postgres for everything has served me very well.
As usual, it depends on the scale, but it's a sane default for 99% of use cases.
Different use cases have different scalability limits in PG, when you get to them you need to deal with them.
It would be perfect if it had somewhat transparent sharding, I mean a way to add another instance and distribute load without having to stop everything.
There are solutions, but they tend to be involved and when you get to that point in many cases it makes sense to just move that workload to something else that scales better.
NVMe drives + Litestream + object storage(S3/R2..). sqlite simplifies things for the entire long tail of apps/services that aren't the Ubers and AirBNBs of the world.
It looks interesting, but deciding on where to put object storage is what keeps me from doing this. I don’t have an AWS or Cloudflare account and I’m not sure what to commit to.
Also, apparently Litestream could use a filesystem instead of an object store?
Hey Raphael Bauer, if you're reading this, I suggest you change the color of hrefs on your website, all of them are purple and underlined, which usually is indicator for "Already visited URL". For me that's not that much of a problem, but it was something i kept noticing when reading the article. I wonder if anyone else also had this thought or am I alone as I didn't see anyone else mention this in the comments. But I wanted to signal that nevertheless :)
Supporting more than 1 writer per process. Strict typing. Access controls. Replication at scale is more effecient than copy-pasting files (seems SQLite has improved on this one).
"MySQL was also potentially faster as it did not implement all features of the SQL standard. "
This is a not great start. I assume it refers to MyISAM which has not been relevant for over a decade at this point. InnoDB made different design than PG decisions and was (and perhaps still is) faster at point lookups.
Well, the poster explicitly talks about 2003 here: „ In 2003, MySQL was much more widely used than PostgreSQL. MySQL was also potentially faster as it did not implement all features of the SQL standard“
true to that - currently using psql (in a single monolithic codebase) as: sql db, json db, vector store, logs store, full-text search, queue, message bus.
Yes, especially something that touches DB. Router and shell are stupidly simple and you get auto failover in some 30 minutes. That is something make me stick to mysql.
Hardware is so far nowadays to make people with little systems knowledge confident to make such claims at least from their use cases. However it is neither generally reasonable nor efficient.
Years ago I used PostgreSQL under Django to drive an industrial test and inspection robotic cell (which I also designed and built) at a major technology company. It worked very well. PostgreSQL maintained machine state, path planning, sensor readings, faults, operator input, etc.
I wanted to see how far I could push that toolset. It worked surprisingly well. Django's capabilities meant such things as multi-user login pages, access controls and remote monitoring were very easy.
For the graph database idea in Postgres, PG 19 has native support for property graphs [0]. You can set up your tables and their relationships as nodes/edges, then query against them using Cypher-esque [1] syntax.
i love postgresql but once we added ai-generated dashboard to our homegrown analytics tool [0] some of the crazy (amazing) dashboards that the ops team was building began accumulating horrendously slow db queries. I considered dynamically adding indexes or alerting around postgres slow queries but also quickly prototyped mirroring the postgres data in clickhouse. At first could not believe how fast clickhouse was on arbitrary analytics queries - like 60s to 0.5s for some gnarly queries. truly amazing software that just works without any tuning for this kind of exploratory analytics workload.
so yes, i'm still a postgres maximalist (worker queues still in pg [1]) but (especially in the age of quick LLM prototypes) it's always worth measuring the more purpose-built approach.
Hehe, not too popular an idea is it? Maybe there's some characteristic about a version control system that makes it qualitatively different from a file store. Maybe it has nothing to do with size or number of customers!
This isn't just theory either, for example: Revolut is a bank that does all its event persistence and streaming on top of postgres. No traditional message queues/brokers in their stack.
https://medium.com/revolut/recording-more-events-but-where-w...
As SRE dealing with this at current company, a benefit of using well known software like Kafka is a lot of problems you will run into have solutions/guidance already available vs you having to explore solutions which a lot of time end with “Kafka could easily do this. “
100% except when Kafka goes wrong, who maintains it?
Nose goes
There are two sizes of companies: those that can afford '1+ dedicated ____-person' and those that can't. Which should filter through to technology choices more than it does.
Often you start as the latter and grow toward the former.
That transition can be super super painful as you don't quite have enough work for the dedicated person.
It's not 1+ person when a system needs to operate 24/7. To have a proper on-call rotation you need 5 to 8 people.
One specialist and a group of generalists is often enough. Especially if you are allowed to contact the specialist outside normal working hours in rare emergencies.
Or, you can do like one of my former bosses, and just rattle off a list of 60+ major projects which would require a team of 10 to make any reasonable progress on in the near future, then pin it on one underqualified person, refuse to provide a proper budget, and continually press them about why targeted deadlines are being missed.
I mean, with this custom thing, you have that question as well with downside is you cannot pick up the knowledge from off the street.
I became the Kafka guy at my current company, it took me about a week of reading and every time I had further question, I didn't have to bother anyone, I could Google and get data I needed.
When it's some NIH thing, you have to bother coworkers and knowledge is whatever is in YOUR company knowledge base with no ability to get knowledge from outside the company.
EDIT: You could also leverage contractors or outside support if not homegrown software.
Yup, and every time it breaks, you've got to pester someone whose job probably isn't maintaining that thing actively.
I worked at a startup with massive NIH syndrome, once. We even used our own in-house programming language, because it was "better than anything else out there on the market." It did have a lot of nifty features that others don't have: a pretty novel type system, programmatic macros, a built-in build system and other fun bells and whistles -- but also not-so-fun ones like having no syntax highlighter, LSP, or debugger, and having to constantly shuffle around your code to avoid ICEs in the compiler.
The compiler wasn't the product, but we found ourselves fighting that thing more actively than any of the real problems our custom programming language was supposed to solve. The CTO found himself spending all his nights and weekends mostly trying to get the compiler to not explode.
A few years later, after I had long left (for that reason, among many) I heard they switched to Python. Can't imagine how long it took them to get that all rewritten.
> e even used our own in-house programming language, because it was "better than anything else out there on the market." It did have a lot of nifty features that others don't have: a pretty novel type system, programmatic macros, a built-in build system and other fun bells and whistles
A DSL can work, but not for the features you list. Those features you already get from existing languages anyway!
If you need general programming language features like excellent type system, programmatic macros, a build system (doesn't need to be built into the language), etc... then use a general purpose programming language.
I have a DSL for backend/endpoints, and exactly none of those are in my feature list. What it has are things like easy way to specify access-control directives[1], the SQL query to execute, mapping request variables to SQL parameters, mapping SQL results-sets to response fields, etc.
I have another DSL for a test program. Both of those DSLs have specs that's literally 2x screens of bullet points and examples. LLMs can output those DSL programs because the spec for the DSL is so small.
For general purpose programming stuff (while loops, conditionals, etc) my DSLs break out to Python.
A good indicator that you shouldn't be creating a new language for production is when you find yourself implementing conditionals, loops, etc.
===========================
[1] Limit endpoint to specific roles, or members of the same team, or both, or even to the user itself - someone calling `/user/profile/update` should only be allowed if the profile they are updating is theirs, for example.
Ah, but you see, it was a general purpose programming language. A general purpose, functional, optionally-typed programming language, with its own optimizing compiler and build system. In fairness, it wasn't originally developed to be our in-house language, but it was the creation of the CTO.
It was fun while it lasted and I had a lot of fun working on it. But it was really not a good business fit. The programmatic macro system was supposed to allow us to build customer-facing DSLs on top of it, but everybody just wanted Python anyways.
> A general purpose, functional, optionally-typed programming language, with its own optimizing compiler and build system.
> The programmatic macro system was supposed to allow us to build customer-facing DSLs on top of it,
Honestly, it sounds a lot like Lisp.
As a former Lisper, I don't doubt that it was a bundle of fun :-)
This problem doesn't go away with postgres. It's totally anecdotal but this is one thing that I've noticed different in mysql shops and postgres shops - with mysql there is usually at least one person on staff who knows MySQL DBA and scaling pretty well, with postgres it's rarely the case to have someone who knows the internals well - like you said, the person capable of maintaining it when it goes wrong.
You could argue it's because postgres requires less poking though I would say you don't need the DBA for when things go right.
Of course most people are just handing the management off to the cloud and that's potentially why, but it doesn't cover everything
MySQL will generally run fairly well with default tuning, assuming you've sized the buffer pool well relative to the amount of RAM you have (cloud providers do this automatically, but it's also not that hard to calculate). There are some knobs you can turn to eke out more performance in certain situations, and there are some defaults that are truly terrible (lock_wait_timeout is set to 1 year...), but all in all, it doesn't take a lot of care and feeding to run reasonably well.
Postgres, on the other hand, has a million knobs, many of them interact, you'll find conflicting advice for some of them, and it can rapidly fall over if you aren't keeping a close eye on long-running transactions. It's also more performant than MySQL in _most_ situations (hello, clustered index), if you've tuned it correctly. It also of course has far more extensibility out of the box, with tons of index types that are extremely helpful, if you know how and when to use them.
This difference is why I'm always frustrated when people parrot "just use Postgres" as though that solves all problems. It's an extremely powerful tool that can replace most of your stack, yes, but it also would really, really like you to RTFM. Not random Medium blog posts, the canonical documentation.
> You could argue it's because postgres requires less poking though
This is the myth people who parrot "just use Postgres" believe. It is false, obviously
I love the naming Kafka. Either they knew what it stands for or they didn't. And the latter is the worse option.
they likely have something on top of PG to distribute data across shards, which is still untrivial task I think and require ops overhead.
If you start here, with the "Postgres will take you wherever you need to go" meme, without thinking extremely deeply about your schema and how you expect to evolve it in the future, you can easily paint yourself into a very difficult and expensive corner.
It's easy to use Postgres poorly in ways that result in painful centralized bottlenecks.
(Obviously this is largely true for anything, but I think that in 2026, where there's also a lot of more-specialized/less-fleible but much-easier-to-scale well-supported mature alternatives, you should be VERY wary of making everything have a single central SPOF. What are your users going to expect in terms of maintenance windows, etc.)
I'd be cautious with articles that say things like "All cloud providers allow you to run (and scale!) PostgreSQL by clicking a single button." with no mention of how long that will take and what options should be set to make it faster, or the costs of those things.
It doesn't take very long (because compute and storage are separate in most of them) but good lord does it get expensive. Every time you click that upgrade button you are doubling your cost. It's really painful when you have a spiky workload that is performing fine like 95% of the time but you are watching the p99 and need to double the cost of a very expensive infra component, only to improve the experience of the heaviest 4% of your workload. This is to say nothing of the gambit you then have to play with reservations/prepays.
I haven't seen a way to get guarantees of upscaling operations under like 30 seconds (with Multi-AZ RDS) with well-supported RDS stuff (leaving out active-active setups with logical replication because that's a whole other can of worms).
If you know you're gonna be ok with that for a long time, go nuts. I'm just saying: think about it in advance!
The cost pain for spikes is also a thing - some of Aurora's billing models look potentially promising but I haven't used them in practice - though it's also somethings that's harder to avoid with alternatives. Distributed DBs aren't generally super friendly to dynamic scaling IME.
I dunno. I think the main takeaway here is that you can do 80-95% of your stuff in Postgres and eschew all the unnecessary, unproven stores.
Having done that, e.g. used rabbitmq plus postgres, honestly I wish I had just used postgresql for both messages and data. It would have been easier to manage by an order of magnitude, especially at scale and needing to satisfy enterprise requirements. Also the flexibility of postgres would have solved problems that we ran into because of limitations of rabbitmq.
And now there’s pgmq
Without knowing any specifics of your uses, my usual starting point on that sort of design is that "messages AND data" is it's own special little way of ending up with a hard-to-debug-and-operate system. ;)
It's very hard to best-of-both worlds event-driven system + RDBMS-storage, it's very easy to end up with worst-of-both-worlds. Hello distributed transactions!
Again, you just should think about all the ways you want to use it and the maintenance/uptime requirements your users are going to have in advance.
I think messages + database are extremely common in any sort of large application where you have data processing nodes. Postgresql actually has very good mechanisms to support message style communication, and as long as you design your message tables independently you shouldn't have horrid issues around locking and transactions. Message queues don't save you from thinking about that anyways, they just replace transactions with acknowledgements.
Trying to manage a highly available and durable rabbitmq or other message system that can also be recovered from backup to an offsite mirror infrastructure in the worst case is actually incredibly difficult. Usually these systems are designed with the assumption that you can just regenerate messages based on database state anyways in worst case scenarios.
In this use case your database already is highly available and can recover on an offsite backup if you have suitable wall shipping going on. So you've done all the hard work once, may as well reuse it unless you truly have some mind bogglingly large message throughput needs.
Finally, we had a need of a queue that was more than just first in first out. We wanted to fairly balance workloads across users and tenants. Whenever you have such a need postgresql lets you design this type of queue far easier than trying to do some elaborate multi-queue setup with a traditional queue.
A counter anecdata. We transitioned from a postgres job queue to Rabbit. We had never ending problems after that, many of them were misunderstandings, some where just wrong-fit. We migrated because we had some time on our hands and thought we would alleviate some high pressure jobs. Not only did it not solve the problem, but having written all the code that decides when to pull the next message and what to do with it, and how to dead-letter it - just worked great for us on Postgres. It was so easy to understand and doing things like reprocessing just using a standard postgres DB interface was much easier.
Ultimate the entire processing got removed from our team and no longer needs to do these deployments (acquisition transitions)...
I tried very hard to use postgres as a queue, it was robust but slow once I started to push from more than a few processes/servers. Moving to zeromq initially and sqs after solved all my perf issues, and was still solid.
I think that’s the way to go. Start with Postgres and only if there are problems, then think about something more specialized. Same for microservices. Start simple and introduce a service when really needed.
I hate it when people already start out with 10 or more different systems/services for a few messages per second.
it's not the way to go if you hit the limits very quickly and have to waste immense time migrating.
From my experience the limits are way higher than people think.
I think that using the right tool for the job is important and saves a lot of time in the long run. There are expensive headaches that we have to resolve.
I never know what to think about the phrase “ right tool for the job”. It almost feels like a platitude at this point.
What were your experiences with ZeroMQ?
It worked fine, we had no issue with it. We moved to SQS because it was one less thing we needed to spend time on.
Honestly Rabbit sucks more than it shines
Also it is very "unconventional". Everything has to be done in its weird and quirky way
It’s also entirely possible that nothing you do in the eventual history of your company hits a scale where this matters.
Sometimes to scale is "continue to satisfy SLAs as service usage increases" and other times to scale is "successfully evolve functional capabilities over time."
While the GP may have been referencing the former, embracing "PostgreSQL for Everything" often prohibits the latter.
It *can* do that. I wouldn't say it's necessarily "often". Again, it's hard to predict the future.
I think that the "use Postgres for everything" messaging was a necessity, even if it is overstated. Use it until you can demonstrate it doesn't meet your near term needs. When that happens, shift. It wasn't that many years ago when I'd enter situations where people were knee deep in FAANG level infrastructure when postgres on a relatively small instance would have more than been sufficient. I'd suggest they look at converting to postgres to save money & all the energy they spend maintaining their soup. "It won't scale the way we need it!". Sometimes they were demonstrably wrong. Other times they were half-right, in that the real problems was terrible decisions made at the software layer, leading to a situation that required heavier duty infra. Almost never were they actually right* though.
Might they have been right 5 years later? Perhaps. But I know for a fact that none of the ones I encountered were.</i>
> If you start here, with the "Postgres will take you wherever you need to go" meme, without thinking extremely deeply about your schema and how you expect to evolve it in the future, you can easily paint yourself into a very difficult and expensive corner.
Yeah, backwards compatibility is not a thing for Java, Rust, C++, etc. :eye-roll:
Meanwhile in SQL if you need to make a backwards-incompatible change to your schema you can always use VIEWs and INSTEAD OF triggers to maintain backwards compatibility for code you've not fixed yet.
> in painful centralized bottlenecks.
I find the opposite to be true. I cut out the decentralization and get it all one one machine, and the bugs go away and the perf improves.
I once needed to maintain an application written in everything Oracle. If I ever encounter the original author of that product: I have things to say to him.
We quickly replaced part by part by easier, less costly parts.
Software development is not just writing code; I think all HN users know that.
starling bank uk uses a similar kind of stack. both java based as revolut.
During pgConf.eu in 2016(-ish, could have been one or two years later; I don't remember too well), a representative of payment processor Adyen told the audience that they were, essentially, one big postgres cluster in their backend, too ("cluster" used as per the postgres-native meaning of the term, as in, an installation on a single host with a data directory containing any number of databases).
My general rule of thumb is "Use Postgres until you've discovered why you can't use Postgres."
Anything you introduce is another moving part you have to operate and maintain, and in the beginning, Postgres can probably handle it. Wait for load, see where its failing, and then you'll have a better idea if adding another tool is worth the cost.
Doesn't the same argument apply even more to using SQLite instead?
In a lot of cases using SQLite means you write queries incompatible with RDBMS. No need to worry about race conditions or the amount of queries you make, when 100 selects are uber fast.
Yes, for very small or embedded, single-purpose systems sqlite is usually a good choice. It's very well tested, and there's nothing extra to run or manage. But be careful if the system starts growing beyond that, you'll want a real RDBMS before you abuse sqlite too much.
Depends on what you mean by "small" and "growing".
If you mean database size, SQLite can handle massive amounts of data. I've seen 281 TB quoted as theoretical max size.
How does it handle 100000 write transactions per second ?
From multi-client architecture and with regional HA?
Not really. They are two different paradigms. Use the one that is right for you.
SQLite is embedded for local applications with one writer mostly.
Postgres is for a client-server architecture with many writers.
When you start a project, you generally know which architecture you need.
So if it needs to work offline, but it syncs with a server, then you use both? (And the schema becomes some kind of lowest common denominator?)
I would probably do an event-source architecture, where you record events on the client and then push them to the server when you're reconnected. It has a lot of benefits, for instance, trivial auditing and free serialization.
yeah essentially, see electricsql
though they also have "pglite" running in wasm.
same concept though, sync slices to an embedded db.
SQLite is embedded for local applications with one writer mostly.
In 2026 that advice feels antiquated. SQLite now is absolutely useful now for concurrent, mutli-writer applications.
https://www.sqlite.org/src/doc/wal2/doc/wal2.md
Is it? Sqlite seems pretty fast. [1]
[1] - https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...
No. SQLite doesn't have users, proper views, row level security, proper foreign keys, or functions.
What don't you like about SQLite views or foreign keys?
For example you can’t insert or update a SQLite view.
I don’t even think SQLite lets you fully update a table schema.
If you start trying to use it for sql and not just storing rows you run into these everywhere. SQLite is not serving the same needs as Postgres.
You can use a trigger with INSTEAD OF to update a view:
CREATE TRIGGER update_customer_emails_trigger INSTEAD OF UPDATE ON customer_emails BEGIN UPDATE customers SET email = new.email, name = new.name WHERE id = old.id; END;
Maybe that's not as flexible as you need.
SQLites process for table updates can be pretty onerous if their ALTER TABLE lacks support. Its a 12 step process the docs have the temerity to call "simple" instead of "tedious and risky" - https://www.sqlite.org/lang_altertable.html#otheralter
Yes. I already did that once though, so I skip that step now (unless ofc it's a SQLite usecase).
I used this approach to drive entire app, and it works. Nearly all data are fetched from SQLite. User can select a database, which can change app views, and the data. In my experience it is quite fast.
My example for android app:
https://f-droid.org/pl/packages/io.github.rumcajs.offlineweb...
Note that I am not android experienced programmer, and I am still learning.
Another rule of thumb. Use what you are comfortable with until it stops doing what you want.
Well the problem is that sometimes there is just too much choice to make
The issue with this general rule of thumb is that we can swap Postgres for many others, including non-relational, and it works.
i don't think that's an issue. it's still a perfectly good rule.
use what you're familiar with, until it stops working. then use something else. postgres just goes a lot further than a lot of other tools before you get to the "use something else" phase. and postgres is the database a lot of people are familiar with.
My rule is "use a single database for as many of you persistence needs as possible". Often Postgres is a good choice for that database, but many other general purpose DBMSs will work just as well.
In my previous company we used MongoDB, for almost everything, including text logs, request logs, job-queues and small template files. We added S3 since storing terabytes of files in a database is expensive. Now, I wouldn't choose MongoDB for a new app, since the data model and query language suck, but it performed reasonably across many use-cases.
For my next application I will use Postgres as primary database. Probably will integrate S3 before going live, to avoid the necessary data migration later, but haven't decided yet if that's a premature optimization.
This kind of post (Postgres! It's all you need!) is getting pretty tiresome. Postgres does not even come close to a full replacement for Elastic, and that's just the first bullet.
Looking down the list it is pretty easy to go: Yes, postgres can be used instead of that for extremely basic use cases, but it all goes out the window you actually need any of the power of these other tools.
if you need elastic youre doing something wrong
Or something big. Which is often not wrong.
I'm partial to Typesense, especially for smaller data sets, since it runs primarily in memory, is easy to use and is hella fast. For bigger data sets, I hear good things about Meilisearch.
Fwiw, I, as someone who has worked on Postgres for a long time, also find it quite tiresome. Like there's plenty stuff I wouldn't use Postgres for, and I can probably get get more out of it than most.
Exactly - these recommendations often come with no context or scale provisions.
Yes Postgres can work in the small for a lot of things, it can even work at surprising scale if you use it according to its strengths.
But if you use it for things it doesn't shine at, at inappropropriate scale - you'll almost certainly run into issues. And resolving those can often be a bigger challenge than choosing a more suitable solution in the first place. But often I think younger/less experienced engineers just have to burn themselves, thus why this never seems to die.
Postgres its all you need means to me(IMHO) postgres for all Olap (DB + message) , not all analytical databases.
It can run analytics too, natively on some volumes of data, but also there are more specialized extensions.
sorry all OLTP
That's just it - most use-cases are pretty basic, and if you don’t know what you need then Postgres is probably a great place to start.
If you’re just starting out, keep things simple. Otherwise, you probably already know exactly why you need something more than Postgres.
The point is in general for people to just consider it, often people start out on their side projects or internal company projects and commission Elastic, Redis, Postgres, Kafka before even getting started. In reality they could fit it all into Postgres for a very long time.
Nobody is saying that a huge ecommerce store with complicated filtered search logic should throw away their Elasticsearch cluster and switch to Postgres.
If you actually start looking into these things, you often start looking at custom pg extensions, which means you just made the decision to "simplify" your stack by maintaining your own postgres cluster with custom extensions. This is just papering over the fact that you're increasing the complexity and saying "well it's still just postgres!" as you do it.
Not really, most popular extensions are available on GCP/AWS (https://docs.cloud.google.com/sql/docs/postgres/extensions) out of the box and there's many things that you can easily run in Postgres without extensions (Queue, KV store).
Installing & maintaining a Postgres extension is vastly, vastly simpler than running Elasticsearch and Kafka. Like, how could you even compare these things if you know what you are talking about?
Or maybe you are looking at it from "just swipe your credit card at AWS" perspective, in which case "just use Postgres" articles are for a different audience.
I have a lot of troubles with a small private instance of Rocket chat, all due to MongoDb stuff, versions, migrations and backups. I bet almost all private instances of Rocket chat would be perfectly served with Postgres.
Posts like this can be tiresome, yet the general consensus among developers seems to be "yeah, Postgre/SQLite is ok for 99% of the cases, but MY case is going to be in the 1%, because I am going to be the next Facebook".
The power of the other tools mostly shines in large scales. For most applications, though, performance of postgres more than suffices.
I tried to use rabbitmq for a small app, installed it, configured it and then it didn't work. Spent a day jumping through hoops getting it right.
Dumped it and used postgres, in half an hour. Worked like a charm.
Sure, best not to overcomplicate early if you don't need it.
PG is great and I work with it daily, but it's also not a problem to think about scale early and at least have a notional plan for what to and how to know when scale is becoming an issue in your system as you're designing it. Even PG is overkill and sqlite is more than enough for some of my projects.
There are a lot of specialized tools available, but you definitely don't need to put every one in your toolbox. Experience and observation help you make those edits -- and of course there's almost always room for improvement, but "good enough" definitely exists (until it doesn't anymore :D).
This is the way. Notionally building a space/path to scale into architecture early, but delaying implementation of that scaling component until actually needed.
Then a system gets most of the benefits of not accidentally making it torturous to rearchitect for scale, without paying the headcount / complexity cost until it's needed.
Would your app run equally well with sqlite?
I think it would be helpful if some of these posts included scale. There are almost always two groups talking past each other
i think 1 billion DAU is the exception here, so I would not expect everyone to constantly caveat personally.
Agreed, but many of the criticisms I am reading here are assuming high scaling requirements and invalidating the approach entirely. When there are many business domains that will comfortably fit within a modestly specced database instance.
Even just B2B vs B2C is a huge split. Plenty of specialized ERPs in the world that have relatively few "butts in seats" users but have a lot of data to work through.
Size of the data, read/write ratio, number of "requests"... lots of axes that change how much pain or not you're in by just pointing to pg
I broadly agree, but would tweak those numbers a little for small B2B apps: I ran a small B2B application on a cheap VPS using PostgreSQL as a primitive messaging interface, and even on a small VPS 50k DAU won't even cause the machine to break a sweat.
You're right that vanilla Postgres doesn't come close to replacing Elastic. There are efforts to resolve this, though, like ParadeDB: https://github.com/paradedb/paradedb (disclaimer: I work for ParadeDB)
"Disclaimer" means "don't take this seriously because I'm not an expert". You mean "disclosure".
My English fails me again :'). Thank you for the correction!
I see Tantivy mentioned in your readme but AFAICT there is no PG-Tantivy sync. I also see "native vector support is coming to our search index soon". Could you clarify?
What do you suggest for a language like Malayalam which has no native support, preferably with low RAM requirements?
> Postgres does not even come close to a full replacement for Elastic
Size matters!
For most of the application out there elastic (or kafka or any other specialized tool) is just too much(and too costly). They can do fine with postgres or mysql. Actually, I'd argue that in a lot of cases even postgres is too much, probably sqlite is enough.
I use SQLite for everything, and I'm perfectly happy with it. I'm aware of the concurrent writer issues, but at my scale it doesn't even matter.
Yes, especially for a web app where there’s realistically only a need for one VM/server. By the time you outgrow that approach, a very straightforward migration to Postgres is probably the least complex problem you face.
Same, I posted some corrections to Dr. Bauer's article: https://joecode.com/2026-08-19-sqlite3/
Perfectly reasonable:
I'm a huge PG fan, so I start everything with it, but SQLite is sane, and it generally has a happy upgrade path to PG If you need it.
It's the other way around for me: as 99% of the stuff I develop is .NET (and I use EF Core for database stuff), I can get away with SQLite for local development, prototyping (and even staging), and then just "flip a switch" for it to run on production PostgreSQL.
Both are amazing technologies.
EF Core is so easy to turn into a disgrace for performance, developer experience, AND build times...
Yeah this really isn't so true anymore. I was a diehard Dapper fan for a long time, but the performance of EF Core is comparable to Dapper now. Dropping down to SQL in EF Core is super easy and parameterized. I just don't even bother with Dapper anymore. I have an app with ~200k users with the slowest query being ~6ms and of 1000s of queries only 3 are hand rolled complicated SQL written for perf executed via EF Core.
if you knew how to cook it, you could use make older EF Core (and original EF) go brrr. you sound like you do know. my problem is that it is hard to find out what exactly is the best without dropping down several abstraction layers, sometimes, as you noted, to raw SQL.
why not just construct SQL queries in a type-safe DSL, like, say, JetBrains Exposed does? you are writing what's basically SQL that your compiler understands and your existing tooling checks for free. (granted, C# may need an additional Roslyn analyzer, but it's still simpler than either guessing what transaction LINQ will make or writing SQL in strings.)
I can't recommend this "switch". If you are not testing locally with the same relational database as in production, you can miss mistakes and bugs. This is not just theoretical. One example where I thought I will be fine using SQLite was with a small Django project. But time and time again I ran into limitations of either SQLite or Django's database adapter for SQLite, when it came to dealing with many to many relationships in the model and through tables, requiring me to work around the limitations. There is no guarantee, that these workarounds in turn will work the same in PostgreSQL in production.
Anyway, it is a basic practice of keeping test and dev environment as close as feasible to production, to avoid missing issues and wrong assumptions.
> Anyway, it is a basic practice of keeping test and dev environment as close as feasible to production, to avoid missing issues and wrong assumptions.
Containers are great for this during development.
Testcontainers are great for tests in particular when you don't want to use some mocked in-memory DB because those have the same issues as using a different DB during development: https://testcontainers.com/
Agreed, and it is easy to have a Postgres container for local tests or in CI as well. I don't really see much need to avoid Postgres in testing. Also if production runs in containers, testing can and probably should just run in containers as well. Also makes for a cleaner test setup usually.
That's kind of risky considering the radical differences between types in SQLite and Postgres. There are plenty of situations where EF will need to be configured differently in order to map your .Net types correctly to each DB. Or subtle differences in behavior due to storage differences (particularly SQLite's predilection for storing things as strings). It's so trivially easy to run Postgres in Docker that I don't really see the advantage of using SQLite for local dev.
The main issue with SQLite is the very poor type system, after testing it for an app I was shocked.
Are you saying that STRICT was insufficient for you? What more did you want beyond one of: INT, INTEGER, REAL, TEXT, BLOB, ANY?
https://sqlite.org/stricttables.html
Want SQL-92 standard DATE/TIME/TIMESTAMP.
Can't those be stored as integers with a desired resolution?
No data integrity, No automatic formatting, No timezone support, No standard date functions, etc
You can mitigate this with decent bindings, but I agree it's not ideal.
> Events, queues and persistent logs are getting more and more important in today’s software systems. Systems like Kafka, RabbitMQ, SQS and others provide that functionality. But maintaining them is annoying, custom and you need the skillset.
In tech stack choices, I prefer staying simple as long as feasible. That said, you also need to know and understand concepts at a thorough level.
The above comment from the article suggests PG as a central server that simplifies event architecture. As if the "annoying, custom and needed skillset" around those specific alternatives are unnecessary baggage.
If you know anything about queues, scaling, availability, access semantics, message formats and concepts such as delivery guarantees, you find out very quickly that the server which stores a queued message is not the high-order bit in that equation.
To me, your concluding sentence cuts the opposite direction. The server is not the most important thing, and each new kind of server that exists in the system is an appreciable increase in maintenance burden. To me, taken together, this is an argument for waiting until you have a very concrete forcing function to introduce that new kind of server for this purpose.
I think a good way to think of this is: What empirical metric will the introduction of kafka (or whatever you choose) move in a positive direction? Latency? Oncall burden? The amount of code you have to maintain? Do you have correctness or data integrity metrics that this change would register on? etc.
This isn't at all intended as an unanswerable question. Many or most organizations will easily say "yes" that they expect some improvement on some metrics by adopting the "right" system for the job. But lots of other organizations are cargo culting "well we know this is the right way, so we should do it this way" long before any metrics they care about would demonstrate the improvement.
Heartily agree with the metrics for analysis, but this seems anchored around a focus on introduction of "something else" without regard firstly to correctness.
> The server is not the most important thing, and each new kind of server that exists in the system is an appreciable increase in maintenance burden.
Sure, but compared to what? It's right to consider complexity, but understanding tradeoffs requires depth. The OP's original premise was that learning all the things about specific queuing services was unnecessary chafe; that INSERTs, SELECTs and UPDATEs are all anyone needs.
The maintenance burden sits with your producers & consumers (or publishers/subscribers, whatever your nomenclature...), whether you want it there or not.
My learned experience is that as soon as you start moving messages that are beyond trivial and carry different operational characteristics, you're going to have to understand those deeper concepts anyway.
I agree with you about being willing and unafraid of getting the depth in the concepts that matter. But that's also why I agree with you that the question of the server technology is the less important thing. I just think that leads to the conclusion "maybe stick with whatever you're already using for oltp for longer?" more so than to "you may as well introduce kafka".
I think both your questions, about correctness and about the comparison point, are answered by thinking through the empirical metrics you're trying to improve.
I often feel a bit out of step with other engineers on this point, but to me, "correctness" is not a binary yes/no, it is also a continuum. It's one of the metrics you probably want to be pushing to a large number of 9s, but trying to get to actually 100% has low ROI for what most people work on. (Not for everything! But for most things.) If you can already get to five 9s with postgres, but more 9s with kafka, is that worth it? Maybe, but maybe not. If you're missing way more messages than that and corrupting data because of it or some other bad thing, and this is the culprit, then that's certainly a good time to compare the trade offs of fixing your implementation or switching to a "proper" system for this.
And to "compared to what?", the answer is, compared to the current values of the metrics you've decided you care about. One of those might be the complexity of the implementation and the necessity to maintain specific expertise in a bespoke implementation of non-trivial complexity. This, to me, is usually the compelling reason to "buy" rather than build. But I think it is too often left too implicit and vibes-y instead of making it explicit and seeking to quantify the impact.
PostGIS is also another very useful addition for storing, indexing, and querying geospatial data.
http://www.postgis.net
I tend to agree with quite a few points in the article, but some topics warrant some careful scrutiny.
* As a message queue: Only if your required features are very basic, like if you need cluster communication and run your own coordination protocol on top.
* High Volume Time Series: TimeScale works, but composes badly with other workloads on the same DB server ( from an operational perspective at scale )
* Vector Database: The same issues as with TimeScale.. PgVector for example lives in its own seperate "world" and the query planner sees it as a very opaque thing. Forget about adding vector storage to an existing high volume db, that must server other complex queries.. PGVector will either trash your caches, or take over your cpu so that workloads that used to work fine stall. This is IMO not a pgvector problem itself ( Kudos to those guys ) but rather that postgresql extension apis are not very good at exposing custom costs and tradeoffs to the system as a whole.
* Raw Data: Works for small files... why anyone would want to store large amounts of data in it would be a mystery, where it shines is accessing LOTS of small files where internal caching etc help a lot compared to raw filesystem access ( also a bit dependent on the filesystem and its tuning though )
* Microservice: If your service is ONLY exposing json data from some database model, then it should not exist at all IMO. Create a view and be done with it.
I like to consider Postgres the starting point for all of these things, that can be outgrown and replaced when appropriate. I do love just shoving everything in Postgres and seeing that I only end up needing a few additional dedicated services as the product groups. Redis is usually the next pickup for me.
Sure, thats a good way of working.. I just have the experience when handing over a project ( consulting ) anything i have put in place will never get replaced or kept for too long outgrowing its capacity by far, and offset with huge expenses in hardware or operations. Technically not my problem anymore ( except when it breaks on a maintenance contract ) but i still like to avoid it early if i can
Also to note: (Not a fault of PGVector again just a limit of our algorithmic knowledge) PGVector does HSNW or IVFlat indices ... (there is nothing better persistent) however it breaks down with high latency at LARGE amounts of vectors ( 100MIO+ ) that seems like a high ceiling, but when designing production RAG systems, you tend to do per chunk embeddings, or even visual patch embeddings... e.g one page of a document becomes 1024 vectors in itself (for visual patch embeddings ) ... so you hit those limits at 100000 pages already.. something larger organizations definitly have.
I would keep a per-document summary, then dive down only into the filtered set. This is more production grade than selecting from billions of chunks.
> Microservice: If your service is ONLY exposing json data from some database model, then it should not exist at all IMO. Create a view and be done with it.
Yeah, this has nothing to do with Postgres. If the service is accessing a database that isn't internal to the service, then that database is already a standalone service in itself.
These types of articles needed to be written because we have gone way too much in the other direction. The issue is that people use too many tools prematurely when they are not needed at their stage. So yea, in most cases, you are probably better off just with Postgres. I m a culprit of this myself so I wouldn't say that I know better. It is just too tempting to setup too many tools to feel cooler or feeling that "we must use elasticsearch as no one does search in db".
This article seems like a reaction to DuckDB's surge in popularity. Having multiple DB engines to choose from is good and it often doesn't matter which one you use. One could make the same argument about DuckDB. Many database engines are multi-purpose. Though of course there are specific use cases where a different DB may be more appropriate...
Anyway databases nowadays are a commodity. A sticky commodity but nonetheless they are replaceable; increasingly so in the age of AI where data migrations are easier than ever.
Intrigued by this
> After some performance checks it became clear that PostgreSQL was even faster than reading from the file system for our use-case. PostgreSQL uses the file system very efficiently for its data - and it adds a lot of caching and efficient reading and writing strategies that can outperform writing and reading raw data on a file system.
This goes against conventional knowledge. I've always heard (and followed best practice) to avoid storing binary data in BYTEA columns that should otherwise be put on a filesystem or an object storage like S3.
I'd like to find out more about this, because in many cases it would be very convenient indeed to store it in the database itself.
The primary reason to avoid doing so is avoiding thrashing your buffers, along with increased size of backups, WAL bloat, etc.
Can you? Yes. Should you? Not at anything beyond a toy scale, unless you want to pay for more RAM to ensure that your normal OLTP queries don’t take a performance hit.
Agreed. Even putting them on the filesystem and rsyncing in a cronjob would be better, which says a lot.
Listen to this advice.
I had a system that has ~600gb of blob data in bytea that could have easily been an S3 bucket + db reference. It made backups way more of a pain than necessary.
It was intentional in the design, because I wanted total consistency with a single backup for the system. It worked great for years. But as we got more and more clients, it really should have been migrated to the above design to make sure our backups could be taken / restored faster.
So the original advice actually still stands. You can still start off with Posgres, store it in BYTEA columns, and then work on a plan to use an object storage as you grow. S3 may not be possible and you will be evaluating other options like Minio
You know what, I agree...original advice stands. I should have known that the data I was storing would eventually grow larger than reasonable, so in this case it would have been smarter to design it properly from the start. Hindsight.
But there are still plenty of cases I would say BYTEA is perfectly reasonable choice.
I guess I just don’t see the point for most applications. If you’re using a DBaaS, as most are, object storage from the same provider is almost certainly going to be cheaper, and it’s a trivial amount of code to handle the separate push / pull code.
In our multi-tenant e-commerce application we handled it like this:
* Metadata for each file is stored in the database
* The application accesses files through an abstraction based on that metadata, and doesn't care if the actual file is stored in S3 or the database
* File types which are small and few, are stored in the database. For example letterheads, logos, terms-and-conditions. (a few gigabytes total)
* File types which are big (e.g. CSV-reports) or many (e.g. invoice-PDFs) are stored in S3 (several terabytes total)
* Development and test systems often use the database for everything and don't have an associated S3 bucket.
* Most of the application remains usable without S3 (access to invoice PDFs and CSV-reports is not critical)
In our case the DB was Mongo, but I expect Postgres to work the same.
We actually stored all files in the database originally, and only migrated after it reached several terabytes. It worked perfectly fine, but was rather expensive. So next time I'd go for S3 for the start.
> PostgreSQL Replacing Your Microservice
I've done that before and the code was a mess. It works at the beginning but APIs do much more than piping data from the database. When you start dealing with ACL, external calls, code reuse, etc. It's just nice to have all the tools available to you from something like Python or Go.
No mention of https://postgis.net/ - shameful
Or... Just use files for everything until you actually need a database. ( I know I'm going to get heat for saying that )
Anecdotally:
The main caveat as someone who works on mostly average web CRUD apps, is that "Use PG/SQLite for everything" usually falls flat when the tools I use day to day don't support that use case super well or have rougher edges.
If your framework/ORM/whatever of choice doesn't support the full feature set of that driver compared to Redis/ES/Whatever you're replacing, you'll find yourself going down rabbit holes doing workarounds instead of staying with the "happy path" and just using separate tech for what it's specialised in.
If you already are doing most of these sorts of features by yourself instead of with frameworks, maybe it's fine, but this does start to feel like a time-to-release hindrance if you don't want to fiddle with the minutia.
Tetris on postgres? Not doom? So not a candidate for everything!
Just kidding. Of course there's doom for postgres: https://github.com/cedardb/DOOMQL (pure SQL) and https://github.com/DreamNik/pg_doom (extension).
Oh and there's https://github.com/snaplet/postgres-wasm that allows to run everything else in postgres ^^
> PostgreSQL allowed us to use a fulltext search plugin to do everything in one system. No need to sync any data. No need to maintain and run two systems. It just worked and made us smile (after some tweaks of course). Simplicity.
I found MariaDB to be wonderfully simple to use for somewhat casual use cases: https://mariadb.com/docs/server/ha-and-performance/optimizat... and still reach for it in some personal projects, however the whole growing MySQL incompatibility is a big issue if the tech you use only officially supports MySQL and you can't (easily) get MariaDB specific DB drivers.
Personally, one of the best things about PostgreSQL is transactional DDL, every DB should support it. Also they handle JSON pretty nicely (though I'd prefer not to store data like that unless necessary) alongside excellent plugins like pgvector and PostGIS.
On the other hand, for things like queues, or even any sort of blob storage, I'd look at things like RabbitMQ or Garage (S3 compatible). Sometimes specialized software is nice for keeping things logically separated. I maintain that it's good to be able to divide your stack up by mechanisms/concerns (rather than business domain necessarily).
as someone who loves postgres, this take is getting pretty old. yes we can do quite a bit with extensions but extensions often need to interface with external systems and even then managed providers don't consistently support all extensions. some gaps: bm25 indexes, olap support, also extensions also run into licensing restrictions.
You can do BM25 and hybrid search in Postgres.
Shameless plug: https://github.com/jankovicsandras/plpgsql_bm25 BM25 search implemented in PL/pgSQL ( Unlicense / Public domain )
The repo includes also plpgsql_bm25rrf.sql : PL/pgSQL function for hybrid search ( plpgsql_bm25 + pgvector ) with Reciprocal Rank Fusion; and Jupyter notebook examples.
The related posts at the end are...interesting.
> Three Strike Dismissal in One-On-Ones
> Keith Rabois recommends to dismiss your report when you feel bad about an upcoming one on one more than three times in a row. Interesting. …
Its not a meme. Postgres does all of those things good enough to be always considered the first pick.
I love Postgres. Im using it for majority of my career in IT. It never failed me, even on very big scale.
Amazing piece of software.
For full text search you could also use pg_search (Tantivy) which looks very cool.
But at scale you probably don't want to manage a bunch of mission critical systems that were jacked into your database server. The database is slow? How do we monitor that?
So I would definitely begin like this, but you need to have a plan to break all of these out sooner or later.
Bona Fides: I learned c on with the K&R book on an Amiga (and transitioned to enterprise software engineering from there).
This seems like one more "When all you have is a hammer, everything looks like a nail" take. I agree with the other commenters who advocate for best-of-breed (e.g. Kafka, etc. for a message queue). PS I freakin love PostgreSQL as a relational (or even a time-series or OLAP) DB.
At Comper we have a very hot key-value store for annotating git data. We maintain a parallel git-blame data structure so we can do incremental "git blame -w -M -C -C". Typically a very expensive operation, but if you make it incremental, you can make it very cheap when new commits need to be analyzed. However, building the git blame tree is still pretty intensive for large repos.
We currently use rocksdb with storage on the same node, and hit rocksdb 1000s of times per second during our analysis. About 20% writes, 80% reads. The issue is that we need to start scaling horizontally, for burstable workers and zero-downtime deployment. So we're thinking to offload to an external kv service instead of a local rocksdb.
TiKV seems a good replacement, about 3-4x slower, but very scalable. Reading this article, I think a separate postgres cluster with unlogged tables might be a good idea. If anyone has some experience to share, let me know!
Unlogged tables still support MVCC and keep old versions of tuples around until they're garbage collected.
I love postgres and use it heavily, but I still don't fully understand how it overlook MySQL. Maybe because of Heroku adopting it.
MySQL was generally faster, and while MyISAM was a bit limited Innodb was pretty powerful, and you had the choice. It was also simpler (imo) and avoided a lot of the xid/vacuum issues.
That said, still love Postgres. But at the time it started eclipsing MySQL, MySQL felt better positioned.
Maria, MySQL, Oracle shenannigans, perhaps.
Also postgres is a "proper" db, so I'm glad it generally won out.
MySQL was a proven solution back in the day, i.e late 2000s. Github/Twitter/Heroku etc were using it. In the past 10-15 years, Postgres has come a long way.
MySQL was always behind in terms of features. In early 2000s it had very limited constraints. Most people were running in ISAM backend and did not even have transaction support. It was being used by people who did not understand how advanced relational DBs were being used. What changed is Postgres overtook its actual competition, which were Oracle, SQL Server and Sybase.
MySQL caught up as well as far as I know, but it still may have some poor defaults that are widely used.
In the early days, PostgreSQL was so much more awkward to deal with. Crash-prone at first, and then there was the whole business around having to drop the db during upgrades. It didn't really match MySQL operationally until around 2002.
During the dot com era it was common to develop and launch on MySQL with the intention of migrating to something else if they became successful (though your typical LAMP stack developer regarded Oracle and SQL Server as being deeply 'weird', so many were willing to stick with MySQL despite the well-known limitations of MyISAM).
From where I'm standing, it seems that PostgreSQL became clearly preferable for new projects from the mid 2000s onwards, but it was only the Oracle acquisition that began to push existing users off MySQL.
I would agree with that, although I personally found it preferable in 2001-2002, since it matched the feature set of SQL Server in all the important ways that MySQL didn't. I think it may have been late 2000s before it was clearly preferred for new projects and even later before it was the thing you had to explain why you weren't using it.
edit to add: MySQL did have operational advantages even later than mid 2000s since it had master/master replication from very early (I don't know how sound it was, I doubt it was perfect). That was a real reason to choose it. We still don't have it in Postgres without extensions and even there citus is not really the same.
Oh yeah, we were early adopters of MySQL's master/master replication at an online retailer. Certainly an "interesting" experience, and I remember becoming very well acquainted with the vagaries of the binlog!
(I remember it being heavily touted in the first edition of the O'Reilly "High Performance MySQL" book. The second edition was about twice the length, with most of the additional pagecount going into detailed explanations of why you should actually be very careful and do lots of testing before deciding to rely on master/master!)
Actually, I remember that one of the drivers away from MySQL before Oracle came along was the 4.x and 5.x period, when there were various performance regressions. And even when those were sorted out, the introduction of InnoDB made people realise that MySQL's apparent speed advantage was really just down to MyISAM lacking referential integrity and transactions.
Certainly, there were people clinging on to MySQL 3.23 for read-heavy data warehousing applications for a very long time.
> And even when those were sorted out, the introduction of InnoDB made people realise that MySQL's apparent speed advantage was really just down to MyISAM lacking referential integrity and transactions.
Yes we saw the same thing play out with MongoDB. Ack without fsync is indeed very fast.
In the times when everyone was installing Apache + PHP + "Some database" stack, the easy path was to use MySQL for a very simple reason: it had ready to use MS Windows installer.
Another thing: those were times when web applications were practically 99% reads, and not so great ACID was a non-issue.
Postgres is OK, but it has really a lot of quirks that are not that obvious.
MySQL had some problematic design decisions initially. They might be fixed now, but the impression remained. And later there was the added complication that they were bought by Oracle, so you didn't really know how this would turn out in the end.
PostgreSQL also had more features back then, e.g. the JSON support is very nice if you need to do anything that doesn't neatly fit into the relational model.
Not only where the problematic design decisions, but large numbers of people said for years "nobody will never need/want that anyway so stop talking about it". That is they didn't even attempt to talk about trade offs, you were just wrong if you suggested anything else.
Then people who knew something got involved (or likely were involved all along - but I never followed MySQL so I'm not sure) and fixed those because they matter and suddenly the crowd shut up.
Back in the day, the sentiment was the MySQL was more-performant but the criticized tradeoff of having "cut corners". I still remember when their transaction support InnoDB table engine came out. Anyway Postgres was viewed as slower but more standards compliant - so mature architects preferred that. MySQL, in my opinion, fell into default usage among LAMP stacks and PHP-using kiddies. Postgres took the crown over time.
I've not interacted with mysql a whole lot, but when i did I was regularly surprised that it didn't have stuff I was missing from Postgres. Off the top of my mind:
- Query planner is much worse (just yesterday I had to USE INDEX to sped up a query by 300x, I'm near-certain postgres would just have gotten it right) - Indexes are much more limited: no GIST, no GIN - No transactional lock (`pg_advisory_xact_lock` in postgres). This one was very surprising, it's a really useful thing and I had to implement it myself as a lock table
At least you have access to USE INDEX on MySQL. On Postgres it's not rare to have a query suddenly perform awful in production because some switch flipped in the planner and now it's picking some random index
Good news is that they just added a plan stability feature in pg19. It's actually full planner hints, so you can edit the plan to whatever you want if you have no fear, but the main motivation is exactly index stability.
I looked it up because I'm interested in the subject but it seems it's only a proposal right now? At least according to this 5 month old blog post https://rhaas.blogspot.com/2026/03/pgplanadvice-plan-stabili...
Edit: Never mind, found it https://www.postgresql.org/docs/19/pgplanadvice.html
Recent MySQL comes with a rewritten optimizer (which they call "hypergraph") that will soon become the default, but you can already use if you want to.
One application I'm working on (CRUD, but with fairly complicated business logic) is seeing large performance improvements, especially on reports which join many rows from 10+ tables.
I regularly test the same dataset on both PostgreSQL and MySQL -- we support both, and this new optimizer puts them pretty close.
The current default optimizer has some pathological cases where reports run 8-10 times slower on MySQL compared to PostgreSQL. Not with this one.
https://blogs.oracle.com/mysql/the-hypergraph-optimizer-is-n...
Maybe things have changed, but my memory of MySQL ~15 years ago was that it was so... hacky. Basically, the (non-strict) JavaScript of the RDBMS world.
I always thought Postgres was the one that did correctness first, then performance, while MySQL was the inverse. I also enjoyed Postgres documentation. But in practice I have very little experience with MySQL, but I do recall it liked to silently coerce invalid dates and its UTF-8 wasn't quite UTF-8.
And MySQL apparently still doesn't support transactional DDL (i.e. BEGIN, ALTER, ALTER, UPDATE, COMMIT), which is quite nice for db schema version migrations.
It's more "what one tool can do everything", not that its ideal. Like why people use Microsoft Teams even though its terrible.
The relational model and sql force us to simplify our data models too much by eliminating relationships or just not dealing with them.
Think about a nested json blob from some web service api and storing it in SQL in normalized tables. No one is going to do that. Everything just becomes a denormalized mess and everything is hacked around it.
Instead of modeling things in the proper way, most of the world's data is modeled in a way so that we don't have join explosions in sql queries because they look scary. Data pipelines become these scary batch transformations where data is dumped somewhere else without anyway to trace back where it came from.
I encounter so many end-user applications and systems where you wonder: "why couldn't they allow a list of items here instead of a single box" or "why can't this reference this other thing".
people do that with JSON way too often...
I think you’re responding to the general idea of a relational database, not Postgres, and definitely not what’s in the article (DR;CA).
Postgres has built in data types and functions that allows it to work with unstructured json documents, like you would use in MongoDB.
That feature is definitely part of why it's still so relevant. The hstore approach wasn't nearly enough when it was all PG offered.
"Work with" != "work well." GIN indices aren't the same as B+tree, and even then, you'll have to decide / know about jsonb_path_ops vs. the default operator class. Or you just accept sub-optimal performance, I suppose.
The lack of a rigid schema makes it super fun as well. Does this attribute exist in this row? Who knows! Maybe there's a long-forgotten version lurking, waiting to be retrieved, that will utterly bork the calling app.
Use case matters.
I use a SQL databases as needed. I've used Postgres, Sqlite, Duckdb, Json files with AWS Athena, Oracle enterprise for ERP systems (a multitude of schemas and objects with interoperability), and others.
I'm currently, deploying Duckdb with AWS S3 Tables (Iceberg) to see how it fits for a use case I have.
IT is great and always changing. Keep trying new things.
Cheers
My go-to has been replicas for each use case, with well defined semantics for replication lags. I'm working on something that does most of this seamlessly (transactional,search, columnar & time series, vectors and queues) without having to bother about extension management, replication setup or tuning.
Hopefully there is some value in this - one click multipurpose postgres fleet.
> Timescale lately released the pgvector extension, that turns your PostgreSQL into a vector database.
I don't think this is accurate and smells like an LLM hallucination to me.
From the Timescale/Tiger Data _pgvectorscale_ project's README:
> pgvectorscale builds on pgvector with higher performance embedding search and cost-efficient storage for AI applications.
I think this is where the confusion originates. I believe pgvector is primarily Andrew Kane (@ankane) and a cadre of OSS contributors.
As an aside, I've used Timescale/Tiger Data products and was very happy with them and their support. Their team was very engaged and responsive to all of our questions. They also fixed a pretty gnarly indexing bug I uncovered in pgvectorscale in an impressively short amount of time.
Thanks for the kind words.
And yes, Andrew Kane (et al) are the people to thank for pgvector.
We (Tiger Data) developed pgvectorscale and pg_textsearch (and timescaledb, and some others)
if you are searching for something similar but with more meat: https://ebellani.github.io/blog/2026/all-you-need-is-postgre...
Or an entire book covering it: https://www.manning.com/books/just-use-postgres
Or an entire site: https://postgresisenough.dev/
Postgres is great, but I certainly don't think it's great for everything. For instance, while you can in theory implement OLAP aggregation you're going to be hand-rolling a bunch of stuff that something like Clickhouse gives you for free declaratively.
I don't think the point is that PostgreSQL is great for everything. But you may get by with a single piece of infrastructure instead of 7.
In most of the applications we build or maintain we use PostgreSQL + cloud storage. That's it. And it works very well, also for: storing JSON, full text search, as a queue, as a vector database. Other software may be better at providing those features, but I'm extremely happy we only need to understand & manage PostgreSQL.
The article says verbatim “PostgreSQL Replaces Clickhouse”.
Coming from storing billions of rows in Clickhouse and performing dozens of materialized operations I shudder to think about what that would look like in a DB that doesn’t even support declarative IVM.
The article suggested using TimescaleDB which has its own concept of IVM: continuos aggregates. And compared to the approach by ClickHouse it can also update the materialized views when you update/delete old raw data
https://sqlfordevs.com/books+courses/timescale/05-continuous...
Yes but that is just one use case. Columnar OLAP engines operating on object storage can do all kinds of stuff so much better than Postgres that it may as well be a completely different capability. That said - the point is that you can get a lot further with just Postgres than many people think, and now we also have options like pg_lake. But I wish I'd changed analytics platforms A LOT sooner than I did.
I've been working on that in Postgres for the past...way too long now: https://commitfest.postgresql.org/patch/6305/
Still not totally declarative, but it at least gets IVM into core in a way people can make use of.
With Lakebase Postgres you can do this very easily: https://docs.databricks.com/aws/en/oltp/projects/quickstart-...
It is already a quite smooth experience, but there is work to make it even easier than that.
I work on Lakebase, opinions my own.
As a big fan of Postgres, Databricks and Lakebase: Lakebase is not Postgres, and this is just CDC.
Today CDC, tomorrow an authoritative part of storage.
I use Postgres for a lot of things where textbooks say not to, but not caching. I'm not going to do it with triggers. Maybe if it supported TTL properly, even then, probably don't want to think about whether caching will bog down the rest of the DB.
This is exactly how tightly coupled, unmaintainable software is constructed.
By picking the tools before understanding the model and building bespoke architecture.
You pick the tools that the business model requires. It might be a relational data store. It might not be. You might want an event store. You might want to reduce costs with lambdas and DynamoDB. You may need a pub/sub event broker.
The OP clearly loves Postgres. Cool. They also have limited experience with complex systems architectures because if they had that experience, they would have never written this article.
> This is exactly how tightly coupled, unmaintainable software is constructed.
No. If you're struggling to build software against a DB and then abstract parts to use Redis or ES or whatever in the future, that's kinda a skill issue you or your team have with building poor software to begin with. Nothing to do with using a DB for multiple things like a Queue/Search etc.
The technical solution isn’t the skill issue I’m pointing towards.
It’s the business modeling skill that most developers lack, so they skip it and believe an ERD will magically cover all invariants.
> You might want to reduce costs with lambdas and DynamoDB.
I don't think that's ever saved money.
> because if they had that experience, they would have never written this article.
That's not true.
> I don't think that's ever saved money.
I spent about a year as a consultant in the AWS space, visited about ~15 clients of varying sizes.
More often than not there's a single pg aurora instance responsible for 50%+ of the bill. Even worse are the serverless aurora offenders.
All the indexes and guarantees of PG don't come cheaply and dynamodb pricing is not cheap but comparatively reasonable. It really is a good product if you know how to use it.
> All the indexes and guarantees of PG don't come cheaply
I’m currently paying digital ocean about 30 dollars a month for 4 commercial websites with fully featured Postgres.
Why write a fanboy text with unfair comparisons that hide the Postgres limitations?
For instance, for many simple needs MySQL is simpler than Postgres, with similar performance and consistency.
* No need for a connection pool, while many use cases with Postgres require PgBouncer and Co.
* Easy sort (and basic search) of multilingual text, because MySQL has case insensitive UTF8 collations.
* No need to VACUUM, which can be a hard problem (it was, the last time I used Postgres).
For full text search, I once worked on a project that considered several alternatives for this, including Postgres. Manticore Search was finally chosen because it was more performant, with better search results.
If you run a single application, or a few instances of the same application, you don't need an external pool and most frameworks have an internal connection pool anyway.
Not sure if I'm missing anything here, but if I want case-insensitive search I simply create an index on lower(column) and use that to query.
VACUUM is something you need to pay attention to at scale. And at that point you need to know your DB anyway and tune it. For smaller applications (and I don't mean only toy applications) it usually isn't an issue.
>if I want case-insensitive search I simply create an index on lower(column) and use that to query
Or even pg_trgm trigram indexes, which are case-insensitive by default and support similarity search to accept typos and misspellings.
They're also massive.
> * No need for a connection pool, while many use cases with Postgres require PgBouncer and Co.
is there a strong evidence you even need client side connection pool at all? What is the purpose?
The limitation is that you have many clients with connection pools, they hold internal PG connection without allowing it to be reused by other clients..
> case-insensitive
Tbf you can also achieve this in Postgres, it's just not present by default. From the docs [0]: CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
0: https://www.postgresql.org/docs/current/collation.html
> Contrary to popular belief - the answer to everything is NOT 42
42 is not the answer to everything.
42 is the Answer to the Ultimate Question about Life, the Universe, and Everything.
Ive built data warehouses and job queues on postgres. The DW got replaced with bigquery when we started doing more tracking. We still run postgres as an app-facing cache of the aggregated data from bigquery though.
The job queue runs on the cache db, scheduling jobs to move data from bigquery into postgres. It’s pretty neat.
Now we’ve run into near-real-time requirements so clickhouse is getting thrown into the mix.
It’s pretty funny the lengths we go to to implement user facing analytics that’s basically just “you are visitor number X” from 1995.
Funny. I was just this joking this morning with a colleague about using Postgres for everything.
Considering adding mongo for unstructured data? Just use postgres jsonb.
Building a search index? Postgres is fine too.
Considering using redis for fragment caching? Just use an unlogged table in postgres with key value columns. Need pub/sub? Well just use postgres listen/notify.
Using postgres for everything has served me very well.
As usual, it depends on the scale, but it's a sane default for 99% of use cases.
Different use cases have different scalability limits in PG, when you get to them you need to deal with them.
It would be perfect if it had somewhat transparent sharding, I mean a way to add another instance and distribute load without having to stop everything.
There are solutions, but they tend to be involved and when you get to that point in many cases it makes sense to just move that workload to something else that scales better.
I like Postgres. It is a good general purpose database. I like other databases too. Other databases can do some things that Postgres can't do as well.
For "replacing your microservice" you should checkout PostgREST. It basically turns PostgreSQL into a microservice.
I tried PostgREST and regretted it. I ran into many situations where I wanted a thicker backend between my webapp and db.
- mind coming and enligtening about PostgreSQL and XML?
- https://www.reddit.com/r/PostgreSQL/comments/1vbo5j8/raw_xml...
- your post did not have a single word on XML hence my comment
There's a missing essay: Postgres for nothing. A tiny store on SQLite/D1 with no admin UI is boring and it stays up. The day I need JSONB I'll know.
One addition: Postgres for all your GPU-based machine learning training and inference needs: https://github.com/postgresml/postgresml
Nice one re: flatbuffers in `blob` column. Have been working with flatbuffers a lot and that's a neat idea in general.
Not 100% sure about using PG for file system at scale however. I'd love to hear more on the challenges (vacuum, toast, anything else?)
sqlite for everything
NVMe drives + Litestream + object storage(S3/R2..). sqlite simplifies things for the entire long tail of apps/services that aren't the Ubers and AirBNBs of the world.
It looks interesting, but deciding on where to put object storage is what keeps me from doing this. I don’t have an AWS or Cloudflare account and I’m not sure what to commit to.
Also, apparently Litestream could use a filesystem instead of an object store?
Surprised it doesn't mention LISTEN / NOTIFY.
yes. my people.
https://github.com/seanwevans/pg_gpt2
https://github.com/seanwevans/pg_shell
https://github.com/seanwevans/pg_os
Hey Raphael Bauer, if you're reading this, I suggest you change the color of hrefs on your website, all of them are purple and underlined, which usually is indicator for "Already visited URL". For me that's not that much of a problem, but it was something i kept noticing when reading the article. I wonder if anyone else also had this thought or am I alone as I didn't see anyone else mention this in the comments. But I wanted to signal that nevertheless :)
Honorable mention - https://postgrest.org/
I find it odd that this isn't mentioned anywhere in an article about using postgres for everything.
to risk sounding like a madman - if you're a solo individual- serving b2b small businesses.
then Sqlite works as well too. can run the whole thing on Cloudflare.
running Postgres isn't difficult. but dealing with a VPS for low traffic is a headache that's not necessary.
SQLite has so many advantages over PostgreSQL.
No deamon. Single file per DB. Less configuration overhead.
It's probably perfect for a majority of work (and DuckDB takes that even further).
But for big, multi-writer work PG is the way to go.
Postgres also has many advantages over SQLite.
Supporting more than 1 writer per process. Strict typing. Access controls. Replication at scale is more effecient than copy-pasting files (seems SQLite has improved on this one).
"MySQL was also potentially faster as it did not implement all features of the SQL standard. "
This is a not great start. I assume it refers to MyISAM which has not been relevant for over a decade at this point. InnoDB made different design than PG decisions and was (and perhaps still is) faster at point lookups.
Well, the poster explicitly talks about 2003 here: „ In 2003, MySQL was much more widely used than PostgreSQL. MySQL was also potentially faster as it did not implement all features of the SQL standard“
At the time, MySQL was also the default for every PHP backed webhost provider. That's the market they lost. They're the Perl of DBs.
Didn't very early mysql play fast and loose with the concept of actually syncing to disk? That was also fast. Web scale fast. :)
Can Partitioning be used to move data to S3 Storage, for long term archiving?
No, and with timescaledb is a feature of their cloud platform. For on prem you can use something like Arc: https://github.com/Basekick-Labs/arc
Thank you.
I have decided to use clickhouse with that config because of missing S3 for logs and metrics for long term store.
In theory yes, just need s3 fdw
I demonstrated this with ClickHouse: https://github.com/ClickHouse/pg_clickhouse/blob/main/doc/of...
We're working on a chdb based mechanism to copy to/from s3, maybe with fdw on top we can back table in s3
You can try similar things with pg_duckdb & pg_lake
How many production system has serious queues using PostgreSQL?
true to that - currently using psql (in a single monolithic codebase) as: sql db, json db, vector store, logs store, full-text search, queue, message bus.
multiple processes connected to it.
there is also https://postgresforeverything.com/
The older I get the more I agree with this
How do you implement HA in postgres, i found MySQL HA stack pretty straight forward with Innodb cluster, MySQL router and Shell.
https://patroni.readthedocs.io/
But I sure wish it was 'core' and we didn't have to worry about it potentially going away, becoming de-supported..
Yes, especially something that touches DB. Router and shell are stupidly simple and you get auto failover in some 30 minutes. That is something make me stick to mysql.
I'm waiting for the followup contrarian shitpost: "Firebird for Everything".
isn't the process per connection restriction pretty heavyweight though?
Hardware is so far nowadays to make people with little systems knowledge confident to make such claims at least from their use cases. However it is neither generally reasonable nor efficient.
Years ago I used PostgreSQL under Django to drive an industrial test and inspection robotic cell (which I also designed and built) at a major technology company. It worked very well. PostgreSQL maintained machine state, path planning, sensor readings, faults, operator input, etc.
I wanted to see how far I could push that toolset. It worked surprisingly well. Django's capabilities meant such things as multi-user login pages, access controls and remote monitoring were very easy.
In my experience, it still kinda sucks if you want to store blobs. Anything on this front?
Except horizontal scaling.
But a lot of companies are trying to solve that, notably multigres, neki and even pgdog.
the more ive coded the more this is true
For the graph database idea in Postgres, PG 19 has native support for property graphs [0]. You can set up your tables and their relationships as nodes/edges, then query against them using Cypher-esque [1] syntax.
0: https://www.postgresql.org/docs/19/ddl-property-graphs.html
1: https://www.postgresql.org/docs/19/queries-graph.html
I really want a daemonless PostgreSQL, in the style of SQLite.
So https://pglite.dev/?
i love postgresql but once we added ai-generated dashboard to our homegrown analytics tool [0] some of the crazy (amazing) dashboards that the ops team was building began accumulating horrendously slow db queries. I considered dynamically adding indexes or alerting around postgres slow queries but also quickly prototyped mirroring the postgres data in clickhouse. At first could not believe how fast clickhouse was on arbitrary analytics queries - like 60s to 0.5s for some gnarly queries. truly amazing software that just works without any tuning for this kind of exploratory analytics workload.
so yes, i'm still a postgres maximalist (worker queues still in pg [1]) but (especially in the age of quick LLM prototypes) it's always worth measuring the more purpose-built approach.
[0]: https://setoku.com
[1]: https://worker.graphile.org
> My tip: Start with PostgreSQL as a queueing system. Only when that does no longer perform well switch to other systems like Kafka, RabbitMQ or SQS.
My tip: store your company's source code on a samba file server. Only when that no longer performs well, switch to other systems like Git.
Hehe, not too popular an idea is it? Maybe there's some characteristic about a version control system that makes it qualitatively different from a file store. Maybe it has nothing to do with size or number of customers!