6 ms·

These days, for a new Web2 type startup, I would use SQLite. Because it requires no setup and has been used to scale typcial Web2 applications to millions in r

• mandeepj 4y ago
  Exactly! no separate machine required just for db. You can also backup SQLite db asynchronously
• senttoschool 4y ago
  You can get a managed Postgres instance for $15/month these days. Likely cheaper, more secure, faster, better than an SQLite db hosted manually on a VM. >Because it requires no setup and has been used to scale typcial Web2 applications to millions in revenue on a single cheap VM. Plenty of setup. How would you secure your VM? SSL configuration? SSL expiration management? Security? How would you scale your VM to millions without downtime? Backups? Restoring backups? All these problems are solved with managed database hosting.
  • throwaway0x7E6 4y ago
    my brother in Christ, SQLite is not a RDBMS. it runs on the same machine as your application. the scary, overwhelmingly complex problems you've enumerated should indeed be left to competent professionals to deal with, but they do not affect SQLite.
    • geocar 4y ago
      > the scary, overwhelmingly complex problems you've enumerated should indeed be left to competent professionals to deal with You also aren’t getting that for $15/mo, either.
      • senttoschool 4y ago
        https://www.digitalocean.com/pricing/managed-databases https://www.digitalocean.com/pricing/managed-databases $15/month for Postgres managed hosting. I would consider DO as competent professionals.
        • geocar 4y ago
          And are they going to deal with that scary stuff for me for $15/mo? Or are they going to turn it off and on and tell me I need a bigger boat if it’s slow?
          • senttoschool 4y ago
            What scary stuff are you dealing with?
    • oefrha 4y ago
      > it runs on the same machine as your application. Better, it runs in the same process.
  • zinodaur 4y ago
    I recently hopped on the SQLite train myself - I think it's a great default. If your business can possibly use sqlite, you get so much performance for free - performance that translates to a better user experience without the need to build a complicated caching layer. The argument boils down to how the relative performance costs of databases have changed in the 30 years since MySQL/Postgres were designed - and the surprising observation that a read from a fast SSD is usually faster than a network call to memcached. [1] https://fly.io/blog/all-in-on-sqlite-litestream/ https://fly.io/blog/all-in-on-sqlite-litestream/ [2] https://www.usenix.org/publications/loginonline/jurassic-cloud https://www.usenix.org/publications/loginonline/jurassic-clo...
• paldepind2 4y ago
  I sometimes see recommendations like this and I just don’t get it. It’s not like setting up a PosgreSQL databases is hard at all? And using files? That just sounds like a horrible developers experience and problems waiting to happen. Databases gives you so much for free: ACID, transactions, easy DSL for queries, structure to your data, etc. On top of that every dev knows some SQL. What am I missing?
  • deleted 4y ago
    [deleted]
  • nfhshy68 4y ago
    It's easy to install k8s curl a helm chart too, doesn't mean you should. What you're missing is complexity and second order effects. Making decisions about databases early results in needing to make a lot of secondary decisions about all sorts of things far earlier than you would if you just left it all in sqlite on one VM. It's not for everyone. People have varying levels of experience and needs. I'll almost always setup a MySQL db but I'd be lying if I said it never resulted in navel gazing and premature db/application design I didn't need.
    • hurril 4y ago
      That's a cop-out answer. You're making the claim that simply using the filesystem compared to going with Postgres somehow has less complexity and fewer (or lesser) second order effects, but you don't even indicate how come. So here's my question: how come?
      • geocar 4y ago
        > simply using the filesystem compared to going with Postgres somehow has less complexity Postgres is also using the file system, it’s just got some extra layers in-front like networking and authentication. People often refer to those “extra layers” as complexity. > how come? Why is it that more code is more stuff that can go wrong? That’s just how it is.
• oefrha 4y ago
  I love SQLite and use it whenever I can, but if you’re building the next Reddit, you obviously can’t live with the lack of concurrent writes. HN is fine as the write volume is really low, just a couple posts per minute, plus maybe ten times as many votes.
  • okasaki 4y ago
    What about one sqlite db per subreddit?
    • senttoschool 4y ago
      Where would you store the user data? Duplicate it across millions of SQLite databases?
  • mekster 4y ago
    > couple posts per minute There are hundreds of posts that people are reading at every moment. I don't think it's that less.
    • oefrha 4y ago
      You don't have to guess. Just check https://news.ycombinator.com/new https://news.ycombinator.com/new and https://news.ycombinator.com/newcomments https://news.ycombinator.com/newcomments for everything that's posted here. Or take a quantitative approach. HN item IDs are sequential. Using the API: $ curl 'https://hacker-news.firebaseio.com/v0/maxitem.json' 32409711 $ curl -s "https://hacker-news.firebaseio.com/v0/item/32409711.json" | jq .time 1660125598 $ curl -s "https://hacker-news.firebaseio.com/v0/item/$(( 32409711 - 14400 )).json" | jq .time 1660031940 so, 14400 items in ~26 hours, fewer than 10 items per minute on average. At the peak you'll see somewhat more than that.
• asdff 4y ago
  Why sqlite and not postgres? every sqlite tutorial or documentation or anything at all that I've seen says not to use this tool for a large database
  • threatripper 4y ago
    For what definition of "large"? SQLite surely has no problem holding gigabytes of data or millions of rows. In many cases it's much faster than most other kinds of databases. So, size is not the problem. Throughput on one machine with one active process on the same VM is not the problem. Having multiple processes on one VM in most cases is no problem. But if you spread your compute across many machines that access a single database it can get iffy. If you need the database accessible through network there are server extensions which use SQLite as storage format but you're dealing with a server and probably could use any other database without much difference in deployment&maintenance.
  • bdlowery 4y ago
    https://remoteok.com https://remoteok.com and https://nomadlist.com https://nomadlist.com both use SQLite as the database. 100+ million requests a month and there's been no problems.
• polote 4y ago
  Sure and I would recommend they use C to code the backend, they should not use a CDN either to save some bucks. And on the front-end there is no sense to use javascript as everything can be done in the backend
• colinmhayes 4y ago
  Using files sounds insane. 100% chance you end up with a nasty data race.
• 14u2c 4y ago
  This all sounds good until you consider high-availability, which IMO is absolutely essential for any serious business. How do you handle fail-overs when that cheap VM goes down? How do you handle regional replication? You could cobble something together with rsync, etc, but then you have added a bunch of complexity and built a custom and unproven system. Another option is to use one of the SQLLite extensions popping recently like Dqlite, but again, this approach is relatively unproven for basing your entire business on. Or you could simply use an off the shelf DBMS like Postgres or even MySQL. They already solve all of these problems and are as battle-tested as can be.
  • Skinney 4y ago
    > high-availability, which IMO is absolutely essential for any serious business Depends very much on the business. You can have downtime-free deploys on a single node, and as long as you've setup a system to automatically replace a faulty node with a new one (which typically takes < 10min) then a lot of businesses can live with that just fine. It's not like that cheap VM goes down every day, but just in case you can usually schedule a new instance every week or so to reduce the chance of that happening. > How do you handle regional replication? For backup purposes, you'd use litestream. Very easy to use with something like systemd or as a docker sidecar. For performance purposes, if you do need that performance you'd obviously use something else. Depending on the type of service you have, though, you can get very far with a CDN in front of your server. > Or you could simply use an off the shelf DBMS like Postgres or even MySQL. If you need it, sure.
• klysm 4y ago
  Completely disagree with the suggestion to just write files. The file system APIs are incredibly easy to misuse. A database with good transaction semantics and a schema is worth so much.
• Tade0 4y ago
  > These days, for a new Web2 type startup, I would use SQLite. There's a whole page in the docs explaining why this might be a bad idea for a write-heavy site: https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html Section 2 is especially interesting. SQLite locks on the whole database file - usually one per database - and that is a major bottleneck. > Another option worth taking a look at is to use no DB at all. We tried that in a project I'm currently in as a means of shipping an MVP as soon as possible. Major headache in terms of performance down the road. We still serve some effectively static stuff as files, but they're cached in memory, otherwise they would slow the whole thing down by an order of magnitude.
• _ea1k 4y ago
  TBH, I've used hypersonic sql before. People thought it was crazy, but there was no concurrency, and backups were just copying a couple of files. Fixing them if they failed was easy to. People get too caught up in assumptions without knowing the use case. There are a million use cases where a tool like sqlite would be bad, but also a million where its likely the easiest and best tool. Wisdom is knowing the difference.
• kcartlidge 4y ago
  If you're doing .Net Core you could also take a look at LiteDB. Use SQL queries in a local (optionally encrypted) file using a single embedded nuget package. Also creates schemas on the fly when you first persist collection types as per traditional NoSQL systems. I'm using it to create an embeddable out of the box passwordless auth system that has no external dependencies. Works great. Admittedly I've not tried it under extreme load yet, but I'm happy to migrate it onwards if something I create goes massive. (Edit: to be clear it is NoSQL, but you can query it using almost standard SQL which is what I do).