SchemaSmith PostgreSQL Enterprise Documentation

Forge Deeper with SchemaQuench

Gain a deeper understanding of SchemaQuench.

Overview Understanding QuenchSlots The Quench Process Resources
SchemaQuench

Overview

SchemaQuench is a state-based, opinionated database migration tool inspired by the principles of infrastructure-as-code tools like HashiCorp's Terraform. It enables you to define the desired end state of your PostgreSQL databases using metadata and applies these definitions to target servers, ensuring consistency and repeatability across environments. We refer to this process as quenching.

Understanding the QuenchSlots

Quenching a product happens as a series of ordered events. Each slot must complete successfully before we move to the next except as noted below.

  • Validate the server when a Product validation script is provided.
  • Validate the server baseline version when a Product level script is provided.
  • Resolve any product level Query Tokens.
  • Run any Before product scripts. See Configurable Script Folders
  • Quench each template based on the TemplateOrder defined in the Product.
    • Validate the baseline version if a script is provided in the template definition.
    • Apply any Object scripts without query tokens. Object scripts located in folders configured at the Template level with the Object quench slot. Object scripts will loop to resolve dependencies each time they are applied.
    • Quench missing tables and columns using SchemaSmith.MissingTableAndColumnQuench.
    • Apply any remaining Object scripts without query tokens.
    • Resolve template query tokens.
    • Apply any Migration scripts from folders defined with the Before quench slot.
    • Quench modified tables using SchemaSmith.ModifiedTableQuench.
    • Apply any remaining Object scripts after resolving query tokens.
    • Apply any Migration scripts from folders defined with the BetweenTablesAndKeys quench slot.
    • Quench missing and modified indexes and constraints using SchemaSmith.MissingIndexesAndConstraintsQuench.
    • Apply any Migration scripts from folders defined with the AfterTablesScripts quench slot.
    • Apply any remaining Object scripts plus any scripts in folders defined with the AfterTablesObjects quench slot. This is the final chance to resolve script dependencies, and any remaining scripts that fail to apply in this pass will be reported as errors.
    • Perform Data Delivery for any tables with those properties defined.
    • Apply any table data scripts from folders defined with the TableData quench slot.
    • Apply any Migration scripts from folders defined with the After quench slot.
    • Stamp the template version if a script is provided in the template.
  • Run any After product scripts.
  • Stamp the product version if a script is provided in the product.

Tip

Object scripts are always applied and Quench will loop through them to resolve dependencies if possible. SchemaQuench will only fail if we cannot resolve a dependency multiple times after tables have been updated.

The Table Quench Process

The process of quenching tables is broken into three separate slots in the Enterprise SchemaQuench to allow more robust and granular control over the entire process.

Quench missing tables and columns

  • Parse the JSON definitions into temp tables and keep them for use in the later quench steps below
  • Handle Table Renames
  • Handle Column Renames
  • Attempt custom table restore for tables being added in case they were custom dropped previously if SchemaSmith.CustomTableRestore exists in the database
  • Add New Tables
  • Add New Physical Columns

Quench modified tables

  • Validate Table Ownership. We will error if this product definition conflicts with a table already "owned" by another product.
  • Drop tables removed from the product using SchemaSmith.CustomTableDrop when it exists, otherwise DROP TABLE
  • Detect Existing Column Definitions
  • Detect Existing Index Definitions
  • Detect Existing Foreign Key Definitions
  • Detect Existing Check Constraint Definitions
  • Drop Modified or Removed Foreign Keys
  • Drop Modified or Removed Check Constraints
  • Handle Renamed Indexes And Unique Constraints
  • Identify Unknown, Removed, and Modified Indexes to Drop
  • Drop Unknown, Removed, and Modified Indexes. Unknown indexes are only dropped when DropUnknownIndexes is set to true at the product level.
  • Drop Columns No Longer Part of The Product Definition
  • Drop Generated Columns Referencing Columns That Are Changing Data Type
  • Drop Columns Changing to Generated
  • Drop Views and Rules Dependent on Modified Columns
  • Add New Physical Columns Switched From Generated
  • Alter Modified Columns
    • Data Type
    • Collation
    • Expression
    • Nullability
    • Default
    • Storage Type
    • Compression
  • Fixup Table Attributes
    • Logged/Unlogged
    • Row Level Security

Quench missing and modified indexes and constraints

  • Add New Computed Columns. This is deferred here to allow one last chance to create objects that they might depend on.
  • Add Missing Indexes
  • Add Missing Defaults
  • Add Missing Check Constraints
  • Add Missing Foreign Keys
  • Add Missing Product Ownership to tables
  • Remove Product Ownership for Obsolete Tables
  • Add Missing Index Product Ownership
  • Remove Product Ownership for Obsolete Indexes

Hooks for custom table drop and restore processes

When SchemaQuench detects that a table should be dropped, i.e., it was once defined in the product but has been removed, it looks to see if there is a stored procedure named SchemaSmith.CustomTableDrop. If there is such a procedure, it calls it passing the schema and name of the table as the first and second parameters respectively and skips actually dropping the table.

Similarly, near the beginning of the quench process, an attempt is made to restore tables that had been custom dropped previously. This hook expects a stored procedure named SchemaSmith.CustomTableRestore passing the same schema and name parameters.

These hooks allow you to time delay dropping tables or to perform some custom backup procedure or whatever else your process may require.

Additional Resources

SchemaQuench Walkthrough - A step-by-step guide for quenching the sample product. How-to Configurable Script Folders - Make the project folder structure fit your team. Reference Migration Scripts - How to handle edge cases. Reference Data Delivery - Making data deployment easier. Reference Declarative vs Imperative, Which Migration Strategy Is The Best? Video