Just use Postgres
mccue.dev
Just use Postgres
1–10 of 238 posts
Re: Just use Postgres
#2Postgres will do to other databases, what Linux did to other Unix(/BSD-like) operating systems (IRIX, SunOs, ...).
Re: Just use Postgres
#3There 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.
Re: Just use Postgres
#4Yup. With nothing but love for sqlite.
Re: Just use Postgres
#5The "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.
Re: Just use Postgres
#6Totally 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.
Re: Just use Postgres
#7There 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 st…
With Pglite it should now be possible to have the cake of Postgres' rich functionality and embed it too
Re: Just use Postgres
#8There 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 st…
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.
Re: Just use Postgres
#9Missing 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.
Re: Just use Postgres
#10It'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.