website·Indexed 33 days ago
https://squawkhq.com/A linter for Postgres migrations designed to prevent downtime and data loss. Squawk analyzes SQL migrations for dangerous operations, such as those requiring ACCESS EXCLUSIVE locks, missing timeouts, or non-robust statements, and provides guidance on safe alternatives.
Adding a column with a default value can trigger a table rewrite, which requires an ACCESS EXCLUSIVE lock and blocks all reads and writes.
VOLATILE default does not require a table rewrite.VOLATILE default always causes a table rewrite regardless of the Postgres version.Playground: https://play.squawkhq.com VSCode Marketplace: https://marketplace.visualstudio.com/items?itemName=sbdchd.squawk
When using ALTER TYPE ... ADD VALUE in PostgreSQL, omitting the BEFORE or AFTER keywords appends the new value to the end of the enum type. This can break queries that rely on enum ordering (e.g., using the < or > operators), as new values will be considered 'greater than' all existing values regardless of their logical meaning.
To ensure a new value is placed correctly within the existing sequence, explicitly specify its position using BEFORE 'existing_value' or AFTER 'existing_value'.
-- Existing type
CREATE TYPE status AS ENUM ('draft', 'submitted', 'approved', 'rejected');
-- Query relying on order
SELECT * FROM papers WHERE status < 'approved';
-- INCORRECT: Appends to the end, making 'pending_review' > 'approved'
ALTER TYPE status ADD VALUE 'pending_review';
-- CORRECT: Inserts value in the specific logical position
ALTER TYPE status ADD VALUE 'pending_review' BEFORE 'approved';
-- OR
ALTER TYPE status ADD VALUE 'pending_review' AFTER 'submitted';/data inside the container.# Assuming you want to check sql files in the current directory
docker run --rm -v $(pwd):/data ghcr.io/sbdchd/squawk:latest *.sqlDROP TABLE command. It is strongly recommended to back up the table before deletion to allow for future restoration if needed.DROP TABLE mytable;CREATE INDEX CONCURRENTLY). To make migrations robust and idempotent, use guards like IF NOT EXISTS or IF EXISTS so that the migration can be run multiple times without erroring if some steps have already been completed.ALTER TABLE statement requires an ACCESS EXCLUSIVE lock, which blocks all reads and writes to the table while the index is being built. To avoid this downtime, create the unique index concurrently first, then attach it as a constraint. The ACCESS EXCLUSIVE lock is only held for a brief moment during the metadata update in the second step.-- 1. Create the unique index concurrently (allows reads and writes during build)
CREATE UNIQUE INDEX CONCURRENTLY account_pk_idx ON account (id);
-- 2. Add the constraint using the existing index (blocks reads/writes only briefly for schema update)
ALTER TABLE account ADD CONSTRAINT account_pk PRIMARY KEY USING INDEX account_pk_idx;NOT VALID clause when creating the constraint. This allows the constraint to be applied immediately to new rows and updates without blocking writes. Subsequently, call VALIDATE CONSTRAINT to verify existing data; this step still performs a table scan but only requires a SHARE UPDATE EXCLUSIVE lock, allowing concurrent writes to continue.-- 1. Add the constraint without validating existing data (non-blocking)
ALTER TABLE "accounts" ADD CONSTRAINT "positive_balance" CHECK ("balance" >= 0) NOT VALID;
-- 2. Validate existing data (non-blocking for writes)
ALTER TABLE accounts VALIDATE CONSTRAINT positive_balance;DROP INDEX command acquires an ACCESS EXCLUSIVE lock on the table, which blocks all other reads and writes until the operation completes. To avoid this downtime, use the CONCURRENTLY option. This allows the table to remain accessible while PostgreSQL waits for conflicting transactions to finish.-- Avoid this (blocks reads and writes):
DROP INDEX "app"."email_idx";
-- Use this (allows reads and writes):
DROP INDEX CONCURRENTLY "app"."email_idx";