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?

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

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

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

Enterprise database management is the practice of organizing, securing, and optimizing database systems and workflows at scale, keeping data integrity, availability, and compliance intact across large organizations.

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

A practical look at what LGPD (Brazil's General Personal Data Protection Act) means for database operations, how it differs from GDPR, and the controls teams need to actually comply.

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

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

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

Understanding AWS RDS Pricing (2025)

Explain why MySQL provides a variety of timeout settings.

MySQL Timeout Explained

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

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

The top 10 database schema design best practices with code snippets

Top 10 Database Schema Design Best Practices

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

Database Version Control Best Practice

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

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

What is Database Activity Monitoring (DAM)?

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

Multi-Tenant Database Architecture Patterns Explained

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

ServiceNow and GitHub Integration for Database Change Automation

Pros and Cons between choosing uuidv1, uuidv4, uuidv7

How to Choose Database UUID?

What are the differences between Primary Key and Foreign Key

Primary Key vs Foreign Key: What are the Differences?

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

What is Write Ahead Logging (WAL)

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 is the practice of integrating security throughout the entire database development lifecycle, much like how DevSecOps evolved from DevOps.

What is Database DevSecOps?

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

What is Dynamic Data Masking (DDM)

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

What is Database DevOps?

Explain why Postgres provides a variety of timeout settings.

Postgres Timeout Explained

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

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

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

Database Compliance for GDPR: Implications and Best Practices

How Bytebase tracks database changes throughout the lifecycle

How Bytebase Tracks Database Change

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

Understanding Google Cloud AlloyDB Pricing (updated for 2025)

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

Snowflake CI/CD and DevOps Best Practice

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

All the Database Tools Bytebase Replaces

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

What is Database Schema Drift?

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

Postgres Dynamic Data Masking

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

What is Database Version Control?

Compare MySQL 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?

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

Bytebase 1.12.1 Deep Dive

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

Bytebase 1.12.0 Deep Dive

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

Bytebase 1.11.0 Deep Dive

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

Bytebase 1.10.0 Deep Dive

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

VCS Integrated Database CI/CD Workflow Best Practice

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

Bytebase 1.9.1 Deep Dive

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

What is Database Change Management (DCM)?

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

Three Ways to Integrate Automatic SQL Review into GitHub

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

Bytebase 1.9.0 Deep Dive

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

Bytebase 1.8.0 New Features Dive

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

What is a Database Schema?


If you already do HIPAA, [HITRUST CSF](https://hitrustalliance.net/) (Common Security Framework) is the part that finally tells you what to actually build. The two map cleanly:

- HIPAA defines **what** must be protected, electronic Protected Health Information (ePHI), and then stays deliberately vague on how.
- HITRUST fills in the **how** with prescriptive, certifiable controls that satisfy HIPAA and leave you with far less room to interpret.

Databases are where the ePHI lives, so they are where most of the HITRUST controls land. The rest of this post walks the four database control domains that auditors actually look at, what goes wrong when you skip each one, and how to implement them.

## Access Control

The bar is simple to state and hard to enforce: only authorized users touch sensitive data, with an identity you can verify and privileges scoped to their job. Everything else in HITRUST assumes you got this right first.

- **Role-Based Access Control (RBAC).** Grant by job function, not by person. Clinical staff read patient records, billing reads financials, DBAs run the infrastructure. Per-user grants do not scale and they rot.
- **Least Privilege.** Hand out the minimum needed for the task at hand. A developer chasing a bug does not need write access to production, and giving it to them is how you end up dropping prod by accident.
- **Just-in-Time Access.** Elevated access that expires on its own. Standing privileges are a window that is always open, JIT shrinks it to the minutes you actually need.
- **Authentication.** Verify identity through MFA, SSO, and LDAP/AD. The moment you share a credential, accountability is gone, and accountability is the whole point of an audit.

Skip these and the failure modes write themselves: ePHI reachable by people who should never see it, over-privileged accounts sitting around as standing exposure, and no way to say who did what when something goes wrong.

## Audit Logging

Every access and every change to sensitive data has to be recorded, centralized, and queryable. This is what an assessor asks for first, and it is also what saves you at 2am when you are trying to figure out what just happened.

- **Query logging.** Who read what data, and when. This is the evidence trail, and without it you are taking the breach investigation on faith.
- **Change tracking.** Record DDL and DML with before/after state. Knowing a table changed is not enough, you need to know what it changed from and who changed it.
- **Log retention.** Keep the trails for the required window. For HIPAA-related compliance that is 6+ years, so plan storage accordingly.
- **SIEM integration.** Stream database audit logs into your SIEM (Security Information and Event Management). A breach rarely lives only in the database, and correlating database activity with the rest of your security events is how you catch it.

Without this you cannot investigate a breach or prove compliance, you have no accountability for reads or writes, your database activity is siloed off from the rest of your monitoring, and the gaps in the trail are exactly what fails a certification.

## Change Management

Database changes have to be controlled, reviewed, and traceable. Uncontrolled change is the common root cause behind both the security incident and the compliance miss, usually the same one.

- **Approval workflows.** Review and sign-off before anything reaches production. No direct production access without a human in the loop.
- **Risk-based classification.** Match the approval path to the blast radius. A column rename is not a `DROP TABLE`, and treating them the same just trains people to rubber-stamp.
- **Rollback capability.** Keep the ability to revert. When a change goes wrong, the question is how fast you can get back, not whether you can.
- **Change history.** A complete record of what changed, who approved it, and why. This is the audit evidence, and "we think Bob did it" is not evidence.

Skip it and unreviewed changes quietly introduce vulnerabilities or break a control, you lose the ability to trace when and how data was altered, and emergency changes route around every safeguard you built with nobody watching.

## Data Protection

Sensitive data has to be protected from disclosure both at rest and at the moment someone reads it.

- **Data masking.** Hide SSNs, diagnosis codes, payment fields from people who do not need the raw value. A support engineer can troubleshoot an order from its status without ever seeing the card number.
- **Data classification.** Label the sensitive data so you can apply the right controls to it. You cannot protect what you have not found.
- **Encryption.** At rest and in transit. This is table stakes for any framework, not a differentiator.
- **Secret management.** Handle credentials and connection strings properly. Credentials sitting in a repo or a log line are one of the most boring and most common ways data walks out the door.

Without it, sensitive data leaks to users who only needed a partial view, you have no map of where the sensitive data even sits across your fleet, and your credentials end up in a code repository or an application log waiting to be found.

## How Bytebase Can Help

[Bytebase](/) is a database DevSecOps platform built for exactly this kind of heterogeneous, cross-database control surface that HITRUST demands:

| Control Domain    | Bytebase Features                                                                                                                                                                                                                                                                                                                  |
| ----------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Access Control    | [RBAC](https://docs.bytebase.com/administration/roles), [Just-in-Time access](https://docs.bytebase.com/security/database-permission/overview), [MFA](https://docs.bytebase.com/administration/2fa), [SSO](https://docs.bytebase.com/administration/sso/overview), [SCIM](https://docs.bytebase.com/administration/scim/overview/) |
| Audit Logging     | [Audit logging](https://docs.bytebase.com/security/audit-log)                                                                                                                                                                                                                                                                      |
| Change Management | [Change workflows](https://docs.bytebase.com/change-database/plan), [risk-based approval](https://docs.bytebase.com/change-database/approval), [rollback](https://docs.bytebase.com/change-database/rollback-data-changes), [change history](https://docs.bytebase.com/change-database/change-history)                             |
| Data Protection   | [Dynamic Data Masking](https://docs.bytebase.com/security/data-masking/overview), [data classification](https://docs.bytebase.com/security/data-masking/data-classification), [secret manager](https://docs.bytebase.com/get-started/connect/overview#secret-manager-integration)                                                  |

HITRUST is the layer that turns HIPAA's "protect ePHI" into a checklist you can actually implement, and the database is where most of that checklist gets enforced because that is where the data lives. Get the tooling right and you can pass the assessment without making every schema change feel like filing paperwork.



A table without a primary key looks harmless when you create it. The trouble shows up later, once the table has grown, replication is running, and something downstream depends on each row being uniquely identifiable. By then the missing primary key is no longer a cosmetic detail. It's the reason your duplicates are creeping in, your CDC pipeline is dropping events, or your replica has quietly stopped catching up.

Bytebase [SQL Review includes the rule](https://docs.bytebase.com/sql-review/review-rules#require-primary-key):

> Bytebase considers this rule to be violated if the SQL tries to create a no primary key table or drop the primary key. If the SQL drops all columns in the primary key, Bytebase also considers that this SQL drops the primary key.

In other words, it catches both the `CREATE TABLE` that forgets a primary key and the `ALTER TABLE` that removes one, before either reaches production.

## Real Incidents Caused by Missing Primary Keys

This is not a theoretical concern. The same failure has been reported across different databases and tools.

**PostgreSQL logical replication breaks.** Without a primary key, Postgres has no way to identify which row an update or delete applies to, so it can't replay those changes during logical replication (or through Debezium). See [TIL: Creating tables without primary keys CAN cause updates and deletes to fail in Postgres](https://abhinavomprakash.com/posts/replica-identities/) for a walkthrough.

**Matomo production replication stalled.** A single table with no primary key was enough to stop MySQL master-slave replication. The thread is here: [Master-Slave Replication Stalls Because of Missing Primary Key](https://forum.matomo.org/t/master-slave-replication-stalls-because-of-missing-primary-key/36251).

**GitLab reported schema inconsistencies.** GitLab engineers traced environment drift and ongoing maintenance pain back to tables that lacked primary keys: [Database schema missing many primary keys - breaks replication](https://gitlab.com/gitlab-org/gitlab-ce/-/issues/51964).

## Why Missing Primary Keys Are Dangerous

### **1. Duplicate rows slip in**

A primary key is what lets the database enforce uniqueness. Without one, nothing stops the same logical record from being inserted twice, and accidental duplicates quietly corrupt your analytics and reports.

### **2. CDC systems can't track row changes**

Change-data-capture tools like Debezium and Kafka Connect need a stable identity for each row. If there's no primary key, they have no reliable way to emit the correct update and delete events.

### **3. Replication may stop or diverge**

Both MySQL and PostgreSQL lean on the primary key during replication. A missing one can stall the replica or let it drift out of sync with the source, which is exactly what happened in the Matomo and GitLab cases above.

### **4. Upserts don't work correctly**

`INSERT … ON CONFLICT`, `MERGE`, and the various UPSERT patterns all depend on a primary key to decide what counts as a conflict. Without one, the database can't resolve those conflicts reliably.

### **5. Debugging becomes guesswork**

When you need to delete, fix, or investigate a single record, you need a way to point at exactly that row. With no unique identifier, every targeted operation becomes a gamble.

## How to Fix Tables Without Primary Keys?

There's no single right answer here. The best key depends on whether your table already has a natural unique value to lean on.

### **1. Add a surrogate primary key**

When there's no obvious natural key, a generated identifier is the simplest option:

```sql
ALTER TABLE events
ADD COLUMN id BIGSERIAL PRIMARY KEY;
```

### **2. Use a natural composite key if appropriate**

If a combination of existing columns is already guaranteed unique, you can promote it directly:

```sql
ALTER TABLE order_items
ADD PRIMARY KEY (order_id, line_number);
```

### **3. Combine surrogate PK with a unique business key**

Often you want both: a stable surrogate key for joins and replication, plus a unique constraint to protect the business value:

```sql
ALTER TABLE shipments
ADD COLUMN id BIGSERIAL PRIMARY KEY,
ADD CONSTRAINT shipments_unique UNIQUE (tracking_number);
```



Bytebase [SQL Review](https://docs.bytebase.com/change-database/review) contains a rule to enforce including "NOT VALID" option when adding "CHECK" constraints:

> Adding a CHECK constraint needs to verify the existing data and requires ACCESS EXCLUSIVE table lock. This blocks read and write, which may cause business interruption. It is recommended to add the "NOT VALID" option to validate new data and manually validate existing data after the change is completed.

PostgreSQL lets you add `CHECK` constraints to enforce data quality rules on a table. That part is useful. The problem is that adding one the obvious way can block reads and writes and cause downtime you didn't plan for. This rule makes sure every new CHECK constraint goes in using the safe, non-blocking approach.

## How CHECK Constraints Work

A CHECK constraint makes sure a column's value satisfies a given condition, for example:

```sql
CHECK (amount > 0)
```

PostgreSQL will reject any insert or update that violates this rule.

To add a CHECK constraint, you typically run:

```sql
ALTER TABLE orders
ADD CONSTRAINT orders_positive CHECK (amount > 0);
```

This enforces the rule on future writes. But it also immediately validates all existing rows, and that is where the risk comes from.

## Why Adding CHECK Constraints Can Cause Downtime

To validate an existing table, PostgreSQL has to scan every row to confirm none of them violate the constraint. While it does that, it holds an `ACCESS EXCLUSIVE` lock. This is the strongest lock in PostgreSQL, and it blocks:

- Reads
- Writes
- Other schema changes

On a small table you'll never notice. On a large table or a busy production database, that same lock can turn into:

- Query timeouts
- Application errors
- Service degradation
- A full outage

This is exactly the kind of blocking change that slips into a deployment unnoticed, which is what the rule is there to catch.

## The Safe Approach: Use `NOT VALID`

PostgreSQL gives you a safer way to add a CHECK constraint to an existing table: separate creating the constraint from validating it.

**Step 1: Create the constraint without validating existing rows**

```sql
ALTER TABLE orders
ADD CONSTRAINT orders_positive CHECK (amount > 0) NOT VALID;
```

- Only a brief catalog lock is required
- The constraint is enforced for all new inserts and updates
- Existing rows are not scanned yet

**Step 2: Validate at a convenient time**

```sql
ALTER TABLE orders
VALIDATE CONSTRAINT orders_positive;
```

Validation here uses a lighter lock that does not block reads and writes. You can run it during a low-traffic window, or fold it into a controlled rollout.

## Example

Unsafe pattern (not allowed):

```sql
ALTER TABLE accounts
ADD CONSTRAINT check_balance CHECK (balance >= 0);
```

Safe pattern (recommended):

```sql
ALTER TABLE accounts
ADD CONSTRAINT check_balance CHECK (balance >= 0) NOT VALID;
```

Validate separately:

```sql
ALTER TABLE accounts
VALIDATE CONSTRAINT check_balance;
```

Same end result, the constraint is applied without disrupting user traffic.

## Summary

Add a CHECK constraint without `NOT VALID` and you risk locking the table long enough to block reads and writes, which on a busy database means downtime. The rule enforces the two-step PostgreSQL practice instead:

- Add the constraint using `NOT VALID`
- Validate it later using a non-blocking operation

You get the same constraint correctness, without betting production stability on how big the table happens to be.


The
Bytebase Blog

Explore the standard for database development

Solutions & Platform

Comparisons

Legal

Resources

Company