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.
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.
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.
Surprised to hear that losing 15 min of state is not a big deal in a banking context.
I haven't worked with banks before, genuinely curious, how do they recover from something like this? Wouldn't this potentially destroy all transactions made in that time period?
To be fair they said "frontline application", not a transaction processor specifically. Also in cases where you can't lose stuff it's common to use reliable steam processing, so maybe they can reprocess old events into the system running sqlite. (This is extra general info about "important systems"; I don't know what OP is running)
At best this will increase the load on customer support with an associated reputational hit. At worst you will need to deal with questions from financial regulators.
> There is absolutely no reason you can't make SQLite go all the way.
No reason you can't, but one you to consider if you should. If you're using libraries which make assumptions about your database layer, they may not like the sqlite model. Holding the writer mode for too long is something I experienced in write a few projects. For example paperless-ngx will block the web interface while batch importing documents, even though there's really no reason to do that.
It's less of an issue if you write all your own code and you explicitly target sqlite. But worth keeping in mind.
Sure. I think the caveats I mentioned are real though.
Using SQLite locks you out of some platforms as a service and out of some application architectures. It also means you need to be doing stuff like snapshotting your VM every x minutes.
Given minimal certainty about the scale, future, and properties of your application and organization: Postgres is the better default.
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.
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.
>> 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.
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.
Agreed. For some situations, it might well be easier to take advantage of the static nature of the site and use SQLite compared to setting up a Postgres server. For others, setting up a server could be easier than the “easy” options.
You don't need to maintain, secure and tweak postgres any more than you would with SQLite. Just install it and it'll work. Postgres backup is a single command. And actually you're supposed to create sqlite backups with special command as well, if you're copying a file, you're doing it wrong.
I really don't see any cons with Postgres over SQLite for server applications.
> You don't need to maintain, secure and tweak postgres any more than you would with SQLite.
That's not true. Postgres is another standalone process, SQLite is a library. Even if you have your service and Postgres on the same box, you need to account for yet another process that can independently go down, that is competing for resources etc...
I've never had Postgres "go down". It might if you run out of disk space, but that is going to be a bad time with any database. It is not "competing for resources" when it is running the workload your app is sending it. You may as well say Sqlite is competing for resources in that case.
Major version upgrades are not automatic, you can't just install a newer binary/library version and start it as you can for SQLite. You need to shut down the DB and run `pg_upgrade`, or write manual full export-import scripts with `pg_dump`/`pg_dumpall`/`pg_restore`/`psql`.
And good luck deciding between the different format options, as some of them are unsupported across some of these tools, some cannot export and reimport the full database cluster, there's no idempotent "just import this snapshot" operation (point-in-time restore), lack of progress reporting, etc.
Here are some notes I on the topic:
# Note on Postgres backups
#
# Unfortunately, postgres backup+restore is not straightforward.
#
# * Backups created with `pg_dumpall`, which create an .sql file,
# cannot simply be used for point-in-time recovery.
# They need to be restored with `psql` (not `pg_restore`),
# which errors if the data already exists.
# * You could probably tell it to ignore errors, but naturally it'll just
# run through `INSERT ...`, so it's not a proper point-in-time recovery,
# because it doesn't remove data newer than the backup as expected.
# * To use `pg_restore` (which can ignore existing data, re-creating
# everything with the `--clean` flag), you need to use `pg_dump`
# (not `pg_dumpall`), which cannot backup *all* databases,
# only a single given one.
# * Further, `pg_restore` does not accept `--format=plain` SQL backups
# (the default created by `pg_dump`). Only the non-plain backups are
# accepted, which are less readable for a human to determine whether
# a given backup is the one desired to restore based on the data.
#
# As a result, we aim for restoration using `pg_restore --clean`,
# backing up only the `postgres` database using `pg_dump -d postgres`.
# This works for us because we currently store all our tables in the
# `postgres` database.
# We use `--format=tar` because it is a plain text format, which
# * deduplicates better than compressed formats, and
# * allows a human to `grep` in plain text for desired contents.
Why isn't there a mode with which I can just tell postgres to migrate my data automatically upon startup with a newer version?
And why can't I just have postgres-as-a-library to link into my binary, like I can do with SQLite?
You also can't just run postgres as root (e.g. in a container), and have to set up UNIX users to work around that, because postgres has it hardcoded to avoid running as root. This, too, you don't need to do with SQLite.
Also, postgres is harder to secure.
You need to either use TCP and ensure that other UNIX users on the same system can't just connect, or use UNIX Domain Sockets which have a 108 char path length restriction [1] (which is of course not documented in postgres's docs [2]), so it will suddenly break your CI when its path changes from
> 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
I'll never understand this idea that Postgres and SQLite are somehow interchangeable when the time is right.
My database and Postgres are _literally_ the core definition of everything that my application does. My app is written in Rust, but that doesn't matter because it's a _Postgres_ application. I use Postgres-specific features extensively. Converting the application to SQLite would be essentially a re-write, and it would be worse in every way.
Also, I generally just don't understand this fad of running production backends on SQLite. SQLite is great for what it is, a tiny little embeddable client-side database. But it is a _terrible_ database for non trivial business applications where ref integrity, real types instead of "everything is a string", and battle-tested scaling is essential.
The fact that you use Postgres-specific features extensively is a design decision that many people would never make, regardless of their trust in the engine.
In the enterprise space, it isn't. You often have to build software that will run on different database back-ends. Just because your worldview doesn't align with other people's doesn't mean you own the truth...
I don't think people often switch from Postgres to SQLite, it's probably more common (and much easier) to prototype with SQLite lite first and then switch.
If by referential integrity you just mean FK constraints, you can turn that on in sqlite3.
I think SQLite is pretty good for a lot of use cases. An Axum/sqlite CRUD app should be able to handle at least few hundred requests per second on a medium powered box, which is good enough for a lot of things.
Postgres is really powerful but I don't think it's actually that common to structure your app around it's unique features.
Lately I've been using sqlite in this way on small projects I'm just hacking at, but after seeing pglite this week (https://news.ycombinator.com/item?id=41224689) I'll probably give that a try next time
IMO a downside of SQLite that isn't discussed as often as it should be is the poor support for some table operations like ALTER COLUMN. Need to change a column to null / not null? Drop a foreign key constraint? Tough luck, in some cases the only way to implement a change is recreating the table.
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.
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.
Try to avoid the bespoke features of psql in favor of generic SQL unless cornered by circumstances into doing so, methinks.
If there's one complaint I have about pg, it's that it has too many features that encourage finding cute, non standard, non obvious ways of going about things.
> Try to avoid the bespoke features of psql in favor of generic SQL unless cornered by circumstances into doing so, methinks.
Why? To make migration to another database easier? I've never had the need to migrate any application away from postgres. I usually take full advantage of what the database can do.
I’m a proponent of vendor lock in is not a big deal - you’re not going to switch from AWS to Azure on a whim and if you do, the fact that you’re using ecs instead of k8s isn’t going to slow you down.
But data ownership is the one place I get iffy. What if your db does a rug pull and changes licenses? There’s certainly precedent in this space for that.
"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!"
From my courses I had at university, I've been led to believe that the current trend is doing hexagonal architecture, as that allows for better modularisation of the project and helps keep code clean over many years with many software engineers coming in and out.
As a part of that I've been taught that the only part you could trust then is your internal modules - and even database has to treated as an external source, whose only job is to pull data in and out.
How does that work in what you're suggesting? Is it just a different way of approaching things that will work depending on what's your goal is?
I'm just curious about this as I'm trying to get myself to learn a bit more, just to clarify
The model described in the parent comment is essentially using the database (in this case PostgreSQL, but any RDMS would do) as the hexagonal "core" in which adapters plug in to. This is a powerful pattern that works very well when you use the full features of the RDMS like constraints, triggers, views, etc. This does require "coupling" to the RDMS-specific features, which makes migrating to an alternative system difficult, but in practice this rarely happens if you choose a strong RDMS from the beginning.
You can certainly use the database as a "dumb storage" tool in the hexagonal architecture, that is, as just another adapter. But most of the time you'll end up re-creating RDMS features in poorly written/documented application code that has to interact with the database anyways. Why not just do it all in the database? With a RDMS core, hexagonal adapters can be pure functional components, making them much easier to reason about and maintain.
For more on this idea, and how to avoid pitfalls with the hexagonal pattern, I recommend reading Out of the Tar Pit [1]. It's a short but highly influential paper on "functional relational programming".
I see it, that makes sense! Right, I don't think that you'd have a reason to swap RDMS unless licencing issues come up, like with Oracle. Thank you for helping out.
With Pglite in a few years you'll be able to treat Postgres as a library, and merely allow selecting between in-process and remote via configuration.
The first job of a database is to be a data structure for persisting data, but you're allowed to extend said data structure in your own code. As long as you can come up with a way to keep all the code in version control, test it, etc., it's fine.
You gain: Model consistency guaranteed by the database, your backend basically only acts as an external API for the database.
You lose: Modularity, makes it harder to swap out databases. Also, you have to write SQL for business logic which many developers are bad at or dislike or both.
I've seen a system running on this approach for ten years and it survived three generations of developers programming against this API. There's Python wx frontends, web frontends, Rust software, Java software, C software, etc. They all use the same database procedures for manipulating the model so it stays consistent. Postgres is (kinda, not very) heavy for small projects but it scales for medium up to large-ish projects (where it still scales but not as trivially). One downside I've seen in this project is that some developers were afraid to change the SQL procedures so they started to work around them instead of adding new ones or changing the existing ones. So in addition to your regular work horse programming language you also have to be pretty good at SQL.
> "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!"
This is one of the categories of opinions that I’ve heard, the proponents of which suggest that databases will typically be more efficient at querying and transforming data, since you’ll only need to transfer the end result over a network and will often avoid the N+1 problem altogether.
You probably don’t want some reporting or dashboard functionality in your app to have to pull tens or hundreds of thousands of rows to the back end, just because you have decided to iterate over the dataset and do some transformations there.
That said, I’ve worked in an app where the Java back end only called various stored procedures and displayed their results in tables and while it was blazingly fast, the developer experience was miserable compared to most other projects I’ve worked with - lots of tables with bad naming (symbol limits in that RDBMS to thank), badly commented (not) procedures with obscure flags, no way to step through anything with a debugger, no proper logging, no versioning or good CI tooling, no good tools for code navigation, no refactoring suggestions, no good tracing or metrics, nothing.
Sure, it might have just been a bad codebase, but it was worse than most of the ones where too much logic is in the back end, those just run badly, so I get the other category of opinions, which suggests that trying to use the DB for everything isn’t a walk in the park either.
There’s probably a good balance to be found and using tools in ways that both perform okay and don’t make the developer experience all that bad.
Ofc I reserve the right to be wrong, just wanted to share my subjective experience, that there can be tradeoffs and there probably aren't any silver bullets.
For the most part, I think that you should put any mass/batch processing in the DB (just comment/version/test/deploy your code like you would on the back end, as best as you can with the tools available to you) and don't sweat too much about handling the CRUD operations in your back end, through whatever ORM you use or don't use (regular queries are also fine, as long as parametrized to prevent injection).
For complex schemas, a nice approach I've found is making one DB view per table/list/section of your front end, so you only need 1 DB call to load a particular component, otherwise the N+1 risk gets far greater ("Oh hey, I got this list of orders, but each other needs a delivery status, so I'll just iterate over those and fetch them for each item, whoops, the DB is spammed with requests.").
Stored procedures suck. First of all SQL is a dubious language to write business logic in because it doesn’t have static typing and other goodies we expect nowadays. But more importantly stored procedures tend to drift out of version control. So please don’t.
Still a lot more complicated to deal with in every aspect, obviously so to anyone who has long term experience with them, what are you gaining from pretending otherwise?
Great list of Postgres features called out that highlight the extensive feature set.
Most of these are covered in my book, for anyone that’s interested in learning them. The book uses a Ruby on Rails app with Postgres instances for examples and exercises. Hope the plug is ok here as some folks may be looking for learning resources for Postgres.
https://andyatkinson.com/pgrailsbook
You should use the most powerful and convenient language you have at your disposal, which is most likely the host language.
ORMs are funny things, it's like we got stuck in the idea of making the database object oriented. MongoDB just means we don't have to pretend anymore, not that it was a good idea.
It is perfectly possible to use relational concepts in a general purpose language. Tables, Columns, Foreign Keys, Records, Indexes, Queries etc. And you can build whatever Model abstractions you need on top of that; or not, for simple CRUD you don't really need a type system.
I usually build that layer along with the foundation of the application, it still evolves slightly every time around but the basics are very tried and proven by now.
> 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).
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.
> 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.
sure. 1. sqlite can have more than 1 file when using wal mode. 2. You don't need to know your exact dyanmodb access patterns upfront, you can evolve the schema. again, not worth effort to point out more.
DynamoDb is not a distributed hash map, it is a distributed forest of B-Trees. And B-Trees are what PG uses for indices and MySQL for both tables and indices. You don’t demoralize with it, but rather you build and maintain table and index like structures, they would contain submitted of ids or other data fields but it is the same with normal DB indexes. The difference if you have to maintain them manually. The upside is scale, serverlessnes and maybe less latency.
I believe what the author has in mind is the fact that you need to create your LSI at the same time as you create the table, you cannot add them later (until GSI). So there's some truth to what they are saying regarding access patterns.
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.
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.
Depends on your workload, immutable tuples and vacuum can hurt a lot. (Although that got much better recently) Also the mad decisions about the query planner which you can't control. Often this doesn't matter, but it's worth being aware.
Oh the contortions I've gone through with the query planner to get it not to do things.
Also, if you're using JSONB, long strings or other toast entries, your query plan and your performance will be wildly divorced since the planner doesn't factor in a lot of the toast IO and associated memory management. The lesson for others here is if you have JSONB/long text fields, store them in their own table.
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.
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.
> Especially as a fresh user. It's how we learn. From both good and bad experiences.
I'd do that, but a superior strategy is letting other people make mistakes and then learning from them. It is best to always be making choices that seem like they could be optimal, with very rare exceptions.
> If this was a superior strategy that was so obvious no 1 would be making mistakes so how does this work?
Either people aren't aware of an optimal strategy (if one exists) or they ignore it for various reason.
The latter is surprisingly common. People know they should exercise, get enough sleep, eat healthy, stay hydrated, tackle high priority tasks instead of procrastinating, etc. - and yet they still aren't doing those things (or as much as they should).
> Either people aren't aware of an optimal strategy (if one exists) or they ignore it for various reason.
And my point was precisely that it often doesn't exist. What is an optimal strategy?
> People know they should exercise, get enough sleep, eat healthy, stay hydrated, tackle high priority tasks instead of procrastinating, etc. - and yet they still aren't doing those things (or as much as they should).
> Knowing something is not enough.
Your example doesn't even support this. People know they have to get enough sleep but they also know they have to <insert something else>. It depends on what they are optimizing for. i.e. there is no singular optimal strategy.
You've just proved my point rather than yours.
I don't know if knowing is not enough, but clearly the people in your example don't know what the optimal strategy is. E.g. eating healthy is NOT the optimal strategy as it could make them unhappy (e.g. don't like the taste). It's not optimal unless it's strictly better.
> If this was a superior strategy that was so obvious no 1 would be making mistakes so how does this work?
I agree with duckmysick, and also please take note that having a strategy of not making mistakes will not avoid all mistakes. Outcomes and intent never match up perfectly. But that is why it is important to learn from others right from the start.
I agree. But unless you are at least tangentially involved, you are likely not to hear such „gone bad“ stories.
First, people are not nearly as self reflective or admit to failures at all. And second, a bad tech decision might not be directly observable (as you will then often see ppl fighting symptoms rather than change and identify the root cause).
MongoDB salesdroids rely heavily on you having a low familiarity with other database tech to spin themselves as the only game in town. "Being led astray" isn't a passive, ambient occurence, and it makes sense to push back against it.
On the other hand I know of multiple databases where all tables had attribute_1, attribute_2,..., attribute_5 columns Just in Case™
But more seriously the one feature I like in MongoDB is the pipeline API, where you can express a complex query with multiple filters/aggregations/transformations/joins as a list of simple steps.
There are some use cases where it is very ergonomic (even if I suspect that mongo can easily lose indexes along the steps so pretty performance might not be super intuitive)
I forgot about what I hate about mongo, so I will rant here about it: its data model is close enough but different enough from json to be both annoying and dangerous.
The empty string is a valid json key but not a mongo document key.
Mongo uses $operator keys to serialize its datatypes to json but does not sanitise the result: which means that {"foo":100000000000000000} and {"foo":{"$longInteger":"100000000000000000"}} will have a collision with the json export format. (Even if you choose the fully explicit Canonical format as there is no $document operator to wrap ambiguous documents)
So if your plan is to dump json to mongo you should plan for that (also sometimes $operators are evaluated sometimes they are not, it depends on each method and the documentation does not tell you )
The official client (both csv and json) is unable to export a collection if a field is both a value field both an atomic value and an object, so a collection with two documents:
{a:1} and {a:{b:1}} will cause problems of you try to export it.
My colleagues have other issues with the json DSL and how most operators exist in 2-3 different forms with different syntax or how the syntax {$operator:{arg1:..., arg2:...}} is unintuitive but I actually sort of like it.
Maybe it's just me, but I think it's purely rational?
A lot of these OSS projects provided a commercial offering in the form of SaaS. Yet AWS/GCP/Azure can just take the OSS project, not contribute anything, and reap all the profit.
AFAICS, these licenses are only intended to defend against the cloud providers, not against companies just using the product commercially and internally.
Amazon, Google, and Microsoft can do anything, snuffing out some mid company is the least of their crimes.
> not contribute anything, and reap all the profit
Yeah, it's called capitalism.
What's their stance on Lina Khan breaking up the monopolies? Have they written letters criticising Reid Hoffman for pressuring Kamala?
Fully agree. At that level, the justification for using MongoDB usually boils down to not wanting to deal with table schemas or SQL. In both cases, there are better alternatives.
I’ve heard stuff like this from supposedly senior people - if we use mongo we can just store anything, we don’t need to think about a schema (also you can only store short strings in an SQL database)
But typically, if you do spend some time to think about schema, and relationships, you only have to spend a small amount of time, and map that into the appropriate place, and then usually you don't have to mess with it again. It's worth the investment.
In most cases you're not even removing the concept of the schemas, your just moving them from your database to your application. Which in a sense is worse, especially if multiple applications needs to access the same database.
As for SQL, I have yet to see something solve the problem for querying a database in a simpler manor.
I'll give you one counter-argument, though: maybe it's hard to appreciate all of the problems a traditional SQL RDBMS solves until you try and solve them without an RDBMS... and crash and burn badly.
But, if they're actually building a real product with real funding money and they only know MongoDB... yeah, it's intervention time.
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.
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?
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”.
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.
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.
> 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.
> 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.
Go with Postgres. SQLite is somewhat barebones, you are getting like 5 datatypes grand total with it (counting null as separate type), 1 type of indexes, somewhat unexplored tooling around it. Also column type is not strict and you can write strings into integer column. With Postgres you are getting very established and rich ecosystem, with various and VERY optimized data types, indexes, extensions (like postgis), tooling around it, lots of tutorials how to fix it if something goes wrong.
P.S. but if your project don't need any of that, e.g. it's desktop audio player, just embed sqlite - remove another subsystem to care about
Postgres isn't hard to use, but it requires maintenance. You need more scripts, more tooling, more knowledge of DBA, and that may not be necessary. When you're using a database to store a few thousand rows of data, Postgres quickly becomes overkill. Postgres is a V8 engine when all you may need to power is a lawn mower.
I personally prefer to use abstractions like ORMs for most of my database interactions, and direct SQL when those abstractions get in the way (by generating expensive queries and not finding an easy fix without a large refactor).
This way, starting out with sqlite (good enough for most websites I reckon, easy to backup) doesn't interfere with any necessary migration to postgres (like when the need for scaling arises). This also makes setting up tests easier (except for the manually written SQL) because starting an application with a temporary in-memory database is a lot faster than starting a full container.
Unless I'm doing native apps, I'll probably always want to reserve the ability to use Postgres. Sometimes that means hooking up a Postgres account and such, but often that just means sticking with sqlite and leaving my options open for when sqlite doesn't work anymore.
> Postgres isn't hard to use, but it requires maintenance. You need more scripts, more tooling, more knowledge of DBA, and that may not be necessary.
I don't think Postgres needs to be maintained at all for small databases, which is usually the use case for SQLite. Their default configurations would take care of most things for trivial applications.
> Starting an application with a temporary in-memory database is a lot faster than starting a full container.
Starting a container might be way slower than SQLite, but I would still consider it fast for most, if not all use cases.
> hooking up a Postgres account
You can configure Postgres to start up in trust mode, which doesn't require a password for any user. This is basically the same as the unencrypted SQLite database file but with a fixed connection string: `postgresql://postgres@localhost`
I would use SQLite if the embeddable part makes things considerably easier. For example, in a desktop or mobile application where a single application process is easier, because it fits the deployment model better.
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.
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.
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.
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...
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-fi...
* 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.
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
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
237 comments
[ 4.4 ms ] story [ 260 ms ] threadIt 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.
(I work on PGlite)
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.
And you don't have to run Docker to run Postgres, it's just an easy way to do so. Even airgapped.
I haven't worked with banks before, genuinely curious, how do they recover from something like this? Wouldn't this potentially destroy all transactions made in that time period?
In all cases, rubbish customer experience.
No reason you can't, but one you to consider if you should. If you're using libraries which make assumptions about your database layer, they may not like the sqlite model. Holding the writer mode for too long is something I experienced in write a few projects. For example paperless-ngx will block the web interface while batch importing documents, even though there's really no reason to do that.
It's less of an issue if you write all your own code and you explicitly target sqlite. But worth keeping in mind.
Using SQLite locks you out of some platforms as a service and out of some application architectures. It also means you need to be doing stuff like snapshotting your VM every x minutes.
Given minimal certainty about the scale, future, and properties of your application and organization: Postgres is the better default.
- 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.
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.
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.
It's more like besides the point. Everything in Linux is "just a file".
I really don't see any cons with Postgres over SQLite for server applications.
That's not true. Postgres is another standalone process, SQLite is a library. Even if you have your service and Postgres on the same box, you need to account for yet another process that can independently go down, that is competing for resources etc...
Of course you need to maintain postgres.
Major version upgrades are not automatic, you can't just install a newer binary/library version and start it as you can for SQLite. You need to shut down the DB and run `pg_upgrade`, or write manual full export-import scripts with `pg_dump`/`pg_dumpall`/`pg_restore`/`psql`.
And good luck deciding between the different format options, as some of them are unsupported across some of these tools, some cannot export and reimport the full database cluster, there's no idempotent "just import this snapshot" operation (point-in-time restore), lack of progress reporting, etc.
Here are some notes I on the topic:
Why isn't there a mode with which I can just tell postgres to migrate my data automatically upon startup with a newer version?And why can't I just have postgres-as-a-library to link into my binary, like I can do with SQLite?
You also can't just run postgres as root (e.g. in a container), and have to set up UNIX users to work around that, because postgres has it hardcoded to avoid running as root. This, too, you don't need to do with SQLite.
Also, postgres is harder to secure.
You need to either use TCP and ensure that other UNIX users on the same system can't just connect, or use UNIX Domain Sockets which have a 108 char path length restriction [1] (which is of course not documented in postgres's docs [2]), so it will suddenly break your CI when its path changes from
to And then you need to tell people to use shorter branch name "because otherwise the DB doens't work".Postgres is still my DB of choice, but it would be very misleading to say that it needs no maintenance and just works.
[1]: https://serverfault.com/questions/641347/check-if-a-path-exc...
[2]: VWWHFSfQ ↗ > 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 rcarmo ↗ The fact that you use Postgres-specific features extensively is a design decision that many people would never make, regardless of their trust in the engine. VWWHFSfQ ↗ That's like saying I use Rust but I don't want to use any Rust specific features because I might want to port it to Python someday. rcarmo ↗ In the enterprise space, it isn't. You often have to build software that will run on different database back-ends. Just because your worldview doesn't align with other people's doesn't mean you own the truth... [deleted] ↗ (comment deleted) sampullman ↗ I don't think people often switch from Postgres to SQLite, it's probably more common (and much easier) to prototype with SQLite lite first and then switch. mnahkies ↗ Lately I've been using sqlite in this way on small projects I'm just hacking at, but after seeing pglite this week (https://news.ycombinator.com/item?id=41224689) I'll probably give that a try next time [dead] xcdzvyn ↗ [dead] rmsaksida ↗ IMO a downside of SQLite that isn't discussed as often as it should be is the poor support for some table operations like ALTER COLUMN. Need to change a column to null / not null? Drop a foreign key constraint? Tough luck, in some cases the only way to implement a change is recreating the table.
I'll never understand this idea that Postgres and SQLite are somehow interchangeable when the time is right.
My database and Postgres are _literally_ the core definition of everything that my application does. My app is written in Rust, but that doesn't matter because it's a _Postgres_ application. I use Postgres-specific features extensively. Converting the application to SQLite would be essentially a re-write, and it would be worse in every way.
Also, I generally just don't understand this fad of running production backends on SQLite. SQLite is great for what it is, a tiny little embeddable client-side database. But it is a _terrible_ database for non trivial business applications where ref integrity, real types instead of "everything is a string", and battle-tested scaling is essential.
It's complete nonsense.
If by referential integrity you just mean FK constraints, you can turn that on in sqlite3.
I think SQLite is pretty good for a lot of use cases. An Axum/sqlite CRUD app should be able to handle at least few hundred requests per second on a medium powered box, which is good enough for a lot of things.
Postgres is really powerful but I don't think it's actually that common to structure your app around it's unique features.
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.
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.
If there's one complaint I have about pg, it's that it has too many features that encourage finding cute, non standard, non obvious ways of going about things.
Why? To make migration to another database easier? I've never had the need to migrate any application away from postgres. I usually take full advantage of what the database can do.
But data ownership is the one place I get iffy. What if your db does a rug pull and changes licenses? There’s certainly precedent in this space for that.
"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!"
From my courses I had at university, I've been led to believe that the current trend is doing hexagonal architecture, as that allows for better modularisation of the project and helps keep code clean over many years with many software engineers coming in and out. As a part of that I've been taught that the only part you could trust then is your internal modules - and even database has to treated as an external source, whose only job is to pull data in and out. How does that work in what you're suggesting? Is it just a different way of approaching things that will work depending on what's your goal is?
I'm just curious about this as I'm trying to get myself to learn a bit more, just to clarify
You can certainly use the database as a "dumb storage" tool in the hexagonal architecture, that is, as just another adapter. But most of the time you'll end up re-creating RDMS features in poorly written/documented application code that has to interact with the database anyways. Why not just do it all in the database? With a RDMS core, hexagonal adapters can be pure functional components, making them much easier to reason about and maintain.
For more on this idea, and how to avoid pitfalls with the hexagonal pattern, I recommend reading Out of the Tar Pit [1]. It's a short but highly influential paper on "functional relational programming".
[1]: https://curtclifton.net/papers/MoseleyMarks06a.pdf
The first job of a database is to be a data structure for persisting data, but you're allowed to extend said data structure in your own code. As long as you can come up with a way to keep all the code in version control, test it, etc., it's fine.
You gain: Model consistency guaranteed by the database, your backend basically only acts as an external API for the database.
You lose: Modularity, makes it harder to swap out databases. Also, you have to write SQL for business logic which many developers are bad at or dislike or both.
I've seen a system running on this approach for ten years and it survived three generations of developers programming against this API. There's Python wx frontends, web frontends, Rust software, Java software, C software, etc. They all use the same database procedures for manipulating the model so it stays consistent. Postgres is (kinda, not very) heavy for small projects but it scales for medium up to large-ish projects (where it still scales but not as trivially). One downside I've seen in this project is that some developers were afraid to change the SQL procedures so they started to work around them instead of adding new ones or changing the existing ones. So in addition to your regular work horse programming language you also have to be pretty good at SQL.
This is one of the categories of opinions that I’ve heard, the proponents of which suggest that databases will typically be more efficient at querying and transforming data, since you’ll only need to transfer the end result over a network and will often avoid the N+1 problem altogether.
You probably don’t want some reporting or dashboard functionality in your app to have to pull tens or hundreds of thousands of rows to the back end, just because you have decided to iterate over the dataset and do some transformations there.
That said, I’ve worked in an app where the Java back end only called various stored procedures and displayed their results in tables and while it was blazingly fast, the developer experience was miserable compared to most other projects I’ve worked with - lots of tables with bad naming (symbol limits in that RDBMS to thank), badly commented (not) procedures with obscure flags, no way to step through anything with a debugger, no proper logging, no versioning or good CI tooling, no good tools for code navigation, no refactoring suggestions, no good tracing or metrics, nothing.
Sure, it might have just been a bad codebase, but it was worse than most of the ones where too much logic is in the back end, those just run badly, so I get the other category of opinions, which suggests that trying to use the DB for everything isn’t a walk in the park either.
There’s probably a good balance to be found and using tools in ways that both perform okay and don’t make the developer experience all that bad.
For the most part, I think that you should put any mass/batch processing in the DB (just comment/version/test/deploy your code like you would on the back end, as best as you can with the tools available to you) and don't sweat too much about handling the CRUD operations in your back end, through whatever ORM you use or don't use (regular queries are also fine, as long as parametrized to prevent injection).
For complex schemas, a nice approach I've found is making one DB view per table/list/section of your front end, so you only need 1 DB call to load a particular component, otherwise the N+1 risk gets far greater ("Oh hey, I got this list of orders, but each other needs a delivery status, so I'll just iterate over those and fetch them for each item, whoops, the DB is spammed with requests.").
Good luck!
No, not rubbish
Portability matters. Lockin sucks
Old wives tale.
Modern IDEs are very good at version control over all database elements.
https://www.jetbrains.com/help/datagrip/databases-in-the-ver...
Most of these are covered in my book, for anyone that’s interested in learning them. The book uses a Ruby on Rails app with Postgres instances for examples and exercises. Hope the plug is ok here as some folks may be looking for learning resources for Postgres. https://andyatkinson.com/pgrailsbook
ORMs are funny things, it's like we got stuck in the idea of making the database object oriented. MongoDB just means we don't have to pretend anymore, not that it was a good idea.
It is perfectly possible to use relational concepts in a general purpose language. Tables, Columns, Foreign Keys, Records, Indexes, Queries etc. And you can build whatever Model abstractions you need on top of that; or not, for simple CRUD you don't really need a type system.
I usually build that layer along with the foundation of the application, it still evolves slightly every time around but the basics are very tried and proven by now.
https://github.com/codr7/hostr/tree/main/src/Hostr/DB
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.
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).
TFA is also implying that it's either Datomic or Postgres but you can use Datomic on top of Postgres.
[1] Ok, just one, Rick Houlihan is currently at MongoDB.
[2] https://rfd.shared.oxide.computer/rfd/53
Not according to the YT video.
AWS in 2018
I did not detect technical flaws in the article. I thought it was very good
That is not what is called a “technical flaw“.
Would you believe I don't have an editor?
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.
Man people have no clue what they’re talking about lol
js is more associated with Mongo, another bad db. Most modern js projects (or any modern project really, except PHP) use Postgres
does that claim do anything for anyone? India is the most populated country on earth, so?
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.
I never understood back then why MySQL was the default choice for so many people.
Also, if you're using JSONB, long strings or other toast entries, your query plan and your performance will be wildly divorced since the planner doesn't factor in a lot of the toast IO and associated memory management. The lesson for others here is if you have JSONB/long text fields, store them in their own table.
I like this sentence way more than I should.
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.
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).
[1]: https://boringtechnology.club/
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.
I'd do that, but a superior strategy is letting other people make mistakes and then learning from them. It is best to always be making choices that seem like they could be optimal, with very rare exceptions.
If this was a superior strategy that was so obvious no 1 would be making mistakes so how does this work? Not everything is strictly better.
And by that logic...
>> the superior strategy is letting other people comment (and make mistakes) and then learn from them
i.e. don't read the post until years later to ensure you have all the mistakes and learnings. Sorry, this thread is still live.
Either people aren't aware of an optimal strategy (if one exists) or they ignore it for various reason.
The latter is surprisingly common. People know they should exercise, get enough sleep, eat healthy, stay hydrated, tackle high priority tasks instead of procrastinating, etc. - and yet they still aren't doing those things (or as much as they should).
Knowing something is not enough.
And my point was precisely that it often doesn't exist. What is an optimal strategy?
> People know they should exercise, get enough sleep, eat healthy, stay hydrated, tackle high priority tasks instead of procrastinating, etc. - and yet they still aren't doing those things (or as much as they should). > Knowing something is not enough.
Your example doesn't even support this. People know they have to get enough sleep but they also know they have to <insert something else>. It depends on what they are optimizing for. i.e. there is no singular optimal strategy.
You've just proved my point rather than yours.
I don't know if knowing is not enough, but clearly the people in your example don't know what the optimal strategy is. E.g. eating healthy is NOT the optimal strategy as it could make them unhappy (e.g. don't like the taste). It's not optimal unless it's strictly better.
I agree with duckmysick, and also please take note that having a strategy of not making mistakes will not avoid all mistakes. Outcomes and intent never match up perfectly. But that is why it is important to learn from others right from the start.
It's important if you know what you're learning but...
What do you even agree with? i.e. what's the learning?
>> duckmysick claims people ignore the optimal strategy but could not even give an example of 1.
This proves it's better to try it yourself than to assume you're learning and make even worse mistakes.
> and also please take note that having a strategy of not making mistakes will not avoid all mistakes
Where is the strategy to begin with? 0 + 0 was 0 to begin with.
First, people are not nearly as self reflective or admit to failures at all. And second, a bad tech decision might not be directly observable (as you will then often see ppl fighting symptoms rather than change and identify the root cause).
But more seriously the one feature I like in MongoDB is the pipeline API, where you can express a complex query with multiple filters/aggregations/transformations/joins as a list of simple steps.
There are some use cases where it is very ergonomic (even if I suspect that mongo can easily lose indexes along the steps so pretty performance might not be super intuitive)
The empty string is a valid json key but not a mongo document key.
Mongo uses $operator keys to serialize its datatypes to json but does not sanitise the result: which means that {"foo":100000000000000000} and {"foo":{"$longInteger":"100000000000000000"}} will have a collision with the json export format. (Even if you choose the fully explicit Canonical format as there is no $document operator to wrap ambiguous documents)
So if your plan is to dump json to mongo you should plan for that (also sometimes $operators are evaluated sometimes they are not, it depends on each method and the documentation does not tell you )
The official client (both csv and json) is unable to export a collection if a field is both a value field both an atomic value and an object, so a collection with two documents: {a:1} and {a:{b:1}} will cause problems of you try to export it.
My colleagues have other issues with the json DSL and how most operators exist in 2-3 different forms with different syntax or how the syntax {$operator:{arg1:..., arg2:...}} is unintuitive but I actually sort of like it.
[1] https://news.ycombinator.com/item?id=41273563
A lot of these OSS projects provided a commercial offering in the form of SaaS. Yet AWS/GCP/Azure can just take the OSS project, not contribute anything, and reap all the profit.
AFAICS, these licenses are only intended to defend against the cloud providers, not against companies just using the product commercially and internally.
> not contribute anything, and reap all the profit
Yeah, it's called capitalism. What's their stance on Lina Khan breaking up the monopolies? Have they written letters criticising Reid Hoffman for pressuring Kamala?
>Good programmers worry about data structures and their relationships
https://news.ycombinator.com/item?id=41268803
In most cases you're not even removing the concept of the schemas, your just moving them from your database to your application. Which in a sense is worse, especially if multiple applications needs to access the same database.
As for SQL, I have yet to see something solve the problem for querying a database in a simpler manor.
I'll give you one counter-argument, though: maybe it's hard to appreciate all of the problems a traditional SQL RDBMS solves until you try and solve them without an RDBMS... and crash and burn badly.
But, if they're actually building a real product with real funding money and they only know MongoDB... yeah, it's intervention time.
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.
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
Even freecodecamp who is excellent, does this.
They have a rel-db course https://www.freecodecamp.org/learn/relational-database/ but their backend course uses mongodb https://www.freecodecamp.org/learn/back-end-development-and-...
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”.
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.
2. Like the author, I will like to understand "Why not MariaDB? (a free variant of MySql)".
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.
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.
P.S. but if your project don't need any of that, e.g. it's desktop audio player, just embed sqlite - remove another subsystem to care about
FYI, as of late 2021, you can opt into strictness.
https://sqlite.org/stricttables.html
I personally prefer to use abstractions like ORMs for most of my database interactions, and direct SQL when those abstractions get in the way (by generating expensive queries and not finding an easy fix without a large refactor).
This way, starting out with sqlite (good enough for most websites I reckon, easy to backup) doesn't interfere with any necessary migration to postgres (like when the need for scaling arises). This also makes setting up tests easier (except for the manually written SQL) because starting an application with a temporary in-memory database is a lot faster than starting a full container.
Unless I'm doing native apps, I'll probably always want to reserve the ability to use Postgres. Sometimes that means hooking up a Postgres account and such, but often that just means sticking with sqlite and leaving my options open for when sqlite doesn't work anymore.
I don't think Postgres needs to be maintained at all for small databases, which is usually the use case for SQLite. Their default configurations would take care of most things for trivial applications.
> Starting an application with a temporary in-memory database is a lot faster than starting a full container.
Starting a container might be way slower than SQLite, but I would still consider it fast for most, if not all use cases.
> hooking up a Postgres account
You can configure Postgres to start up in trust mode, which doesn't require a password for any user. This is basically the same as the unencrypted SQLite database file but with a fixed connection string: `postgresql://postgres@localhost`
Literally
If you are not storing much data no datase manager is th best
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.
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...
Just added it to my "Postgres Is Enough" gist: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
* 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.
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