シオン
@shion
Common DB schema change mistakes | Postgres.AIhttps://postgres.ai/blog/20220525-common-db-schema-change-mistakes

Common DB schema change mistakes | Postgres.AI

Three Categories of Mistakes:

1. Concurrency-related: These involve issues with locking mechanisms, such as holding locks for too long, blocking other queries, and failing to acquire necessary locks. 2. Logical Errors: These include schema mismatches, incorrect assumptions about data, and misuse of features like IF NOT EXISTS. 3. Miscellaneous: This category covers issues specific to certain features or configurations, such as hitting statement_timeout, using inefficient data types, and neglecting vacuuming.

Specific Cases and Recommendations:

* Schema Mismatch: Ensure proper workflow and avoid using IF NOT EXISTS as a workaround. * Statement Timeout: Test changes on realistic data volumes to identify potential timeouts. * Unlimited Massive Changes: Break down large updates/deletes into smaller batches to avoid locking issues and excessive resource consumption. * Long-held Exclusive Locks: Minimize the time spent holding exclusive locks to prevent blocking other queries. * DDL + Massive DML: Separate DDL and DML operations into different transactions to avoid prolonged locking. * Waiting for Exclusive Locks: Implement retry logic with low lock_timeout to prevent blocking issues when acquiring locks. * Careless FK/CHECK Constraint Creation: Use the NOT VALID option and validate in a separate transaction to avoid full table scans and locking issues. * Careless NOT NULL Addition: Utilize techniques like adding a default value or a temporary CHECK constraint to avoid full table scans when adding NOT NULL constraints. * Careless Data Type Changes: Create a new column, backfill data, and switch applications to the new column instead of directly altering the data type. * Careless Index Creation/Deletion: Use CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY to avoid locking issues. * Renaming Objects: Implement strategies to avoid inconsistencies between application code and the database schema during renaming. * Adding Columns with Defaults: Leverage the efficient column creation with defaults feature in Postgres 11 and later. * Leftovers from Failed Index Creation: Clean up invalid indexes left behind by failed CREATE INDEX CONCURRENTLY attempts. * 4-byte Integer Primary Keys: Use int8 for primary keys to avoid potential limitations with large tables.

General Recommendations:

* Extensive Testing: Test schema changes thoroughly with realistic data volumes, paying attention to lock durations and potential blocking issues. * Automation: Implement robust automation for deployment and utilize tools like Database Lab Engine for testing and analysis. * Learning and Sharing: Learn from others' experiences and share your own knowledge to improve schema change practices.

シオン
@shion 1 month ago
115

Post a new comment...

No comments yet.

Related

Laurence Tratt: What Factors Explain the Nature of Software?

A triad of interacting factors that define the nature of software:

1. Liminal state: Software occupies a state between the constraints of the physical world and the fantasy world of unlimited possibilities. This leads to a mix of hard and soft constraints, making it difficult to determine what is possible and what is not. 2. Circular specification problem: It is impossible to fully specify software before building it, as the act of creating software is also an act of specification. This leads to gaps between our ideas and the reality of the software. 3. Observer effect: The act of observing software in action changes what we think the software should be. This leads to changes in requirements and specifications, and can result in extra work and friction.

tratt.net

how is prangent formed

the most ridiculous spelling mistakes

youtu.be