4 ms·
A thoroughly good article. It's probably worth also considering adding a CDN if you take this approach at scale. You get to use their WAF and DNS failover. A b
by matt-p 1y ago
A thoroughly good article. It's probably worth also considering adding a CDN if you take this approach at scale. You get to use their WAF and DNS failover.
A big pain point that I personally don't love is that this non-cloud approach normally means running my own database. It's worth considering a provider who also provides cloud databases.
If you go for an 'active/passive' setup, consider saving even more money by using a cloud VM with auto scaling for the 'passive' part.
In terms of pricing the deals available these days on servers are amazing you can get 4GB RAM VPSs with decent CPU and bandwidth for ~$6 or bare metal for ~$90 for 32GB RAM quad core worth using sites like serversearcher.com to compare.
- andersmurphy 1y agoIf you're running on a single machine then you'll get way more performance with something like sqlite (instead of postgres/MySQL) which also makes managing the database quite trivial.
- immibis 1y agoSQLite has serious concurrency concerns which have to be evaluated. You should consider running postgres or mysql/mariadb even if it's on the same server. SQLite uses one reader/writer lock over the whole database. When any thread is writing the database, no other thread is reading it. If one thread is waiting to write, new reads can't begin. Additionally, every read transaction starts by checking if the database has changed since last time, and then re-loading a bunch of caches. This is suitable for SQLite's intended use case. It's most likely not suitable for a server with 256 hardware threads and a 50Gbps network card. You need proper transaction and concurrency control for heavy workloads. Additionally, SQLite lacks a bunch of integrity checks, like data types and various kinds of constraints. And things like materialised views, etc. SQLite is lite. Use it for lite things, not hevy things.
- hruk 1y agoAgree on many things here, but SQLite does support WAL mode which supports 1 writer/N writer readers with snapshot isolation on reads. Writes are serialized but still quite fast. SQLite (actually SQL-ite, like a mineral) maybe be light, but so are many workloads these days. Even 1000 queries per second is quite doable with SQLite and modest hardware, and I've worked at billion dollar businesses handling fewer queries than that.
- Rohansi 1y agoIs any SQL database suitable for 50GBps of network traffic hitting it? Most if not all of your concerns with SQLite are simply a matter of not using the default configuration. Enable WAL mode, enable strict mode, etc. and it's a lot better.
- andersmurphy 1y agoNot sure what you are talking about? In WAL mode (which is what you should be using) writes don't block reads and reads don't block writes. If you are connections pooling (which you should) the cache will stay hot. Sqlite (properly configured) will outperform "proper databases" often by an order of magnitude in the context of a single box. You want a single writer for high performance as it lets you batch. > 256 hardware threads... Have you tried? I have. Others have too. [1] > Additionally, SQLite lacks a bunch of integrity checks, like data types and various kinds of constraints. And things like materialised views, etc. Sqlite has blobs so you can use your own custom encoding which is what you want in a high performance context. Here's sqlite on a 5$ shared VPS that can handle 10000+ checks per second over a billion checkboxes [2]. You're gonna be fine. - [1] https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a-single-server https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a... - [2] https://checkboxes.andersmurphy.com https://checkboxes.andersmurphy.com
- wild_egg 1y agoYou know, it's ok to say that you're out of your element and don't have direct experience with the thing you're commenting on. SQLite is easily the best scaling DB tech I've used. I've moved all my postgres workloads over to it and the gains have been incredible. It's not a panacea and not the best in all cases but it's a very sane default that I recommend everyone start with and only complicate their stack with an external DB when they they start hitting real limits (often never happens)
- immibis 1y ago> You know, it's ok to say that you're out of your element and don't have direct experience with the thing you're commenting on. I moved several projects from sqlite to postgres because sqlite didn't scale enough for any of them.
- andersmurphy 1y agoMay I suggest you could have been holding it wrong? The out of the box defaults for sqlite are terrible for web apps.
- immibis 1y agoAre you aware of the irony behind saying "You're holding it wrong"? Do you know where that phrase came from?
- rixed 1y agoIf you have a single request at a time and need little integrity checks.
- railorsi 1y agoWhat’s the issue with running Postgres inside a docker container + regular backups? Never had problem and relatively easy to manage.
- matt-p 1y agono PITB, but mostly just 'it's hassle' for the application server I literally don't need backups, just automated provisioning/docker container etc. Adding postgres then means I need full backups including PITB because I don't even want to lose an hours data.
- ozozozd 1y agoOr use SQLite and your backups are literally a copy of a file. You can abuse git for it if you really want to cut corners.
- vanviegen 1y agoOnly if you can freeze your application for that long, in which case your statement is true for all non-broken databases.
- wild_egg 1y agoIt only freezes your application if you've misconfigured it.
- vanviegen 1y agoIf you want to backup your database using just a file copy, you'd better freeze your database if you value your data. Or use a fancy snapshotting filesystem.
- markusw 1y agoYou can easily do consistent backup on live databases. There’s a backup command and API.