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

On-Behalf-Of Database Access for AI Agents

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

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

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

What is a Database MCP Server?

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

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

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

When the Agent Writes the Migration, Who Approves It?

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

What is Database PAM (Privileged Access Management)?

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

SQL Server Schema Migration and Change Management

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

Managing Database Passwords and Secrets: Best Practices

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

MySQL Audit Logging Guide

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

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

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

Static vs Dynamic Data Masking: When to Use Which

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

BigQuery Dynamic Data Masking

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

SQL Server Dynamic Data Masking

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

Oracle Dynamic Data Masking

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

How to Handle Ad-hoc Database Data Changes?

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

What is Database Access Control (DAC)?

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

Dynamic Data Masking Best Practices

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

How Bytebase Handles Audit Logging

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

Database Access Control (DAC) Best Practices

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

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

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

Database Design Patterns Every Developer Should Know

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

pgvector: How to Add Vector Search to PostgreSQL

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

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

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

From Schema as Code to Schema as Context

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

How to Fix Slow MySQL Queries: A Practical Guide

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

Database Blue-Green Deployment: A Practical Guide

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

How to Govern AI Agent Database Access

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

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

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

Postgres Query Plan: How to Read and Fix Performance Issues

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

The Anatomy of Postgres Permission ERROR 42501

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

Postgres Logs Explained: A Practical Guide for Production

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

Database Multi-Environment Deployments - The Challenges and Patterns

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

Database Audit Logging: Two Layers, One Trail

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

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

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

Require Primary Key: A SQL Review Rule Explained

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

Enforce NOT VALID in CHECK: A SQL Review Rule Explained

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

Postgres Audit Logging Guide

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

Prohibit CASCADE: A SQL Review Rule Explained

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

How to Migrate from Supabase to AWS

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

What is Enterprise Database Management (EDM)?

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

Postgres Transaction ID (XID) Wraparound

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

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

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

Database Compliance for LGPD: Implications and Best Practices

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

What is Postgres LATERAL JOIN?

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

MySQL Online DDL: A Practical Guide

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

SQL Table Naming Dilemma: Singular vs. Plural

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

Postgres Case Sensitivity Explained

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

What is pg_database_owner in Postgres

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

Which Postgres Operation causes a table rewrite

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

Postgres Row-Level Security Footguns

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

Postgres Rollback Explained

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

Postgres Schema Migration without Downtime

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

gh-ost Limitations You Need to Know Before Production

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

Postgres best practices I wish every app developer knew

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

How to Use Postgres CREATE INDEX CONCURRENTLY

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

Database Security Lifecycle Management

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

HIPAA Data Security and Retention Requirements

Compare the online index creation difference between Postgres and MySQL

Postgres vs. MySQL: Online Index Creation

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

SOC 2 Data Security and Retention Requirements

Compare the different indexing options between Postgres and MySQL

Postgres vs. MySQL: Indexing Options

Compare the JSON support difference between Postgres and MySQL

Postgres vs. MySQL: JSON Support

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

Postgres Row-Level Security (RLS) Limitations and Alternatives

Compare the DDL transaction difference between Postgres and MySQL

Postgres vs. MySQL: DDL Transaction Difference

Understanding DynamoDB Pricing (2026)

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

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

Understanding AWS RDS Pricing (2026)

Explain why MySQL provides a variety of timeout settings.

MySQL Timeout Explained

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

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

The top 10 database schema design best practices, ranked by what each mistake costs to fix once the table holds real data. With PostgreSQL snippets.

Top 10 Database Schema Design Best Practices

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

Database Version Control Best Practice

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

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

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

Database Security Compliance

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

What is a Change Advisory Board (CAB)?

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

What is Production Database

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

What is Database Activity Monitoring (DAM)?

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

Multi-Tenant Database Architecture Patterns Explained

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

ServiceNow and GitHub Integration for Database Change Automation

Pros and Cons between choosing uuidv1, uuidv4, uuidv7

How to Choose Database UUID?

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

Primary Key vs Foreign Key: What Are the Differences?

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

What is Write Ahead Logging (WAL)

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

Just-in-Time Database Access

Explain the different database permission levels

Database Security: Read, Write, and Admin Permission Levels

Explain database rollback and how to automate it

Database Rollback: When Shit Hits the Fan

Database DevSecOps integrates security throughout the database development lifecycle. The threats and controls for each of the four access paths, from schema migration to AI agents.

What is Database DevSecOps?

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

What is Dynamic Data Masking (DDM)

Database DevOps integrates DevOps practices into database work. Why the database lagged a decade behind code CI/CD, the typical workflow it replaces, and how to measure whether you actually have it.

What is Database DevOps?

Explain why Postgres provides a variety of timeout settings.

Postgres Timeout Explained

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

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

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

Database Compliance for GDPR: Implications and Best Practices

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

Understanding Google Cloud AlloyDB Pricing (updated for 2026)

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

Snowflake CI/CD and DevOps Best Practice

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

All the Database Tools Bytebase Replaces

Database schema drift is when the actual schema in the live database differs from the source of truth. How drift happens, why it causes outages, and how to detect and fix it.

What is Database Schema Drift?

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

Postgres Dynamic Data Masking

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

What is Database Version Control?

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

MySQL Dynamic Data Masking

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

What is Snowflake Auto-Suspend and Auto-Resume?

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

Snowflake Schema Change and CI/CD Best Practice

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

What is Database Schema Migration?

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

How to Handle Database Migration / Schema Change?


pgvector is a PostgreSQL extension that lets you store vectors in a table column and search them by similarity. It reached version 0.8.2 in late 2025, and it now ships by default on most hosted PostgreSQL services: AWS RDS, Google Cloud SQL, Supabase, and Neon. If you already run PostgreSQL, you can add vector search without standing up a separate database.

Vectors are arrays of numbers that represent meaning. A sentence about "database migrations" and a sentence about "schema changes" end up with vectors that sit close to each other, even though they share no keywords. That closeness is the whole point of vector search: it finds semantically related content rather than exact word matches.

## What pgvector stores

An embedding model converts text into a list of numbers. Send "I love coffee" to the model and it returns 1,536 numbers. Send "I enjoy espresso" and you get a nearly identical list, even though the words are completely different. Send "database migration" and the numbers look nothing like either of those. The model learned these relationships from patterns in text; you don't define them yourself.

pgvector adds a column type to PostgreSQL that stores these lists. You set the dimension count when you create the column (1,536 for OpenAI's `text-embedding-3-small`) and it stays fixed from then on. When you search, pgvector finds the rows whose numbers are closest to your query, which means closest in meaning, not in wording.

## Installing pgvector

On a self-hosted PostgreSQL server:

```bash
# Ubuntu/Debian
sudo apt install postgresql-16-pgvector

# macOS with Homebrew
brew install pgvector
```

Then enable it inside the database:

```sql
CREATE EXTENSION vector;
```

On hosted services (RDS, Google Cloud SQL, Supabase, Neon), the package is already installed, so `CREATE EXTENSION vector;` is all you need.

## Storing and querying vectors

You add a `vector` column to any table and store embeddings like any other value:

```sql
CREATE TABLE documents (
  id        SERIAL PRIMARY KEY,
  content   TEXT,
  embedding vector(1536)
);
```

In practice you generate the embeddings in application code, sending text to an embedding model (OpenAI, Cohere, a local model), then inserting the returned array. To search, you pass a query embedding and order by distance:

```sql
-- Find the 5 documents most similar to a query
SELECT content, embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;
```

The `<=>` operator computes cosine distance, the most common choice for text. pgvector also supports L2 distance (`<->`) and inner product (`<#>`). The advantage over a dedicated vector database shows up here: you can combine vector search with any SQL filter or join in the same query.

## Indexing for performance

Without an index, every similarity query scans every row. That's fine up to roughly 100,000 rows, but it slows down past that. Add an HNSW index to switch to approximate nearest-neighbor search, which is much faster at the cost of very slightly less precision:

```sql
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
```

HNSW can be built on an empty table and handles new inserts without a rebuild. On large tables, use `CREATE INDEX CONCURRENTLY` to avoid locking. pgvector also has an IVFFlat index type that uses less memory, but HNSW is the better default for most cases.

## pgvector vs. a dedicated vector database

The main alternative to pgvector is a purpose-built vector database: Pinecone, Weaviate, Qdrant, or Milvus. Which one fits depends on your scale and how much operational complexity you're willing to take on.

| Factor | pgvector | Dedicated vector DB |
|--------|----------|---------------------|
| Setup | `CREATE EXTENSION` | Separate service to run |
| SQL joins and filters | Yes, natively | Metadata filtering only |
| ACID transactions | Yes | Varies |
| Comfortable scale | Up to ~10M rows | Hundreds of millions |
| Operational overhead | Existing PostgreSQL infra | New service to manage |
| Managed options | RDS, Supabase, Neon | Pinecone, Weaviate Cloud |

For most teams building a first RAG pipeline or adding semantic search to an existing product, pgvector is the right place to start. You get vector search without new infrastructure, and your vectors live next to your relational data where you can query them together.

A purpose-built database earns its keep at very large scale (50M+ vectors), when you need extremely low query latency, or when you want built-in hybrid search. [PostgreSQL vs MongoDB](/blog/postgres-vs-mongodb/) covers a related trade-off: MongoDB Atlas has its own vector search, and if your data already lives there, that may be the simpler path.

## pgvector and schema migrations

Adding pgvector to a production database is a schema change like any other: you enable an extension, add columns, and build indexes. A couple of things are worth knowing before you do.

- HNSW index builds are CPU-intensive. Use `CREATE INDEX CONCURRENTLY` on production tables.
- Switching embedding models means changing the dimension count, which means dropping the old column and backfilling all the embeddings. Plan that as a multi-step migration, not a one-liner.

pgvector follows the same lifecycle as any other PostgreSQL extension: enable it in staging first, confirm the version matches production, then roll it out. [What is database migration?](/blog/what-is-database-migration/) covers safe sequencing. pgvector also shows up consistently among the [top PostgreSQL extensions](/blog/top-postgres-extension/) in active use.

[Bytebase](https://www.bytebase.com) handles PostgreSQL schema migrations with a review step before SQL runs in production, and keeps a full history of every change, including extension additions and index builds. For teams managing pgvector alongside other [PostgreSQL schema changes](/blog/postgres-schema-migration-without-downtime/), that review step is where you catch problems before they reach production.

## FAQ

**Does pgvector work with all PostgreSQL hosting providers?**
Most major providers include it as of 2024: AWS RDS (PostgreSQL 15.2+), Google Cloud SQL, Azure Database for PostgreSQL, Supabase, Neon, and Render.

**How many vectors can pgvector handle?**
Most teams run comfortably up to 5-10 million vectors on a well-sized PostgreSQL instance. Approximate indexes (HNSW, IVFFlat) help the most past 100,000 rows. Beyond ~50M vectors, a dedicated vector database is worth evaluating.

**Is pgvector production-ready?**
Yes. pgvector 0.7.0 (March 2024) added HNSW indexing, which was the main gap for production workloads. Version 0.8.2 is the current stable release. It runs in production at Supabase, Neon, and plenty of teams building RAG applications.



An upsert is a single database operation that inserts a new row or updates an existing one, depending on whether a conflict is found on a primary key or unique index. The name is just "update" and "insert" stuck together. Most SQL databases support the pattern natively, but the syntax differs: MySQL uses `INSERT ... ON DUPLICATE KEY UPDATE`, PostgreSQL uses `INSERT ... ON CONFLICT`, and SQL Server uses `MERGE`.

The basic idea is the same everywhere, but the edge cases are not. Each implementation behaves differently when many users write at the same time, or on tables with multiple unique indexes. This guide walks through the syntax for each database, the pitfalls worth knowing before you ship, and how upserts interact with migrations.

## What is upsert and when to use it

An upsert is useful whenever you need to write data without first checking whether a row already exists. A few common cases:

- **Syncing external data**: you pull records from an API and write them to a local table, updating the row if it's already there and inserting it if not.
- **Safe retries**: if a write fails and you retry it, an upsert produces the same result whether it runs once or ten times.
- **Counters**: increment a value if the row exists, or start it from scratch if it doesn't.
- **Seed data in migrations**: write default rows that should always exist, without failing if they're already there.

Without native upsert support, the usual workaround is SELECT-then-INSERT/UPDATE: two separate queries with a gap in between where two users can race to insert the same row. Upsert collapses that into one step, which is why it's worth using even when it isn't strictly necessary.

## MySQL: INSERT ON DUPLICATE KEY UPDATE

MySQL's upsert syntax:

```sql
INSERT INTO table (col1, col2, ...)
VALUES (val1, val2, ...)
ON DUPLICATE KEY UPDATE col2 = VALUES(col2), ...;
```

When the row you're inserting would conflict with an existing primary key or unique index, MySQL runs the `UPDATE` clause instead of failing. To reference the new values inside that `UPDATE` clause, you use the `VALUES()` function on MySQL 5.x, or column aliases on MySQL 8.0.19 and later:

```sql
-- MySQL 8.0.19+ (preferred)
INSERT INTO page_views (page_id, view_count)
VALUES (42, 1) AS new_vals
ON DUPLICATE KEY UPDATE view_count = view_count + new_vals.view_count;

-- Older syntax using VALUES() function
INSERT INTO page_views (page_id, view_count)
VALUES (42, 1)
ON DUPLICATE KEY UPDATE view_count = view_count + VALUES(view_count);
```

**Affected rows behavior**

MySQL's affected-rows count is not what you'd guess:

| Result | Affected rows |
|--------|--------------|
| Row inserted | 1 |
| Existing row updated | 2 |
| Row matched, no columns changed | 0 |

This matters if your code checks the affected-row count to work out what happened. Most ORMs hide the detail, but raw database clients hand you the raw number, so it's worth knowing the convention.

**The multiple unique index caveat**

If a table has more than one unique or primary key index, and the row you're inserting matches more than one of them, MySQL only runs the update for whichever index it finds first. MySQL's own documentation warns against using `ON DUPLICATE KEY UPDATE` on tables like this, and for good reason: the result depends on internal index ordering and isn't predictable.

For tables with a single primary key, which is the common case, the behavior is straightforward. One thing to know: for [AUTO_INCREMENT primary keys](/reference/mysql/how-to/how-to-use-auto-increment-mysql/), the counter still ticks up even when a conflict happens and no row is inserted, so you can end up with gaps in your IDs.

**REPLACE INTO: skip this**

MySQL also has `REPLACE INTO`, which handles conflicts by deleting the old row and inserting a fresh one. That sounds equivalent, but it isn't. It resets the primary key on auto-increment tables, fires DELETE plus INSERT triggers instead of UPDATE triggers, and can break foreign key relationships that point at the deleted row. Stick with `ON DUPLICATE KEY UPDATE`.

## PostgreSQL: INSERT ... ON CONFLICT

PostgreSQL added `INSERT ... ON CONFLICT` in version 9.5. The syntax:

```sql
INSERT INTO table (col1, col2, ...)
VALUES (val1, val2, ...)
ON CONFLICT (conflict_column)
DO UPDATE SET col2 = EXCLUDED.col2, ...;
```

`EXCLUDED` is the special name PostgreSQL gives to the row you tried to insert. You can reference it anywhere in the `SET` clause, which reads more cleanly than MySQL's `VALUES()` approach:

```sql
INSERT INTO page_views (page_id, view_count, last_seen)
VALUES (42, 1, NOW())
ON CONFLICT (page_id)
DO UPDATE SET
    view_count = page_views.view_count + EXCLUDED.view_count,
    last_seen = EXCLUDED.last_seen;
```

**DO NOTHING**

When you want to silently skip conflicts rather than update:

```sql
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;
```

`DO NOTHING` is handy for seed data you're safe to re-run, or any pipeline where a duplicate just means the work was already done.

**Conflict target options**

PostgreSQL requires a conflict target when you use `DO UPDATE`:

```sql
-- By column (most common; infers the unique index)
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name

-- By named constraint
ON CONFLICT ON CONSTRAINT users_email_key DO NOTHING

-- By partial unique index
ON CONFLICT (user_id) WHERE is_active = true DO UPDATE SET ...
```

Targeting by column name is usually safer than hard-coding a constraint name. `ON CONFLICT (email)` keeps working if you drop and recreate the unique constraint, or if PostgreSQL rebuilds the underlying index, whereas `ON CONFLICT ON CONSTRAINT users_email_key` breaks the moment that specific constraint name no longer exists.

**Safe under concurrent writes**

`INSERT ... ON CONFLICT` is safe when multiple users or processes write to the same table at the same time. PostgreSQL guarantees that exactly one insert or update fires per row, so two sessions can't both win on the same row. This is the part the SELECT-then-INSERT pattern gets wrong, where a second user can slip in between your SELECT and your INSERT.

## SQL Server and other databases

**SQL Server: MERGE**

SQL Server uses `MERGE`, which is also the SQL standard syntax (SQL:2003):

```sql
MERGE INTO target_table AS target
USING (VALUES (42, 1)) AS source (page_id, view_count)
ON target.page_id = source.page_id
WHEN MATCHED THEN
    UPDATE SET target.view_count = target.view_count + source.view_count
WHEN NOT MATCHED THEN
    INSERT (page_id, view_count) VALUES (source.page_id, source.view_count);
```

`MERGE` requires a semicolon terminator; leave it off and you get Error 10713.

It's more verbose than the MySQL or PostgreSQL upsert, but it's also more general. You can handle MATCHED, NOT MATCHED BY TARGET, and NOT MATCHED BY SOURCE in one statement, which makes it a good fit for full table synchronization rather than just single-row writes. [MySQL vs SQL Server](/blog/mysql-vs-sqlserver/) covers more syntax differences between the two.

**SQLite**

SQLite gives you two options. `INSERT OR REPLACE` has the same delete-then-insert problem as MySQL's `REPLACE INTO`, so prefer the cleaner `INSERT ... ON CONFLICT` syntax added in version 3.24.0 (released June 2018), which closely mirrors PostgreSQL. If you're on an older SQLite version, upgrade before relying on `ON CONFLICT DO UPDATE`.

```sql
INSERT INTO page_views (page_id, view_count)
VALUES (42, 1)
ON CONFLICT(page_id) DO UPDATE SET view_count = view_count + excluded.view_count;
```

Note: SQLite spells `excluded` in lowercase; PostgreSQL accepts both cases.

**Oracle**

Oracle uses `MERGE` with syntax similar to SQL Server. There's no `INSERT ... ON CONFLICT` shorthand.

## Performance and locking considerations

Upserts save a round trip compared to SELECT-then-INSERT/UPDATE, but they aren't free. They carry some locking overhead, and how much depends on the database.

**MySQL locking**

`ON DUPLICATE KEY UPDATE` locks the conflicting row even when the update doesn't change any values. On tables with heavy write traffic and frequent conflicts, this can push up wait times. For workloads where most writes are new inserts and conflicts are rare, it can actually be faster to catch the duplicate-key error in your application code and retry as an UPDATE. That's only worth doing if you handle the exception carefully, though.

**PostgreSQL**

`INSERT ... ON CONFLICT` locks the conflicting row only for the duration of that statement, then releases it. Multiple users can safely upsert to the same table at the same time without stepping on each other.

**Deadlocks in batch upserts**

When two users upsert batches of rows at the same time, they can get stuck waiting for each other if their batches overlap in opposite orders: user A holds a lock on row 1 and waits for row 2, while user B holds row 2 and waits for row 1. The remedy is simple. Sort your rows by primary key before batching, so every session processes them in the same order:

```sql
-- Sort by primary key before batching
INSERT INTO page_views (page_id, view_count)
VALUES (1, 5), (2, 3), (7, 1)
ON CONFLICT (page_id)
DO UPDATE SET view_count = page_views.view_count + EXCLUDED.view_count;
```

**Checking what happened (PostgreSQL)**

PostgreSQL's `RETURNING` clause lets you see whether the row was inserted or updated:

```sql
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING xmax;
-- xmax = 0 → row was inserted; non-zero → row was updated
```

## Upsert in application code and migrations

**ORM support**

Most ORMs support upsert natively, though the method name varies:

| ORM | Method |
|-----|--------|
| Prisma | `upsert()` |
| Sequelize | `upsert()` |
| SQLAlchemy | `insert().on_conflict_do_update()` |
| Knex.js | `onConflict().merge()` |
| TypeORM | `save()` with `upsert: true` |

**Upserts in migration scripts**

Upsert-based seed data is safe to run more than once: running the migration twice produces the same result. For reference tables such as countries, currencies, status codes, or permission names, this is much safer than a plain `INSERT`, which fails if the rows are already there:

```sql
-- Safe to run multiple times
INSERT INTO currencies (code, name)
VALUES ('USD', 'US Dollar'), ('EUR', 'Euro')
ON CONFLICT (code) DO NOTHING;
```

One thing to watch for: if you add a `UNIQUE` constraint to a column as part of a migration, any duplicate values already in that column will make the `ALTER TABLE` fail before your upsert even runs. Clean up the existing duplicates first. [What is Database Schema Migration?](/blog/what-is-database-migration/) covers safe migration sequencing in more detail.

Which conflicts an upsert detects depends entirely on which primary keys and unique indexes exist on the table. [Primary key vs foreign key](/blog/primary-key-vs-foreign-key/) explains the difference if you're trying to work out why a particular upsert is or isn't triggering the update path.

## Bytebase and safe upsert migrations

Upsert patterns interact with schema changes in ways that aren't always obvious. Adding or removing a unique index changes which conflicts the upsert detects. Renaming a constraint in PostgreSQL breaks any `ON CONFLICT ON CONSTRAINT` references in your application code. Changing a column's data type can quietly affect whether two rows are considered equal during the conflict check.

[Bytebase](https://www.bytebase.com) reviews schema migrations before they reach production. Its SQL review engine enforces rules like requiring indexes on columns used in WHERE clauses, blocking `DROP INDEX` without an explicit override, and flagging tables that lack a primary key, which are the same constraints that upsert logic depends on. For teams running [PostgreSQL and MySQL side by side](/blog/postgres-vs-mysql/), Bytebase runs the same review workflow against both databases regardless of which upsert dialect each one uses.

## FAQ

**What is the difference between upsert and MERGE?**
`MERGE` is the SQL standard way to do an upsert, used by SQL Server and Oracle. MySQL and PostgreSQL have their own shorter syntax that covers the insert-or-update case. `MERGE` can also handle DELETE in the same statement, which makes it useful for syncing a whole table rather than individual rows.

**Can I use ON CONFLICT with a two-column unique index in PostgreSQL?**
Yes. List both columns: `ON CONFLICT (col1, col2) DO UPDATE SET ...`. PostgreSQL finds the unique index that covers exactly those columns. If no such index exists, you get an error at runtime.

**Why does MySQL ON DUPLICATE KEY UPDATE return 2 affected rows on an update?**
It's MySQL's convention: 1 means the row was inserted, 2 means an existing row was updated, and 0 means the row matched but nothing changed. It's intentional and documented. Most ORMs hide it, but raw database clients return the raw number.

**Is REPLACE INTO the same as upsert?**
No. `REPLACE INTO` deletes the old row and inserts a brand new one. That resets the primary key on auto-increment tables, fires DELETE triggers instead of UPDATE triggers, and can break foreign keys that point at the deleted row. Use `INSERT ... ON DUPLICATE KEY UPDATE` instead.

**Does PostgreSQL ON CONFLICT work with partial indexes?**
Yes. If your unique index was created with a `WHERE` clause, include the same condition in the conflict target: `ON CONFLICT (col) WHERE is_active = true DO UPDATE SET ...`. PostgreSQL only uses the partial index when the row being inserted matches that condition.



For over two decades, the industry has been telling teams to treat database schemas like application code: version it, review it, ship it through a pipeline. That advice still holds. But a new consumer has shown up at the table, and it doesn't read pull requests the way your DBA does.

LLMs and AI agents are increasingly the ones [generating SQL](https://spider2-sql.github.io/), proposing migrations, and executing data changes. For these actors, a versioned migration file is nowhere near enough. They need **context**.

## Schema as Code Was a Necessary First Step

The [Database-as-Code](https://martinfowler.com/articles/evodb.html) movement (let's set aside the migration-based vs. state-based debate for now) brought real discipline to schema management. Tools like Liquibase and Flyway gave teams version-controlled migrations applied through CI/CD pipelines and a single source of truth, the same way Infrastructure as Code did. Bytebase extended this with review workflows, access control, and governance.

Here's the catch. A `CREATE TABLE` statement tells you the shape of the data. It tells you nothing about who is allowed to see or modify that data, which columns hold PII or financial or health records, what masking rules kick in when different roles query it, or what approval a change must clear before it touches production.

For a human developer, that knowledge lives in tribal memory and old Slack threads. For an AI agent, if it isn't codified, it doesn't exist.

## Agents Need More than DDL

Point a text-to-SQL agent at a production database and the typical setup feeds it a schema dump (table names, column types, maybe a few comments) and asks it to generate queries.

This works for demos. It falls apart in production.

The agent doesn't know that `hr.employees.salary` is Confidential and should be masked. It doesn't know that `customer.ssn` needs a compliance-mandated masking algorithm. It has no idea that querying `payments` requires just-in-time access approval that expires in one hour.

The real risk isn't that the agent writes a bad query. It's that the agent writes a **correct** query that should never have run in the first place.

## Context Is Everything Surrounding the Schema

The schema (tables, columns, indexes, constraints) is the structural skeleton. Context is everything that gives it meaning and keeps it safe:

- **Data Classification.** Every column tagged with its sensitivity level (public, internal, confidential, or restricted) as structured, machine-readable metadata that drives downstream policies.

- **Dynamic Data Masking.** Full masking, partial masking, or custom algorithms applied at query time based on who, or what, is asking. This is the enforcement boundary that stops sensitive data from leaking into an LLM's context window.

- **Access Control.** Fine-grained, role-based access beyond database-native GRANTs. Project-level scoping, environment-level restrictions, and just-in-time access with automatic expiration.

- **SQL Review Policies.** 200+ lint rules covering anti-pattern detection, naming conventions, and performance guardrails, serving as the automated reviewer when an agent generates or proposes SQL.

- **Change Workflows.** The process itself, codified: which changes need DBA approval, which environments need staged rollout, and what the rollback plan is. These workflows are what keep autonomous systems from making irreversible mistakes.

Audit trails (every query, every change, every access request) aren't something you codify. They fall out at runtime as a byproduct of enforcing the policies above, giving you accountability over what your agents actually did.

## Codifying Context: Terraform, API, and Your Own Format

You can't hand an LLM a PDF of your security policies and expect it to comply. You need machine-readable, version-controlled policy definitions enforced programmatically.

Bytebase manages all the context layers above and exposes them through its [Terraform Provider](https://docs.bytebase.com/integrations/terraform/overview) and [API](https://docs.bytebase.com/integrations/api/overview), so the same CI/CD pipeline that provisions a database also provisions who can access it, what masking rules apply, and what review process governs changes. For teams that want to go further, the API lets you build your own context layer in whatever format your agents consume best: pull classification taxonomies as JSON, export masking policies as structured data, or serialize the whole thing as YAML, TOML, or Markdown.

When everything is codified, policies are either enforced at the platform level (masking at query time, access denied before the query runs) or available as structured metadata the agent reasons about before it acts.

Schema as Code got us version control. Schema as Context is what gets us AI-readiness. The schema dump told the model what your data looks like. The question now is whether you'll also tell it what your data is allowed to do.

## References

- [Evolutionary Database Design](https://martinfowler.com/articles/evodb.html) - Martin Fowler and Pramod Sadalage's foundational article on treating database changes as evolutionary, version-controlled migrations.
- [Spider 2.0](https://spider2-sql.github.io/) - A benchmark for evaluating LLMs on enterprise-level text-to-SQL tasks across real-world databases.

## The rest of the series

- [What is a Database MCP Server?](/blog/what-is-a-database-mcp-server/) - the foundation: what a database MCP server is, and why a raw one is fine locally but bites in production.
- [Governed MCP vs. Raw MCP](/blog/governed-mcp-vs-raw-mcp/) - the MCP server is the one place agent database access is governed or left wide open.
- [How to Govern AI Agent Database Access](/blog/how-to-govern-ai-agent-access-to-enterprise-data/) - the read path: identity, authorization, masking, and audit for an agent that queries your data.
- [When the Agent Writes the Migration, Who Approves It?](/blog/governing-agent-authored-database-changes/) - the write path: governing schema and data changes an agent authors.


The
Bytebase Blog

Explore the standard for database governance

Solutions & Platform

Resources

Change Management

Access & Security

AI & Agents

Comparisons

Company

Legal

The Bytebase Blog

Explore the standard for database governance

The
Bytebase Blog