6 ms·

I love postgres for most things, but these days (Especially while my product is in early development, embedded, or just not internet-facing) sqlite is amazingly

I love postgres for most things, but these days (Especially while my product is in early development, embedded, or just not internet-facing) sqlite is amazingly workable. Killer postgres features however: Row-level security (Fantastic when you're using something like postgrest for rapid backend development [1]), and its built in fulltext search engine is 'good enough' for use cases like when you have an enormous users table and need to index something simple enough, like email addresses for quick login. [1] https://postgrest.org/ https://postgrest.org/

kristianp 4y ago
One thing I miss in sqlite is numeric types that store binary-coded decimal. (Is there a plugin for that?) This is very useful for currency calculations.
giovannibonetti 4y ago
It is not very useful to add full text search to an email field used for login. A regular unique index, perhaps case insensitive, is what you should be using.
- VWWHFSfQ 4y ago
  is case insensitive fulltext faster or slower than case insensitive index search on a varchar
  - microsoftdoes 4y ago
    The index should not be slower unless something is seriously wrong.
- lolinder 4y ago
  There's an extension for trigram similarity operators which is useful for a quick and easy fuzzy search for small things like email and name: https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11.7.44.6 https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11....
- fbdab103 4y ago
  Today I could be a lucky 10,000 - are emails case sensitive? I had always assumed you could pre-process (ie lowercase) them before insertion so that database case sensitivity was not an issue.
  - datalopers 4y ago
    Technically speaking the portion of the email address before the @ is case-sensitive. However in practice it is ubiquitous that they’re treated as case-insensitive across all mail platforms.
    - 8organicbits 4y ago
      Does anyone know a single example of a case sensitive email provider or email server implementation? I believe I saw a positive answer to this 10 years back (an old university mail server?) but these must be quite rare.
    - notpushkin 4y ago
      This means however that you should store the original email and not just lower case it on insert. Imagine if you could reset password for Jane.Doe@example.com by registering the jane.doe@example.com address (assuming example.com does differentiate between the two) and requesting password reset for that.
      - fbdab103 4y ago
        It goes deeper than that. If emails are case sensitive, everything changes in the context of unique accounts. If you have jane.doe@ and Jane.doe@ attempts to login - what do you do?
        giantrobot 4y ago
        You create a contact address from a normalized version of the entered address (after address verification) and an independent account ID. You can also generate an account ID derived from that normalized address. The positive response of the address verification will tell you the address is deliverable and the user has access to it. Later if someone tries to register a capitalized form of the address it'll get rejected because of that account ID collision. Then the user can be pushed to a password recovery path where they'll need access to the e-mail/MFA to get control of the account.
deleted 4y ago
[deleted]
KingOfCoders 4y ago
I would agree, I'm currently also playing with Postgres that triggers some data to sqllite data wrapper to be distributed by Litestream to servers so they can locally read data.
- asenchi 4y ago
  Why would you add sqlite here? Postgres can do all of that without the extra tech of litestream.
  - VWWHFSfQ 4y ago
    Yeah I'm super confused about what is going on in this architecture
    - fbdab103 4y ago
      I think the design is for local (edge node) read replicas?
necovek 4y ago
> need to index something simple enough, like email addresses for quick login It sounds like you might be unfamiliar with the common trick to index long text fields in Postgres: you just make an index of hashed values, and use that for lookup as well to ensure index gets hit. In case you are familiar with it, maybe it helps someone else who stumbles upon this. :)
- VWWHFSfQ 4y ago
  it's a huge waste of space and now you have to have triggers somewhere to keep the index field consistent. the database should be able handle case insensitive indexed lookups directly.
  - efficax 4y ago
    you can use postgres generated columns to keep the index field automatically consistent. https://www.postgresql.org/docs/current/ddl-generated-columns.html https://www.postgresql.org/docs/current/ddl-generated-column...
    - VWWHFSfQ 4y ago
      I've seen this kind of thing a lot before and I'm saying that it's almost never needed. All you're doing is building your own bespoke indexing system on top of a database that is doing it (much better) already.
      - giantrobot 4y ago
        You're augmenting the indexing system. By using hashes you get a column with fixed predictable size. If the average size of your large strings is larger than 16 bytes (or 32 bytes if you store the hex string) you'll get more rows per memory page. If you've got many millions of rows the savings adds up. A little bit of savings let's a smaller DB instance go farther.
        jamal-kumar 4y ago
cachehit 4y ago
Do you need (or at least benefit from) your database to run in-process? Because that is the only advantage I can see to SQLite over Postgres. Which makes it the better candidate in many places, but not for anything like a server.
- luto 4y ago
  It makes the dev setup trivial, since there is no database server around.
  - moron4hire 4y ago
    Setting up postgres on the same machine is also trivial. Modern, production-grade, web-scale machines are able to run more than one process, these days.
    - aiwv 4y ago
      Sure, but an embedded database is still simpler than client/server. For many tasks, postgres does not offer any meaningful benefit over sqlite so why add the complexity?
      - antifa 4y ago
        I've never worked on such a project that didn't benefit from postgres, but if I did, I would still use postgres because upgrading from sqlite to postgres, and learning a second SQL dialect, and converting a SQL between dialects sounds like a useless nightmare that costs me nothing to avoid.
        CoolCold 4y ago
        I feel it's modern hype on lonely/indie/solo developer creating new thing which changes the world. Not caring on "complex" things till the project reaches 1 request per minute and finally have some data to be lost, is totally fine with this goal/scale.
- 4y ago

kristianp 4y ago

One thing I miss in sqlite is numeric types that store binary-coded decimal. (Is there a plugin for that?) This is very useful for currency calculations.

giovannibonetti 4y ago

It is not very useful to add full text search to an email field used for login. A regular unique index, perhaps case insensitive, is what you should be using.

VWWHFSfQ 4y ago
is case insensitive fulltext faster or slower than case insensitive index search on a varchar
- microsoftdoes 4y ago
  The index should not be slower unless something is seriously wrong.
lolinder 4y ago
There's an extension for trigram similarity operators which is useful for a quick and easy fuzzy search for small things like email and name: https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11.7.44.6 https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11....
fbdab103 4y ago
Today I could be a lucky 10,000 - are emails case sensitive? I had always assumed you could pre-process (ie lowercase) them before insertion so that database case sensitivity was not an issue.
- datalopers 4y ago
  Technically speaking the portion of the email address before the @ is case-sensitive. However in practice it is ubiquitous that they’re treated as case-insensitive across all mail platforms.
  - 8organicbits 4y ago
    Does anyone know a single example of a case sensitive email provider or email server implementation? I believe I saw a positive answer to this 10 years back (an old university mail server?) but these must be quite rare.
  - notpushkin 4y ago
    This means however that you should store the original email and not just lower case it on insert. Imagine if you could reset password for Jane.Doe@example.com by registering the jane.doe@example.com address (assuming example.com does differentiate between the two) and requesting password reset for that.
    - fbdab103 4y ago
      It goes deeper than that. If emails are case sensitive, everything changes in the context of unique accounts. If you have jane.doe@ and Jane.doe@ attempts to login - what do you do?
      - giantrobot 4y ago
        You create a contact address from a normalized version of the entered address (after address verification) and an independent account ID. You can also generate an account ID derived from that normalized address. The positive response of the address verification will tell you the address is deliverable and the user has access to it. Later if someone tries to register a capitalized form of the address it'll get rejected because of that account ID collision. Then the user can be pushed to a password recovery path where they'll need access to the e-mail/MFA to get control of the account.

deleted 4y ago

[deleted]

KingOfCoders 4y ago

I would agree, I'm currently also playing with Postgres that triggers some data to sqllite data wrapper to be distributed by Litestream to servers so they can locally read data.

asenchi 4y ago
Why would you add sqlite here? Postgres can do all of that without the extra tech of litestream.
- VWWHFSfQ 4y ago
  Yeah I'm super confused about what is going on in this architecture
  - fbdab103 4y ago
    I think the design is for local (edge node) read replicas?

necovek 4y ago

> need to index something simple enough, like email addresses for quick login It sounds like you might be unfamiliar with the common trick to index long text fields in Postgres: you just make an index of hashed values, and use that for lookup as well to ensure index gets hit. In case you are familiar with it, maybe it helps someone else who stumbles upon this. :)

VWWHFSfQ 4y ago
it's a huge waste of space and now you have to have triggers somewhere to keep the index field consistent. the database should be able handle case insensitive indexed lookups directly.
- efficax 4y ago
  you can use postgres generated columns to keep the index field automatically consistent. https://www.postgresql.org/docs/current/ddl-generated-columns.html https://www.postgresql.org/docs/current/ddl-generated-column...
  - VWWHFSfQ 4y ago
    I've seen this kind of thing a lot before and I'm saying that it's almost never needed. All you're doing is building your own bespoke indexing system on top of a database that is doing it (much better) already.
    - giantrobot 4y ago
      You're augmenting the indexing system. By using hashes you get a column with fixed predictable size. If the average size of your large strings is larger than 16 bytes (or 32 bytes if you store the hex string) you'll get more rows per memory page. If you've got many millions of rows the savings adds up. A little bit of savings let's a smaller DB instance go farther.
      - jamal-kumar 4y ago

cachehit 4y ago

Do you need (or at least benefit from) your database to run in-process? Because that is the only advantage I can see to SQLite over Postgres. Which makes it the better candidate in many places, but not for anything like a server.

luto 4y ago
It makes the dev setup trivial, since there is no database server around.
- moron4hire 4y ago
  Setting up postgres on the same machine is also trivial. Modern, production-grade, web-scale machines are able to run more than one process, these days.
  - aiwv 4y ago
    Sure, but an embedded database is still simpler than client/server. For many tasks, postgres does not offer any meaningful benefit over sqlite so why add the complexity?
    - antifa 4y ago
      I've never worked on such a project that didn't benefit from postgres, but if I did, I would still use postgres because upgrading from sqlite to postgres, and learning a second SQL dialect, and converting a SQL between dialects sounds like a useless nightmare that costs me nothing to avoid.
      - CoolCold 4y ago
        I feel it's modern hype on lonely/indie/solo developer creating new thing which changes the world. Not caring on "complex" things till the project reaches 1 request per minute and finally have some data to be lost, is totally fine with this goal/scale.
4y ago