sql-programming

Documentation

• Make sure every create statement or CTE has a descriptive comment -- in front of it.
• Write enough comments so you can deduce what was a requirement in the future and not walk in circles.
• Every feature needs to have comprehensive up-to-date documentation near it.

Style

• SQL is lowercase unless instructed otherwise.
• Values in databases and layers should be absolute as much as possible: store "birthday" or "construction date" instead of "age".
• Do not mix tabs and spaces in code.
• Add empty lines between logical blocks.
• Format the code nicely and consistently.

Indexing

• Consider BRIN indexes for very large naturally ordered tables that will be used for ad-hoc range queries.
• If you have a cache table that has a primary key, it can make sense to add frequently read values into including on the same index for faster lookup.