Best practices for database access control covering least privilege, RBAC, just-in-time access, and compliance with SOC 2, HIPAA, and GDPR.

Database Access Control Best Practices

TRUNCATE and DELETE both remove rows, but they differ in speed, rollback behavior, and locking. Compare how they work in PostgreSQL, MySQL, and SQL Server.

TRUNCATE vs DELETE in SQL: Differences Across PostgreSQL, MySQL, and SQL Server

Six database design patterns for production systems: normalization, star schema, EAV, polymorphic associations, multi-tenancy, and anti-patterns.

Database Design Patterns Every Developer Should Know

pgvector adds vector storage and similarity search to PostgreSQL. Learn how to install it, query embeddings, build indexes, and decide when it is enough.

pgvector: How to Add Vector Search to PostgreSQL

Upsert inserts or updates a row based on key conflicts. Learn MySQL INSERT ON DUPLICATE KEY UPDATE, PostgreSQL ON CONFLICT, and SQL Server MERGE.

Upsert in SQL: INSERT ON DUPLICATE KEY vs ON CONFLICT Explained

How codifying the full database development lifecycle — not just DDL — empowers LLMs and AI Agents.

From Schema as Code to Schema as Context

A practical guide to governing AI agent access to enterprise databases — covering authentication, authorization, query control, audit logging, and data masking.

How to Govern AI Agent Access to Enterprise Data

Schema change detection and notification are essential for data pipelines. This article explains why a simple column rename can break your data pipeline and how to prevent it.

Schema Change Detection and Notification: Why a Simple Column Rename Can Break Your Data Pipeline

A comprehensive guide to understanding and analyzing Postgres query plans.

Postgres Query Plan: How to Read and Fix Performance Issues

This article explores the anatomy of Postgres permissions ERROR 42501.

The Anatomy of Postgres Permission ERROR 42501

Postgres Logs Explained: A Practical Guide for Production

A guide to multi-environment deployments in databases.

Database Multi-Environment Deployments - The Challenges and Patterns

How to set up database audit logging for SOC 2, HIPAA, and ISO 27001 compliance across PostgreSQL, MySQL, SQL Server, and Oracle.

Database Audit Logging Best Practices for Compliance

A practical guide to HITRUST database compliance covering access control, audit logging, change management, and data protection.

Database Compliance for HITRUST: Controls, Risks, and Best Practices

Learn why requiring a primary key is important and how the "Require Primary Key" review rule protects your production database.

SQL Review Rule Explained - Require Primary Key

Learn why enforcing NOT VALID in CHECK constraints is important and how the "Enforce NOT VALID in CHECK" review rule protects your production database.

SQL Review Rule Explained - Enforce NOT VALID in CHECK

Postgres Audit Logging Guide

Learn why CASCADE operations in SQL can lead to catastrophic data loss and how the "Prohibit CASCADE" review rule protects your production database.

SQL Review Rule Explained - Prohibit CASCADE

A comprehensive guide on migrating from Supabase to AWS, covering database, auth, storage, functions, realtime, and networking.

How to Migrate from Supabase to AWS

Enterprise database management is a comprehensive approach to organizing, securing, and optimizing database systems and workflows at scale, ensuring data integrity, availability, and compliance across large organizations.

What is Enterprise Database Management (EDM)?

What is Postgres Transaction ID (XID) Wraparound and how to monitor and prevent it.

Postgres Transaction ID (XID) Wraparound

A comprehensive guide on building a CI/CD pipeline for automated database schema migration, covering best practices, tools, and implementation strategies.

How to Build a CI/CD Pipeline for Database Schema Migration

A comprehensive overview of the essential practices and principles necessary for achieving LGPD (Brazilian General Personal Data Protection Act), compliance within database operations, and its difference with GDPR

Database Compliance for LGPD: Implications and Best Practices

Explain what is Postgres LATERAL JOIN and how to use it.

What is Postgres LATERAL JOIN?

Introduction to MySQL Online DDL and its three algorithms: INSTANT, INPLACE, and COPY

MySQL Online DDL: A Practical Guide

A comprehensive guide to understanding the dilemma of singular vs. plural table naming in SQL databases.

SQL Table Naming Dilemma: Singular vs. Plural

An engineering perspective to evaluate Postgres case sensitivity

Postgres Case Sensitivity Explained

A comprehensive guide to understanding pg_database_owner in Postgres

What is pg_database_owner in Postgres

Which operation causes a Postgres table rewrite

Which Postgres Operation causes a table rewrite

An engineering perspective to evaluate Postgres row-level-security footguns

Common Postgres Row-Level-Security footguns

An engineering perspective to evaluate Postgres rollback strategies

Postgres Rollback Explained

Learn how Postgres locks affect schema migrations and which DDL operations conflict with DML to achieve zero-downtime deployments.

Postgres Schema Migration without Downtime Best Practice

A checklist of gh-ost limitations and potential gotchas

gh-ost Limitations You Need to Know Before Production

5 quick wins for application developers to improve Postgres reliability and make both their own and DBA/infrastructure teams' lives easier.

5 Postgres Production Tips for Application Developers

Learn how to use CREATE INDEX CONCURRENTLY in Postgres to build indexes without blocking writes. Pitfalls, safety checks, and performance tips.

How to Use Postgres CREATE INDEX CONCURRENTLY

Learn how Bytebase enables organizations to implement comprehensive Database Security Lifecycle Management, unifying security and operations from development to production.

Database Security Lifecycle Management

This guide focuses on HIPAA compliance at the database layer, providing healthcare organizations and database teams with practical guidance for implementing HIPAA Security Rule requirements.

HIPAA Data Security and Retention Requirements

Compare the online index creation difference between Postgres and MySQL

Postgres vs. MySQL: Online Index Creation

SOC 2 Data Security and Retention Requirements for Database Systems and How Bytebase Helps

SOC 2 Data Security and Retention Requirements

Compare the different indexing options between Postgres and MySQL

Postgres vs. MySQL: Indexing Options

Compare the JSON support difference between Postgres and MySQL

Postgres vs. MySQL: JSON Support

While PostgreSQL RLS is a powerful feature, it has inherent design limitations that hinder broader adoption. In this post, we’ll examine these limitations and explore alternatives.

PostgreSQL Row-level Security (RLS) Limitations and Alternatives

Compare the DDL transaction difference between Postgres and MySQL

Postgres vs. MySQL: DDL Transaction Difference

Understanding DynamoDB Pricing (2025)

Complete guide to AWS Aurora pricing: instance types and costs, Aurora Serverless v2 pricing, I/O-Optimized vs Standard, reserved instance savings, and real-world cost examples. Learn how to optimize your Aurora costs.

AWS Aurora Pricing Explained: Instance Types, Serverless, and Cost Optimization (2026)

Understanding AWS RDS Pricing (2025)

Explain why MySQL provides a variety of timeout settings.

MySQL Timeout Explained

Learn the difference between DDL and DML in SQL databases. DDL (Data Definition Language) defines database structure with CREATE, ALTER, DROP. DML (Data Manipulation Language) manages data with SELECT, INSERT, UPDATE, DELETE. Includes examples and comparison table.

DDL vs DML in SQL: What is Data Definition Language and Data Manipulation Language?

The top 10 database schema design best practices with code snippets

Top 10 Database Schema Design Best Practices

Database version control is the practice of managing and tracking changes to a database schema and its associated data over time. Why best practice matters? Mitigate risks, improve collaboration, and ensure data audibility.

Database Version Control Best Practice

Data access governance is a critical component of any organization's security strategy. By implementing the right tools and policies, you can protect sensitive data, ensure regulatory compliance, and maintain a secure and productive work environment.

Data Access Governance: Why It Matters and How to Get it Right

Database Security Compliance is a critical framework that not only mitigates legal and financial risks but also enhances overall data governance.

Database Security Compliance

A comprehensive guide to Change Advisory Boards (CAB), including their purpose, limitations, popular tools, and best practices for database change management.

What is Change Advisory Board (CAB)?

Understanding production databases, their critical importance, common deletion mistakes, and best practices for safeguarding these essential systems.

What is Production Database

Give an overview about Database Activity Monitoring and its difference from Database Monitoring

What is Database Activity Monitoring (DAM)?

A deep dive into multi-tenant database architecture patterns, exploring pros and cons of shared vs. isolated models, and how to choose the right approach for your SaaS application.

Multi-Tenant Database Architecture Patterns Explained

How to integrate ServiceNow, GitHub with Bytebase to Automate Database Changes.

ServiceNow and GitHub Integration for Database Change Automation

Pros and Cons between choosing uuidv1, uuidv4, uuidv7

How to Choose Database UUID?

What are the differences between Primary Key and Foreign Key

Primary Key vs Foreign Key: What are the Differences?

Explain write ahead logging in database and the implementation difference between different databases.

What is Write Ahead Logging (WAL)

The Just-in-Time Database Access workflow

Just-in-Time Database Access

Explain the different database permission levels

Database Security: Read, Write, and Admin Permission Levels

Explain database rollback and how to automate it

Database Rollback: When Shit Hits the Fan

Database DevSecOps is the practice of integrating security throughout the entire database development lifecycle, much like how DevSecOps evolved from DevOps.

What is Database DevSecOps?

Dynamic Data Masking (DDM) protects sensitive data in real-time by dynamically altering the data returned to the application or user without changing the data at rest

What is Dynamic Data Masking (DDM)

Database DevOps integrates DevOps practices into database management, automating processes like schema changes, testing, and deployment.

What is Database DevOps?

Explain why Postgres provides a variety of timeout settings.

Postgres Timeout Explained

Pros and Cons between choosing UUID or auto increment integer / serial as the primary key for SQL database.

How to Choose between UUID and Auto Increment Integer / Serial as the Primary Key?

A comprehensive overview of the essential practices and principles necessary for achieving GDPR compliance within database operations

Database Compliance for GDPR: Implications and Best Practices

How Bytebase tracks database changes throughout the lifecycle

How Bytebase Tracks Database Change

Explain the pricing details of Google Cloud AlloyDB and compare it with Google Cloud SQL

Understanding Google Cloud AlloyDB Pricing (updated for 2025)

Describe the current feature gap of Snowflake CI/CD story and present a solution

Snowflake CI/CD and DevOps Best Practice

Bytebase is an all-in-one solution for database development lifecycle management. It replaces fragmented database tools and unifies the workflow in a single place.

All the Database Tools Bytebase Replaces

Database schema drift is the case where the actual schema in the live database is different from the source of truth. It's also one of the most frequent root cause of the database related outages.

What is Database Schema Drift?

Comparing PostgreSQL data masking solutions - PostgreSQL Anonymizer and Bytebase Dynamic Data Masking.

Data Masking for PostgreSQL Databases

Collaboration and Teamwork, Change Tracking and History, Migration Scripts, Branching and Merging, Deployment and Rollback, VCS Integration GitOps are key aspects of database version control

What is Database Version Control?

Compare MySQL data masking solutions. MySQL Enterprise Data Masking, Percona Data Masking Plugin and Bytebase Dynamic Data Masking.

Data Masking for MySQL Databases

Snowflake provides auto-suspend and auto-resume to help customers to save budget when the warehouse is inactive

What is Snowflake Auto-Suspend and Auto-Resume?

While Snowflake revolutionizes data management and analytics, effectively managing schema changes and tracking change history becomes increasingly crucial.

Snowflake Schema Change and CI/CD Best Practice

Database schema migration involves modifying the structure of objects within relational databases, evolving their schema from the current state to a new desired state. The two main schema transformation strategies are migration-based migration and state-based migration.

What is Database Schema Migration?

The common mistakes when making schema migrations and the best practice to avoid those pitfalls.

How to Handle Database Migration / Schema Change?

The second release of the 🐰  year, the version number was upgraded from 1.12.0 to 1.12.1, and although it was a minor upgrade (since no schema changes were involved), it still included 230 code commits

Bytebase 1.12.1 Deep Dive

First release of the Year of Rabbit 🐰 is out, the version number was upgraded from 1.11.0 to 1.12.0. - SQL Review Policy - Price Plan Update.

Bytebase 1.12.0 Deep Dive

We are thrilled to announce the first release launch of 2023, marking a new beginning for our product and our valued customers. The version number was upgraded from 1.10.0 to 1.11.0. - MongoDB - Schema Diagram - Schema Editor.

Bytebase 1.11.0 Deep Dive

Last week, we released Bytebase 1.10.0. Let's take a deeper look into the new features - Audit Log - Terraform Provider to manage user roles in PostgreSQL - Support for large SQL files.

Bytebase 1.10.0 Deep Dive

If we can develop a complete Database CI/CD workflow, and break the wall between developers and DBAs, will it improve release efficiency?

VCS Integrated Database CI/CD Workflow Best Practice

Last week, we released Bytebase 1.9.1. Let's take a deeper look into the new features - schema editor, mark issues as Need Attention, data access control, and data masking.

Bytebase 1.9.1 Deep Dive

Database Change Management (DCM) explained — definition, why it is hard, the ideal workflow, state-based vs change-based approaches, tool evaluation criteria, and how it fits into DevOps.

What is Database Change Management (DCM)?

The three ways to integrate Bytebase SQL Review with GitHub. GitHub Action, GitHub App, and GitOps CI

Three Ways to Integrate Automatic SQL Review into GitHub

Last week, we released Bytebase 1.9.0. Let's take a deeper look into the new features - Sync Schema for PostgreSQL, SQL Editor Admin Mode, and External Approval via Feishu.

Bytebase 1.9.0 Deep Dive

Database access control can be a headache for DBAs and engineering teams. In this post, we explore database access control scenarios, and how Bytebase helps you to centralize database access control.

How to Manage Database Access Control

Last week, we released Bytebase 1.8.0. Let's take a deeper look into the new features - MySQL Sync Schema, Admin Mode for SQL Editor, and External Approval via Feishu.

Bytebase 1.8.0 New Features Dive

A database schema defines the logical structure of your data: tables, columns, types, keys, and constraints. Learn how schemas work and evolve.

What is a Database Schema?

SQL review catches bad SQL before it hits the database. This guide covers the three layers (linting, semantic rules, and policy with approval) and compares the main SQL review tools.

SQL Review: From Linting to Governance

Explain and address the confusion between Developer (Dev), Site Reliability Engineer (SRE), (Operations) Ops and DevOps.

Dev, SRE, Operations, DevOps - What’s the Difference?

We dive the rationale behind the industry divergence between state-based and migration-based approach. In the end, we also show how Bytebase can help to get the best of both worlds.

Database Version Control, State-based or Migration-based?

Database-as-Code has many benefits in today's DevOps world. However, there exists several missing bricks preventing its wider adoption. We touch many points around Database-as-Code and also explain why Bytebase could help the transition.

Database as Code - the Good, the Bad and the Ugly


Application code has long enjoyed the benefits of CI/CD pipelines—automated testing, version control, and structured deployment processes. Yet databases, which are often the most critical component of an application, frequently lag behind with manual, error-prone change processes.

A well-designed CI/CD pipeline for database schema migrations reduces deployment errors, improves auditability, and enables faster iteration. This guide walks you through the essential components, implementation patterns, and tooling options for building a database CI/CD pipeline.

## Why Database CI/CD Matters

Database changes carry unique risks compared to application deployments:

- **State persistence**: Unlike stateless application code, databases hold critical state. A bad migration can corrupt data permanently.
- **Downtime impact**: Schema changes often require table locks, directly affecting availability.
- **Coordination complexity**: Database changes must coordinate with application deployments—deploy schema changes too early and old code breaks; too late and new code breaks.
- **Limited rollback**: While application code can be rolled back instantly, database rollbacks are complex. You can't always undo a `DROP COLUMN` that deleted data.

A CI/CD pipeline addresses these risks through:

- **Automated validation**: Catch syntax errors, missing indexes, and unsafe operations before production
- **Consistent process**: Same deployment process across all environments eliminates "works on my machine" issues
- **Audit trail**: Track who approved what change, when it deployed, and what SQL executed
- **Controlled rollout**: Test migrations in dev/staging with production-like data before touching production
- **Reduced coordination overhead**: Automation reduces the cognitive load of manual deployments

According to the [6 levels of database automation](/blog/database-automation-levels), most organizations operate at Level 0-1 (manual changes or ticketing systems). Level 3-4 (streamlined and integrated) provides automated deployments with SQL review and approval workflows.

## Core Components of a Database CI/CD Pipeline

A complete database CI/CD pipeline consists of six essential components:

### 1. Change Planning

The pipeline begins with defining what needs to change. This includes:

- **Schema migrations (DDL)**: CREATE, ALTER, DROP statements for tables, indexes, and other schema objects
- **Data modifications (DML)**: INSERT, UPDATE, DELETE operations
- **Target scope**: Single database, multiple databases, or database groups

Modern database CI/CD platforms support both UI-driven and GitOps workflows. Choose based on your team's needs:

- **UI-Driven**: Visual interface for teams preferring centralized control and multi-level approvals
- **GitOps**: Code-first approach integrated with Git providers (GitHub, GitLab, Bitbucket) for developer-centric teams

### 2. Automatic SQL Review

Before any change reaches production, automated SQL review validates the migration:

**Syntax Validation**

- Catch SQL errors before deployment
- Verify database compatibility

**Schema Rules**

- Enforce naming conventions
- Validate data types and constraints
- Check for required fields

**Performance Checks**

- Identify missing indexes
- Detect inefficient queries
- Flag full table scans

**Security Policies**

- Prevent unsafe operations (DROP TABLE in production)
- Detect potential data exposure
- Enforce access controls

**Backward Compatibility**

- Ensure changes won't break existing applications
- Verify migration reversibility

SQL Review policies can be configured at the environment or project level, allowing you to enforce different standards for development versus production environments.

### 3. Approval Process

Changes must go through an approval workflow before deployment. Effective approval systems offer:

**Risk-Based Routing**

- ✅ Low-risk changes (dev environment, backward-compatible): Automatic approval or minimal review
- ⚠️ Moderate-risk changes: Single approver review
- 🚨 High-risk changes (production DDL, large data updates): Multi-level approval

**Role-Based Authorization**

- DBA approval for schema changes
- Security team approval for permission changes
- Manager approval for production deployments

**Integration Options**

- Built-in approval within the database CI/CD platform
- Pull request reviews in GitHub/GitLab/Bitbucket
- External ticketing systems (ServiceNow, Jira)

The approval process should be flexible enough to handle both planned releases and emergency hotfixes without becoming a bottleneck.

### 4. Multi-Environment Rollout Pipeline

Database changes must progress through environments in a controlled manner:

**Environment Chain**

```plain
Development → Testing → Staging → Production
```

**Stage Configuration**

- Define custom environment chains
- Configure different database groups per stage
- Set environment-specific policies

**Deployment Execution**

- Parallel execution across database groups
- Automatic retry for transient failures

**Gated Progression**

- Manual gates for critical environments
- Automatic promotion for lower environments
- Smoke tests between stages

### 5. Rollback Capabilities

Even with thorough testing, things can go wrong. Robust rollback capabilities are essential:

**DML Rollback**

- One-click recovery for UPDATE/DELETE operations
- Automatic backup before risky data changes

**Schema Rollback**

- Generate reverse migration scripts
- Test rollback procedures in lower environments
- Document rollback steps

Not all database changes are easily reversible (e.g., column drops destroy data). Document irreversible changes and ensure stakeholder awareness before deployment.

### 6. Schema Drift Detection

Schema drift occurs when changes are made outside the CI/CD pipeline—a common problem in organizations with mixed practices.

**Drift Detection Features**

- Continuous monitoring of database schemas
- Alerts when unexpected changes detected
- Comparison against expected state
- Integration with change management workflow

When drift is detected, the system should:

1. Notify relevant teams immediately
1. Document the unexpected change
1. Provide options to either incorporate into version control or revert

## Real-World Example: Adding a New Column

Here's how a schema change flows through a CI/CD pipeline, with technical considerations at each stage:

**1. Developer Creates Migration**

```sql
-- V042__add_user_email_verified_column.sql
-- Add nullable column first to avoid rewriting the entire table
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT NULL;

-- Backfill in batches for large tables (assuming 10M+ rows)
-- UPDATE users SET email_verified = FALSE WHERE email_verified IS NULL;
-- (Run separately in batches to avoid long-running transactions)

-- Add index - considerations:
-- - MySQL: Can cause table locks, consider ALGORITHM=INPLACE
-- - PostgreSQL: Use CREATE INDEX CONCURRENTLY to avoid blocking writes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email_verified
ON users(email_verified) WHERE email_verified = FALSE;
-- Partial index: only index unverified users for query efficiency
```

**2. Pull Request and Review**

Automated SQL review catches potential issues:

- ✅ Pass: Partial index reduces index size
- ⚠️ Warning: Index creation may take 10+ minutes on production (12M rows)
- ✅ Pass: No foreign key constraints that could cause cascading locks

Manual review considerations:

- Index cardinality: Will this index be selective enough? (Expected: 5% false, 95% true)
- Deployment timing: Index creation doesn't block reads but may impact replication lag
- Application compatibility: New column defaults to NULL, application must handle

**3. Merge and Automatic Deployment to Dev**

```bash
# CI/CD pipeline runs:
- git merge main
- ./migrate.sh dev  # Applies migration to dev environment
- npm run test:integration  # Verifies application handles new column
```

Migration completes in 2 seconds on dev (10K rows). No issues detected.

**4. Staging Deployment**

Staging has production-like data volume (10M rows):

- Migration takes 8 minutes (index creation)
- Replication lag spikes to 45 seconds during index build
- QA verifies:
  - Application code reads email_verified correctly
  - Performance of queries using the new index
  - NULL handling for existing rows

**5. Production Deployment**

DBA review checklist:

- ✅ Confirmed: Staging migration succeeded
- ✅ Confirmed: No application errors in staging
- ✅ Plan: Deploy during low-traffic window (2 AM PST)
- ✅ Plan: Monitor replication lag during index creation
- ✅ Rollback: Can drop column and index if needed

Deployment execution:

```sql
-- PostgreSQL: Monitor execution time and locking
SELECT pid, query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE query LIKE '%idx_users_email_verified%';

-- Check index creation progress
SELECT phase, blocks_done, blocks_total,
       round(100.0 * blocks_done / NULLIF(blocks_total, 0), 1) AS percent_done
FROM pg_stat_progress_create_index;

-- If issues arise, can cancel concurrent index creation:
-- DROP INDEX CONCURRENTLY idx_users_email_verified;

-- MySQL: Monitor execution time and locking
-- SHOW PROCESSLIST;  -- Check for blocked queries
-- SELECT * FROM information_schema.innodb_trx;  -- Check transactions
```

Migration completes in 11 minutes. Application deployment follows after confirming no database issues.

**Key Takeaways from This Example:**

- Nullable columns avoid expensive table rewrites on large tables
- Index creation strategy differs by database (CONCURRENTLY, ALGORITHM=INPLACE)
- Production deployments need specific timing based on table size and traffic patterns
- Always verify migrations at production scale in staging first

## Choosing the Right Tools

While this guide focuses on concepts and processes, implementation requires tooling. Three widely-used open-source options for database schema migration are Bytebase, Liquibase, and Flyway. Here's how they compare across the core CI/CD components:

| Component                     | Bytebase                                                 | Liquibase                                                   | Flyway                                                                                          |
| ----------------------------- | -------------------------------------------------------- | ----------------------------------------------------------- | ----------------------------------------------------------------------------------------------- |
| **Interface**                 | Web GUI, API, Terraform                                  | CLI, Java API, Maven/Gradle                                 | CLI, Java API, Maven/Gradle                                                                     |
| **Installation**              | ⭐ Single binary (Go), Docker, K8s                       | Requires JVM                                                | Requires JVM                                                                                    |
| **Change Planning**           | ⭐ UI-driven or GitOps with project/issue model          | Changelog files (XML/YAML/SQL) + CLI                        | SQL migration files + CLI                                                                       |
| **Batch Changes**             | ⭐ Multi-environment, Multi-tenant with Database Groups  | Manual scripting required                                   | Manual scripting required                                                                       |
| **SQL Review**                | ⭐ 200+ built-in SQL Review rules (Free)                 | Policy Checks (Pro plan only) - custom rules can be created | Code Analysis (Teams/Enterprise plans) - supports Regex and SQLFluff rules                      |
| **Approval Workflow**         | ⭐ Risk-based custom approval with multi-stage flow      | Not a built-in feature                                      | Not a built-in feature                                                                          |
| **Multi-Environment Rollout** | ⭐ Automated pipeline with environment-specific policies | Manual orchestration via scripts                            | Manual orchestration via scripts                                                                |
| **Rollback**                  | ⭐ Auto-generated rollback statements for DDL/DML        | Automatic for some operations, manual for others            | Undo migrations (Teams/Enterprise plans); auto-generation of undo scripts in Enterprise edition |
| **Schema Drift Detection**    | ⭐ Automatic detection with alerts                       | Not a built-in feature                                      | Drift reports (Enterprise plan) - manual check via CLI command                                  |
| **GitOps**                    | Manual CI/CD integration                                 | Manual CI/CD integration                                    | Manual CI/CD integration                                                                        |
| **Change History**            | ⭐ Full history with diffs, issue tracking, audit logs   | Database changelog table                                    | Database migration table                                                                        |
| **Webhook Integration**       | ⭐ Slack, Teams, Discord, and more                       | Not a built-in feature                                      | Not a built-in feature                                                                          |
| **Supported Databases**       | 20+ SQL & NoSQL                                          | ⭐ 50+ SQL & NoSQL                                          | ⭐ 50+ SQL & NoSQL                                                                              |

## When You Might NOT Need Full CI/CD

Database CI/CD adds overhead. You might not need the full pipeline if:

- **Early-stage startup**: (less than 10 databases, 5 developers) - CLI tools may suffice
- **Read-only analytical databases**: Fewer schema changes, lower risk
- **Ephemeral dev environments**: Fully automated recreation might be simpler
- **Legacy systems**: Migration effort may outweigh benefits for systems nearing replacement

Start with version control and automated deployments, then add approval workflows and observability as team size and complexity grow.

## Conclusion

Database CI/CD moves you from ad-hoc changes to systematic, auditable processes. The goal is to achieve [Level 3-4 automation](/blog/database-automation-levels)—streamlined deployments with integrated approval workflows—without overengineering.

Implementation path:

1. Version control your migrations (Level 2)
2. Add automated deployment and SQL review (Level 3)
3. Layer in approval workflows and observability (Level 4)

Choose tooling based on your architecture: platforms like Bytebase for collaboration and governance, libraries like Liquibase/Flyway for CLI-first workflows. All three are production-ready; the right choice depends on your team's size, practices, and compliance requirements.



The [**Lei Geral de Proteção de Dados Pessoais (LGPD)**](https://en.wikipedia.org/wiki/General_Personal_Data_Protection_Law)—Brazil’s General Data Protection Law—came into effect in 2020. Much like Europe’s GDPR, it gives people more control over their personal data and sets out clear rules for how companies can collect, store, and use it.

But LGPD isn’t just a copy of GDPR. It reflects Brazil’s legal and cultural context, enforced by the [**Autoridade Nacional de Proteção de Dados (ANPD)**](https://www.gov.br/anpd/pt-br). For companies operating in or serving users from Brazil, LGPD compliance isn’t just a checkbox—it’s a matter of trust and legal risk.

And when it comes to data protection, the database is where it all becomes real. This is where personal data lives, where access happens, and where things can go wrong if not managed carefully.

In this post, we’ll look at LGPD through a database lens—and show how **Bytebase** helps teams bake compliance into their day-to-day workflow.

## Who LGPD Applies To

LGPD applies to _any_ organization—Brazilian or foreign—that processes personal data from individuals located in Brazil.

It defines:

- **Personal data** as any information that can identify someone, directly or indirectly.
- **Sensitive data** as information about health, biometrics, religion, political beliefs, or ethnicity.

So if your systems store or process user data from Brazil—even if your servers sit halfway across the world—you fall under LGPD.
And because the database holds the most sensitive data, it’s the first place you need strong controls.

![](/content/blog/database-compliance-for-lgpd/lgpd-persona.webp)

Under LGPD, **the same three roles from GDPR apply**:

- The **Data Subject (Titular)** is the individual whose personal data is collected.
- The **Controller (Controlador)** decides how and why data is processed.
- The **Processor (Operador)** handles data on behalf of the controller.  
  Both must designate a **Data Protection Officer (Encarregado)** as the contact point with Brazil’s ANPD and data subjects.

## LGPD Principles Meet Database Reality

Article 6 of LGPD lists ten principles for handling personal data. Many of them map directly to how databases should be managed.

| LGPD Principle                                               | What It Means for Databases                             | How Bytebase Helps                                                                                                                                                          |
| ------------------------------------------------------------ | ------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Purpose** – Use data only for legitimate, specific reasons | Every schema or data change should have a clear purpose | [Issue-based workflow](https://docs.bytebase.com/change-database/change-workflow) tracks who changed what, and why                                                          |
| **Security** – Prevent unauthorized access                   | Control who can access which databases                  | [RBAC](https://docs.bytebase.com/security/database-permission/overview) and [approval policies](https://docs.bytebase.com/change-database/approval) enforce least privilege |
| **Accountability** – Prove you’re following the rules        | Show evidence of controls                               | [Review workflow](https://docs.bytebase.com/change-database/review) and [immutable audit log](https://docs.bytebase.com/security/audit-log) provide an auditable trail      |
| **Necessity** – Limit processing to what’s essential         | Avoid querying or exposing extra data                   | [Dynamic data masking](https://docs.bytebase.com/security/data-masking/overview) hides sensitive fields in query results                                                    |

Instead of leaving these principles to policy documents, Bytebase brings them into your database workflow.

## Processing Data on a Legal Basis

LGPD says you can only process personal data if there’s a valid reason—such as user consent, legal obligation, or legitimate interest.

In practice, that means you need to know **why** each piece of data exists and be able to remove it when that reason no longer applies.

Bytebase makes this easier with **issue-based workflow** and **audit log**, so every change is reviewed, recorded, and tied to a legitimate purpose. If you ever need to prove compliance, you already have the evidence.

## Handling Sensitive and Anonymized Data

Sensitive data—health, biometric, or belief-related information—gets special protection under Article 11.
At the same time, LGPD encourages anonymization whenever possible.

Here’s how Bytebase helps with both:

- **Access control (RBAC)** limits who can see or modify sensitive tables.
- **Dynamic masking** hides real values in query results, perfect for lower environments.
- **Review worfklow** ensures all schema and permission changes are peer-reviewed before rollout.

It’s how you turn “we should protect sensitive data” into “we actually do.”

## Honoring Data Subject Rights

LGPD gives individuals rights to access, correct, delete, and even port their data. That sounds straightforward—until you realize it often involves multiple databases and dozens of tables.

Bytebase helps you act responsibly without chaos:

- The **database catalog** gives you visibility into where personal data lives.
- The **SQL Editor** lets you run controlled operations—like a targeted deletion—under proper review and audit.

So when users exercise their rights, you can respond accurately and securely.

## Being Ready for Incidents

If a security incident exposes personal data, LGPD requires companies to notify both the ANPD and the affected users. That’s not the time to start figuring out who had access or what changed.

Bytebase helps you prepare:

- Every SQL execution is **logged and attributed**.
- **Environment segregation** keeps production data isolated from test or staging.
- **Immutable records** support clear post-incident investigation.

With those in place, you’re not just reacting—you’re ready.

## Proving and Maintaining Compliance

LGPD’s final principle is accountability: it’s not enough to comply—you have to prove it.

With Bytebase, every schema update, permission change, and data fix goes through an **approval workflow**.
All of it is **traceable**, **reviewable**, and **exportable** for audits.
And because policies are enforced consistently across environments, compliance isn’t a one-off effort—it’s continuous.

## Closing Thoughts

Compliance with LGPD starts where your data lives. By embedding review, access control, and auditability directly into your database development process, Bytebase helps teams turn complex legal requirements into everyday best practices.

In short: **LGPD compliance isn’t a separate project—it’s how you work.** And Bytebase makes that possible.



A **LATERAL JOIN** allows a subquery in the `FROM` clause to reference columns from tables that appear earlier in the same `FROM` list.
PostgreSQL evaluates that subquery **once per row** of the left-side table, similar to a correlated subquery but written in `FROM`.

Use it when you need "for each row X, run this query that depends on X."

## Basic Syntax

The fundamental pattern looks like this:

```sql
FROM left_table lt
LEFT JOIN LATERAL (
  SELECT ...
  FROM ...
  WHERE ... lt.col ...
) s ON true
```

You can also write it as:

```sql
FROM lt, LATERAL (SELECT ...) s              -- CROSS JOIN LATERAL
FROM lt CROSS JOIN LATERAL (SELECT ...) s    -- explicit CROSS JOIN
```

The difference between `CROSS JOIN LATERAL` and `LEFT JOIN LATERAL` is crucial. `CROSS JOIN LATERAL` drops left rows when the subquery returns no rows, while `LEFT JOIN LATERAL` keeps all left rows and fills the right side with NULLs when there's no match.

## Why is it Called "Lateral"?

The term comes from Latin *lateralis*, meaning "of or pertaining to the side." In SQL, it refers to the subquery's ability to look **sideways** (laterally) at columns from tables that appear to its **left** in the `FROM` clause.

In the SQL standard, a *lateral derived table* is a subquery in `FROM` that can refer to columns from items that appear earlier in `FROM`. “Lateral” means the dependency runs sideways to the left, not downward like a nested subquery. So `LATERAL` just signals "this subquery may use columns from the tables before it."

## Core Example: Top-N Per Group

The classic use case is finding the top N records for each group. Suppose you want the most recent order for each customer:

```sql
SELECT c.id, c.name, o.order_id, o.placed_at, o.total
FROM customers c
LEFT JOIN LATERAL (
  SELECT *
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.placed_at DESC
  LIMIT 1
) o ON true;
```

This is elegant and efficient. The `LIMIT 1` runs **per customer**, not globally. With a proper index on `(customer_id, placed_at DESC)`, PostgreSQL can grab the most recent order for each customer with minimal work. Compare this to window functions or self-joins, which often require more complex logic and can be slower.

## Common Use Cases

**Expanding JSON arrays per row.** When you have JSON data stored in a column and need to expand it into multiple rows:

```sql
SELECT p.id,
       item->>'sku' AS sku,
       (item->>'qty')::int AS qty
FROM purchase_requests p
LEFT JOIN LATERAL jsonb_array_elements(p.items_json) AS item ON true;
```

Each row's `items_json` array becomes multiple rows, one per element.

**Set-returning functions.** Functions that return multiple rows can be used with `LATERAL` (though the keyword is optional for functions):

```sql
SELECT u.id, g
FROM users u
JOIN generate_series(1, u.max_groups) AS g ON true;
```

The function `generate_series` can reference `u.max_groups` from the preceding table.

**Conversion funnel analysis.** Tracking user journeys through sequential events where each step depends on the previous one. For example, finding users who viewed a homepage, then used a demo within a week, then entered credit card details within another week. Each `LATERAL` join represents a funnel step that can reference timestamps and conditions from prior steps.

## Performance Considerations

LATERAL joins are implemented as nested loop joins. This means for N rows on the left and M rows per subquery, the complexity is O(N×M). However, with proper indexing, this can be very efficient:

| Optimization | Impact |
|:-------------|:-------|
| **Index on filtered columns** | Critical. For the "most recent order" example, an index on `(customer_id, placed_at DESC)` allows PostgreSQL to use an index scan per customer. |
| **LIMIT in subquery** | Drastically reduces work per left row by stopping early. |
| **LEFT vs CROSS JOIN** | Use `LEFT JOIN LATERAL` when you want to keep all left rows; use `CROSS JOIN LATERAL` when you only want rows with matches. |

Always use `EXPLAIN (ANALYZE, BUFFERS)` to verify your query plan and ensure indexes are being used effectively.

## Gotchas

The lateral subquery can only reference **preceding** `FROM` items. Order matters. If you write `FROM a, LATERAL (SELECT ...) s, b`, the subquery can see `a` but not `b`.

`CROSS JOIN LATERAL` drops left rows with no match. If you want to keep all left rows (like a standard `LEFT JOIN`), use `LEFT JOIN LATERAL ... ON true`.

It's easy to accidentally multiply rows. If you only need one row per left row, make sure to use `ORDER BY ... LIMIT 1` in the subquery.

## Quick Reference Patterns

**Top N items per parent:**

```sql
SELECT p.id, i.*
FROM projects p
LEFT JOIN LATERAL (
  SELECT *
  FROM issues i
  WHERE i.project_id = p.id
  ORDER BY i.priority DESC, i.created_at DESC
  LIMIT 3
) i ON true;
```

**Tokenize text per row:**

```sql
SELECT d.id, t.token
FROM docs d
LEFT JOIN LATERAL regexp_split_to_table(d.body, '\s+') AS t(token) ON true;
```

**Per-row function with parameters:**

```sql
SELECT a.id, nearby.id AS nearby_id, nearby.distance
FROM addresses a
LEFT JOIN LATERAL (
  SELECT b.id, st_distance(a.geom, b.geom) AS distance
  FROM addresses b
  WHERE b.city = a.city
  ORDER BY a.geom <-> b.geom
  LIMIT 1
) nearby ON true;
```

## Conclusion

LATERAL joins are a powerful tool for solving problems that would otherwise require complex window functions, procedural code, or inefficient self-joins. They shine in top-N-per-group scenarios, working with set-returning functions, and any situation where you need to run a parameterized query for each row of a table. Master this feature, and you'll write cleaner, faster queries.


Database schemas evolve constantly as applications grow and requirements change. Traditional schema modifications require downtime, disrupting user experience and causing revenue loss. MySQL's Online DDL solves this problem by allowing schema changes while the database remains operational.

## Three Algorithms: INSTANT, INPLACE, and COPY

MySQL uses three algorithms for schema changes, each with different performance characteristics and operation support.

### INSTANT: The Fastest Option

**INSTANT** applies changes instantly by modifying only table metadata. It requires no data copying, minimal resources, and works perfectly with replication. However, it supports only specific operations like adding columns with default values or modifying enums.

### INPLACE: The Balanced Approach

**INPLACE** modifies tables directly without creating temporary copies. It allows concurrent DML operations (INSERT, UPDATE, DELETE) during execution and supports more operations than INSTANT, including index creation and column drops. The trade-off is higher resource consumption.

### COPY: The Traditional Method

**COPY** creates a new table, copies all data, then swaps tables. It's the slowest method but supports all schema changes, including data type modifications. Use only when INSTANT and INPLACE aren't available.

## Operation Support Matrix

| Operation                  | INSTANT | INPLACE | COPY |
| :------------------------- | :-----: | :-----: | :--: |
| **Adding a Column**        |  ✅ \*  |   ✅    |  ✅  |
| **Dropping a Column**      |  ✅ \*  |   ✅    |  ✅  |
| **Renaming a Column**      |  ✅ \*  |   ✅    |  ✅  |
| **Changing Data Type**     |   ❌    |   ❌    |  ✅  |
| **Adding Secondary Index** |   ❌    |   ✅    |  ✅  |
| **Dropping Index**         |   ❌    |   ✅    |  ✅  |
| **Adding Primary Key**     |   ❌    |  ✅ \*  |  ✅  |
| **Dropping Primary Key**   |   ❌    |   ❌    |  ✅  |
| **Adding Foreign Key**     |   ❌    |   ✅    |  ✅  |

_Conditions apply. See [MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html) for details._

## Key Limitations

**Resource Impact**: INPLACE operations consume significant CPU, memory, and I/O, potentially affecting database performance on busy servers.

**Replication Lag**: The biggest limitation is replication behavior. An INPLACE operation taking 3 hours on the primary will stall replication for 3 hours on each replica, creating massive lag.

**Locking Issues**: Despite being "online", metadata locks can still block other DDL operations and some DML statements.

**Limited INSTANT Support**: Most complex operations like data type changes, index additions, and primary key modifications aren't supported by INSTANT.

## Best Practices

**Choose Wisely**: Use INSTANT when possible, INPLACE when necessary, and COPY as a last resort.

**Test First**: Always test schema changes in staging environments that mirror production.

**Monitor Performance**: Watch CPU, memory, and I/O usage during operations.

**Consider Third-Party Tools**: For complex migrations or strict availability requirements, tools like `gh-ost` or `pt-online-schema-change` offer better control and replication handling.

**Plan for Replication**: In replicated environments, consider running changes individually on each server or using external tools to avoid lag.

## Conclusion

MySQL Online DDL provides valuable capabilities for schema evolution, but understanding its limitations is crucial. INSTANT operations are ideal when supported, while INPLACE operations require careful resource planning. For mission-critical environments, combining native features with third-party tools often provides the best balance of safety and efficiency.



**TL;DR:** Pick one style, document it, and stick to it. If you’re undecided, **plural tables** (`users`, `orders`) are a safe default: they read naturally in SQL and dodge some reserved words. Consistency matters more than the “right” choice.
## Why it matters

Names ripple through SQL, ORMs, migrations, BI, and docs. A consistent convention lowers cognitive load and onboarding time.

## The two camps

### Singular (`user`, `order_item`)

**Pros**

* **OOP alignment:** maps cleanly to classes/entities (a row is a `User`).
* **Language simplicity:** avoids irregular plurals for international teams.
* **Master–detail reads cleanly:** `order` ↔ `order_detail` can feel natural.

**Cons**

* **Reserved words:** `user`, `order`, `group`, `session` can clash.
* **Query feel:** `SELECT * FROM user` reads a bit stilted.

### Plural (`users`, `order_items`)

**Pros**

* **Natural language in SQL:** `SELECT * FROM users WHERE age > 21`.
* **Reserved-word avoidance:** `orders` > `order`.
* **Framework affinity:** popular in Rails and many modern stacks.

**Cons**

* **Semantic mismatch:** a row is a user but lives in `users`.
* **Irregulars:** `person/people`, `child/children` add edge cases.

## Industry split (rule of thumb)

| Approach | Common In                        | Key Advantage                   |
| -------- | -------------------------------- | ------------------------------- |
| Singular | Enterprise / traditional systems | OOP consistency; entity focus   |
| Plural   | Web frameworks / modern stacks   | Readability; framework defaults |

> There’s no universal standard; both conventions are widespread. What matters is a deliberate choice and consistency.

## Quick decision guide

* **Greenfield & undecided:** choose **plural tables**.
* **Existing codebase:** **match what’s there**—consistency beats preference.
* **Reserved-word risk:** prefer **plural** (`orders`, `groups`).
* **Strict DDD shops / heavy OOP mapping:** singular can fit better.

## Practical conventions (copy-paste)

1. **Tables:** plural, `snake_case` → `users`, `orders`, `order_items`, `audit_logs`.
2. **Join tables:** plural + plural, alphabetical → `orders_products`, `roles_users`.
3. **Columns:** singular, `snake_case` → `id`, `user_id`, `created_at`, `updated_at`.
4. **Views / MVs:** prefix + plural → `v_active_users`, `mv_daily_signups`.
5. **Reference tables:** plural → `countries`, `currencies`, `order_statuses`.
6. **Irregular nouns:** standardize once (e.g., always `people` *or* `persons`) and stick to it.

## Master–detail naming tips

* **Singular world:** `order` / `order_detail` is tidy and readable.
* **Plural world (most common):** `orders` / `order_items` keeps the collection metaphor; avoid `orders_details`.
  Use the object’s name (`order_items`) rather than “detail(s)”.

## Governance (make it stick)

* Write the rule in your engineering handbook.
* Add a schema linter/check in CI to block drift.
* Provide examples for tricky cases (irregular plurals, join tables, reserved words).

## Examples

**Plural (recommended default)**

```sql
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  status TEXT NOT NULL,
  total_cents INTEGER NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL DEFAULT 1,
  PRIMARY KEY (order_id, product_id)
);
```

**Singular (for strict entity alignment)**

```sql
CREATE TABLE user (
  id BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE order (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES user(id)
);
/* Beware: user/order can conflict with reserved words in some contexts */
```

## Final recommendation

Choose **one** convention, document it, enforce it. If you don’t have strong reasons, pick **plural tables + singular columns** with `snake_case`, and don’t revisit the debate in every PR. Consistency > perfection.


PostgreSQL's case sensitivity rules can surprise even experienced developers. Here's the concise, no-gotchas guide.

## 1. Identifiers (table/column names)

* **Unquoted identifiers are folded to lowercase.**

  ```sql
  CREATE TABLE Customer (ID int, UserName varchar(50));
  -- Actually created as: customer(id, username)
  
  SELECT * FROM customer;   -- ✅ works
  SELECT * FROM Customer;   -- ✅ works (folded to lowercase)
  ```

* **Quoted identifiers preserve case and must be referenced exactly.**

  ```sql
  CREATE TABLE "Customer" ("ID" int, "UserName" varchar(50));
  
  SELECT * FROM "Customer";           -- ✅ works
  SELECT * FROM customer;             -- ❌ ERROR: relation "customer" does not exist
  SELECT id FROM "Customer";          -- ❌ ERROR: column "id" does not exist
  ```

**Best practice:** avoid quoted identifiers. Use lowercase `snake_case` (e.g., `order_items`, `created_at`).

## 2. Strings (data values)

* **String comparisons are case-sensitive by default.**

  ```sql
  SELECT 'abc' = 'ABC';  -- false
  INSERT INTO users (username) VALUES ('JohnDoe'), ('janedoe');
  SELECT * FROM users WHERE username = 'johndoe';  -- 0 rows
  ```

* **Case-insensitive matching**

  * Functions and operators:

    ```sql
    SELECT * FROM users WHERE lower(username) = 'johndoe';  -- ✅ finds 1 row
    SELECT * FROM users WHERE username ILIKE 'john%';       -- ✅ case-insensitive LIKE
    SELECT * FROM users WHERE username ~* '^john';          -- ✅ case-insensitive regex
    ```

  * `citext` extension:

    ```sql
    CREATE EXTENSION IF NOT EXISTS citext;
    CREATE TABLE users (id SERIAL, username CITEXT UNIQUE);
    SELECT * FROM users WHERE username = 'johndoe';  -- ✅ automatically case-insensitive
    ```

  * Nondeterministic collations (PostgreSQL 12+):

    ```sql
    CREATE COLLATION case_insensitive (
        provider = icu, 
        locale = 'und-u-ks-level2', 
        deterministic = false
    );
    CREATE TABLE users (username TEXT COLLATE "case_insensitive");
    ```

## 3. Indexing for case-insensitive search

* Functional index to avoid full scans:

  ```sql
  CREATE INDEX users_name_lower_idx ON users (lower(name));
  -- Query must match the expression:
  SELECT * FROM users WHERE lower(name) = lower($1);
  ```

* For prefix searches:

  ```sql
  CREATE INDEX users_name_lower_like_idx ON users (lower(name) text_pattern_ops);
  SELECT * FROM users WHERE lower(name) LIKE lower($1) || '%';
  ```

## 4. ORMs & migrations: common pitfalls

* Some ORMs emit **quoted identifiers**, locking you into exact casing everywhere. Prefer ORM settings that generate unquoted, lowercase names.
* Mixing quoted and unquoted names across migrations leads to "why can't it find my table?" bugs — standardize on lowercase, unquoted schema objects.

```sql
-- Wrong: Mixing quoted and unquoted
CREATE TABLE user_accounts (user_id SERIAL);
ALTER TABLE "User_Accounts" ADD COLUMN email VARCHAR(100);  -- ❌ Fails

-- Correct: Consistent unquoted
ALTER TABLE user_accounts ADD COLUMN email VARCHAR(100);    -- ✅ Works
```

## Quick Rules of Thumb

1. **Schema:** lowercase + unquoted identifiers, always.
2. **Search:** use `ILIKE`, `lower()` with functional indexes, or `citext`.
3. **Avoid quoted names** unless you have a compelling reason.
4. **ORMs:** configure to generate lowercase, unquoted schema objects.

That's it — you'll stay consistent, avoid case traps, and keep queries fast.


PostgreSQL 14 (released September 30, 2021) introduced a powerful predefined role: **`pg_database_owner`**. This role changes how database ownership and privileges are managed by providing a dynamic way to handle permissions for database owners.

## What is `pg_database_owner`?

`pg_database_owner` is a **built-in role** that automatically maps to the **owner of the current database**.

* You don’t grant it to users.
* Membership is **implicit and context-dependent** — whoever owns the database is the member.

This allows privileges granted to `pg_database_owner` in template databases to carry over automatically when new databases are created from them.

## Why it Exists

Two main problems it solves:

1. **Template Database Privileges**
   Before, ensuring consistent privileges across databases required manual grants. Now, you can grant to `pg_database_owner` in a template, and new DBs automatically apply them.

2. **Safer Defaults**
   Starting in PostgreSQL 15, the `public` schema is owned by `pg_database_owner`. This gives each DB owner direct control over schema access, fixing the long-standing risk of overly permissive defaults.

## Key Characteristics

| Property                           | Description                                    |
| ---------------------------------- | ---------------------------------------------- |
| **Implicit membership**            | Always maps to the database owner              |
| **Database scope**                 | Valid only in the current database             |
| **No login**                       | Cannot log in directly                         |
| **No explicit members**            | You cannot `GRANT ... TO pg_database_owner`    |
| **Template inheritance**           | Privileges granted in a template DB carry over |
| **Isolation**                      | Cannot be part of role hierarchies             |
| **Public schema ownership (v15+)** | Owns the `public` schema                       |

## Common Error

Trying to grant it explicitly produces an error:

```sql
GRANT pg_database_owner TO my_user;
ERROR: role "pg_database_owner" cannot have explicit members
```

✅ Correct usage:

* Grant privileges *to* `pg_database_owner`
* Or transfer DB ownership if you want a new effective owner

```sql
ALTER DATABASE mydb OWNER TO new_owner;
```

## Practical Examples

**Schema Privileges**

```sql
GRANT USAGE, CREATE ON SCHEMA app TO pg_database_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pg_database_owner;
```

**Template Setup**
Grant monitoring or utility functions to `pg_database_owner` in a template DB so every new database owner gets access automatically.

## Best Practices

**Do:**

* Use it in template DBs for consistent privileges
* Grant privileges *to it*, not users
* Leverage it for schema or function access

**Don’t:**

* Try to grant it to users (will fail)
* Expect cross-database scope
* Assume it has privileges by default

## Conclusion

`pg_database_owner` is a small but important change: it gives PostgreSQL a clean, dynamic way to handle database ownership and privilege inheritance. Whether you’re building multi-tenant apps or just want safer defaults, it simplifies privilege management and improves security.


Postgres is a powerful and popular open-source object-relational database system. However, like any complex system, it has its intricacies. One of the most important concepts to understand for maintaining a healthy and performant Postgres database is the idea of a **table rewrite**.

When Postgres "rewrites" a table, it creates a brand-new copy of the relation (a new **relfilenode**) and swaps it in. This process often requires approximately 2× temporary disk space and usually holds strong locks that can block traffic. A handy mental model is: _if the on-disk row layout must change or the table has to be physically moved/reordered, expect a rewrite._

This article will explore which Postgres operations cause a table rewrite, which ones don't, and the locking implications of these operations.

## Which Operations Cause a Table Rewrite?

Here is a summary of common Postgres operations and whether they cause a table rewrite:

| Operation                                          | Rewrite?             | Lock Level             | Why?                                                                      |
| -------------------------------------------------- | -------------------- | ---------------------- | ------------------------------------------------------------------------- |
| `TRUNCATE`                                         | ✅ Yes               | ACCESS EXCLUSIVE       | Deletes table content by replacing its file.                              |
| `ALTER TABLE ... SET TABLESPACE`                   | ✅ Yes               | ACCESS EXCLUSIVE       | Moves data to new storage layout or tablespace.                           |
| `ALTER TABLE ... SET LOGGED/UNLOGGED`              | ✅ Yes               | ACCESS EXCLUSIVE       | Changing persistence forces a rewrite.                                    |
| `ALTER TABLE ... ALTER COLUMN TYPE` (incompatible) | ✅ Often             | ACCESS EXCLUSIVE       | Rewrite if type change isn't binary-compatible.                           |
| `ALTER TABLE ... ALTER COLUMN TYPE` (compatible)   | ❌ No                | ACCESS EXCLUSIVE       | Binary-compatible changes (e.g., varchar(50)→varchar(100)) avoid rewrite. |
| `ALTER TABLE ... ADD COLUMN DEFAULT` (constant)    | ❌ No (Postgres 11+) | ACCESS EXCLUSIVE       | Stored as metadata only. Old rows remain untouched.                       |
| `ALTER TABLE ... ADD COLUMN DEFAULT` (volatile)    | ✅ Yes               | ACCESS EXCLUSIVE       | Each existing row needs its own computed value.                           |
| `ALTER TABLE ... DROP COLUMN`                      | ❌ No (mostly)       | ACCESS EXCLUSIVE       | Column is marked dropped in metadata; no immediate rewrite.               |
| `ALTER TABLE ... ALTER COLUMN SET NOT NULL`        | ❌ No                | ACCESS EXCLUSIVE       | Metadata-only change (with validation scan).                              |
| `ALTER TABLE ... ALTER COLUMN SET STORAGE`         | ❌ No                | ACCESS EXCLUSIVE       | Only changes future TOAST strategy; existing rows unchanged.              |
| `CREATE INDEX`                                     | ❌ No                | SHARE                  | Creates a separate index file, blocks writes but not reads.               |
| `CREATE INDEX CONCURRENTLY`                        | ❌ No                | SHARE UPDATE EXCLUSIVE | Allows reads and writes; takes longer.                                    |
| `DROP INDEX`                                       | ❌ No                | ACCESS EXCLUSIVE       | Deletes index files, leaves table file alone.                             |
| `VACUUM FULL`                                      | ✅ Yes               | ACCESS EXCLUSIVE       | Compacts table into a new file, frees all space.                          |
| `CLUSTER`                                          | ✅ Yes               | ACCESS EXCLUSIVE       | Reorders the table based on an index into a new file.                     |
| `ANALYZE`                                          | ❌ No                | SHARE UPDATE EXCLUSIVE | Collects statistics, no file change.                                      |
| `VACUUM` (regular)                                 | ❌ No                | SHARE UPDATE EXCLUSIVE | Cleans dead tuples in-place, no rewrite.                                  |

The default lock level for most `ALTER TABLE` forms is **ACCESS EXCLUSIVE**:

- ACCESS EXCLUSIVE blocks everything
- SHARE blocks writes but not reads;
- SHARE UPDATE EXCLUSIVE allows normal reads & writes.

## How to Test for a Rewrite

You can verify whether an operation causes a table rewrite by checking the **relfilenode** before and after the operation:

```sql
-- 1) Check the current relfilenode
SELECT pg_relation_filenode('public.mytable');

-- 2) Run your DDL operation

-- 3) Check again
SELECT pg_relation_filenode('public.mytable');
```

If the value changed, a rewrite occurred. This is a reliable way to test risky DDL before running it in production.

## Playbook

To avoid production issues with re-write:

**Prefer non-rewriting forms:** Use binary-compatible type changes (e.g., `varchar(50)` to `varchar(100)`) when possible.

**Schedule rewrites:** Plan operations like `VACUUM FULL`, `CLUSTER`, and `SET TABLESPACE` during maintenance windows.

## Conclusion

Understanding which Postgres operations cause table rewrites and their associated locking behavior is crucial for maintaining a healthy and performant database. Modern Postgres databases know which Postgres ops rewrite tables and what they lock. Newer versions skip many rewrites (e.g., constant DEFAULT). Follow the playbook: prefer non-rewriting changes and schedule rewrites in off-peak hours.



Postgres's Row-Level Security (RLS) is a powerful feature for implementing fine-grained access control, but it's riddled with subtle traps that can destroy performance or completely bypass security. This comprehensive guide covers all the major footguns with practical fixes and real-world examples.

---

## Performance Footguns

### 1. The LEAKPROOF Function Performance Killer

**The footgun:** Non-LEAKPROOF functions in RLS policies prevent index usage, causing catastrophic performance degradation.

**Why it happens:** Postgres must apply RLS filtering first, then non-LEAKPROOF functions, preventing the query planner from using indexes.

**Example problem:**

```sql
-- This will cause full table scans even with an index on title
CREATE POLICY user_documents ON documents
FOR SELECT
USING (owner_id = current_user_id() AND title ILIKE '%search%');
```

**The fix:**

```sql
-- Use LEAKPROOF functions or move complex logic out of policies
CREATE OR REPLACE FUNCTION safe_ilike(text, text)
RETURNS boolean
LANGUAGE sql
LEAKPROOF
AS $$ SELECT $1 ILIKE $2 $$;

CREATE POLICY user_documents ON documents
FOR SELECT
USING (owner_id = current_user_id() AND safe_ilike(title, '%search%'));
```

**Performance impact:** Queries can go from milliseconds to hours on large tables.

### 2. Complex Policy Performance Death

**The footgun:** Complex RLS policies with subqueries execute for every row, multiplying query cost exponentially.

**Bad example:**

```sql
CREATE POLICY complex_access ON orders
USING (
  EXISTS (
    SELECT 1 FROM user_permissions up
    JOIN departments d ON up.dept_id = d.id
    WHERE up.user_id = current_user_id()
    AND d.region = orders.region
  )
);
```

**Better approach:**

```sql
-- Move complexity to a LEAKPROOF function
CREATE OR REPLACE FUNCTION user_has_region_access(region_name text)
RETURNS boolean
LANGUAGE sql
LEAKPROOF
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_permissions up
    JOIN departments d ON up.dept_id = d.id
    WHERE up.user_id = current_user_id()
    AND d.region = region_name
  );
$$;

CREATE POLICY simple_access ON orders
USING (user_has_region_access(region));
```

### 3. Missing Indexes on Policy Columns

**The footgun:** Forgetting to index columns used in RLS policies forces sequential scans.

**Essential indexes:**

```sql
-- Always index columns used in policies
CREATE INDEX ON orders(tenant_id);
CREATE INDEX ON orders(owner_id);
CREATE INDEX ON orders(tenant_id, owner_id); -- composite for AND conditions
```

---

## Security Footguns

### 4. The BYPASSRLS Superuser Trap

**The footgun:** Superusers and table owners bypass RLS by default, creating false security confidence during testing.

**Why it's dangerous:** Testing with superuser accounts makes RLS appear to work when it's actually being ignored.

**The fix:**

```sql
-- Force RLS even for table owners
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;
ALTER TABLE sensitive_data FORCE ROW LEVEL SECURITY;

-- Never grant BYPASSRLS to application roles
-- Test with dedicated non-superuser accounts
```

**Testing pattern:**

```sql
-- Create proper test user
CREATE ROLE test_user;
GRANT app_user TO test_user;

-- Test as the actual application user
SET ROLE test_user;
SET app.tenant_id = 'test-tenant-id';
-- Run your tests here
RESET ROLE;
```

### 5. SECURITY DEFINER View Bypass

**The footgun:** Views are SECURITY DEFINER by default, running with creator's privileges and bypassing RLS.

**Dangerous example:**

```sql
-- Created by superuser - bypasses ALL RLS policies!
CREATE VIEW all_patient_data AS
SELECT * FROM patients;
```

**Secure approaches:**

```sql
-- Postgres 15+: Use SECURITY INVOKER
CREATE VIEW patient_data
WITH (security_invoker = true)
AS SELECT * FROM patients;

-- Older versions: Use SECURITY DEFINER with explicit RLS
CREATE OR REPLACE FUNCTION get_patient_data()
RETURNS TABLE(id uuid, name text, doctor_id uuid)
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
BEGIN
  -- Explicitly check RLS in SECURITY DEFINER functions
  IF NOT row_security_active('patients') THEN
    RAISE EXCEPTION 'Row security must be active';
  END IF;

  RETURN QUERY SELECT p.id, p.name, p.doctor_id FROM patients p;
END;
$$ LANGUAGE plpgsql;
```

### 6. Timing Side-Channel Attacks

**The footgun:** Query execution time leaks information about restricted data, allowing sophisticated attacks.

**Attack scenario:** In a multi-tenant medical database, an attacker measures query times to determine if patients with specific conditions exist in other tenants' data.

**Technical details:**

- RLS policy enforcement creates measurable timing differences
- Attackers can infer secret cardinality information
- Works even across network latency in cloud environments

**Example vulnerable query:**

```sql
-- Timing reveals if forbidden patients exist
SELECT COUNT(*) FROM patients
WHERE condition = 'rare_disease'
AND tenant_id = current_setting('app.tenant_id')::uuid;
```

**Mitigation strategies:**

1. Use data-oblivious query patterns (performance cost)
1. Add artificial delays to normalize timing
1. Limit query complexity for untrusted users
1. Monitor for suspicious timing-based query patterns

**Research note:** This attack has been demonstrated in academic research and works in real-world cloud environments.

### 7. CVE-2019-10130: Statistics Leakage

**The footgun:** [CVE-2019-10130](https://www.postgresql.org/support/security/CVE-2019-10130/). Postgres's query planner statistics could leak sampled data from RLS-protected rows.

**Technical details:**

- Query planner collects statistics by sampling column data
- Users could craft operators to read statistics containing forbidden data
- Affected Postgres 9.5-11 before May 2019 patches

**Status:** Fixed in Postgres 9.5.17, 9.6.13, 10.8, 11.3

**Lesson:** Keep Postgres updated and remember that even internal mechanisms can leak data.

---

## Implementation Footguns

### 8. Missing FORCE ROW LEVEL SECURITY

**The footgun:** Enabling RLS without FORCE allows table owners to bypass policies.

**Problem:**

```sql
-- Table owner still sees everything!
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
```

**Solution:**

```sql
-- Force RLS for everyone, including owners
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
```

### 9. USING vs WITH CHECK Confusion

**The footgun:** USING filters existing rows for SELECT/UPDATE/DELETE, but WITH CHECK validates new/modified rows for INSERT/UPDATE.

**Dangerous example:**

```sql
-- Users can INSERT data they can't see!
CREATE POLICY tenant_data ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid);
```

**Correct approach:**

```sql
CREATE POLICY tenant_isolation ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);
```

### 10. Connection Pooling Context Loss

**The footgun:** With connection pooling, `current_user` is a shared database role, useless for tenant isolation.

**Problem:**

```sql
-- Useless with PgBouncer - all connections share same user
CREATE POLICY user_data ON orders
USING (owner_id = current_user);
```

**Solution:**

```sql
-- Use application-controlled session variables
-- App sets per transaction:
SET app.user_id = 'user-uuid';
SET app.tenant_id = 'tenant-uuid';

-- Policy reads from session:
CREATE POLICY tenant_isolation ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);
```

**Security hardening:**

```sql
-- Prevent clients from setting app.* directly
REVOKE ALL ON SCHEMA pg_catalog FROM app_user;
-- Only allow trusted server code to set these variables
```

### 11. Foreign Key Failures Under RLS

**The footgun:** INSERT into child tables fails FK checks because RLS blocks SELECT on parent rows.

**Example failure:**

```sql
-- This INSERT fails even if customer exists
INSERT INTO orders (customer_id, tenant_id)
VALUES ('existing-customer-id', 'my-tenant');
-- ERROR: insert or update on table "orders" violates foreign key constraint
```

**Solution:**

```sql
-- Parent table needs SELECT policy for FK checks
CREATE POLICY customer_fk_visibility ON customers
FOR SELECT
USING (tenant_id = current_setting('app.tenant_id')::uuid);
```

### 12. Unique Constraint Cross-Tenant Leakage

**The footgun:** Global unique constraints reveal data existence across tenants.

**Problem:**

```sql
-- This reveals that email exists in ANY tenant
CREATE UNIQUE INDEX users_email_unique ON users(email);
-- INSERT fails with "duplicate key" even for other tenants
```

**Solution:**

```sql
-- Scope uniqueness to tenant
CREATE UNIQUE INDEX users_email_per_tenant
ON users(tenant_id, lower(email));
```

### 13. Silent Failures

**The footgun:** RLS failures are silent - operations fail without errors or warnings.

**Example:** An UPDATE that should modify 100 rows silently affects 0 rows due to RLS policy.

**Debugging approach:**

```sql
-- Temporarily disable RLS to test
SET row_security = off;
-- Run your query to see if RLS is the issue
SET row_security = on;

-- Or check if policies are active
SELECT row_security_active('table_name');
```

### 14. Column-Level Security Gaps

**The footgun:** RLS filters rows, not columns. Sensitive columns remain visible in allowed rows.

**Problem:**

```sql
-- Users can see SSN in their own records
SELECT * FROM users WHERE tenant_id = current_setting('app.tenant_id')::uuid;
```

**Solutions:**

```sql
-- Option 1: Column privileges
REVOKE SELECT (ssn, salary) ON users FROM app_user;

-- Option 2: Secure views
CREATE VIEW users_safe AS
SELECT id, name, email,
       CASE WHEN has_role('hr_role')
            THEN ssn
            ELSE 'XXX-XX-' || right(ssn, 4)
       END as ssn_masked
FROM users;
```

### 15. Materialized Views and Background Jobs

**The footgun:** Data copied to materialized views or exported by jobs isn't automatically protected by source table policies.

**Problems:**

```sql
-- Materialized view bypasses RLS
CREATE MATERIALIZED VIEW order_summary AS
SELECT * FROM orders; -- Contains all tenants' data!

-- Background job exports everything
COPY (SELECT * FROM orders) TO '/tmp/backup.csv';
```

**Solutions:**

```sql
-- Apply filtering in materialized views
CREATE MATERIALIZED VIEW tenant_order_summary AS
SELECT tenant_id, COUNT(*), SUM(amount)
FROM orders
GROUP BY tenant_id;

-- Use RLS-aware exports
COPY (
  SELECT * FROM orders
  WHERE tenant_id = 'specific-tenant'
) TO '/tmp/tenant_backup.csv';
```

### 16. Multiple Policy Confusion

**The footgun:** Multiple permissive policies are OR-ed together; one broad policy can override stricter ones.

**Problem:**

```sql
-- These policies are OR-ed - users get access if EITHER is true
CREATE POLICY user_own_data ON orders
USING (owner_id = current_user_id());

CREATE POLICY admin_all_data ON orders
USING (has_role('admin')); -- Oops, too broad!
```

**Solutions:**

```sql
-- Option 1: Use restrictive policies (AND-ed)
CREATE POLICY tenant_restriction ON orders
AS RESTRICTIVE
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Option 2: Combine logic in single policy
CREATE POLICY combined_access ON orders
USING (
  tenant_id = current_setting('app.tenant_id')::uuid
  AND (
    owner_id = current_user_id()
    OR has_role('tenant_admin')
  )
);
```

## Key Takeaways

1. **RLS is not a security silver bullet** - it can be bypassed through multiple vectors
1. **Performance impact is severe** - always index policy columns and keep policies simple
1. **Testing methodology is critical** - never test with superuser accounts
1. **Silent failures make debugging painful** - policies fail without warnings
1. **Context management is crucial** - use secure session variables, not `current_user`
1. **Defense in depth** - combine RLS with column privileges, secure views, and application-level controls

RLS is powerful when implemented correctly, but requires careful attention to these footguns to avoid catastrophic security and performance failures.



Database integrity and recovery mechanisms are critical for any production system. Postgres provides multiple rollback strategies: built-in transaction rollback with SAVEPOINT, Point-in-Time Recovery (PITR), and modern cross-transaction DML rollback solutions with tools. Each serves different use cases with distinct limitations.

## Built-in Transaction Rollback and SAVEPOINT

Postgres transactions allow rolling back all changes within a transaction block. For granular control, `SAVEPOINT` create markers within transactions, enabling partial rollbacks without affecting earlier operations.

### Using SAVEPOINT

Create a savepoint:

```sql
SAVEPOINT my_savepoint;
```

Roll back to it:

```sql
ROLLBACK TO SAVEPOINT my_savepoint;
```

**Practical pattern for risky operations:**

```sql
BEGIN;

-- Step 1: safe operations
INSERT INTO employees (name, department) VALUES ('Alice', 'Engineering');

SAVEPOINT sp_batch;

-- Step 2: risky operations
INSERT INTO employees (name, department) VALUES ('Bob', 'Marketing');
-- Oops, Bob is actually in Sales

-- Roll back only the risky step
ROLLBACK TO SAVEPOINT sp_batch;

-- Step 3: continue with corrected operation
INSERT INTO employees (name, department) VALUES ('Bob', 'Sales');

COMMIT;
```

The savepoint remains usable after rollback, but any savepoints created after it are destroyed and invalidated by the rollback (not just released).

### Limitations

- Some DDL statements (`CREATE DATABASE`, `DROP DATABASE`, `CREATE TABLESPACE`, `DROP TABLESPACE`) cannot run inside transactions
- Only works for uncommitted transactions - once committed, `ROLLBACK` cannot undo changes

## Point-In-Time Recovery (PITR)

PITR restores databases to specific points in time using continuous WAL archiving. Postgres's Write-Ahead Log records every database change. PITR combines base backups with archived WAL files to replay changes up to any desired moment.

### Cloud Provider Support

Major cloud providers offer one-click PITR experiences:

- **AWS RDS for Postgres**: Restore to point in time via Console/CLI/API
- **Google Cloud SQL**: PITR from console interface
- **Azure Database for Postgres**: Portal "Restore" to latest or chosen restore point

### Named Restore Points

Create targeted recovery points for easier PITR:

```sql
-- Before risky migration
SELECT pg_create_restore_point('pre_migration_2025_09_04');
```

Later recover using `recovery_target_name = 'pre_migration_2025_09_04'` instead of guessing timestamps.

### Advantages

- Handles any rollback scenario regardless of transaction commit status
- Can recover from errors discovered hours or days later

### Limitations

- Operates at cluster level - rolls back entire database, not individual tables or rows
- Heavyweight operation unsuitable for small, isolated changes
- Rolling back one incorrect `UPDATE` also undoes all subsequent valid changes

## Cross-Transaction DML Rollback (Compensating Changes)

After a bad `UPDATE`/`DELETE`/`INSERT` is committed, you need **compensating DML** that restores previous values - think "git revert" for data.

### Manual Compensating DML Example

Accidentally ran:

```sql
UPDATE accounts SET status = 'inactive' WHERE org_id = 42;
```

Compensate using audit/history table:

```sql
-- Revert to last known status per row
UPDATE accounts a
SET status = h.old_status
FROM account_status_history h
WHERE a.id = h.account_id
  AND h.org_id = 42
  AND h.changed_at = (
       SELECT max(h2.changed_at)
       FROM account_status_history h2
       WHERE h2.account_id = a.id
         AND h2.changed_at < :mistake_time
     );
```

Real systems must handle sequences, cascades, triggers, and side-effects.

### Bytebase Solution

Bytebase provides [point-and-click rollback](https://docs.bytebase.com/change-database/rollback-data-changes) functionality:

1. **Prior Backup**: Automatically captures affected rows before DML execution and stores in the dedicated `bbdataarchive` schema
1. **1-Click Rollback**: Generates and executes rollback scripts automatically

### Workflow Benefits

- Eliminates manual rollback script creation
- Integrated review and approval process
- Multi-task rollback across databases
- Safe, controlled change management

## Choosing the Right Rollback Method

Now that you understand the three rollback approaches, here's how to choose the right one for your situation:

| Situation                             | Best Tool                                 | Why                                                  |
| ------------------------------------- | ----------------------------------------- | ---------------------------------------------------- |
| Still in session, haven't committed   | **Transaction rollback / SAVEPOINT**      | Instant, lossless; keep good work, discard bad chunk |
| Committed a small wrong UPDATE/DELETE | **Cross-transaction rollback (Bytebase)** | Surgical fix; no cluster restore                     |
| Dropped table / mass data corruption  | **PITR**                                  | Ubiquitous, reliable; recovers to clean time point   |

- **Use transactions + SAVEPOINT** to avoid mistakes in the first place
- **Use PITR** when blast radius is unclear or damage is large - it's ubiquitous and cloud-friendly
- **Use compensating DML (or Bytebase's rollback workflow)** for small, precise fixes after commit - without PITR's weight



## Understanding Postgres Locks

Postgres uses a [sophisticated locking system](https://www.postgresql.org/docs/current/explicit-locking.html) with multiple lock modes. DDL operations often acquire locks that conflict with routine DML operations like `INSERT`, `UPDATE`, and `DELETE`. This means a seemingly harmless schema change can bring your entire application to a halt if not executed carefully.

Let's understand what locks DML operations acquire:

| DML                        | Lock Mode         | Description                                                            |
| -------------------------- | ----------------- | ---------------------------------------------------------------------- |
| `SELECT`                   | **ACCESS SHARE**  | Only conflicts with ACCESS EXCLUSIVE                                   |
| `INSERT`/`UPDATE`/`DELETE` | **ROW EXCLUSIVE** | Conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE |

Now we can see how the two most common schema migration operations conflict with DML:

| DDL Operation              | Lock Mode            | SELECT        | INSERT/UPDATE/DELETE |
| -------------------------- | -------------------- | ------------- | -------------------- |
| `ALTER TABLE` (most cases) | **ACCESS EXCLUSIVE** | ❌ **BLOCKS** | ❌ **BLOCKS**        |
| `CREATE INDEX`             | **SHARE**            | ✅ Allows     | ❌ **BLOCKS**        |

Many `ALTER TABLE` forms acquire an **ACCESS EXCLUSIVE** lock, which is the most restrictive lock in Postgres. This completely blocks your application - no reads, no writes, nothing can happen on that table until the operation completes.

`CREATE INDEX` uses a **SHARE** lock, which allows `SELECT` statements but blocks all write operations (`INSERT`, `UPDATE`, `DELETE`). For a large table, index creation can take hours, effectively making your application read-only.

Both scenarios cause downtime. In the following sections, we'll explore alternatives that avoid these blocking behaviors.

## ALTER TABLE without Downtime

While most `ALTER TABLE` operations acquire `ACCESS EXCLUSIVE` locks, Postgres provides several techniques to avoid downtime for common schema changes.

### Adding Columns with DEFAULT Values

Since [Postgres 11](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16828d5c0273b4fe5f10f42588005f16b415b2d8), adding a column with a `DEFAULT` value no longer requires a table rewrite in many cases.

```sql
-- ✅ Fast operation (Postgres 11+)
-- No table rewrite, minimal lock time
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- ❌ Slow operation (requires table rewrite)
-- Only use if you need a non-constant default
ALTER TABLE users ADD COLUMN created_at timestamp DEFAULT now();
```

**How it works**: Postgres stores the default value in the system catalog instead of rewriting every row. When you read a row that doesn't have the new column, Postgres automatically returns the default value.

### Adding Constraints in Two Phases

For constraints that need validation against existing data, use the `NOT VALID` approach:

#### Check Constraints

```sql
-- Step 1: Add constraint without validating existing data
-- Fast - only blocks briefly to update catalog
ALTER TABLE users ADD CONSTRAINT users_age_positive
  CHECK (age > 0) NOT VALID;

-- Step 2: Validate existing data (can take time but doesn't block writes)
-- Uses SHARE UPDATE EXCLUSIVE lock - allows reads/writes
ALTER TABLE users VALIDATE CONSTRAINT users_age_positive;
```

#### Foreign Key Constraints

```sql
-- Step 1: Add foreign key without validation
-- Fast - only updates catalog, doesn't validate existing data
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fk
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Step 2: Validate existing relationships
-- Can take time but allows concurrent operations
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fk;
```

### Setting Columns to NOT NULL

The safest approach for `SET NOT NULL` is a four-step process:

```sql
-- Step 1: Add a check constraint (fast with NOT VALID)
ALTER TABLE users ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: Validate the constraint (allows concurrent operations)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- Step 3: Set NOT NULL (fast since constraint guarantees no nulls)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Step 4: Drop the redundant check constraint
ALTER TABLE users DROP CONSTRAINT users_email_not_null;
```

**Why this works**: The final `SET NOT NULL` is fast because Postgres can see from the validated constraint that no null values exist.

**Postgres 18 Update**: The upcoming Postgres 18 introduces a [simplified form](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a379061a2) that allows `NOT NULL` constraints to use the `NOT VALID` attribute directly:

```sql
-- Postgres 18+ - Simplified approach
ALTER TABLE users ALTER COLUMN email SET NOT NULL NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;
```

## CREATE INDEX without Downtime

Instead of the blocking `CREATE INDEX`, use `CREATE INDEX CONCURRENTLY` to build indexes without interfering with application traffic:

```sql
-- ❌ Blocks all writes during index creation
CREATE INDEX idx_users_email ON users(email);

-- ✅ Allows writes to continue during index creation
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
```

`CREATE INDEX CONCURRENTLY` uses a **SHARE UPDATE EXCLUSIVE** lock instead of a **SHARE** lock, allowing `INSERT`, `UPDATE`, and `DELETE` operations to continue normally while the index is being built.

**Trade-offs**: While concurrent index creation avoids downtime, it takes longer to complete and has some limitations (cannot be used inside transactions, requires more careful error handling).

For a comprehensive guide on concurrent index creation, see our detailed article on [CREATE INDEX CONCURRENTLY](/blog/postgres-create-index-concurrently).

## Use lock_timeout

Regardless of the lock level, all DDL operations should use [lock_timeout](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT) to prevent creating lock queues that can freeze your application.

### The Lock Queue Problem

When an `ACCESS EXCLUSIVE` operation waits for existing queries to complete, it blocks all subsequent operations from starting. Your application can appear completely frozen while waiting for one long query to finish.

```sql
-- Session 1: Long-running query (holds ACCESS SHARE)
SELECT COUNT(*) FROM large_table; -- Takes 5 minutes

-- Session 2: DDL waiting for ACCESS EXCLUSIVE
ALTER TABLE large_table ALTER COLUMN description TYPE text; -- Waits behind the SELECT

-- Session 3: New application query
SELECT * FROM large_table WHERE id = 123; -- BLOCKED by waiting DDL!
```

### Set lock_timeout

The `lock_timeout` parameter can be set at multiple levels:

```sql
-- Option 1: Session level (temporary)
SET lock_timeout = '5s';

-- Option 2: Role level (recommended - persistent across sessions)
CREATE ROLE ddl_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE ddl_user SET lock_timeout = '5s';

-- Option 3: Database level (applies to all connections)
ALTER DATABASE mydb SET lock_timeout = '5s';
```

Setting `lock_timeout` at the Postgres role level is recommended because:

1. The setting applies to all sessions for that role
1. No need to remember to set it in each DDL session

```sql
-- Use the dedicated DDL user for schema migrations
-- Connect as ddl_user and run:
ALTER TABLE users ALTER COLUMN description TYPE text;
```

If the operation can't acquire the lock within 5 seconds, it fails with an error instead of blocking the application indefinitely.

### Retry Logic

Combine `lock_timeout` with retry logic to handle busy periods:

```sql
-- Example DDL script with retry logic
DO $$
DECLARE
    max_attempts INTEGER := 10;
    attempt INTEGER := 1;
    success BOOLEAN := FALSE;
BEGIN
    WHILE attempt <= max_attempts AND NOT success LOOP
        BEGIN
            SET lock_timeout = '2s';
            ALTER TABLE users ALTER COLUMN description TYPE text;
            success := TRUE;
            RAISE NOTICE 'DDL succeeded on attempt %', attempt;
        EXCEPTION
            WHEN lock_not_available THEN
                RAISE NOTICE 'Attempt % failed, retrying in 30 seconds...', attempt;
                PERFORM pg_sleep(30);
                attempt := attempt + 1;
        END;
    END LOOP;

    IF NOT success THEN
        RAISE EXCEPTION 'DDL failed after % attempts', max_attempts;
    END IF;
END $$;
```

<HintBlock type="info">

Bytebase automatically handles [retry logic](https://github.com/bytebase/bytebase/blob/c32bf12c40fec3adc16f56c274d490e407465b04/backend/plugin/db/pg/pg.go#L439-L460) during schema migrations.

</HintBlock>

## Conclusion

Achieving zero-downtime Postgres schema migrations requires understanding locks and applying the right techniques:

1. **Understand the impact**: Know which DDL operations block your application
1. **Use modern Postgres features**: Leverage `CONCURRENTLY`, `NOT VALID`, and other optimizations
1. **Always set lock_timeout**: Prevent lock queues that can freeze your application
1. **Plan for retries**: Handle busy periods gracefully with retry logic

By following these practices, you can deploy schema changes confidently without impacting your users.

## References

1. [Postgres locking mode](https://www.postgresql.org/docs/current/explicit-locking.html)
1. [Postgres 11: ADD COLUMN with DEFAULT optimization](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16828d5c0273b4fe5f10f42588005f16b415b2d8)
1. [Postgres 18: NOT NULL constraints with NOT VALID](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a379061a2)
1. [How to Use Postgres CREATE INDEX CONCURRENTLY](/blog/postgres-create-index-concurrently)


The
Bytebase Blog

Explore the standard for database development

Solutions & Platform

Comparisons

Legal

Resources

Company