If you are a novice or even if just a bit rusty with relational databases I recommend:
- Grab a good book or two. The Art of PostgreSQL is one. There are _many_ others. Take a bit of time to find out what suits you. I typically like reading a more theoretical one and a more practical one (with actionable, concrete advice, actual code in it and so on).
- Get a nice tool/IDE/editor plugin that can interactively work with databases, create them, query them etc. Almost all of the editor utilities that you expect from general purpose programming also applies to programming with SQL.
- PostgreSQL is a very powerful, primarily relational database, but there is very little it cannot do. For example it is perfectly feasible to implement a document (JSON) store with it, and convenient to boot. There are also great extensions such as for temporal tables.
- There is a ton of advice, discussion etc. to find, especially around data modelling. Word of caution: Some of it is religious, at least slightly. The book I mentioned above too to a certain degree, but it is still a very good book. Realize that a relational DB can give you a ton of stuff in a performant, declarative manner. But not everything should live in the DB, nor is everything relational in the strictest sense. Be pragmatic.
Source: I've been diving deeper into this topic since a few years and still am. The more I learn and are able to apply, the more I understand why the relational paradigm is so dominant and powerful.
As a young developer I stayed away from traditional relational databases because I thought they were boring and restrictive. I quickly fell in love with them. I realized they painlessly provided all the things I was already doing with data and they were doing it much faster and more reliably.
I hope you have the same kind of experience and enjoy Postgres!
As far as actual advice...
1. The key concept of an RDBMS is that each table typically specifies relationships to other tables. So in order to query data you will typically be joining multiple tables. Sometimes new DB programmers get a lot of enlightenment from the simple Venn diagrams that illustrate the basic types of JOINs: https://www.google.com/search?q=inner+join+diagram ...IMHO, once you get this (fairly simple) paradigm the rest is easy.
2. Database constraints are your friends. At a minimum you specify the types of the columns, obviously, but you can go much farther - specifying NOT NULL constraints, foreign keys, and more complex check conditions. Do as much of this as feasible at the database level. Otherwise it is something you need to code and enforce at the application level, where it will generally be less performant and more prone to coder error. Additionally, any constraints not enforced at the application level will need to be implemented across multiple applications if more than one app uses the database.
The second one above is an example of something that sounds boring and restrictive but really frees you up to do other things as a developer. About a decade ago, the "trend" was to treat RDBMSs as "dumb" storage and do all that stuff at the application level, in the name of being database-agnostic. Opinions remain divided, but I think that was an objectively bad trend. For one thing, folks noticed they hardly ever needed to suddenly switch databases, but there are other reasons as well.
Pg docs are so good I reference them whenever I want to check the SQL standards, even if I'm working on another DB. (I prefer standard syntax to minimize effort moving DBs.)
I stick to standard SQL syntax/features whenever possible as well, but...
Honest question: how often do you switch databases?
I've never really found myself wanting or needing to do this.
Only time I could really see myself wanting to do this is if I was writing some kind of commercial software (eg, a database IDE like DataGrip) that needed to simultaneously support various multiple databases.
> MySQL
It feels particularly limiting to stick to "standard" SQL for MySQL's sake, since they frequently lag behind on huge chunks of "standard" SQL functionality anyway. For example, window functions (SQL2003 standard) took them about a decade and a half to implement.
From the few days I have explored it, it is absolutely incredible, so congratulations for the work done and good luck on keeping the quality so high!