Databases Code Diary Entries

This is part of the Semicolon&Sons Code Diary - consisting of lessons learned on the job.

• Always include timestamps
• Avoid enumerating column names when expanding a select statement
• Basic postgres specific CLI commands
• Best practices with data migrations
• Check read and write works after changing any DB settings
• Data migration scripts should take into account recently introduced delegations
• Difference between has one belongs to and has many
• Double check you are in correct DB
• Double quotes are not supported in SQL
• Encoding pitfalls when exporting and importing data
• Ensure correct priviledges when creating tables in shared DB
• Find records with no associated records using outer joins
• Has and belongs to many tables often need unique indexes
• Has many through and has and belongs to many are substitutes
• Having clauses may not be able to use select aliases
• How to denormalize and represent an associated record on the same table
• How to get complete postgres logs
• How to use transactions to reduce risk in major data modifications
• Like is case sensitive
• Never add a foreign key without considering cascades
• Notes on on delete clauses
• Optimistic vs pessimistic locking
• Pgbouncer
• Prefer richer data to booleans
• Readonly DB access
• Redis
• Soft delete gotchas
• Some intuitions for understanding joins
• SQL general
• SQL is strict about argument order
• SQL performance
• SQL query for records with at least one associated record
• Tables containing referenced foreign keys will not be dropped by default
• Use DB locks when simultaneous work may break DB constraints
• Use maintenance mode for migrations
• What is a correlated subquery
• What is a HBA
• What to do when postgres suddenly will not start
• When doing raw SQL updates do not forget timestamps
• When you want to group subsets of rows by one column you probably dont want group by
• Whenever possible make fields not null from day 1