3 ms·

I can’t see any valid reason not to use Postgres at the back end, unless you are in some sort of environment such as embedded or cloudflare workers that require

I can’t see any valid reason not to use Postgres at the back end, unless you are in some sort of environment such as embedded or cloudflare workers that requires it. Or if you need a graph database there are better choices than Postgres. Postgres is good on multi core, incredibly feature rich, multi user, supported by everything, lightweight and has all the tools for production workload and management. All stuff that is important. Most important difference to me being SQLite I understand lacks flexibility in modifying table structures.

dimgl 3y ago
> any valid reason Well, cost, right? Cost is a reason why someone may not want to use a traditional RDBMS. AWS RDS and GCP Cloud SQL aren't exactly the cheapest solutions out there.
- andrewstuart 3y ago
  Postgres is free. Put it on a computer. Cost is certainly not an argument against Postgres cause it can run on any back end that runs Linux.
  - zaphar 3y ago
    Out of not cheaper to run a postgres server than an embedded sqlite DB. There is more to the cost than just the software license.
  - arcatech 3y ago
    Who pays for the computer and bandwidth?
kentonv 3y ago
Postgres is great at what it does, but it is extremely inefficient for storing a small amount of data, e.g. kilobytes or a few megabytes. sqlite, on the other hand, scales nicely all the way down to a few kb. This matters for cloud in that it means with Postgres you cannot take a "lots of small databases" strategy, e.g. database per user or database per document. You pretty much have to group a lot of data into one big database. Many apps want to do that anyway! For them, Postgres makes sense. But in the growing world of global deployments and edge compute, the lots-of-small-databases approach is getting popular because it means you can store than data out on hundreds or thousands of edge locations, rather than a single central location. And many (not all) applications actually fit pretty well into a database-per-user or database-per-document model. Centralizing their storage only hurts performance for no benefit. As a bonus, if you are able to run sqlite compiled directly into your app, not making any kind of network connection, it can be much faster than Postgres, especially in "N+1 select" situations (which are well-known to be a problem with most SQL databases, but are not a problem when using local sqlite). Postgres does not support running as a library like this.
- deleted 3y ago
  [deleted]
- fauigerzigerk 3y ago
  >This matters for cloud in that it means with Postgres you cannot take a "lots of small databases" strategy, e.g. database per user or database per document. You pretty much have to group a lot of data into one big database. You're right, it's a very interesting strategy. Unfortunately, last time I looked Cloudflare's D1 didn't support this approach as there was no API to create a database at runtime from Worker code. Has that changed?
- candiddevmike 3y ago
  Now your app is stateful, you need storage for it, a backup strategy, a free space monitoring strategy, a way to have the storage follow the app, etc. Depending on your situation, that could be harder than just getting a Postgres database.
  - ngrilly 3y ago
    I used to be in the stateless camp. But I think we pushed that argument too far. Stateless apps are not very useful. Most useful apps are stateful. What we are doing with promoting stateless services is just delegating the necessary stateful complexity to someone else, sweeping it under the carpet. By doing that, we are losing so many opportunities to do smarter and better things where the complexity really lies, which is where the state is. That’s why I really appreciate SQLite and other approaches like using KV embedded databases like RocksDB making a come back. That’s the job of the infrastructure providers, AWS, GCP, DO, and others, to provide the tools solving the problems you mentioned: block storage that is synchronously replicated across data centers (GCP does this), snapshots and backups on block storage, possibility to quickly reattach the block storage to a new computing node if the previous one died, etc.
  - kentonv 3y ago
    Edge compute platforms aim to take care of all that for you.
bambax 3y ago
> I can’t see any valid reason not to use Postgres at the back end From TFA: > if you don’t need the Postgres features, they’re a liability. For example, even if you don’t use multiple user accounts, you’ll still need to configure and debug host-based authentication. You have to firewall off your Postgres server. And more features mean more documentation, which makes it difficult to understand the software you’re running. The documentation for Postgres 14 is nearly 3,000 pages.
- andrewstuart 3y ago
  >> you’ll still need to configure and debug host-based authentication. False >> you have to firewall off your server Well yes. Are you saying SQLite servers don’t need a firewall? >> more features/more documentation/hard to understand your software Features lead to powerful software, documentation leads to understanding.
  - bambax 3y ago
    > Are you saying SQLite servers don’t need a firewall I'm not the author. But I can tell you there are no "SQLite servers". That's kind of the whole point.
- voganmother42 3y ago
  them citing the (excellent) Postgres documentation page count is hilarious. There is a really useful comparison to be made re: features / scope -- but approximating that and punishing a project for how comprehensive their docs are...feels like lines of code as a productivity measure but like 10x less accurate or useful lol
  - benbjohnson 3y ago
    Author here. My goal in the comparison was only in terms of scope, not that Postgres folks should be penalized for having good documentation. I think Postgres is great and it makes sense to use it when it's called for. But I think it can be overkill for many projects.
sumtechguy 3y ago
SQLite fills a need in the market that postgres does not. Local single process data storage and retrieval from that with some structure. Postgres and all of the DB's like it kick in when you want more than one process involved. MS used to have Access that filled this need very nicely. Once you go multi user/process you probably do not want sqlite you will want something that can do ACID on a multi user level. Once you go more than one process to store data you can make your installation a larger burden than you really need to deal with. On the other hand I can think of maybe one or two projects where sqllite fit very nicely for a data store. Most of the other cases I had involved a 'real' db. For this case I guess they could try to bend sqlite to do this but the pain in doing so will probably not be worth the long term trouble than just using mysql or postgres or something like those.
bajsejohannes 3y ago
> I can’t see any valid reason not to use Postgres at the back end I'm using sqlite in production for a backend service. There are plenty of downsides, but to focus on the positives: - I can run all tests in a database in memory. It's incredibly fast to "spin up" and I can use a separate database per test - Related, I find that I write more tests against the database instead of mocking a database, which cuts down on time writing tests. - I don't need to start a database to run the backend - I can have snapshots of the databases in a single files for various scenarios All in all, the development process feels a lot faster. When something takes a millisecond instead of seconds, you do things differently.
srcreigh 3y ago
Postgres doesn’t support b-trees for primary storage, so data can’t be automatically clustered, which reduces efficiency for joins by 50x. MySQL and SQLite don’t have this issue.
- adzm 3y ago
  this fact still blows me away

dimgl 3y ago

> any valid reason Well, cost, right? Cost is a reason why someone may not want to use a traditional RDBMS. AWS RDS and GCP Cloud SQL aren't exactly the cheapest solutions out there.

andrewstuart 3y ago
Postgres is free. Put it on a computer. Cost is certainly not an argument against Postgres cause it can run on any back end that runs Linux.
- zaphar 3y ago
  Out of not cheaper to run a postgres server than an embedded sqlite DB. There is more to the cost than just the software license.
- arcatech 3y ago
  Who pays for the computer and bandwidth?

kentonv 3y ago

Postgres is great at what it does, but it is extremely inefficient for storing a small amount of data, e.g. kilobytes or a few megabytes. sqlite, on the other hand, scales nicely all the way down to a few kb. This matters for cloud in that it means with Postgres you cannot take a "lots of small databases" strategy, e.g. database per user or database per document. You pretty much have to group a lot of data into one big database. Many apps want to do that anyway! For them, Postgres makes sense. But in the growing world of global deployments and edge compute, the lots-of-small-databases approach is getting popular because it means you can store than data out on hundreds or thousands of edge locations, rather than a single central location. And many (not all) applications actually fit pretty well into a database-per-user or database-per-document model. Centralizing their storage only hurts performance for no benefit. As a bonus, if you are able to run sqlite compiled directly into your app, not making any kind of network connection, it can be much faster than Postgres, especially in "N+1 select" situations (which are well-known to be a problem with most SQL databases, but are not a problem when using local sqlite). Postgres does not support running as a library like this.

deleted 3y ago
[deleted]
fauigerzigerk 3y ago
>This matters for cloud in that it means with Postgres you cannot take a "lots of small databases" strategy, e.g. database per user or database per document. You pretty much have to group a lot of data into one big database. You're right, it's a very interesting strategy. Unfortunately, last time I looked Cloudflare's D1 didn't support this approach as there was no API to create a database at runtime from Worker code. Has that changed?
candiddevmike 3y ago
Now your app is stateful, you need storage for it, a backup strategy, a free space monitoring strategy, a way to have the storage follow the app, etc. Depending on your situation, that could be harder than just getting a Postgres database.
- ngrilly 3y ago
  I used to be in the stateless camp. But I think we pushed that argument too far. Stateless apps are not very useful. Most useful apps are stateful. What we are doing with promoting stateless services is just delegating the necessary stateful complexity to someone else, sweeping it under the carpet. By doing that, we are losing so many opportunities to do smarter and better things where the complexity really lies, which is where the state is. That’s why I really appreciate SQLite and other approaches like using KV embedded databases like RocksDB making a come back. That’s the job of the infrastructure providers, AWS, GCP, DO, and others, to provide the tools solving the problems you mentioned: block storage that is synchronously replicated across data centers (GCP does this), snapshots and backups on block storage, possibility to quickly reattach the block storage to a new computing node if the previous one died, etc.
- kentonv 3y ago
  Edge compute platforms aim to take care of all that for you.

bambax 3y ago

> I can’t see any valid reason not to use Postgres at the back end From TFA: > if you don’t need the Postgres features, they’re a liability. For example, even if you don’t use multiple user accounts, you’ll still need to configure and debug host-based authentication. You have to firewall off your Postgres server. And more features mean more documentation, which makes it difficult to understand the software you’re running. The documentation for Postgres 14 is nearly 3,000 pages.

andrewstuart 3y ago
>> you’ll still need to configure and debug host-based authentication. False >> you have to firewall off your server Well yes. Are you saying SQLite servers don’t need a firewall? >> more features/more documentation/hard to understand your software Features lead to powerful software, documentation leads to understanding.
- bambax 3y ago
  > Are you saying SQLite servers don’t need a firewall I'm not the author. But I can tell you there are no "SQLite servers". That's kind of the whole point.
voganmother42 3y ago
them citing the (excellent) Postgres documentation page count is hilarious. There is a really useful comparison to be made re: features / scope -- but approximating that and punishing a project for how comprehensive their docs are...feels like lines of code as a productivity measure but like 10x less accurate or useful lol
- benbjohnson 3y ago
  Author here. My goal in the comparison was only in terms of scope, not that Postgres folks should be penalized for having good documentation. I think Postgres is great and it makes sense to use it when it's called for. But I think it can be overkill for many projects.

sumtechguy 3y ago

SQLite fills a need in the market that postgres does not. Local single process data storage and retrieval from that with some structure. Postgres and all of the DB's like it kick in when you want more than one process involved. MS used to have Access that filled this need very nicely. Once you go multi user/process you probably do not want sqlite you will want something that can do ACID on a multi user level. Once you go more than one process to store data you can make your installation a larger burden than you really need to deal with. On the other hand I can think of maybe one or two projects where sqllite fit very nicely for a data store. Most of the other cases I had involved a 'real' db. For this case I guess they could try to bend sqlite to do this but the pain in doing so will probably not be worth the long term trouble than just using mysql or postgres or something like those.

bajsejohannes 3y ago

> I can’t see any valid reason not to use Postgres at the back end I'm using sqlite in production for a backend service. There are plenty of downsides, but to focus on the positives: - I can run all tests in a database in memory. It's incredibly fast to "spin up" and I can use a separate database per test - Related, I find that I write more tests against the database instead of mocking a database, which cuts down on time writing tests. - I don't need to start a database to run the backend - I can have snapshots of the databases in a single files for various scenarios All in all, the development process feels a lot faster. When something takes a millisecond instead of seconds, you do things differently.

srcreigh 3y ago

Postgres doesn’t support b-trees for primary storage, so data can’t be automatically clustered, which reduces efficiency for joins by 50x. MySQL and SQLite don’t have this issue.

adzm 3y ago
this fact still blows me away