4 ms·
My general rule of thumb is "Use Postgres until you've discovered why you can't use Postgres." Anything you introduce is another moving part you have to operat
by psadauskas 2mo ago
My general rule of thumb is "Use Postgres until you've discovered why you can't use Postgres."
Anything you introduce is another moving part you have to operate and maintain, and in the beginning, Postgres can probably handle it. Wait for load, see where its failing, and then you'll have a better idea if adding another tool is worth the cost.
- andai 2mo agoDoesn't the same argument apply even more to using SQLite instead?
- seki285 2mo agoIn a lot of cases using SQLite means you write queries incompatible with RDBMS. No need to worry about race conditions or the amount of queries you make, when 100 selects are uber fast.
- SoftTalker 2mo agoYes, for very small or embedded, single-purpose systems sqlite is usually a good choice. It's very well tested, and there's nothing extra to run or manage. But be careful if the system starts growing beyond that, you'll want a real RDBMS before you abuse sqlite too much.
- Lio 2mo agoDepends on what you mean by "small" and "growing". If you mean database size, SQLite can handle massive amounts of data. I've seen 281 TB quoted as theoretical max size.
- pojzon 2mo agoHow does it handle 100000 write transactions per second ? From multi-client architecture and with regional HA?
- crazygringo 2mo agoNot really. They are two different paradigms. Use the one that is right for you. SQLite is embedded for local applications with one writer mostly. Postgres is for a client-server architecture with many writers. When you start a project, you generally know which architecture you need.
- andai 2mo agoSo if it needs to work offline, but it syncs with a server, then you use both? (And the schema becomes some kind of lowest common denominator?)
- preg_match 2mo agoI would probably do an event-source architecture, where you record events on the client and then push them to the server when you're reconnected. It has a lot of benefits, for instance, trivial auditing and free serialization.
- theultdev 2mo agoyeah essentially, see electricsql though they also have "pglite" running in wasm. same concept though, sync slices to an embedded db.
- LAC-Tech 2mo agoSQLite is embedded for local applications with one writer mostly. In 2026 that advice feels antiquated. SQLite now is absolutely useful now for concurrent, mutli-writer applications. https://www.sqlite.org/src/doc/wal2/doc/wal2.md https://www.sqlite.org/src/doc/wal2/doc/wal2.md
- andersmurphy 2mo agoIs it? Sqlite seems pretty fast. [1] [1] - https://andersmurphy.com/2025/12/02/100000-tps-over-a-billion-rows-the-unreasonable-effectiveness-of-sqlite.html https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...
- groundzeros2015 2mo agoNo. SQLite doesn't have users, proper views, row level security, proper foreign keys, or functions.
- bbkane 2mo agoWhat don't you like about SQLite views or foreign keys?
- groundzeros2015 2mo agoFor example you can’t insert or update a SQLite view. I don’t even think SQLite lets you fully update a table schema. If you start trying to use it for sql and not just storing rows you run into these everywhere. SQLite is not serving the same needs as Postgres.
- bbkane 2mo agoYou can use a trigger with INSTEAD OF to update a view: CREATE TRIGGER update_customer_emails_trigger INSTEAD OF UPDATE ON customer_emails BEGIN UPDATE customers SET email = new.email, name = new.name WHERE id = old.id; END; Maybe that's not as flexible as you need. SQLites process for table updates can be pretty onerous if their ALTER TABLE lacks support. Its a 12 step process the docs have the temerity to call "simple" instead of "tedious and risky" - https://www.sqlite.org/lang_altertable.html#otheralter https://www.sqlite.org/lang_altertable.html#otheralter
- groundzeros2015 2mo agoYes, friction like this. It’s just not a fully featured SQL implementation. As their saying goes, it competes with fopen not oracle.
- frollogaston 2mo agoYes. I already did that once though, so I skip that step now (unless ofc it's a SQLite usecase).
- renegat0x0 2mo agoI used this approach to drive entire app, and it works. Nearly all data are fetched from SQLite. User can select a database, which can change app views, and the data. In my experience it is quite fast. My example for android app: https://f-droid.org/pl/packages/io.github.rumcajs.offlinewebsearch/ https://f-droid.org/pl/packages/io.github.rumcajs.offlineweb... Note that I am not android experienced programmer, and I am still learning.
- boznz 2mo agoAnother rule of thumb. Use what you are comfortable with until it stops doing what you want.
- Geof25 2mo agoWell the problem is that sometimes there is just too much choice to make
- rafael-lua 2mo agoThe issue with this general rule of thumb is that we can swap Postgres for many others, including non-relational, and it works.
- notatoad 2mo agoi don't think that's an issue. it's still a perfectly good rule. use what you're familiar with, until it stops working. then use something else. postgres just goes a lot further than a lot of other tools before you get to the "use something else" phase. and postgres is the database a lot of people are familiar with.
- CodesInChaos 2mo agoMy rule is "use a single database for as many of you persistence needs as possible". Often Postgres is a good choice for that database, but many other general purpose DBMSs will work just as well. In my previous company we used MongoDB, for almost everything, including text logs, request logs, job-queues and small template files. We added S3 since storing terabytes of files in a database is expensive. Now, I wouldn't choose MongoDB for a new app, since the data model and query language suck, but it performed reasonably across many use-cases. For my next application I will use Postgres as primary database. Probably will integrate S3 before going live, to avoid the necessary data migration later, but haven't decided yet if that's a premature optimization.