Comparing Dev vs Prod Data: SQL Queries to Spot Environment Drift

Detecting schema drift

-- Run in each environment, then diff the two result sets externally,
-- or via a cross-database query if your tooling supports it
SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, column_name;
-- A column present in one environment's output but not the other,
-- or the same column with a different data_type, is schema drift

Comparing row counts across environments

-- Run in each environment and compare the results
SELECT
  table_name,
  (SELECT reltuples::BIGINT FROM pg_class WHERE relname = table_name) AS approx_row_count
FROM information_schema.tables
WHERE table_schema = 'public';
-- PostgreSQL-specific example using catalog statistics for a fast estimate;
-- use COUNT(*) per table for an exact count on smaller tables

A large, unexplained row count gap on a table that should be roughly in sync (not intentionally sampled or anonymized) is the first sign of a stale environment or a failed sync job.

Checksum-based row comparison

-- Hash each row's combined columns into a single comparable value
SELECT
  id,
  MD5(CONCAT_WS('|', name, email, status, updated_at::TEXT)) AS row_hash
FROM customers
ORDER BY id;
-- Run in both environments; a differing hash for the same id means
-- at least one compared column differs, without checking each individually

Real-world example: a migration that never ran in prod

A feature works correctly in dev but throws a "column does not exist" error in prod. Running the schema drift query in both environments shows a preferred_contact_method column present in dev's customers table but absent from prod's. Checking the migration history shows the migration adding that column was applied manually to dev during local development but was never committed to the shared migration folder — so it never ran against prod through the normal deployment pipeline. The fix is adding the missing migration file, not patching the column directly onto prod by hand, which would just reintroduce the same undocumented-drift problem.

Common mistakes

• Applying schema changes directly to one environment outside the normal migration process, creating undocumented drift.
• Assuming identical schemas mean identical behavior, when dev's stale or sampled data can still mask bugs that only appear against realistic prod-scale data.
• Comparing row counts without accounting for intentional differences, like a dev environment deliberately seeded with a small sample.
• Running checksum comparisons on columns with non-deterministic formatting (like a timestamp with inconsistent precision) that produce different hashes despite representing the same value.