What i was learning

Today i started learning PostgreSQL , the objective was to create a mental model to build a strong foundation and potentially a smooth learning curve for PostgreSQL.

What confused me

Coming from SQLite, confusion was inevitable.The initial assumption was that PostgreSQL would be smooth, that perhaps all i needed to do was change the app configuration from SQLite to PostgreSQL and everything else would remain untouched.

To my surprise, the reality was a complete contradiction . I had to adopt an entirely different mental model.Unlike SQLite, PostgreSQL is not a file , its a database service.
I had to research deeper , I needed to understand why PostgreSQL's service label was crucial.

Grounded on this curiosity, intuition started to kick in and here is what i learned:

What clicked

I had to simplify everything and focus on one key word "Service".
A service in the context of PostgreSQL highlighted an interesting difference between the two (PostgreSQL and SQLite).
SQLite is simply a file inside the application(in my case a flask application).It is inside the application , everything that is concerned with the database is inside that file.

PostgreSQL is a data-service , it is independent of the application and it is initiated with "sudo systemctl start postgresql" in linux. The command initiates PostgreSQL and it continues to run in the background from then , hence the root of its independence from the application.PostgreSQL is self managed, it manages permissions, roles etc .
It does everything SQLite does and more.
What i found fascinating was Role Based Access Control(RBAC).PostgreSQL recognizes roles rather than users. A role is attached with permissions, that means the permissions an application has on the database depends on the role and permission connected to the application. PostgreSQL's equivalent of linux's sudo is postgres, its a database inside Postgesql and it has access to all permissions.Building secure applications stems from this knowledge.

my takeaway

The mental shift was no easy task but the reward was appealing.Linking a database to web security made the complexity of PostgreSQL click.