Preventing Postgres search_path Drift During Spring Boot Migrations

by

Summary

During a production migration, an application failed to locate newly created tables, leading to UndefinedTableException errors in the Spring Boot service. The investigation revealed that the tables were being created in the public schema by default, while the application’s connection string and search path were configured to look for a specific tenant-specific schema. This mismatch caused a complete breakdown in database communication despite the tables physically existing in the database.

Root Cause

The fundamental issue is the behavior of the PostgreSQL search_path and the implicit assignment of the default schema.

  • Implicit Schema Assignment: When a CREATE TABLE command is issued without a schema prefix (e.g., CREATE TABLE users (...)), PostgreSQL does not error out. Instead, it assigns the table to the first schema listed in the current session’s search_path.
  • The public Default: By default, the search_path is set to "$user", public. If the user does not have a schema named after their username, the table is dumped into the public schema.
  • Search Path Mismatch: In multi-tenant or highly structured environments, engineers often set a specific schema via the connection string. However, if the migration tool (like Flyway or Liquibase) or a manual DDL script executes without explicit schema qualification, the tables “drift” into the public schema.

Why This Happens in Real Systems

In complex distributed systems, this occurs due to environmental drift and tooling inconsistencies:

  • Migration Tool Divergence: A developer might run a migration script locally where the default schema is public, but the production environment uses a specialized schema for security isolation.
  • Connection String Ambiguity: Many developers assume that setting currentSchema=myschema in a JDBC URL handles all implicit logic, but certain administrative tasks or secondary connections might default back to the database-level settings.
  • Privilege Escalation: Higher-privileged users (like postgres or superuser) may have different default paths than the limited application user, leading to tables being created in locations the application cannot see.

Real-World Impact

  • Data Siloing: Data is written to the public schema while the application logic expects it in tenant_a, leading to silent data loss from the application’s perspective.
  • Deployment Failures: CI/CD pipelines may pass because the “table creation” step succeeds, but the “integration test” step fails because the application cannot find the tables.
  • Operational Overhead: SRE and DBA teams waste significant time debugging “missing tables” that are actually present but “hidden” in the wrong namespace.

Example or Code

-- Scenario: The application expects everything in 'app_schema'
-- Current session search_path: 'app_schema', 'public'

-- This command is dangerous because it is unqualified
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    amount DECIMAL
);

-- Check where the table actually went
SELECT table_schema, table_name 
FROM information_schema.tables 
WHERE table_name = 'orders';

-- If the search_path was misconfigured, the result will be:
-- | public | orders |

How Senior Engineers Fix It

Senior engineers move away from implicit behavior and toward explicit declaration:

  • Fully Qualified Names: Always use schema_name.table_name in all DDL and DML scripts. This removes all ambiguity regardless of the search_path.
  • Strict Migration Configuration: Configure migration tools (Flyway/Liquibase) to explicitly set the schema via the schemas property or by executing SET search_path TO my_schema; at the start of the migration script.
  • Database-Level Defaults: Set the search_path at the user level rather than the session level to ensure any connection made by the application service automatically points to the correct schema. 
    ALTER ROLE app_user SET search_path TO app_schema, public;
  • Infrastructure as Code (IaC) Validation: Implement linting rules in the CI/CD pipeline that flag any CREATE statements missing a schema prefix.

Why Juniors Miss It

  • Reliance on “Magic”: Juniors often rely on the framework (like Spring Data JPA) to “just work,” assuming the connection string handles all architectural nuances.
  • Local vs. Prod Parity: They often test against a local Docker container where everything is in public, failing to account for the strict schema isolation used in production.
  • Lack of SQL Fundamentals: They may focus on the Java/ORM layer (the Entity mapping) while neglecting the underlying database engine’s namespace resolution logic.

Leave a Comment