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)?

Practical guide to dynamic data masking: five best practices, a vendor comparison, an end-to-end walkthrough, and the compliance specifics auditors ask about.

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

PostgreSQL 19 features I'm excited about: planner advisor framework, DDL extraction, online REPACK, pg_stat_recovery, parallel autovacuum, sequence sync, and 64-bit MultiXact.

PostgreSQL 19 features I'm excited about

Bytebase is HIPAA-compliant — independent verification that our administrative, physical, and technical safeguards meet the HIPAA Security Rule for handling Protected Health Information.

HIPAA Compliance

PAM tools gate connections; databases also need SQL review, schema change workflow, and statement-level approval. Why most teams use PAM with Bytebase.

Bytebase vs. PAM: Why Database Teams Need More Than Privileged Access Management

What SOC 2 compliance audits actually want from your database audit log — not 'logging enabled' but evidence your auditor will accept. Lessons from going through our own audit.

SOC 2 Audit Log Requirements: Lessons From Our Own Audit

Bytebase vs. Atlas compared across migration approach, CI/CD, access control, and pricing. Updated for Atlas v1.1 and Bytebase 3.16.

Bytebase vs. Atlas: a side-by-side comparison for database schema migration

Audit-surviving database access control: statement-level enforcement across machine, human, and AI agent workloads, with the mistakes that fail SOC 2.

Database Access Control (DAC) Best Practices

SOX Section 404 requires IT general controls over database changes. Learn the ITGC domains, six audit questions teams fail, and how to build SOX-ready controls

Database Change Management for SOX Compliance

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

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

Learn how to find and fix slow MySQL queries using the slow query log, EXPLAIN, index tuning, and query rewrites, with practical examples for each.

How to Fix Slow MySQL Queries: A Practical Guide

A detailed comparison of Convex and Supabase covering architecture, real-time capabilities, data model, pricing, and schema management to help you choose.

Convex vs Supabase: Which Backend Platform Should You Choose?

Blue-green deployments work cleanly for stateless apps. Databases are stateful. Learn what makes database blue-green deployments hard and how to do them safely.

Database Blue-Green Deployment: A Practical Guide

The 10 best data management tools in 2026 — database GUI clients, schema migration tools, data pipelines, and analytics platforms compared with pricing.

Top 10 Data Management Tools in 2026

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

Postgres-only SQL editors are designed specifically for Postgres databases, providing a more focused and efficient experience for Postgres users. In this post, we are taking a look at the top Postgres-only SQL editors in 2026.

Enterprise Text-to-SQL: Context, Evaluation, and Governance

Database deployment tools for devops teams in 2026.

Database Deployment Tools for DevOps Teams in 2026

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

Postgres 19 widens MultiXactOffset to 64 bits, eliminating the hidden 4 billion member limit that caused catastrophic outages for high-concurrency workloads.

Postgres 19 Feature Preview: 64-bit MultiXactOffset

A guide to MySQL schema migration best practice.

MySQL Schema Migration Best Practice

Postgres 19 introduces automatic sequence synchronization for logical replication, eliminating manual tracking and making major version upgrades and failover significantly easier.

Postgres 19 Feature Preview: Sequence Synchronization for Logical Replication

A guide to multi-environment deployments in databases.

Database Multi-Environment Deployments - The Challenges and Patterns

A guide to open source sensitive data discovery tools — from NLP libraries like spaCy and Microsoft Presidio to multi-source scanners like Hawk-Eye and full metadata platforms like OpenMetadata.

Top Open Source Sensitive Data Discovery Tools in 2026

Database audit logging has two layers: infrastructure (provider-managed) and workflow (database activity). What each captures, and why compliance requires 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

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

Require Primary Key: A SQL Review Rule Explained

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

Enforce NOT VALID in CHECK: A SQL Review Rule Explained

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 comprehensive guide on migrating from Supabase to AWS, covering database, auth, storage, functions, realtime, and networking.

How to Migrate from Supabase to AWS

Top open-source Postgres backup solutions for 2026.

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

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

A practical guide to shipping features efficiently using PostgreSQL, covering development workflows, best practices, and real-world examples

How I Ship Features with Postgres

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

List of open source postgres connection poolers

Open Source Postgres Connection Pooler

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

SQL Table Naming Dilemma: Singular vs. Plural

PostgreSQL 18 features that change how application developers write and ship code.

What's New in PostgreSQL 18: A Developer's Perspective

Explore Snowflake Dynamic Data Masking capabilities and compare with alternative data masking solutions

Snowflake Dynamic Data Masking (DDM) and Alternatives

Top open-source Postgres authentication solutions for 2025.

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

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

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

An extensive comparison between Supabase and MongoDB on performance, usability, operability, ecosystem and more.

Supabase vs. MongoDB: a Complete Comparison in 2025

When evaluating database management tools, DataGrip and Bytebase represent two different philosophies: individual developer productivity versus team collaboration and governance.

Bytebase vs. DataGrip: a side-by-side comparison for database management

Supabase pricing starts free. We break down real costs for Pro ($25/mo) and Team ($599/mo) plans, explain overages, and compare each service head-to-head with AWS.

Supabase vs AWS: Feature and Pricing Comparison (2026)

Is Supabase free? Compare Supabase pricing vs AWS RDS and Aurora across free tier, Pro, and production plans. Includes Supabase free tier limits, storage costs, and compute pricing for 2026.

Supabase Pricing vs AWS RDS: Is Supabase Free? Full Cost Comparison (2026)

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

Database Security Lifecycle Management

Areas where MySQL shines and Postgres falls short.

Features I Wish Postgres Had but MySQL Already Has

Areas where PostgreSQL shines and MySQL falls short.

Features I Wish MySQL Had but Postgres Already Has

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

Evaluate several database schema diagram tools, and help you to choose the right one for 2025

Top 4 Free, Open Source Database Schema Diagram Tools to Visualize Database Easier in 2025

An overview of the most popular text-to-SQL AI tools in 2026, including DataGrip, TablePlus, SQL Chat, Vanna.ai, SQLAI.ai, Postgres.new, and DBHub MCP server. Compare features and capabilities to find the best SQL AI assistant for your workflow.

Top 5 Text-to-SQL Query Tools in 2026

Compare the DDL transaction difference between Postgres and MySQL

Postgres vs. MySQL: DDL Transaction Difference

Evaluate several TypeScript ORMs, and help you to choose the right one for 2025

Top TypeScript ORM 2025

Evaluate Prisma and TypeORM, and help you to choose the right TypeScript ORM for 2025

Prisma vs TypeORM: The Better TypeScript ORM in 2025

PostgreSQL 18 features and what they change for running production databases.

What's New in PostgreSQL 18: A DBA's Perspective

Explore alternatives to Amazon RDS, including 

RDS Alternatives in 2025

An extensive comparison between DynamoDB and Cassandra on performance, usability, operability, ecosystem and more.

DynamoDB vs. Cassandra: a Complete Comparison in 2025

An extensive comparison between DynamoDB and MongoDB on performance, usability, operability, ecosystem and more.

RDS vs. DynamoDB: a Complete Comparison in 2025

How gh-ost and pt-online-schema-change compare on architecture, cut-over, foreign keys, and runtime control — for teams choosing a MySQL online schema migration tool.

gh-ost vs pt-online-schema-change

GitHub and Bitbucket are both tools for code collaboration. This article compares the features and pricing of GitHub and Bitbucket.

GitHub vs Bitbucket:a Complete Comparison in 2025

Drizzle ORM vs Prisma comparison: type safety, performance, bundle size, serverless support, migrations, and ecosystem. Find out which TypeScript ORM is right for your project in 2026.

Drizzle ORM vs Prisma: Which TypeScript ORM Should You Use in 2026?

An engineering perspective to evaluate Google AlloyDB and Cloud SQL, and help you to choose the right Google Cloud database for 2025

AlloyDB vs. Cloud SQL: engineering guide to choose the right Google Cloud database for 2025

Understanding DynamoDB Pricing (2025)

A simple comparison of AWS Aurora versus RDS pricing in 2025, helping you understand key differences and choose the right database service for your needs and budget.

AWS Aurora vs. RDS Pricing: A Detailed Comparison 2025

Infrastructure as Code security tools help organizations identify vulnerabilities and misconfigurations in their infrastructure definitions. This post explores the top open-source IaC security tools available in 2025.

Top Open Source Infrastructure as Code (IaC) Security Tools in 2025

Compare the price of the mainstream managed PostgreSQL hosting providers

PostgreSQL Hosting Options in 2025: Pricing Comparison

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)

Database governance tools help organizations manage database changes, enforce policies, and maintain data quality. This post explores the top open-source database governance tools available in 2025.

Top Open Source Database Governance Tools in 2025

Workflow orchestration tools help coordinate tasks, handle errors, and monitor execution processes. In this post, we are taking a look at open source workflow orchestration options

Top Open Source Workflow Orchestration Tools in 2025

Understanding AWS RDS Pricing (2025)

Compare the price of the mainstream managed MySQL hosting providers

MySQL Hosting Options in 2025: Pricing Comparison

A comprehensive guide to the best open-source Kubernetes cluster management tools in 2025, including Argo CD, Karmada, Sveltos, and Plural.

Top 4 Open Source Kubernetes Cluster Management Tools in 2025

An extensive comparison between GitLab CI and GitHub Actions on features, performance, and more.

GitLab CI vs. GitHub Actions: a Complete Comparison in 2025

Review the state of the Golang ORM and query builder

Choose the Right Golang ORM or Query Builder in 2025

Overview of MySQL 9 features with spicy comments

What's New in MySQL 9 - a DBA's Perspective


The CI/CD pipeline approach moves database change onto the same pipeline as application code. Migration scripts live in Git. CI runs lint and review. Deployment runs through stages.

This post walks the approach end-to-end — what the pipeline gates, the work it leaves outside, and the tools that fit.

<HintBlock type="info">

The pipeline-led approach sits at Level 3 of [the 6 levels of database automation](/blog/database-automation-levels).

</HintBlock>

## What the pipeline does

Every change runs through the same path. Validate. Promote. Approve. Record.

### Validate

SQL review runs on the pull request. Syntax errors, missing indexes, unsafe operations, naming-rule violations — all caught before merge. The ruleset applies across environments; strictness varies. Production runs the full set. Dev runs a subset.

### Promote

The migration applies in dev. Then staging. Then production. Each stage runs the same script against progressively more realistic data. Failures stop the chain.

### Approve

Approval gates sit between stages. Routine changes auto-promote into dev and staging. Production-bound DDL waits for a human. The approver sees the SQL, the validation results, and the rollout plan in one record.

### Record

Every applied migration is logged with version, environment, executor, and timestamp. Schemas across environments are compared automatically. Drift — a change made outside the pipeline — triggers an alert.

## A change through the pipeline

A developer adds a nullable column to a 12M-row `users` table.

**Pull request.** Migration committed:

```sql
-- V042__add_user_email_verified_column.sql
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT NULL;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email_verified
ON users(email_verified) WHERE email_verified = FALSE;
```

SQL review flags two things. Index creation will take ~10 minutes at production scale. The partial-index `WHERE` clause keeps the index small but skews cardinality.

**Dev.** Migration applies in 2 seconds against a 10K-row database. Integration tests pass.

**Staging.** Production-like volume. Migration takes 8 minutes. Replication lag spikes to 45 seconds during the index build. QA verifies the application handles the new nullable column.

**Production.** DBA approves after staging completes clean. Deployment runs during a low-traffic window. Migration takes 11 minutes. Application deploys after database confirmation.

The pipeline records every step. Each environment has its own audit trail. The migration script is the same across all four.

Index strategy varies by engine. PostgreSQL uses `CREATE INDEX CONCURRENTLY`. MySQL uses `ALGORITHM=INPLACE`, falling back to `gh-ost` or `pt-online-schema-change` for tables the engine would otherwise copy. The developer picks the strategy. The pipeline enforces that the choice flows through review.

## Where it stops short

The pipeline gates one class of work: planned, version-controlled schema changes. Several others fall outside.

- **Ad-hoc operations.** Emergency hotfixes, one-off data patches, an `ANALYZE` during an incident — none start as a pull request.
- **Credential management.** Database credentials live in PAM, or in developer laptops. The pipeline runs as a service identity, not as the developer.
- **Read-path activity.** Queries, exports, ad-hoc lookups don't show up in the migration log.
- **Policy beyond the merge.** Naming rules and lock-grade checks run at PR review. Once merged, nothing rechecks at execution.

The pipeline gates planned change. It does not gate everything that touches the database.

[Securing Database Change](/blog/top-database-change-management-tools) covers the same gap from the security stack — bastion, ITSM, PAM.

## Tools that fit

Three open-source tools cover the pipeline's core mechanics.

- **Liquibase, Flyway** — CLI-first migration runners. Apply migrations from a changelog, track which have run, integrate with any CI system. Java-based.
- **Bytebase** — web GUI plus GitOps. SQL review, approvals, and stage promotion built in. Single Go binary.

| Capability                      | Liquibase | Flyway     | Bytebase         |
| ------------------------------- | --------- | ---------- | ---------------- |
| Apply migrations from changelog | ✓         | ✓          | ✓                |
| SQL review built in             | Pro plan  | Teams plan | Free, 200+ rules |
| Stage promotion + approvals     | Manual    | Manual     | Built in         |
| Schema drift detection          | —         | Enterprise | Built in         |
| Web UI                          | —         | —          | ✓                |

CLI-first teams use Liquibase or Flyway and orchestrate the pipeline themselves. Teams that want the pipeline pre-built use Bytebase.

## Where this lands

A CI/CD pipeline is Level 3 in [the 6 levels of database automation](/blog/database-automation-levels). It eliminates the manual hand-off from developer to DBA for planned schema change.

Not every team needs the full pipeline. Read-only analytical databases, ephemeral dev environments, and small teams (under 10 databases, under 5 developers) can run with version control plus a CLI runner. Add the pipeline as the team and database count grow.

The gap is what Level 4 platforms close: ad-hoc operations, credentials, read-path activity, post-merge policy. The pipeline-led approach is the strongest L3 a team can build. It is not L4.



This article focuses on my Day 1 development workflow with Postgres—the tools I use to build and ship features. I won't be covering Day 2 operations like backup, monitoring, or performance tuning in production. Instead, this is about the everyday development cycle: spinning up a local database, writing queries, managing schema changes, and running tests. These are the tools that live in my development environment and help me move from idea to deployed feature.

## Running Postgres Locally

I'm on a Mac, and my current go-to for local Postgres is [StackBricks](https://stackbricks.app/). It supports for multiple Postgres versions and other database engines beyond Postgres. Under the hood, it spins up Docker containers, which gives isolation.

I also used [DBngin](https://dbngin.com/) before, which provides a similarly smooth experience with a lightweight UI for managing local database instances.

Before discovering these tools, I was a long-time user of [Postgres.app](https://postgresapp.com/). Postgres.app is dead simple: download, click, and you have Postgres running natively. Also, I think it has the best icon among all Postgres tools.

<img src="/content/blog/how-i-ship-features-with-postgres/postgres-app.webp" alt="Postgres.app" style={{maxWidth: '200px', width: '100%', height: 'auto', display: 'block', margin: '0 auto'}} />

## SQL Editor

For writing and running SQL queries, I use [TablePlus](https://tableplus.com/). It has an elephant logo, but it actually supports a wide range of databases beyond Postgres—MySQL, Redis, SQLite, and more.

I can't point to one particular feature that makes TablePlus stand out. It's more that everything just feels smooth and easier to use compared to other tools. Part of this comes from it being built with native Mac AppKit instead of web technology—you can feel the difference in responsiveness and how it integrates with macOS. But really, it's all those small details adding up.

## Schema Migration

For schema migrations, my choice depends on the tech stack. With Go backends, I use [golang-migrate](https://github.com/golang-migrate/migrate). It's straightforward—write SQL migration files, run them up or down. No magic, just SQL.

When working with Node, I prefer [Drizzle](https://orm.drizzle.team/). The main selling point for me is that Drizzle can export the full schema. This is useful for keeping a copy of my latest database schema in the repo, which makes it easy to understand the current state without piecing together migration files.

Overall though, I think schema migration tooling for Postgres can be improved. Most tools follow the imperative migration pattern—you write a series of "ALTER TABLE" statements. That's why I built [pgschema](https://github.com/pgplex/pgschema)—a Postgres-specific declarative solution. You define what you want your schema to look like, and the tool figures out the migrations needed to get there. It's still early, but it solves a real pain point I've experienced.

## Running Integration Tests

Integration tests often need a real Postgres database.

For Node applications, I use [Testcontainers](https://testcontainers.com/modules/postgresql/). It spins up Docker containers on-demand during tests and tears them down afterward. It supports all mainstream Postgres versions.

For Go applications, I use [embedded-postgres](https://github.com/fergusstrange/embedded-postgres). Instead of spinning up Docker containers, it downloads and runs a Postgres binary directly. This saves the cost and complexity of running Docker—tests start faster, and it's one less dependency to manage.

## Type Safety

For Go applications, I use [sqlc](https://github.com/sqlc-dev/sqlc) to generate type-safe code from SQL queries. You write SQL, sqlc generates the Go code with proper types. No ORM overhead, just type-safe database access.

For TypeScript applications, I pair my database layer with [Zod](https://zod.dev/) to enforce runtime type validation. This catches type mismatches between your database schema and application code before they cause problems in production.

## For Tool Builders

As a Postgres tool builder, I heavily rely on [libpg_query](https://github.com/pganalyze/libpg_query) to parse PG statements. Most developers probably won't need it, but I want to give it a special shoutout—it's a key differentiator for Postgres compared with other database engines. It gives tool developers reliable parsing capability, which is a cornerstone to build strong tooling.

## Data Seeding

This is one area where I don't have a standard toolkit yet. I've used [Faker.js](https://fakerjs.dev/) before to generate realistic test data, but it's never become part of my regular workflow. Most of the time, I end up writing quick SQL scripts or custom seed files for each project.

I haven't found a solution that feels as smooth as the other tools in this article. If you have a data seeding approach that works well, I'd genuinely love to hear about it.

---

That's my current Postgres development toolkit. It's always evolving, what's working for you?



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.


The
Bytebase Blog

Explore the standard for database development

Solutions & Platform

Comparisons

Legal

Resources

Company