• Index nearly everything, index early. Not indexing results in poor performances. Indexing too late can create downtime or inconsistent data. Do not consider removing/avoiding index to preserve disk space, buy more space instead. Remember that index may have an impact on write performance though.

• PostgreSQL is your best choice, it has a skilled community, including StackOverflow heroes. There is no MySQL, MariaDB, or SQLite user group where I live.

• ORM are great for simple CRUD operation. Don't use it to query specific stats or any complex query, use raw queries. BTW, for TS/JS devs, Prisma 3 is cool, Sequelize is obsolete.

• SQL syntax is case insensitive, use lower case everywhere (and underscores), do not name your tables with camelCase. ALSO, YOU DON'T HAVE TO YELL. select id from users is the same as SELECT id FROM users. For string comparison, case sensitivity depends on collation.

• Never miss a foreign key. Implicit links and relations between tables should be avoided.

• Follow a pattern when naming tables and columns. Example: users, posts, comments (plural). post_user, comment_post, comment_user for junction tables (singular + alphabetical order). id for primary key, id_user, id_comment, and id_post (singular + id first) for foreign key. Reading a database schema is hard for newcomers. Be super consistent when naming tables.

• 1 model ≠ 1 table. Unfortunately, when designing a database you have to think at a lower level than "models" and "objects". You will have to deal with technical tables.

• Use the right data type. Do not create "custom" data types defined by an implicit contract.

• JSONB is great but don't overuse it. Also, learn to query it it's complex yet powerful. When you iterate a lot on designing a schema, you can use JSONB to avoid modifying the schema over and over.

• PostgreSQL does not scale automatically. Expect migrations and downtime.

• CTE is more readable than subqueries, you can read it from the top to the bottom. Also, you can understand the way it was designed.

• Keep tables schema simple, avoid patterns such as EAV that makes tables hard to understand and read; try to keep most tables as simple as vertical CSV sheets, with one line for each record.

• Don't be afraid of writing long and verbose queries to address a specific need, it's often more efficient.

• The order of the words in a query is not the order in which they are evaluated. Know the order of execution: from, where, group by, having, order by, select, limit.

• 120 tables are way too much, even for big products. You can build a robust application with 25 tables.

Thank you for reading 🙏