On-behalf-of (OBO) is the pattern for AI agents to act with a caller's delegated identity instead of a shared credential. No mainstream database engine carries it the way agents need, and here is why a governance layer in front of the database is where it has to live.

On-Behalf-Of Database Access for AI Agents

Compare direct database CLI access with a governed MCP server across six dimensions: setup, reach, identity, governance, auditability, and workflow fit.

MCP vs. CLI: How an AI Agent Should Reach Your Database

What a database MCP server actually hands an AI agent, how a query runs through one, and why a raw connection-string server is fine on your laptop but risky against production.

What is a Database MCP Server?

Most database MCP servers hand an agent a connection string and call it a day. The MCP server is the one place agent database access is governed or wide open.

Governed MCP vs. Raw MCP: How Agents Reach Your Database

AI agents are no longer just reading your database, they are authoring the migrations. The write path is a different risk class, and it needs its own gate.

When the Agent Writes the Migration, Who Approves It?

Generic PAM controls the database connection. Database PAM controls the SQL statement: per-query authorization and approval, SQL review, data masking, and statement-level audit. What the category means and why it exists.

What is Database PAM (Privileged Access Management)?

How to run SQL Server schema migrations safely in production, covering state-based vs migration-based workflows, online index operations, batched backfills, the expand-and-contract pattern, and CI/CD.

SQL Server Schema Migration and Change Management

How to store, rotate, and scope database credentials: secrets managers, dynamic credentials, the application path vs the human path, and the mistakes that leak passwords.

Managing Database Passwords and Secrets: Best Practices

The practical ways to audit MySQL activity, from the general query log to audit plugins, binlog CDC, and Bytebase, and where each one falls short.

MySQL Audit Logging Guide

Dynamic data masking is chosen by access path, not product. Five approaches (engine-native, in-database, application, BI layer, and Bytebase) and which caller each one masks.

How to Implement Dynamic Data Masking: Five Approaches by Access Path

Static vs dynamic data masking, when each applies, how they differ on backups, performance, and reversibility, and when to run both.

Static vs Dynamic Data Masking: When to Use Which

Compare BigQuery data masking options: native column-level dynamic data masking (built-in masking rules via policy tags and data policies, Dataplex catalog) and Bytebase fleet-wide masking.

BigQuery Dynamic Data Masking

Compare SQL Server data masking options, native Dynamic Data Masking (five mask types, SQL Server 2022 granular UNMASK) and Bytebase fleet-wide masking.

SQL Server Dynamic Data Masking

Compare Oracle data masking options. Native Data Redaction (five function types via DBMS_REDACT, EXEMPT REDACTION POLICY) and Bytebase fleet-wide masking.

Oracle Dynamic Data Masking

You write a one-line UPDATE against production. Here's the lane that keeps it safe: the four phases, the controls, and what each one prevents.

How to Handle Ad-hoc Database Data Changes?

Most DAC implementations stop at the connection. Real DAC operates at the statement and covers three vectors: machine, human, and AI agent.

What is Database Access Control (DAC)?

How to run dynamic data masking so it survives an audit: mask by role, go column-level, log unmask events, review policy changes in code, and keep environments in sync. Plus a vendor comparison and an end-to-end walkthrough.

Dynamic Data Masking Best Practices

How Bytebase audits the workflow layer: every SQL, schema change, approval, and export tied to a real user identity, with structured exports for any SIEM.

How Bytebase Handles Audit Logging

Database access control practices that hold up in a SOC 2 audit: least privilege, separation of duties, just-in-time access, statement-level enforcement, and the common mistakes that get flagged.

Database Access Control (DAC) Best Practices

TRUNCATE and DELETE both remove rows, but they differ in speed, rollback behavior, and locking. Here is how each one behaves in PostgreSQL, MySQL, and SQL Server, and when to reach for which.

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

A practical walk through six database design patterns you will actually meet in production: normalization, star schema, EAV, polymorphic associations, multi-tenancy, and the anti-patterns worth avoiding.

Database Design Patterns Every Developer Should Know

A practical walkthrough of pgvector: installing the extension, storing embeddings, writing similarity queries, building HNSW indexes, and knowing when plain PostgreSQL is enough.

pgvector: How to Add Vector Search to PostgreSQL

How upsert works across MySQL, PostgreSQL, and SQL Server, with the syntax for each, the edge cases worth knowing before you ship, and how upserts interact with migrations.

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

AI agents need more than DDL. The full database lifecycle codified (schema, dependencies, ownership, history) gives LLMs the context to query correctly.

From Schema as Code to Schema as Context

How to find and fix slow MySQL queries: reading the slow query log, using EXPLAIN, tuning indexes, and rewriting queries, with a worked example for each.

How to Fix Slow MySQL Queries: A Practical Guide

Blue-green deployment is easy when your servers are stateless. The trouble starts at the database. This guide walks through why database blue-green is hard and how to do it without corrupting your data.

Database Blue-Green Deployment: A Practical Guide

How to give AI agents governed access to your databases. Identity, authorization, query control, audit, and masking, applied to a new principal type.

How to Govern AI Agent Database Access

A walk-through of how renaming a single column can quietly break an analytics pipeline, why it happens, and how schema change detection and notification help you catch it early.

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

How to read a Postgres query plan, spot the common performance problems, and fix them without breaking other queries.

Postgres Query Plan: How to Read and Fix Performance Issues

A walk through every layer of the Postgres permission system that can raise ERROR 42501, why each one fires, and how the behavior differs from MySQL.

The Anatomy of Postgres Permission ERROR 42501

A practical look at Postgres logs: what they record, how the logging collector works, which parameters actually matter in production, and the mistakes that turn logs into expensive noise.

Postgres Logs Explained: A Practical Guide for Production

What multi-environment database deployment actually involves, the problems teams keep hitting, and the patterns (manual scripts vs GitOps) for handling them.

Database Multi-Environment Deployments - The Challenges and Patterns

Database audit logging really has two layers: the infrastructure layer your cloud provider ships, and the workflow layer you have to build yourself. Here is what each one captures, and why an audit needs both.

Database Audit Logging: Two Layers, One Trail

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

What the "Require Primary Key" SQL review rule checks, the production problems a missing primary key tends to cause, and the SQL to fix tables that don't have one.

Require Primary Key: A SQL Review Rule Explained

What the "Enforce NOT VALID in CHECK" SQL Review rule does, why adding a CHECK constraint can lock your table, and how the two-step NOT VALID approach avoids it.

Enforce NOT VALID in CHECK: A SQL Review Rule Explained

The practical ways to audit a PostgreSQL database, from native logging to triggers, logical replication, pgAudit, and Bytebase, with the trade-offs of each.

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.

Prohibit CASCADE: A SQL Review Rule Explained

A walkthrough of moving each Supabase piece (database, auth, storage, functions, realtime, networking) to its AWS counterpart, with a services-first order that keeps the final database cutover simple.

How to Migrate from Supabase to AWS

What enterprise database management actually comprises: the five practice areas, a self-assessment rubric, and why the ops/dev seam is where it breaks down.

What is Enterprise Database Management (EDM)?

A plain explanation of Postgres Transaction ID (XID) wraparound: what causes it, how to check how close you are, and what keeps it from taking your database offline.

Postgres Transaction ID (XID) Wraparound

How a CI/CD pipeline for database schema migration works end-to-end. What it gates, what it leaves outside, and the tools that fit.

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

What LGPD means for database operations: how it actually differs from GDPR, what the ANPD sanctions in practice, and the database controls (classification, masking, retention, audit) that hold up.

Database Compliance for LGPD: Implications and Best Practices

What a Postgres LATERAL JOIN does, when to reach for one, and the patterns and gotchas you'll run into using it.

What is Postgres LATERAL JOIN?

What MySQL Online DDL actually does, how its three algorithms (INSTANT, INPLACE, and COPY) differ, and the limitations to plan around before you run a schema change in production.

MySQL Online DDL: A Practical Guide

Should your tables be named user or users? This post walks through the trade-offs of singular vs. plural table naming, what the common stacks default to, and a set of conventions you can copy into your own handbook.

SQL Table Naming Dilemma: Singular vs. Plural

How PostgreSQL handles case in identifiers and string values, where it bites, and the settings that keep your queries consistent and fast.

Postgres Case Sensitivity Explained

What pg_database_owner does in Postgres, why it was added, and how to use it without hitting the "cannot have explicit members" error.

What is pg_database_owner in Postgres

A reference for which Postgres DDL operations rewrite the whole table (and take heavy locks) versus which ones are metadata-only, plus how to test it yourself before you run it in production.

Which Postgres Operation causes a table rewrite

A practical rundown of the Postgres Row-Level Security traps that quietly break performance or leak data, with the fix for each.

Postgres Row-Level Security Footguns

A walk through the three ways to roll back changes in Postgres: transactions and SAVEPOINT, Point-in-Time Recovery, and compensating DML, and when each one fits.

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

The limitations and gotchas of gh-ost for online MySQL schema changes, and when to reach for something else instead.

gh-ost Limitations You Need to Know Before Production

PostgreSQL best practices for application developers: connection naming, timeouts, online migrations, role separation, and UPSERT for reliable production apps.

Postgres best practices I wish every app developer knew

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.

Postgres 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 (2026)

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 (2026)

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, ranked by what each mistake costs to fix once the table holds real data. With PostgreSQL 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

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

Database 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 a 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

What Database Activity Monitoring is, how the four collection architectures differ, the vendor landscape, and where DAM stops short of prevention.

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?

Primary key vs foreign key: what each does and how they differ on uniqueness, NULLs, indexing, and relationships, with SQL examples and a comparison table.

Primary Key vs Foreign Key: What Are the Differences?

What write-ahead logging is, the steal/no-force theory behind it, and how SQL Server, Oracle, MySQL, PostgreSQL, and SQLite each implement redo and undo differently.

What is Write Ahead Logging (WAL)

Just-in-Time database access — the use cases, the traditional workflow and its blind spot, and how Bytebase governs the SQL, not just the request.

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 integrates security throughout the database development lifecycle. The threats and controls for each of the four access paths, from schema migration to AI agents.

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 work. Why the database lagged a decade behind code CI/CD, the typical workflow it replaces, and how to measure whether you actually have it.

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?

What seven years of GDPR enforcement teach database teams: the fines cluster around data topology, database breaches, and shadow data. The concrete database controls that address each.

Database Compliance for GDPR: Implications and Best Practices

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

Understanding Google Cloud AlloyDB Pricing (updated for 2026)

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 when the actual schema in the live database differs from the source of truth. How drift happens, why it causes outages, and how to detect and fix it.

What is Database Schema Drift?

Compare PostgreSQL data masking options — PostgreSQL Anonymizer 2.0 (Rust rewrite, five strategies, security labels) and Bytebase fleet-wide masking.

Postgres Dynamic Data Masking

What database version control is, why versioning a stateful system is harder than versioning code, the mechanics (migration files, version tables, checksums), and the honest limits of rollback.

What is Database Version Control?

Compare MySQL and MariaDB data masking — 9.7 Enterprise DDM, the legacy plugin, Percona, MariaDB MaxScale's proxy filter, and Bytebase fleet-wide masking.

MySQL Dynamic Data Masking

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?

You add a column. The migration locks a 200M-row table for forty minutes. Here's the lane that keeps planned schema change from breaking prod.

How to Handle Database Migration / Schema Change?


Brazil's [Lei Geral de Proteção de Dados (LGPD)](https://lgpd-brazil.info/) has been in force since 2020, and for the first few years most engineering teams filed it under "GDPR's Brazilian cousin, we'll get to it." That stopped being a safe filing in July 2023, when the regulator, the [ANPD](https://www.gov.br/anpd/pt-br), issued its first fine: R$14,400 against Telekall Infoservice, a tiny operation selling WhatsApp contact lists, sanctioned for processing without a legal basis and for having no data protection officer. Small fine, big signal: enforcement is not reserved for Meta.

Meta got its turn anyway. In July 2024 the ANPD [ordered Meta to suspend](https://fpf.org/blog/processing-of-personal-data-for-ai-training-in-brazil-takeaways-from-anpds-preliminary-decisions-in-the-meta-case/) the use of Brazilians' personal data for training its AI models, and the suspension held for two months until Meta reworked the rollout. That is the part of LGPD that fine amounts don't capture: the ANPD can halt your processing, not just bill you for it.

This post looks at LGPD from the database, because that is where the law gets tested. The statute talks about principles and rights; the sanction proceedings talk about who could access what, whether anyone noticed, and how long notification took. All three questions are answered by your database controls, not your privacy policy.

## Not a Translated GDPR

If you already run a GDPR program, about 80% carries over. The cast is even the same: the data subject (titular), the controller (controlador), the processor (operador), and the DPO (encarregado).

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

The remaining 20% is where teams get surprised:

| Dimension | LGPD | GDPR |
| --- | --- | --- |
| Legal bases | 10 (Art. 7), including credit protection | 6 (Art. 6) |
| Maximum fine | 2% of Brazil revenue, capped at R$50M per infraction | 4% of global revenue |
| Breach notification | 3 business days ([Resolution CD/ANPD 15/2024](https://www.ibanet.org/LGPD-development-in-Brazil)) | 72 hours |
| DPO | Expected of controllers by default; small agents exempted | Required only above thresholds (public bodies, large-scale monitoring) |
| Regulator | One national authority with preventive measures that can suspend processing | Per-country DPAs |

Three of these deltas matter operationally.

First, the fine cap cuts both ways. R$50M per infraction is a smaller worst case than GDPR's 4% of global revenue, which tempts finance teams to price LGPD risk lower. But the ANPD's preventive measures reset that math: an order to stop processing, like Meta's, costs more than most fines would.

Second, "3 business days" sounds more generous than GDPR's 72 hours until you try to meet it. The clock starts when you learn of the incident, and what you must deliver is scoped: which data, which subjects, what risk. Two of the sanctioned public bodies (IAMSPE, which notified 1.5 million affected subjects late, and the Santa Catarina health department) were cited for slow or insufficient notification, not for being breached.

Third, the DPO expectation is broader than GDPR's. Telekall was fined partly for simply not having one. If your GDPR analysis concluded you're under the DPO threshold, that conclusion does not transfer.

## What the ANPD Actually Sanctions

Read the docket so far and a pattern emerges. Telekall: no legal basis, no DPO. IAMSPE and Santa Catarina: weak security controls, missing impact assessment, late breach notification. Meta: no valid legal basis for a new processing purpose. Nothing exotic. The ANPD is sanctioning teams that couldn't demonstrate the basics: know what you process, secure it, notify on time, and prove all three.

That's good news for database teams, because "prove the basics" is a tractable engineering problem. Here is the work, mapped to where it lands in the database.

## The Database Work

### Know where the personal data lives

Brazilian schemas have their own tells: `cpf` (the 11-digit taxpayer ID), `rg` (state identity card), `cnh` (driver's license), `cns` (national health card), plus the universal `email` and `phone` columns. Classification is step one because every other control keys off it: you cannot mask, delete, or report on columns you haven't found. Pay extra attention to Art. 11 sensitive categories (health, biometrics, religion, political opinion, racial or ethnic origin), which carry stricter bases and heavier sanctions.

### Restrict by statement, not by connection

LGPD's necessity principle (Art. 6) says process only what the purpose requires. At the database layer that means least privilege at the query level: the support engineer who needs order status does not need `SELECT *` on the customers table. Standing superuser access for humans is exactly the "inadequate security measure" language that shows up in ANPD decisions. Grant scoped, time-boxed access on request instead.

### Mask the human path, and be honest about its boundary

For the everyday case (a developer debugging against production, an analyst pulling numbers), dynamic data masking lets the query run while returning `***.***.***-42` instead of a real CPF. One boundary to state plainly: query-layer masking governs humans going through your tooling. Your application's own connection is unaffected, and backups stay cleartext. For lower environments, use static masking or full anonymization instead. Art. 12 is the payoff: data that is genuinely anonymized, with no reasonable means of reversal, falls outside LGPD entirely. Irreversible anonymization is the highest-leverage control in the whole law.

### Retention and erasure that reach every copy

Art. 18 gives subjects the right to deletion, and Arts. 15-16 require erasure when the processing purpose ends. The hard part is the word "every": the row in the primary, the replica, the analytics warehouse, the S3 backup from last quarter. Where a record must stay immutable for a conflicting retention duty (tax, anti-fraud), the practical pattern is to keep PII in a separate store referenced by opaque ID, or encrypt it per user and erase by deleting the key.

### The 3-business-day clock runs on your audit trail

You cannot scope a breach in 3 business days without being able to answer who read or changed what, when, from where. This is the same lesson we learned in [our own SOC 2 audit](/blog/soc2-audit-logging/): "logging enabled" is a configuration; audit evidence is an artifact you can produce on demand. If reconstructing a week of access to the customers table takes your team longer than an afternoon, the notification deadline is already lost.

### Replicas cross borders too

[Resolution CD/ANPD 19/2024](https://www.saudlaw.com/overview-of-the-anpds-activities-in-2024-new-resolutions-and-enforcement-actions/) regulates international transfers and ships Brazil's version of standard contractual clauses. Engineering teams tend to read "transfer" as an API integration, but a read replica in `us-east-1` and a backup bucket in Frankfurt are transfers as well. Inventory where every copy of Brazilian personal data physically sits before the legal team papers the SCCs.

## Where Bytebase Fits

Bytebase covers the human-to-database slice of this list: classification-driven [dynamic masking](https://docs.bytebase.com/security/data-masking/overview) in the SQL Editor, [just-in-time access grants](https://docs.bytebase.com/security/database-permission/overview) with expiry, four-eyes approval on data changes, and an [audit log](https://docs.bytebase.com/security/audit-log) you can filter and export when the 3-day clock starts. It does not encrypt your backups, place your replicas, or govern your application's own connection; those controls live in your infrastructure layer, and you need both halves.

## Closing Thoughts

The ANPD's first years of enforcement have not punished sophisticated failures. They punished a company with no DPO, hospitals that notified late, and a platform that skipped the legal basis. LGPD compliance is not a translation project from your GDPR binder; it is the ability to prove, from the database outward, that the basics hold. So far, every name on the sanction list is a team that couldn't.



A **LATERAL JOIN** lets a subquery in the `FROM` clause reference columns from tables that appear earlier in the same `FROM` list.
PostgreSQL evaluates that subquery once per row of the left-side table, much like a correlated subquery, except you write it in `FROM` instead of in `WHERE` or `SELECT`.

The plain-language version: 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` matters, and it's the one thing people get wrong most often. `CROSS JOIN LATERAL` drops a left row when the subquery returns nothing for it. `LEFT JOIN LATERAL` keeps every left row and fills the right side with NULLs when there's no match, exactly like an ordinary `LEFT JOIN`.

## Why is it Called "Lateral"?

The word comes from Latin *lateralis*, "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 appearing earlier in `FROM`. "Lateral" means the dependency runs sideways to the left, not downward like a nested subquery. So `LATERAL` is really just a signal: this subquery may use columns from the tables before it.

## Core Example: Top-N Per Group

The classic case is finding the top N records for each group. Say 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;
```

The key thing here is that the `LIMIT 1` runs per customer, not globally. With an index on `(customer_id, placed_at DESC)`, PostgreSQL can grab the most recent order for each customer with very little work. The usual alternatives, window functions or self-joins, tend to need more involved logic and can run slower.

## Common Use Cases

**Expanding JSON arrays per row.** When you have JSON stored in a column and need to unfold 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 pair naturally 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;
```

Here `generate_series` can read `u.max_groups` from the preceding table.

**Conversion funnel analysis.** Tracking a user's journey through a sequence of events where each step depends on the one before it. For instance, finding users who viewed a homepage, then used a demo within a week, then entered credit card details within another week. Each `LATERAL` join is a funnel step that can reference timestamps and conditions from the prior steps.

## Performance Considerations

LATERAL joins run as nested loop joins. For N rows on the left and M rows per subquery, that's O(N×M) in the worst case. With the right indexing, though, it's often 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. |

Run `EXPLAIN (ANALYZE, BUFFERS)` to confirm the plan and check that your indexes are actually being used. The query that looks efficient on paper isn't always the one Postgres picks.

## Gotchas

A lateral subquery can only reference items that come **before** it in `FROM`. Order matters. If you write `FROM a, LATERAL (SELECT ...) s, b`, the subquery can see `a` but not `b`.

`CROSS JOIN LATERAL` quietly drops left rows that have no match. If you want to keep every left row, the way a standard `LEFT JOIN` would, use `LEFT JOIN LATERAL ... ON true`.

And it's easy to accidentally multiply rows. If you only want one row per left row, make sure the subquery has `ORDER BY ... LIMIT 1`.

## 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 solve a whole class of problems that would otherwise need window functions, procedural code, or awkward self-joins. They're at their best in top-N-per-group queries, with set-returning functions, and anywhere you need to run a parameterized query for each row of a table. Once it clicks, you'll reach for it more often than you'd expect.



Schemas change all the time. You add a column, drop an index, switch a data type. The old way of doing this locked the table while MySQL rebuilt it, which meant downtime, blocked writes, and an unhappy on-call rotation. MySQL Online DDL is the feature that lets you make many of these changes while the database keeps serving traffic. It's genuinely useful, but it isn't magic, and the differences between how it runs your statement matter a lot in production.

## Three Algorithms: INSTANT, INPLACE, and COPY

When you run a DDL statement, MySQL picks one of three algorithms to carry it out. They differ in how much work they do, how much they let other queries run alongside them, and which operations they can actually handle.

### INSTANT: The Fastest Option

**INSTANT** changes only the table's metadata. There is no data copying and no table rebuild, so it finishes in a moment regardless of table size and barely touches CPU or I/O. It also plays nicely with replication. The catch is that it only works for a narrow set of operations, such as adding a column with a default value or modifying an enum. When it applies, it's the one you want.

### INPLACE: The Balanced Approach

**INPLACE** modifies the table in place without building a temporary copy. It covers more ground than INSTANT, including creating indexes and dropping columns, and it allows concurrent DML (INSERT, UPDATE, DELETE) while it runs. The cost is real work: the operation can consume meaningful CPU, memory, and I/O while it churns through the table.

### COPY: The Traditional Method

**COPY** is the old behavior: MySQL builds a new table, copies every row across, then swaps the two. It's the slowest of the three, but it supports every schema change, including data type modifications that the other two can't touch. Reach for it when neither INSTANT nor INPLACE can do the job.

## 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

"Online" sets expectations that the feature doesn't always meet, so it's worth knowing where it bites.

**Resource Impact**: INPLACE operations are not free. On a busy server, the CPU, memory, and I/O they consume can be enough to drag down the queries you actually care about.

**Replication Lag**: This is the one that surprises people. The operation runs on the primary, then runs again on every replica. An INPLACE change that takes 3 hours on the primary will take roughly 3 hours on each replica too, and replication stalls behind it the whole time. On a large table, that's a lot of lag.

**Locking Issues**: Even an online operation needs a metadata lock at certain points, and that lock can block other DDL and some DML. The window is usually brief, but it exists.

**Limited INSTANT Support**: The fast path is also the most restricted one. The heavier operations, such as data type changes, index additions, and primary key modifications, fall outside what INSTANT can do, which means you're back on INPLACE or COPY for exactly the changes that hurt the most.

## Best Practices

A few habits keep schema changes boring, which is the goal.

**Choose Wisely**: Prefer INSTANT, fall back to INPLACE when you have to, and treat COPY as the last resort.

**Test First**: Run the change in a staging environment that mirrors production before you run it for real. Table size and data distribution change how long these operations take.

**Monitor Performance**: Keep an eye on CPU, memory, and I/O while the operation runs so you can catch trouble early.

**Consider Third-Party Tools**: For complex migrations or strict availability requirements, tools like `gh-ost` or `pt-online-schema-change` give you more control and handle replication more gracefully than the native algorithms.

**Plan for Replication**: In a replicated setup, think through how the change propagates. Running it on each server individually, or using an external tool, can save you from a multi-hour lag spike.

## Conclusion

MySQL Online DDL is a real improvement over locking the table and waiting, but the name oversells it. INSTANT is great when it applies, INPLACE works for most everyday changes if you plan for the resource and replication cost, and COPY is there for the rest. The teams that avoid surprises are the ones who know which algorithm their statement will use before they run it, and who reach for `gh-ost` or `pt-online-schema-change` when native isn't enough.


The
Bytebase Blog

Explore the standard for database governance

Solutions & Platform

Resources

Change Management

Access & Security

AI & Agents

Comparisons

Company

Legal

The Bytebase Blog

Explore the standard for database governance

The
Bytebase Blog