10 ms·

Just use Postgres

236 points by refset 2y ago

KingOfCoders 2y ago
Postgres will do to other databases, what Linux did to other Unix(/BSD-like) operating systems (IRIX, SunOs, ...).
- worik 2y ago
  Been waiting a rew decades now...
- Gud 2y ago
  Linux didn’t “do” anything to *BSD. Linux emerged at the same time there was a nasty lawsuit against BSD.
  - KingOfCoders 2y ago
    SunOS is/was an BSD based operating system, and Linux replaced sun servers that were running SunOS with Intel servers in the same way as Linux based servers replaced most server operating systems in data centers (and today cloud providers), like Ultrix, IRIX, HP-UX etc. I've meant "do" in this way.
bob1029 2y ago
There is absolutely no reason you can't make SQLite go all the way. Starting with it is the only thing that makes sense to me. 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're talking 3-4 orders of magnitude difference in latency. It's not even a contest. 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'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's the most trivial thing imaginable to configure and use. Hosted SQL solutions will never come close to this level of simplicity. 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've still not had any outages related to SQLite in the 8+ years we've been using it in prod.
- Kinrany 2y ago
  With Pglite it should now be possible to have the cake of Postgres' rich functionality and embed it too
  - jddj 2y ago
    *in nodejs (for now)
    - Kinrany 2y ago
      Famous last words, but it's already in a Wasm module and only needs a bit of glue code, so how hard could it be? :D
      - samwillis 2y ago
        Yep, we are actively working on native support. (I work on PGlite)
- codeflo 2y ago
  Why would you run Postgres on a different box in such a scenario? A docker run command to get a Postgres instance up and running isn’t any more complicated than linking in Sqlite, maybe even simpler. And you get proper ACID transactions for free.
  - rcarmo 2y ago
    1) Fault isolation in case of hardware failure 2) You may not even be able to _think_ about using Docker (OS restrictions, airgaps to install images, etc.) These are just 2 that come to mind from working in regulated industries.
    - codeflo 2y ago
      In this thread, we're making a comparison to running Sqlite. Fault isolation is not an argument. And you don't have to run Docker to run Postgres, it's just an easy way to do so. Even airgapped.
frithsun 2y ago
Yup. With nothing but love for sqlite.
rcarmo 2y ago
The "SQLite is just a file" 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: - Mix static and dynamic content generation (and let's face it, most websites are mostly static from a server perspective) - Designate a writer node and use any of the multiple SQLite replication features But, in short, if you use an ORM that supports both SQLite and Postgres you'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.
- sampullman 2y ago
  Even without an ORM that supports both, as long as the DB layer is reasonably separated in your application it shouldn't be too much effort to switch. And if you've scaled to the point where it matters, you probably have the resources to do so.
  - CraigJPerry 2y ago
    >> as long as the DB layer is reasonably separated in your application I find this is easy in retrospect but tricky when you’re building a system. It’s all shades of grey when you’re building: Should I put my queue in my DB and just avoid the whole 2PC drama (saga is a more apt word but too much opportunity for confusion in this context). I probably should implement that check constraint or that trigger but should I add a plugin to my DB to offer better performance and correctness of special type X or just use a trigger for that too? Should I create my own db plugin so that triggers can publish messages themselves without going through an app layer? In retrospect it’s easy to see when you went too far, or not far enough. At decision time the design document your team are refining starts to head past the ~10 page sweet spot limit.
    - sampullman 2y ago
      That's true, even with "perfect" abstractions, switching gets more complicated as you use more complex database features. It's only really easy if you push most of your constraints and triggers to the application. In practice, I've only ever switched databases with really simple CRUD stuff and have otherwise been able to predict that I'll eventually want Postgres/RabbitMQ/etc and build it in from the start.
andrewstuart 2y ago
Totally agree - I have tried many databases of all flavors, but I always come back to Postgres. HOWEVER - this blog post is missing a critical point.... the quote should be: ---> Just use Postgres AND ---> Just use SQL "Program the machine" stop using abstractions, ORMs, libraries and layers. Learn how to write SQL - or at least learn how to debug the very good SQL that ChatGPT writes. 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/Subscription for Logical Replication. Push all your business logic into big long stored procedures/functions - don't be pulling the data back and munging it in some other language - make the database do the work! All this stuff you get from programming the machine. Stop using that ORM/lib and write SQL. EDIT: People replying saying "only use generic SQL so you cans switch databases!" - to that I say - rubbish! I nearly wrote a final sentence in the above saying "forget that old wives tale about the dangers of using a databases functionality because you'll need to switch databases in the future and then you'll be stuck!" Because the reason people switch databases is when they switch to Postgres after finding some other thing didn't get the job done. The old "tut tut, don't use the true power of a database because you'll need to switch to Oracle/MySQL/SQL server/MongoDB" - that just doesn't hold.
- rcarmo 2y ago
  your answer was fine and good until you classified ChatGPT's SQL generation as "very good" -- which it is not. I've had _all_ GPT models spit out monstrosities and slow queries of all kinds. ORMs are not all bad. In fact, some ORMs generate better code for really complex joins (think hundreds of tables, each with hundreds of columns) than humans, and often ensure that trivial best practices (like indexes and consistent foreign keys) are followed. Writing SQL is a great skill, but if you tie yourself to a single database engine's idioms then you're in for a shock when you switch platforms/jobs/environments.
  - deleted 2y ago
fulafel 2y ago
Missing sqlite comparison point: data types. SQLite is like JS with column datatypes, except even looser. The claim about Datomic only working with JVM languages isn't right, it has a rest api there are eg python and js client libs using that.
- masklinn 2y ago
  > Missing sqlite comparison point: data types. SQLite is like JS with column datatypes, except even looser. Also defaults: - sqlite has STRICT tables, you have to opt in, per table. - sqlite does not check foreign keys by default, you have to opt in, per connection. - sqlite has WAL mode, you have to opt in, per database. And even with that you may want / need to add a fair amount of work to ensure you're not upgrading connections lazily (fecking SQLITE_BUSY).
- TacticalCoder 2y ago
  > The claim about Datomic only working with JVM languages isn't right ... TFA is also implying that it's either Datomic or Postgres but you can use Datomic on top of Postgres.
arpinum 2y ago
It'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's evaluation[2] for control plane storage that actually goes over their specific needs and context. [1] Ok, just one, Rick Houlihan is currently at MongoDB. [2] https://rfd.shared.oxide.computer/rfd/53 https://rfd.shared.oxide.computer/rfd/53
- wordofx 2y ago
  Na. The post is good.
- worik 2y ago
  > Rick Houlihan is currently at MongoDB. Not according to the YT video. AWS in 2018 I did not detect technical flaws in the article. I thought it was very good
  - arpinum 2y ago
    [flagged]
    - jb1991 2y ago
      > The author asked where Rick is today and got his name wrong. That is not what is called a “technical flaw“.
    - emccue 2y ago
      Yeah, shit. Fixed that one. Would you believe I don't have an editor?
- marcus0x62 2y ago
  > It's not worth pointing out the technical flaws in the post[1]. It might help your argument if you pointed out a real technical flaw in the content of the post, and not an example of the author being mistaken about a stranger's first name.
tormeh 2y ago
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.
- nsonha 2y ago
  more like PHP, which is funny because they always come in pair, prolly originate from the days of LAMP stack. js is more associated with Mongo, another bad db. Most modern js projects (or any modern project really, except PHP) use Postgres
  - a012 2y ago
    20 years ago LAMP (Linux, Apache, MySQL, PHP) stack ~is~ was the most common combo of the web
    - graemep 2y ago
      Not sure about Apache, but Linux, MySQL and PHP is still the most common combo in terms of number of sites running it. Wordpress alone is enough to establish that.
      - nsonha 2y ago
        > most common combo in terms of number of sites running it. Wordpress... does that claim do anything for anyone? India is the most populated country on earth, so?
  - treflop 2y ago
    It’s because PHP’s popularity is old and Postgres used to be mid. MySQL was faster and better than Postgres. But Postgres slowly improved and then got better than MySQL while MySQL stagnated. The most basic bugs persisted, basic features never got added and consistency never seemed to be a point of improvement.
    - Gud 2y ago
DarkNova6 2y ago
> If you see a college student or fresh grad using MongoDB stop them. They need help. They have been led astray. I like this sentence way more than I should.
- cryptoz 2y ago
  I don’t. How are new grads supposed to learn the ups and downs of different choices they make? Just being told they’re led astray in a blog post isn’t gonna work - it’ll backfire. I used node as a new grad for things it wasn’t meant for and that’s how I learned what it is good at and what it isn’t.
  - mewpmewp2 2y ago
    Out of curiousity what were the things you shouldn't have used node for?
    - chrisldgk 2y ago
      I‘d also like to know. I have yet to run into anything Node can’t handle
  - vbezhenar 2y ago
    It's OK to use anything in personal or throw-away projects. But if your choices might cause big losses, it's better to avoid experimental technologies. "Nobody was fired for choosing IBM" is a known meme, but it's not just meme, it's actually solid advice (not specifically about IBM).
    - sdoering 2y ago
      You are basically referring to the "Boring Technology Club" [1] by @mcfunley. [1]: https://boringtechnology.club/ https://boringtechnology.club/
- re-thc 2y ago
  > They have been led astray. They haven't though. What's wrong with using a tool even if it might be bad? Especially as a fresh user. It's how we learn. From both good and bad experiences. > They need help. Sadly it's not the fresh grad, but the "experienced" that only keep their old experiences that need help. Is this comment from 2010? MongoDB has improved. Maybe not to the point of being the best but definitely not unusable.
nsonha 2y ago
> AI is a bubble why does it even matter? I know that I need multimodal search in my product, and that is why I need vector DB. You're not saying anything interesting by saying "AI is a bubble". If you say something like I may not actually need RAG/mutimodal/semantic search/dedicated vector db then you may have my attention.
- emccue 2y ago
  Where does the funding for the companies developing those databases come from? I do not know enough about vector search to assert pgvector is enough for you, but I do know enough about supply chains to get woozy
  - nsonha 2y ago
    so like if the funding for AI disappears then somehow my requirement of multimodal search also disappears, and with it all the existing solutions, some NOT VC funded, like pgvector?
kingkongjaffa 2y ago
Does anyone know why seemingly all the introductory courses advocated nosql stuff like using mongoDB Even freecodecamp who is excellent, does this. They have a rel-db course https://www.freecodecamp.org/learn/relational-database/ https://www.freecodecamp.org/learn/relational-database/ but their backend course uses mongodb https://www.freecodecamp.org/learn/back-end-development-and-apis/ https://www.freecodecamp.org/learn/back-end-development-and-...
- tytho 2y ago
  I can’t speak to the official decisions made by these camps/courses, but from my own experience as an undergrad, I was first introduce to MySQL, and the professors at my university did not teach using migration management tools for bringing a schema in a database up. You were either using a GUI to set up the tables, or running your own cobbled together sql files. For class assignments this was fine. Then I had a professor introduce mongo to me. I was floored by the idea of having my schema live along-side the application code! No more messing around in SQL GUIs! Then of course over time I realized you still need to maintain a schema over time and provide someway to “upgrade” data when your schema evolves, and keep your data consistent. Then I discovered the tools around migrating mongo data are not nearly as mature as the ones you’ll find for SQL databases. I find mongo alright at producing a short-lived prototype of an application (e.g. school assignments), but the risk of it shipping to production for a long period is too risky for the “benefit”.
MrThoughtful 2y ago
Their reasoning is that some platforms like Heroku do not support SQLite. Why use those then and not a platform that supports it, like Glitch? 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.
dom96 2y ago
This should be titled "Just use Sqlite", you really rarely need anything more unless you're Google or Facebook.
- deleted 2y ago
  [deleted]
cosmicradiance 2y ago
1. With the recent developments at CockroachDB one may like to bundle it along with MSSQL and Oracle. 2. Like the author, I will like to understand "Why not MariaDB? (a free variant of MySql)".
hit8run 2y ago
Just use SQLite3. You will with 99.99% chance never need more. Now what?
Kiro 2y ago
When people say "Just use SQLite. It's almost as good as Postgres and you won't need anything more" I'm trying to understand why I shouldn't just use Postgres. It's not like it's hard to install or has any significant overhead. Please enlighten me.
- masklinn 2y ago
  > It's not like it's hard to install or has any significant overhead. Depends on the environment or lack thereof, postgres is a pain in the ass on windows, and then you need support for software configuration so that it can talk to postgres, and then you have to take care of the features you're using. If you're deploying a complex server-side system with lots of moving parts, then yes postgres is basically free. But if you're deploying client-side, or want to run it in a VPS, or whatever, postgres might go from not available to extra cost to a huge chore. > Just use SQLite. It's almost as good as Postgres and you won't need anything more Can't say I agree with that sentiment in any way though, every time I use it sqlite frustrates me in its limitations compared to postgres, and how weak the defaults are from a safety and consistency perspective.
  - CuriouslyC 2y ago
    > Depends on the environment or lack thereof, postgres is a pain in the ass on windows, and then you need support for software configuration so that it can talk to postgres, and then you have to take care of the features you're using. This is why everyone uses docker and .env files. The problem has already been solved and you can copy/paste starter files from project to project to make it a non issue.
    - 0dayz 2y ago
      Well running dbs in containers are generally not a good idea.
      - CuriouslyC 2y ago
        Works fine to enable developers, and most people use cloud database offerings like RDS in production.
worik 2y ago
And often no databases manager is the best solution. Literally If you are not storing much data no datase manager is th best
sjeneenee 2y ago
Ah yes, the I don't have any other use case therefor all others are not good
- kmac_ 2y ago
  Yes, one very common use case where Postgres does not scale well is analytics. Snowflake, Vertica, and ClickHouse work much better. I say this after working on several projects where development teams hit a wall with row storage databases. Still, PostgreSQL is great, it's my default DB as well.
  - winrid 2y ago
    Hit a wall because they didn't want to buy faster disks? What hardware were they running?
deleted 2y ago
[deleted]
tetha 2y ago
On the MySQL vs Postgres topic: We migrated for two reasons. 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're audited, try as you might. 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. For example, we'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. 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.
- philipwhiuk 2y ago
  Yeah there's absolutely no reason to use MySQL. You should use MariaDB if you are stuck on a MySQL project to avoid Oracle. And you should use PostgreSQL for everything new.
- azurelake 2y ago
  MySQL has a very mature open source HA story with the flavors of group replication, as well as being able to replicate DDL. Not to mention Orchestrator and friends. As a matter of fact, EnterpriseDB (the largest contributor to Postgres) has a paid multi master offering, so there's anti incentives in place to improve its HA story...
refset 2y ago
If Postgres already had decent temporal table support (per SQL:2011 system time + application time "bitemporal" 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.), "just use temporal tables" would be the ideal default choice. To get an idea of why, see https://docs.xtdb.com/tutorials/financial-usecase/time-in-finance.html https://docs.xtdb.com/tutorials/financial-usecase/time-in-fi...
cpursley 2y ago
Great post - the comparison to specific tech was really useful. Just added it to my "Postgres Is Enough" gist: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#postgresql-is-enough https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
Jupe 2y ago
From the article, DynamoDB-likes are good IF: * You know exactly what your app needs to do, up-front But isn'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/O intensive... and risky.
- azurelake 2y ago
  You can also add add GSIs (with their caveats) without any re-work.
- williamdclt 2y ago
  50M rows is really not that much, I’d guesstimate an index creation to take single-digit minutes. None of these operations I’d expect to cause downtime, or require table duplication or to be risky Edit: to be fair, you’re right there’s footguns. Make sure index creation is concurrently, and be careful with column default that might take a lock. It’s easy to do the right thing and have no problem, but also to do the wrong thing and have downtime
  - andatki 2y ago
    Newer versions of Postgres also support dropping indexes concurrently. I recommend using the concurrently option when dropping unused or unneeded indexes on any table with active writes and reads. https://www.postgresql.org/docs/current/sql-dropindex.html https://www.postgresql.org/docs/current/sql-dropindex.html
deleted 2y ago
[deleted]
mgaunard 2y ago
Not everyone builds the same kind of application and has the same amount of data with the same kind of interactions.
geenat 2y ago
Horizontal scale of writes. Citus would be alright if the HA story was better: https://github.com/citusdata/citus/issues/7602 https://github.com/citusdata/citus/issues/7602
endisneigh 2y ago
Use what you know, ship useful stuff.
- maipen 2y ago
  I agree. Mariadb is one of the easiest dbs i have ever used. Easy to setup and flexible. I prefer to use whatever makes it quicker to build something, which usually means whatever I am experienced with already. Building products is what’s important at the end of the day. No body cares, nor should they, what kind of tools Michael Angelo used. His art is what we value.
  - philipwhiuk 2y ago
    > Michael Angelo Michaelangelo was his first name. His full name is Michelangelo di Lodovico Buonarroti Simoni
    - andrewinardeer 2y ago
      Like Leo Nardo, Don E. Telli and Raph A. El. All masters of their chosen marital arts.
christkv 2y ago
Really just reads as an article reaffirming his own bias. For Mongo at least most of it is wrong. - 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. - 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. - JSON schema can be used to enforce integrity on collections. - You can join but this I definitely don’t recommend if possible. - I personally like the pipeline concept for queries and wish there was something like this for relational databases to make writing queries easier. - 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. - 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. 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.
ginko 2y ago
I wish postgres had a library only mode that directly stored to a file like sqlite. That'd make starting development a lot easier since you don't have to jump through the hoops of setting up a postgres server. You could then switch to a "proper" DB when your application grows.
- mortehu 2y ago
  It's literally 4 lines of Python code calling subprocess.Popen to start a PostgreSQL server for a given database directory and connecting to it via a pipe on the filesystem. However, you can't launch multiple concurrent instances like this.
aussieguy1234 2y ago
If you're just starting a startup, go with Postgres for most things. With limited devops resources it'll be good enough in most scenarios. You can always over optimise later on.
wood_spirit 2y ago
My own advice would be start with SQLite and do a trivial migration to Postgres if warranted.
CuriouslyC 2y ago
I'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. 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 -> Pouch story is easy and robust.
dmezzetti 2y ago
I'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/month DigitalOcean droplet. Can't say the same for Postgres. > AI is a bubble 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 (https://github.com/asg017/sqlite-vec https://github.com/asg017/sqlite-vec). > Even if your business is another AI grift, you probably only need to import openai. There's much more than this. There are frameworks such as LangChain, LlamaIndex and txtai (disclaimer I'm the primary author of https://github.com/neuml/txtai https://github.com/neuml/txtai) that handle generating embeddings locally or with APIs and storing them in databases such as Postgres.
- jononor 2y ago
  Why can't you run Postgres on a 4 USD droplet? They seem to have 512 MB RAM, that is enough for a basic Postgres instance and a HTTP application server.
yigitcan07 2y ago
I've found out key/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. Postgres flexibility enables for design that is hard to scale. Both in terms of maintainability and performance. Enforcing K/V as a default database in one of my previous companies worked wonders.
- h_tbob 2y ago
  So like how do you do that? How do you store the user record for example as kv?
  - yigitcan07 2y ago
    It mostly revolves around; understanding your primary business concept that write operations revolve around (aggregate root) and duplicating data for different read scenarios (view models). For example imagine you have an "E-commerce" product which you can change details about. The "Product" would be a write-model that you store as K/V. It would accept operations such as; "change price", "change category" etc. Your key would be "product id" and the value would be the whole object represented as json etc. For every write operation you would read the write-model from the database, deserialize, modify it, put it back. Changes to the write-model would trigger events and you could build different read-models to access the data.
- m11a 2y ago
  I don't think there's anything unscalable about Postgres, or RDBMS's in general. I've seen even poorly tuned Postgres with unnormalised table designs work fine at a decent scale, to the point where I'm convinced that Postgres with a decent table design gets you very far. As in: far enough that if you outscaled it, you'd be able to afford a team of excellent engineers to write an appropriate database system. Almost all companies don't need the hyper scaling NoSQL databases supposedly promise. What they do often eventually realise is that they want the querying power and additional ACID guarantees of a typical relational database, so they end up developing a shitty relational database on top of a NoSQL database.
hk__2 2y ago
> Why not MySQL? MySQL is owned by Oracle. Ok, so what about MariaDB?
- codr7 2y ago
  Still nowhere near Postgres from my experience, full of nasty surprises like arbitrary missing features and weird defaults.
christophilus 2y ago
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.
- nh2 2y ago
  If your app is sane (== uses threads for blocking IO operations) then this does not happen.
nu11ptr 2y ago
I don't hate SQL and I agree for many applications it makes sense, but I disagree 100% with "default to a SQL database" (like Postgres). Instead, figure out what you need based on your app. 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'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/key/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 "tables" (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't to say MongoDB is "better". 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. TL;DR Don't default to anything, look at your requirements and make an intelligent choice.
adityapatadia 2y ago
Almost all statements about MongoDB are wrong. > You know exactly what your app needs to do, up-front No one does. Mongodb still perfectly fits. > You know exactly what your access patterns will be, up-front 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. > You have a known need to scale to really large sizes of data This is exactly a great point. When data size goes to a billion rows, Postgres is tough. MongoDB just works without issue. > You are okay giving up some level of consistency This is said for ages about MongoDB. Today, it provides very good consistency. > This is because this sort of database is basically a giant distributed hash map. Putting MongoDB in category of Dynamo is a big mistake. It's NOT a giant distributed hash map. > Arbitrary questions like "How many users signed up in the last month" 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's just outside the scope of this kind of database. You need to be ETL-ing your data out to handle it. This shows the author has no idea how MongoDB aggregation works. I don't want fresh grads to use SQL just because they learn relations (and consistency and constraints and what not). It's perfectly fine to start on MongoDB and make it the primary DB.
- deleted 2y ago
  [deleted]
- j45 2y ago
  What percentage of projects hit a billion rows? I guess one could write a lot of extra rows to try and get there.
- rastignack 2y ago
  > This is exactly a great point. When data size goes to a billion rows, Postgres is tough. MongoDB just works without issue. Is it though ? Maybe 5-10 years ago it was.
  - endisneigh 2y ago
    It is still true that vanilla Postgres doesn’t scale well beyond multiple machines. There are extensions that help, though.
louwrentius 2y ago
> You can only have so much RAM. You can have a lot more than you'd think, but its still pretty limited compared to hard drives. Your data fits in ram[0]. [0]: https://yourdatafitsinram.net https://yourdatafitsinram.net
- m11a 2y ago
  I suppose the question is: at what cost? eg on RDS, they'll give you instances with 1TB of RAM, eg a `db.r6idn.32xlarge`, at the nice price of $75/hr ($54k/mo). Not to mention that, in a microservices architecture, assuming you're not sharing a database, you might be multiplying that figure out a few times. So just because it's possible for it to fit in RAM doesn't mean it's economical. RAM isn't exactly getting exponentially cheaper or more spacious anymore. The hope was flash memory would be the solution, but not sure how far that's getting these days.
evilmonkey19 2y ago
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.
- chimert 2y ago
  I agree. Sqlite is fine unless you need concurrent write connections. I use it for everything.
emccue 2y ago
Okay, I am very sorry that I got Rick Houlihan's name wrong. In my defense, I hadn't watched his talks _recently_ and we've all been Berenstain Bear'ed a few times. But also the comparison of DynamoDB/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 "powerful" operators. It is still a database where you store denormalized information and therefore is inflexible to changes in access patterns.
- arpinum 2y ago
  > It is still a database where you store denormalized information and therefore is inflexible to changes in access patterns. It is flexible and you don't need to know your exact access patterns upfront. It may not be as flexible as your chosen technology, but that doesn't make your statement true.
- endisneigh 2y ago
  You can store normalized information. What you’re saying is still wrong. With respect to schema you can use Atlas Schema. If you’re not really familiar you shouldn’t make these comparisons IMO.
dangoodmanUT 2y ago
There's a lot of _very_ arguably false statements in here, esp around mongo and dynamo. Postgres still has to "rewrite" data if you need another index. In fact it's about the same amount if you had to add an index for dynamodb... Also, when's the last time you changed your primary key in a postgres table? Or are you just adding indexes?
dangoodmanUT 2y ago
These posts are always so biased to the person that's used postgres 100x more than any other DB.
- evanelias 2y ago
  Absolutely this. The author seems blind to Postgres shortcomings. For example, the author notes MySQL has "features locked behind their enterprise editions." That is true for some features in MySQL, yes. But the same thing is true in Postgres for DDL logical replication and other HA-related features, which are only in EDB Postgres -- and yet those are features MySQL has had in open source for over two decades.
deleted 2y ago
[deleted]
Longwelwind 2y ago
> It's annoying because, especially with MongoDB, people come into it having been sold on it being a more "flexible" database. Yes, you don'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. I really like this sentence because it perfectly encapsulates a mistake that, I think, people do when considering using MongoDB. They believe that the schemaless nature of NoSQL database is an advantage because you don't need to do migrations when adding features (adding columns, splitting them, ...). But that'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.
j45 2y ago
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. It’s way less work just to learn sql or an orm. Nosql is great at being a document store. 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. 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.
throw0101d 2y ago
For MySQL, for smaller deployments, I've found Galera to really be a handy HA system to get going: > 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. * https://galeracluster.com/library/documentation/overview.html https://galeracluster.com/library/documentation/overview.htm... * https://packages.debian.org/search?keywords=galera https://packages.debian.org/search?keywords=galera The closest out-of-box solution that I know of for Postgres is the proprietary BDR: * https://www.enterprisedb.com/docs/pgd/4/bdr/ https://www.enterprisedb.com/docs/pgd/4/bdr/ * https://wiki.postgresql.org/wiki/BDR_Project https://wiki.postgresql.org/wiki/BDR_Project There are systems like Bucardo, but they are trigger-based and external to the Postgres software: * https://www.percona.com/blog/multi-master-replication-solutions-for-postgresql/ https://www.percona.com/blog/multi-master-replication-soluti... Having a built-in 3-node MMR (or 2N+1arb[0]) solution would solve a bunch of 'simple' HA situations. [0] https://packages.debian.org/search?keywords=galera-arbitrator https://packages.debian.org/search?keywords=galera-arbitrato...
- Sesse__ 2y ago
  > For MySQL, for smaller deployments, I've found Galera to really be a handy HA system to get going: Well, at least if you don't value your data. (https://aphyr.com/posts/327-jepsen-mariadb-galera-cluster https://aphyr.com/posts/327-jepsen-mariadb-galera-cluster; Galera failed Jepsen testing in 2015 and the bug is still open with a 2022 mention of basically “we have experimental support [for actually providing the data consistency we promise], but it's not clear if it's worth it because it will be very slow”)
jb3689 2y ago
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.
graemep 2y ago
I do not get the reasoning around SQLite. 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. 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). 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).
PeterZaitsev 2y ago
Note... you can use PostgreSQL as MongoDB... with FerretDB :)
PeterZaitsev 2y ago
Here we go again... Just use X, forever, in all cases, is misguided whatever X is - a database, programming language, ... a vehicle. 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 Postgres

236 points by refset 2y ago

KingOfCoders 2y ago

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

worik 2y ago
Been waiting a rew decades now...
Gud 2y ago
Linux didn’t “do” anything to *BSD. Linux emerged at the same time there was a nasty lawsuit against BSD.
- KingOfCoders 2y ago
  SunOS is/was an BSD based operating system, and Linux replaced sun servers that were running SunOS with Intel servers in the same way as Linux based servers replaced most server operating systems in data centers (and today cloud providers), like Ultrix, IRIX, HP-UX etc. I've meant "do" in this way.

bob1029 2y ago

There is absolutely no reason you can't make SQLite go all the way. Starting with it is the only thing that makes sense to me. 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're talking 3-4 orders of magnitude difference in latency. It's not even a contest. 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'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's the most trivial thing imaginable to configure and use. Hosted SQL solutions will never come close to this level of simplicity. 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've still not had any outages related to SQLite in the 8+ years we've been using it in prod.

Kinrany 2y ago
With Pglite it should now be possible to have the cake of Postgres' rich functionality and embed it too
- jddj 2y ago
  *in nodejs (for now)
  - Kinrany 2y ago
    Famous last words, but it's already in a Wasm module and only needs a bit of glue code, so how hard could it be? :D
    - samwillis 2y ago
      Yep, we are actively working on native support. (I work on PGlite)
codeflo 2y ago
Why would you run Postgres on a different box in such a scenario? A docker run command to get a Postgres instance up and running isn’t any more complicated than linking in Sqlite, maybe even simpler. And you get proper ACID transactions for free.
- rcarmo 2y ago
  1) Fault isolation in case of hardware failure 2) You may not even be able to _think_ about using Docker (OS restrictions, airgaps to install images, etc.) These are just 2 that come to mind from working in regulated industries.
  - codeflo 2y ago
    In this thread, we're making a comparison to running Sqlite. Fault isolation is not an argument. And you don't have to run Docker to run Postgres, it's just an easy way to do so. Even airgapped.

frithsun 2y ago

Yup. With nothing but love for sqlite.

rcarmo 2y ago

The "SQLite is just a file" 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: - Mix static and dynamic content generation (and let's face it, most websites are mostly static from a server perspective) - Designate a writer node and use any of the multiple SQLite replication features But, in short, if you use an ORM that supports both SQLite and Postgres you'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.

sampullman 2y ago
Even without an ORM that supports both, as long as the DB layer is reasonably separated in your application it shouldn't be too much effort to switch. And if you've scaled to the point where it matters, you probably have the resources to do so.
- CraigJPerry 2y ago
  >> as long as the DB layer is reasonably separated in your application I find this is easy in retrospect but tricky when you’re building a system. It’s all shades of grey when you’re building: Should I put my queue in my DB and just avoid the whole 2PC drama (saga is a more apt word but too much opportunity for confusion in this context). I probably should implement that check constraint or that trigger but should I add a plugin to my DB to offer better performance and correctness of special type X or just use a trigger for that too? Should I create my own db plugin so that triggers can publish messages themselves without going through an app layer? In retrospect it’s easy to see when you went too far, or not far enough. At decision time the design document your team are refining starts to head past the ~10 page sweet spot limit.
  - sampullman 2y ago
    That's true, even with "perfect" abstractions, switching gets more complicated as you use more complex database features. It's only really easy if you push most of your constraints and triggers to the application. In practice, I've only ever switched databases with really simple CRUD stuff and have otherwise been able to predict that I'll eventually want Postgres/RabbitMQ/etc and build it in from the start.

andrewstuart 2y ago

Totally agree - I have tried many databases of all flavors, but I always come back to Postgres. HOWEVER - this blog post is missing a critical point.... the quote should be: ---> Just use Postgres AND ---> Just use SQL "Program the machine" stop using abstractions, ORMs, libraries and layers. Learn how to write SQL - or at least learn how to debug the very good SQL that ChatGPT writes. 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/Subscription for Logical Replication. Push all your business logic into big long stored procedures/functions - don't be pulling the data back and munging it in some other language - make the database do the work! All this stuff you get from programming the machine. Stop using that ORM/lib and write SQL. EDIT: People replying saying "only use generic SQL so you cans switch databases!" - to that I say - rubbish! I nearly wrote a final sentence in the above saying "forget that old wives tale about the dangers of using a databases functionality because you'll need to switch databases in the future and then you'll be stuck!" Because the reason people switch databases is when they switch to Postgres after finding some other thing didn't get the job done. The old "tut tut, don't use the true power of a database because you'll need to switch to Oracle/MySQL/SQL server/MongoDB" - that just doesn't hold.

rcarmo 2y ago
your answer was fine and good until you classified ChatGPT's SQL generation as "very good" -- which it is not. I've had _all_ GPT models spit out monstrosities and slow queries of all kinds. ORMs are not all bad. In fact, some ORMs generate better code for really complex joins (think hundreds of tables, each with hundreds of columns) than humans, and often ensure that trivial best practices (like indexes and consistent foreign keys) are followed. Writing SQL is a great skill, but if you tie yourself to a single database engine's idioms then you're in for a shock when you switch platforms/jobs/environments.
- deleted 2y ago

fulafel 2y ago

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

masklinn 2y ago
> Missing sqlite comparison point: data types. SQLite is like JS with column datatypes, except even looser. Also defaults: - sqlite has STRICT tables, you have to opt in, per table. - sqlite does not check foreign keys by default, you have to opt in, per connection. - sqlite has WAL mode, you have to opt in, per database. And even with that you may want / need to add a fair amount of work to ensure you're not upgrading connections lazily (fecking SQLITE_BUSY).
TacticalCoder 2y ago
> The claim about Datomic only working with JVM languages isn't right ... TFA is also implying that it's either Datomic or Postgres but you can use Datomic on top of Postgres.

arpinum 2y ago

It'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's evaluation[2] for control plane storage that actually goes over their specific needs and context. [1] Ok, just one, Rick Houlihan is currently at MongoDB. [2] https://rfd.shared.oxide.computer/rfd/53 https://rfd.shared.oxide.computer/rfd/53

wordofx 2y ago
Na. The post is good.
worik 2y ago
> Rick Houlihan is currently at MongoDB. Not according to the YT video. AWS in 2018 I did not detect technical flaws in the article. I thought it was very good
- arpinum 2y ago
  [flagged]
  - jb1991 2y ago
    > The author asked where Rick is today and got his name wrong. That is not what is called a “technical flaw“.
  - emccue 2y ago
    Yeah, shit. Fixed that one. Would you believe I don't have an editor?
marcus0x62 2y ago
> It's not worth pointing out the technical flaws in the post[1]. It might help your argument if you pointed out a real technical flaw in the content of the post, and not an example of the author being mistaken about a stranger's first name.

tormeh 2y ago

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.

nsonha 2y ago
more like PHP, which is funny because they always come in pair, prolly originate from the days of LAMP stack. js is more associated with Mongo, another bad db. Most modern js projects (or any modern project really, except PHP) use Postgres
- a012 2y ago
  20 years ago LAMP (Linux, Apache, MySQL, PHP) stack ~is~ was the most common combo of the web
  - graemep 2y ago
    Not sure about Apache, but Linux, MySQL and PHP is still the most common combo in terms of number of sites running it. Wordpress alone is enough to establish that.
    - nsonha 2y ago
      > most common combo in terms of number of sites running it. Wordpress... does that claim do anything for anyone? India is the most populated country on earth, so?
- treflop 2y ago
  It’s because PHP’s popularity is old and Postgres used to be mid. MySQL was faster and better than Postgres. But Postgres slowly improved and then got better than MySQL while MySQL stagnated. The most basic bugs persisted, basic features never got added and consistency never seemed to be a point of improvement.
  - Gud 2y ago

DarkNova6 2y ago

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

cryptoz 2y ago
I don’t. How are new grads supposed to learn the ups and downs of different choices they make? Just being told they’re led astray in a blog post isn’t gonna work - it’ll backfire. I used node as a new grad for things it wasn’t meant for and that’s how I learned what it is good at and what it isn’t.
- mewpmewp2 2y ago
  Out of curiousity what were the things you shouldn't have used node for?
  - chrisldgk 2y ago
    I‘d also like to know. I have yet to run into anything Node can’t handle
- vbezhenar 2y ago
  It's OK to use anything in personal or throw-away projects. But if your choices might cause big losses, it's better to avoid experimental technologies. "Nobody was fired for choosing IBM" is a known meme, but it's not just meme, it's actually solid advice (not specifically about IBM).
  - sdoering 2y ago
    You are basically referring to the "Boring Technology Club" [1] by @mcfunley. [1]: https://boringtechnology.club/ https://boringtechnology.club/
re-thc 2y ago
> They have been led astray. They haven't though. What's wrong with using a tool even if it might be bad? Especially as a fresh user. It's how we learn. From both good and bad experiences. > They need help. Sadly it's not the fresh grad, but the "experienced" that only keep their old experiences that need help. Is this comment from 2010? MongoDB has improved. Maybe not to the point of being the best but definitely not unusable.

nsonha 2y ago

> AI is a bubble why does it even matter? I know that I need multimodal search in my product, and that is why I need vector DB. You're not saying anything interesting by saying "AI is a bubble". If you say something like I may not actually need RAG/mutimodal/semantic search/dedicated vector db then you may have my attention.

emccue 2y ago
Where does the funding for the companies developing those databases come from? I do not know enough about vector search to assert pgvector is enough for you, but I do know enough about supply chains to get woozy
- nsonha 2y ago
  so like if the funding for AI disappears then somehow my requirement of multimodal search also disappears, and with it all the existing solutions, some NOT VC funded, like pgvector?

kingkongjaffa 2y ago

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

tytho 2y ago
I can’t speak to the official decisions made by these camps/courses, but from my own experience as an undergrad, I was first introduce to MySQL, and the professors at my university did not teach using migration management tools for bringing a schema in a database up. You were either using a GUI to set up the tables, or running your own cobbled together sql files. For class assignments this was fine. Then I had a professor introduce mongo to me. I was floored by the idea of having my schema live along-side the application code! No more messing around in SQL GUIs! Then of course over time I realized you still need to maintain a schema over time and provide someway to “upgrade” data when your schema evolves, and keep your data consistent. Then I discovered the tools around migrating mongo data are not nearly as mature as the ones you’ll find for SQL databases. I find mongo alright at producing a short-lived prototype of an application (e.g. school assignments), but the risk of it shipping to production for a long period is too risky for the “benefit”.

MrThoughtful 2y ago

Their reasoning is that some platforms like Heroku do not support SQLite. Why use those then and not a platform that supports it, like Glitch? 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.

dom96 2y ago

This should be titled "Just use Sqlite", you really rarely need anything more unless you're Google or Facebook.

deleted 2y ago
[deleted]

cosmicradiance 2y ago

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

hit8run 2y ago

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

Kiro 2y ago

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

masklinn 2y ago
> It's not like it's hard to install or has any significant overhead. Depends on the environment or lack thereof, postgres is a pain in the ass on windows, and then you need support for software configuration so that it can talk to postgres, and then you have to take care of the features you're using. If you're deploying a complex server-side system with lots of moving parts, then yes postgres is basically free. But if you're deploying client-side, or want to run it in a VPS, or whatever, postgres might go from not available to extra cost to a huge chore. > Just use SQLite. It's almost as good as Postgres and you won't need anything more Can't say I agree with that sentiment in any way though, every time I use it sqlite frustrates me in its limitations compared to postgres, and how weak the defaults are from a safety and consistency perspective.
- CuriouslyC 2y ago
  > Depends on the environment or lack thereof, postgres is a pain in the ass on windows, and then you need support for software configuration so that it can talk to postgres, and then you have to take care of the features you're using. This is why everyone uses docker and .env files. The problem has already been solved and you can copy/paste starter files from project to project to make it a non issue.
  - 0dayz 2y ago
    Well running dbs in containers are generally not a good idea.
    - CuriouslyC 2y ago
      Works fine to enable developers, and most people use cloud database offerings like RDS in production.

worik 2y ago

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

sjeneenee 2y ago

Ah yes, the I don't have any other use case therefor all others are not good

kmac_ 2y ago
Yes, one very common use case where Postgres does not scale well is analytics. Snowflake, Vertica, and ClickHouse work much better. I say this after working on several projects where development teams hit a wall with row storage databases. Still, PostgreSQL is great, it's my default DB as well.
- winrid 2y ago
  Hit a wall because they didn't want to buy faster disks? What hardware were they running?

deleted 2y ago

[deleted]

tetha 2y ago

On the MySQL vs Postgres topic: We migrated for two reasons. 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're audited, try as you might. 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. For example, we'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. 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.

philipwhiuk 2y ago
Yeah there's absolutely no reason to use MySQL. You should use MariaDB if you are stuck on a MySQL project to avoid Oracle. And you should use PostgreSQL for everything new.
azurelake 2y ago
MySQL has a very mature open source HA story with the flavors of group replication, as well as being able to replicate DDL. Not to mention Orchestrator and friends. As a matter of fact, EnterpriseDB (the largest contributor to Postgres) has a paid multi master offering, so there's anti incentives in place to improve its HA story...

refset 2y ago

If Postgres already had decent temporal table support (per SQL:2011 system time + application time "bitemporal" 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.), "just use temporal tables" would be the ideal default choice. To get an idea of why, see https://docs.xtdb.com/tutorials/financial-usecase/time-in-finance.html https://docs.xtdb.com/tutorials/financial-usecase/time-in-fi...

cpursley 2y ago

Great post - the comparison to specific tech was really useful. Just added it to my "Postgres Is Enough" gist: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#postgresql-is-enough https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...

Jupe 2y ago

From the article, DynamoDB-likes are good IF: * You know exactly what your app needs to do, up-front But isn'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/O intensive... and risky.

azurelake 2y ago
You can also add add GSIs (with their caveats) without any re-work.
williamdclt 2y ago
50M rows is really not that much, I’d guesstimate an index creation to take single-digit minutes. None of these operations I’d expect to cause downtime, or require table duplication or to be risky Edit: to be fair, you’re right there’s footguns. Make sure index creation is concurrently, and be careful with column default that might take a lock. It’s easy to do the right thing and have no problem, but also to do the wrong thing and have downtime
- andatki 2y ago
  Newer versions of Postgres also support dropping indexes concurrently. I recommend using the concurrently option when dropping unused or unneeded indexes on any table with active writes and reads. https://www.postgresql.org/docs/current/sql-dropindex.html https://www.postgresql.org/docs/current/sql-dropindex.html

deleted 2y ago

[deleted]

mgaunard 2y ago

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

geenat 2y ago

Horizontal scale of writes. Citus would be alright if the HA story was better: https://github.com/citusdata/citus/issues/7602 https://github.com/citusdata/citus/issues/7602

endisneigh 2y ago

Use what you know, ship useful stuff.

maipen 2y ago
I agree. Mariadb is one of the easiest dbs i have ever used. Easy to setup and flexible. I prefer to use whatever makes it quicker to build something, which usually means whatever I am experienced with already. Building products is what’s important at the end of the day. No body cares, nor should they, what kind of tools Michael Angelo used. His art is what we value.
- philipwhiuk 2y ago
  > Michael Angelo Michaelangelo was his first name. His full name is Michelangelo di Lodovico Buonarroti Simoni
  - andrewinardeer 2y ago
    Like Leo Nardo, Don E. Telli and Raph A. El. All masters of their chosen marital arts.

christkv 2y ago

Really just reads as an article reaffirming his own bias. For Mongo at least most of it is wrong. - 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. - 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. - JSON schema can be used to enforce integrity on collections. - You can join but this I definitely don’t recommend if possible. - I personally like the pipeline concept for queries and wish there was something like this for relational databases to make writing queries easier. - 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. - 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. 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.

ginko 2y ago

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

mortehu 2y ago
It's literally 4 lines of Python code calling subprocess.Popen to start a PostgreSQL server for a given database directory and connecting to it via a pipe on the filesystem. However, you can't launch multiple concurrent instances like this.

aussieguy1234 2y ago

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

wood_spirit 2y ago

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

CuriouslyC 2y ago

I'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. 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 -> Pouch story is easy and robust.

dmezzetti 2y ago

I'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/month DigitalOcean droplet. Can't say the same for Postgres. > AI is a bubble 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 (https://github.com/asg017/sqlite-vec https://github.com/asg017/sqlite-vec). > Even if your business is another AI grift, you probably only need to import openai. There's much more than this. There are frameworks such as LangChain, LlamaIndex and txtai (disclaimer I'm the primary author of https://github.com/neuml/txtai https://github.com/neuml/txtai) that handle generating embeddings locally or with APIs and storing them in databases such as Postgres.

jononor 2y ago
Why can't you run Postgres on a 4 USD droplet? They seem to have 512 MB RAM, that is enough for a basic Postgres instance and a HTTP application server.

yigitcan07 2y ago

I've found out key/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. Postgres flexibility enables for design that is hard to scale. Both in terms of maintainability and performance. Enforcing K/V as a default database in one of my previous companies worked wonders.

h_tbob 2y ago
So like how do you do that? How do you store the user record for example as kv?
- yigitcan07 2y ago
  It mostly revolves around; understanding your primary business concept that write operations revolve around (aggregate root) and duplicating data for different read scenarios (view models). For example imagine you have an "E-commerce" product which you can change details about. The "Product" would be a write-model that you store as K/V. It would accept operations such as; "change price", "change category" etc. Your key would be "product id" and the value would be the whole object represented as json etc. For every write operation you would read the write-model from the database, deserialize, modify it, put it back. Changes to the write-model would trigger events and you could build different read-models to access the data.
m11a 2y ago
I don't think there's anything unscalable about Postgres, or RDBMS's in general. I've seen even poorly tuned Postgres with unnormalised table designs work fine at a decent scale, to the point where I'm convinced that Postgres with a decent table design gets you very far. As in: far enough that if you outscaled it, you'd be able to afford a team of excellent engineers to write an appropriate database system. Almost all companies don't need the hyper scaling NoSQL databases supposedly promise. What they do often eventually realise is that they want the querying power and additional ACID guarantees of a typical relational database, so they end up developing a shitty relational database on top of a NoSQL database.

hk__2 2y ago

> Why not MySQL? MySQL is owned by Oracle. Ok, so what about MariaDB?

codr7 2y ago
Still nowhere near Postgres from my experience, full of nasty surprises like arbitrary missing features and weird defaults.

christophilus 2y ago

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.

nh2 2y ago
If your app is sane (== uses threads for blocking IO operations) then this does not happen.

nu11ptr 2y ago

I don't hate SQL and I agree for many applications it makes sense, but I disagree 100% with "default to a SQL database" (like Postgres). Instead, figure out what you need based on your app. 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'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/key/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 "tables" (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't to say MongoDB is "better". 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. TL;DR Don't default to anything, look at your requirements and make an intelligent choice.

adityapatadia 2y ago

Almost all statements about MongoDB are wrong. > You know exactly what your app needs to do, up-front No one does. Mongodb still perfectly fits. > You know exactly what your access patterns will be, up-front 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. > You have a known need to scale to really large sizes of data This is exactly a great point. When data size goes to a billion rows, Postgres is tough. MongoDB just works without issue. > You are okay giving up some level of consistency This is said for ages about MongoDB. Today, it provides very good consistency. > This is because this sort of database is basically a giant distributed hash map. Putting MongoDB in category of Dynamo is a big mistake. It's NOT a giant distributed hash map. > Arbitrary questions like "How many users signed up in the last month" 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's just outside the scope of this kind of database. You need to be ETL-ing your data out to handle it. This shows the author has no idea how MongoDB aggregation works. I don't want fresh grads to use SQL just because they learn relations (and consistency and constraints and what not). It's perfectly fine to start on MongoDB and make it the primary DB.

deleted 2y ago
[deleted]
j45 2y ago
What percentage of projects hit a billion rows? I guess one could write a lot of extra rows to try and get there.
rastignack 2y ago
> This is exactly a great point. When data size goes to a billion rows, Postgres is tough. MongoDB just works without issue. Is it though ? Maybe 5-10 years ago it was.
- endisneigh 2y ago
  It is still true that vanilla Postgres doesn’t scale well beyond multiple machines. There are extensions that help, though.

louwrentius 2y ago

> You can only have so much RAM. You can have a lot more than you'd think, but its still pretty limited compared to hard drives. Your data fits in ram[0]. [0]: https://yourdatafitsinram.net https://yourdatafitsinram.net

m11a 2y ago
I suppose the question is: at what cost? eg on RDS, they'll give you instances with 1TB of RAM, eg a `db.r6idn.32xlarge`, at the nice price of $75/hr ($54k/mo). Not to mention that, in a microservices architecture, assuming you're not sharing a database, you might be multiplying that figure out a few times. So just because it's possible for it to fit in RAM doesn't mean it's economical. RAM isn't exactly getting exponentially cheaper or more spacious anymore. The hope was flash memory would be the solution, but not sure how far that's getting these days.

evilmonkey19 2y ago

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.

chimert 2y ago
I agree. Sqlite is fine unless you need concurrent write connections. I use it for everything.

emccue 2y ago

Okay, I am very sorry that I got Rick Houlihan's name wrong. In my defense, I hadn't watched his talks _recently_ and we've all been Berenstain Bear'ed a few times. But also the comparison of DynamoDB/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 "powerful" operators. It is still a database where you store denormalized information and therefore is inflexible to changes in access patterns.

arpinum 2y ago
> It is still a database where you store denormalized information and therefore is inflexible to changes in access patterns. It is flexible and you don't need to know your exact access patterns upfront. It may not be as flexible as your chosen technology, but that doesn't make your statement true.
endisneigh 2y ago
You can store normalized information. What you’re saying is still wrong. With respect to schema you can use Atlas Schema. If you’re not really familiar you shouldn’t make these comparisons IMO.

dangoodmanUT 2y ago

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

dangoodmanUT 2y ago

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

evanelias 2y ago
Absolutely this. The author seems blind to Postgres shortcomings. For example, the author notes MySQL has "features locked behind their enterprise editions." That is true for some features in MySQL, yes. But the same thing is true in Postgres for DDL logical replication and other HA-related features, which are only in EDB Postgres -- and yet those are features MySQL has had in open source for over two decades.

deleted 2y ago

[deleted]

Longwelwind 2y ago

> It's annoying because, especially with MongoDB, people come into it having been sold on it being a more "flexible" database. Yes, you don'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. I really like this sentence because it perfectly encapsulates a mistake that, I think, people do when considering using MongoDB. They believe that the schemaless nature of NoSQL database is an advantage because you don't need to do migrations when adding features (adding columns, splitting them, ...). But that'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.

j45 2y ago

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. It’s way less work just to learn sql or an orm. Nosql is great at being a document store. 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. 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.

throw0101d 2y ago

For MySQL, for smaller deployments, I've found Galera to really be a handy HA system to get going: > 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. * https://galeracluster.com/library/documentation/overview.html https://galeracluster.com/library/documentation/overview.htm... * https://packages.debian.org/search?keywords=galera https://packages.debian.org/search?keywords=galera The closest out-of-box solution that I know of for Postgres is the proprietary BDR: * https://www.enterprisedb.com/docs/pgd/4/bdr/ https://www.enterprisedb.com/docs/pgd/4/bdr/ * https://wiki.postgresql.org/wiki/BDR_Project https://wiki.postgresql.org/wiki/BDR_Project There are systems like Bucardo, but they are trigger-based and external to the Postgres software: * https://www.percona.com/blog/multi-master-replication-solutions-for-postgresql/ https://www.percona.com/blog/multi-master-replication-soluti... Having a built-in 3-node MMR (or 2N+1arb[0]) solution would solve a bunch of 'simple' HA situations. [0] https://packages.debian.org/search?keywords=galera-arbitrator https://packages.debian.org/search?keywords=galera-arbitrato...

Sesse__ 2y ago
> For MySQL, for smaller deployments, I've found Galera to really be a handy HA system to get going: Well, at least if you don't value your data. (https://aphyr.com/posts/327-jepsen-mariadb-galera-cluster https://aphyr.com/posts/327-jepsen-mariadb-galera-cluster; Galera failed Jepsen testing in 2015 and the bug is still open with a 2022 mention of basically “we have experimental support [for actually providing the data consistency we promise], but it's not clear if it's worth it because it will be very slow”)

jb3689 2y ago

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.

graemep 2y ago

I do not get the reasoning around SQLite. 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. 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). 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).

PeterZaitsev 2y ago

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

PeterZaitsev 2y ago

Here we go again... Just use X, forever, in all cases, is misguided whatever X is - a database, programming language, ... a vehicle. 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.