6 ms·

much easier just to use pg

jilles 2y ago
Not really. Setting a few sqlite options and enabling litestream for backups is much easier than setting up Postgres with reliable backups. Should you do this for all apps? No. Do you have read heavy applications? Consider SQLite
vidarh 2y ago
I'm not using Rails, but I now have several sites using my own little thing that is a single docker container where all state + content is in a single sqlite file, and it's very nice to be able to just move that single file around. I love postgres, but doing the equivalent of that with Postgres is a lot more hassle.
- tracker1 2y ago
  While I'm fine with using SQLite for these things, I would counter that a docker-compose file makes using a db with your app roughly as easy as a sqlite file, only in that you'd have have a data directory as a volume mount for the db. PostgreSQL and MySQL/MariaDB in particular are pretty easy to launch with a configured user/pass for the db/app specifically with docker/compose.
  - vidarh 2y ago
    Docker compose itself introduces a lot of complexity I don't need with that setup.
    - tracker1 2y ago
      If you're comparing to SQLite, sure... if you're comparing to installing and configuring an RDBMS server on a host OS for development work, I'm going to hard disagree. Most services already have a hosted docker container configured, usually by the developers of said service. Getting that running is often as simple as googling "service-name docker-compose example" and "docker compose up". And once you do understand docker-compose, it becomes second nature. I'd be willing to state that dealing with a merge conflict with source control is more difficult than docker-compose.
tptacek 2y ago
It is until you realize that using SQLite means you don't have to worry about N+1 queries, which actually does make a pretty big difference in Rails code.
- abound 2y ago
  Not sure I understand this point, how does SQLite fix the N+1 query problem? Just by having the data co-located with the app and avoiding the round-trip latency hit? If so, I'd argue you still have N+1 problems, you just won't notice them until N gets a bit larger.
  - tptacek 2y ago
    https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html
    - abound 2y ago
      Ah cool, thanks for the link! For others, the short-ish answer is that doing hundreds of SQL queries in response to a request (loading nested timeline elements in their case) in SQLite is fine because of the lack of networking/IPC overhead. The nature of N+1 queries is unchanged.
      - plorkyeran 2y ago
        The other half of it that the sqlite page doesn't mention is that sqlite lacks query engine optimizations which would make one large query faster than many smaller queries. If you had a hypothetical in-process version of postgres which didn't have any IPC or networking overhead you'd still see benefits from performing fewer larger queries when using it because the query planner adds some overhead to small queries but makes complex queries faster.
        notpushkin 2y ago
        I'm wondering how would it perform if we can compile https://pglite.dev/ https://pglite.dev/ as a native library and use it as an in-process Postgres... I know Node folks already use it as a Wasm module, so it shouldn’t be too tricky?
anothername12 2y ago
Agreed. I always regret starting a new rails project with sqlite. Invariably I end up wanting some bunch of features only pg has. Even for running tens of thousands of integration tests in a few seconds, pg is fine.
dewey 2y ago
I kind of have to agree, I recently thought I'll use sqlite in Rails for my new project to keep things simple but then realized it's actually more annoying for my use case. I'd need a persistent volume with the right permissions, and I can't just connect to my PG instance running on the server from my local machine to run some queries. I'm sure it makes things easier for some use cases but it's not a given.

Not really. Setting a few sqlite options and enabling litestream for backups is much easier than setting up Postgres with reliable backups. Should you do this for all apps? No. Do you have read heavy applications? Consider SQLite

vidarh 2y ago

I'm not using Rails, but I now have several sites using my own little thing that is a single docker container where all state + content is in a single sqlite file, and it's very nice to be able to just move that single file around. I love postgres, but doing the equivalent of that with Postgres is a lot more hassle.

tracker1 2y ago
While I'm fine with using SQLite for these things, I would counter that a docker-compose file makes using a db with your app roughly as easy as a sqlite file, only in that you'd have have a data directory as a volume mount for the db. PostgreSQL and MySQL/MariaDB in particular are pretty easy to launch with a configured user/pass for the db/app specifically with docker/compose.
- vidarh 2y ago
  Docker compose itself introduces a lot of complexity I don't need with that setup.
  - tracker1 2y ago
    If you're comparing to SQLite, sure... if you're comparing to installing and configuring an RDBMS server on a host OS for development work, I'm going to hard disagree. Most services already have a hosted docker container configured, usually by the developers of said service. Getting that running is often as simple as googling "service-name docker-compose example" and "docker compose up". And once you do understand docker-compose, it becomes second nature. I'd be willing to state that dealing with a merge conflict with source control is more difficult than docker-compose.

tptacek 2y ago

It is until you realize that using SQLite means you don't have to worry about N+1 queries, which actually does make a pretty big difference in Rails code.

abound 2y ago
Not sure I understand this point, how does SQLite fix the N+1 query problem? Just by having the data co-located with the app and avoiding the round-trip latency hit? If so, I'd argue you still have N+1 problems, you just won't notice them until N gets a bit larger.
- tptacek 2y ago
  https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html
  - abound 2y ago
    Ah cool, thanks for the link! For others, the short-ish answer is that doing hundreds of SQL queries in response to a request (loading nested timeline elements in their case) in SQLite is fine because of the lack of networking/IPC overhead. The nature of N+1 queries is unchanged.
    - plorkyeran 2y ago
      The other half of it that the sqlite page doesn't mention is that sqlite lacks query engine optimizations which would make one large query faster than many smaller queries. If you had a hypothetical in-process version of postgres which didn't have any IPC or networking overhead you'd still see benefits from performing fewer larger queries when using it because the query planner adds some overhead to small queries but makes complex queries faster.
      - notpushkin 2y ago
        I'm wondering how would it perform if we can compile https://pglite.dev/ https://pglite.dev/ as a native library and use it as an in-process Postgres... I know Node folks already use it as a Wasm module, so it shouldn’t be too tricky?

anothername12 2y ago

Agreed. I always regret starting a new rails project with sqlite. Invariably I end up wanting some bunch of features only pg has. Even for running tens of thousands of integration tests in a few seconds, pg is fine.

dewey 2y ago

I kind of have to agree, I recently thought I'll use sqlite in Rails for my new project to keep things simple but then realized it's actually more annoying for my use case. I'd need a persistent volume with the right permissions, and I can't just connect to my PG instance running on the server from my local machine to run some queries. I'm sure it makes things easier for some use cases but it's not a given.