Compare zero-downtime schema migration strategies: native online DDL, expand-and-contract, online schema change tools, and blue-green deployments.

Zero-Downtime Database Schema Migration Strategies

Replit's agent wiped a production database during a code freeze, then said rollback was impossible. It wasn't. Every step of that failure maps to a control you can put in place today.

How to Prevent an AI Agent From Dropping Your Production Database

What the HIPAA Security Rule asks of database access: a name on every session, minimum necessary at the column, emergency access that still gets logged, and an audit trail that can say who read a patient's chart. The controls behind each, what OCR fines, and what the proposed 2027 rule tightens.

Database Access Control for HIPAA: The Security Rule at the Database

What PCI DSS v4.0.1 asks of database access: least privilege by role, MFA on every CDE session, service accounts treated like humans, masked PANs, and a query-level trail kept for a year. Where each control lives, and where Bytebase stops.

Database Access Control for PCI DSS: Requirements 7, 8, and 10 at the Database

RDS IAM authentication replaces a stored database password with a 15-minute token. This reference implementation adds the rest of the JIT path: a scoped IAM role, verified identity, a bounded session, approval in Git, and an honest look at the four limits that remain.

Just-in-Time Database Access with AWS RDS IAM Authentication

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

On-Behalf-Of Database Access for AI Agents

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

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

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

What is a Database MCP Server?

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

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

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

When the Agent Writes the Migration, Who Approves It?

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

What is Database PAM (Privileged Access Management)?

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

SQL Server Schema Migration and Change Management

Database credentials management best practices: store passwords and secrets in one place, rotate on a schedule, and separate the application path from the human path.

Database Credentials Management: Best Practices

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

MySQL Audit Logging Guide

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

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

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

Static vs Dynamic Data Masking: When to Use Which

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

BigQuery Dynamic Data Masking

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

SQL Server Dynamic Data Masking

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

Oracle Dynamic Data Masking

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

How to Handle Ad-hoc Database Data Changes?

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

What is Database Access Control (DAC)?

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

Dynamic Data Masking Best Practices

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

How Bytebase Handles Audit Logging

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

Database Access Control (DAC) Best Practices

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

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

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

Database Design Patterns Every Developer Should Know

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

pgvector: How to Add Vector Search to PostgreSQL

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

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

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

From Schema as Code to Schema as Context

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

How to Fix Slow MySQL Queries: A Practical Guide

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

Database Blue-Green Deployment: A Practical Guide

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

How to Govern AI Agent Database Access

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

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

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

Postgres Query Plan: How to Read and Fix Performance Issues

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

The Anatomy of Postgres Permission ERROR 42501

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

Postgres Logs Explained: A Practical Guide for Production

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

Database Multi-Environment Deployments - The Challenges and Patterns

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

Database Audit Logging: Two Layers, One Trail

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

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

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

Require Primary Key: A SQL Review Rule Explained

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

Enforce NOT VALID in CHECK: A SQL Review Rule Explained

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

Postgres Audit Logging Guide

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

Prohibit CASCADE: A SQL Review Rule Explained

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

How to Migrate from Supabase to AWS

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

What is Enterprise Database Management (EDM)?

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

Postgres Transaction ID (XID) Wraparound

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

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

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

Database Compliance for LGPD: Implications and Best Practices

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

What is Postgres LATERAL JOIN?

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

MySQL Online DDL: A Practical Guide

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

SQL Table Naming Dilemma: Singular vs. Plural

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

Postgres Case Sensitivity Explained

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

What is pg_database_owner in Postgres

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

Which Postgres Operation causes a table rewrite

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

Postgres Row-Level Security Footguns

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

Postgres Rollback Explained

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

Postgres Schema Migration without Downtime

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

gh-ost Limitations You Need to Know Before Production

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

Postgres best practices I wish every app developer knew

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

How to Use Postgres CREATE INDEX CONCURRENTLY

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

Database Security Lifecycle Management

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

HIPAA Data Security and Retention Requirements

Compare the online index creation difference between Postgres and MySQL

Postgres vs. MySQL: Online Index Creation

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

SOC 2 Data Security and Retention Requirements

Compare the different indexing options between Postgres and MySQL

Postgres vs. MySQL: Indexing Options

Compare the JSON support difference between Postgres and MySQL

Postgres vs. MySQL: JSON Support

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

Postgres Row-Level Security (RLS) Limitations and Alternatives

Compare the DDL transaction difference between Postgres and MySQL

Postgres vs. MySQL: DDL Transaction Difference

Understanding DynamoDB Pricing (2026)

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

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

Understanding AWS RDS Pricing (2026)

Explain why MySQL provides a variety of timeout settings.

MySQL Timeout Explained

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

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

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

Top 10 Database Schema Design Best Practices

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

Database Version Control Best Practice

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

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

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

Database Security Compliance

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

What is a Change Advisory Board (CAB)?

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

What is Production Database

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

What is Database Activity Monitoring (DAM)?

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

Multi-Tenant Database Architecture Patterns Explained

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

ServiceNow and GitHub Integration for Database Change Automation

Pros and Cons between choosing uuidv1, uuidv4, uuidv7

How to Choose Database UUID?

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

Primary Key vs Foreign Key: What Are the Differences?

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

What is Write Ahead Logging (WAL)

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

Just-in-Time Database Access

Explain the different database permission levels

Database Security: Read, Write, and Admin Permission Levels

Explain database rollback and how to automate it

Database Rollback: When Shit Hits the Fan

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

What is Database DevSecOps?

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

What is Dynamic Data Masking (DDM)

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

What is Database DevOps?

Explain why Postgres provides a variety of timeout settings.

Postgres Timeout Explained

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

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

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

Database Compliance for GDPR: Implications and Best Practices

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

Understanding Google Cloud AlloyDB Pricing (updated for 2026)

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

Snowflake CI/CD and DevOps Best Practice

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

All the Database Tools Bytebase Replaces

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

What is Database Schema Drift?

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

Postgres Dynamic Data Masking

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

What is Database Version Control?


<HintBlock type="info">

This post is maintained by Bytebase, an open-source database governance platform that is [SOC 2 Type II compliant](https://www.bytebase.com/blog/soc2-type2/). Bytebase helps teams implement SOC 2 controls at the database layer, including access control, audit logging, change management, and data retention.

</HintBlock>

## What is SOC 2?

**SOC 2 (System and Organization Controls 2)** is a widely adopted security and compliance framework developed by the **American Institute of Certified Public Accountants (AICPA)**. It sets the standard for how service organizations should manage customer data to protect it from unauthorized access, security incidents, and operational failures — especially in cloud-based environments.

SOC 2 compliance is evaluated against five **Trust Services Criteria**:

- **Security** (mandatory) — Safeguarding systems and data from unauthorized access
- **Availability** — Ensuring systems operate reliably and as promised
- **Processing Integrity** — Verifying accurate and complete data processing
- **Confidentiality** — Protecting sensitive information from disclosure
- **Privacy** — Handling personal data appropriately

There are two report types:

- **Type I**: A snapshot of controls at a single point in time
- **Type II**: An audit of how well those controls operate over time (typically 3–12 months)

Type II reports are considered more rigorous and credible, especially for enterprise customers.

### Why SOC 2 Matters for Database Systems

SOC 2 is particularly relevant for:

- SaaS platforms and cloud service providers
- Organizations that manage large volumes of user data
- Teams responsible for storing, processing, or transmitting customer data in production environments

While SOC 2 encompasses multiple operational areas, **this article focuses specifically on the database-related aspects of SOC 2** — namely, **data security and retention requirements**.

These are the areas where tools like [Bytebase](/) can help engineering teams automate and enforce controls for compliance.

## Data Security Requirements

In SOC 2, **Security** is the only mandatory Trust Services Criteria — and it's especially relevant for **database systems**, which are often the **central store of sensitive customer data**. To meet SOC 2 expectations, organizations must establish strict controls to prevent unauthorized access, monitor database activities, and ensure secure operational practices.

### 1. Information Security

Organizations must implement robust technical controls to secure their database infrastructure:

- Enable **activity monitoring and alerting** for suspicious database queries
- Detect **database anomalies** and **slowdowns**
- Define **incident response plans** for database-specific security breaches

<HintBlock type="info">

**Bytebase** helps enforce these practices with built-in [audit log](/blog/soc2-audit-logging/), **anomaly detection**, and possible integration with incident workflows.

</HintBlock>

### 2. Access Controls

Access to production databases must be strictly limited:

- Use **multi-factor authentication (MFA)** for all database access
- Perform **quarterly permission reviews** for all database accounts
- Prevent data exfiltration through **download restrictions**
- Monitor production environments for **unauthorized access attempts**

<HintBlock type="info">

With **MFA**, **role-based access control (RBAC)**, and **just-in-time access**, Bytebase ensures only the right people in the right time can access your specific databases.

</HintBlock>

### 3. Change Management

Database changes are a major risk vector and must be carefully managed:

- Establish **formal change request processes**
- Enforce **approval workflows** before schema changes reach production
- Test all changes in **non-production environments**
- Maintain [audit logs](/blog/soc2-audit-logging/) of every schema modification
- Implement **rollback procedures** for failed changes

<HintBlock type="info">

Bytebase offers **UI and GitOps-style workflows** for schema change management, **custom approval flows**, **change history**, and **1-click rollback**.

</HintBlock>

### Real-World Database Security Controls

Organizations may tailor their implementation to fit their architecture. Examples include:

- Using database management tool like Bytebase to manage database access approvals and schema deployments
- Restricting superuser roles to on-call DBAs with just-in-time access
- Setting up alerting for unusual query patterns in production databases
- Automating user access reviews and integrating audit logs into SIEM systems

The essential point is this: your controls must align with the **Security Trust Services Criteria** — regardless of whether they're manual, scripted, or automated through tools like **Bytebase**.

## Data Retention Requirements

Under SOC 2’s **Confidentiality** and **Privacy** criteria, organizations must manage data responsibly throughout its lifecycle. While SOC 2 doesn’t specify exact durations, it requires:

- Identifying and classifying sensitive data in databases
- Defining retention periods by data type
- Protecting data during retention
- Ensuring secure and verifiable deletion afterward

<HintBlock type="info">

Bytebase users can align retention policies with **data classification, data masking, schema change management, and audit logs** to ensure enforcement across environments.

</HintBlock>

Here is the basic process to implement data retention policies:

### 1. Data Identification & Classification

- Audit what data is stored across all environments (e.g., production, staging)
- Classify by sensitivity: e.g., **Public**, **PII**, **PHI**, **Confidential**
- Map data types to retention and deletion policies

<HintBlock type="info">

Consider tagging schemas or tables in Bytebase by classification.

</HintBlock>

### 2. Legal and Compliance Requirements

- Identify applicable regulations: **GDPR**, **HIPAA**, **SOX**, **PCI DSS**, etc.
- Document how retention timelines map to legal obligations
- Include any **client contract clauses** affecting data storage

### 3. Retention Periods

- Define periods per data category or table
- Consider different rules for logs, transactions, and user profiles
- Document in a central retention policy referenced by DBAs and auditors

### 4. Secure Deletion Procedures

- Detect data that has reached its retention limit
- Apply deletion techniques appropriate to your database engine (e.g., row-level delete, partition drop, full table purge)
- Log and verify all deletions
- Automate purging when feasible

<HintBlock type="info">

Integrate deletion workflows with Bytebase’s change workflows and audit logs for traceability.

</HintBlock>

### Best Practices for Database Retention

- **Policy Review:** Update regularly to stay aligned with evolving laws and systems
- **Automation:** Use tooling to reduce manual effort and risk
- **Backups:** Include backup lifecycle management in your retention strategy
- **Auditing:** Log all deletion activities; regularly validate adherence
- **Legal Holds:** Implement workflows to override retention rules during investigations

## SOC 2 Implementation Strategies for Databases

| Focus Area              | Strategy                                                        |
| ----------------------- | --------------------------------------------------------------- |
| **Access Control**      | RBAC, least privilege, just-in-time access, audit logs          |
| **Encryption**          | At rest (AES-256), in transit (TLS), with secure key management |
| **Activity Monitoring** | Full query logging, alerting on suspicious behavior             |
| **Change Management**   | Change history, approval flows, rollback plans                  |
| **Backup & Recovery**   | Secure, automated backups with tested restore plans             |

<HintBlock type="info">

**Bytebase** helps implement and enforce these practices through DevSecOps automation and built-in auditability.

</HintBlock>

By formalizing your database data retention policy and combining it with secure, automated workflows, you strengthen both your **SOC 2 compliance** and your organization’s operational maturity. Tools like **Bytebase** not only enforce these controls, but also simplify evidence collection for audits.



_For the impatience, jump to the [comparison table](#comparison-table-postgresql-17-vs-mysql-8)_

PostgreSQL and MySQL stand as two of the most popular choices. Both systems have evolved significantly over the years, with PostgreSQL 17 and MySQL 8 representing their latest General Availability (GA) versions. One of the most critical aspects of database performance tuning is the effective use of indexes. This article compares the indexing capabilities of PostgreSQL 17 and MySQL 8, providing insights for experienced database administrators and developers.

## B-tree Indexes: The Foundation

B-tree indexes are the default and most commonly used index type in both PostgreSQL and MySQL. They are well-suited for equality and range queries on data that can be sorted.

### PostgreSQL B-tree Indexes

```sql
-- Basic B-tree index
CREATE INDEX idx_customer_name ON customers(name);

-- Multi-column B-tree index
CREATE INDEX idx_customer_location ON customers(city, state, country);

-- B-tree index with sort options
CREATE INDEX idx_product_price_desc ON products(price DESC NULLS LAST);
```

PostgreSQL B-trees can handle equality and range queries, as well as `BETWEEN`, `IN`, `IS NULL`, and `IS NOT NULL` conditions. They can also be used with pattern matching operators like `LIKE` and `~` when the pattern is anchored to the beginning of the string.

### MySQL B-tree Indexes

```sql
-- Basic B-tree index
CREATE INDEX idx_customer_name ON customers(name);

-- Multi-column B-tree index
CREATE INDEX idx_customer_location ON customers(city, state, country);

-- B-tree index with descending order (MySQL 8.0+)
CREATE INDEX idx_product_price_desc ON products(price DESC);
```

MySQL B-trees support equality and range queries, as well as `BETWEEN` and `IN` conditions. They can also be used with `LIKE` when the pattern is anchored to the beginning of the string.

### Key Differences

While both systems use B-tree as their default index type, PostgreSQL offers more control over NULL sorting with `NULLS FIRST`/`NULLS LAST` options and supports regex pattern matching with the `~` operator. MySQL added support for descending indexes in version 8.0, catching up with a feature PostgreSQL has had for many years.

## Hash Indexes: Equality-Only Optimization

Hash indexes are optimized for equality comparisons and generally provide faster lookups than B-trees for simple equality operations.

### PostgreSQL Hash Indexes

```sql
-- Hash index
CREATE INDEX idx_user_id_hash ON users USING HASH (user_id);
```

PostgreSQL's hash indexes are transaction-safe and crash-recoverable, making them suitable for production use.

### MySQL Hash Indexes

```sql
-- Hash index for MEMORY table
CREATE TABLE lookup (
    id INT NOT NULL,
    name VARCHAR(100),
    INDEX USING HASH (name)
) ENGINE = MEMORY;
```

In MySQL, explicit hash indexes are primarily available for MEMORY tables. InnoDB uses an adaptive hash index internally, but this is managed automatically by the storage engine.

### Key Differences

PostgreSQL's hash indexes are more versatile and can be used with any storage engine, while MySQL's explicit hash indexes are limited to MEMORY tables. PostgreSQL's implementation is also transaction-safe and crash-recoverable, making it more suitable for production environments.

## Specialized Index Types in PostgreSQL

PostgreSQL offers several specialized index types that have no direct equivalent in MySQL.

### GiST (Generalized Search Tree) Indexes

GiST indexes provide an infrastructure for various indexing strategies, particularly useful for complex data types:

```sql
-- GiST index for geometric data
CREATE INDEX idx_location ON cities USING GIST (location);

-- GiST index for text with trigram matching
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_document_trigram ON documents USING GIST (content gin_trgm_ops);
```

### SP-GiST (Space-Partitioned GiST) Indexes

SP-GiST indexes support non-balanced disk-based data structures like quadtrees and k-d trees:

```sql
-- SP-GiST index for points
CREATE INDEX idx_points ON points USING SPGIST (point);

-- SP-GiST index for text pattern matching
CREATE INDEX idx_text_prefix ON documents USING SPGIST (content text_ops);
```

### GIN (Generalized Inverted Index) Indexes

GIN indexes are "inverted indexes" suitable for composite values like arrays and full-text search:

```sql
-- GIN index for array containment
CREATE INDEX idx_tags ON posts USING GIN (tags);

-- GIN index for full-text search
CREATE INDEX idx_document_search ON documents USING GIN (to_tsvector('english', content));
```

### BRIN (Block Range INdex) Indexes

BRIN indexes store summaries about values in consecutive physical block ranges, making them efficient for large tables with natural ordering:

```sql
-- BRIN index for timestamp data
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (created_at);

-- BRIN index with larger block range
CREATE INDEX idx_temperature ON weather_data USING BRIN (recorded_at) WITH (pages_per_range = 32);
```

## Specialized Index Types in MySQL

MySQL also offers some specialized index types that have no direct equivalent in PostgreSQL.

### SPATIAL Indexes (R-tree)

MySQL uses R-tree indexes for spatial data:

```sql
-- Spatial index
CREATE TABLE locations (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    location POINT NOT NULL SRID 4326,
    SPATIAL INDEX idx_location (location)
) ENGINE = InnoDB;

-- Query using spatial index
SELECT name, ST_AsText(location)
FROM locations
WHERE ST_Contains(
    ST_GeomFromText('POLYGON((-74 40.7, -74 40.9, -73.9 40.9, -73.9 40.7, -74 40.7))', 4326),
    location
);
```

### FULLTEXT Indexes

MySQL has built-in support for full-text search:

```sql
-- Full-text index
CREATE TABLE articles (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT INDEX idx_content (title, body)
) ENGINE = InnoDB;

-- Natural language mode search
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('database optimization');

-- Boolean mode search
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -PostgreSQL' IN BOOLEAN MODE);
```

### Multi-Valued Indexes

MySQL 8.0.17 introduced multi-valued indexes for JSON arrays:

```sql
-- Table with JSON column and multi-valued index
CREATE TABLE products (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    tags JSON,
    INDEX idx_tags ((CAST(tags AS ARRAY)))
) ENGINE = InnoDB;

-- Query using multi-valued index
SELECT * FROM products
WHERE JSON_CONTAINS(tags, '"electronics"');
```

## Advanced Indexing Features

Both PostgreSQL and MySQL offer advanced indexing features that can significantly improve query performance in specific scenarios.

### Functional Indexes

Both systems support indexing expressions rather than just columns:

**PostgreSQL:**

```sql
-- Expression index
CREATE INDEX idx_email_domain ON users ((split_part(email, '@', 2)));
```

**MySQL:**

```sql
-- Functional index
CREATE INDEX idx_upper_name ON customers ((UPPER(name)));
```

### Covering Indexes

Both systems support covering indexes, which can satisfy a query without accessing the table:

**PostgreSQL:**

```sql
-- Covering index with included columns
CREATE INDEX idx_orders_customer ON orders (customer_id) INCLUDE (order_date, status);
```

**MySQL:**

```sql
-- Index that covers a query
CREATE INDEX idx_covering ON customers (id, name, email);
```

### Partial Indexes (PostgreSQL Only)

PostgreSQL supports partial indexes that only index a subset of rows:

```sql
-- Partial index for active users only
CREATE INDEX idx_active_users ON users (username) WHERE active = true;
```

### Invisible Indexes (MySQL Only)

MySQL 8.0 introduced invisible indexes for testing index removal without actually dropping the index:

```sql
-- Create invisible index
CREATE INDEX idx_test ON orders (status) INVISIBLE;

-- Make existing index invisible
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;

-- Temporarily use invisible indexes for a query
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
```

## Index Maintenance

Proper index maintenance is crucial for maintaining optimal database performance.

### PostgreSQL

PostgreSQL provides several tools for index maintenance:

```sql
-- Rebuild an index
REINDEX INDEX idx_name;

-- Rebuild all indexes on a table
REINDEX TABLE table_name;

-- Update statistics
ANALYZE table_name;

-- Monitor index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'table_name';
```

### MySQL

MySQL integrates index maintenance with table operations:

```sql
-- Update statistics
ANALYZE TABLE customers;

-- Rebuild table and indexes
OPTIMIZE TABLE customers;

-- View index information
SHOW INDEX FROM customers;

-- Query index metadata
SELECT * FROM information_schema.STATISTICS
WHERE table_schema = 'mydb' AND table_name = 'customers';
```

## Best Practices for Index Creation and Management

### PostgreSQL

1. **Use CONCURRENTLY for production environments**:

   ```sql
   CREATE INDEX CONCURRENTLY idx_new_index ON large_table(column);
   ```

   This avoids locking the table for writes during index creation.

1. **Monitor index usage**:

   ```sql
   SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
   FROM pg_stat_user_indexes
   WHERE schemaname = 'public'
   ORDER BY idx_scan DESC;
   ```

   Regularly check which indexes are being used and which are not.

1. **Consider partial indexes for large tables with filtered queries**:

   ```sql
   CREATE INDEX idx_recent_orders ON orders(order_date)
   WHERE order_date > CURRENT_DATE - INTERVAL '3 months';
   ```

   This creates smaller, more efficient indexes.

1. **Use expression indexes for queries with functions**:
   ```sql
   CREATE INDEX idx_lower_email ON users(lower(email));
   ```
   This allows the optimizer to use the index for queries with `lower(email)`.

### MySQL

1. **Use ALGORITHM=INPLACE for online operations**:

   ```sql
   ALTER TABLE large_table ADD INDEX idx_new_index (column), ALGORITHM=INPLACE;
   ```

   This minimizes locking during index creation.

1. **Monitor index usage**:

   ```sql
   SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
   WHERE object_schema = 'your_database'
   ORDER BY count_star DESC;
   ```

   Check which indexes are being used and which are not.

1. **Use invisible indexes for testing**:

   ```sql
   CREATE INDEX idx_test ON orders(status) INVISIBLE;
   ```

   Test the impact of an index before making it visible to the optimizer.

1. **Consider prefix indexes for string columns**:
   ```sql
   CREATE INDEX idx_description ON products(description(50));
   ```
   This creates smaller indexes while maintaining good selectivity.

## Comparison Table: PostgreSQL 17 vs MySQL 8

| Feature                       | PostgreSQL 17             | MySQL 8                 |
| ----------------------------- | ------------------------- | ----------------------- |
| **B-tree indexes**            | Yes (default)             | Yes (default)           |
| **Hash indexes**              | Yes (all storage engines) | Limited (MEMORY tables) |
| **Spatial indexes**           | Yes (GiST)                | Yes (R-tree)            |
| **Full-text indexes**         | Via extensions            | Built-in                |
| **GiST indexes**              | Yes                       | No                      |
| **SP-GiST indexes**           | Yes                       | No                      |
| **GIN indexes**               | Yes                       | No                      |
| **BRIN indexes**              | Yes                       | No                      |
| **Multi-valued indexes**      | No                        | Yes (for JSON)          |
| **Functional indexes**        | Yes                       | Yes                     |
| **Partial indexes**           | Yes                       | No                      |
| **Covering indexes**          | Yes (explicit INCLUDE)    | Yes (implicit)          |
| **Invisible indexes**         | No                        | Yes                     |
| **Descending indexes**        | Yes                       | Yes                     |
| **Concurrent index creation** | Yes                       | No                      |
| **Index-only scans**          | Yes                       | Yes                     |
| **Parallel index scans**      | Yes                       | Limited                 |

## Conclusion and Recommendations

Both PostgreSQL 17 and MySQL 8 offer robust indexing capabilities that can significantly improve database performance when properly utilized. Choose PostgreSQL if you need specialized index types, complex data types, partial indexes, or concurrent index creation in production environments. Choose MySQL if you prefer built-in full-text search, invisible indexes for testing, multi-valued indexes for JSON data, or simpler configuration and management.

For most general-purpose applications, either system can provide excellent performance with proper index design. The key is understanding the specific query patterns of your application and selecting the appropriate index types and configurations to optimize those patterns.

Remember that indexes come with maintenance overhead and storage costs, so it's important to balance the benefits of faster queries against these costs. Regularly monitor index usage and performance to ensure your indexing strategy remains optimal as your application evolves.

## Postgres vs MySQL Comparison Series

- [Overall comparison](/blog/postgres-vs-mysql/)
- [DDL Transaction Difference](/blog/postgres-vs-mysql-ddl-transaction/)
- [JSON Support](/blog/postgres-vs-mysql-json-support/)
- [Online Index Creation](/blog/postgres-vs-mysql-online-index-creation/)

## References

**PostgreSQL Documentation**

1. [PostgreSQL 17 Index Types](https://www.postgresql.org/docs/current/indexes-types.html)
1. [PostgreSQL CREATE INDEX](https://www.postgresql.org/docs/current/sql-createindex.html)
1. [PostgreSQL Index-Only Scans](https://www.postgresql.org/docs/current/indexes-index-only-scans.html)
1. [PostgreSQL Partial Indexes](https://www.postgresql.org/docs/current/indexes-partial.html)
1. [PostgreSQL GiST and GIN Index Types](https://www.postgresql.org/docs/current/textsearch-indexes.html)
1. [PostgreSQL BRIN Indexes](https://www.postgresql.org/docs/current/brin-intro.html)

**MySQL Documentation**

1. [MySQL 8.0 CREATE INDEX Statement](https://dev.mysql.com/doc/refman/8.0/en/create-index.html)
1. [MySQL 8.0 How MySQL Uses Indexes](https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html)
1. [MySQL 8.0 SPATIAL Index Optimization](https://dev.mysql.com/doc/refman/8.0/en/spatial-index-optimization.html)
1. [MySQL 8.0 Full-Text Search Functions](https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html)
1. [MySQL 8.0 Invisible Indexes](https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html)
1. [MySQL 8.0 Descending Indexes](https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html)
1. [MySQL 8.0 Multi-Valued Indexes](https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued)



JSON has become an essential format for storing and exchanging semi-structured data. Both PostgreSQL and MySQL have embraced JSON support. This article compares JSON support in their respective latest GA release PostgreSQL 17 and MySQL 8, examining their approaches to data types, functions, indexing, and performance. We'll provide code examples to illustrate key differences and help you decide which database might better suit your JSON-related needs.

## JSON Data Types and Storage

### PostgreSQL 17

PostgreSQL continues to offer two distinct JSON data types:

1. **`jsonb`** (Preferred): Stores data in a decomposed binary format. While slightly slower for input due to conversion overhead, it's significantly faster for processing since no reparsing is needed. It also supports indexing.

1. **`json`**: Stores an exact copy of the input text, preserving whitespace and the order of object keys. Each processing operation requires reparsing the text.

Additionally, PostgreSQL provides a `jsonpath` data type for efficient query mechanisms.

```sql
-- Creating JSON values in PostgreSQL 17
SELECT '{"name": "John", "age": 30}'::json;  -- json type
SELECT '{"name": "John", "age": 30}'::jsonb; -- jsonb type
```

### MySQL 8

MySQL implements a single `JSON` data type that's conceptually similar to PostgreSQL's `jsonb`. It stores JSON documents in an optimized binary format that enables quick access to elements without parsing the entire document.

```sql
-- Creating JSON values in MySQL 8
SELECT JSON_OBJECT('name', 'John', 'age', 30);
-- Or directly
SELECT CAST('{"name": "John", "age": 30}' AS JSON);
```

Both databases automatically validate JSON documents, ensuring only valid JSON can be stored in JSON columns.

## New Features and Enhancements

### PostgreSQL 17

`JSON_TABLE()` function is the most notable addition, which converts JSON data into a tabular format. This makes it easier to work with JSON data in SQL contexts.

```sql
-- PostgreSQL 17's new JSON_TABLE function
SELECT *
FROM JSON_TABLE(
    '{"users": [{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]}',
    '$.users[*]' COLUMNS(
        name TEXT PATH '$.name',
        age INT PATH '$.age'
    )
) AS jt;
```

### MySQL 8

MySQL 8 has had several years to refine its JSON support:

1. **JSON_TABLE Function**: MySQL 8 has offered the `JSON_TABLE()` function since its early releases, allowing for conversion between JSON and relational formats.

   ```sql
   -- MySQL 8's JSON_TABLE function
   SELECT *
   FROM JSON_TABLE(
       '{"users": [{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]}',
       '$.users[*]' COLUMNS(
           name VARCHAR(100) PATH '$.name',
           age INT PATH '$.age'
       )
   ) AS jt;
   ```

1. **JSON Schema Validation**: MySQL 8 provides built-in functions for validating JSON against JSON Schema.

   ```sql
   -- MySQL 8's JSON schema validation
   SELECT JSON_SCHEMA_VALID(
       '{"type": "object", "properties": {"name": {"type": "string"}, "age": {"type": "integer"}}}',
       '{"name": "John", "age": 30}'
   );
   ```

1. **Multi-valued Indexing**: Since version 8.0.17, MySQL has supported multi-valued indexes on JSON arrays.

## Functions and Operators

### PostgreSQL 17

PostgreSQL provides a rich set of operators for JSON manipulation:

- `->`: Extract JSON object field or array element (returns JSON)
- `->>`: Extract JSON object field or array element as text
- `#>`: Extract JSON sub-object at specified path
- `#>>`: Extract JSON sub-object at specified path as text

For `jsonb` type, additional operators are available:

- `@>`: Contains (does the first JSON value contain the second?)
- `<@`: Contained by (is the first JSON value contained in the second?)
- `?`: Does the string exist as a top-level key?
- `?|`: Do any of these strings exist as top-level keys?
- `?&`: Do all of these strings exist as top-level keys?
- `||`: Concatenate JSON values

```sql
-- PostgreSQL 17 JSON operators
SELECT '{"user": {"name": "John", "email": "john@example.com"}}'::json -> 'user' ->> 'email';
-- Result: john@example.com

-- Check containment (jsonb only)
SELECT '{"name": "John", "tags": ["developer", "postgresql"]}'::jsonb @> '{"tags": ["postgresql"]}';
-- Result: true
```

### MySQL 8

MySQL uses functions for most JSON operations, though it also supports the `->` and `->>` operators:

- `JSON_EXTRACT()`: Extract data from JSON document
- `JSON_SET()`, `JSON_INSERT()`, `JSON_REPLACE()`: Modify JSON documents
- `JSON_CONTAINS()`, `JSON_CONTAINS_PATH()`: Check for values or paths
- `JSON_SEARCH()`: Find paths to specific values
- `JSON_MERGE_PATCH()`, `JSON_MERGE_PRESERVE()`: Combine JSON documents

```sql
-- MySQL 8 JSON functions
SELECT JSON_EXTRACT('{"user": {"name": "John", "email": "john@example.com"}}', '$.user.email');
-- Result: "john@example.com"

-- Using the -> operator (same as JSON_EXTRACT)
SELECT '{"user": {"name": "John", "email": "john@example.com"}}' -> '$.user.email';
-- Result: "john@example.com"

-- Using the ->> operator (same as JSON_UNQUOTE(JSON_EXTRACT(...)))
SELECT '{"user": {"name": "John", "email": "john@example.com"}}' ->> '$.user.email';
-- Result: john@example.com
```

## JSON Path Expressions

Both databases support JSON path expressions, but with different syntax and capabilities.

### PostgreSQL 17

PostgreSQL uses a path expression syntax with dots for object navigation and brackets for array access. In PostgreSQL 17, the SQL/JSON path language has been enhanced:

```sql
-- PostgreSQL 17 path syntax
SELECT '{"users": [{"name": "John"}, {"name": "Jane"}]}'::json #> '{users,1,name}';
-- Result: "Jane"

-- Using jsonpath for complex queries (enhanced in PostgreSQL 17)
SELECT jsonb_path_query(
    '{"users": [{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]}'::jsonb,
    '$.users[*] ? (@.age > 28).name'
);
-- Result: "John"
```

### MySQL 8

MySQL uses a dot notation similar to JavaScript for object properties and brackets for array indices:

```sql
-- MySQL 8 path syntax
SELECT JSON_EXTRACT('{"users": [{"name": "John"}, {"name": "Jane"}]}', '$.users[1].name');
-- Result: "Jane"

-- Using path expressions with conditions
SELECT JSON_SEARCH(
    '{"users": [{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]}',
    'one',
    'John'
);
-- Result: "$.users[0].name"
```

## Indexing JSON Data

### PostgreSQL 17

PostgreSQL's `jsonb` type supports several index types:

1. **GIN (Generalized Inverted Index)**: Ideal for checking containment and existence operations.

   ```sql
   -- Create a GIN index for all keys and values
   CREATE INDEX idx_data ON table_name USING GIN (data_column);

   -- Create a more compact GIN index for containment operations
   CREATE INDEX idx_data_path ON table_name USING GIN (data_column jsonb_path_ops);
   ```

1. **Functional indexes**: For specific JSON paths.

   ```sql
   -- Create an index on a specific JSON path
   CREATE INDEX idx_name ON table_name ((data_column->>'name'));
   ```

### MySQL 8

MySQL doesn't directly index JSON columns. Instead, it offers:

1. **Generated columns with indexes**: Extract values from JSON into generated columns that can be indexed.

   ```sql
   -- Create a virtual column and index it
   ALTER TABLE users ADD COLUMN name VARCHAR(100)
       GENERATED ALWAYS AS (data->>'$.name') STORED;

   CREATE INDEX idx_name ON users(name);
   ```

1. **Multi-valued indexes**: For JSON arrays (InnoDB, since MySQL 8.0.17).

   ```sql
   -- Create a multi-valued index on a JSON array
   CREATE INDEX idx_tags ON users((CAST(data->'$.tags' AS JSON) ARRAY));
   ```

## Performance Considerations

### PostgreSQL 17

- `jsonb` is optimized for processing but has conversion overhead during insertion
- GIN indexes provide efficient querying for complex JSON data
- Partial updates of JSON values are possible
- PostgreSQL 17 includes various query performance improvements, including for sequential reads

### MySQL 8

- Optimized binary storage format for quick element access
- Partial, in-place updates of JSON columns reduce overhead
- Compact binary log format for partial JSON updates
- Improved parsing and indexing performance in MySQL 8.0

## Code Example: Working with Nested JSON

Let's compare how both databases handle a common task: extracting and filtering nested JSON data.

### PostgreSQL 17

```sql
-- Create a table with JSON data
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- Insert sample data
INSERT INTO products (data) VALUES
    ('{"name": "Laptop", "specs": {"cpu": "i7", "ram": 16, "storage": 512}}'),
    ('{"name": "Smartphone", "specs": {"cpu": "A15", "ram": 8, "storage": 256}}'),
    ('{"name": "Tablet", "specs": {"cpu": "M1", "ram": 8, "storage": 128}}');

-- Query for products with at least 8GB RAM and 256GB storage
SELECT data->>'name' AS product_name
FROM products
WHERE (data->'specs'->>'ram')::int >= 8
  AND (data->'specs'->>'storage')::int >= 256;

-- Result:
-- product_name
-- -----------
-- Laptop
-- Smartphone

-- Using PostgreSQL 17's JSON_TABLE function
SELECT jt.product_name, jt.ram, jt.storage
FROM products,
JSON_TABLE(
    data,
    '$' COLUMNS(
        product_name TEXT PATH '$.name',
        ram INT PATH '$.specs.ram',
        storage INT PATH '$.specs.storage'
    )
) AS jt
WHERE jt.ram >= 8 AND jt.storage >= 256;
```

### MySQL 8

```sql
-- Create a table with JSON data
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);

-- Insert sample data
INSERT INTO products (data) VALUES
    (JSON_OBJECT('name', 'Laptop', 'specs', JSON_OBJECT('cpu', 'i7', 'ram', 16, 'storage', 512))),
    (JSON_OBJECT('name', 'Smartphone', 'specs', JSON_OBJECT('cpu', 'A15', 'ram', 8, 'storage', 256))),
    (JSON_OBJECT('name', 'Tablet', 'specs', JSON_OBJECT('cpu', 'M1', 'ram', 8, 'storage', 128)));

-- Query for products with at least 8GB RAM and 256GB storage
SELECT data->>'$.name' AS product_name
FROM products
WHERE CAST(data->'$.specs.ram' AS UNSIGNED) >= 8
  AND CAST(data->'$.specs.storage' AS UNSIGNED) >= 256;

-- Result:
-- product_name
-- -----------
-- Laptop
-- Smartphone

-- Using MySQL 8's JSON_TABLE function
SELECT jt.product_name, jt.ram, jt.storage
FROM products,
JSON_TABLE(
    data,
    '$' COLUMNS(
        product_name VARCHAR(100) PATH '$.name',
        ram INT PATH '$.specs.ram',
        storage INT PATH '$.specs.storage'
    )
) AS jt
WHERE jt.ram >= 8 AND jt.storage >= 256;
```

## Conclusion

| Feature                      | PostgreSQL 17                    | MySQL 8                                 |
| ---------------------------- | -------------------------------- | --------------------------------------- |
| **JSON Data Types**          | `jsonb` and `json`               | Single `JSON` type                      |
| **Format Preservation**      | Yes (with `json` type)           | No                                      |
| **Binary Storage**           | Yes (with `jsonb` type)          | Yes                                     |
| **Automatic Validation**     | Yes                              | Yes                                     |
| **JSON_TABLE Function**      | Yes (new in PostgreSQL 17)       | Yes                                     |
| **Indexing Support**         | GIN indexes, functional indexes  | Generated columns, multi-valued indexes |
| **Containment Operators**    | Yes (`@>`, `<@`)                 | No (uses functions instead)             |
| **Path Expression**          | Yes (dot and bracket notation)   | Yes (dot and bracket notation)          |
| **Schema Validation**        | No built-in support              | Yes (`JSON_SCHEMA_VALID()`)             |
| **Partial Updates**          | Yes                              | Yes                                     |
| **Array Membership Testing** | Yes (`?`, `?\|`, `?&` operators) | Yes (`MEMBER OF()` operator)            |
| **Pretty Printing**          | No built-in function             | Yes (`JSON_PRETTY()`)                   |

PostgreSQL 17 offers broader JSON functionality. If your workloads rely heavily on JSON and demand advanced querying, its `jsonb` type with GIN indexes is often the better fit. MySQL, however, is steadily improving and already provides a growing set of built-in JSON functions.

## Postgres vs MySQL Comparison Series

- [Overall comparison](/blog/postgres-vs-mysql/)
- [DDL Transaction Difference](/blog/postgres-vs-mysql-ddl-transaction/)
- [Indexing Options](/blog/postgres-vs-mysql-indexing-options/)
- [Online Index Creation](/blog/postgres-vs-mysql-online-index-creation/)

## References

1. PostgreSQL Documentation. (2024). "JSON Types." Retrieved from https://www.postgresql.org/docs/current/datatype-json.html

1. PostgreSQL Documentation. (2024). "JSON Functions and Operators." Retrieved from https://www.postgresql.org/docs/current/functions-json.html

1. MySQL Documentation. (2024). "The JSON Data Type." Retrieved from https://dev.mysql.com/doc/refman/8.0/en/json.html

1. MySQL Documentation. (2024). "JSON Function Reference." Retrieved from https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html

1. PostgreSQL Documentation. (2024). "PostgreSQL 17.0 Release Notes." Retrieved from https://www.postgresql.org/docs/release/17.0/

1. Stokes, D. (2023). "JSON Data Improvements in MySQL 8.0." Oracle MySQL Summit 2023. Retrieved from https://downloads.mysql.com/events/mysql-summit-2023/Oracle_MySQL_Summit_2023_JSON.pdf


The
Bytebase Blog

Explore the standard for database governance

Solutions & Platform

Resources

Change Management

Access & Security

AI & Agents

Comparisons

Company

Legal

The Bytebase Blog

Explore the standard for database governance

The
Bytebase Blog