Defer to the PostgreSQL wiki about what not to do.
The PostgreSQL Wiki has a handy “Don’t Do This” page. While there are exceptions when most of these discouraged practices may be safe or even necessary, we should avoid them unless we’re sure we’re the exception.
Enforce data integrity at the database level whenever possible.
PostgreSQL gives us a range of tools to ensure invalid data isn’t just unlikely but impossible:
unique: truein migrations) to prohibit “duplicate” data. For instance, if it doesn’t make sense for a Lesson to be tagged with “Sales” twice, we want a unique composite index on the
null: falsein migrations) to guarantee necessary relationships (e.g. make
company_idnon-nullable on tables that must be associated with a company) or attributes (if a
titleis required, make it
FOREIGN KEYconstraints to ensure that references to other tables like
company_idpoint to records that actually exist, and specify
NULLIFYbehaviors when those records are removed. (Be mindful of performance when adding foreign key constraints: see this migration for an illustration the challenges and a way around them.)
Bugs resulting from invalid data can be tricky to detect and it can be time-consuming to clean up—best to prevent the possibility of it in the first place.
Avoid nullable booleans.
We expect boolean values typically to be either
false. Make them
NOT NULL with a default value when we don’t intend to make use of the default third
Use transactions only when necessary.
Transactions are sometimes essential for ensuring data integrity, but when we include operations within the transaction that don’t need to be atomic, it makes transactions take longer and increases the likelihood of locks, deadlocks, and other performance issues resulting from slow queries (example).