Relational database theory - KeynesYouDigIt/Knowledge GitHub Wiki

Storage Structure

A massively important question is the purpose of your database. https://github.com/KeynesYouDigIt/Knowledge/wiki/1.3-Storage-and-retrieval#transaction-processing-or-analytics

Relational systems can make good warehouses, especially if you enable Column Oriented Storage (possible in Postgres). Some warehouses and most lakes or lake-like systems depart.

A good rule of thumb is - the more structured your data, the more your database can look like a SQL database.

Indexing

Indexing at it's core is just like an index in a book - it prevents us from scanning every single piece of content when searching for something specific. In the relational context, most indexes are B-trees for this sort of data https://github.com/KeynesYouDigIt/Knowledge/wiki/1.3-Storage-and-retrieval#b-trees

But many can be supported! https://www.postgresql.org/docs/13/indexes-types.html

PostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST, GIN and BRIN. Each index type uses a different algorithm that is best suited to different types of queries. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations. -- pg docs