Notes on the MySQL 9.7 LTS release: Hypergraph Optimizer in Community, JSON Duality View DML, Group Replication observability, and Dynamic Data Masking for Enterprise.

Notes on the MySQL 9.7 LTS release

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


A slow MySQL query usually has one of four causes: a missing index, a poorly written query, a schema change that invalidated an existing index, or a table that has grown past the point where its original indexes are still selective. This guide walks through each cause with concrete diagnostics and fixes.

The most effective fixes: add indexes on frequently-filtered columns, rewrite queries that apply functions to indexed columns, and audit your schema after major changes. For persistent slowness, use EXPLAIN to identify which tables are read inefficiently, check the slow query log to find patterns affecting multiple queries, and add monitoring to catch regressions early.

## What makes a MySQL query slow

MySQL executes a query by building an execution plan: a step-by-step description of which tables to read, in what order, and which indexes (if any) to use. When the planner picks a bad plan, the query reads far more rows than it needs to.

The main signals that a query is doing too much work:

- **`type: ALL` in EXPLAIN**: full table scan, reading every row regardless of WHERE conditions
- **`key: NULL` in EXPLAIN**: no index was used
- **`Extra: Using filesort`**: MySQL had to sort results in memory or on disk rather than reading an already-sorted index
- **`Extra: Using temporary`**: MySQL created an internal temp table, common with GROUP BY or DISTINCT on non-indexed columns
- **`rows` estimate is very large**: MySQL expects to examine tens of thousands of rows to return a handful

Any of these in isolation may be acceptable. All of them together on a table with millions of rows means the query will be slow.

## How to enable and read the slow query log

The slow query log records any query that takes longer than `long_query_time` seconds (default: 10). For production investigation, lower it to 1 or 2 seconds. For development, set it to 0 to catch everything.

```sql
-- Check current settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable globally (affects all connections, no restart required, requires SYSTEM_VARIABLES_ADMIN)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Or lower long_query_time for your session only (does not affect other connections)
SET SESSION long_query_time = 1;
```

`log_queries_not_using_indexes` logs any query that skips an index, even if it finishes fast on a small table. This is useful during development to catch bad query patterns before the table grows.

The log file location is set by `slow_query_log_file`. To find it:

```sql
SHOW VARIABLES LIKE 'slow_query_log_file';
```

Two tools parse and group the log by total time:

```bash
# Built-in summarizer
mysqldumpslow -s t -t 10 /path/to/slow.log

# More detailed output from Percona Toolkit
pt-query-digest /path/to/slow.log
```

`mysqldumpslow -s t` sorts by total execution time and shows the top 10 offenders. `pt-query-digest` groups normalized queries and shows average time, rows examined, and row-send ratios. The ratio of rows examined to rows returned tells you how selective the query is. A ratio of 100:1 means MySQL read 100 rows for every 1 it returned, which usually points to a missing index.

## Using EXPLAIN to read the execution plan

Once you have a slow query, run it through EXPLAIN before touching anything:

```sql
EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC;
```

The columns to focus on:

| Column | What to look for |
|---|---|
| `type` | `ALL` = full scan (bad), `ref` / `range` / `eq_ref` = index lookup (good) |
| `key` | `NULL` means no index used |
| `rows` | Estimated rows scanned (lower is better) |
| `Extra` | Watch for `Using filesort`, `Using temporary`, `Using where` with no index |

In MySQL 8.0+, `EXPLAIN ANALYZE` actually runs the query and returns real execution times alongside the estimates:

```sql
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC;
```

When the estimated and actual row counts differ significantly, MySQL's table statistics are stale. Fix that with:

```sql
ANALYZE TABLE orders;
```

## Common causes and fixes

### Missing indexes

The most common cause of a slow query. If EXPLAIN shows `type: ALL` and `key: NULL` on a large table, the fix is almost always adding an index on the column(s) in the WHERE or JOIN clause.

```sql
-- Before: full table scan on customer_id
SELECT * FROM orders WHERE customer_id = 42;

-- Add the index
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

-- Confirm it's used
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
```

For queries that filter on multiple columns, a **composite index** is more efficient than two separate indexes. Put the equality column first, then the range column:

```sql
-- Query filters on both customer_id (equality) and created_at (range)
SELECT * FROM orders
WHERE customer_id = 42 AND created_at >= '2025-01-01';

-- Composite index: equality column first
ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at);
```

MySQL can use the leftmost prefix of a composite index. An index on `(customer_id, created_at)` can serve queries that filter on `customer_id` alone, but not queries that filter on `created_at` alone.

### Full table scans from functions on indexed columns

Wrapping an indexed column in a function prevents MySQL from using the index:

```sql
-- Bad: index on created_at is ignored
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

-- Good: rewrite as a range condition
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';
```

The same issue occurs with implicit type coercion. If `user_id` is INT but you pass a string, MySQL must convert every row:

```sql
-- Bad: coerces every row
SELECT * FROM users WHERE user_id = '42';

-- Good: types match
SELECT * FROM users WHERE user_id = 42;
```

### N+1 queries

N+1 happens when code runs one query to fetch N parent rows, then runs N more queries to fetch related data for each parent: one trip per row instead of one trip total.

```sql
-- One query to get 100 orders
SELECT id FROM orders WHERE customer_id = 42;

-- Then 100 queries, one per order
SELECT * FROM order_items WHERE order_id = ?;
```

Fix it with a JOIN:

```sql
SELECT o.id, oi.*
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.customer_id = 42;
```

N+1 patterns are hard to spot in application logs because each individual query is fast and doesn't appear in the slow query log on its own. They become visible when the cumulative count gets high. `pt-query-digest` groups normalized queries and shows execution count, which makes N+1 obvious at a glance.

### Correlated subqueries

A correlated subquery re-executes for every row in the outer query:

```sql
-- Slow: subquery runs once per order row
SELECT * FROM orders o
WHERE (SELECT COUNT(*) FROM order_items WHERE order_id = o.id) > 5;
```

Rewrite as a JOIN with aggregation:

```sql
SELECT o.*
FROM orders o
JOIN (
  SELECT order_id, COUNT(*) AS item_count
  FROM order_items
  GROUP BY order_id
) counts ON counts.order_id = o.id
WHERE counts.item_count > 5;
```

### SELECT *

Selecting all columns forces MySQL to read the full row even when only a few columns are needed. On tables with large TEXT or BLOB columns, this significantly increases I/O. Always list the columns you need. For queries that only need indexed columns, a **covering index** means MySQL never has to read the row at all; it returns results directly from the index.

```sql
-- All columns, reads full row
SELECT * FROM users WHERE email = 'user@example.com';

-- Only what's needed
SELECT id, name FROM users WHERE email = 'user@example.com';
```

## How schema changes affect query performance

Schema changes can silently break existing query plans in ways that only show up weeks later as tables grow. Common patterns:

**Adding a column with a default value** on a large table rewrites the table in older MySQL versions. In MySQL 8.0+, most `ADD COLUMN` operations are online, but adding a column with `NOT NULL` and no default still locks the table. See [MySQL Online DDL: A Practical Guide](/blog/mysql-online-ddl/) for which operations are safe.

**Dropping an index** removes query optimization that existing application queries may depend on. Check `sys.schema_unused_indexes` first, and audit slow query logs before removing any index.

**Changing a column type** (e.g., INT to BIGINT, VARCHAR length increase) can invalidate existing indexes or change their selectivity. Always run EXPLAIN on affected queries after a type change to confirm the plan is still efficient.

**Table growth** is the most common reason a previously-fast query becomes slow. An index on `status` with values `pending/shipped/cancelled` has low cardinality. When the table had 1,000 rows it was fast, but at 50 million rows MySQL may decide a full scan is cheaper. Check `EXPLAIN` output on large tables after each order-of-magnitude growth.

Tracking schema changes in version control helps identify when a performance regression started. [MySQL Schema Migration Best Practice](/blog/mysql-schema-migration-best-practice/) covers how to structure migrations so regressions are easier to trace.

## Monitoring slow queries in production

Reviewing the slow query log manually doesn't scale beyond a small team. A few approaches for ongoing monitoring:

**Performance Schema** (enabled by default in MySQL 5.6+) tracks per-query execution statistics in memory:

```sql
-- Top 10 queries by total execution time
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
```

**`sys` schema** provides readable views over Performance Schema:

```sql
-- Queries with full table scans
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC LIMIT 10;

-- Queries with high rows-examined to rows-sent ratio
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile
LIMIT 10;
```

**[Top SQL Server Monitoring Tools](/blog/top-sql-server-monitoring-tools/)** and equivalent MySQL monitoring tools (Percona Monitoring and Management, VividCortex, Datadog's MySQL integration) pull these metrics continuously and alert when query latency spikes.

For timeouts that are intermittent rather than consistent, [MySQL Timeout Explained](/blog/mysql-timeout/) covers the difference between `wait_timeout`, `interactive_timeout`, `innodb_lock_wait_timeout`, and `net_read_timeout`; each governs a different failure mode.

## How Bytebase helps prevent slow queries

Most slow-query incidents in production trace back to a schema change (an added column, a dropped index, a changed foreign key) that was reviewed casually or not at all. [Bytebase](https://www.bytebase.com) integrates SQL review into the schema migration workflow, flagging missing indexes, full-table-scan risks, and schema anti-patterns before changes reach production.

The SQL review engine checks for common patterns that cause slow queries: tables without primary keys, columns referenced in application code that lack indexes, and `SELECT *` in views. Combining this with [Top 4 MySQL Schema Compare Tools](/blog/top-mysql-schema-compare-tools/) to diff schema state across environments means slow-query regressions are caught at review time rather than discovered at 2am.

For teams running [MySQL process list monitoring](/reference/mysql/how-to/how-to-show-mysql-process-list-and-kill-process/), Bytebase's audit log tracks which schema change caused a query plan change, making post-incident investigation faster.

## FAQ

**What is the default value of long_query_time?**
10 seconds. This is too high for most applications. Set it to 1–2 seconds for production monitoring to catch queries that are slow but not catastrophic.

**Why does EXPLAIN show a good plan but the query is still slow?**
EXPLAIN shows estimated row counts based on table statistics. If statistics are stale, the estimates are wrong. Run `ANALYZE TABLE` to refresh them, then re-check with `EXPLAIN ANALYZE` to see actual row counts.

**When should I use a composite index vs separate indexes?**
Use a composite index when a query consistently filters on the same set of columns together. Put equality conditions before range conditions in the column order. Separate indexes are better when different queries use different column subsets.

**Can adding too many indexes slow MySQL down?**
Yes. Each index adds write overhead: inserts, updates, and deletes must maintain every index on the table. Indexes also increase storage. Audit index usage with `sys.schema_unused_indexes` and drop any that haven't been used recently.

**What is a covering index?**
A covering index contains all the columns a query needs, so MySQL can return results from the index alone without reading the full table row. It appears as `Using index` in the Extra column of EXPLAIN. Covering indexes are the most effective optimization for read-heavy queries on large tables.



Blue-green deployment keeps two identical production environments (blue and green) and routes traffic to one at a time. When you deploy a new version, you push it to the idle environment and flip the load balancer. Rollback is a single switch flip back.

That model works cleanly for stateless application servers. Databases are stateful. Both environments share, or must eventually converge on, the same data. A schema migration that deploys to green breaks the blue app reading from the same tables. This guide covers what makes database blue-green deployments uniquely hard and how to handle each piece.

## What is blue-green deployment?

Blue-green deployment is a release strategy that maintains two production-equivalent environments. At any moment, one environment (say, blue) handles live traffic. The other (green) is idle and used for staging the next release.

![Blue-green deployment architecture: load balancer routing traffic to active blue environment while green receives the new release](/content/blog/database-blue-green-deployment/blue-green-architecture-mmd.webp)

The release sequence:
1. Deploy the new version to the idle environment (green).
2. Run smoke tests against green while blue handles live traffic.
3. Flip the load balancer from blue to green.
4. Monitor green. If something breaks, flip back to blue.
5. Once green is stable, blue becomes the new idle environment for the next release.

The main advantage is a near-instant, low-risk cutover. The rollback path (flip the load balancer back) is the same operation as the promotion, so the blast radius of a bad deploy is bounded.

## Why database blue-green deployments are uniquely hard

Application servers are stateless. You can run version 1 and version 2 side by side, route traffic to one, and discard the other without any coordination problem. Databases don't work that way.

**Both environments point at the same data.** Unless you provision two separate databases (expensive, with sync complexity), blue and green both read and write the same tables. A schema change that adds a `NOT NULL` column to `orders` will break the blue application the moment it tries to insert a row without that column.

**Schema changes can't be rolled back by flipping a switch.** If green's schema has a column that blue doesn't know about, blue writes will fail. If you drop a column green was using, green breaks. The load balancer flip is reversible; the schema migration usually isn't.

**There's a window when both versions run simultaneously.** During cutover, in-flight requests from blue are still being processed. You can't guarantee a clean break between old and new code. Any schema change that is incompatible with the old code will cause errors during that window.

**Data written after cutover can't go back.** If you flip to green and green writes data in a new format, then flip back to blue, blue may not understand that data. You've now corrupted your dataset.

These aren't problems you can solve at the infrastructure layer. They require a disciplined approach to schema changes.

## Making schemas backward-compatible: the expand/contract pattern

The expand/contract pattern (sometimes called parallel change) is the standard way to make schema migrations safe for blue-green and rolling deployments. The principle: a schema change is split into phases, each of which is compatible with both the old and new application code.


### Phase 1: Expand

Add the new structure without removing anything old. The database now supports both versions of the app.

**Example: renaming a column `user_name` to `username`**

```sql
-- Phase 1: Add the new column. Both old (user_name) and new (username) columns exist.
ALTER TABLE users ADD COLUMN username VARCHAR(255);

-- Backfill new column from old data
UPDATE users SET username = user_name WHERE username IS NULL;
```

At this point: the old app reads and writes `user_name`, the new app reads and writes `username`. Both work.

### Phase 2: Deploy new application code

Deploy the new application version (which uses `username`) to the idle environment. Flip traffic to it. Both the expand-phase schema and the new app code are live.

Monitor. If something breaks, flip back. The old column is still there, old code still works.

### Phase 3: Contract

After the new code has been running cleanly for an acceptable monitoring period (hours, days, depending on risk tolerance), remove the old structure.

```sql
-- Phase 3: Remove the old column now that no running code references it
ALTER TABLE users DROP COLUMN user_name;
```

This cleanup step is safe because no deployed code references `user_name` anymore.

### Rules for expand/contract

The pattern only works if every schema change follows these rules:

| Change | Safe to deploy directly? | Expand/contract needed? |
|--------|--------------------------|------------------------|
| Add nullable column | Yes | No |
| Add NOT NULL column with default | Yes (with care) | Recommended for large tables |
| Rename column | No | Yes |
| Drop column | No | Yes (contract phase only) |
| Add index (CONCURRENTLY) | Yes | No |
| Change column type | No | Yes |
| Add foreign key constraint | Caution | Yes, if old code may violate it |

The key test: can both the current version and the new version of the application code run correctly against this schema, simultaneously?

## Step-by-step: database blue-green deployment with Bytebase

Bytebase maps directly to the blue-green pattern through its multi-environment deployment pipeline. Each environment (dev, staging, production-blue, production-green) is a separate target in Bytebase, with its own approval and deployment rules.

### Set up environments

In Bytebase, create four environments in order: `Dev`, `Staging`, `Production-Blue`, `Production-Green`. Each environment maps to a database instance.


### Submit a migration

A developer submits the schema change through Bytebase. The change goes through the pipeline: Dev first, then Staging, then the production environments in sequence.

![Bytebase issue detail showing a schema migration with SQL diff, environment deployment stages, and DBA approval controls](/content/blog/database-blue-green-deployment/bytebase-issue.webp)

Before the change reaches any environment, Bytebase's SQL review rules run automatically. They check for:
- Missing `WHERE` clauses on `UPDATE` or `DELETE`
- `NOT NULL` columns added without defaults
- Statements that lock tables
- Naming convention violations

Any violation blocks the issue from advancing until resolved.

### Deploy to the idle environment

When the change reaches the production stage, deploy to the idle environment first (say, Production-Green). Production-Blue is still live and receiving traffic.

Bytebase records the exact SQL applied, the timestamp, and who approved it. The change is visible in the change history before it goes anywhere near live traffic.

### Flip and monitor

After deploying to Production-Green and validating, flip the load balancer. Production-Green is now live. Production-Blue is idle.

If the new release has a problem, flip back. The schema is in expand phase, so both old and new code still work against it.

### Run the contract phase

Once the new code has been stable for your monitoring period, submit the contract migration through Bytebase. This removes the old columns or constraints that the old application code used. Route it through the same pipeline, same review process.

The full [database change management](/blog/what-is-database-change-management/) workflow in Bytebase means every migration, including the contract phase, has an approval trail. If something goes wrong weeks later, you have an exact record of what changed, when, and who approved it.

## Blue-green vs. canary vs. rolling for database changes

These three strategies have different implications for schema compatibility.

| Strategy | Traffic pattern | Schema compatibility requirement |
|----------|----------------|----------------------------------|
| Blue-green | All-or-nothing cutover | Schema must support both versions simultaneously during cutover window (minutes to hours) |
| Canary | Small percentage routed to new version, gradually increased | Schema must support both versions for the entire canary period (hours to days) |
| Rolling | Instances updated one at a time, old and new code running together | Schema must support both versions for the entire rollout period (minutes to hours per instance) |

Blue-green has the shortest overlap window, which makes it easier to reason about. Canary has the longest, because old and new code run together for days — every schema change in a canary deployment must be backward-compatible for the entire observation window.

All three strategies benefit from expand/contract. Blue-green makes the contract phase simplest because there's a clear point in time when no blue code is running.

## Common pitfalls and how to avoid them

**Skipping the expand phase and deploying schema changes directly**

The most common mistake is treating database migrations like application deployments: ship the new schema to green, flip traffic, done. If the schema removes something the old code needs, blue requests fail during the cutover window. Always expand first.

**Long-running migrations holding locks**

`ALTER TABLE` on a large table can hold an exclusive lock for minutes, blocking all reads and writes. In PostgreSQL, use `ADD COLUMN` with a default set separately (not inline on the `ALTER`), and build indexes with `CREATE INDEX CONCURRENTLY`. Test migration duration in staging on a production-sized dataset before promoting.

**Forgetting the contract phase**

Expand adds columns and keeps old ones. Without the contract phase, the database accumulates dead columns, old tables, and orphaned indexes. Treat the contract migration as a required follow-up, not optional cleanup. Track it explicitly (in Bytebase issues or a ticketing system entry) so it doesn't get dropped.

**Not testing rollback**

The value of blue-green is the rollback path. If you've never actually tested flipping back, you don't know it works. Include a rollback test in your deployment runbook. Flip to green, run smoke tests, flip back to blue, run smoke tests again. Validate the path before you need it in an incident.

**Schema drift between environments**

If dev, staging, and production-blue have drifted from each other, a migration that passes dev may fail in production. [Database schema drift](/blog/what-is-database-schema-drift/) is a silent killer in deployment pipelines. Bytebase's drift detection checks each environment against the expected schema state and flags discrepancies before a migration runs.

**Over-engineering the deployment infrastructure**

Blue-green at the database layer doesn't require two physical database clusters. Most teams implement it as two logical environment slots in their deployment pipeline, routing to the same underlying database instance with schema changes managed carefully. Physical cluster duplication is only necessary if you need data isolation between environments, which is rare.

---

Blue-green deployment reduces release risk at the application tier to a load balancer flip. The database tier requires more work: expand/contract migrations, backward-compatible schema changes, and a disciplined contract phase. The tooling (Bytebase for migration management, load balancer configuration for traffic routing) handles the mechanics. The discipline is in the schema design.

For a deeper look at the multi-environment deployment workflow in Bytebase, see [database multi-environment deployments](/blog/database-multi-environment-deployments/). For the CI/CD integration that feeds schema changes into this pipeline, see [how to build a CI/CD pipeline for database schema migration](/blog/how-to-build-cicd-pipeline-for-database-schema-migration/).



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?



This is a series of articles about SQL parsers:

1. [How We Improved Our SQL Parser Speed by 70x](/blog/how-we-improved-sql-parser-speed-70x)
1. How We Built the SQL Autocomplete Framework with ANTLR4 (this one)

---

## Background

Bytebase is a DevSecOps solution for databases, supporting a wide range of database systems. It provides a web-based [SQL Editor](/sql-editor/) equipped with robust security features, including permission control, data masking, and audit logging. However, beyond these security capabilities, a core feature for any effective SQL client is **autocomplete**.

After researching the mainstream SQL clients in the market, we found that while they perform well for simple queries, they often struggle when faced with more complex SQL statements. In this article, we’ll share how we implement autocomplete in Bytebase SQL Editor to tackle these limitations.

## Problem

Autocomplete (also known as IntelliSense) is a familiar tool that developers use regularly. The problem it addresses is well-defined: providing relevant completion suggestions based on:

1. Input text
1. Predefined syntax rules
1. The given completion position

In essence, autocomplete is a prediction problem that leverages these three contextual factors.

## "Naive" Approach

A naive approach is actually not difficult. We can simulate the user's thought process and establish a series of associative rules to handle scenarios that require auto-completion. Given my work and background, in this article, I will use simple SQL as an example.

```sql
SELECT | FROM t;
```

In the example above, the symbol `|` is used to represent the cursor position, which corresponds to the **given completion position** mentioned earlier. Based on SQL background knowledge, we can infer that the cursor is likely placed to write a column name, and since the text includes `FROM t`, we can reasonably deduce that the column should be from table t.

From this reasoning, we can summarize a simple rule: when attempting to complete fields in the `SELECT` clause, look for the subsequent `FROM` clause, identify the corresponding table name, and provide a list of the table's column names for the user to choose from.

We can refer to such a rule as an empirical rule. While describing it in words is not too difficult, implementing it does present some challenges.

**Step 1 - Identify the cursor position**: We first need to determine the cursor's location. This can be achieved using a parser or tools like regular expressions. Clearly, the complexity of the tools used to check whether the cursor is within the `SELECT` clause is directly proportional to the complexity of the scenarios they need to handle. In simpler cases, tools like regular expressions can suffice, but they may fail with more complex SQL queries, such as those involving subqueries. In these cases, more advanced methods and tools are required.

**Step 2 - Locate the `FROM` clause**: Next, we need to identify the `FROM` clause that follows immediately after, and attempt to parse the table name from it. Fortunately, parsers built with ANTLR4 naturally support **partial parsing** which means we can attempt to parse only the FROM clause without needing to parse the entire query from the beginning.

**Step 3 - Retrieve table structure information**: Finally, based on the parsed table name, we retrieve the table structure information and provide the column names for auto-completion.

Key Insights:

1. By continuously adding such empirical rules, we can progressively improve the set of scenarios that autocomplete handles, thereby enhancing the overall user experience.
1. Empirical rules heavily rely on specific syntax and must be added manually.
1. Different empirical rules may require distinct implementations, with the complexity of each rule correlating to the breadth of scenarios it aims to cover.

## Real-world Challenge

At this point, you may have realized that a naive implementation based on empirical rules is typically only suited for simpler scenarios and syntaxes. This is particularly true for Bytebase, which must handle numerous SQL dialects that are broadly similar but differ in details. A non-scalable solution would quickly lead to an explosion of engineering effort.

In [How We Improved Our SQL Parser Speed by 70x](/blog/how-we-improved-sql-parser-speed-70x), we discussed how Bytebase generates and maintains a parser for each SQL dialect based on ANTLR4. Naturally, the question arises: can we leverage ANTLR4 to create a solution for implementing autocomplete across different dialects that offers both extensibility and strong completion capabilities?

## Parser & ANTLR4

Here, we need to discuss the essence of a parser.

From a functional perspective, a parser is essentially a tool that takes a string of text and converts it into structured data according to predefined syntax rules. From an implementation standpoint, a parser is essentially a mechanism that transforms the predefined syntax rules into an [automaton](https://en.wikipedia.org/wiki/Automaton). This automaton then performs state transitions based on the input, ultimately determining whether the input conforms to the syntax rules based on its final state.

Specifically, for ANTLR4, the ANTLR4 generator creates a state machine called the **Augmented Transition Network (ATN)** based on a written grammar file (`.g4`). The ATN is an automaton that represents the grammar of the language.

When an ANTLR4-based parser processes an input string, it transitions through states based on the ATN, until it reaches a final state or the input is exhausted. This state machine-driven process allows ANTLR4 to recognize and validate whether the input conforms to the specified grammar.

For a parser, if the input string is valid, it will construct and return a parse tree (or abstract syntax tree, AST), which represents the syntactic structure of the input according to the grammar. However, if the input string is invalid, the parser will throw an error and will not proceed further. This behavior ensures that the parser only processes valid input, adhering strictly to the defined grammar rules.

```sql
SELECT a FROM t;
```

![tree](/content/blog/sql-auto-complete/parser-tree.webp)

In the example above, I use ellipses to represent grammar rules, while rounded rectangles represent terminals (tokens).

However, for the previously mentioned autocomplete example:

```sql
SELECT | FROM t;
```

The parser would fail to parse the `SELECT items` and thus halt the parsing process.

## Automation? Automaton!

So, how does this relate to autocomplete? Earlier, when discussing the naive approach, we mentioned that autocomplete is essentially a prediction problem — we want to predict the user's intended input. Importantly, this prediction is not arbitrary; it must adhere to predefined syntax rules.

Now that we know syntax rules are converted into an automaton by ANTLR4, we can apply a state-machine-based approach to autocomplete!

We can try transitioning through states in the automaton. When we reach the cursor position (the point where autocomplete is triggered), the set of valid next states becomes limited. These valid next states represent the possible autocomplete suggestions at that position. Essentially, the automaton guides us to predict what the next valid input could be, based on the syntax rules, and these predictions can be used as autocomplete options.

So, what autocomplete needs to do is:

1. Receive the input string and stop at the cursor position.
1. Examine the current state and the possible valid state transitions.
1. Convert the acceptable transitions into text and return them as autocomplete suggestions.

As you can see, this new approach is not fundamentally different from the naive method. Both approaches aim to predict the next valid input. The key difference is that the naive approach relies on specific, syntax-dependent empirical rules, which makes its implementation inherently tied to particular grammar structures. In contrast, the automaton-based approach is grounded in a universal mechanism that applies across SQL dialects. This makes it syntax-agnostic at least during the first two steps of the process (receiving the input and examining state transitions).

The same underlying core enables cross-syntax extensibility. This is the remarkable capability that ANTLR4 brings, and it's one of the key advantages it offers — the ability to provide an extensible solution for autocomplete across various SQL dialects.

## Cross-SQL Dialect Autocomplete on ANTLR4

Now that we've identified the core approach, we need to return to the real-world problem at hand. Bytebase aims to provide accurate and effective SQL autocomplete functionality across different SQL dialects.

In practice, we've found that users typically expect the SQL autocomplete feature to handle several common scenarios, such as:

**Scenario 1: Keywords in SQL can be long or hard to remember, so users want autocomplete suggestions after typing just a few characters.**

Solution: The completion of SQL keywords is essentially a matter of transitioning to terminal symbols in the automaton's state transitions. These keyword definitions are part of the lexical syntax rules, so this can be handled in a syntax-agnostic manner.

**Scenario 2: Providing completions for table names, column names, function names, etc., at appropriate places, similar to variable name completion in programming languages.**

Solution: In SQL, these names can be grouped as Identifiers, which are defined by specific syntax rules. We don't need to find the corresponding terminal symbols for each case; instead, we can focus on identifying relevant syntax rules and, with additional information (like database schema), provide the appropriate table names, column names, functions, etc. This, too, can be done in a syntax-agnostic way for the most part.

**Scenario 3: Suggesting less commonly used SQL syntax, such as how to write the next part of an `ALTER TABLE` statement, or other similar code snippet completions.**

Solution: This is akin to code snippet completions, like how `FOR LOOP` or other code structures are completed in languages like C. It involves predicting common patterns and SQL syntax structures, which can be handled with specific rules or templates.

Here, we will focus on scenarios 1 and 2.

The entire autocomplete architecture is actually very simple and consists of two parts:

1. **Syntax-Agnostic Code Completion Core based on ANTLR4**. It uses the automaton and input to identify terminal symbols (keywords) and the grammar rules of interest (Identifiers). We refer to the latter as Candidate Rules.

1. **Syntax-Specific Post-Processing**. It primarily converts the candidate rules into corresponding strings and returns them to the user.

## Autocomplete Core on ANTLR4

Thanks to ANTLR4! The ANTLR4 runtime provides interfaces for using and manipulating the ATN, which makes it easy for us to do what we need with the ATN.

The general idea here is to first traverse the ATN to the cursor position, then find the possible subsequent states from the current state. These possible subsequent states are referred to as the `Follow Set`. We then check the `Follow Set` to see if it contains any syntax rules or terminals that we are interested in. After that, we collect these candidates and pass them to the next stage.

Here’s a small optimization: it's clear that the `Follow Set` only depends on the ATN, so we can pre-compute and cache all the `Follow Sets` for each specific grammar. This way, we don't need to recalculate them every time we perform autocomplete.

The approach here is fairly straightforward, but the technical implementation details are quite extensive. Due to space constraints, we won't go into further explanation here. Let's save that for the next article!

## Grammars

After receiving the candidates, we need to classify and process them.

For **terminals**, there isn't much processing required; we simply find the corresponding string for the terminal symbol and return it.

For **candidate rules**, we need to handle them in a categorized manner. Here, we will take table names and column names as examples.

Let's first assume a simplified SQL syntax:

```sql
SelectStatement:
	SelectClause FromClause SEMICOLON;

SelectClause:
	SELECT SelectItems;

FromClause:
	FROM FromItems;

SelectItems:
	ColumnName (COMMA ColumnName)*;

ColumnName:
	(TableName DOT)? Identifier; // Lower performance but more readable.

FromItems:
	TableName (COMMA TableName)*;

TableName:
	Identifier;
```

For the syntax mentioned above, the rules we are typically interested in are `TableName` and `ColumnName`. Let's now look at the following example:

```sql
SELECT * FROM ｜
```

Using the autocomplete core, we can determine that the candidate rule contains a `TableName`. From there, we simply need to look up all the `TableName` entries in the schema information and return them to the user.

The example for column names is a bit more complicated:

```sql
SELECT | FROM t;
```

Similarly, through the autocomplete core, we can identify that the candidate rule contains a `ColumnName`. The simplest approach would be to return all column names from the schema. However, a clear observation here is that the `FROM t` clause restricts the column names to those that belong to table `t`.

Since the autocomplete core only parses up to the cursor position, it doesn't have information about the `FROM` clause. Therefore, we need to do some additional work:

1. We can use ANTLR4's Lexer to locate the `FROM` keyword that follows.
1. With ANTLR4’s partial parsing capabilities, we try to parse `FROM t` as a `FromClause`.
1. Once we have the parse tree, we can extract the table name `t`. This requires using ANTLR4's `Visitor/Listener` pattern.
1. Once we have the table name from the `FROM` clause, we can return only the columns belonging to table `t` to the user.

## Afterwords

This article mainly introduces the thought process behind how Bytebase built an autocomplete framework with cross-SQL dialect capabilities. The journey and ideas shared here aim to offer some insights and inspiration to the readers.

The example provided is highly simplified for ease of understanding, but the real-world scenarios are much more complex. This includes, but is not limited to, subqueries, nested statements, CTEs (Common Table Expressions), and so on. If you're interested, stay tuned for future articles where we will dive deeper into the full implementation, including how to use the ANTLR4-based autocomplete core, syntax-specific processing, and how to integrate with [LSP (Language Server Protocol)](https://en.wikipedia.org/wiki/Language_Server_Protocol) to provide a complete and robust SQL autocomplete.

It’s evident that some dialect-specific work still needs to be done to ensure a sufficiently good autocomplete experience. There's no denying that this approach drastically reduces development and maintenance costs, allowing a small team to maintain autocomplete functionalities for multiple dialects. Currently, Bytebase has implemented and maintains autocomplete modules for five different dialects based on this framework. They are
all open-sourced:

- [Oracle](https://github.com/bytebase/bytebase/blob/main/backend/plugin/parser/plsql/completion.go)
- [SQL Server](https://github.com/bytebase/bytebase/blob/main/backend/plugin/parser/tsql/completion.go)
- [PostgreSQL](https://github.com/bytebase/bytebase/blob/main/backend/plugin/parser/pg/completion.go)
- [MySQL](https://github.com/bytebase/bytebase/blob/main/backend/plugin/parser/mysql/completion.go)
- [DynamoDB](https://github.com/bytebase/bytebase/blob/main/backend/plugin/parser/partiql/completion.go)

Due to Bytebase's tech stack choice, we implemented the ANTLR4-based autocomplete core in Golang, and we've also [open-sourced it](https://github.com/bytebase/bytebase/blob/main/backend/plugin/parser/base/c3.go).

During implementation, we also identified several areas for improvement in the ANTLR4 Golang runtime. We are actively verifying and contributing to upstream improvements. Let's work together to make ANTLR4 even better!

## You don't Need to Reinvent the Wheel

We’ve been working on SQL Editor autocomplete for about two years, and we’re still refining it. You don't need to go through this again and can just adopt Bytebase SQL Editor directly:

- Let your developers visit the Bytebase console and interact with the database from SQL Editor.
- [Embed SQL Editor](https://docs.bytebase.com/tutorials/embed-sql-editor/) into your own internal web portal.

<HintBlock type="info">

You can try the demo at https://sql-editor.com (no sign-up required).

</HintBlock>

## References

- https://github.com/antlr/antlr4
- The Definitive ANTLR 4 Reference
- https://www.abstractsyntaxseed.com/blog/antlr4-autocomplete-library/introduction
- https://github.com/mike-lischke/antlr4-c3 - a TypeScript c3 implementation


How Bytebase leverages ANTLR4 to build a framework to implement SQL autocomplete for different SQL dialects.

Bytebase ブログ

データベース開発のスタンダードを体験する

ソリューションとプラットフォーム

比較

法的事項

リソース

会社

ソリューションとプラットフォーム

リソース

比較

会社

法的事項