Just use Postgres

&gt; If you see a college student or fresh grad using MongoDB stop them. They need help. They have been led astray.<p>I like this sentence way more than I should.

Almost all statements about MongoDB are wrong.<p>&gt; You know exactly what your app needs to do, up-front<p>No one does. Mongodb still perfectly fits.<p>&gt; You know exactly what your access patterns will be, up-front<p>This one also no one knows when they start. We successfully scaled MongoDB from a few users a day to millions of queries an hour.<p>&gt; You have a known need to scale to really large sizes of data<p>This is exactly a great point. When data size goes to a billion rows, Postgres is tough. MongoDB just works without issue.<p>&gt; You are okay giving up some level of consistency<p>This is said for ages about MongoDB. Today, it provides very good consistency.<p>&gt; This is because this sort of database is basically a giant distributed hash map.<p>Putting MongoDB in category of Dynamo is a big mistake. It&#x27;s NOT a giant distributed hash map.<p>&gt; Arbitrary questions like &quot;How many users signed up in the last month&quot; can be trivially answered by writing a SQL query, perhaps on a read-replica if you are worried about running an expensive query on the same machine that is dealing with customer traffic. It&#x27;s just outside the scope of this kind of database. You need to be ETL-ing your data out to handle it.<p>This shows the author has no idea how MongoDB aggregation works.<p>I don&#x27;t want fresh grads to use SQL just because they learn relations (and consistency and constraints and what not). It&#x27;s perfectly fine to start on MongoDB and make it the primary DB.

The &quot;SQLite is just a file&quot; thing is actually an advantage. The example of a website is actually a pretty poor one, since any website that needs to scale beyond a single box has many options. The two easiest ones are:<p>- Mix static and dynamic content generation (and let&#x27;s face it, most websites are mostly static from a server perspective)<p>- Designate a writer node and use any of the multiple SQLite replication features<p>But, in short, if you use an ORM that supports both SQLite and Postgres you&#x27;ll have the option to upgrade if your site brings in enough traffic. Which might never happen, and in that case you have a trivial backup strategy and no need to maintain, secure and tweak a database server.

It&#x27;s not worth pointing out the technical flaws in the post[1]. It is obvious the author does not have a strong grasp of the tools he is criticising. A better example of this style of post is Oxide&#x27;s evaluation[2] for control plane storage that actually goes over their specific needs and context.<p>[1] Ok, just one, <i>Rick</i> Houlihan is currently at MongoDB.<p>[2] <a href="https:&#x2F;&#x2F;rfd.shared.oxide.computer&#x2F;rfd&#x2F;53" rel="nofollow">https:&#x2F;&#x2F;rfd.shared.oxide.computer&#x2F;rfd&#x2F;53</a>

On the MySQL vs Postgres topic: We migrated for two reasons.<p>The first is that I consider everything remotely owned by Oracle as a business risk. Personal opinion and maybe too harsh, but Oracle licenses are made to be violated accidentially so you can be sued and put on the license hook once you&#x27;re audited, try as you might.<p>But besides that, Postgres gives you more tools to keep your data consistent and the extension world can save a lot of dev-time with very good solutions.<p>For example, we&#x27;re often exporting tenants at an SQL level and import somewhere else. This can turn out very weird if those are 12 year old on-prem tenants. MySQL in such a case has you turn of all foreign key validations and whatever happens happens. A lot of fun with every future DB migration is what happens. With Postgres, you just turn on deferred foreign key validation. That way it imports the dump, eventually complains and throws it all away. No migration issues in the future.<p>Or the overall tooling ecosystem around PostgreSQL just feels more mature and complete to me at least. HA (Patroni and such), Backups (pgbackrest, ...), pg_crypto, pg_partman and so on just offer a lot of very mature solutions to common operational and dev-issues.

If Postgres already had decent temporal table support (per SQL:2011 system time + application time &quot;bitemporal&quot; versioning) we never would have gone down the road of building XTDB. From the perspective of anyone building applications on top of SQL with complex reporting requirements in heavily regulated sectors (FS, Insurance, Healthcare etc.), &quot;just use temporal tables&quot; would be the ideal default choice. To get an idea of why, see <a href="https:&#x2F;&#x2F;docs.xtdb.com&#x2F;tutorials&#x2F;financial-usecase&#x2F;time-in-finance.html" rel="nofollow">https:&#x2F;&#x2F;docs.xtdb.com&#x2F;tutorials&#x2F;financial-usecase&#x2F;time-in-fi...</a>

When people say &quot;Just use SQLite. It&#x27;s almost as good as Postgres and you won&#x27;t need anything more&quot; I&#x27;m trying to understand why I shouldn&#x27;t just use Postgres. It&#x27;s not like it&#x27;s hard to install or has any significant overhead. Please enlighten me.

Really just reads as an article reaffirming his own bias. For Mongo at least most of it is wrong.<p>- Secondaries are read replicas and you can specify if you want to read from them using the drivers selecting that you are ok with eventual consistency.<p>- You can shard to get a distributed system but for small apps you will probably never have to. Sharing can also be geo specific so you query for french data on the french shards etc lowering latency while keeping a global unified system.<p>- JSON schema can be used to enforce integrity on collections.<p>- You can join but this I definitely don’t recommend if possible.<p>- I personally like the pipeline concept for queries and wish there was something like this for relational databases to make writing queries easier.<p>- The AI query generator based on the data using Atlas has reduced the pain of writing good pipelines. Chat gpt helps a lot here too.<p>- The change streams are awesome and has let us create a unified trigger system that works outside of the database and it’s easy to use.<p>We run postgres as well for some parts of the system and it also is great. Just pick the tool that makes the most sense for your usecase.

Okay, I am very sorry that I got Rick Houlihan&#x27;s name wrong.<p>In my defense, I hadn&#x27;t watched his talks _recently_ and we&#x27;ve all been Berenstain Bear&#x27;ed a few times.<p>But also the comparison of DynamoDB&#x2F;Cassandra to MongoDB comes directly from his talks. He currently works at MongoDB. I understand MongoDB has more of a flowery API with some more &quot;powerful&quot; operators. It is still a database where you store denormalized information and therefore is inflexible to changes in access patterns.

Use what you know, ship useful stuff.

MySQL is like Javascript: Full of bad decisions and footguns. It works perfectly fine, but I don’t see why you’d use it when Postgres exists.

1. With the recent developments at CockroachDB one may like to bundle it along with MSSQL and Oracle.<p>2. Like the author, I will like to understand &quot;Why not MariaDB? (a free variant of MySql)&quot;.

Their reasoning is that some platforms like Heroku do not support SQLite.<p>Why use those then and not a platform that supports it, like Glitch?<p>I have used Postgres, MySql etc, but having the project storage in a single file is making things so much easier, I would never ever want to lose that again.

For MySQL, for smaller deployments, I&#x27;ve found Galera to really be a handy HA system to get going:<p>&gt; <i>Galera Cluster is a synchronous multi-master database cluster, based on synchronous replication and MySQL and InnoDB. When Galera Cluster is in use, database reads and writes can be directed to any node. Any individual node can be lost without interruption in operations and without using complex failover procedures.</i><p>* <a href="https:&#x2F;&#x2F;galeracluster.com&#x2F;library&#x2F;documentation&#x2F;overview.html" rel="nofollow">https:&#x2F;&#x2F;galeracluster.com&#x2F;library&#x2F;documentation&#x2F;overview.htm...</a><p>* <a href="https:&#x2F;&#x2F;packages.debian.org&#x2F;search?keywords=galera" rel="nofollow">https:&#x2F;&#x2F;packages.debian.org&#x2F;search?keywords=galera</a><p>The closest out-of-box solution that I know of for Postgres is the proprietary BDR:<p>* <a href="https:&#x2F;&#x2F;www.enterprisedb.com&#x2F;docs&#x2F;pgd&#x2F;4&#x2F;bdr&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.enterprisedb.com&#x2F;docs&#x2F;pgd&#x2F;4&#x2F;bdr&#x2F;</a><p>* <a href="https:&#x2F;&#x2F;wiki.postgresql.org&#x2F;wiki&#x2F;BDR_Project" rel="nofollow">https:&#x2F;&#x2F;wiki.postgresql.org&#x2F;wiki&#x2F;BDR_Project</a><p>There are systems like Bucardo, but they are trigger-based and external to the Postgres software:<p>* <a href="https:&#x2F;&#x2F;www.percona.com&#x2F;blog&#x2F;multi-master-replication-solutions-for-postgresql&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.percona.com&#x2F;blog&#x2F;multi-master-replication-soluti...</a><p>Having a built-in 3-node MMR (or 2N+1arb[0]) solution would solve a bunch of &#x27;simple&#x27; HA situations.<p>[0] <a href="https:&#x2F;&#x2F;packages.debian.org&#x2F;search?keywords=galera-arbitrator" rel="nofollow">https:&#x2F;&#x2F;packages.debian.org&#x2F;search?keywords=galera-arbitrato...</a>

Postgres will do to other databases, what Linux did to other Unix(&#x2F;BSD-like) operating systems (IRIX, SunOs, ...).

I&#x27;d like to mention that CouchDB is really useful for one reason - a very robust sync story with clients, and a javascript version called PouchDB that can run on the browser and do bidirectional sync with remote Couch instances.<p>This can be done with sqlite by jumping through a few extra hoops, and now with in-browser WASM postgres, there as well with a few more hoops, but the Couch -&gt; Pouch story is easy and robust.

There&#x27;s a lot of _very_ arguably false statements in here, esp around mongo and dynamo.<p>Postgres still has to &quot;rewrite&quot; data if you need another index. In fact it&#x27;s about the same amount if you had to add an index for dynamodb...<p>Also, when&#x27;s the last time you changed your primary key in a postgres table? Or are you just adding indexes?

&gt; It&#x27;s annoying because, especially with MongoDB, people come into it having been sold on it being a more &quot;flexible&quot; database. Yes, you don&#x27;t need to give it a schema. Yes, you can just dump untyped JSON into collections. No, this is not a flexible kind of database. It is an efficient one.<p>I really like this sentence because it perfectly encapsulates a mistake that, I think, people do when considering using MongoDB.<p>They believe that the schemaless nature of NoSQL database is an advantage because you don&#x27;t need to do migrations when adding features (adding columns, splitting them, ...). But that&#x27;s not why NoSQL database should be used. They are used when you are at a scale when the constraints of a schema become too costly and you want your database to be more efficient.

These posts are always so biased to the person that&#x27;s used postgres 100x more than any other DB.

Every database has issues and quirks whether they be about how you design your application, how you need to scale, or how you need to maintain your database. You can play this game “just use XYZ and have no problems”, but it isn’t realistic. Production databases at scale require heavy dedicated infra to stay highly available and performant, and even out of the box solutions require you to understand what is going on and tune them else you run into “surprises” which are almost always that no one RTFM. Pretty much every mainstream database is capable of both highly available and highly consistent workloads at scale. The storage engine largely shouldn’t matter as much as the application tuning.

I do not get the reasoning around SQLite.<p>SQlite is easy to backup, especially if you are OK with write locking for long enough to copy a file. It now has a backup API too of you are not OK with that.<p>Lots of things do not scale enough to need more than one application server. A lot of the time, even though I mostly use Postgres, the DB and the application are on the same server, which gets rid of the difficulties of working over a network (more configuration, more security issues, more maintenance).<p>The main reasons I do not use SQLite are its far more limited data types and and its lack of support for things like ALTER COLUMN (other comments have covered these individually).

It has never made sense to me why someone uses no, and then proceed to little by little to make their implementation into having relations.<p>It’s way less work just to learn sql or an orm.<p>Nosql is great at being a document store.<p>I’ve used MySQL longer, it’s been a good default option, the jump to how Postgres works and what it offers is too much to ignore.<p>Postgres can act as a queue, many of the functions that a nosql has, handle being ann embedding db, and do so until a decent volume. It can be the backbone of many low code tools like supabase, hasura, etc. the only thing that’s different is there seems to be nice currents for MySQL but you get the hang of it pretty quick.

Does anyone know why seemingly all the introductory courses advocated nosql stuff like using mongoDB<p>Even freecodecamp who is excellent, does this.<p>They have a rel-db course <a href="https:&#x2F;&#x2F;www.freecodecamp.org&#x2F;learn&#x2F;relational-database&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.freecodecamp.org&#x2F;learn&#x2F;relational-database&#x2F;</a> but their backend course uses mongodb <a href="https:&#x2F;&#x2F;www.freecodecamp.org&#x2F;learn&#x2F;back-end-development-and-apis&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.freecodecamp.org&#x2F;learn&#x2F;back-end-development-and-...</a>

If you&#x27;re just starting a startup, go with Postgres for most things. With limited devops resources it&#x27;ll be good enough in most scenarios.<p>You can always over optimise later on.

I&#x27;ve found out key&#x2F;value databases pushes for better architectural designs in enterprise environments. Especially in companies where different teams are responsible for a given business capability and it needs to scale above 1+ million users.<p>Postgres flexibility enables for design that is hard to scale. Both in terms of maintainability and performance. Enforcing K&#x2F;V as a default database in one of my previous companies worked wonders.

From the article, DynamoDB-likes are good IF:<p>* You know exactly what your app needs to do, up-front<p>But isn&#x27;t this true of any database? Generally, adding a new index to a 50 million row table is a pain in most RDBs. As is adding a column, or in some cases, even deleting an index. These operations usually incur downtime, or some tricky table duplication with migration process that is rather compute + I&#x2F;O intensive... and risky.

&gt; Why not MySQL? MySQL is owned by Oracle.<p>Ok, so what about MariaDB?

Totally agree - I have tried many databases of all flavors, but I always come back to Postgres.<p>HOWEVER - this blog post is missing a critical point.... the quote should be:<p>---&gt; Just use Postgres<p>AND<p>---&gt; Just use SQL<p>&quot;Program the machine&quot; stop using abstractions, ORMs, libraries and layers.<p>Learn how to write SQL - or at least learn how to debug the very good SQL that ChatGPT writes.<p>Please, use all the very powerful features of Postgres - Full-Text Search, Hstore, Common Table Expressions (CTEs) with Recursive Queries, Window Functions, Foreign Data Wrappers (FDW), put JSON in, get JSON out, Array Data Type, Exclusion Constraints, Range Types, Partial Indexes, Materialized Views, Unlogged Tables, Generated Columns, Event Triggers, Parallel Queries, Query Rewriting with RULES, Logical Replication, PartialIndexes, Policy-Based Row-Level Security (RLS), Publication&#x2F;Subscription for Logical Replication.<p>Push all your business logic into big long stored procedures&#x2F;functions - don&#x27;t be pulling the data back and munging it in some other language - make the database do the work!<p>All this stuff you get from <i>programming the machine</i>. Stop using that ORM&#x2F;lib and write SQL.<p>EDIT:<p>People replying saying &quot;only use generic SQL so you cans switch databases!&quot; - to that I say - rubbish!<p>I nearly wrote a final sentence in the above saying &quot;forget that old wives tale about the dangers of using a databases functionality because you&#x27;ll need to switch databases in the future and then you&#x27;ll be stuck!&quot;<p>Because the reason people switch databases is when they switch to Postgres after finding some other thing didn&#x27;t get the job done.<p>The old &quot;tut tut, don&#x27;t use the true power of a database because you&#x27;ll need to switch to Oracle&#x2F;MySQL&#x2F;SQL server&#x2F;MongoDB&quot; - that just doesn&#x27;t hold.

For everyone saying, “Just use SQLite”, how do you deal with pathological queries causing a denial of service? SQLite is synchronous, so you end up blocking your entire application when a query takes a long time. It’s a problem in Postgres, too, especially if the query involves table locks, but your app can Postgres can generally hobble along.

&gt; AI is a bubble<p>why does it even matter? I know that I need multimodal search in my product, and that is why I need vector DB. You&#x27;re not saying anything interesting by saying &quot;AI is a bubble&quot;. If you say something like I may not actually need RAG&#x2F;mutimodal&#x2F;semantic search&#x2F;dedicated vector db then you may have my attention.

Great post - the comparison to specific tech was really useful.<p>Just added it to my &quot;Postgres Is Enough&quot; gist: <a href="https:&#x2F;&#x2F;gist.github.com&#x2F;cpursley&#x2F;c8fb81fe8a7e5df038158bdfe0f06dbb#postgresql-is-enough" rel="nofollow">https:&#x2F;&#x2F;gist.github.com&#x2F;cpursley&#x2F;c8fb81fe8a7e5df038158bdfe0f...</a>

Not everyone builds the same kind of application and has the same amount of data with the same kind of interactions.

I wish postgres had a library only mode that directly stored to a file like sqlite. That&#x27;d make starting development a lot easier since you don&#x27;t have to jump through the hoops of setting up a postgres server. You could then switch to a &quot;proper&quot; DB when your application grows.

&gt; You can only have so much RAM. You can have a lot more than you&#x27;d think, but its still pretty limited compared to hard drives.<p>Your data fits in ram[0]. [0]: <a href="https:&#x2F;&#x2F;yourdatafitsinram.net" rel="nofollow">https:&#x2F;&#x2F;yourdatafitsinram.net</a>

Horizontal scale of writes.<p>Citus would be alright if the HA story was better: <a href="https:&#x2F;&#x2F;github.com&#x2F;citusdata&#x2F;citus&#x2F;issues&#x2F;7602">https:&#x2F;&#x2F;github.com&#x2F;citusdata&#x2F;citus&#x2F;issues&#x2F;7602</a>

Personally, most of the projects i do are in self-hosted servers. The traffic isnt big. In such cases sqlite has been way better than postgres. Many times i see postgres not well used. Its meant for big project, not small ones.

My own advice would be start with SQLite and do a trivial migration to Postgres if warranted.

I don&#x27;t hate SQL and I agree for many applications it makes sense, but I disagree 100% with &quot;default to a SQL database&quot; (like Postgres). Instead, figure out what you need based on your app.<p>Recently I had the opportunity to rewrite an application from scratch in a new language. This was a career first for me and I won&#x27;t go into the why aspect. Anyway, the v1 of the app used SQL and v2 was written against MongoDb. I planned the data access patterns based on knowledge that my DB was effectively document&#x2F;key&#x2F;value. The end result: it is much simpler. The v1 DB had like 100+ tables with lots of relations and needs lots of documentation. The v2 DB has like 10 &quot;tables&quot; (or whatever mongo calls them) yet does the same thing. Granted, I could have made 10 equivalent SQL tables as well but this would have defeated the purpose of using SQL in the first place. This isn&#x27;t to say MongoDB is &quot;better&quot;. If I had tons of fancy queries and relations I needed it would be easier with SQL, but for this particular app, it is a MUCH better choice.<p>TL;DR Don&#x27;t default to anything, look at your requirements and make an intelligent choice.

And often no databases manager is the best solution.<p>Literally<p>If you are not storing much data no datase manager is th best

I&#x27;m always cautious with a one-size-fits-all approach. If a team is working on a small project and SQLite works then great. You can use a SQLite database on something like a $4&#x2F;month DigitalOcean droplet. Can&#x27;t say the same for Postgres.<p>&gt; AI is a bubble<p>Many say this but Generative AI and LLMs have gotten bunched up with everything else. There is a clear need for vectors and multimodal search. There is no core SQL statement to find concepts within an image for example. Machine learning models support that with arrays of numbers (i.e. vectors). pgvector adds vector storage and similarity search for Postgres. There was a recent post about storing vectors in SQLite (<a href="https:&#x2F;&#x2F;github.com&#x2F;asg017&#x2F;sqlite-vec">https:&#x2F;&#x2F;github.com&#x2F;asg017&#x2F;sqlite-vec</a>).<p>&gt; Even if your business is another AI grift, you probably only need to import openai.<p>There&#x27;s much more than this. There are frameworks such as LangChain, LlamaIndex and txtai (disclaimer I&#x27;m the primary author of <a href="https:&#x2F;&#x2F;github.com&#x2F;neuml&#x2F;txtai">https:&#x2F;&#x2F;github.com&#x2F;neuml&#x2F;txtai</a>) that handle generating embeddings locally or with APIs and storing them in databases such as Postgres.

Ah yes, the I don&#x27;t have any other use case therefor all others are not good

Note... you can use PostgreSQL as MongoDB... with FerretDB :)

Yup. With nothing but love for sqlite.

Here we go again... Just use X, forever, in all cases, is misguided whatever X is - a database, programming language, ... a vehicle.<p>PostgreSQL is good for many things and default to PostgreSQL and use something else if clearly justified is a sound advice, but assuming there is no room for anything else but PostgreSQL is not.

Just use SQLite3. You will with 99.99% chance never need more. Now what?

Missing sqlite comparison point: data types. SQLite is like JS with column datatypes, except even looser.<p>The claim about Datomic only working with JVM languages isn&#x27;t right, it has a rest api there are eg python and js client libs using that.

This should be titled &quot;Just use Sqlite&quot;, you really rarely need anything more unless you&#x27;re Google or Facebook.

There is absolutely no reason you can&#x27;t make SQLite go all the way. Starting with it is the only thing that makes sense to me.<p>It is certainly a higher performance solution in the fair comparison of a hermetically sealed VM using SQLite vs application server + Postgres instance + Ethernet cable. We&#x27;re talking 3-4 orders of magnitude difference in latency. It&#x27;s not even a contest.<p>There are also a lot of resilience strategies for SQLite that work so much better. For instance, you can just snapshot your VM in AWS every x minutes. This doesn&#x27;t work for some businesses, but you can also use one of the log replication libraries (perhaps in combination with snapshots). If snapshots work for your business, it&#x27;s the most trivial thing imaginable to configure and use. Hosted SQL solutions will never come close to this level of simplicity.<p>I personally got 4 banks to agree to the snapshot model with SQLite for a frontline application. Losing 15 minutes of state was not a big deal given that we&#x27;ve still not had any outages related to SQLite in the 8+ years we&#x27;ve been using it in prod.