What are the pros and cons of using Postgres over SQLite for tiny and small projects? 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?
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.