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.

▲

railorsi 6 days ago | parent | next [-]

What’s the issue with running Postgres inside a docker container + regular backups? Never had problem and relatively easy to manage.

▲

Biganon 5 days ago | parent | next [-]

Why use a docker container? I run Postgres as is, what would I gain with running it in a container?

	▲	Nextgrid 5 days ago \| parent [-]
		It makes the whole thing is configured in a docker-compose file (or your raw Docker CLI invocation) + the data volume. So as long as you have those two things you can replicate it and move it to other hosts regardless of their distro. Compare that with using your distro's packaged version where you can have version variations, variations in default config or file path locations, etc.

▲

matt-p 6 days ago | parent | prev [-]

no 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.

▲

doganugurlu 5 days ago | parent [-]

Or 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 5 days ago | parent [-]

Only if you can freeze your application for that long, in which case your statement is true for all non-broken databases.

▲

markusw 4 days ago | parent | next [-]

You can easily do consistent backup on live databases. There’s a backup command and API.

	▲	vanviegen 4 days ago \| parent [-]
		Sure. But then it's not "just a file" copy, like GP said.

▲

wild_egg 5 days ago | parent | prev [-]

It only freezes your application if you've misconfigured it.

	▲	vanviegen 4 days ago \| parent [-]
		If 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.

▲

andersmurphy 6 days ago | parent | prev [-]

If 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 6 days ago | parent | next [-]

SQLite 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.

▲

andersmurphy 5 days ago | parent | next [-]

Not 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...

- [2] https://checkboxes.andersmurphy.com

▲

hruk 6 days ago | parent | prev | next [-]

Agree 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.

▲

wild_egg 5 days ago | parent | prev | next [-]

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.

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 5 days ago | parent [-]

> 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 5 days ago | parent [-]

May I suggest you could have been holding it wrong?

The out of the box defaults for sqlite are terrible for web apps.

	▲	immibis 4 days ago \| parent [-]
		Are you aware of the irony behind saying "You're holding it wrong"? Do you know where that phrase came from?

▲

Rohansi 5 days ago | parent | prev [-]

Is 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.

▲

rixed 6 days ago | parent | prev [-]

If you have a single request at a time and need little integrity checks.