Best practices for database access control covering least privilege, RBAC, just-in-time access, and compliance with SOC 2, HIPAA, and GDPR.

Database Access Control Best Practices

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

How codifying the full database development lifecycle — not just DDL — empowers LLMs and AI Agents.

From Schema as Code to Schema as Context

A practical guide to governing AI agent access to enterprise databases — covering authentication, authorization, query control, audit logging, and data masking.

How to Govern AI Agent Access to Enterprise Data

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

A guide to multi-environment deployments in databases.

Database Multi-Environment Deployments - The Challenges and Patterns

How to set up database audit logging for SOC 2, HIPAA, and ISO 27001 compliance across PostgreSQL, MySQL, SQL Server, and Oracle.

Database Audit Logging Best Practices for Compliance

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.

SQL Review Rule Explained - Require Primary Key

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

SQL Review Rule Explained - Enforce NOT VALID in CHECK

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.

SQL Review Rule Explained - Prohibit CASCADE

A comprehensive guide on migrating from Supabase to AWS, covering database, auth, storage, functions, realtime, and networking.

How to Migrate from Supabase to AWS

Enterprise database management is a comprehensive approach to organizing, securing, and optimizing database systems and workflows at scale, ensuring data integrity, availability, and compliance across large organizations.

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

A comprehensive guide on building a CI/CD pipeline for automated database schema migration, covering best practices, tools, and implementation strategies.

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

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

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

SQL Table Naming Dilemma: Singular vs. Plural

An engineering perspective to evaluate Postgres case sensitivity

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

Common 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 Best Practice

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

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.

PostgreSQL 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

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

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

The Just-in-Time Database Access workflow

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?

Comparing PostgreSQL data masking solutions - PostgreSQL Anonymizer and Bytebase Dynamic Data Masking.

Data Masking for PostgreSQL Databases

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 data masking solutions. MySQL Enterprise Data Masking, Percona Data Masking Plugin and Bytebase Dynamic Data Masking.

Data Masking for MySQL Databases

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?

The common mistakes when making schema migrations and the best practice to avoid those pitfalls.

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

Database access control can be a headache for DBAs and engineering teams. In this post, we explore database access control scenarios, and how Bytebase helps you to centralize database access control.

How to Manage Database Access Control

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?

SQL review catches bad SQL before it hits the database. This guide covers the three layers (linting, semantic rules, and policy with approval) and compares the main SQL review tools.

SQL Review: From Linting to Governance

Explain and address the confusion between Developer (Dev), Site Reliability Engineer (SRE), (Operations) Ops and DevOps.

Dev, SRE, Operations, DevOps - What’s the Difference?

We dive the rationale behind the industry divergence between state-based and migration-based approach. In the end, we also show how Bytebase can help to get the best of both worlds.

Database Version Control, State-based or Migration-based?

Database-as-Code has many benefits in today's DevOps world. However, there exists several missing bricks preventing its wider adoption. We touch many points around Database-as-Code and also explain why Bytebase could help the transition.

Database as Code - the Good, the Bad and the Ugly


Last time we addressed [What is Database DevOps](/blog/what-is-database-devops/), today we are extending
the topic to Database Dev**Sec**Ops.

**Database DevSecOps** is the practice of integrating security throughout the entire database development
lifecycle, much like how DevSecOps evolved from DevOps. Given that data security is critical,
especially in preventing breaches, it makes sense for security to be a central focus in database operations.
By embedding security measures from the start, organizations can better protect their most valuable
asset—data—while minimizing risks and ensuring compliance.

## The Security Role in Database DevSecOps

In database DevSecOps, besides the typical Developer and DBA roles, security engineers join the party:

- **Developer** - Build and deliver ASAP.
- **DBA** - Prevent database outage.
- **Security engineer** - Prevent data breach.

By looping in the security engineers, we are also facing the classic trolley problem in the database domain.

![trolley-problem](/content/blog/what-is-database-devsecops/trolley-problem.webp)

If DBAs give too much freedom to the developers, the security engineers will get upset as the developers
are more likely to mess up or leak the production data. However, if DBA impose overly restrictive access controls,
developers will become frustrated due to slower workflows, which hinders their development velocity.

Let's look at the top 3 developer-to-db access paths:

1. **Schema migration**. This usually happens when evolving the application as part of the overall CI/CD pipeline.
1. **One-off, adhoc change**. This usually happens to fix some production data.
1. **Query data**. Developers sometimes need to query the production database for troubleshooting and analysis.

## Schema migration

Database schema migration should be automated just like code in a CI/CD pipeline . By treating schema changes
as part of the CI/CD process, teams can automate testing, validation, and deployment, ensuring that database
modifications are applied uniformly across environments.

<HintBlock type="info">

Bytebase provides [built-in GitOps integration](https://docs.bytebase.com/gitops/overview/) and [SQL Review](https://docs.bytebase.com/sql-review/overview/) to detect erroneous SQLs automatically.

</HintBlock>

## One-off, adhoc change

![roaring-dba](/content/blog/what-is-database-devsecops/roaring-dba.webp)

One-off DML changes are risky, often bypassing standard review and testing processes, leading to potential errors.
While teams may use ticketing systems like Jira to manage these requests, the manual copy-pasting between the ticket system and database client introduces inefficiencies and risks. Additionally, there's no simple rollback mechanism.

<HintBlock type="info">

Bytebase streamlines request, review, deploy in a single place and offers [1-click automatic rollback](https://docs.bytebase.com/change-database/rollback-data-changes/).

</HintBlock>

## Query data

Traditional SQL clients often lack robust access control capabilities, posing challenges for
maintaining data security without hindering query productivity. To address this, a solution is needed
that centrally manages data access, ensuring consistent and secure control over who can query what data.
Additionally, it should incorporate data masking features, allowing different levels of data visibility
based on the user's permissions, ensuring sensitive information is protected while still enabling
efficient querying for authorized users.

<HintBlock type="info">

Unlike client-side solutions, Bytebase is a server-side solution providing [access control](https://docs.bytebase.com/security/database-permission/overview/), [dynamic data masking](https://docs.bytebase.com/security/data-masking/overview/), [audit logging](https://docs.bytebase.com/security/audit-log/). You can also [codify these data security policies (Policy-as-Code)](https://github.com/bytebase/example-database-security).

</HintBlock>

## Summary

Security is intrinsic to database operations, and as teams embrace DevOps methodologies, it is crucial not to sacrifice security for the sake of speed or efficiency.
To embed security within the DevOps framework, a centralized solution is required that governs all human-to-database interactions and automates security enforcement as much as possible. This approach ensures that security policies are consistently applied without hindering the pace of development and operations.

## Further Readings

- [What is Database DevOps](/blog/what-is-database-devops/)



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

![ddm](/content/blog/what-is-dynamic-data-masking/dynamic-data-masking.webp)

DDM contrasts with Static Data Masking (SDM). While SDM involves creating a permanently altered, non-reversible copy of the original data, DDM modifies the data on-the-fly as it is accessed in real-time. This dynamic approach ensures that sensitive data remains protected during query execution without changing the underlying data at rest.

## When to Use Dynamic Data Masking vs Static Data Masking

Static Data Masking (SDM) creates sanitized copies of production data for dev/test environments. DDM is different — it masks data in real-time in production, controlling what each user sees based on their role and permissions. The underlying data stays untouched.

| | Static Data Masking | Dynamic Data Masking |
|---|---|---|
| **Environment** | Non-production (dev, test, staging) | Production |
| **Data altered?** | Yes — permanent copy | No — masked on-the-fly |
| **Use case** | Safe test data | Role-based access control |

## What Makes Dynamic Data Masking Hard

DDM has to make real-time decisions about what each user sees. The complexity comes from the number of variables involved:

- **User role and identity** — a DBA sees unmasked data, an analyst sees partial masks, a contractor sees full masks. The same query returns different results depending on who runs it.
- **Temporary access** — an on-call engineer needs unmasked access to debug a production incident, then the access should expire.
- **Column-level granularity** — an `email` column might need partial masking while a `phone` column needs full masking, even in the same table.
- **Multiple databases and environments** — masking rules in production differ from staging. If you run MySQL, PostgreSQL, and Oracle, each has different (or no) native DDM support.
- **Masking algorithm choice** — partial masking keeps data useful for debugging (`john@****`), but full masking or hashing is needed for compliance. Picking the wrong algorithm makes the data either too exposed or too useless.
- **Performance** — masking happens on every query at runtime. A poorly implemented DDM layer adds latency to every SELECT.

## Which Databases Support Dynamic Data Masking

| Databases  | Supported                                                                                           |
| ---------- | --------------------------------------------------------------------------------------------------- |
| Oracle     | ✅ [Link](https://www.oracle.com/security/database-security/data-masking/)                          |
| SQL Server | ✅ [Link](https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking) |
| BigQuery   | ✅ [Link](https://cloud.google.com/bigquery/docs/column-data-masking-intro)                         |
| Snowflake  | ✅ [Link](https://docs.snowflake.com/en/user-guide/security-column-ddm-intro)                       |
| MySQL      | ❌                                                                                                  |
| PostgreSQL | ❌                                                                                                  |

Mainstream commercial databases all support DDM. On the other hand, neither MySQL nor PostgreSQL, the 2 most popular open-source databases support DDM out-of-the-box. For those supported databases, DDM is exposed via the extended SQL syntax. Taking Snowflake as
an example:

```sql
CREATE OR REPLACE MASKING POLICY email_mask AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('ANALYST') THEN val
    ELSE '*********'
  END;

-- apply masking policy to a table column
ALTER TABLE IF EXISTS user_info MODIFY COLUMN email SET MASKING POLICY email_mask;
```

Database engines only provide masking primitives. Holistically configuring masking policies for an entire organization — across multiple databases, environments, and user roles — is still a big challenge. For database-specific guides, see [Data Masking for MySQL](/blog/mysql-data-masking/) and [Data Masking for PostgreSQL](/blog/postgres-data-masking/). For Snowflake specifically, see [Snowflake Dynamic Data Masking and Alternatives](/blog/snowflake-dynamic-data-masking-and-alternatives/).

## How Bytebase Handles Dynamic Data Masking

[Bytebase](https://docs.bytebase.com/security/data-masking/overview/) implements DDM at the application layer rather than relying on database-native features. All queries through Bytebase's SQL Editor are masked in real-time based on policies you define. This is particularly valuable for MySQL and PostgreSQL, which have no native DDM support.

### Supported databases

Bytebase DDM works with MySQL, PostgreSQL, Oracle, TiDB, and others — the same masking policies apply across all of them, regardless of whether the engine has native DDM.

### How masking is configured

Bytebase uses a three-level policy system:

1. **Global masking rules** — workspace admins apply batch masking to columns matching a name pattern (e.g., all columns named `ssn` or `email` across every database)
2. **Column-level masking** — project owners set masking on specific table columns
3. **Masking exemptions** — grant specific users access to unmasked data when needed

Precedence: exemptions > global rules > column masking.

Policies are organized around **semantic types** — you classify columns (e.g., "PII-email", "PII-phone") and attach a masking algorithm to the type. Changing one semantic type updates masking for all columns tagged with it.

### Masking algorithms

Five built-in algorithms:

| Algorithm | Example | Use case |
|-----------|---------|----------|
| Full mask | `123456789` → `*` | Completely hide the value |
| Range mask | `john@example.com` → `john@****` | Preserve prefix for usability |
| Inner mask | `123456` → `12**56` | Show edges, hide middle |
| Outer mask | `123456` → `**34**` | Show middle, hide edges |
| MD5 mask | `value` → `2063c1608d6e0baf80249c42e2be5804` | Irreversible hash for analytics |

### Infrastructure as code

Masking policies can be managed via [Bytebase's Terraform provider](https://docs.bytebase.com/tutorials/manage-data-masking-with-terraform/) — define semantic types, global rules, and column masking in HCL and apply across environments.

### Availability

Dynamic Data Masking is available on the [Enterprise plan](https://www.bytebase.com/pricing/). DDM is one part of Bytebase's broader [database access control](/blog/database-access-control-best-practices/) capabilities, which also include role-based access, [just-in-time access](/blog/just-in-time-database-access/), and [audit logging](/blog/database-audit-logging/).

## FAQ

**What is Dynamic Data Masking?**

Dynamic Data Masking (DDM) protects sensitive data by altering query results in real-time based on user roles and policies, without changing the data at rest. Unlike static data masking, which creates a permanent sanitized copy, DDM applies masking on-the-fly during query execution.

**Which databases support Dynamic Data Masking natively?**

Oracle, SQL Server, BigQuery, and Snowflake have built-in DDM features. MySQL and PostgreSQL do not support DDM natively. Bytebase provides application-layer DDM for MySQL, PostgreSQL, Oracle, TiDB, and others, using the same policies across all engines.

**How does Bytebase implement DDM for MySQL and PostgreSQL?**

Bytebase applies masking at the application layer when queries run through its SQL Editor. No database extensions, views, or plugins are required. You define masking policies centrally in Bytebase, and they apply consistently across all connected databases.

**What is the difference between Dynamic Data Masking and Static Data Masking?**

Static Data Masking (SDM) creates a permanent, altered copy of production data for use in non-production environments. Dynamic Data Masking (DDM) modifies data on-the-fly as it is queried, without changing the underlying data. SDM is for dev/test environments; DDM is for production access control.



<HintBlock type="info">

Beyond Database DevOps, you can also check out [Database DevSecOps](/blog/what-is-database-devsecops/) that integrates security into the database DevOps framework.

</HintBlock>

Before delving into Database DevOps, let's first review what is DevOps. While there is no uniform definition for
DevOps, we know DevOps originates from combining software development methodologies with deployment and operations.

> Around 2007 and 2008, concerns were raised by those within the software development and IT communities that the separation between the two industries, where one wrote and created software entirely separate from those that deploy and support the software was creating a fatal level of dysfunction within the industry.

DevOps is to break silos between developers and operations with shared ownership, workflow automation, and continuous feedback loop across the software development lifecycle (SDLC). Database DevOps is a specialized subset within the broader DevOps framework, focusing on the database-related activities.

## Roles in Database DevOps

Developer and DBA are the 2 primary roles in database DevOps. They have different and somewhat conflicting priorities:

- **Developer** - Build and deliver ASAP.
- **DBA** - Prevent database outage.

![dba-vs-developer](/content/blog/how-many-dbas-should-a-company-hire/freedom-control.webp)

## A Typical Database Schema Migration Workflow

1. Developer works on the feature on the local IDE, applies the schema change to the local DB, and test locally.
1. Developer submits a SQL change request from a ticketing system such as [Jira](/blog/use-jira-for-database-change/).
1. DBA reviews the ticket and exchange several round with the developer.
1. DBA applies the change offline using a SQL client.
1. Repeat steps 2 ~ 5 multiple times to propagate the change across UAT, Staging, Prod environments.

### Lack of Consolidated Workspace

Developers and DBAs jump between different tools as they progress with a database change. A developer copies the SQL statement from local IDE and pastes into Jira for review. Once review approved, a DBA then copies the SQL statement from Jira to the local SQL client to applies the change. Then the DBA updates the Jira ticket and notify the developer.

### Lack of Automation

- SQL statements are manually copied over -> Paste wrong SQL statements.
- SQL statements are reviewed manually -> Review delay and overlooked defects.
- SQL statements are deployed manually -> Apply to the wrong database (the infamous DROP PROD by accident).

These are not atypical. DBAs are always out-numbered by developers and many engineering organizations even don't have a dedicated DBA role, instead they only assign someone as a DB person.

## The Road to Database DevOps

### A Consolidated Workspace

DevOps has become a reality thanks to the significant advancements in tooling, including platforms like GitLab, GitHub, PagerDuty, and Datadog. These platforms allow developers, release engineers, operation engineers, and SREs collaborate within a single place. Database DevOps also calls for a consolidated platform.

The bare minimum is to cover the change process. This can be achieved by adopting [GitOps](/blog/database-as-code/), a typical workflow:

1. Developer submits a SQL migration script for review in VCS such as GitHub.
1. Review is approved and the script is merged.
1. After the script is merged, a CI pipeline is triggered to schedule a deployment.

This leverages VCS to request, review, and version the SQL statements. However, VCS even with the built-in CI like GitHub Actions, GitLab CI still rely on an external CD system to deploy the database change. Besides, apart from the change task, another common database task is to query the database. VCS does not have the interactive interface to allow users to issue a SQL statement and get the result back.

A real consolidated database workspace like [Bytebase](https://docs.bytebase.com/introduction/what-is-bytebase/) should cover both write (change database) and read (query database) paths. It must provide a workflow to request, review, and deploy the database changes as well as an interactive SQL interface to query the database.

### Automation Points

Automation can be applied in several places:

- Adopt GitOps to streamline database change from merge to deployment.
- Lint SQL to detect anti-SQL patterns automatically.
- Record change history and prepare rollback automatically.
- Send notification upon operational events.
- Custom automation via API.

<HintBlock type="info">

Bytebase automates database tasks via [GitOps](https://docs.bytebase.com/gitops/overview/), [SQL Review](https://docs.bytebase.com/sql-review/overview/), [built-in rollback](https://docs.bytebase.com/change-database/rollback-data-changes/), [Webhook Integration](https://docs.bytebase.com/change-database/webhook/), and [API](https://docs.bytebase.com/api/overview/).

</HintBlock>

## Summary

Database DevOps emphasizes the integration of databases into the DevOps lifecycle, fostering collaboration between development, operations, and database teams. By leveraging automation tools and practices, teams can streamline database deployment and query process, reducing manual errors and improving consistency. This approach not only accelerates delivery cycles but also ensures that database development lifecycle is seamlessly integrated into the application development lifecycle, promoting a culture of shared responsibility and continuous improvement.

## Further Readings

- [What is Database DevSecOps](/blog/what-is-database-devsecops)
- [Database-as-Code](/blog/database-as-code)
- [How many DBAs should a company hire](/blog/how-many-dbas-should-a-company-hire)
- [Using Jira for Database Changes Won't Cut It](/blog/use-jira-for-database-change)



PostgreSQL offers various timeout settings to help manage and optimize database operations by controlling the duration of certain processes.
These timeouts are crucial for ensuring the stability and performance of your system, particularly in environments with high traffic or complex queries. Let's review each of them.

## lock_timeout

[lock_timeout](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT) controls how long a transaction will wait to acquire a lock on a database object before giving up with an error:

```plain
ERROR:  canceling statement due to lock timeout
```

**The Lock Queue Problem with DDL Operations**

DDL operations require `ACCESS EXCLUSIVE` locks that conflict with all other lock types, creating a blocking chain:

1. **Lock Acquisition Order**: First transaction holds a basic lock → DDL waits for exclusive access → All subsequent transactions wait behind the DDL
1. **Cascading Effect**: Without timeout limits, a single blocked DDL can halt all database activity

**Visual Example of a Lock Queue**

```bash
Transaction 1: Running SELECT (has ROW SHARE lock)
Transaction 2: Waiting for ALTER TABLE (needs ACCESS EXCLUSIVE lock)
Transaction 3: Waiting for SELECT (needs ROW SHARE lock)
Transaction 4: Waiting for INSERT (needs ROW EXCLUSIVE lock)
```

To mitigate the issue, create a dedicated DDL user with `lock_timeout`:

```sql
-- Create a dedicated user with appropriate timeout
CREATE ROLE ddl_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE ddl_user SET lock_timeout = 10000; -- 10 seconds
```

## statement_timeout

[statement_timeout](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT) sets a limit on the maximum amount of time that a single query is allowed to execute.
If the query exceeds this time limit, PostgreSQL will automatically terminate the query and return an error.

```plain
ERROR:  canceling statement due to statement timeout
```

If multiple SQL statements appear in a single simple-Query message, the timeout is applied to each statement separately. `statement_timeout` effectively preventing long-running queries from consuming too many resources or causing performance issues in your database.

## idle_in_transaction_session_timeout

[idle_in_transaction_session_timeout](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT) controls the maximum amount of time that a session can remain idle while inside a transaction. If a session stays idle within a transaction for longer than the specified timeout period, PostgreSQL will automatically terminate the session and roll back the ongoing transaction.

```plain
ERROR:  terminating connection due to idle-in-transaction timeout
```

Imagine you have an application that occasionally leaves transactions open while waiting for user input or performing some non-database-related processing. If a transaction is left open and idle for too long, it might hold locks on tables or rows, preventing other transactions from accessing those resources. By setting `idle_in_transaction_session_timeout`, you can automatically terminate these idle sessions, ensuring that resources are not held up unnecessarily. Even when no significant locks are held, an open transaction prevents vacuuming away recently-dead tuples that may be visible only to this transaction; so remaining idle for a long time can contribute to table bloat.

## idle_session_timeout

[idle_session_timeout](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT) controls the maximum amount of time a session can remain idle before it is automatically terminated. Unlike `idle_in_transaction_session_timeout`, which applies only to sessions that are idle while inside a transaction, `idle_session_timeout` applies to any session that is idle, whether or not it is within a transaction.

```plain
ERROR:  terminating connection due to idle session timeout
```

Be careful when you are using connection pooler or other middleware, as such a layer may not react well to unexpected connection closure. `idle_session_timeout` is usually set for the interactive sessions. It's a good practice
to create a separate Postgres user for interactive processing and set `idle_session_timeout` accordingly.

```sql
ALTER ROLE interactive_user SET idle_session_timeout = 600000; -- 10 minutes
```

## transaction_timeout

![transaction-timeout-commit](/content/blog/postgres-timeout/transaction-timeout-commit.webp)

Postgres 17 introduces a new [transaction_timeout](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TRANSACTION-TIMEOUT). From the doc:

> Terminate any session that spans longer than the specified amount of time in a transaction. The limit applies both to explicit transactions (started with BEGIN) and to an implicitly started transaction corresponding to a single statement.

A typical web service consists of 3 main components:

- Web server
- Application server
- Database server

To prevent long-running connections, you usually set a connection timeout on both the web server and the application server. It's a waste to process the transaction when web server/application server already terminate the connection. Before the introduction of `transaction_timeout`, there is no reliable way to prevent long-transactions. Even you set both `statement_timeout` and `idle_in_transaction_session_timeout`, the transaction will still
be open if it consists of short statements and short pauses in between.

You might be wondering why it has taken PostgreSQL so long to introduce a straightforward `transaction_timeout` feature. Well, better late than never! And by the way, [MySQL](/blog/mysql-timeout/#transactiontimeout) doesn’t have this feature either.

## References

- [Official doc](https://www.postgresql.org/docs/current/runtime-config-client.html)
- [pgsql-hackers discussion about introducing `transaction_timeout`](https://www.postgresql.org/message-id/flat/f508267d1ba8f0bfd7b93181d10511dc%40oss.nttdata.com#2506da45ff92aaea65c30996fbf19c85)



This is a series of articles about database keys:

1. [How to Choose Database UUID?](/blog/how-to-choose-database-uuid)
1. How to Choose between UUID and Auto Increment Integer / Serial as the Primary Key? (this one)

---

One of the first things when designing a new SQL database schema is to decide which type of **primary key** to use. And 99% of the time, developers need to choose between either UUID or Auto Increment Integer/Serial.

Developers may not realize initially, but choosing the primary key type can have consequential impact down the road and it's almost impossible to switch afterwards.

**Choosing a proper primary key format requires a good understanding of both the business requirements as well as the underlying database system, so that the schema designer can make the educated tradeoff.**

## **Auto Increment Integer/Serial**

Using auto increment integer/serial as the primary key in your SQL database is also quite common and every major database engine provides the native support. e.g.

- MySQL - **AUTO_INCREMENT**
- PostgreSQL - **SERIAL**
- SQL Server - **IDENTITY**
- SQLite - **AUTOINCREMENT**

### Pros

- Readable. This is especially valuable if we would expose it externally. Thinking of issue id, obviously, `issue-123` is much more readable than `issue-b1e92c3b-a44a-4856-9fe3-925444ac4c23`.
- Less space. UUID always occupies 16 bytes. For Auto Increment Integer, when stored as Long format, it occupies 8 bytes. If the table itself has only a few columns, the extra primary key space overhead will become more significant.

### Cons

- It can't be used in the distributed system since it's quite likely that different hosts could produce exactly the same number.
- It can't be generated on the fly. Instead, we must consult the database to figure out the next available primary key. In a distributed system, this often means to introduce a separate service to produce this sequential number. And that service becomes a single-point-of-failure (SPOF).
- Some business data can be exposed, since the latest ID could represent the total number of inventory. Attackers can also scan the integer range to explore leakage (though it shouldn't happen if ACL is implemented correctly).

## **UUID**

The original UUID standard includes 5 UUID formats. Most of the time, people either choose UUIDv1 (timestamp) or UUIDv4 (random).

### Pros

- Globally unique. e.g. No false positive for finding items using log. Easy for migrating data between systems since collision is only theoretically possible.
- Stateless, it can be generated on the fly.
- A sense of secure since malicious user can't guess the ID. However, your security team would always insist that a public accessible UUID path does not meet the security standard.
- Version 1 UUID stores timestamp info, could be useful sometimes.

### Cons

- Not readable.
- Not naturally sortable according to creation time. Though v1 UUID format contains timestamp, it encodes the timestamp using little-endian in that the least significant time appears first, which renders the UUID hard to sort according to creation time.
- For database like MySQL, Oracle, which uses clustered primary key, both UUIDv1 and UUIDv4 will hurt insertion performance if used as the primary key. This is because it requires reordering the rows to place the newly inserted row at the right position inside the clustered index. On the other hand, PostgreSQL uses heap instead of clustered primary key, thus using UUID as the primary key won't impact PostgreSQL's insertion performance.

## UUIDv7 - Best of Both Worlds (Almost)

Bespoke solutions are invented to address the limitation of UUID including [Snowflake ID](https://en.wikipedia.org/wiki/Snowflake_ID), [ulid](https://github.com/ulid/spec), [cuid](https://github.com/paralleldrive/cuid2). IETF also published
a draft in April 2021 to propose a new UUID format. And in May 2024, IETF finally approved the draft. The approved UUIDv7
format:

```bash
 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           unix_ts_ms                          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms           |  ver  |       rand_a          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                        rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                            rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
```

The most significant difference from UUIDv1 is the first 48 bits (unix_ts_ms) store big-endian unsigned number of the Unix Epoch timestamp in milliseconds. This means that UUIDs generated at a later time will have higher values, making it easier to sort and query by creation time. Since UUIDv7 is time-ordered, it reduces the need for random I/O operations when inserting new records in databases. This can lead to better performance and more efficient indexing.

|                | Auto Increment | UUIDv1          | UUIDv7           |
| -------------- | -------------- | --------------- | ---------------- |
| Sortable       | ✅             | ❌              | ✅               |
| Time precision | ❌             | ✅ (nanosecond) | ✅ (millisecond) |
| Global Unique  | ❌             | ✅              | ✅               |
| Stateless      | ❌             | ✅              | ✅               |
| Readable       | ✅             | ❌              | ❌               |

Due to the inherent limitation of UUID, UUIDv7 is still not as human-readable as integer and occupies more space. On the other hand, UUIDv7 has addressed the biggest shortcoming from the previous versions. We expect industry will gradually abandon the bespoke solution and converge on UUIDv7 as the primary key for most use cases.

## **References**

1. [RFC 4122 (original UUID RFC)](https://tools.ietf.org/html/rfc4122)
1. [RFC 9562 (new UUID RFC)](https://datatracker.ietf.org/doc/html/rfc9562)



The [General Data Protection Regulation (GDPR)](https://gdpr-info.eu/) is a comprehensive data protection law implemented by the European Union (EU) to safeguard the personal data and privacy of individuals within the EU and the European Economic Area (EEA).

Database is the primary medium to store the personal data. Thus database compliance for GDPR is a mandatory.

## Database Hosting

The GDPR does not explicitly require that data be stored within the EU, but it sets conditions for data protection that often lead organizations to choose storing data within the EU to ensure compliance. In practice, most orgs would choose to do so and provision separate databases in EU.

![](/content/blog/database-compliance-for-gdpr/multi-region-databases.webp)

Each set of databases are in different regions/continents. They bear the same database schema whereas store different data. The
databases in EU store the personal data from EU.

Provisioning separate databases satisfies the GDPR compliance, however it adds the maintenance overhead. e.g. If you want to make a schema change, you need to make sure to deploy the schema change to all databases.

<HintBlock type="info">

Bytebase provides [Batch Mode](https://docs.bytebase.com/change-database/batch-change/) to consistently change databases from all locations.

</HintBlock>

## Database Operations

GDPR defines several key personas:

- **Personal Data Subject**. The individual whose personal data is being collected, held, or processed. This is any person who can be identified, directly or indirectly, by reference to data such as name, identification number, location data, online identifier, or specific characteristics. e.g. end users.
- **Personal Data Controller**. The entity (person, company, or organization) that determines the purposes and means of processing personal data. The data controller is responsible for ensuring that the processing of data complies with GDPR. e.g. Online store.
- **Personal Data Processor**. The entity (person, company, or organization) that processes personal data on behalf of the data controller. Data processors must adhere to the instructions of the data controller and comply with GDPR requirements related to data security and breach notification. e.g. Payment Service Vendor.
- **Data Protection Officer (DPO)**. e.g. CIO / CSO.

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

From [GDPR Art. 24: Responsibility of the controller](https://gdpr-info.eu/art-24-gdpr/):

> Taking into account the nature, scope, context and purposes of processing as well as the risks of varying likelihood and severity for the rights and freedoms of natural persons, the controller shall implement appropriate **technical and organisational measures** to ensure and to be able to demonstrate that processing is performed in accordance with this Regulation.

The Processor also assumes the similar responsibility as the controller. And sometimes, an organization acts as both the Data Controller and Data Processor at the same time. They need to comply with 2 categories of database operations:

- [Database maintenance operations (infrastructure level)](#infrastructure-level-database-maintenance-operations)
- [Database development operations (application level)](#application-level-database-development-operations)

### Database Maintenance Operations (Infrastructure Level)

These are handled by the database service provider, internal DBA and/or infrastructure teams. They need to make sure:

- Databases are provisioned inside the EU region.
- Databases and all their backups/traces are timely deprovisioned and purged upon request.
- Transfer of data outside of EU requires careful review according to [Art. 44: General principle for transfers](https://gdpr-info.eu/art-44-gdpr/).
- Encryption are enforced both at rest and in transit.

### Database Development Operations (Application Level)

Database development operations impose more compliance risks. Different job functions participate various
day-to-day database activities in different database systems:

- **Database schema evolution**. Schema evolves as the application releases new versions. Developers need to
  propagate the schema migration from dev environment to prod environment.
- **Adhoc change**. Developers sometimes need to make one-off change to correct the production data.
- **Query data**. Developers, DBAs, Business Analysts, Product Managers all need to query the production data to troubleshooting and conduct analysis.

To ensure GDPR compliance for these database operations, organizations must restrict access to authorized data only to authorized personnel. Additionally, they should document the reasons for access and maintain audit trails, as mandated by [Art. 30: Records of processing activities](https://gdpr-info.eu/art-30-gdpr/).

<HintBlock type="info">

Bytebase is a single place to provide [access control](https://docs.bytebase.com/security/database-permission/overview/), [review workflow](https://docs.bytebase.com/change-database/change-workflow/), [data masking](https://docs.bytebase.com/security/data-masking/overview/), [audit logging](https://docs.bytebase.com/security/audit-log/) for all database development operations for all database systems.

</HintBlock>

## Make your Database GDPR Compliant

Placing your databases in the EU is only the first step. To meet GDPR compliance requirements while maintaining productivity, organizations need the right structure, adaptive processes, and advanced tools in place.



With Bytebase, teams know **who**, **what**, **when**, **why**, **how** a database change has happened.

## Issue Detail

![_](/content/blog/how-bytebase-tracks-database-change/issue-detail.webp)

Bytebase Issue is the container to capture a database change process. A single issue can contain a change
to a single database as well as changes to hundreds of databases spanning multiple environments.

## Who

![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-who.webp)

1. Issue Creator
1. Reviewers according to the [Rollout Policy](https://docs.bytebase.com/change-database/environment-policy/rollout-policy/)
1. Subscribers interested in the progress.
1. Other participants can comment.

## What

![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-what.webp)

1. The target database instance, environment, and the database.
1. The SQL change statement.

## When

![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-when.webp)

You can specify when to deploy the issue (e.g. 2:00 midnight during non-business hours).

## Why

![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-why.webp)

1. Title to provide a change summary.
1. Description to provide detailed change context.
1. Labels to attach keyword information.
1. Comment to provide more context.

## How

![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-how.webp)

1. The database change process is organized into multiple stages.
1. The change is identified as high risk change and requires two approvers.
1. Checks
   - [SQL Review](https://docs.bytebase.com/sql-review/overview/) checks various anti-SQL patterns.
     ![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-how-sql-review.webp)
   - Summary report shows the estimated impact.
     ![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-how-summary.webp)

### Editing Activity Log

![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-activity.webp)

![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-activity-detail.webp)

### Change Execution Log

![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-change-execution-log1.webp)

![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-change-execution-log2.webp)

### Change History

![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-change-history.webp)

1.  Affected Tables
1.  Change statement
1.  Change diff

### Webhook Notification

![_](/content/blog/how-bytebase-tracks-database-change/issue-detail-webhook.webp)

Configure webhook to post change progress to the IM channel.

### Summary

Different from a [general issue tracking system like Jira](/blog/use-jira-for-database-change/), Bytebase
is built specifically for the database change management. Bytebase provides much more context
to track the entire database change process.



| Update History | Comment                                             |
| -------------- | --------------------------------------------------- |
| 2024/03/26     | Initial version.                                    |
| 2025/04/25     | Update for 2025. Add charts, optimization strategy. |

> > My co-founder Danny and I build the first version of [Google Cloud SQL for PostgreSQL](https://cloud.google.com/sql/postgresql). If you have ever wondered what the heck `cloudsqlsuperuser` is, that was Danny's legacy:) Occasionally, we get asked about AlloyDB for PostgreSQL and whether it's worthwhile to migrate from Cloud SQL. Below I collect some pricing notes.

## Introduction

[AlloyDB](https://cloud.google.com/alloydb) is Google Cloud's fully managed PostgreSQL-compatible database service designed for demanding enterprise workloads. It's positioned as the premium upgrade from Google Cloud SQL for PostgreSQL, offering enhanced performance, availability, and advanced features for mission-critical applications.

For customers migrating from Google Cloud SQL, other cloud database services like AWS Aurora, or on-premises PostgreSQL deployments, understanding AlloyDB's pricing structure is essential for accurate budgeting and cost optimization. This comprehensive guide explores AlloyDB pricing in detail, comparing it with alternatives and providing strategies to maximize your investment.

## Summary of Pricing Tiers

Different tiers in us-east1 according to the [Google Cloud pricing calculator](https://cloud.google.com/products/calculator/) as of April 2025:

| Tier       | Spec                                                                | Monthly cost |
| ---------- | ------------------------------------------------------------------- | ------------ |
| Low-end    | Cloud SQL Enterprise, 1c3.75G, 100 GB storage, HA                   | $130         |
| Middle-end | Cloud SQL Enterprise, 2c16G, 100 GB storage, HA                     | $315         |
| Middle-end | Cloud SQL Enterprise Plus, 2c16g, 100 GB storage, HA, Data Cache    | $521         |
| Middle-end | AlloyDB, 2c16g, 100 GB storage, HA                                  | $546         |
| High-end   | Cloud SQL Enterprise Plus, 96c768g, 1000 GB storage, HA, Data Cache | $19,966      |
| High-end   | AlloyDB, 96c768g, 1000 GB storage, HA                               | $24,977      |

AlloyDB pricing follows a predictable model similar to Cloud SQL, with charges based on computing resources (vCPU/memory), storage, and networking. While AlloyDB commands a premium over Cloud SQL, this markup reflects its enhanced performance capabilities, advanced features, and enterprise-grade architecture.

Unlike AWS Aurora, which uses a less-predictable, request-based pricing model (charging per million requests), AlloyDB offers transparent resource-based pricing that makes budgeting more straightforward. This predictability is particularly valuable for enterprises with stable workloads seeking to avoid billing surprises.

![Google Cloud Database Pricing Comparison](/content/blog/understanding-google-alloydb-pricing/pricing_comparison_chart.webp)

## AlloyDB Architecture and Pricing Components

Understanding AlloyDB's architecture is key to grasping its pricing model. AlloyDB is organized into clusters containing different types of instances:

- **Primary instance**: The read-write instance that serves as the main database
- **Read pool instances**: Optional read-only instances that scale read capacity
- **Secondary instances**: Optional instances in different regions for disaster recovery

Each instance can be configured with different amounts of compute resources (vCPU and memory) and can be either highly available (HA) with standby nodes across multiple zones or basic (non-HA) with a single node in one zone.

AlloyDB's pricing breaks down into four main components:

1. **CPU and memory**: Resources allocated to your instances
2. **Storage**: Data storage, backup storage, and transaction logs
3. **Networking**: Data transfer between regions and to the internet

Let's examine each component in detail.

## CPU and Memory Pricing

| USD per month (us-east1) | AlloyDB  | Cloud SQL Enterprise Plus | Cloud SQL Enterprise | GCE N2    |
| ------------------------ | -------- | ------------------------- | -------------------- | --------- |
| Per vCPU                 | $54.5091 | $39.201                   | $30.149              | $23.07603 |
| Per GB memory            | $9.2418  | $6.643                    | $5.11                | $3.09301  |

AlloyDB's compute pricing reflects its position as a premium database service:

- AlloyDB has a 39% markup over Cloud SQL Enterprise Plus
- Cloud SQL Enterprise Plus has a 30% markup over Cloud SQL Enterprise
- Cloud SQL Enterprise has a 30% markup for vCPU and 65% markup for memory over the underlying GCE N2 instance type

This tiered pricing structure corresponds to increasing levels of performance, features, and management capabilities across the product line.

### Committed Use Discounts

Both AlloyDB and Cloud SQL offer the same committed use discounts:

- 1-year commitment: 25% discount (pay 75% of on-demand price)
- 3-year commitment: 52% discount (pay 48% of on-demand price)

These discounts can significantly reduce costs for stable, predictable workloads.

![AlloyDB Committed Use Discounts](/content/blog/understanding-google-alloydb-pricing/committed_use_discounts.webp)

### High Availability Pricing

High availability (HA) configurations, which provide automated failover capabilities across multiple zones, double the compute costs for both AlloyDB and Cloud SQL. While this represents a substantial increase, the business continuity benefits often justify the investment for production workloads.

### Capacity Limits

AlloyDB offers substantial scalability with support for:

- Up to 128 vCPUs per instance
- Up to 864 GB of memory per instance
- Up to 20 read pool instances per cluster for horizontal scaling

This allows AlloyDB to handle enterprise-scale workloads while maintaining performance.

## Storage Pricing

| USD per month (us-east1) | AlloyDB                       | Cloud SQL Enterprise Plus        | Cloud SQL Enterprise |
| ------------------------ | ----------------------------- | -------------------------------- | -------------------- |
| Per GB data              | $0.338939                     | $0.17 SSD, $0.09 HDD (2x for HA) | Same as Plus         |
| Per GB backup            | $0.113004                     | $0.08                            | Same as Plus         |
| Per GB transaction log   | $0.113004 (first 7 days free) | N/A                              | N/A                  |

AlloyDB uses an intelligent regional storage system that automatically scales based on your needs. Key aspects of AlloyDB storage pricing include:

### Regional Storage System

AlloyDB's storage architecture separates compute from storage, allowing for:

- Independent scaling of compute and storage resources
- Storage sharing across all instances in a cluster
- Consistent storage costs when horizontally scaling with read pool instances

This architecture is particularly beneficial for read-heavy workloads, as you can add read capacity without increasing storage costs.

### Log-Based Architecture

AlloyDB's log-based architecture includes transaction logs as part of its backup and recovery system:

- First seven days of transaction logs are retained at no extra charge
- Logs beyond seven days are charged at the same rate as backup storage
- This architecture enables point-in-time recovery and efficient cross-region replication

### Storage Pricing Comparison

AlloyDB's storage is priced higher than Cloud SQL, reflecting its advanced storage architecture:

- AlloyDB data storage ($0.338939/GB) is approximately twice the cost of Cloud SQL SSD storage ($0.17/GB)
- AlloyDB backup storage ($0.113004/GB) is about 40% more expensive than Cloud SQL backup storage ($0.08/GB)
- AlloyDB's transaction log storage is unique to its architecture and has no direct equivalent in Cloud SQL

Despite the higher per-GB cost, AlloyDB's storage system can be more cost-effective for certain workloads due to its efficiency and shared nature across instances.

## Networking Pricing

Networking costs are consumption-based for both AlloyDB and Cloud SQL, making them inherently less predictable than compute and storage costs. Understanding these costs is particularly important for deployments using cross-region replication or serving global audiences.

### Data Transfer Within Google Cloud

AlloyDB provides more granular pricing for cross-region data transfer compared to Cloud SQL:

### Cross-Region Data Transfer (Northern America to Europe)

- AlloyDB: $0.05/GB
- Cloud SQL: $0.12/GB (if not intra-continental)

This more granular pricing can lead to significant savings for workloads with specific regional traffic patterns.

### Internet Egress Pricing

AlloyDB also offers tiered pricing for internet egress based on volume and region:

### Internet Egress (Within North America)

- AlloyDB: $0.12/GB for 0-1TB, $0.11/GB for 1-10TB, $0.08/GB for 10+TB
- Cloud SQL: $0.19/GB (not using Cloud Interconnect)

For high-volume outbound traffic, AlloyDB's tiered pricing can provide substantial savings.

### Cross-Region Replication Considerations

Cross-region replication is a key feature for disaster recovery and global distribution of data. While both AlloyDB and Cloud SQL support this capability, AlloyDB's implementation is more performant and feature-rich:

- AlloyDB supports up to five secondary clusters for a primary cluster
- Each secondary cluster can have up to 20 read pool instances
- Secondary clusters can be promoted to primary clusters in disaster scenarios
- Switchover with zero data loss is supported for planned migrations

When planning cross-region deployments, consider both the data transfer costs and the performance benefits of AlloyDB's replication architecture.

## Cost Optimization Strategies

![AlloyDB Cost Optimization Strategies](/content/blog/understanding-google-alloydb-pricing/cost_optimization_strategies.webp)

**1. Use Basic Instances for Non-Production Environments**

- Deploy basic (non-HA) instances for development, testing, and staging
- Save approximately 50% on compute costs compared to HA instances
- Easily upgrade to HA when moving to production

**2. Leverage Committed Use Discounts**

- Analyze your workload stability to determine commitment eligibility
- Consider 1-year commitments (25% discount) for newer applications
- Use 3-year commitments (52% discount) for stable, long-term workloads

**3. Right-Size Your Instances**

- Monitor CPU and memory usage to identify over-provisioned resources
- Scale instances up or down based on actual requirements
- Consider vertical scaling for primary instances and horizontal scaling with read pools for read-heavy workloads

**4. Optimize Storage Usage**

- Take advantage of AlloyDB's automatic storage scaling
- Monitor transaction ID usage to prevent vacuum-related issues
- Configure appropriate backup retention periods based on compliance requirements

**5. Reduce Networking Costs**

- Co-locate related resources in the same region when possible
- Be strategic about cross-region replication based on pricing tiers
- Consider data transfer costs when designing multi-region architectures

**6. Implement Performance Best Practices**

- Add read pool instances to offload read traffic from primary instances
- Enable the Columnar Engine for analytical queries to improve performance
- Split large instances into smaller instances where possible
- Keep database table count under 10,000 to optimize performance

**7. Monitor and Optimize Continuously**

- Use AlloyDB tools like performance snapshot reports and query insights
- Configure maintenance windows to minimize disruption
- Set up alerts for resource utilization thresholds
- Regularly review and adjust your configuration based on changing workload patterns

## Real-World Pricing Examples

To illustrate AlloyDB pricing in practice, let's examine some real-world scenarios:

**Example 1: E-commerce Database (Middle Tier)**

Configuration:

- 8 vCPU, 64 GB memory primary instance (HA)
- 2 read pool instances with 4 vCPU, 32 GB memory each
- 500 GB storage
- 1 TB monthly data transfer between regions

Monthly cost breakdown:

- Compute (primary): $1,090 (8 vCPU × $54.51 × 2 for HA + 64 GB × $9.24 × 2 for HA)
- Compute (read pools): $545 (2 instances × 4 vCPU × $54.51 + 2 instances × 32 GB × $9.24)
- Storage: $169 (500 GB × $0.339)
- Backup: $56 (500 GB × $0.113)
- Data transfer: $50 (1 TB × $0.05 between North America and Europe)
- Total: Approximately $1,910 per month

With a 1-year commitment, this would reduce to approximately $1,570 per month.

**Example 2: Financial Services OLTP Workload (High Tier)**

Configuration:

- 32 vCPU, 256 GB memory primary instance (HA)
- 4 read pool instances with 16 vCPU, 128 GB memory each
- 2 TB storage
- 5 TB monthly data transfer between regions

Monthly cost breakdown:

- Compute (primary): $4,360 (32 vCPU × $54.51 × 2 for HA + 256 GB × $9.24 × 2 for HA)
- Compute (read pools): $4,360 (4 instances × 16 vCPU × $54.51 + 4 instances × 128 GB × $9.24)
- Storage: $678 (2 TB × $0.339)
- Backup: $226 (2 TB × $0.113)
- Data transfer: $250 (5 TB × $0.05 between North America and Europe)
- Total: Approximately $9,874 per month

With a 3-year commitment, this would reduce to approximately $5,740 per month.

## References

- [AlloyDB pricing](https://cloud.google.com/alloydb/pricing)
- [Cloud SQL pricing](https://cloud.google.com/sql/pricing)
- [Google Cloud pricing calculator](https://cloud.google.com/products/calculator/)
- [AlloyDB cross-region replication](https://cloud.google.com/alloydb/docs/cross-region-replication/about-cross-region-replication)
- [AlloyDB best practices](https://cloud.google.com/alloydb/docs/best-practices-improve-performance-availability)
- [AlloyDB basic instances](https://cloud.google.com/alloydb/docs/basic-instance)



Snowflake offers scalable compute and storage resources, with a SQL-based interface for data manipulation and analysis. However, when integrating Continuous Integration/Continuous Deployment (CI/CD) processes with Snowflake, especially in comparison to its querying capabilities in SnowSight, several shortcomings or challenges can be identified.

## No streamlined review and rollout process

A typical change workflow in Snowflake:

1. A data engineer creates a schema change ticket in Jira.
1. The Snowflake admin reviews the ticket, and then uses SnowSight to apply the change to the testing instance.
1. The data engineer verifies the change and replies to the ticket to request the admin to apply the change to the production instance.
1. The Snowflake admin uses SnowSight to apply the change to the production environment.
1. The data engineer verifies the change and closes the ticket.

The above process involves back-and-forth and is error-prone. e.g. What if the admin mistakenly applies the change to prod first?

## No automated SQL lint

Modern CI pipeline has adopted automatic review checks. For Snowflake, it's especially important, since dropping a column might break the downstream data pipeline. The snowflake platform doesn't provide such lint.

## GitOps

Snowflake recently [announced Git integration](https://www.snowflake.com/blog/snowflake-expands-developer-programmability-snowpark-container-services/):

> This includes such features as Git integration (private preview), which provides easy integration of application code with git and git workflow. Users can view, run, edit, and collaborate with assets that exist in a Git repo, right inside of Snowflake.

It would be even better to see [Vercel-like](https://vercel.com) experience, where when a change script is merged into a branch, it will automatically trigger a rollout pipeline with optional approval flow.

## Meet Bytebase

To tackle the CI/CD challenges, Snowflake creates [schemachange](https://github.com/Snowflake-Labs/schemachange). The other solution is Bytebase ([schemachange vs. Bytebase](https://www.bytebase.com/blog/snowflake-schema-change/)).

### Web-based streamlined review and rollout process

Bytebase provides a review and rollout interface. It's similar to Jira, but is specifically tailored for doing database rollout. A staged rollout to propagate changes from test to prod instance.

![issue](/content/blog/snowflake-ci-cd-devops/issue.webp)

Record schema change history.

![change history](/content/blog/snowflake-ci-cd-devops/change-history.webp)

Detect schema drift caused by unexpected change.

![schema-drift](/content/blog/snowflake-ci-cd-devops/schema-drift1.webp)
![schema-drift](/content/blog/snowflake-ci-cd-devops/schema-drift2.webp)

### SQL Review and API

Bytebase provides automatic SQL review rules to detect Snowflake SQL anti-patterns. Once configured, the SQL review will be activated during the review process above. Moreover, one can call the Bytebase API from the VCS CI pipeline.

![sql-review](/content/blog/snowflake-ci-cd-devops/sql-review-policy.webp)

Inline Check in [GitHub PR](https://github.com/bytebase/ci-example/pull/4/files).

![github-pr](/content/blog/snowflake-ci-cd-devops/github-pr.webp)

## Summary

Bytebase brings the DevOps methodology and GitHub/GitLab-like experience to the Snowflake world. To learn more, check out the [step-by-step tutorial](https://docs.bytebase.com/tutorials/database-change-management-with-snowflake/).



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

![replaced-tools](/content/blog/_shared/replaced-tools.webp)

## SQL Client

![sql-editor-data-masking](/content/blog/all-database-tools-bytebase-replaces/mask.webp)

Traditional SQL clients such as DBeaver, DataGrip, Navicat, pgAdmin, phpMyAdmin provide a GUI interface.
Bytebase also provides a web-based [SQL Editor](/sql-editor/) providing:

- Centralized database credential management
- Request and grant [database permissions](https://docs.bytebase.com/security/database-permission/overview/) workflow and enable Just-in-Time Database Access
- [Dynamic data masking](https://docs.bytebase.com/security/data-masking/overview/)
- Audit logs

## Schema Migration

Liquibase, Flyway, Sqitch are CLI based. On the other hand, Bytebase provides a collaboration workspace
for DBAs and developers to collaborate on database changes.

- [SQL linter](https://docs.bytebase.com/sql-review/overview/) to detect anti-patterns.
  ![schema-review-table-drop-naming](/content/blog/all-database-tools-bytebase-replaces/schema-review-table-drop-naming.webp)
- [Streamline the change](https://docs.bytebase.com/change-database/batch-change/) across different environments and allow to batch change many databases consistently.
- [GitOps integration](https://docs.bytebase.com/gitops/overview/) with mainstream VCSs to enable database-as-code workflow.
- [Online schema migration for MySQL](https://docs.bytebase.com/change-database/online-schema-migration-for-mysql/) with gh-ost integration.

## Change Coordination

A typical database change workflow:

1. The developer creates a Jira ticket with the planned SQL change script.
1. DBA reviews the ticket, discuss it with the developer in Jira or via IM.
1. DBA runs the change script offline.
1. DBA updates the ticket status / notify the developer.

![Issue detail interface](/content/blog/all-database-tools-bytebase-replaces/issue-detail.webp)

Bytebase provides a single place to coordinate the database changes. The issue interface provides
a holistic view of the database change status. Upon approval, changes are rolled out from Bytebase
directly instead of switching to a different tools. All the activities are recorded and can be further
configured to post to IM via [webhook](https://docs.bytebase.com/change-database/webhook).

## Summary

Bytebase takes care of all human-to-database operations. It's an all-in-one solution for teams to
adopt a unified process to change, query, secure, govern the databases. Bytebase consolidates disparate DB tools and 10x efficiency, security and PII compliance for the engineering org. To learn more, please check out [case studies](/blog/category/case-study/).

![change-query-secure-govern-database-all-in-one](/content/blog/_shared/db-scheme-lg.png)



**Database schema drift** or just **schema drift** is the case where the actual schema in the live database (the actual state) is different from the source of truth (the desired state). It's also one of the most frequent root causes of the database related outages.

The **live database part** is easy to understand:

- For MySQL, it's the output of `mysqldump --no-data`
- For PostgreSQL, it's the output of `pg_dump --schema-only`

While the **source of truth part** is more complex...

## The source of truth

One may first wonder why we need to keep a separate source of truth. The reason is because the schema in the live database may not always be the desired state. Human error or software bugs could both accidentally change the database schema. So it's better to have a separate source of truth, this idea is similar to the classic [double-entry bookkeeping](https://en.wikipedia.org/wiki/Double-entry_bookkeeping) used in accounting.

Naturally, a good place to store this source of truth is the version control system (VCS), the same place where the application code is stored. This is known as database-as-code, a GitOps practice. Solutions like Liquibase, Flyway support this approach. Bytebase also supports this and even go a step further to provide point-and-click UI to configure this.

![_](/content/blog/what-is-database-schema-drift/project-vcs.webp)

## The format of source of truth

After we figure out where to store the source of truth, next we need fo decide which format to use as the source of truth. There are 2 approaches here,  state-based and migration-based, long story short:

- State-based approach stores the desired end state of the entire schema in the code repository
- Migration-based approach stores the migration scripts in the repository. Each script contains a set of DDL statements such as CREATE/ALTER/DROP TABLE. The desired schema state is achieved by executing each of those scripts in a deterministic order.

State-based approach is a more intuitive format since a single file corresponds to a database schema. However, state-based approach has its limitation. e.g. it can't distinguish between table rename and drop/create table. Bytebase supports both approaches.

## Schema drift detection

Using migration-based approach makes drift detection harder. System needs to derive the desired database schema from all migration files, and then compare it with the actual schema in the live database to detect the drift.

Bytebase optimizes this process by introducing the schema snapshot and write back feature:

1. For every schema migration, Bytebase will record the schema snapshot.
2. If team manage database schema under version control system, they can configure Bytebase to write the schema snapshot back to the repository at a specified path. Below shows an example how Bytebase writes back the full schema snapshot at the user configured path.

![_](/content/blog/what-is-database-schema-drift/schema-write-back.webp)
And by leveraging the schema snapshot, Bytebase continuously compare between the schema snapshot and the actual database schema in the live database and report drift once found👇
![_](/content/blog/what-is-database-schema-drift/drift-alert.webp)
The detailed drift👇
![_](/content/blog/what-is-database-schema-drift/drift-diff.webp)

## Summary

Database schema drift is one of the most frequent root causes of the database related outages. Recording the desired database schema state in the version control system like GitLab is the first stepping stone to tackle it. Bytebase takes the extra mile to present an easy-to-use UI for users to adopt this practice and surfaces detailed schema drift info once detected.

If this interests you, please do check out our [live demo](https://demo.bytebase.com) or use 1 liner to [deploy yourself](https://docs.bytebase.com/get-started/deploy-with-docker).

![change-query-secure-govern-database-all-in-one](/content/blog/_shared/db-scheme-lg.png)

## Further Readings

- [What is Database Change Management?](/blog/what-is-database-change-management)
- [Database-as-Code](/blog/database-as-code)
- [Bytebase Database-as-Code VCS integration](https://docs.bytebase.com/gitops/overview)
- [Database Version Control, State-based or Migration-based?](/blog/database-version-control-state-based-vs-migration-based)


The
Bytebase Blog

Explore the standard for database development

Solutions & Platform

Comparisons

Legal

Resources

Company