Discussion on: SQL tips for beginners; what I learned after 10+ years of occasionally writing queries and schemas as a web dev

Index nearly everything, index early

Don't go nuts with this. Too many indexes can slow down writes.

Also:

• When things get complex (and they often will) remember that you can create views and perform reads (and sometimes writes) with those views with a model/SQL.
• Be careful with cascade actions. This can make a seemingly simple action incredibly long-running. If you have header tables linked to detail tables and your detail tables have millions of records in them or cascade to other tables that do, I encourage you to use soft deletes at the header-level instead. Once that is done, you can determine if and when to actually delete the data.
• If you have queries that have to perform multiple aggregations against a large set of data, you may want to investigate partitioning.
• Don't be afraid of VACUUM operations. It is often better to have more frequent, shorter vacuum executions vs fewer, longer ones.
• For more modern versions of PostgreSQL, investigate your server specs (the server itself, that is: memory CPU, etc) if you have enough, explore increasing some work memory and adding more parallel workers.
• Check out using the nice data types PostgreSQL provides such as range types. Those can be used with special indexing to provide complex operations like range overlap and in some cases nearest neighbor, if I remember correctly.
• If you are writing SQL that performs a manipulating operation (INSERT, UPDATE, DELETE), you can use the RETURNING clause to return the (full or partial) rows that were affected.
• Think of doing you operations in batches where possible. This can be more efficient when processing large sets of data.
• When writing your code, commit at the highest level possible. Don't commit in a low-level loop, unless you really like your code to run slow and be I/O intensive.

• Don't forget you can cast certain types to others with a double colon (::). Ex:
  

  select integer_col::text from table;
  

• Understand that sometimes anti-join patterns using EXISTS or NOT EXISTS with a subquery can sometime execute faster that a LEFT/RIGHT JOIN where col is null.

-- 40-year DB developer