3 ms·

I haven't tried LiteFS yet, but as far as I understand, you still get the embedded performance for reads, and on the primary node you get that for writes as wel

I haven't tried LiteFS yet, but as far as I understand, you still get the embedded performance for reads, and on the primary node you get that for writes as well. SQLite can do things PostgreSQL can't. Like the "insert or replace" is superior to PostgreSQL upsert for maintaining seed data. And the flexible typing is great for a settings table where you can just have key/value columns and store numbers/text/bool/dates/etc... in the value column. Also, if you want to do a graph style query then SQLite is better then PostgreSQL. The way I see it, both PostgreSQL and SQLite have their strengths and LiteFS expands the areas where SQLite can be used.

paulryanrogers 4y ago
Postgres has MERGE and INSERT ON CONFLICT. Pg also has JSONB type which can store a variety of types within it. How is SQLite's replace and typing is superior to these?
- chillfox 4y ago
  It all depends upon what you are trying to do. If you want to store lot's of json data then yes, JSONB is the better option. As for the MERGE and INSERT ON CONFLICT vs INSERT OR REPLACE, they all work differently, they are not the same. The SQLITE option is simpler and better if you just want to ensure a full row is there in a specific state. If you want to preserve certain columns then the PostgreSQL options are better. Edit: I do wish that PostgreSQL had the SQLite options and that SQLite had the PostgreSQL options, because they are good for different things and it would be nice to have access to all of it regardless of which DB I am working with (I use both in different apps).
  - paulryanrogers 4y ago
    If you make a column JSONB you can store strings, numbers, arrays, objects, booleans. They don't have to be nested in an object. Though I suppose if you don't want to decode strings that's an extra step. I find duck typing in a DB more of a problem than a solution, unless the dataset is so small and variable I don't even need a RDBMS at all.
    - chillfox 4y ago
      I prefer SQLite's flexible types for a small settings table (other tables I will setup as strict). Anyway, it's nice having multiple options of how to do things.
- LAC-Tech 4y ago
  sqlite has json1 enabled by default these days https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html

paulryanrogers 4y ago

Postgres has MERGE and INSERT ON CONFLICT. Pg also has JSONB type which can store a variety of types within it. How is SQLite's replace and typing is superior to these?

chillfox 4y ago
It all depends upon what you are trying to do. If you want to store lot's of json data then yes, JSONB is the better option. As for the MERGE and INSERT ON CONFLICT vs INSERT OR REPLACE, they all work differently, they are not the same. The SQLITE option is simpler and better if you just want to ensure a full row is there in a specific state. If you want to preserve certain columns then the PostgreSQL options are better. Edit: I do wish that PostgreSQL had the SQLite options and that SQLite had the PostgreSQL options, because they are good for different things and it would be nice to have access to all of it regardless of which DB I am working with (I use both in different apps).
- paulryanrogers 4y ago
  If you make a column JSONB you can store strings, numbers, arrays, objects, booleans. They don't have to be nested in an object. Though I suppose if you don't want to decode strings that's an extra step. I find duck typing in a DB more of a problem than a solution, unless the dataset is so small and variable I don't even need a RDBMS at all.
  - chillfox 4y ago
    I prefer SQLite's flexible types for a small settings table (other tables I will setup as strict). Anyway, it's nice having multiple options of how to do things.
LAC-Tech 4y ago
sqlite has json1 enabled by default these days https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html