4 ms·

One thing I don't get ... are people really having problems getting PostgreSQL, or even MySQL, up and running? Is this REALLY a dev/devops/ops hurdle for peop

One thing I don't get ... are people really having problems getting PostgreSQL, or even MySQL, up and running? Is this REALLY a dev/devops/ops hurdle for people? Just seems strange that is actually a problem. Don't get me wrong, I use SQLite ... it's just not the database for my company's management web portal.

squirtlebonflow 3y ago
The vast majority of my development time for any project is setting up a server, database, web framework, etc. It's a massive pain in the ass and gets in the way of what I'm actually trying to do.
- Ensorceled 3y ago
  I've literally put a new django app live on AWS using PostgreSQL in a morning. Now ... it didn't do anything but it was live and you could login, create users etc.
yawaramin 3y ago
Administering an RDBMS server is only part of the problem. It's also mentioned quite clearly there that by having data locality (just disk I/O) you get massive efficiency gains in queries and effectively don't have to worry about the N+1 query problem. Queries run in the order of microseconds, not milliseconds because of network latency.
- winrid 3y ago
  A DB server won't have to go to disk for every query, sqlite almost always will, or at least reach out from user space to virtual memory, which is still slower than reading from a buffer table. So ya, in process is faster for trivial stuff. But it's not faster when doing heavier workloads that benefit from things being in memory or having a better query planner.
  - simonw 3y ago
    I don't think that's true? SQLite uses in-memory caching just like MySQL and PostgreSQL do.
    - winrid 3y ago
      yeah my bad it does have a page cache: https://www.sqlite.org/arch.html https://www.sqlite.org/arch.html The more you know :) The query planner is still not nearly as good as PG's, but it's okay for simple applications. I have actually used sqlite "at scale"... I'm still not sure people know that system (30k+qps dumping data into kafka) is running on sqlite on an EBS volume lol. But in this case it's just streaming stuff from disk, so pretty much any DB would have worked.
nh2 3y ago
"Just starting a postgres" is possible but it's a lot harder than it should be. Generally, you can just run the `postgres` command, but it creates many unnecessary hurdles: * Just want to invoke `postgres`? Not possible, you need to invoke `initdb` first. Why can't `postges` do that for automatically you if the DB dir doesn't exist, like other servers do (e.g. `consul`)? * Depending on your invocation, you're likely to run into errors mentioning "initdb: invalid locale settings; check LANG and LC_* environment variables". Why isn't there a simple UTF-8 default? * Postgres refuses to run as `root`; that's unoverridably hardcoded in its code. So you can't "just start it" when you're `root` inside a container or VM in which only postgres runs. It can't run well under `unshare` when you want things to shut down reliably on cancellation in CI. This constraint should just be removed; no other software refuses to run as root. This mindset is indeed from 27 years ago. * You can't start an in-memory postgres for quick testing or CI. This feature does not exist. All of this is fixable, but it currently creats the "oh you can 'just run' postgres, but not in random conditions A, B, C and D".
- returningfory2 3y ago
  I think just running Postgres via the standard Docker image solves most of these issues. Also an in memory Postgres would be nice, but Docker again gives an easy way of setting up “throwaway Postgresses”. This all assumes Linux ofc.
  - DANmode 3y ago
    That doesn't sound super friendly for local development env/prototyping when swathes of webdevs literally don't even know what Docker is, today.
    - 5Qn8mNbc2FNCiVV 3y ago
      I always put a docker-compose.yaml into the repo and in the readme some instructions to install docker and docker-compose and that they should run `docker-compose up` Works fine for almost any team. Obviously it would be great if they would try to understand it, but it's really not necessary. Not like most web devs know how most of the tools they use work (Source: All the teams I worked at)
IshKebab 3y ago
It's not about having problems, it's about the work that it takes to do. It's an extra hurdle that simply doesn't exist for SQLite. For a lot of projects that's worth it. Your comment is basically the famous HN Dropbox comment but for databases: "are people really having problems getting an FTP account, mounting it locally with curlftpfs, and then using SVN or CVS on the mounted filesystem?"
xnorswap 3y ago
Indeed, and given the constraints that allow for SQLite also allow for running a local postgresQL instance, so no service discovery, complicated auth flows, etc, required, it should be even easier. SQLite is fantastic, it absolutely should be used where appropriate. I don't however understand those who argue that replacing the likes of PostgresQL / SQL Server with it is generally appropriate.
Cthulhu_ 3y ago
I had one use case at my previous job. It was a bit of management software installed on premises / on physical or virtual servers (it was a bit old fashioned). The total installation involved setting up PHP, Apache, MySQL, and then installing the application itself, which also involved running some migration scripts written in shell scripts. What I replaced it with was a Go binary with SQLite, where installing it would be a matter of installing the package or just the files and starting it up, it would take care of the rest. SQLite is great for systems you don't control or have to set up, but maybe not for webservers. Common use cases are apps' internal storage (does not need to be shared with other applications or distributed), you don't want to have to install, configure and run MySQL or Postgres in those cases.
- Ensorceled 3y ago
  Dedicated apps are a FANTASTIC use case for SQLite, but most people already have SQLite as their goto for these applications.
  - zaphar 3y ago
    I think you vastly underestimate how many people use SQLite for this. The number of times I've seen someone boot up an entire Mysql Server fora dedicated app is far larger than the number of times I've seen them just reach for sqlite.
    - Ensorceled 3y ago
      Got it ... maybe this article is for those people :-)
Aurornis 3y ago
I agree. SQLite is great for some simple purposes, but this article acts like launching PostgreSQL is impossibly difficult and can only be done on a server 100ms away from your web server. For simple apps I just run Postgres on the same server and size things appropriately in settings. It’s really not hard and it’s well-trodden territory. If someone is making a single-tenant app that runs on an embedded device or something then SQLite is awesome. The way this developer concludes that “most of you reading this” should use SQLite is very strange. Is that indicative of his audience building small or toy websites rather than actual production websites?
- BenjiWiebe 3y ago
  How many actual production websites are actually too much for SQLite? 1%? Most production websites are not multi million dollar businesses.
  - Ensorceled 3y ago
    Most production websites are running Wordpress or something like Squarespace ...
  - d1sxeyes 3y ago
    Exactly the point here. There’s a bunch of brilliant reasons to use SQLite. One good reason is that by the time you outgrow it, you’re probably shifting enough data for it to be a worthwhile use of an engineering team’s time to plan a migration. If you use an ORM from the beginning, you might not even have to change very much of your application code.
corbezzoli 3y ago
Your argument sounds right but also why use the more difficult tool if the simple one fits your needs? Use right tool for the job.
gorjusborg 3y ago
Just let the HN hype wave wash over you my friend :) I see the sqlite hype as a (legitimate) pendulum swing away from defaulting to 'web scale' everything to a realization that most of the systems we design don't need it. On the other hand, when you start mixing in new tech like litefs to paper over shortcomings in the fundamental nature of an alternative, I start to question the sanity of the choice. We have screwdrivers, hammers, wrenches, let's not select a wrench and wrap it in a 'screwdriver adapter as a service'.
- merpnderp 3y ago
  As an aside, literally every single one of my tools in the garage doubles as a hammer... And I also use SQLite in places I definitely shouldn't be.
- sophacles 3y ago
  I question your metaphor - SQLite has been popular here since I started reading this site. Wave suggests a rise and fall of popularity (possibly in a long lasting cycle)... sqlite has always just been popular and well regarded. You may be noticing it more out of a frequency bias (baader-meinhoff effect) situation.
- calvinmorrison 3y ago
  Why do we need to hype what amounts to a core software for the planet. Sqlite is good in some places. Mysql is good in others. Were all smart enough here to make that decision when to reach for what
- logical_proof 3y ago
  > We have screwdrivers, hammers, wrenches, let's not select a wrench and wrap it in a 'screwdriver adapter as a service'. My hats off to you on this comment, even more so if you are the originator. Personal experience shows that I have added more complexity by spinning up a postgres server 'in case I need it' more often than not.
- LeonenTheDK 3y ago
  > I see the sqlite hype as a (legitimate) pendulum swing away from defaulting to 'web scale' everything to a realization that most of the systems we design don't need it. That was exactly my thought, the majority of work that gets put out there isn't going to need to scale to many different nodes to serve thousands+ of concurrent users. If you're actually serving tonnes of traffic and need clustering or high end redundancy, have at 'er. But for most of the professional work I've done, SQLite is more than enough (provided you back it up properly!) I think this is also influenced by my philosophy of keep things simple and only scale when you need to. I haven't had need of k8s or anything like that and suspect many companies serving small-medium traffic loads don't either.
dpeck 3y ago
It likely has more to do with the “data” team being different than the “application” team at a lot of big companies. It leads to many handoffs and inefficiencies and so people do a lot of things to route around it. Not a knock against SQLite at all, I use it in production for my own projects and some professionally as well.
jampekka 3y ago
It is a hurdle. I'd say vast majority of all development time goes into navigating through an endless stream of such trivial hurdles.
tootie 3y ago
There's so, so much missing context from almost every "you should use this tech" post. Software engineering is such an incredibly broad profession. People working on firmware for embedded medical devices and people making line of business software for an insurance company have wildly different choke points on productivity and utility. Budgets vary too. Maybe scaling SQLite can be done for low hosting costs if you just invest 200 hours of labor, but for most orgs, the 200 hours of labor are way more expensive than the hosting costs, so just pay for Postgres on AWS and don't worry about it.

squirtlebonflow 3y ago

The vast majority of my development time for any project is setting up a server, database, web framework, etc. It's a massive pain in the ass and gets in the way of what I'm actually trying to do.

Ensorceled 3y ago
I've literally put a new django app live on AWS using PostgreSQL in a morning. Now ... it didn't do anything but it was live and you could login, create users etc.

yawaramin 3y ago

Administering an RDBMS server is only part of the problem. It's also mentioned quite clearly there that by having data locality (just disk I/O) you get massive efficiency gains in queries and effectively don't have to worry about the N+1 query problem. Queries run in the order of microseconds, not milliseconds because of network latency.

winrid 3y ago
A DB server won't have to go to disk for every query, sqlite almost always will, or at least reach out from user space to virtual memory, which is still slower than reading from a buffer table. So ya, in process is faster for trivial stuff. But it's not faster when doing heavier workloads that benefit from things being in memory or having a better query planner.
- simonw 3y ago
  I don't think that's true? SQLite uses in-memory caching just like MySQL and PostgreSQL do.
  - winrid 3y ago
    yeah my bad it does have a page cache: https://www.sqlite.org/arch.html https://www.sqlite.org/arch.html The more you know :) The query planner is still not nearly as good as PG's, but it's okay for simple applications. I have actually used sqlite "at scale"... I'm still not sure people know that system (30k+qps dumping data into kafka) is running on sqlite on an EBS volume lol. But in this case it's just streaming stuff from disk, so pretty much any DB would have worked.

nh2 3y ago

"Just starting a postgres" is possible but it's a lot harder than it should be. Generally, you can just run the `postgres` command, but it creates many unnecessary hurdles: * Just want to invoke `postgres`? Not possible, you need to invoke `initdb` first. Why can't `postges` do that for automatically you if the DB dir doesn't exist, like other servers do (e.g. `consul`)? * Depending on your invocation, you're likely to run into errors mentioning "initdb: invalid locale settings; check LANG and LC_* environment variables". Why isn't there a simple UTF-8 default? * Postgres refuses to run as `root`; that's unoverridably hardcoded in its code. So you can't "just start it" when you're `root` inside a container or VM in which only postgres runs. It can't run well under `unshare` when you want things to shut down reliably on cancellation in CI. This constraint should just be removed; no other software refuses to run as root. This mindset is indeed from 27 years ago. * You can't start an in-memory postgres for quick testing or CI. This feature does not exist. All of this is fixable, but it currently creats the "oh you can 'just run' postgres, but not in random conditions A, B, C and D".

returningfory2 3y ago
I think just running Postgres via the standard Docker image solves most of these issues. Also an in memory Postgres would be nice, but Docker again gives an easy way of setting up “throwaway Postgresses”. This all assumes Linux ofc.
- DANmode 3y ago
  That doesn't sound super friendly for local development env/prototyping when swathes of webdevs literally don't even know what Docker is, today.
  - 5Qn8mNbc2FNCiVV 3y ago
    I always put a docker-compose.yaml into the repo and in the readme some instructions to install docker and docker-compose and that they should run `docker-compose up` Works fine for almost any team. Obviously it would be great if they would try to understand it, but it's really not necessary. Not like most web devs know how most of the tools they use work (Source: All the teams I worked at)

IshKebab 3y ago

It's not about having problems, it's about the work that it takes to do. It's an extra hurdle that simply doesn't exist for SQLite. For a lot of projects that's worth it. Your comment is basically the famous HN Dropbox comment but for databases: "are people really having problems getting an FTP account, mounting it locally with curlftpfs, and then using SVN or CVS on the mounted filesystem?"

xnorswap 3y ago

Indeed, and given the constraints that allow for SQLite also allow for running a local postgresQL instance, so no service discovery, complicated auth flows, etc, required, it should be even easier. SQLite is fantastic, it absolutely should be used where appropriate. I don't however understand those who argue that replacing the likes of PostgresQL / SQL Server with it is generally appropriate.

Cthulhu_ 3y ago

I had one use case at my previous job. It was a bit of management software installed on premises / on physical or virtual servers (it was a bit old fashioned). The total installation involved setting up PHP, Apache, MySQL, and then installing the application itself, which also involved running some migration scripts written in shell scripts. What I replaced it with was a Go binary with SQLite, where installing it would be a matter of installing the package or just the files and starting it up, it would take care of the rest. SQLite is great for systems you don't control or have to set up, but maybe not for webservers. Common use cases are apps' internal storage (does not need to be shared with other applications or distributed), you don't want to have to install, configure and run MySQL or Postgres in those cases.

Ensorceled 3y ago
Dedicated apps are a FANTASTIC use case for SQLite, but most people already have SQLite as their goto for these applications.
- zaphar 3y ago
  I think you vastly underestimate how many people use SQLite for this. The number of times I've seen someone boot up an entire Mysql Server fora dedicated app is far larger than the number of times I've seen them just reach for sqlite.
  - Ensorceled 3y ago
    Got it ... maybe this article is for those people :-)

Aurornis 3y ago

I agree. SQLite is great for some simple purposes, but this article acts like launching PostgreSQL is impossibly difficult and can only be done on a server 100ms away from your web server. For simple apps I just run Postgres on the same server and size things appropriately in settings. It’s really not hard and it’s well-trodden territory. If someone is making a single-tenant app that runs on an embedded device or something then SQLite is awesome. The way this developer concludes that “most of you reading this” should use SQLite is very strange. Is that indicative of his audience building small or toy websites rather than actual production websites?

BenjiWiebe 3y ago
How many actual production websites are actually too much for SQLite? 1%? Most production websites are not multi million dollar businesses.
- Ensorceled 3y ago
  Most production websites are running Wordpress or something like Squarespace ...
- d1sxeyes 3y ago
  Exactly the point here. There’s a bunch of brilliant reasons to use SQLite. One good reason is that by the time you outgrow it, you’re probably shifting enough data for it to be a worthwhile use of an engineering team’s time to plan a migration. If you use an ORM from the beginning, you might not even have to change very much of your application code.

corbezzoli 3y ago

Your argument sounds right but also why use the more difficult tool if the simple one fits your needs? Use right tool for the job.

gorjusborg 3y ago

Just let the HN hype wave wash over you my friend :) I see the sqlite hype as a (legitimate) pendulum swing away from defaulting to 'web scale' everything to a realization that most of the systems we design don't need it. On the other hand, when you start mixing in new tech like litefs to paper over shortcomings in the fundamental nature of an alternative, I start to question the sanity of the choice. We have screwdrivers, hammers, wrenches, let's not select a wrench and wrap it in a 'screwdriver adapter as a service'.

merpnderp 3y ago
As an aside, literally every single one of my tools in the garage doubles as a hammer... And I also use SQLite in places I definitely shouldn't be.
sophacles 3y ago
I question your metaphor - SQLite has been popular here since I started reading this site. Wave suggests a rise and fall of popularity (possibly in a long lasting cycle)... sqlite has always just been popular and well regarded. You may be noticing it more out of a frequency bias (baader-meinhoff effect) situation.
calvinmorrison 3y ago
Why do we need to hype what amounts to a core software for the planet. Sqlite is good in some places. Mysql is good in others. Were all smart enough here to make that decision when to reach for what
logical_proof 3y ago
> We have screwdrivers, hammers, wrenches, let's not select a wrench and wrap it in a 'screwdriver adapter as a service'. My hats off to you on this comment, even more so if you are the originator. Personal experience shows that I have added more complexity by spinning up a postgres server 'in case I need it' more often than not.
LeonenTheDK 3y ago
> I see the sqlite hype as a (legitimate) pendulum swing away from defaulting to 'web scale' everything to a realization that most of the systems we design don't need it. That was exactly my thought, the majority of work that gets put out there isn't going to need to scale to many different nodes to serve thousands+ of concurrent users. If you're actually serving tonnes of traffic and need clustering or high end redundancy, have at 'er. But for most of the professional work I've done, SQLite is more than enough (provided you back it up properly!) I think this is also influenced by my philosophy of keep things simple and only scale when you need to. I haven't had need of k8s or anything like that and suspect many companies serving small-medium traffic loads don't either.

dpeck 3y ago

It likely has more to do with the “data” team being different than the “application” team at a lot of big companies. It leads to many handoffs and inefficiencies and so people do a lot of things to route around it. Not a knock against SQLite at all, I use it in production for my own projects and some professionally as well.

jampekka 3y ago

It is a hurdle. I'd say vast majority of all development time goes into navigating through an endless stream of such trivial hurdles.

tootie 3y ago

There's so, so much missing context from almost every "you should use this tech" post. Software engineering is such an incredibly broad profession. People working on firmware for embedded medical devices and people making line of business software for an insurance company have wildly different choke points on productivity and utility. Budgets vary too. Maybe scaling SQLite can be done for low hosting costs if you just invest 200 hours of labor, but for most orgs, the 200 hours of labor are way more expensive than the hosting costs, so just pay for Postgres on AWS and don't worry about it.