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

Managing Database Passwords and Secrets: Best Practices

A guide to audit logging in MySQL — from the general query log to audit plugins, binlog-based CDC, and Bytebase.

MySQL Audit Logging Guide

Every critical asset has its system of record, except the database. Bytebase is the one. The standard for database development.

The Standard for Database Development

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

The static data masking tools that produce safe non-production data, Delphix, Tonic.ai, and greenmask compared, plus how to roll your own when no tool fits.

Static Data Masking Tools (and How to Roll Your Own)

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?

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

Notes on the MySQL 9.7 LTS release

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

What is Database Access Control (DAC)?

Practical guide to dynamic data masking: five best practices, a vendor comparison, an end-to-end walkthrough, and the compliance specifics auditors ask about.

Dynamic Data Masking Best Practices

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

How Bytebase Handles Audit Logging

PostgreSQL 19 features I'm excited about: planner advisor framework, DDL extraction, online REPACK, pg_stat_recovery, parallel autovacuum, sequence sync, and 64-bit MultiXact.

PostgreSQL 19 features I'm excited about

Bytebase is HIPAA-compliant — independent verification that our administrative, physical, and technical safeguards meet the HIPAA Security Rule for handling Protected Health Information.

HIPAA Compliance

PAM tools gate connections; databases also need SQL review, schema change workflow, and statement-level approval. Why most teams use PAM with Bytebase.

Bytebase vs. PAM: Why Database Teams Need More Than Privileged Access Management

What SOC 2 compliance audits actually want from your database audit log: not 'logging enabled' but evidence your auditor will accept. Lessons from going through our own audit.

SOC 2 Audit Log Requirements: Lessons From Our Own Audit

Bytebase vs. Atlas compared across migration approach, CI/CD, access control, and pricing. Updated for Atlas v1.1 and Bytebase 3.16.

Bytebase vs. Atlas: a side-by-side comparison for database schema migration

Audit-surviving database access control: statement-level enforcement across machine, human, and AI agent workloads, with the mistakes that fail SOC 2.

Database Access Control (DAC) Best Practices

SOX Section 404 requires IT general controls over database changes. Learn the ITGC domains, six audit questions teams fail, and how to build SOX-ready controls

Database Change Management for SOX Compliance

TRUNCATE and DELETE both remove rows, but they differ in speed, rollback behavior, and locking. Compare how they work in PostgreSQL, MySQL, and SQL Server.

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

Six database design patterns for production systems: normalization, star schema, EAV, polymorphic associations, multi-tenancy, and anti-patterns.

Database Design Patterns Every Developer Should Know

pgvector adds vector storage and similarity search to PostgreSQL. Learn how to install it, query embeddings, build indexes, and decide when it is enough.

pgvector: How to Add Vector Search to PostgreSQL

Upsert inserts or updates a row based on key conflicts. Learn MySQL INSERT ON DUPLICATE KEY UPDATE, PostgreSQL ON CONFLICT, and SQL Server MERGE.

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

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

From Schema as Code to Schema as Context

Learn how to find and fix slow MySQL queries using the slow query log, EXPLAIN, index tuning, and query rewrites, with practical examples for each.

How to Fix Slow MySQL Queries: A Practical Guide

A detailed comparison of Convex and Supabase covering architecture, real-time capabilities, data model, pricing, and schema management to help you choose.

Convex vs Supabase: Which Backend Platform Should You Choose?

Blue-green deployments work cleanly for stateless apps. Databases are stateful. Learn what makes database blue-green deployments hard and how to do them safely.

Database Blue-Green Deployment: A Practical Guide

The 10 best data management tools in 2026 — database GUI clients, schema migration tools, data pipelines, and analytics platforms compared with pricing.

Top 10 Data Management Tools in 2026

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

How to Govern AI Agent Database Access

Postgres-only SQL editors are designed specifically for Postgres databases, providing a more focused and efficient experience for Postgres users. In this post, we are taking a look at the top Postgres-only SQL editors in 2026.

Enterprise Text-to-SQL: Context, Evaluation, and Governance

Database deployment tools for devops teams in 2026.

Database Deployment Tools for DevOps Teams in 2026

Schema change detection and notification are essential for data pipelines. This article explains why a simple column rename can break your data pipeline and how to prevent it.

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

A comprehensive guide to understanding and analyzing Postgres query plans.

Postgres Query Plan: How to Read and Fix Performance Issues

This article explores the anatomy of Postgres permissions ERROR 42501.

The Anatomy of Postgres Permission ERROR 42501

Postgres Logs Explained: A Practical Guide for Production

Postgres 19 widens MultiXactOffset to 64 bits, eliminating the hidden 4 billion member limit that caused catastrophic outages for high-concurrency workloads.

Postgres 19 Feature Preview: 64-bit MultiXactOffset

A guide to MySQL schema migration best practice.

MySQL Schema Migration Best Practice

Postgres 19 introduces automatic sequence synchronization for logical replication, eliminating manual tracking and making major version upgrades and failover significantly easier.

Postgres 19 Feature Preview: Sequence Synchronization for Logical Replication

A guide to multi-environment deployments in databases.

Database Multi-Environment Deployments - The Challenges and Patterns

A guide to open source sensitive data discovery tools, from NLP libraries like spaCy and Microsoft Presidio to multi-source scanners like Hawk-Eye and full metadata platforms like OpenMetadata.

Top Open Source Sensitive Data Discovery Tools in 2026

Database audit logging has two layers: infrastructure (provider-managed) and workflow (database activity). What each captures, and why compliance requires both.

Database Audit Logging: Two Layers, One Trail

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

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

Learn why requiring a primary key is important and how the "Require Primary Key" review rule protects your production database.

Require Primary Key: A SQL Review Rule Explained

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

Enforce NOT VALID in CHECK: A SQL Review Rule Explained

Postgres Audit Logging Guide

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

Prohibit CASCADE: A SQL Review Rule Explained

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

How to Migrate from Supabase to AWS

Top open-source Postgres backup solutions for 2026.

What is Enterprise Database Management (EDM)?

What is Postgres Transaction ID (XID) Wraparound and how to monitor and prevent it.

Postgres Transaction ID (XID) Wraparound

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

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

A practical guide to shipping features with PostgreSQL, covering my day-to-day development workflow, the tools I actually use, and real-world examples

How I Ship Features with Postgres

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

Database Compliance for LGPD: Implications and Best Practices

Explain what is Postgres LATERAL JOIN and how to use it.

What is Postgres LATERAL JOIN?

Introduction to MySQL Online DDL and its three algorithms: INSTANT, INPLACE, and COPY

MySQL Online DDL: A Practical Guide

List of open source postgres connection poolers

Open Source Postgres Connection Pooler

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

SQL Table Naming Dilemma: Singular vs. Plural

PostgreSQL 18 features that change how application developers write and ship code.

What's New in PostgreSQL 18: A Developer's Perspective

Compare Snowflake Dynamic Data Masking with database views and Bytebase fleet-wide masking on edition gate, policy management, and audit trail.

Snowflake Dynamic Data Masking (DDM) and Alternatives

Top open-source Postgres authentication solutions for 2025.

Postgres Case Sensitivity Explained

A comprehensive guide to understanding pg_database_owner in Postgres

What is pg_database_owner in Postgres

Which operation causes a Postgres table rewrite

Which Postgres Operation causes a table rewrite

An engineering perspective to evaluate Postgres row-level-security footguns

Postgres Row-Level Security Footguns

An engineering perspective to evaluate Postgres rollback strategies

Postgres Rollback Explained

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

Postgres Schema Migration without Downtime

A checklist of gh-ost limitations and potential gotchas

gh-ost Limitations You Need to Know Before Production

5 quick wins for application developers to improve Postgres reliability and make both their own and DBA/infrastructure teams' lives easier.

5 Postgres Production Tips for Application Developers

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

How to Use Postgres CREATE INDEX CONCURRENTLY

An extensive comparison between Supabase and MongoDB on performance, usability, operability, ecosystem and more.

Supabase vs. MongoDB: a Complete Comparison in 2025

When evaluating database management tools, DataGrip and Bytebase represent two different philosophies: individual developer productivity versus team collaboration and governance.

Bytebase vs. DataGrip: a side-by-side comparison for database management

Supabase pricing starts free. We break down real costs for Pro ($25/mo) and Team ($599/mo) plans, explain overages, and compare each service head-to-head with AWS.

Supabase vs AWS: Feature and Pricing Comparison (2026)

Is Supabase free? Compare Supabase pricing vs AWS RDS and Aurora across free tier, Pro, and production plans. Includes Supabase free tier limits, storage costs, and compute pricing for 2026.

Supabase Pricing vs AWS RDS: Is Supabase Free? Full Cost Comparison (2026)

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

Database Security Lifecycle Management

Areas where MySQL shines and Postgres falls short.

Features I Wish Postgres Had but MySQL Already Has

Areas where PostgreSQL shines and MySQL falls short.

Features I Wish MySQL Had but Postgres Already Has

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

HIPAA Data Security and Retention Requirements

Compare the online index creation difference between Postgres and MySQL

Postgres vs. MySQL: Online Index Creation

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

SOC 2 Data Security and Retention Requirements

Compare the different indexing options between Postgres and MySQL

Postgres vs. MySQL: Indexing Options

Compare the JSON support difference between Postgres and MySQL

Postgres vs. MySQL: JSON Support

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

Postgres Row-Level Security (RLS) Limitations and Alternatives

Evaluate several database schema diagram tools, and help you to choose the right one for 2025

Top 4 Free, Open Source Database Schema Diagram Tools to Visualize Database Easier in 2025

An overview of the most popular text-to-SQL AI tools in 2026, including DataGrip, TablePlus, SQL Chat, Vanna.ai, SQLAI.ai, Postgres.new, and DBHub MCP server. Compare features and capabilities to find the best SQL AI assistant for your workflow.

Top 5 Text-to-SQL Query Tools in 2026

Compare the DDL transaction difference between Postgres and MySQL

Postgres vs. MySQL: DDL Transaction Difference

Evaluate several TypeScript ORMs, and help you to choose the right one for 2025

Top TypeScript ORM 2025

Evaluate Prisma and TypeORM, and help you to choose the right TypeScript ORM for 2025

Prisma vs TypeORM: The Better TypeScript ORM in 2025

PostgreSQL 18 features and what they change for running production databases.

What's New in PostgreSQL 18: A DBA's Perspective

Explore alternatives to Amazon RDS, including 

RDS Alternatives in 2025

An extensive comparison between DynamoDB and Cassandra on performance, usability, operability, ecosystem and more.

DynamoDB vs. Cassandra: a Complete Comparison in 2025

An extensive comparison between DynamoDB and MongoDB on performance, usability, operability, ecosystem and more.

RDS vs. DynamoDB: a Complete Comparison in 2025

How gh-ost and pt-online-schema-change compare on architecture, cut-over, foreign keys, and runtime control — for teams choosing a MySQL online schema migration tool.

gh-ost vs pt-online-schema-change

GitHub and Bitbucket are both tools for code collaboration. This article compares the features and pricing of GitHub and Bitbucket.

GitHub vs Bitbucket:a Complete Comparison in 2025

Drizzle ORM vs Prisma comparison: type safety, performance, bundle size, serverless support, migrations, and ecosystem. Find out which TypeScript ORM is right for your project in 2026.

Drizzle ORM vs Prisma: Which TypeScript ORM Should You Use in 2026?

An engineering perspective to evaluate Google AlloyDB and Cloud SQL, and help you to choose the right Google Cloud database for 2025

AlloyDB vs. Cloud SQL: engineering guide to choose the right Google Cloud database for 2025

Understanding DynamoDB Pricing (2025)

A simple comparison of AWS Aurora versus RDS pricing in 2025, helping you understand key differences and choose the right database service for your needs and budget.

AWS Aurora vs. RDS Pricing: A Detailed Comparison 2025


PostgreSQL's case sensitivity rules can surprise even experienced developers. Here's the concise, no-gotchas guide.

## 1. Identifiers (table/column names)

* **Unquoted identifiers are folded to lowercase.**

  ```sql
  CREATE TABLE Customer (ID int, UserName varchar(50));
  -- Actually created as: customer(id, username)
  
  SELECT * FROM customer;   -- ✅ works
  SELECT * FROM Customer;   -- ✅ works (folded to lowercase)
  ```

* **Quoted identifiers preserve case and must be referenced exactly.**

  ```sql
  CREATE TABLE "Customer" ("ID" int, "UserName" varchar(50));
  
  SELECT * FROM "Customer";           -- ✅ works
  SELECT * FROM customer;             -- ❌ ERROR: relation "customer" does not exist
  SELECT id FROM "Customer";          -- ❌ ERROR: column "id" does not exist
  ```

**Best practice:** avoid quoted identifiers. Use lowercase `snake_case` (e.g., `order_items`, `created_at`).

## 2. Strings (data values)

* **String comparisons are case-sensitive by default.**

  ```sql
  SELECT 'abc' = 'ABC';  -- false
  INSERT INTO users (username) VALUES ('JohnDoe'), ('janedoe');
  SELECT * FROM users WHERE username = 'johndoe';  -- 0 rows
  ```

* **Case-insensitive matching**

  * Functions and operators:

    ```sql
    SELECT * FROM users WHERE lower(username) = 'johndoe';  -- ✅ finds 1 row
    SELECT * FROM users WHERE username ILIKE 'john%';       -- ✅ case-insensitive LIKE
    SELECT * FROM users WHERE username ~* '^john';          -- ✅ case-insensitive regex
    ```

  * `citext` extension:

    ```sql
    CREATE EXTENSION IF NOT EXISTS citext;
    CREATE TABLE users (id SERIAL, username CITEXT UNIQUE);
    SELECT * FROM users WHERE username = 'johndoe';  -- ✅ automatically case-insensitive
    ```

  * Nondeterministic collations (PostgreSQL 12+):

    ```sql
    CREATE COLLATION case_insensitive (
        provider = icu, 
        locale = 'und-u-ks-level2', 
        deterministic = false
    );
    CREATE TABLE users (username TEXT COLLATE "case_insensitive");
    ```

## 3. Indexing for case-insensitive search

* Functional index to avoid full scans:

  ```sql
  CREATE INDEX users_name_lower_idx ON users (lower(name));
  -- Query must match the expression:
  SELECT * FROM users WHERE lower(name) = lower($1);
  ```

* For prefix searches:

  ```sql
  CREATE INDEX users_name_lower_like_idx ON users (lower(name) text_pattern_ops);
  SELECT * FROM users WHERE lower(name) LIKE lower($1) || '%';
  ```

## 4. ORMs & migrations: common pitfalls

* Some ORMs emit **quoted identifiers**, locking you into exact casing everywhere. Prefer ORM settings that generate unquoted, lowercase names.
* Mixing quoted and unquoted names across migrations leads to "why can't it find my table?" bugs — standardize on lowercase, unquoted schema objects.

```sql
-- Wrong: Mixing quoted and unquoted
CREATE TABLE user_accounts (user_id SERIAL);
ALTER TABLE "User_Accounts" ADD COLUMN email VARCHAR(100);  -- ❌ Fails

-- Correct: Consistent unquoted
ALTER TABLE user_accounts ADD COLUMN email VARCHAR(100);    -- ✅ Works
```

## Quick Rules of Thumb

1. **Schema:** lowercase + unquoted identifiers, always.
2. **Search:** use `ILIKE`, `lower()` with functional indexes, or `citext`.
3. **Avoid quoted names** unless you have a compelling reason.
4. **ORMs:** configure to generate lowercase, unquoted schema objects.

That's it — you'll stay consistent, avoid case traps, and keep queries fast.


PostgreSQL 14 (released September 30, 2021) introduced a powerful predefined role: **`pg_database_owner`**. This role changes how database ownership and privileges are managed by providing a dynamic way to handle permissions for database owners.

## What is `pg_database_owner`?

`pg_database_owner` is a **built-in role** that automatically maps to the **owner of the current database**.

* You don’t grant it to users.
* Membership is **implicit and context-dependent** — whoever owns the database is the member.

This allows privileges granted to `pg_database_owner` in template databases to carry over automatically when new databases are created from them.

## Why it Exists

Two main problems it solves:

1. **Template Database Privileges**
   Before, ensuring consistent privileges across databases required manual grants. Now, you can grant to `pg_database_owner` in a template, and new DBs automatically apply them.

2. **Safer Defaults**
   Starting in PostgreSQL 15, the `public` schema is owned by `pg_database_owner`. This gives each DB owner direct control over schema access, fixing the long-standing risk of overly permissive defaults.

## Key Characteristics

| Property                           | Description                                    |
| ---------------------------------- | ---------------------------------------------- |
| **Implicit membership**            | Always maps to the database owner              |
| **Database scope**                 | Valid only in the current database             |
| **No login**                       | Cannot log in directly                         |
| **No explicit members**            | You cannot `GRANT ... TO pg_database_owner`    |
| **Template inheritance**           | Privileges granted in a template DB carry over |
| **Isolation**                      | Cannot be part of role hierarchies             |
| **Public schema ownership (v15+)** | Owns the `public` schema                       |

## Common Error

Trying to grant it explicitly produces an error:

```sql
GRANT pg_database_owner TO my_user;
ERROR: role "pg_database_owner" cannot have explicit members
```

✅ Correct usage:

* Grant privileges *to* `pg_database_owner`
* Or transfer DB ownership if you want a new effective owner

```sql
ALTER DATABASE mydb OWNER TO new_owner;
```

## Practical Examples

**Schema Privileges**

```sql
GRANT USAGE, CREATE ON SCHEMA app TO pg_database_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pg_database_owner;
```

**Template Setup**
Grant monitoring or utility functions to `pg_database_owner` in a template DB so every new database owner gets access automatically.

## Best Practices

**Do:**

* Use it in template DBs for consistent privileges
* Grant privileges *to it*, not users
* Leverage it for schema or function access

**Don’t:**

* Try to grant it to users (will fail)
* Expect cross-database scope
* Assume it has privileges by default

## Conclusion

`pg_database_owner` is a small but important change: it gives PostgreSQL a clean, dynamic way to handle database ownership and privilege inheritance. Whether you’re building multi-tenant apps or just want safer defaults, it simplifies privilege management and improves security.


Postgres is a powerful and popular open-source object-relational database system. However, like any complex system, it has its intricacies. One of the most important concepts to understand for maintaining a healthy and performant Postgres database is the idea of a **table rewrite**.

When Postgres "rewrites" a table, it creates a brand-new copy of the relation (a new **relfilenode**) and swaps it in. This process often requires approximately 2× temporary disk space and usually holds strong locks that can block traffic. A handy mental model is: _if the on-disk row layout must change or the table has to be physically moved/reordered, expect a rewrite._

This article will explore which Postgres operations cause a table rewrite, which ones don't, and the locking implications of these operations.

## Which Operations Cause a Table Rewrite?

Here is a summary of common Postgres operations and whether they cause a table rewrite:

| Operation                                          | Rewrite?             | Lock Level             | Why?                                                                      |
| -------------------------------------------------- | -------------------- | ---------------------- | ------------------------------------------------------------------------- |
| `TRUNCATE`                                         | ✅ Yes               | ACCESS EXCLUSIVE       | Deletes table content by replacing its file.                              |
| `ALTER TABLE ... SET TABLESPACE`                   | ✅ Yes               | ACCESS EXCLUSIVE       | Moves data to new storage layout or tablespace.                           |
| `ALTER TABLE ... SET LOGGED/UNLOGGED`              | ✅ Yes               | ACCESS EXCLUSIVE       | Changing persistence forces a rewrite.                                    |
| `ALTER TABLE ... ALTER COLUMN TYPE` (incompatible) | ✅ Often             | ACCESS EXCLUSIVE       | Rewrite if type change isn't binary-compatible.                           |
| `ALTER TABLE ... ALTER COLUMN TYPE` (compatible)   | ❌ No                | ACCESS EXCLUSIVE       | Binary-compatible changes (e.g., varchar(50)→varchar(100)) avoid rewrite. |
| `ALTER TABLE ... ADD COLUMN DEFAULT` (constant)    | ❌ No (Postgres 11+) | ACCESS EXCLUSIVE       | Stored as metadata only. Old rows remain untouched.                       |
| `ALTER TABLE ... ADD COLUMN DEFAULT` (volatile)    | ✅ Yes               | ACCESS EXCLUSIVE       | Each existing row needs its own computed value.                           |
| `ALTER TABLE ... DROP COLUMN`                      | ❌ No (mostly)       | ACCESS EXCLUSIVE       | Column is marked dropped in metadata; no immediate rewrite.               |
| `ALTER TABLE ... ALTER COLUMN SET NOT NULL`        | ❌ No                | ACCESS EXCLUSIVE       | Metadata-only change (with validation scan).                              |
| `ALTER TABLE ... ALTER COLUMN SET STORAGE`         | ❌ No                | ACCESS EXCLUSIVE       | Only changes future TOAST strategy; existing rows unchanged.              |
| `CREATE INDEX`                                     | ❌ No                | SHARE                  | Creates a separate index file, blocks writes but not reads.               |
| `CREATE INDEX CONCURRENTLY`                        | ❌ No                | SHARE UPDATE EXCLUSIVE | Allows reads and writes; takes longer.                                    |
| `DROP INDEX`                                       | ❌ No                | ACCESS EXCLUSIVE       | Deletes index files, leaves table file alone.                             |
| `VACUUM FULL`                                      | ✅ Yes               | ACCESS EXCLUSIVE       | Compacts table into a new file, frees all space.                          |
| `CLUSTER`                                          | ✅ Yes               | ACCESS EXCLUSIVE       | Reorders the table based on an index into a new file.                     |
| `ANALYZE`                                          | ❌ No                | SHARE UPDATE EXCLUSIVE | Collects statistics, no file change.                                      |
| `VACUUM` (regular)                                 | ❌ No                | SHARE UPDATE EXCLUSIVE | Cleans dead tuples in-place, no rewrite.                                  |

The default lock level for most `ALTER TABLE` forms is **ACCESS EXCLUSIVE**:

- ACCESS EXCLUSIVE blocks everything
- SHARE blocks writes but not reads;
- SHARE UPDATE EXCLUSIVE allows normal reads & writes.

## How to Test for a Rewrite

You can verify whether an operation causes a table rewrite by checking the **relfilenode** before and after the operation:

```sql
-- 1) Check the current relfilenode
SELECT pg_relation_filenode('public.mytable');

-- 2) Run your DDL operation

-- 3) Check again
SELECT pg_relation_filenode('public.mytable');
```

If the value changed, a rewrite occurred. This is a reliable way to test risky DDL before running it in production.

## Playbook

To avoid production issues with re-write:

**Prefer non-rewriting forms:** Use binary-compatible type changes (e.g., `varchar(50)` to `varchar(100)`) when possible.

**Schedule rewrites:** Plan operations like `VACUUM FULL`, `CLUSTER`, and `SET TABLESPACE` during maintenance windows.

## Conclusion

Understanding which Postgres operations cause table rewrites and their associated locking behavior is crucial for maintaining a healthy and performant database. Modern Postgres databases know which Postgres ops rewrite tables and what they lock. Newer versions skip many rewrites (e.g., constant DEFAULT). Follow the playbook: prefer non-rewriting changes and schedule rewrites in off-peak hours.



Postgres's Row-Level Security (RLS) is a powerful feature for implementing fine-grained access control, but it's riddled with subtle traps that can destroy performance or completely bypass security. This comprehensive guide covers all the major footguns with practical fixes and real-world examples.

---

## Performance Footguns

### 1. The LEAKPROOF Function Performance Killer

**The footgun:** Non-LEAKPROOF functions in RLS policies prevent index usage, causing catastrophic performance degradation.

**Why it happens:** Postgres must apply RLS filtering first, then non-LEAKPROOF functions, preventing the query planner from using indexes.

**Example problem:**

```sql
-- This will cause full table scans even with an index on title
CREATE POLICY user_documents ON documents
FOR SELECT
USING (owner_id = current_user_id() AND title ILIKE '%search%');
```

**The fix:**

```sql
-- Use LEAKPROOF functions or move complex logic out of policies
CREATE OR REPLACE FUNCTION safe_ilike(text, text)
RETURNS boolean
LANGUAGE sql
LEAKPROOF
AS $$ SELECT $1 ILIKE $2 $$;

CREATE POLICY user_documents ON documents
FOR SELECT
USING (owner_id = current_user_id() AND safe_ilike(title, '%search%'));
```

**Performance impact:** Queries can go from milliseconds to hours on large tables.

### 2. Complex Policy Performance Death

**The footgun:** Complex RLS policies with subqueries execute for every row, multiplying query cost exponentially.

**Bad example:**

```sql
CREATE POLICY complex_access ON orders
USING (
  EXISTS (
    SELECT 1 FROM user_permissions up
    JOIN departments d ON up.dept_id = d.id
    WHERE up.user_id = current_user_id()
    AND d.region = orders.region
  )
);
```

**Better approach:**

```sql
-- Move complexity to a LEAKPROOF function
CREATE OR REPLACE FUNCTION user_has_region_access(region_name text)
RETURNS boolean
LANGUAGE sql
LEAKPROOF
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_permissions up
    JOIN departments d ON up.dept_id = d.id
    WHERE up.user_id = current_user_id()
    AND d.region = region_name
  );
$$;

CREATE POLICY simple_access ON orders
USING (user_has_region_access(region));
```

### 3. Missing Indexes on Policy Columns

**The footgun:** Forgetting to index columns used in RLS policies forces sequential scans.

**Essential indexes:**

```sql
-- Always index columns used in policies
CREATE INDEX ON orders(tenant_id);
CREATE INDEX ON orders(owner_id);
CREATE INDEX ON orders(tenant_id, owner_id); -- composite for AND conditions
```

---

## Security Footguns

### 4. The BYPASSRLS Superuser Trap

**The footgun:** Superusers and table owners bypass RLS by default, creating false security confidence during testing.

**Why it's dangerous:** Testing with superuser accounts makes RLS appear to work when it's actually being ignored.

**The fix:**

```sql
-- Force RLS even for table owners
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;
ALTER TABLE sensitive_data FORCE ROW LEVEL SECURITY;

-- Never grant BYPASSRLS to application roles
-- Test with dedicated non-superuser accounts
```

**Testing pattern:**

```sql
-- Create proper test user
CREATE ROLE test_user;
GRANT app_user TO test_user;

-- Test as the actual application user
SET ROLE test_user;
SET app.tenant_id = 'test-tenant-id';
-- Run your tests here
RESET ROLE;
```

### 5. SECURITY DEFINER View Bypass

**The footgun:** Views are SECURITY DEFINER by default, running with creator's privileges and bypassing RLS.

**Dangerous example:**

```sql
-- Created by superuser - bypasses ALL RLS policies!
CREATE VIEW all_patient_data AS
SELECT * FROM patients;
```

**Secure approaches:**

```sql
-- Postgres 15+: Use SECURITY INVOKER
CREATE VIEW patient_data
WITH (security_invoker = true)
AS SELECT * FROM patients;

-- Older versions: Use SECURITY DEFINER with explicit RLS
CREATE OR REPLACE FUNCTION get_patient_data()
RETURNS TABLE(id uuid, name text, doctor_id uuid)
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
BEGIN
  -- Explicitly check RLS in SECURITY DEFINER functions
  IF NOT row_security_active('patients') THEN
    RAISE EXCEPTION 'Row security must be active';
  END IF;

  RETURN QUERY SELECT p.id, p.name, p.doctor_id FROM patients p;
END;
$$ LANGUAGE plpgsql;
```

### 6. Timing Side-Channel Attacks

**The footgun:** Query execution time leaks information about restricted data, allowing sophisticated attacks.

**Attack scenario:** In a multi-tenant medical database, an attacker measures query times to determine if patients with specific conditions exist in other tenants' data.

**Technical details:**

- RLS policy enforcement creates measurable timing differences
- Attackers can infer secret cardinality information
- Works even across network latency in cloud environments

**Example vulnerable query:**

```sql
-- Timing reveals if forbidden patients exist
SELECT COUNT(*) FROM patients
WHERE condition = 'rare_disease'
AND tenant_id = current_setting('app.tenant_id')::uuid;
```

**Mitigation strategies:**

1. Use data-oblivious query patterns (performance cost)
1. Add artificial delays to normalize timing
1. Limit query complexity for untrusted users
1. Monitor for suspicious timing-based query patterns

**Research note:** This attack has been demonstrated in academic research and works in real-world cloud environments.

### 7. CVE-2019-10130: Statistics Leakage

**The footgun:** [CVE-2019-10130](https://www.postgresql.org/support/security/CVE-2019-10130/). Postgres's query planner statistics could leak sampled data from RLS-protected rows.

**Technical details:**

- Query planner collects statistics by sampling column data
- Users could craft operators to read statistics containing forbidden data
- Affected Postgres 9.5-11 before May 2019 patches

**Status:** Fixed in Postgres 9.5.17, 9.6.13, 10.8, 11.3

**Lesson:** Keep Postgres updated and remember that even internal mechanisms can leak data.

---

## Implementation Footguns

### 8. Missing FORCE ROW LEVEL SECURITY

**The footgun:** Enabling RLS without FORCE allows table owners to bypass policies.

**Problem:**

```sql
-- Table owner still sees everything!
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
```

**Solution:**

```sql
-- Force RLS for everyone, including owners
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
```

### 9. USING vs WITH CHECK Confusion

**The footgun:** USING filters existing rows for SELECT/UPDATE/DELETE, but WITH CHECK validates new/modified rows for INSERT/UPDATE.

**Dangerous example:**

```sql
-- Users can INSERT data they can't see!
CREATE POLICY tenant_data ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid);
```

**Correct approach:**

```sql
CREATE POLICY tenant_isolation ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);
```

### 10. Connection Pooling Context Loss

**The footgun:** With connection pooling, `current_user` is a shared database role, useless for tenant isolation.

**Problem:**

```sql
-- Useless with PgBouncer - all connections share same user
CREATE POLICY user_data ON orders
USING (owner_id = current_user);
```

**Solution:**

```sql
-- Use application-controlled session variables
-- App sets per transaction:
SET app.user_id = 'user-uuid';
SET app.tenant_id = 'tenant-uuid';

-- Policy reads from session:
CREATE POLICY tenant_isolation ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);
```

**Security hardening:**

```sql
-- Prevent clients from setting app.* directly
REVOKE ALL ON SCHEMA pg_catalog FROM app_user;
-- Only allow trusted server code to set these variables
```

### 11. Foreign Key Failures Under RLS

**The footgun:** INSERT into child tables fails FK checks because RLS blocks SELECT on parent rows.

**Example failure:**

```sql
-- This INSERT fails even if customer exists
INSERT INTO orders (customer_id, tenant_id)
VALUES ('existing-customer-id', 'my-tenant');
-- ERROR: insert or update on table "orders" violates foreign key constraint
```

**Solution:**

```sql
-- Parent table needs SELECT policy for FK checks
CREATE POLICY customer_fk_visibility ON customers
FOR SELECT
USING (tenant_id = current_setting('app.tenant_id')::uuid);
```

### 12. Unique Constraint Cross-Tenant Leakage

**The footgun:** Global unique constraints reveal data existence across tenants.

**Problem:**

```sql
-- This reveals that email exists in ANY tenant
CREATE UNIQUE INDEX users_email_unique ON users(email);
-- INSERT fails with "duplicate key" even for other tenants
```

**Solution:**

```sql
-- Scope uniqueness to tenant
CREATE UNIQUE INDEX users_email_per_tenant
ON users(tenant_id, lower(email));
```

### 13. Silent Failures

**The footgun:** RLS failures are silent - operations fail without errors or warnings.

**Example:** An UPDATE that should modify 100 rows silently affects 0 rows due to RLS policy.

**Debugging approach:**

```sql
-- Temporarily disable RLS to test
SET row_security = off;
-- Run your query to see if RLS is the issue
SET row_security = on;

-- Or check if policies are active
SELECT row_security_active('table_name');
```

### 14. Column-Level Security Gaps

**The footgun:** RLS filters rows, not columns. Sensitive columns remain visible in allowed rows.

**Problem:**

```sql
-- Users can see SSN in their own records
SELECT * FROM users WHERE tenant_id = current_setting('app.tenant_id')::uuid;
```

**Solutions:**

```sql
-- Option 1: Column privileges
REVOKE SELECT (ssn, salary) ON users FROM app_user;

-- Option 2: Secure views
CREATE VIEW users_safe AS
SELECT id, name, email,
       CASE WHEN has_role('hr_role')
            THEN ssn
            ELSE 'XXX-XX-' || right(ssn, 4)
       END as ssn_masked
FROM users;
```

### 15. Materialized Views and Background Jobs

**The footgun:** Data copied to materialized views or exported by jobs isn't automatically protected by source table policies.

**Problems:**

```sql
-- Materialized view bypasses RLS
CREATE MATERIALIZED VIEW order_summary AS
SELECT * FROM orders; -- Contains all tenants' data!

-- Background job exports everything
COPY (SELECT * FROM orders) TO '/tmp/backup.csv';
```

**Solutions:**

```sql
-- Apply filtering in materialized views
CREATE MATERIALIZED VIEW tenant_order_summary AS
SELECT tenant_id, COUNT(*), SUM(amount)
FROM orders
GROUP BY tenant_id;

-- Use RLS-aware exports
COPY (
  SELECT * FROM orders
  WHERE tenant_id = 'specific-tenant'
) TO '/tmp/tenant_backup.csv';
```

### 16. Multiple Policy Confusion

**The footgun:** Multiple permissive policies are OR-ed together; one broad policy can override stricter ones.

**Problem:**

```sql
-- These policies are OR-ed - users get access if EITHER is true
CREATE POLICY user_own_data ON orders
USING (owner_id = current_user_id());

CREATE POLICY admin_all_data ON orders
USING (has_role('admin')); -- Oops, too broad!
```

**Solutions:**

```sql
-- Option 1: Use restrictive policies (AND-ed)
CREATE POLICY tenant_restriction ON orders
AS RESTRICTIVE
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Option 2: Combine logic in single policy
CREATE POLICY combined_access ON orders
USING (
  tenant_id = current_setting('app.tenant_id')::uuid
  AND (
    owner_id = current_user_id()
    OR has_role('tenant_admin')
  )
);
```

## Key Takeaways

1. **RLS is not a security silver bullet** - it can be bypassed through multiple vectors
1. **Performance impact is severe** - always index policy columns and keep policies simple
1. **Testing methodology is critical** - never test with superuser accounts
1. **Silent failures make debugging painful** - policies fail without warnings
1. **Context management is crucial** - use secure session variables, not `current_user`
1. **Defense in depth** - combine RLS with column privileges, secure views, and application-level controls

RLS is powerful when implemented correctly, but requires careful attention to these footguns to avoid catastrophic security and performance failures.


The
Bytebase Blog

Explore the standard for database development

Solutions & Platform

Comparisons

Legal

Resources

Company