5 ms·

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 sou

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.
    - hurril 4y ago
      You are also missing the point and in the same way. You are at A, you want to go to C. We are debating which B to pick between: 1. a filesystem + implementing library over that to cover the interface between A and C. 2. Postgres (which is the same as above - just not invented here) Now, both of you are imagining a world where #2 somehow implies more complexity and also more second order effects than #1 would. Nobody uses all of Postgres, but the cost of not using more than 1% of what's available is hardly larger than implementing that 1% yourself for most use cases. And please don't make the misstake of thinking that I'm trying to sell Postgres over any other database product - this is all to do with the worse is better idea that so many of you cultivate.
      - geocar 4y ago
        > We are debating which B to pick between: No we are not. Postgres isn't a webserver that can handle 1m http GET requests per second; it doesn't distribute data from 30 event collectors to a reporting database without configuration. *Other stuff needs to be written to satisfy the business.* That's life. > both of you are imagining a world where #2 somehow implies more complexity and also more second order effects than #1 would. Most studies into product resolution agree that there's zero difference (from a pass/fail perspective) between writing it yourself or trying to reuse third-party applications. Here's one from some quick ddging: https://standishgroup.com/sample_research_files/CHAOSReport2015-Final.pdf https://standishgroup.com/sample_research_files/CHAOSReport2... and see the resolution of all software products surveyed (page 6). That means that if you can, it is still cheaper to build new software and test it than to try to use Postgres and scale it, because once software is written the cost is (effectively) zero, whereas the cost to support Postgres is a body -- that I might only be able to use 1% of, and that I might need to hire two so that one can go on holiday sometime, with a real salary that we need to pay. Now maybe you're working for a company whose software never gets finished. In that case, by all means use Postgres in your application, since you can share the churn on that part with every other person using Postgres in the world, but I'm going kayaking today.
- senttoschool 4y ago
  >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. Like what? What could be easier than going to AWS, click a few things to spin up a Postgres instance, click a few more things to scale automatically without downtime, create read replicas, have automatic backups, one-click restore? I feel like it's the opposite. Trying to put SQLite on a VM via Kubernetes will likely have a lot of secondary decisions that will make "scaling to millions" far harder and more expensive.
  - gjulianm 4y ago
    > Like what? What could be easier than going to AWS, click a few things to spin up a Postgres instance, click a few more things to scale automatically without downtime, create read replicas, have automatic backups, one-click restore? Not do that and not have to maintain that? Just a regular AWS instance with SQLite will be far easier to setup, with regular filesystem backup which is easier to manage and restore. > Trying to put SQLite on a VM via Kubernetes will likely have a lot of secondary decisions that will make "scaling to millions" far harder and more expensive. The issue is assuming that "scaling to millions" is a design goal from the start. For a lot of projects, the best-case-scenario does not require it. For another big set of them, they will never get to it. For the few that do, it's possible that the cost of maintaining the more complex solution while it isn't needed plus the cost of adapting over time (because, let's be honest, it's almost impossible to get the scaling solution right from the start) is more than the cost of just implementing the complex solution when it's actually needed.
    - senttoschool 4y ago
      >Not do that and not have to maintain that? Just a regular AWS instance with SQLite will be far easier to setup, with regular filesystem backup which is easier to manage and restore. You'd have less maintenance using RDS than you would with an SQLite hosted on a VM. >The issue is assuming that "scaling to millions" is a design goal from the start. I was responding to the OP who said you can scale to "millions of revenue" on SQLite. This part is true. You could. But it'd be easier to do it on a managed Postgres instance.
- ngc248 4y ago
  Choosing a DB is not premature optimization, but it eliminates a whole host of data related problems and smoothens development.
  - geocar 4y ago
    > Choosing a DB is not premature optimization That's your opinion. > it eliminates a whole host of data related problems and smoothens development I don't find this to be true, but maybe you have different problems than I do.
akamaka 4y ago
I recommend that all developers should at least once try to write a web app that uses plain files for data storage. I’ve done it, and I very quickly realized why databases exist, and not to take them for granted.
- sedatk 4y ago
  Exactly. I'd written my now popular web app (most popular Turkish social platform to date) as a Delphi CGI using text files as data store in 99 because I wanted to get it running ASAP, and I thought BDE would be quite cumbersome to get running on a remote hosting service. (Its original source that uses text files is at https://github.com/ssg/sozluk-cgi https://github.com/ssg/sozluk-cgi) I immediately started to have problems as expected, and later migrated to an Access DB. It didn't support concurrent writes, but it was an improvement beyond comprehension. Even "orders of magnitude" doesn't cut it because you get many more luxuries than performance like data-type consistency, ACID compliance, relational integrity, SQL and whatnot.
  - akamaka 4y ago
    What made my implementation disastrous turned out to be that there was no enforcement of string encodings, and users started to see all kinds of garbled text.
    - sedatk 4y ago
      One of my best decisions was to start out ASCII-only. It helped me manage that mess longer. :)
- kcartlidge 4y ago
  > write a web app that uses plain files for data storage ... very quickly realized why databases exist, and not to take them for granted You haven't lived until you've built an in-memory database with background threads persisting to JSON files. Oh, the nightmares.
rtpg 4y ago
So I am also "really people, use PostgreSQL", because you have way less tricks you have to play to get it working compared to SQLite, in serious envs. However, some challenges with Postgres, especially if you have less strict data integrity requirements (reddit-y stuff, for example): - Postgres backups are not trivial. They aren't hard, but well SQLite is just that one file - Postgres isn't really built for many-cardinal-DB setups (talking on order of 100s of DBs). What does this mean in practice? If you are setting up a multi-tenant system, you're going to quickly realize that you're paying a decent cost because your data is laid out by insertion order. Combine this with MVCC meaning that no amount of indices will give you a nice COUNT, and suddenly your 1% largest tenants will cause perf issues across the board. SQLite, meanwhile, one file per customer is not a crazy idea. You'll have to do tricks to "map/reduce" for cross-tenant stuff, of course. But your sharding story is much nicer. - PSQL upgrades are non-trivial if you don't want downtime. There's logical upgrades, but you gotta be real fucking confident (did you remember to copy over your counters? No? Enjoy your downtime on switchover). That being said, I think a lot of people see SQLite as this magical thing because of people like fly posting about it, without really grokking that the SQLite stuff that gets posted is either "you actually only have one writer" or "you will report successful writes that won't successfully commit later on". The fundamentals of databases don't stop existing with a bunch of SQLite middleware! But SQLite at least matches the conceptual model of "I run a program on a file", which is much easier to grok than the client-server based stuff in general. But PSQL has way more straightforward conflict management in general
- rahimnathwani 4y ago
  "SQLite is just that one file" How do you get enough time to back up that file when it's in use? Lock the file until you've copied it? zfs?
  - threatripper 4y ago
    They actually address this problem. https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html > The Online Backup API was created to address these concerns. The online backup API allows the contents of one database to be copied into another database file, replacing any original contents of the target database. The copy operation may be done incrementally, in which case the source database does not need to be locked for the duration of the copy, only for the brief periods of time when it is actually being read from. This allows other database users to continue without excessive delays while a backup of an online database is made.
gfody 4y ago
> What am I missing? only that hn is full, FULL of trolls who give the absolute worst advice possible. it's a sport maybe or it's entirely innocent and related to the Dunning Kruger effect but nothing brings it out more than database threads.
saagarjha 4y ago
> On top of that every dev knows some SQL. Where "some" may include "none" ;)
gjulianm 4y ago
> What am I missing? Different requirements, expertise, and priorities. I don't like generic advice too much because there are so many different situations and priorities, but I've used files, SQLite and PostgreSQL according to the priorities: - One time we needed to store some internal data from an application to compare certain things between batch launches. Yes, we could have translated form the application code to a database, but it was far easier to just dump the structure to a file in JSON, with the file uniquely named for each batch type. We didn't really need transactions, or ACID, or anything like that, and it worked well enough and, importantly, fast enough. - Another time we had a library that provided some primitives for management of an application, and needed a database to manage instance information. We went for SQLite there, as it was far easier to setup, debug and develop for. Also, far easier to deploy, because for SQLite it's just "create a file when installing this library", while for PostgreSQL it would be far more complicated. - Another situation, we needed a database to store flow data at high speed, and which needed to be queried by several monitoring/dashboarding tools. There the choice was PostgreSQL, because anything else wouldn't scale. In other words, it really depends on the situation. Saying "use PostgreSQL always" is not going to really solve anything, you need to know what to apply to each situation.
- senttoschool 4y ago
  >In other words, it really depends on the situation. Saying "use PostgreSQL always" is not going to really solve anything, you need to know what to apply to each situation. We're responding to this below. The requirement is a Web2 app with "millions in revenue". >Because it requires no setup and has been used to scale typcial Web2 applications to millions in revenue on a single cheap VM.
  - gjulianm 4y ago
    > The requirement is a Web2 app with "millions in revenue". Millions in revenue doesn't really say anything about the performance required.
    - senttoschool 4y ago
jokoon 4y ago
I built a gallery web app so I could tag images, for my own use. At first i was writing in a large json file. Each time i did a change, I would write the file and close it. It worked pretty well. I now use sqlite+dataset and the port was trivial. I guess there are better ways to do it than open(), but using dataset is quite simple. Maybe creating folders and duplicating data works, but it seems more complex than using sqlite and dataset.
0xbadcafebee 4y ago
It's "kick-the-can-down-the-road" engineering. If the server can't hold the database in RAM anymore, they buy more RAM. If the disks are too slow, they buy faster/more disks. If the one server goes down, the site is just down for an hour or two (or a day or two) while they build a new server. It works until it doesn't, and most people are fine with things eventually not working. This has only been practical within the last 6 years or so. Big servers were just too expensive. We started using cheap commodity low-resource PCs, which are cheaper to buy/scale/replace, but limits what your apps can do. Bare metal went down hard and there wasn't an API to quickly stand up a replacement. NAS/SAN was expensive. Network bandwidth was limited. The cloud has made everything cheaper and easier and faster and bigger, changing what designs are feasible. You can spin up a new instance with a 6GB database in memory in a virtual machine with network-attached storage and a gigabit pipe for like $20/month. That's crazy.
latchkey 4y ago
You aren't missing anything. Cloud SQL postgres on GCP with <50 connections is like ~$8/month and has automated daily backups. The differences in SQL syntax between SQLite and Postgres, namely around date math (last_ping_at < NOW() - INTERVAL '10 minutes'), make SQLite a non-starter imho... you're going to end up having to rewrite a lot of sql queries.

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.
  - hurril 4y ago
    You are also missing the point and in the same way. You are at A, you want to go to C. We are debating which B to pick between: 1. a filesystem + implementing library over that to cover the interface between A and C. 2. Postgres (which is the same as above - just not invented here) Now, both of you are imagining a world where #2 somehow implies more complexity and also more second order effects than #1 would. Nobody uses all of Postgres, but the cost of not using more than 1% of what's available is hardly larger than implementing that 1% yourself for most use cases. And please don't make the misstake of thinking that I'm trying to sell Postgres over any other database product - this is all to do with the worse is better idea that so many of you cultivate.
    - geocar 4y ago
      > We are debating which B to pick between: No we are not. Postgres isn't a webserver that can handle 1m http GET requests per second; it doesn't distribute data from 30 event collectors to a reporting database without configuration. *Other stuff needs to be written to satisfy the business.* That's life. > both of you are imagining a world where #2 somehow implies more complexity and also more second order effects than #1 would. Most studies into product resolution agree that there's zero difference (from a pass/fail perspective) between writing it yourself or trying to reuse third-party applications. Here's one from some quick ddging: https://standishgroup.com/sample_research_files/CHAOSReport2015-Final.pdf https://standishgroup.com/sample_research_files/CHAOSReport2... and see the resolution of all software products surveyed (page 6). That means that if you can, it is still cheaper to build new software and test it than to try to use Postgres and scale it, because once software is written the cost is (effectively) zero, whereas the cost to support Postgres is a body -- that I might only be able to use 1% of, and that I might need to hire two so that one can go on holiday sometime, with a real salary that we need to pay. Now maybe you're working for a company whose software never gets finished. In that case, by all means use Postgres in your application, since you can share the churn on that part with every other person using Postgres in the world, but I'm going kayaking today.
senttoschool 4y ago
>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. Like what? What could be easier than going to AWS, click a few things to spin up a Postgres instance, click a few more things to scale automatically without downtime, create read replicas, have automatic backups, one-click restore? I feel like it's the opposite. Trying to put SQLite on a VM via Kubernetes will likely have a lot of secondary decisions that will make "scaling to millions" far harder and more expensive.
- gjulianm 4y ago
  > Like what? What could be easier than going to AWS, click a few things to spin up a Postgres instance, click a few more things to scale automatically without downtime, create read replicas, have automatic backups, one-click restore? Not do that and not have to maintain that? Just a regular AWS instance with SQLite will be far easier to setup, with regular filesystem backup which is easier to manage and restore. > Trying to put SQLite on a VM via Kubernetes will likely have a lot of secondary decisions that will make "scaling to millions" far harder and more expensive. The issue is assuming that "scaling to millions" is a design goal from the start. For a lot of projects, the best-case-scenario does not require it. For another big set of them, they will never get to it. For the few that do, it's possible that the cost of maintaining the more complex solution while it isn't needed plus the cost of adapting over time (because, let's be honest, it's almost impossible to get the scaling solution right from the start) is more than the cost of just implementing the complex solution when it's actually needed.
  - senttoschool 4y ago
    >Not do that and not have to maintain that? Just a regular AWS instance with SQLite will be far easier to setup, with regular filesystem backup which is easier to manage and restore. You'd have less maintenance using RDS than you would with an SQLite hosted on a VM. >The issue is assuming that "scaling to millions" is a design goal from the start. I was responding to the OP who said you can scale to "millions of revenue" on SQLite. This part is true. You could. But it'd be easier to do it on a managed Postgres instance.
ngc248 4y ago
Choosing a DB is not premature optimization, but it eliminates a whole host of data related problems and smoothens development.
- geocar 4y ago
  > Choosing a DB is not premature optimization That's your opinion. > it eliminates a whole host of data related problems and smoothens development I don't find this to be true, but maybe you have different problems than I do.

akamaka 4y ago

I recommend that all developers should at least once try to write a web app that uses plain files for data storage. I’ve done it, and I very quickly realized why databases exist, and not to take them for granted.

sedatk 4y ago
Exactly. I'd written my now popular web app (most popular Turkish social platform to date) as a Delphi CGI using text files as data store in 99 because I wanted to get it running ASAP, and I thought BDE would be quite cumbersome to get running on a remote hosting service. (Its original source that uses text files is at https://github.com/ssg/sozluk-cgi https://github.com/ssg/sozluk-cgi) I immediately started to have problems as expected, and later migrated to an Access DB. It didn't support concurrent writes, but it was an improvement beyond comprehension. Even "orders of magnitude" doesn't cut it because you get many more luxuries than performance like data-type consistency, ACID compliance, relational integrity, SQL and whatnot.
- akamaka 4y ago
  What made my implementation disastrous turned out to be that there was no enforcement of string encodings, and users started to see all kinds of garbled text.
  - sedatk 4y ago
    One of my best decisions was to start out ASCII-only. It helped me manage that mess longer. :)
kcartlidge 4y ago
> write a web app that uses plain files for data storage ... very quickly realized why databases exist, and not to take them for granted You haven't lived until you've built an in-memory database with background threads persisting to JSON files. Oh, the nightmares.

rtpg 4y ago

So I am also "really people, use PostgreSQL", because you have way less tricks you have to play to get it working compared to SQLite, in serious envs. However, some challenges with Postgres, especially if you have less strict data integrity requirements (reddit-y stuff, for example): - Postgres backups are not trivial. They aren't hard, but well SQLite is just that one file - Postgres isn't really built for many-cardinal-DB setups (talking on order of 100s of DBs). What does this mean in practice? If you are setting up a multi-tenant system, you're going to quickly realize that you're paying a decent cost because your data is laid out by insertion order. Combine this with MVCC meaning that no amount of indices will give you a nice COUNT, and suddenly your 1% largest tenants will cause perf issues across the board. SQLite, meanwhile, one file per customer is not a crazy idea. You'll have to do tricks to "map/reduce" for cross-tenant stuff, of course. But your sharding story is much nicer. - PSQL upgrades are non-trivial if you don't want downtime. There's logical upgrades, but you gotta be real fucking confident (did you remember to copy over your counters? No? Enjoy your downtime on switchover). That being said, I think a lot of people see SQLite as this magical thing because of people like fly posting about it, without really grokking that the SQLite stuff that gets posted is either "you actually only have one writer" or "you will report successful writes that won't successfully commit later on". The fundamentals of databases don't stop existing with a bunch of SQLite middleware! But SQLite at least matches the conceptual model of "I run a program on a file", which is much easier to grok than the client-server based stuff in general. But PSQL has way more straightforward conflict management in general

rahimnathwani 4y ago
"SQLite is just that one file" How do you get enough time to back up that file when it's in use? Lock the file until you've copied it? zfs?
- threatripper 4y ago
  They actually address this problem. https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html > The Online Backup API was created to address these concerns. The online backup API allows the contents of one database to be copied into another database file, replacing any original contents of the target database. The copy operation may be done incrementally, in which case the source database does not need to be locked for the duration of the copy, only for the brief periods of time when it is actually being read from. This allows other database users to continue without excessive delays while a backup of an online database is made.

gfody 4y ago

> What am I missing? only that hn is full, FULL of trolls who give the absolute worst advice possible. it's a sport maybe or it's entirely innocent and related to the Dunning Kruger effect but nothing brings it out more than database threads.

saagarjha 4y ago

> On top of that every dev knows some SQL. Where "some" may include "none" ;)

gjulianm 4y ago

> What am I missing? Different requirements, expertise, and priorities. I don't like generic advice too much because there are so many different situations and priorities, but I've used files, SQLite and PostgreSQL according to the priorities: - One time we needed to store some internal data from an application to compare certain things between batch launches. Yes, we could have translated form the application code to a database, but it was far easier to just dump the structure to a file in JSON, with the file uniquely named for each batch type. We didn't really need transactions, or ACID, or anything like that, and it worked well enough and, importantly, fast enough. - Another time we had a library that provided some primitives for management of an application, and needed a database to manage instance information. We went for SQLite there, as it was far easier to setup, debug and develop for. Also, far easier to deploy, because for SQLite it's just "create a file when installing this library", while for PostgreSQL it would be far more complicated. - Another situation, we needed a database to store flow data at high speed, and which needed to be queried by several monitoring/dashboarding tools. There the choice was PostgreSQL, because anything else wouldn't scale. In other words, it really depends on the situation. Saying "use PostgreSQL always" is not going to really solve anything, you need to know what to apply to each situation.

senttoschool 4y ago
>In other words, it really depends on the situation. Saying "use PostgreSQL always" is not going to really solve anything, you need to know what to apply to each situation. We're responding to this below. The requirement is a Web2 app with "millions in revenue". >Because it requires no setup and has been used to scale typcial Web2 applications to millions in revenue on a single cheap VM.
- gjulianm 4y ago
  > The requirement is a Web2 app with "millions in revenue". Millions in revenue doesn't really say anything about the performance required.
  - senttoschool 4y ago

jokoon 4y ago

I built a gallery web app so I could tag images, for my own use. At first i was writing in a large json file. Each time i did a change, I would write the file and close it. It worked pretty well. I now use sqlite+dataset and the port was trivial. I guess there are better ways to do it than open(), but using dataset is quite simple. Maybe creating folders and duplicating data works, but it seems more complex than using sqlite and dataset.

0xbadcafebee 4y ago

It's "kick-the-can-down-the-road" engineering. If the server can't hold the database in RAM anymore, they buy more RAM. If the disks are too slow, they buy faster/more disks. If the one server goes down, the site is just down for an hour or two (or a day or two) while they build a new server. It works until it doesn't, and most people are fine with things eventually not working. This has only been practical within the last 6 years or so. Big servers were just too expensive. We started using cheap commodity low-resource PCs, which are cheaper to buy/scale/replace, but limits what your apps can do. Bare metal went down hard and there wasn't an API to quickly stand up a replacement. NAS/SAN was expensive. Network bandwidth was limited. The cloud has made everything cheaper and easier and faster and bigger, changing what designs are feasible. You can spin up a new instance with a 6GB database in memory in a virtual machine with network-attached storage and a gigabit pipe for like $20/month. That's crazy.

latchkey 4y ago

You aren't missing anything. Cloud SQL postgres on GCP with <50 connections is like ~$8/month and has automated daily backups. The differences in SQL syntax between SQLite and Postgres, namely around date math (last_ping_at < NOW() - INTERVAL '10 minutes'), make SQLite a non-starter imho... you're going to end up having to rewrite a lot of sql queries.