2

I'm a fullstack junior programmer with a few side projects, every with a few tables and a few hundred or thousand rows at max.

I like to use a dockerized Postgres even for tiny projects, since it's more fully-featured (from small things such as the built-in regexp function, which SQLite does not have, to bigger, such as Row Based Access Control - all of which I can do without, but they are convenient) and my knowledge will carry over to bigger projects. Its memory footprint seems to be pretty manageable even for 1GB VMs as well, as the Postgres container seems to consume around 100/200 MBs at max, therefore, at least as far as my reason goes, it's definitely not a problem resource-wise.

SQLite, on the other hand, obviously uses even less resources and it seems to be heavily implied it should be used for such tiny projects for other reasons I'm not fully aware of.

What would be the potential reasons to use SQLite over Postgres for small projects?

2
  • 1
    I find the lack of proper data types in SQLite enough to not use it if I can avoid it
    – user1822
    Feb 7, 2021 at 13:40
  • Appropriate Uses For SQLite
    – CL.
    Feb 8, 2021 at 12:04

2 Answers 2

2

SQLite drawbacks that matter for an embedded database:

  • Much more bare-bones, less features, data types, etc
  • Optimizer not so smart as postgres

SQLite advantages:

  • In-process

This means no context switching or network communication, much lower overhead for large numbers of fast queries, much faster data transfer between application and database. Also, no locks, since it's single user. The difference is huge.

  • Small memory/code footprint + In-process

Sure you have a gigabyte of extremely slow DDR-4 21666 RAM. But the memory that isn't extremely slow, which means the CPU cache, is tiny. For workloads heavy on random memory access, smaller memory footprint is always better.

enter image description here

  • Database easy to backup

This is important for usability. Backing up the database just means close the app, copy the files. No need to shutdown the server, use pg_dump, etc.

1

The main differences will be the features you need, for the applications you'll be developing and their use cases. It sounds like if your PostgreSQL instances are only consuming up to 200 MBs of memory at a time, you already have tiny databases, and it doesn't really matter too much if you choose one way or the other due to data size, rather it'll be more feature specific which as you realize SQLite is a much simpler database lacking a lot of addition features a full RDBMS like PostgreSQL has.

Personally, I'd say unless the environment you're working in / hosting your data on is constrained to use SQLite like mobile development (because of its portability for offline storage) I'd opt for a full RDBMS system by default.

Not the answer you're looking for? Browse other questions tagged or ask your own question.