9 ms·

The fact that the performance is so close between a database that requires NO SERVER and one that requires a dedicated server + ops management is a signal to me

The fact that the performance is so close between a database that requires NO SERVER and one that requires a dedicated server + ops management is a signal to me that all projects should start on SQLite unless there is a compelling reason not to.

jayd16 4y ago
Postgres doesn't require a _dedicated_ server. If your requirements allow for SQLite but it didn't exist, you could conceivably run postgres on your app server to reduce the ops load if you really felt it was a significant effort. If you might want to use multiple app instances connected to a shared DB, I would say it's probably easier to just use a local postgres container for dev and a managed cloud DB. Really not that much effort and you get automatic backups. If you plan to never use a shared DB, SQLite is great though.
- mikepurvis 4y ago
  "never use a shared DB" I think this is what it comes down to for me too. Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type, but ultimately, for most use-cases, it's going to hinge on whether or not you ever expect to need to scale any part of the system horizontally, whether that's the frontends or the DB itself.
  - msbarnett 4y ago
    > Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type Or a proper UUID type, materialized views, enforced data-constraints, lateral joins, queries that consider multiple indexes, etc, etc. It's not like there's just a couple of niches where PostgreSQL's features distinguish it from SQLite. SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. It's a good little piece of software, but it hardly relegates PostgreSQL to "Just GIS things" or "Just JSON use-cases".
    - ElectricalUnion 4y ago
      > SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. SQLite is also appropriate for use cases where you are bothered by silent data inconsistency/corruption, it's just that often the hardware running PostgreSQL (or any "serious" DBMS, relational or not) is usually less prone to random inconsistency/corruption (non-interruptible redundant power sources, well cooled environment, running lower but more stable clock speeds on everything, ECC RAM, raid disk array, corruption-resistant FS). If you run PostgreSQL on the cheapest consumer hardware, expect random corruption of the database when power runs out several times in a row.
      - msbarnett 4y ago
        Sorry, "corruption" was probably misleading there. By "silent data inconsistency/corruption" I mean, "a bug in my application led to me storing strings in an integer column, or too-long data in a too-short column, and SQLite just silently let me do that"-type errors – the data consistency is logically (but not physically) corrupted. The author encountered 2 separate occurrences of this just in their simple use-case, evidently.
        323 4y ago
        > led to me storing strings in an integer column Recent SQLite versions have STRICT mode where they forbid that. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html > or too-long data in a too-short column You can use a constraint for that: CREATE TABLE test ( name TEXT NOT NULL CHECK(length(name) <= 20) ) STRICT;
        mikepurvis 4y ago
        Neat, will definitely give this a try. Definitely one of my minor peeves about SQLite is having to set a bunch of pragmas on every new connection. Like, especially stuff such as foreign_keys=1... really, enforcement of foreign key constraints is something each connection has to opt into rather than being set one on the whole DB file and never thought about again? Blah.
  - twhitmore 4y ago
    Well, unless you care about disaster recovery or availability. For most business applications used by more than one user there are usually some expectations that data will be retained and the application will be available.
- robonerd 4y ago
  I read that as "dedicated server[process]" not "dedicated server[hardware]" While the latter would obviously imply the former, it's really the former that increases the complexity of managing whole affair. Desktop applications with a dependency on mysql/postgres running on the same machine are, in my experience without exception, a pain in the ass for users.
  - ed25519FUUU 4y ago
    Managing a publicly accessible DB and user certs to connect to it securely is like someone’s full time job at some companies.
    - dx034 4y ago
      Why should the database ever be publicly accessible? I've only ever seen databases being accessible by applications and maybe users (but those only for analytics databases), and have never experienced anyone working full time on certs and user access for a single database. There's usually a team for that, but they do that for the whole server fleet.
- riku_iki 4y ago
  > you could conceivably run postgres on your app server to reduce the ops load if you really felt it was a significant effort. Still, PGSQL requires some level of setup/maintenance: create user, permissions, database, service with config/security effort. If there is a way to run PGSQL as lib and say to it: just store your data in this dir without other steps, I am very interested to learn.
  - urda 4y ago
    You are likely wanting something like RDS [1], Azure [2], or even Linode (which I just discovered) [3]. Sure you have to pay a larger premium for it, but these products do allow you to focus on the app than the DBA stuff. - [1] https://aws.amazon.com/rds/ https://aws.amazon.com/rds/ - [2] https://azure.microsoft.com/en-us/services/postgresql/#overview https://azure.microsoft.com/en-us/services/postgresql/#overv... - [3] https://www.linode.com/products/postgresql/ https://www.linode.com/products/postgresql/
    - riku_iki 4y ago
      > pay a larger premium for it yeah, premium is very large if I want to do a lot of data crunching (lots of iops, cores and ram is needed), plus latency/throughput to those services will be much higher than to my local disk through PCIE bus.
  - eropple 4y ago
    You can set up Postgres with three lines of bash and a Unix socket. It is more work; I wouldn't say it's appreciably more work, and if you need more rigorous data modeling (I frequently do) then it's worth considering if this is the mode in which you'd like to run your data stores (I wouldn't, but latency isn't the most critical thing for stuff I do).
    - riku_iki 4y ago
      I think it is more than that. Steps will be: - sudo apt install postgres (more steps are needed if you are not satisfied with OS default version) - sudo su postgres - psql -> create db, user, grant permissions - modify hba.conf file to change connection permissions - modify postgresql.conf to change settings to your liking because defaults are very out of touch with modern hardware Another options would be to build your docker image, but I am not good with this.
rco8786 4y ago
yes! It seems to me (or at least, I'm hoping this is happening) that the pendulum is swinging back to simple deployments and away from AWS/Cloud/K8s for every tiny little app. One thing I'd love to see is that the 'default' MVP deployment is your code + SQLite running on a single VM. That will scale well into "traction" for the vast majority of applications people are building.
- namecheapTA 4y ago
  I have a fully working SaaS with 30+ companies paying, with 20 employees each on average. I wrote it as a self taught programmer via YouTube and stackoverflow. It is hosted on a $2 a month namecheap sharedhosting. Php back, jQuery front. I use cpanel to set things up. It runs faster than 90% of webapps on the internet.
  - lazide 4y ago
    Yup, I believe it. As an industry 90% of companies are spending 90% of their time on crap only the remaining 10% of the companies actually need. Good resume fodder though I guess.
  - rco8786 4y ago
    I believe it!
  - downut 4y ago
    Do you implement something akin to Role Based Access Control for those 30x20 employees? If so where do you store and manage that data? I have zero experience with this but I am very curious how people do it in sqlite.
    - ElectricalUnion 4y ago
      Not OP, but like any other database? Even if the database supports it, you usually should not have users directly logged in the database anyways, so whenever the database "has" or "lacks" access control, it doesn't matter. What does matter however, is enforcing parametrized queries everywhere. Unless all the db handles you pass to the client handling code are read-only, chaos will ensure from the DDL permissions. https://xkcd.com/327/ https://xkcd.com/327/
masklinn 4y ago
It's funny because to me it says pretty much the opposite: because sqlite has no server it works entirely in-process, whereas the client will be talking to postgres over a unix socket, or possibly a full-blown network socket. Plus it seems to be a readonly workload, something for which posgres is not ultra relevant (not that it doesn't work, but...).
Beltiras 4y ago
There are enough SQL oopsies in this article to show that you should NEVER use sqlite for any production except as filter databases or other sort of throwaways. RDBMSs are there to enforce constraints on data.
- spopejoy 4y ago
  RDBMS constraints are oversold usually. For instance it is usually the case that things like foreign-key constraints are avoided in production because they cause performance and maintenance nightmares. Widely-used constraints like UNIQUE are production-quality in SQLite.
- ElectricalUnion 4y ago
  > NEVER use sqlite for any production > RDBMSs are there to enforce constraints on data. If you did not ask SQLite to enforce type constraints on relations (with STRICT), it will work in the relaxed, backwards-compatible expected behavior of previous versions of SQLite. That being said, if you want actual validation, you probably need more complex CHECK expressions anyways with your business rules, and those work by default on any database. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html * unless you're a madman that runs things with "PRAGMA ignore_check_constraints = false;" enabled or equivalent; in that case, no DB can help you.
- fmajid 4y ago
  That’s silly, SQLite is the single most widely deployed database engine in the world, possibly the most deployed software, period. It’s unit test suite is second to none. It’s reliable enough that SMF, Solaris’ boot and service management (sort of like systemd) depends on it. That does not mean it is applicable to all databases use-cases, but production-grade it undoubtedly is.
  - ThinkBeat 4y ago
    How many installations SQLite has is not that relevant in a discussion of SQLite vs full RDMS. By your example, a bank should use SQLite because it is deployed widely. You have to look where they are deployed and the use cases. They also come with dramatically different tooling which make sa huge difference. More tooling does not mean that a full RDMS is a better choice: It is just about what you need, what use case you need to cover and what industry you are in.
avinassh 4y ago
hmm in that case I would probably pick Postgres because despite the added network call, it is still faster than an embedded DB.
- ormaaj 4y ago
  As I understand it the actual low-level database interface may be a shared library mechanism in either case. At least, for a local database I'm pretty sure that's a function of the connection type. You're still dlopen()-ing an ODBC library and the underlying glue between that and the rest of the userspace DB engine parts can be implemented in many ways. If there's a socket involved it would probably be AF_UNIX
lazide 4y ago
Uh, most distributed environments you have no choice but to use a server, because you’ll have clients on a (relatively) uncontrollable set of other machines. If you’re writing something to run in an embedded/client app environment, then yeah why would you use Postgres for your one machine? You could, but it’ll add a lot of moving parts you’ll never need and probably don’t want (like remotely accessible network ports you’ll need to secure)
BeefWellington 4y ago
Sure, you can start there. Once you scale though you're going to have lots of fun.
- neverartful 4y ago
  For many applications you're right. However, in the case of a dashboard for blog articles I don't think it could ever come to that.
- ed25519FUUU 4y ago
  I’ve never worked on a project that was scaled out that didn’t require at least moderate DB maintenance, such as a large migration or sharding. A migration is all but expected. Might as well make things easy on yourself for the first year and start with SQLite.
- marginalia_nu 4y ago
  We're talking about data from a blog, though. It's not like it's going to scale to have terabytes of entries anytime soon.
WuxiFingerHold 4y ago
I disagree: Postgres and SQLite are very different in several aspects, e.g. - types and enforcing type safety - handling concurrency and transactions (available isolation levels) - functions / procedures - array support Postgres can be much more valuable than just using it for "relational data storage". Its features can be a good reason for choosing it from the beginning.

jayd16 4y ago

Postgres doesn't require a _dedicated_ server. If your requirements allow for SQLite but it didn't exist, you could conceivably run postgres on your app server to reduce the ops load if you really felt it was a significant effort. If you might want to use multiple app instances connected to a shared DB, I would say it's probably easier to just use a local postgres container for dev and a managed cloud DB. Really not that much effort and you get automatic backups. If you plan to never use a shared DB, SQLite is great though.

mikepurvis 4y ago
"never use a shared DB" I think this is what it comes down to for me too. Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type, but ultimately, for most use-cases, it's going to hinge on whether or not you ever expect to need to scale any part of the system horizontally, whether that's the frontends or the DB itself.
- msbarnett 4y ago
  > Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type Or a proper UUID type, materialized views, enforced data-constraints, lateral joins, queries that consider multiple indexes, etc, etc. It's not like there's just a couple of niches where PostgreSQL's features distinguish it from SQLite. SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. It's a good little piece of software, but it hardly relegates PostgreSQL to "Just GIS things" or "Just JSON use-cases".
  - ElectricalUnion 4y ago
    > SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. SQLite is also appropriate for use cases where you are bothered by silent data inconsistency/corruption, it's just that often the hardware running PostgreSQL (or any "serious" DBMS, relational or not) is usually less prone to random inconsistency/corruption (non-interruptible redundant power sources, well cooled environment, running lower but more stable clock speeds on everything, ECC RAM, raid disk array, corruption-resistant FS). If you run PostgreSQL on the cheapest consumer hardware, expect random corruption of the database when power runs out several times in a row.
    - msbarnett 4y ago
      Sorry, "corruption" was probably misleading there. By "silent data inconsistency/corruption" I mean, "a bug in my application led to me storing strings in an integer column, or too-long data in a too-short column, and SQLite just silently let me do that"-type errors – the data consistency is logically (but not physically) corrupted. The author encountered 2 separate occurrences of this just in their simple use-case, evidently.
      - 323 4y ago
        > led to me storing strings in an integer column Recent SQLite versions have STRICT mode where they forbid that. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html > or too-long data in a too-short column You can use a constraint for that: CREATE TABLE test ( name TEXT NOT NULL CHECK(length(name) <= 20) ) STRICT;
        mikepurvis 4y ago
        Neat, will definitely give this a try. Definitely one of my minor peeves about SQLite is having to set a bunch of pragmas on every new connection. Like, especially stuff such as foreign_keys=1... really, enforcement of foreign key constraints is something each connection has to opt into rather than being set one on the whole DB file and never thought about again? Blah.
- twhitmore 4y ago
  Well, unless you care about disaster recovery or availability. For most business applications used by more than one user there are usually some expectations that data will be retained and the application will be available.
robonerd 4y ago
I read that as "dedicated server[process]" not "dedicated server[hardware]" While the latter would obviously imply the former, it's really the former that increases the complexity of managing whole affair. Desktop applications with a dependency on mysql/postgres running on the same machine are, in my experience without exception, a pain in the ass for users.
- ed25519FUUU 4y ago
  Managing a publicly accessible DB and user certs to connect to it securely is like someone’s full time job at some companies.
  - dx034 4y ago
    Why should the database ever be publicly accessible? I've only ever seen databases being accessible by applications and maybe users (but those only for analytics databases), and have never experienced anyone working full time on certs and user access for a single database. There's usually a team for that, but they do that for the whole server fleet.
riku_iki 4y ago
> you could conceivably run postgres on your app server to reduce the ops load if you really felt it was a significant effort. Still, PGSQL requires some level of setup/maintenance: create user, permissions, database, service with config/security effort. If there is a way to run PGSQL as lib and say to it: just store your data in this dir without other steps, I am very interested to learn.
- urda 4y ago
  You are likely wanting something like RDS [1], Azure [2], or even Linode (which I just discovered) [3]. Sure you have to pay a larger premium for it, but these products do allow you to focus on the app than the DBA stuff. - [1] https://aws.amazon.com/rds/ https://aws.amazon.com/rds/ - [2] https://azure.microsoft.com/en-us/services/postgresql/#overview https://azure.microsoft.com/en-us/services/postgresql/#overv... - [3] https://www.linode.com/products/postgresql/ https://www.linode.com/products/postgresql/
  - riku_iki 4y ago
    > pay a larger premium for it yeah, premium is very large if I want to do a lot of data crunching (lots of iops, cores and ram is needed), plus latency/throughput to those services will be much higher than to my local disk through PCIE bus.
- eropple 4y ago
  You can set up Postgres with three lines of bash and a Unix socket. It is more work; I wouldn't say it's appreciably more work, and if you need more rigorous data modeling (I frequently do) then it's worth considering if this is the mode in which you'd like to run your data stores (I wouldn't, but latency isn't the most critical thing for stuff I do).
  - riku_iki 4y ago
    I think it is more than that. Steps will be: - sudo apt install postgres (more steps are needed if you are not satisfied with OS default version) - sudo su postgres - psql -> create db, user, grant permissions - modify hba.conf file to change connection permissions - modify postgresql.conf to change settings to your liking because defaults are very out of touch with modern hardware Another options would be to build your docker image, but I am not good with this.

rco8786 4y ago

yes! It seems to me (or at least, I'm hoping this is happening) that the pendulum is swinging back to simple deployments and away from AWS/Cloud/K8s for every tiny little app. One thing I'd love to see is that the 'default' MVP deployment is your code + SQLite running on a single VM. That will scale well into "traction" for the vast majority of applications people are building.

namecheapTA 4y ago
I have a fully working SaaS with 30+ companies paying, with 20 employees each on average. I wrote it as a self taught programmer via YouTube and stackoverflow. It is hosted on a $2 a month namecheap sharedhosting. Php back, jQuery front. I use cpanel to set things up. It runs faster than 90% of webapps on the internet.
- lazide 4y ago
  Yup, I believe it. As an industry 90% of companies are spending 90% of their time on crap only the remaining 10% of the companies actually need. Good resume fodder though I guess.
- rco8786 4y ago
  I believe it!
- downut 4y ago
  Do you implement something akin to Role Based Access Control for those 30x20 employees? If so where do you store and manage that data? I have zero experience with this but I am very curious how people do it in sqlite.
  - ElectricalUnion 4y ago
    Not OP, but like any other database? Even if the database supports it, you usually should not have users directly logged in the database anyways, so whenever the database "has" or "lacks" access control, it doesn't matter. What does matter however, is enforcing parametrized queries everywhere. Unless all the db handles you pass to the client handling code are read-only, chaos will ensure from the DDL permissions. https://xkcd.com/327/ https://xkcd.com/327/

masklinn 4y ago

It's funny because to me it says pretty much the opposite: because sqlite has no server it works entirely in-process, whereas the client will be talking to postgres over a unix socket, or possibly a full-blown network socket. Plus it seems to be a readonly workload, something for which posgres is not ultra relevant (not that it doesn't work, but...).

Beltiras 4y ago

There are enough SQL oopsies in this article to show that you should NEVER use sqlite for any production except as filter databases or other sort of throwaways. RDBMSs are there to enforce constraints on data.

spopejoy 4y ago
RDBMS constraints are oversold usually. For instance it is usually the case that things like foreign-key constraints are avoided in production because they cause performance and maintenance nightmares. Widely-used constraints like UNIQUE are production-quality in SQLite.
ElectricalUnion 4y ago
> NEVER use sqlite for any production > RDBMSs are there to enforce constraints on data. If you did not ask SQLite to enforce type constraints on relations (with STRICT), it will work in the relaxed, backwards-compatible expected behavior of previous versions of SQLite. That being said, if you want actual validation, you probably need more complex CHECK expressions anyways with your business rules, and those work by default on any database. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html * unless you're a madman that runs things with "PRAGMA ignore_check_constraints = false;" enabled or equivalent; in that case, no DB can help you.
fmajid 4y ago
That’s silly, SQLite is the single most widely deployed database engine in the world, possibly the most deployed software, period. It’s unit test suite is second to none. It’s reliable enough that SMF, Solaris’ boot and service management (sort of like systemd) depends on it. That does not mean it is applicable to all databases use-cases, but production-grade it undoubtedly is.
- ThinkBeat 4y ago
  How many installations SQLite has is not that relevant in a discussion of SQLite vs full RDMS. By your example, a bank should use SQLite because it is deployed widely. You have to look where they are deployed and the use cases. They also come with dramatically different tooling which make sa huge difference. More tooling does not mean that a full RDMS is a better choice: It is just about what you need, what use case you need to cover and what industry you are in.

avinassh 4y ago

hmm in that case I would probably pick Postgres because despite the added network call, it is still faster than an embedded DB.

ormaaj 4y ago
As I understand it the actual low-level database interface may be a shared library mechanism in either case. At least, for a local database I'm pretty sure that's a function of the connection type. You're still dlopen()-ing an ODBC library and the underlying glue between that and the rest of the userspace DB engine parts can be implemented in many ways. If there's a socket involved it would probably be AF_UNIX

lazide 4y ago

Uh, most distributed environments you have no choice but to use a server, because you’ll have clients on a (relatively) uncontrollable set of other machines. If you’re writing something to run in an embedded/client app environment, then yeah why would you use Postgres for your one machine? You could, but it’ll add a lot of moving parts you’ll never need and probably don’t want (like remotely accessible network ports you’ll need to secure)

BeefWellington 4y ago

Sure, you can start there. Once you scale though you're going to have lots of fun.

neverartful 4y ago
For many applications you're right. However, in the case of a dashboard for blog articles I don't think it could ever come to that.
ed25519FUUU 4y ago
I’ve never worked on a project that was scaled out that didn’t require at least moderate DB maintenance, such as a large migration or sharding. A migration is all but expected. Might as well make things easy on yourself for the first year and start with SQLite.
marginalia_nu 4y ago
We're talking about data from a blog, though. It's not like it's going to scale to have terabytes of entries anytime soon.

WuxiFingerHold 4y ago

I disagree: Postgres and SQLite are very different in several aspects, e.g. - types and enforcing type safety - handling concurrency and transactions (available isolation levels) - functions / procedures - array support Postgres can be much more valuable than just using it for "relational data storage". Its features can be a good reason for choosing it from the beginning.