HN Explorer

Ask HN: What's the database you use for your simple website?

5 points by vira28 ↗ HN
There are lot of options these days.

Running your own DB (EC2/MySQL/Postgres) vs using hosted one (RDS/Aurora/etc).

I am looking for a simple dynamic website that may have 50 visitors per day max doing simple operation (CRUD)

16 comments

[ 0.25 ms ] story [ 47.1 ms ] thread
gilbertmpanga12
CloudFirestore dead simple.
vira28
Actually that's what I used at my previous projects. It is certainly good and better than the Firebase RealTime Database.

Query constraint is the biggest challenge.

vira28
I think i recently the support for "IN" queries. So let's give credit where it is due.
gigatexal
SQLite
vira28
I haven't used it at all.

Do you host it yourself? What about backup, replication, and availability?

japhyr
An sqlite database is a single file. So backing it up is as easy as copying that single file to an archive directory.
thijsvandien
Essentially true, but you need to make sure the file will be in a consistent state. Rather than locking it, it’s better to use the Backup API or VACUUM INTO command.
elamje
Your web server can easily host SQLite. It’s the only mainstream database that doesn’t need a server. It’s actually just a single file of C code, and most languages have an api binding to it so you don’t have to set anything up besides giving it a file to write to!

Backup, yes you can just copy the file to back it up, but SQLite documentation has a preferred method that uses its own backup api which is likely safer than a file copy.

Replication, I don’t think there is a way to keep multiple DB’s in sync. You will likely need to move to a client server DB like Postgres if you get to that point.

Availability, only allows one writer at a time, so that’s the biggest issue for performance. The docs say it’s used on websites that get 100k’s or hits a day, but likely not great for workloads that have a high amount of writes. You obviously will be constrained by how fast the servers CPU and I/O is.

The author of SQLite says for blobs of about 20kb or less, it’s faster than reading and writing to the file system due to the overhead of opening a file.

thijsvandien
While I’m not sure it can be considered ‘mainstream’, Firebird (www.firebirdsql.org) is well-established and can run with a server process as well as without one.

I can totally vouch for SQLite in this case, however.

meretext
Look at using SQLite. Or at least start with that, and if you 'outgrow' it, only then consider PostreSQL. PostgreSQL is the best multi-reader, multi-writer, multi-user database out there, but you don't need the extra DBA overhead of managing the database with the load you expect. I'm certain SQLite could easily handle 1,000s of concurrent writes without a lot of latency, which you can test yourself to find out what those limits are. Plus you can start prototyping with Tcl as the scripting language (or Perl, Python or Ruby, but those are not as good for this use-case -- no flaming please). Tcl can load SQLite as a library directly into the Tcl shell. Awesome. If you 'outgrow' SQLite in any way, migrating to PostgreSQL should be relatively easy. FYI - I attended the Tcl/Tk conference in early November and Richard Hipp, the SQLite creator, gave a full-day tutorial on SQLite internals. Very impressive software.
vira28
Thanks very much. Now I have to take a look at SQLite :)
elamje
SQLite is awesome! I’ve used it on a website, but if you are making a web app and want to use something like Heroku to host, it won’t work. It could be good to just use Postgres from day 1.
meretext
The thought is always start with the simplest capability you can use to meet your requirements -- PG is awesome, but it does require care and feeding by a DBA. Here's a tool to migrate from SQLite to PostgreSQL if and when necessary, but if your schema is reasonably clean it shouldn't be a hardship:

https://github.com/dimitri/pgloader

My suggestion is script what you need to test to check throughput for the number of concurrent conns you think you'll be needing and base decisions on that. FYI - I use Fossil SCM over git. It is awesome.

https://www.fossil-scm.org/home/doc/trunk/www/index.wiki

gtirloni
You don't mention your software stack but it's almost guaranteed to have good support for MySQL or PgSQL.

Re: SQLite, I haven't seen many production websites running on it but it's sure possible. Just make sure the place where you store it is reliable.

billconan
I will go mongodb or sqlite or flat files.