4 ms·

Yup this. People don't realize just how performant SQLite can be. If you are running just 1 server and won't probably need more than SQLite is more than enough

Yup this. People don't realize just how performant SQLite can be. If you are running just 1 server and won't probably need more than SQLite is more than enough. 2 application servers and up is where PostgreSQL can come into play. https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html After reading the SQLite when-to-use document I transitioned some smaller marketing sites over from Postgres because it was overkill for the specific situation. Other multi-app server applications or ones that scale, SQLite might not be the best use, but it still has it's cases. Like copying over a subset of the original data and running queries locally instead of hitting a production database

fauigerzigerk 8y ago
If only SQLite had a decimal type. I would be using it a lot more. I know all the workarounds but they are tedious and error prone.
arendtio 8y ago
The biggest problem with SQLite is that it seems to be easy to use it wrong. If you are using SQLite as it is supposed to be used, it has much better performance than its reputation suggests.
- pjc50 8y ago
  In what way?
  - tim333 8y ago
    Not quite sure but Quora suggests >[slow]...This is because the entire database was locked every time someone viewed the page because it contained updates/inserts. So avoid loads of inserts I guess https://stackoverflow.com/questions/54998/how-scalable-is-sqlite https://stackoverflow.com/questions/54998/how-scalable-is-sq...
    - CydeWeys 8y ago
      There's plenty of use cases where you want to record analytics or metrics for each page load though -- so I guess SQLite simply isn't suitable for that?
      - int_19h 8y ago
        Depends on how many concurrent requests you have.
  - trashcan 8y ago
    Writes require an exclusive file handle/lock (only one write can happen at a time), so read heavy uses are usually a better match.
  - arendtio 8y ago
    I think the easiest way to do it wrong, is to close the file every time you finished your operation. With the default settings, this will sync and wait until the filesystem completes its operation. So unless you will close the file for a long time, keep it open. If you want to access the DB in a multi-threaded environment you better let one process manage the database. I guess 90% or performance problems with SQLite come from the filesystem.
tyldum 8y ago
But postgresql gives you not just the standard SQL database. It also does pubsub, timeseries, and lots more. It's fairly lean in itself and does not induce much overhead. Can run locally just with a unix socket and thus be just as easy as sqlite.

fauigerzigerk 8y ago

If only SQLite had a decimal type. I would be using it a lot more. I know all the workarounds but they are tedious and error prone.

arendtio 8y ago

The biggest problem with SQLite is that it seems to be easy to use it wrong. If you are using SQLite as it is supposed to be used, it has much better performance than its reputation suggests.

pjc50 8y ago
In what way?
- tim333 8y ago
  Not quite sure but Quora suggests >[slow]...This is because the entire database was locked every time someone viewed the page because it contained updates/inserts. So avoid loads of inserts I guess https://stackoverflow.com/questions/54998/how-scalable-is-sqlite https://stackoverflow.com/questions/54998/how-scalable-is-sq...
  - CydeWeys 8y ago
    There's plenty of use cases where you want to record analytics or metrics for each page load though -- so I guess SQLite simply isn't suitable for that?
    - int_19h 8y ago
      Depends on how many concurrent requests you have.
- trashcan 8y ago
  Writes require an exclusive file handle/lock (only one write can happen at a time), so read heavy uses are usually a better match.
- arendtio 8y ago
  I think the easiest way to do it wrong, is to close the file every time you finished your operation. With the default settings, this will sync and wait until the filesystem completes its operation. So unless you will close the file for a long time, keep it open. If you want to access the DB in a multi-threaded environment you better let one process manage the database. I guess 90% or performance problems with SQLite come from the filesystem.

tyldum 8y ago

But postgresql gives you not just the standard SQL database. It also does pubsub, timeseries, and lots more. It's fairly lean in itself and does not induce much overhead. Can run locally just with a unix socket and thus be just as easy as sqlite.