DEV Community: Bytebase

DBHub vs Bytebase MCP: which one to point your agent at

Adela — Thu, 13 Aug 2026 13:16:33 +0000

We maintain two database MCP servers, DBHub and the one built into Bytebase. People keep asking which to use, so: DBHub when you're the only one affected, Bytebase MCP when other people are.

Note that's not the same as dev vs prod. A read-only replica only you touch is fine on DBHub; a staging database loaded with a copy of real customer data isn't. What matters is who pays if the agent does the worst thing it can do.

The rest is one difference, and everything follows from it.

Shared account vs your account

DBHub takes a connection string. In practice that's a shared service account, usually the one your app already uses, and every agent pointed at it connects as that same account.

Bytebase MCP takes an OAuth login. The agent connects as you.

So when your agent runs a DELETE at 2am, DBHub's database logs app_user, same as every other query from everyone else sharing it. You know it happened, not who it happened for, and you can't revoke it for one person without revoking it for everybody. Bytebase logs it under your account.

DBHub

One command, no server:

npx @bytebase/dbhub@latest --transport stdio --dsn "postgres://user:pass@localhost:5432/mydb"

Two tools by default, run SQL and search the schema, which keeps your context window free for the actual problem. Postgres, MySQL, MariaDB, SQL Server and SQLite, several connections in one process. You can start it read-only, cap the rows, set a query timeout.

No identity, though. Read-only is a flag on the process, so it protects you from the agent, not from whoever started the process.

Bytebase MCP

Nothing to install. If you run Bytebase it's at /mcp, and your agent inherits your account, which already has rules on it: masked columns come back ******, schema changes open a review instead of running, and every call is logged under your name.

Worth saying plainly: it inherits all your permissions. Connecting as yourself only helps if yourself isn't an admin.

	DBHub	Bytebase MCP
Connects as	A shared account	You
Reads	As stored	Masked per policy
Writes	Run immediately	Open a review
Audit	Database login records	Every call, under your account
Needs	Nothing	A running Bytebase

The catches

DBHub has no per-person access to grant or revoke, no masking, and nothing between the agent and the table if writes are on.

Bytebase MCP needs Bytebase running with an external URL, and it's HTTP only. You can't dry-run SQL, since the checks happen when the change is created. Results cap at 100 rows by default, 1,000 max, 30 second timeout.

Picking

Local Postgres, a scratch copy, a replica, read-only poking at prod: DBHub. Real customer data, masked columns, writes someone should look at first: Bytebase MCP.

Both, honestly, is the normal answer. DBHub in your client config for local work, Bytebase MCP for anything shared. What to avoid is the middle, where a connection string that happens to reach production ends up in an MCP config because it was the fastest thing to paste.

🐘🤖 Top 3 Open Source Postgres MCP Servers in 2026

Adela — Tue, 28 Jul 2026 12:35:02 +0000

At Bytebase we work on database access control all day, so pointing an AI agent at a real database is not a hypothetical for me. Picking a Postgres MCP server is easy right up until the database on the other end holds something you actually care about. What runs fine against a throwaway local database behaves very differently in production. That split, trusted local development vs. production, is the lens I use here. Most of these servers are great at the first and quietly dangerous at the second.

Building one safely is harder than it looks. Anthropic's own reference Postgres server got archived after people noticed its "read-only" mode happily accepted COMMIT; DROP SCHEMA public CASCADE;. Read-only, except when it wasn't. So I care less about the feature list and more about two questions: what can the agent actually do, and can you prove afterward what it did. The three servers below answer those very differently.

pgconsole

pgconsole is the newest of the three. It is a self-hosted Postgres console, a web SQL editor first, that also hands its connections to agents over a built-in MCP endpoint. The important part: the agent never sees a connection string. It gets a token, and every call runs through the same access rules and audit log as a human clicking around the console. The agent talks to pgconsole, and pgconsole talks to the database.

Most database tools land at one of two extremes. Heavyweight platforms that need real infrastructure to stand up, or lightweight editors with no access controls at all. pgconsole sits in the awkward, useful middle: a Postgres editor with team controls built in, shipped as a single binary you run yourself.

The access control is the heart of it, and it treats agents as first-class principals instead of bolting them on after the fact with built-in delegation and attenuation.

Key Features:

One self-hosted binary. Connections, users, and access rules live in a versioned pgconsole.toml, GitOps-style, and you run it with npx or Docker. No separate database to babysit.
Fine-grained, default-deny access. Seven independent rights (read, write, DDL, admin, and more), scoped per connection, with nothing allowed unless a rule grants it. pgconsole parses every statement before it runs, so a destructive query cannot sneak through a read-only tool.
Agents as first-class identities. An agent authenticates as its own principal, or acts on behalf of a user. In delegated mode it inherits that person's permissions, capped lower and never higher, and it loses access the moment the user does.

The alice-claude agent acts for Alice, capped to read-only on a single connection:

[[agents]]
id = "alice-claude"
on_behalf_of = "alice@example.com"
permissions = ["read"]   # capped at or below Alice's own grants
connections = ["prod"]   # and scoped to a single connection

Best For: teams that want a self-hosted Postgres editor and a genuinely governed agent path (distinct identities, delegation, least-privilege caps) without standing up a whole platform.

One honest caveat: it is young. Around 120 stars, Apache-2.0, recent first releases.

Verdict: the access model is the reason to run it. Distinct agent identity, on-behalf-of delegation, and attenuation that holds an agent at or below the human it acts for, all enforced by a SQL parser rather than by trust. If you remember one design from this post, make it this one.

Supabase MCP

Supabase MCP manages a whole Supabase project: auth, storage, edge functions, branches, not just the Postgres database underneath. That breadth is both the appeal and the cage. It only works on Supabase.

It is a first-party server from Supabase, the open-source Firebase alternative, and it inherits the company's instinct to treat everything as one managed surface. That is why it reaches well past plain SQL into auth, storage, and functions.

Key Features:

Wide surface: 29 tools across eight feature groups (Account, Database, Debugging, Development, Edge Functions, Branching, Docs, Storage), covering migrations, TypeScript type generation, and serverless functions beyond plain SQL.
Hosted, with OAuth 2.1 and automatic token refresh, so no local server, binaries, or hardcoded credentials.
Stronger security defaults than most: project scoping, feature-group restriction, and a dedicated read-only role (supabase_read_only_user).

Best For: teams already on Supabase who want their agent to manage the project, not just query it.

Verdict: excellent if Supabase is your platform, irrelevant otherwise. Two things to know: the default tool set is heavy (~19.3k tokens, though selective loading cuts it to ~4.2k), and the Management API endpoint behind execute_sql is still beta with no row limits. More guardrails than most. But like every server here, it mitigates prompt injection, it does not solve it.

Postgres MCP Pro

Postgres MCP Pro, from Crystal DBA, is usually the first result when you search for a Postgres MCP server. It is host-neutral, point it at any Postgres, and it goes further than most. Where the others stop at "run this query," it brings a DBA's tuning toolkit.

Crystal DBA open-sourced it as the on-ramp to its AI-driven Postgres tooling. Then Temporal acquired the company in September 2025, and active development of the server has mostly stalled since. That matters, and I will come back to it.

Key Features:

Performance analysis: index tuning, EXPLAIN plan inspection, and database health checks. This is the real differentiator.
Two access modes: unrestricted (full read/write, arbitrary SQL) and restricted (read-only, with query-time limits).

Best For: developers and DBAs who want the agent to diagnose a slow query or propose an index, not just read rows.

Two things to weigh before reaching for it:

It defaults to unrestricted, and every README example uses it. Out of the box the agent gets full write access and arbitrary SQL. Set --access-mode=restricted for anything past a dev sandbox. Shipping with the safety off by default is a choice I disagree with.
Development has stalled since the acquisition. The last tagged release (v0.3.0) was May 2025, and commits trailed off after the September 2025 Temporal deal (the last one landed in January 2026). Merged work like streamable-HTTP transport never made it into a release or the Docker image, people are asking for one, and 63 issues sit open, including a connection-pool leak under SSE that is the most-reacted of the bunch.

Verdict: the tuning features are genuinely good, and still the reason to keep it around for hands-on work. But change the unrestricted default on day one, pin a known-good version, and factor in the stalled maintenance before you let it anywhere near production.

Postgres MCP Server Comparison Table

Server	Language	Stars	Neutrality	Governance	Best for
pgconsole	TypeScript	120	Any Postgres	Default-deny IAM, per-connection rights; agent identity, on-behalf-of, attenuation, audit	Self-hosted teams wanting control
Supabase MCP	TypeScript	2.7k	Supabase-locked	Read-only by default, scoped token; project scoping, read-only role	Teams already on Supabase
Postgres MCP Pro	Python	2.9k	Any Postgres	Read/write by default, opt-in read-only; none (forwards the connection)	DBA-style tuning + dev

Star counts are from the time of writing and move fast. Treat them as a rough adoption signal, not a ranking.

Summary

None of these three is finished. Pick by where you sit:

pgconsole has the strongest access model (separate agent identity, on-behalf-of, attenuation) but it is the youngest. Right for a self-hosted editor with a governed agent path.
Supabase MCP is the most polished, as long as you live on Supabase. Right when you are already on Supabase.
Postgres MCP Pro has the richest toolkit but lost its momentum to an acquisition. Right for diagnosing a slow query in dev, once you switch off the unrestricted default.

All three are great on a laptop and get scarier in production, for the same structural reason. Simon Willison named it the lethal trifecta: an agent with access to private data, exposure to untrusted input, and a way to send data back out. A correctly scoped credential does not save you, because the attack arrives through the prompt, not the permissions. So the real test is not "can the agent query the database." It is "what happens when the prompt tells it to do something it shouldn't."

🪄🐘 Postgres best practices I wish every app developer knew 🧑‍💻

Adela — Sun, 14 Jun 2026 20:52:27 +0000

At Bytebase we work with PostgreSQL every day, both inside our own product and across the databases our customers point us at. The same handful of mistakes shows up over and over, and almost none of them are exotic. They are small things an application developer controls and skips anyway.

So this is not a database administration checklist. These are the five things I wish every app developer turned on by default. Each takes minutes, and each one buys you something concrete: a connection you can actually debug, a query that fails fast instead of hanging, a deploy that doesn't lock prod. Do them and your DBA stops paging you at 2am. There's a bonus index trick at the end.

1. Set application_name for Better Debugging

This is the cheapest win on the list, and the one people skip most. By default every connection from your fleet looks identical. When something melts down and you're staring at a wall of pg_stat_activity rows, all the same user from the same pooler IP, you have no idea which service is the culprit. application_name is a free label. Set it once per service and the noise turns into a name.

How to Implement

PostgreSQL connection string:

postgresql://user:password@localhost:5432/mydb?application_name=user-service

Node.js with pg:

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  database: 'mydb',
  user: 'myuser',
  password: 'mypassword',
  application_name: 'user-service',
});

Python with psycopg2:

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="myuser",
    password="mypassword",
    application_name="user-service"
)

Java with JDBC:

String url = "jdbc:postgresql://localhost:5432/mydb?ApplicationName=user-service";
Connection conn = DriverManager.getConnection(url, "myuser", "mypassword");

Go with lib/pq:

import (
    "database/sql"
    _ "github.com/lib/pq"
)

connStr := "host=localhost dbname=mydb user=myuser password=mypassword application_name=user-service"
db, err := sql.Open("postgres", connStr)

What You Get

Now pg_stat_activity actually tells you something:

-- See all active connections from your service
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE application_name = 'user-service';

-- Monitor long-running queries from specific services
SELECT application_name, query, now() - query_start as duration
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '30 seconds';

2. Configure PostgreSQL Statement and Lock Timeouts

Postgres will wait forever if you let it. With no statement timeout, one bad query sits there holding a connection until a human notices. Do that across a pool and you don't get a slow app, you get an outage: every slot taken, nothing left to serve real traffic. Four timeouts decide how patient Postgres is willing to be. Set all four.

The Four Critical Timeouts

statement_timeout. The ceiling on any single statement. This is the one to set first; it kills runaway queries before they take the pool down with them.
lock_timeout. How long a statement waits for a lock before giving up, instead of blocking your whole request path behind someone else's transaction.
idle_in_transaction_timeout. Reaps a transaction that opened, grabbed locks, and then went quiet because the app forgot to commit.
transaction_timeout. The ceiling on an entire transaction, not just one statement (PostgreSQL 17+). Stops a long transaction from sitting on locks the whole time.

How to Configure

In connection string:

postgresql://user:pass@localhost/db?options=-c%20statement_timeout=30s%20-c%20lock_timeout=10s%20-c%20transaction_timeout=60s

At transaction level:

BEGIN;
SET LOCAL statement_timeout = '30s';
SET LOCAL lock_timeout = '10s';
SET LOCAL transaction_timeout = '60s'; -- PostgreSQL 17+
-- Your transaction operations here
COMMIT;

Recommended Values by Use Case

Use Case	statement_timeout	lock_timeout	idle_in_transaction_timeout	transaction_timeout
Web Applications	30s	10s	60s	60s
Background Jobs	300s+	30s	300s	600s
Reporting/Analytics	0 (disabled)	60s	600s	0 (disabled)

The logic is just patience matched to purpose. A user is staring at a spinner, so web requests should fail fast. Background jobs run unattended and can afford to wait a little longer for a lock. Analytics queries legitimately run for hours, so you disable the statement cap there and accept that those sessions are the exception, not the rule.

To learn more, please refer Postgres Timeout Explained.

3. Run Online Schema Migrations Without Downtime

This is where a one-line migration becomes an incident. A statement that runs in 20ms on your laptop can lock a 50-million-row table for ten minutes in production, and for those ten minutes every read and write behind it queues up. The thing that actually takes prod down isn't the migration. It's the pile of normal traffic stuck waiting on its lock.

The Problem with Standard Migrations

Traditional migrations acquire exclusive locks that block all reads and writes to your tables:

-- DON'T: This blocks ALL SELECT, INSERT, UPDATE, DELETE on users table
ALTER TABLE users ADD COLUMN email_verified boolean DEFAULT false;

-- DON'T: This blocks ALL writes (INSERT, UPDATE, DELETE) on orders table for hours
CREATE INDEX ON orders (user_id, created_at);

On a small table you'll never notice. On a large one, the same statement blocks every query for minutes, the connection pool drains as traffic backs up behind it, and the stall cascades into every service that touches that table. One ALTER takes down half the stack.

Online Migration Strategies

Use CREATE INDEX CONCURRENTLY

   -- Safe: Builds index without blocking reads/writes
   -- Note: Cannot be run inside a transaction
   CREATE INDEX CONCURRENTLY idx_orders_user_created
   ON orders (user_id, created_at);

To learn more production tips, please refer Postgres CREATE INDEX CONCURRENTLY.

Add columns and constraints safely

   -- Step 1: Add constraint without validation (fast)
   ALTER TABLE users ADD CONSTRAINT email_verified_not_null
   CHECK (email_verified IS NOT NULL) NOT VALID;

   -- Step 2: Validate constraint (can be done later, non-blocking)
   ALTER TABLE users VALIDATE CONSTRAINT email_verified_not_null;

   -- Step 3: Convert to NOT NULL constraint
   ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

4. Split PostgreSQL Roles for Different Operations

Most apps connect to Postgres as one user that can do everything, because that's what the quickstart told you to do. It works right up until a bug in your code runs a DROP TABLE it should never have been allowed to run. Least privilege isn't bureaucracy here. It's the difference between a bug that returns a permission error and a bug that drops prod by accident.

The Problem with Single "Super User" Approach

Many applications use a single database user with broad permissions:

-- DON'T: Single user with excessive privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

You inherit four problems at once. The app carries DDL rights it never uses, so a single bug can reshape the schema. Every connection is the same identity, so in pg_stat_activity you can't tell a runaway report apart from your write path. You can't tune connection settings per workload. And your audit trail says "the app did it", which tells you nothing. One identity, every risk.

The Three-Role Strategy

Three roles cover almost every app. Think three keys, not one master key:

Read-Only Role - For analytics, reporting, read replicas
Application Role - For normal CRUD operations
Migration Role - For schema changes and DDL operations

Implementation Example

Create the roles:

-- 1. Read-only role for analytics/reporting
CREATE ROLE app_reader;
GRANT CONNECT ON DATABASE myapp TO app_reader;
GRANT USAGE ON SCHEMA public TO app_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;
-- Automatically grant SELECT on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_reader;

-- 2. Application role for normal operations
CREATE ROLE app_writer;
GRANT CONNECT ON DATABASE myapp TO app_writer;
GRANT USAGE ON SCHEMA public TO app_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_writer;
-- Auto-grant on future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_writer;

-- 3. Migration role for schema changes
CREATE ROLE app_migrator;
GRANT CONNECT ON DATABASE myapp TO app_migrator;
GRANT ALL PRIVILEGES ON SCHEMA public TO app_migrator;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_migrator;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_migrator;

Create actual users:

-- Create users and assign roles
CREATE USER analytics_user PASSWORD 'secure_password';
GRANT app_reader TO analytics_user;

CREATE USER application_user PASSWORD 'secure_password';
GRANT app_writer TO application_user;

CREATE USER migration_user PASSWORD 'secure_password';
GRANT app_migrator TO migration_user;

5. Use UPSERT for Idempotent Writes

Networks drop, clients retry, two requests race. In production this isn't an edge case, it's Tuesday. UPSERT (INSERT ... ON CONFLICT) lets you write code that doesn't care whether the row already exists. A retry can't create a duplicate, a race resolves itself, and the "create or update" branching you'd otherwise write in the app disappears. The database settles the conflict atomically, which is exactly where you want that decision made.

Ignore Duplicates

-- Pattern 1: Ignore duplicates
INSERT INTO users (email, name, created_at)
VALUES ('john@example.com', 'John Doe', NOW())
ON CONFLICT (email) DO NOTHING;

Update on Conflict

-- Pattern 2: Update on conflict
INSERT INTO users (email, name, updated_at)
VALUES ('john@example.com', 'John Smith', NOW())
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at;

Conditional Update

-- Pattern 3: Conditional update
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES ($1, 1, NOW())
ON CONFLICT (user_id)
DO UPDATE SET
  login_count = user_stats.login_count + 1,
  last_login = EXCLUDED.last_login
WHERE user_stats.last_login < EXCLUDED.last_login;

Bonus: Implement "Invisible" Indexes

PostgreSQL doesn't ship invisible indexes the way MySQL 8 does. But you can fake it by flipping the indisvalid flag in the pg_index catalog, which takes an index out of the planner's consideration without dropping it. Handy when you suspect an index isn't earning its keep and want to confirm that before you actually remove it. Just know you're poking a system catalog directly, so keep it to staging or a maintenance window.

Making an Index "Invisible"

-- Disable an existing index (make it invisible to the query planner)
UPDATE pg_index
SET indisvalid = false
WHERE indexrelid = 'idx_orders_status'::regclass;

Making the Index "Visible" Again

-- Re-enable the index (make it visible to the query planner)
UPDATE pg_index
SET indisvalid = true
WHERE indexrelid = 'idx_orders_status'::regclass;

Checking Index Status

-- See which indexes are currently disabled
SELECT
  schemaname,
  tablename,
  indexname,
  indisvalid AS is_valid
FROM pg_indexes
JOIN pg_index ON pg_indexes.indexname = pg_class.relname
JOIN pg_class ON pg_index.indexrelid = pg_class.oid
WHERE NOT indisvalid;

Note: PostgreSQL itself uses the indisvalid flag internally. When you run CREATE INDEX CONCURRENTLY, PostgreSQL automatically sets indisvalid = false until the index is fully built.

Conclusion

None of this is database administration. It's five things an application developer controls and usually leaves on the floor: name your connections, set your timeouts, migrate online, split your roles, write idempotent inserts. Start with application_name and timeouts. They take ten minutes and pay you back the first time something breaks at 2am.

None of these will win you praise in a design review. They just quietly keep you out of the incident channel, which is the whole point.

Supabase ⚡️ vs. Firebase 🔥: a Complete Comparison ⚔️ in 2025

Ayra Jett — Thu, 07 Aug 2025 08:07:04 +0000

Introduction

Supabase and Firebase are two leading Backend-as-a-Service (BaaS) platforms that enable developers to build applications without handling backend infrastructure. While they serve similar purposes, they take fundamentally different approaches.

Firebase began in 2011 as a real-time NoSQL database and was acquired by Google in 2014. Since then, it has evolved into a comprehensive, fully-managed backend platform deeply integrated with the Google ecosystem.

Supabase, launched in 2020, emerged as an open-source alternative to Firebase. It’s built on PostgreSQL, offering a relational model with SQL support, and can be self-hosted for greater control and transparency.

Quick Comparison Table

Feature	Supabase	Firebase
Philosophy	Open-source, standards-based	Proprietary, fully-managed
Database Type	PostgreSQL (Relational)	Firestore (NoSQL) & Realtime Database (NoSQL)
Data Model	Relational with tables, schemas, and SQL	Document-based with collections and documents
Query Capabilities	Full SQL support with joins, complex queries	Limited query options, no native joins
Authentication	Email/password, social, phone, MFA	Email/password, social, phone, MFA, anonymous
Real-time	PostgreSQL logical replication	Purpose-built real-time infrastructure
Offline Support	Basic, still evolving	Comprehensive, mature
Functions	Edge Functions (Deno)	Cloud Functions (Node.js, Python, Go, etc.)
Storage	S3-compatible with RLS	Google Cloud Storage with Security Rules
AI/ML	Vector database for embeddings, OpenAI/Hugging Face integrations, RAG support	Firebase Studio, Genkit, Gemini API integration, Vertex AI
Pricing Model	Predictable tiered pricing	Usage-based, pay-as-you-go
Self-hosting	Available	Not available
Best For	Data-intensive apps, SQL expertise, complex relationships	Mobile apps, real-time features, rapid prototyping

Core Philosophy and Approach

Supabase embraces open-source technologies and standards, with PostgreSQL at its core. It provides direct SQL access and follows relational database principles, emphasizing data portability and avoiding vendor lock-in.

Firebase takes a proprietary, fully-managed approach focused on developer experience and seamless integration. It abstracts away infrastructure complexities with NoSQL databases optimized for real-time synchronization and mobile use cases.

Database Capabilities

Supabase leverages PostgreSQL's powerful relational capabilities:

Strong data consistency with full ACID compliance
Support for complex queries, including joins and multi-step transactions
Rich data types and advanced indexing for performance tuning
Native support for stored procedures and triggers

Firebase offers two NoSQL database options:

Firestore: A document-based database using collections and documents
Realtime Database: A lightweight JSON tree structure optimized for real-time data synchronization

Supabase excels in complex data relationships and advanced querying, while Firebase prioritizes real-time synchronization and automatic scaling.

Authentication and Security

Both platforms offer comprehensive authentication with email/password, social logins, and multi-factor authentication.

Supabase:

Built on PostgreSQL Row Level Security (RLS).
Access control is enforced through SQL-based policies, allowing fine-grained, table-level permissions.
Offers flexibility and transparency, ideal for SQL-savvy teams.

Firebase:

Uses Firebase Security Rules, written in a JavaScript-like syntax.
Rules are service-specific (e.g., Firestore, Storage), enabling dynamic access control based on user roles and request context.
Tight integration with Google services makes setup straightforward, especially for front-end developers.

Supabase provides database-native security controls ideal for complex permission schemes, while Firebase offers service-specific rules that are easier to implement for common scenarios. Supabase's SQL-based RLS gives more granular database control, whereas Firebase rules are more approachable for developers without SQL experience.

Real-time and Offline Capabilities

Firebase offers battle-tested real-time features with:

Automatic data synchronization across clients
Built-in offline support with local persistence
Automatic conflict resolution and smooth reconnection handling

Supabase Realtime is powered by PostgreSQL logical replication and Phoenix Channels:

Enables database change subscriptions, including INSERT, UPDATE, DELETE events
Supports presence tracking for multiplayer or collaborative use cases

Firebase leads in offline resilience and real-time maturity, while Supabase offers powerful server-driven change tracking with a relational foundation, best suited for applications needing strong data consistency.

Serverless Functions

Supabase Edge Functions are built on the Deno runtime, supporting TypeScript and JavaScript, and are designed to run at the edge for low-latency performance. These functions have direct access to the Supabase PostgreSQL database, making them ideal for lightweight APIs and custom logic tightly coupled with the database.

Firebase Cloud Functions support multiple runtimes, including Node.js, Python, and Go, and can be triggered by a wide range of events — such as HTTP requests, Firestore updates, authentication events, and Pub/Sub messages. They are deeply integrated with Google Cloud Platform for scalability and flexibility.

Supabase Edge Functions are lightweight, TypeScript-native, and database-aware, while Firebase Cloud Functions provide broad language support, rich trigger options, and tight integration with the wider Google Cloud ecosystem.

AI and Machine Learning

Firebase offers a comprehensive, cloud-based AI development ecosystem:

Firebase Studio: Cloud development environment for AI apps with Gemini integration.
Genkit: Open-source framework for building AI features with generative models.
Vertex AI Integration: Direct access to Google's Gemini models for text, image, video, and audio.
AI-assisted Development: Gemini assistance for coding and app development.

Supabase takes a database-centric approach to AI:

Vector Database (pgvector): PostgreSQL extension for vector similarity search.
AI Toolkit: Tools for storing, indexing, and querying vector embeddings.
Semantic Search: Built-in capabilities for meaning-based search.
AI Integrations: Support for OpenAI, Hugging Face, LangChain, and other providers.

Firebase excels with its fully integrated AI development environment and seamless access to Google's advanced models. Supabase shines by offering flexible, database-native AI capabilities built on PostgreSQL, ideal for developers who want tight control over their AI data infrastructure.

Pricing Models

	Supabase (Predictable Tiered Pricing)	Firebase (Usage-Based, à la Carte Pricing)
Free Tier	Generous limits	Daily/monthly quotas
Paid Plans	Pro plan ($25/month), plus Team and Enterprise options	Blaze plan (pay-per-operation), integrated with Google Cloud billing
Cost Structure	Fixed, predictable pricing	Variable, pay-as-you-go per usage
Scalability	Better for growing teams needing cost predictability	May be cheaper for small apps, but expensive as usage scales
Analogy	Fixed-price buffet: predictable and straightforward	À la carte menu: flexible but potentially unpredictable costs

Performance and Reliability

Firebase

Built on Google's global infrastructure
Automatic scaling with minimal configuration
Multi-region replication ensures high availability and low latency
Optimized for real-time data synchronization and global distribution
Ideal for apps needing instant updates and seamless scalability

Supabase

Hosted on AWS with support for multiple regions
Offers read replicas for scaling read-heavy workloads
Requires manual performance tuning for high-traffic or complex queries
Capable of strong performance, especially for relational workloads
More control and flexibility, but with added configuration overhead

Firebase offers "set-and-forget" performance at global scale, while Supabase provides more control for database tuning at the cost of manual optimization. Firebase handles traffic spikes automatically, whereas Supabase requires planning for high-load scenarios but can achieve better PostgreSQL-specific performance.

Conclusion

Choose Supabase for SQL-powered apps needing PostgreSQL flexibility, open-source control, or AI vector support. Opt for Firebase if you prioritize real-time mobile apps, rapid prototyping, and Google's ecosystem. Supabase offers deeper database control; Firebase delivers effortless scaling and real-time sync.

Supabase vs AWS: Feature 🔖 and Pricing 💰 Comparison (2025)

Ayra Jett — Tue, 29 Jul 2025 06:20:00 +0000

When comparing Supabase and AWS, the key question isn't just about raw features, it's about how much functionality you get per dollar and how easily you can scale from a free plan to enterprise-grade infrastructure.

In this guide, we break down the pricing and functionality for each of Supabase’s core features and compare them to their closest AWS counterparts:

Supabase Database → AWS RDS PostgreSQL
Supabase Auth → AWS Cognito
Supabase Storage → AWS S3
Supabase Edge Functions → AWS Lambda
Supabase Realtime (Messages) → AWS SQS/SNS

We’ll go feature by feature, then compare total costs at different usage tiers, and conclude with final recommendations.

Feature by Feature

🗄️ Database: Supabase DB vs AWS RDS

Supabase: Built-in PostgreSQL with real-time, REST API, and simple pricing.

AWS RDS/Aurora: Fully managed PostgreSQL with high configurability, performance tuning, and multi-AZ support.

🧬 Supabase is easier and more predictable. RDS gives more control and is better for compliance-heavy or complex workloads.

See our full comparison here: Supabase vs AWS Database Pricing (2025)

Supabase bundles compute, storage, backup, and bandwidth into flat tiers.
AWS RDS offers cheaper options if you commit to Reserved Instances, but requires piecing together compute, storage, backup, and bandwidth costs.

➡️ If you want more predictable cost and fast setup, choose Supabase. If you need performance tuning or compliance, go with RDS.

🔐 Auth: Supabase Auth vs AWS Cognito

Supabase: Easy-to-use auth system with social login, magic links, phone support, and DB integration.

AWS Cognito: Feature-rich identity management with SSO, federation, and enterprise IAM support.

🧬 Supabase Auth is simpler and developer-friendly, great for fast setup and apps with straightforward auth needs. Cognito is better for enterprises that require SSO, security compliance, and deep AWS integration.

Feature	Supabase Auth	AWS Cognito
Free Tier	50,000 MAUs	50,000 MAUs
Paid Pricing	\$0.0015/MAU	\$0.0055/MAU
SSO Support	Enterprise plan only	Included (OIDC, SAML)
MFA / Passwords	Included	Included
Rate Limits	Generous	Strict (especially SAML)

➡️ Choose Supabase if you want fast, simple auth for your apps. However, as your business grows, your team will likely need a more sophisticated auth solution. Cognito is one option, but many teams also consider Okta, Azure Entra, or WorkOS.

📦 Storage: Supabase Storage vs AWS S3

Supabase: S3-compatible storage with built-in access control, REST API, and CDN.

AWS S3: Scalable object store with advanced features and global durability.

🧬 Supabase is easier to use for app developers. S3 is more powerful and flexible for enterprise-scale needs.

Feature	Supabase Storage	AWS S3
Free Tier	1GB	5GB (12 months only)
Paid Pricing	\$0.021/GB	\$0.023/GB (Standard)
API	Simple REST	REST/S3 SDKs
Permissions	Built-in (row-level)	IAM Policies
CDN	Included	Optional (CloudFront)

➡️ It seems that Supabase Storage is built on top of AWS S3, providing an easy-to-use interface and lower pricing—likely due to volume discounts they can secure. The tradeoff is reduced flexibility in managing the underlying S3 buckets directly.

⚙️ Edge Functions: Supabase vs AWS Lambda

Supabase: Deno-based edge functions with tight database integration and minimal cold starts.

AWS Lambda: Flexible, multi-runtime functions with deep AWS service integration.

🧬 Supabase is simpler for edge APIs. Lambda is better for complex logic, language flexibility, and AWS ecosystem workflows.

Feature	Supabase Edge Functions	AWS Lambda
Free Tier	500,000 invocations	1 million requests/month
Runtime	Deno	Node.js, Python, etc.
Cold Starts	Minimal	Varies by region
Region	Global edge	Region-based
Request Cost	\$2 per million (includes compute)	\$0.20 per million (requests only)
Compute Cost	Included in flat price	Additional: \$0.00001667 per GB-s

Supabase charges a flat fee per call — simpler but may appear higher at first glance.
AWS Lambda splits billing into two parts:
- Requests (cheap)
- Compute (based on memory x execution time), which can add up.

➡️ Supabase Edge charges only an invocation fee, while AWS Lambda has separate charges for invocations and compute time, measured in GB-seconds. Additionally, AWS Lambda supports a wider range of runtimes.

📡 Realtime: Supabase Messages vs AWS SQS/SNS

Supabase: WebSocket-based realtime updates triggered by PostgreSQL changes. Great for simple in-app messaging and live UIs.

AWS SQS/SNS: Scalable, fully managed messaging services. Ideal for decoupled, event-driven architectures and backend pipelines.

🧬 Supabase is great for realtime UX. AWS is better for complex, distributed systems.

Feature	Supabase Realtime	AWS SQS / SNS
Free Tier	500,000 messages/month	1 million requests/month
Pub/Sub	Built-in	SNS only
Queueing	Limited	SQS provides advanced queueing
Integration	PostgreSQL triggers	Broad: Lambda, S3, EC2, etc.
Pricing	\$2 per million messages (flat)	\$0.40–\$0.50 per million requests + delivery & data transfer costs

Supabase charges a flat fee per message, making pricing simple and predictable.
AWS SQS/SNS charges separately for requests, message delivery, and data transfer, offering greater flexibility and scalability for complex event-driven systems.
- SQS Standard: ~\$0.40 per million requests
- SNS: ~\$0.50 per million publishes
- Additional charges: Message delivery (e.g., to Lambda, HTTP endpoints, or SQS) and Data transfer if messages cross regions

➡️ Supabase Realtime charges a flat fee per message, making pricing simple and predictable. AWS SQS/SNS charges separately for requests, message delivery, and data transfer, offering greater flexibility and scalability for complex event-driven systems.

💰 Pricing Comparison by Tier

💡 About AWS Reserved Pricing

The AWS prices shown below are based on on-demand usage, which is flexible but more expensive.
If you commit to 1-year or 3-year Reserved Instances (RIs), you can cut costs by 30–70% — especially for RDS and Lambda.

For example:

RDS db.m5.large (Multi-AZ) drops from \$250+/mo to ~\$145/mo (1-year RI, no upfront)
Lambda and compute costs can be reduced via Compute Savings Plans

✅ Use on-demand for flexibility.
🔐 Use Reserved pricing for long-term, cost-optimized workloads — but it comes with lock-in.

🧪 0. Free Tier

Feature	Supabase	AWS
Database	✅ Shared CPU /500MB PostgreSQL	⚠️ 2 vCPU (burstable) / 1GB RAM for t4g.micro (12 months)
Auth	✅ 50K MAUs	✅ 50K MAUs (Cognito)
Storage	✅ 1GB w/ CDN	⚠️ 5GB (12 months, no CDN)
Functions	✅ 500K Edge calls	✅ 1M Lambda calls
Messaging	✅ 500K Realtime messages	✅ 1M SQS/SNS messages

🔍 Supabase offers a complete full-stack platform for free, with no expiration.
AWS has generous limits — but database and storage expire after 12 months.

🚀 1. Startup (10K MAUs, 20GB DB, 50GB Storage, 500GB bandwidth)

Supabase – \$26.50/month

Included Service	Details
Pro Plan	8GB DB, 100GB storage, 250GB bandwidth
DB Storage Overage	12GB extra @ \$0.125/GB = \$1.50
Auth, Functions, Messaging	Included in plan
Bandwidth	Still within free 250GB – no charge

✅ Flat, all-inclusive plan with tiny storage add-on.

AWS

On-Demand – \$75.00/month
1-Year RI Estimate: ~\$67.73/month
3-Year RI Estimate: ~\$59.47/month

Service	Cost (On-Demand)	1-Year RI (est. -30%)	3-Year RI (est. -60%)	Notes
RDS (db.t3.small)	\$24.82	\$17.37	\$9.93	1 vCPU, 2GB RAM
RDS Storage (20GB)	\$2.30	—	—	General-purpose SSD
Cognito (10K MAUs)	\$0.00	—	—	Within free tier
S3 (50GB)	\$1.15	—	—	\$0.023/GB
Bandwidth (500GB)	\$45.00	—	—	\$0.09/GB
Lambda (1M execs)	\$2.73	\$1.91	\$1.09	512MB, 200ms duration
SQS (1M requests)	\$0.50	—	—	SNS publish cost

⚠️ Most cost comes from bandwidth. Setup involves configuring 5+ services.

📈 2. Growing Business (100K MAUs, 200GB DB, 1TB Storage, 5TB bandwidth)

Supabase – \$630.40/month

Service	Cost	Notes
Pro Plan Base	\$25.00	Includes basic DB, auth, 100GB storage
Extra DB Storage (192GB)	\$24.00	\$0.125/GB beyond included 8GB
Compute Upgrade	\$60.00	4-core, 2GB RAM
Extra Storage (900GB)	\$18.90	\$0.021/GB beyond included 100GB
Bandwidth (4.75TB)	\$427.50	\$0.09/GB beyond 250GB included
Edge Functions	\$50.00	Estimate for high usage
Messaging	\$25.00	Estimate for 10M events

✅ Integrated platform; all services billed under one umbrella.

AWS

On-Demand – \$2,325.59/month
1-Year RI Estimate: ~\$2,000.69/month
3-Year RI Estimate: ~\$1,675.79/month

Service	Cost (On-Demand)	1-Year RI (est. -30%)	3-Year RI (est. -60%)	Notes
RDS (db.m5.large Multi-AZ)	\$249.66	\$174.76	\$99.86	2 vCPU, 8GB RAM
RDS Storage (200GB)	\$46.00	—	—	SSD, replicated
Cognito (90K MAUs)	\$495.00	—	—	\$0.0055/MAU
Lambda (50M execs)	\$833.33	\$583.33	\$333.33	1GB, 500ms execs
S3 (1TB)	\$23.00	—	—	\$0.023/GB
Bandwidth (5TB)	\$450.00	—	—	Tiered pricing
API Gateway	\$175.00	—	—	50M HTTP requests
CloudWatch	\$50.00	—	—	Monitoring
SQS (10M requests)	\$3.60	—	—	\$0.40 per million

⚠️ More expensive due to metered pricing across services. Complex to manage.

🏢 3. Enterprise (1.5M MAUs, 2TB DB, 50TB Storage, 100TB bandwidth)

Supabase – \$19,383.40/month

Service	Cost	Notes
Team Plan	\$599.00	Enterprise features + support
DB Storage (2TB)	\$249.00	Extra beyond base plan
Compute (4XL)	\$960.00	16-core, 32GB RAM
Auth (1.5M MAUs)	\$4,550.00	\$0.00325 per MAU beyond 100K included
Storage (50TB)	\$1,047.90	\$0.021/GB beyond 100GB
Bandwidth (100TB)	\$8,977.50	\$0.09/GB beyond 250GB
Edge Functions	\$2,000.00	Custom pricing
Messaging	\$1,000.00	Custom pricing

✅ Predictable, transparent billing; all services built-in.

AWS

On-Demand – \$73,122.81/month
1-Year RI Estimate: ~\$62,330.91/month
3-Year RI Estimate: ~\$51,539.00/month

Service	Cost (On-Demand)	1-Year RI (est. -30%)	3-Year RI (est. -60%)	Notes
RDS (r5.4xlarge Multi-AZ)	\$2,639.68	\$1,847.78	\$1,055.87	16 vCPU, 128GB RAM
RDS Storage (2TB IOPS)	\$2,500.00	—	—	High-performance SSD
Cognito (1.5M MAUs)	\$22,350.00	—	—	\$0.015 per MAU (Essential plan)
Lambda (1B execs)	\$33,333.33	\$23,333.33	\$13,333.33	2GB RAM, 1s duration
S3 (50TB)	\$1,100.00	—	—	Tiered pricing
Bandwidth (100TB)	\$7,000.00	—	—	Tiered egress
API Gateway	\$3,500.00	—	—	1B HTTP requests
CloudWatch	\$500.00	—	—	Logging + monitoring
SQS (500M messages)	\$199.80	—	—	\$0.40 per million messages

⚠️ Enterprise-grade everything — but costs are 3–4x higher.

🧠 4. Hyperscale (10M MAUs, 10TB DB, 200TB Storage, 500TB Bandwidth)

At this scale, you're operating at a level that most platforms — including Supabase — aren't built to serve out of the box.

Supabase – ❌ Not Recommended at This Scale

Supabase’s architecture is optimized for small to mid-sized applications. While enterprise plans exist, the platform:

Does not offer horizontal scalability for PostgreSQL (no native sharding or clustering).
Becomes cost-prohibitive with 10M+ MAUs due to flat per-user pricing.
Lacks fine-grained controls over networking, observability, and infrastructure.
May require custom support contracts and offloading core services (e.g., auth to WorkOS, storage to S3) to be viable.

⚠️ Supabase is a great tool for rapid product development, but most teams at this scale migrate to cloud-native stacks like AWS, GCP, or Azure for performance, reliability, and cost control.

✅ If you're approaching hyperscale, you can still start with Supabase — but architect with portability in mind.

AWS – \$246,392.81/month (on-demand)

Service	On-Demand	1-Year RI (est. -30%)	3-Year RI (est. -60%)	Notes
RDS (r6i.8xlarge Multi-AZ)	\$10,560.00	\$7,392.00	\$4,224.00	32 vCPU, 256GB RAM x2
RDS Storage (10TB IOPS)	\$12,500.00	—	—	High IOPS SSD
Cognito (9.9M MAUs)	\$148,500.00	—	—	\$0.015 per MAU
Lambda (10B execs)	\$33,333.33	\$23,333.33	\$13,333.33	2GB, 1s exec duration
S3 (200TB)	\$4,000.00	—	—	Tiered pricing
Bandwidth (500TB)	\$35,000.00	—	—	Tiered egress
API Gateway (10B reqs)	\$6,000.00	—	—	\$3.50/million HTTP requests
CloudWatch	\$1,000.00	—	—	Extended logs and metrics
SQS (1B messages)	\$499.48	—	—	\$0.40 per million messages

✅ AWS is built for hyperscale: global infra, granular optimization, and predictable cost control through Reserved Instances and architectural tuning.

🧠 Final Thoughts

Supabase is a fantastic platform for startups and growing teams — offering a unified experience, generous free tier, and simple pricing across auth, database, storage, edge functions, and realtime.

It’s ideal for:

Prototypes and MVPs
Small-to-mid-scale production apps
Teams that want to move fast without managing infrastructure

But as your product reaches hyperscale — millions of users, terabytes of data, and high-throughput compute — Supabase starts to hit architectural and economic limits:

PostgreSQL isn’t horizontally scalable in their setup
Auth and bandwidth costs grow steeply
Fine-grained performance tuning and compliance become difficult

🛣️ Scale Path

➡️ Many teams start with Supabase to move quickly, then gradually offload services (auth, compute, storage) to cloud-native infrastructure like AWS, GCP, or Azure as scale and complexity grow.

✅ Choose Supabase if you want a fast, simple way to build and scale to your first million users.
✅ Choose AWS if you need performance at scale, deep customization, or global enterprise-grade infrastructure.

👉 For a deep dive into database-specific pricing, see: Supabase vs AWS Database Pricing

Manage Databases 🛢️ with Terraform

Ayra Jett — Tue, 22 Jul 2025 07:18:03 +0000

This tutorial is part of the Bytebase Terraform Provider series:

Part 1: Manage Environments with Terraform - Set up environments with policies
Part 2: Manage Databases with Terraform 👈
Part 3: Manage Projects with Terraform - Organize databases into projects
Part 4: Manage Bytebase Settings with Terraform - Configure workspace profile and approval policies
Part 5: Manage SQL Review Rules with Terraform - Define SQL review policies
Part 6: Manage Users and Groups with Terraform - Configure users and groups
Part 7: Manage Database Access Control with Terraform - Grant database permissions
Part 8: Manage Data Masking with Terraform - Protect sensitive data

This tutorial series uses separate Terraform files for better organization. Files are numbered bytutorial part and sub-step (e.g., 1-1-env-setting.tf, 1-2-env-policy-rollout.tf for Part 1, 2-instances.ti for Part 2, etc.). Terraform automatically handles dependencies between files.

View sample code

What You'll Learn

Prerequisites

Before starting this tutorial, ensure you have:

Completed Part 1:
- Bytebase running with built-in sample data
- Service account created
- Terraform initialized
- Environments configured

Step 1 - Explore Built-in Instances

Bytebase sample data includes two sample PostgreSQL instances. Let's explore them:

In Bytebase, click Instances on the left sidebar. You'll see two PostgreSQL instances:

Test Sample Instance (port 8083)
Prod Sample Instance (port 8084)

Click +Add Instance, you may add more database instances.

Click Projects on the left sidebar to see the Sample Project, then click it. You'll see:

hr_test database on Test instance
hr_prod database on Prod instance

Click + New DB, you may create more databases on the existing instances.

Step 2 - Register Instances with Terraform

Now let's import these instances into Terraform management.


Terraform resource	bytebase_instance
Sample file	2-instances.tf

Create 2-instances.tf with the following configuration:

```hcl 2-instances.tf

Test Sample Instance - PostgreSQL on port 8083

resource "bytebase_instance" "test" {
depends_on = [bytebase_setting.environments]
resource_id = "test-sample-instance"
environment = "environments/test"
title = "Test Sample Instance"
engine = "POSTGRES"
# Assign instance license
activation = true

# Connection settings for the built-in test database
data_sources {
id = "admin-test"
type = "ADMIN"
host = "/tmp" # Unix socket for local connection
port = "8083"
username = "bbsample"
password = "" # Empty for local auth
}
}

Production Sample Instance - PostgreSQL on port 8084

resource "bytebase_instance" "prod" {
depends_on = [bytebase_setting.environments]
resource_id = "prod-sample-instance"
environment = "environments/prod"
title = "Prod Sample Instance"
engine = "POSTGRES"
activation = true

data_sources {
id = "admin-prod"
type = "ADMIN"
host = "/tmp"
port = "8084"
username = "bbsample"
password = ""
}
}




**Understanding the Configuration**

- **depends_on**: Ensures environments from Part 1 exist first
- **resource_id**: Must match the existing instance ID
- **environment**: Links instance to Test or Prod environment
- **data_sources**: Connection details for the database

## Step 3 - Apply Configuration

Now apply the configuration:



```bash
terraform plan
terraform apply

Step 4 - Verify Setup

Go to Instances in Bytebase.
Verify both instances show:
- Correct environment assignment
- Y under License column (if using Enterprise)

You can now modify instance properties through Terraform. For example, to rename an instance:

resource "bytebase_instance" "test" {
  # ... other config ...
  title = "Development Database"  # Changed title
}

Further Reading: Manage Projects with Terraform

Supabase vs AWS: Database Pricing 🛢️💰 Comparison in 2025

Ayra Jett — Tue, 22 Jul 2025 03:59:21 +0000

When choosing a PostgreSQL database platform, whether for a side project or a production app, you need to look beyond features and assess the total cost of ownership — including compute, storage, backups, and bandwidth.

In this guide, we compare Supabase vs AWS (RDS and Aurora) across free, entry-level, and production tiers. We focus strictly on database-related costs and explain on-demand vs reserved pricing where applicable.

0. Free Plans: What Do You Get for \$0?

Both platforms offer free tiers, but they differ significantly in duration, compute power, and resource isolation.

Feature	Supabase Free Tier	AWS Free Tier (12 months)
Duration	Forever	12 months from signup
Compute	Shared CPU / 500MB RAM	2 vCPU (burstable) / 1GB RAM (t4g.micro, 750 hrs/mo)*
Compute Type	Shared container environment	Dedicated EC2 instance (Graviton2, burstable)
CPU Architecture	x86	ARM (AWS Graviton2)
Storage (DB)	500MB	20GB gp2*
Backup	7-day snapshot	20GB snapshot
Bandwidth	5GB outbound	15GB outbound
OS / Isolation	Serverless (no OS access)	Full OS-level isolation

Supabase Free is ideal for hobby projects, quick MVPs, or internal tools with light traffic. It requires no setup and stays free forever.
AWS Free Tier gives you significantly more power and isolation but is limited to the first 12 months after signup.

1. Entry-Level (Low Cost)

When you outgrow the free tier but don’t need production-grade resources, these plans offer low-cost paths. Supabase simplifies everything with a flat rate. AWS offers more control and deeper savings through reserved pricing.

Feature	Supabase Pro Tier	RDS t4g.micro (On-Demand)	RDS t4g.micro (1yr Reserved)	RDS t4g.micro (3yr Reserved)
Monthly Price	\$25 (includes \$10 compute)*	\$11.68	\$6.69	\$4.76
Compute	1 vCPU (shared) / 1GB RAM	2 vCPU (burstable) / 1GB RAM	2 vCPU (burstable) / 1GB RAM	2 vCPU (burstable) / 1GB RAM
Compute Type	Shared container environment	Dedicated ARM instance (Graviton2)	Dedicated ARM instance (Graviton2)	Dedicated ARM instance (Graviton2)
OS Access	No	Yes	Yes	Yes
Storage Included	8GB	20GB gp2	20GB gp2	20GB gp2
Extra Storage	\$0.125/GB	\$0.115/GB	\$0.115/GB	\$0.115/GB
Backups	7-day snapshot	Free up to DB size, then \$0.095/GB*	Free up to DB size, then \$0.095/GB	Free up to DB size, then \$0.095/GB
Bandwidth	250GB outbound included, then $0.09/GB	\$0.09/GB outbound	\$0.09/GB outbound	\$0.09/GB outbound

Supabase Pro is great if you want simple, predictable pricing without dealing with EC2, storage classes, or IOPS tuning.
RDS On-Demand offers low-cost dedicated compute with more configurability.
Reserved instances (1yr and 3yr) reduce costs dramatically, but require long-term commitment.

2. Mid-Tier Production (100GB Storage + Moderate Usage)

For established applications with real user traffic and non-trivial data volumes, cost differences and platform flexibility become more significant.

Feature	Supabase (Large)	RDS m5.large (On-Demand)	RDS m5.large (1yr Reserved)	RDS m5.large (3yr Reserved)	Aurora r5.large (On-Demand)	Aurora r5.large (1yr Reserved)	Aurora r5.large (3yr Reserved)
Monthly Price	\$110 (flat)*	\$130	\$81	\$56	\$211	\$138	\$96
Compute	2 vCPU (shared) / 8GB RAM	2 vCPU / 8GB RAM	2 vCPU / 8GB RAM	2 vCPU / 8GB RAM	2 vCPU / 16GB RAM	2 vCPU / 16GB RAM	2 vCPU / 16GB RAM
Compute Type	Shared container environment	Dedicated EC2 (x86)	Dedicated EC2 (x86)	Dedicated EC2 (x86)	Aurora cluster (I/O-Optimized)*	Aurora cluster (I/O-Optimized)	Aurora cluster (I/O-Optimized)
OS Access	No	Yes	Yes	Yes	Yes	Yes	Yes
Storage (100GB)	Included	\$11.50 (gp2)	\$11.50 (gp2)	\$11.50 (gp2)	\$10 (I/O-Optimized)	\$10 (I/O-Optimized)	\$10 (I/O-Optimized)
Backup	Included	Free up to DB size*	Free up to DB size	Free up to DB size	\$0.021/GB*	\$0.021/GB	\$0.021/GB
Bandwidth (500GB)	\$22.50 (250GB included + $0.09/GB)	\$45 (\$0.09/GB)	\$45 (\$0.09/GB)	\$45 (\$0.09/GB)	\$45 (\$0.09/GB)	\$45 (\$0.09/GB)	\$45 (\$0.09/GB)
Total/Month	\$145	\$186	\$138	\$112.50	\$266	\$193	\$161

Supabase (Large) bundles all costs and removes infrastructure complexity — ideal for fast-moving teams.
RDS Reserved (especially 3-year) cuts monthly bills by over 50%.
Aurora Reserved costs more, but adds built-in high availability, multi-AZ replication, and better scaling.

3. Heavy Workload (500GB+ Storage, High Throughput)

For mission-critical workloads with large storage, high concurrency, and peak traffic, this tier shows how pricing stacks up across platforms.

Feature	Supabase 2XL	RDS r5.xlarge (On-Demand)	RDS r5.xlarge (1yr Reserved)	RDS r5.xlarge (3yr Reserved)	Aurora r5.xlarge (On-Demand)	Aurora r5.xlarge (1yr Reserved)	Aurora r5.xlarge (3yr Reserved)
Monthly Price	\$410 (flat)*	\$422	\$246	\$170	\$422	\$287	\$210
Compute	4 vCPU (shared) / 16GB RAM	4 vCPU / 32GB RAM	4 vCPU / 32GB RAM	4 vCPU / 32GB RAM	4 vCPU / 32GB RAM	4 vCPU / 32GB RAM	4 vCPU / 32GB RAM
Compute Type	Shared container	Dedicated EC2 (x86)	Dedicated EC2 (x86)	Dedicated EC2 (x86)	Aurora cluster (I/O-Optimized)*	Aurora cluster (I/O-Optimized)	Aurora cluster (I/O-Optimized)
OS Access	No	Yes	Yes	Yes	Yes	Yes	Yes
Storage (500GB)	Included	\$57.50 (gp2)	\$57.50 (gp2)	\$57.50 (gp2)	\$50 (I/O-Optimized)	\$50 (I/O-Optimized)	\$50 (I/O-Optimized)
IOPS / Throughput	Included (abstracted)*	\$100+ (io1 estimated)*	\$100+ (io1 estimated)	\$100+ (io1 estimated)	Included	Included	Included
Backup (500GB extra)	Included	\$47.50*	\$47.50	\$47.50	\$10.50	\$10.50	\$10.50
Bandwidth (1TB)	\$67.50 (250GB included + $0.09/GB)	\$90 (\$0.09/GB)	\$90 (\$0.09/GB)	\$90 (\$0.09/GB)	\$90 (\$0.09/GB)	\$90 (\$0.09/GB)	\$90 (\$0.09/GB)
Total/Month	\$477.5	\$717	\$541	\$465	\$572	\$437	\$361

Supabase 2XL is an all-inclusive bundle that scales without requiring DBAs or infra tuning.
RDS Reserved (3yr) delivers maximum cost-efficiency if your workload is stable.
Aurora Reserved is ideal for high-volume, multi-region, or high-availability requirements.

Cost Reference for Storage & Compute

Metric	Supabase	AWS RDS (On-Demand)	AWS RDS (Reserved)*	Aurora (I/O-Optimized)*
Storage	\$0.125/GB	\$0.115/GB (gp2)*	\$0.115/GB (gp2)	\$0.10–\$0.225/GB*
Backup	Included	\$0.095/GB*	\$0.095/GB	\$0.021/GB (snapshot)*
Bandwidth	250GB outbound included	\$0.09/GB outbound	\$0.09/GB outbound	\$0.09/GB outbound
Compute	\$10–\$3,730 (flat)	\$11–\$1,688	\$6–\$1,080	\$67–\$3,376

Reserved pricing can reduce compute cost by 30–60%, especially for year-long or 3-year commitments.
Aurora charges by I/O operations, unless you're on their newer I/O-optimized pricing model.

📝 Explanatory Notes

t4g.micro (burstable): AWS uses burstable instances like t4g.micro for its Free and entry-level RDS tiers. These provide 2 ARM-based vCPUs with a CPU credit system — ideal for low-to-moderate workloads with occasional spikes.
gp2 Storage (AWS): gp2 volumes offer 3 IOPS per GB, with a minimum baseline of 100 IOPS at 20GB. They also include burst capacity, giving better performance than basic shared storage.
Supabase Flat Pricing: Supabase’s pricing includes compute, storage, backups, and bandwidth in a single monthly rate — simplifying cost tracking and reducing surprise bills.
Backup Costs (AWS): RDS provides free backup storage up to the size of your DB. Additional snapshot storage is charged at:
- \$0.095/GB for RDS
- \$0.021/GB for Aurora
Aurora I/O-Optimized: This newer Aurora pricing tier eliminates per-I/O charges and instead bills a flat rate per GB stored. Ideal for write-heavy or high-throughput workloads with unpredictable I/O.
IOPS Costs (RDS): For high-performance needs, RDS users may upgrade to io1 or gp3 storage with provisioned IOPS — typically adding \$100+ per month for workloads requiring consistent low-latency throughput.
Reserved Pricing (AWS): RDS and Aurora support 1-year and 3-year reservations, reducing monthly compute costs by 30–60%. These are billed upfront or monthly and require workload stability.

Conclusion

Use Case	Best Choice	Price Range (Monthly)	Why
Free hobby project	Supabase Free	\$0	No time limit, zero config
Low-cost dev/test DB	Supabase Pro	\$25	Simple flat rate, includes compute, storage, and bandwidth
AWS trial or AWS-focused team	AWS Free Tier	\$0 (12 mo)	Best value compute & storage for teams already using AWS
Cost-sensitive production workload	RDS Reserved	\$4.76–\$96	Long-term commitment cuts RDS compute cost by up to 60%
Simple mid-sized app	Supabase or RDS	\$110–\$186	Supabase for ease, RDS for control & cost tuning
High write/read throughput	Aurora Reserved	\$96–\$437	Built-in performance, replication, and I/O-optimized pricing
Multi-region / enterprise scale	Aurora or RDS	\$161–\$717	Supports replication, multi-AZ, and enterprise-grade configurations

Supabase offers simple pricing and easy setup, ideal for fast-moving projects.
AWS RDS Reserved is best for long-term, cost-optimized workloads with more control.
Aurora suits high-performance, high-availability needs, but at a higher cost.

Choose based on your need for simplicity vs control, and how stable your usage will be. Supabase works well for early-stage apps; AWS shines for scaled, mature systems.

Need more than a database? Our next post will compare Supabase vs AWS pricing across auth, messaging, and more.

How LayerX Achieves “Painless” Governance and Security 🛡️ in the Cloud ☁️

Ayra Jett — Fri, 11 Jul 2025 03:57:53 +0000

At CloudNative Days Summer 2025, Hokuto Hoshi—CISO and Head of SRE/Corporate Engineering at LayerX—shared how the company has built a cloud-native governance framework that balances compliance, security, and developer agility. The presentation, titled Realizing "Painless" Governance and Security in the Cloud, resonated with platform teams facing growing audit requirements without wanting to slow down development.

About LayerX

LayerX is a Tokyo-based technology company with a mission to enable digitalization across all economic activities. The company operates across several domains, including:

Bakuraku: A suite of AI-powered SaaS tools streamlining core enterprise workflows such as expense management, invoice processing, and approval flows.
Fintech: Asset management and securities solutions delivered through a joint venture model.
AI/LLM: An internal platform that transforms organizational knowledge into structured, retrievable data using large language models.

LayerX builds its products with a strong emphasis on automation, auditability, and developer experience—making it a prime example of a cloud-native enterprise balancing innovation with compliance.

Governance Without the Pain

For many organizations, audit readiness still means manually collecting logs, managing permissions through spreadsheets, and retrofitting access controls into existing workflows. LayerX takes a different approach:

Automate controls using infrastructure-as-code and cloud-native platforms
Integrate security practices directly into development workflows
Maintain transparency and explainability for auditors and engineers alike
Treat compliance not as a checkbox but as part of system reliability

Their implementation spans the entire stack—from user account management to infrastructure provisioning, from application deployment to database change management.

From Identity to Infrastructure: A Systems Approach

SSO as a Security Backbone

LayerX uses Microsoft Entra ID to unify identity across systems. Employee onboarding and offboarding are fully automated via HR data and Notion, with Slack-integrated workflows for approvals. Access is consistently enforced through SSO across all internal tools.

Group-Based Permissioning

User groups are defined via SmartHR and translated to Terraform HCL, then synced to Entra ID. This bridges HR systems with access control, enabling scalable role-based access patterns.

Time-Bound Privilege Escalation

To handle temporary elevated permissions, LayerX leverages Entra PIM, allowing just-in-time access with automatic expiration—a modern answer to long-standing privilege management issues.

Change Management in GitHub

Application and infrastructure changes are governed by pull request approvals using GitHub's CODEOWNERS. Even emergency changes are logged and traceable, and deployments are automated via tools like ecspresso and Terraform within a monorepo setup.

Unified Logging and Simplified Audits

Logs from AWS CloudTrail, Entra ID, Datadog, and Amazon Athena are aggregated and searchable via APIs and CLI commands. LayerX stores logs in Snowflake, making it easy to visualize and retrieve audit evidence. Log extraction is automated—no more ad hoc queries or manual exports.

Database Governance with Bytebase

Traditionally, database operations are disconnected from CI/CD and governance workflows. LayerX addressed this gap by adopting Bytebase to introduce structured, auditable change management for their databases.

With Bytebase, LayerX can:

Review and approve schema changes through a web interface or API
Maintain audit trails for all SQL operations
Eliminate the need for bastion hosts by shifting to a secure, review-based model

This brings the same rigor to database operations that exist for code and infrastructure—an essential step for aligning security, reliability, and developer velocity.

A Model for Modern Platform Teams

LayerX shows that auditability and agility are not mutually exclusive. By embedding governance into the developer workflow and codifying it through infrastructure and policy engines, they’ve created a foundation that’s secure, scalable, and operationally efficient.

For teams managing database changes, LayerX’s use of Bytebase highlights a growing trend: bringing CI/CD, approval workflows, and visibility to one of the last remaining blind spots in DevSecOps.

Summary of Tools and Vendors Used

What LayerX has accomplished is a pragmatic example of how modern engineering practices, when paired with the right tools, can turn governance from a bottleneck into a built-in strength.

Tool / Platform	Vendor	Purpose
Microsoft Entra ID	Microsoft	SSO, identity provider, group and access control
SmartHR	SmartHR	HR system for managing employee roles and attributes
Terraform	HashiCorp	Infrastructure-as-code, role syncing with IdP
Entra PIM (Privileged Identity Mgmt)	Microsoft	Time-limited elevated access with approvals
GitHub + CODEOWNERS	Microsoft (GitHub)	Pull request approval workflow for app and infra changes
ecspresso	KAYAC	ECS deployment tool integrated with GitHub and Terraform
Bytebase	Bytebase	CI/CD for database schema and data changes
AWS CloudTrail	Amazon Web Services	Logging of AWS resource activities
Amazon Athena	Amazon Web Services	Serverless log querying and analysis
Snowflake	Snowflake	Data warehouse for storing and visualizing audit logs
Datadog	Datadog	System monitoring and additional log aggregation
Slack	Salesforce	Notification and approval workflows
Notion	Notion	Internal metadata and account provisioning source
CLI / APIs / Scripts	Custom	Automating log extraction and compliance tasks

How to Configure MariaDB 🦦 SSL Connection 🔌

Ayra Jett — Wed, 02 Jul 2025 06:18:30 +0000

This tutorial shows you how to configure MariaDB SSL connection using self-signed certificates. You'll learn to:

Generate SSL certificates (CA, server, client)
Configure MariaDB server for SSL
Test SSL connections from clients

Prerequisites

# Verify MariaDB installation
mariadb --version

# Verify OpenSSL installation
openssl version

Ensure you have MariaDB and OpenSSL installed.

Generate SSL Related Files

OpenSSL Config

Set up the configuration file:

cat >req.conf <<EOF
[ req ]
distinguished_name = req_distinguished_name
x509_extensions = v3_ca
prompt = no
[ req_distinguished_name ]
C = CN
ST = GD
O = Bytebase
CN = root
[ v3_ca ]
basicConstraints = critical,CA:TRUE
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid:always,issuer:always
[ v3_req ]
keyUsage = keyEncipherment, dataEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names
[ alt_names ]
IP.1 = YOUR_SERVER_IP
DNS.1 = YOUR_SERVER_HOSTNAME
DNS.2 = localhost
IP.2 = 127.0.0.1
EOF

Replace YOUR_SERVER_IP with your actual server IP address. You can find it with ifconfig or ip addr show.

Generate Certificates

Generate Root CA key and certificate:

openssl genrsa -out ca-key.pem 2048
openssl req -x509 -new -key ca-key.pem -sha256 -days 36500 -out ca-cert.pem -extensions 'v3_ca' -config req.conf

Generate Server key and certificate:

openssl genrsa -out server-key.pem 2048
openssl req -new -sha256 -key server-key.pem -out server-req.pem -subj "/C=CN/ST=GD/O=Bytebase/CN=YOUR_SERVER_IP"
openssl x509 -req -days 36500 -sha256 -extensions v3_req -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -in server-req.pem -out server-cert.pem

Replace YOUR_SERVER_IP with your real server IP.

Generate Client key and certificate:

openssl genrsa -out client-key.pem 2048
openssl req -new -sha256 -key client-key.pem -out client-req.pem -subj "/C=CN/ST=GD/O=Bytebase/CN=mariadb-client"
openssl x509 -req -days 36500 -sha256 -extensions v3_req -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -in client-req.pem -out client-cert.pem

Configure MariaDB Server

Copy SSL files and set permissions:

For macOS (Homebrew):

# For Apple Silicon Macs
sudo mkdir -p /opt/homebrew/etc/mariadb/ssl
sudo cp ca-cert.pem server-cert.pem server-key.pem /opt/homebrew/etc/mariadb/ssl/
sudo chown -R $(whoami):admin /opt/homebrew/etc/mariadb/ssl/
sudo chmod 600 /opt/homebrew/etc/mariadb/ssl/*-key.pem
sudo chmod 644 /opt/homebrew/etc/mariadb/ssl/ca-cert.pem /opt/homebrew/etc/mariadb/ssl/server-cert.pem

# For Intel Macs
# sudo mkdir -p /usr/local/etc/mariadb/ssl
# sudo cp ca-cert.pem server-cert.pem server-key.pem /usr/local/etc/mariadb/ssl/
# sudo chown -R $(whoami):admin /usr/local/etc/mariadb/ssl/
# sudo chmod 600 /usr/local/etc/mariadb/ssl/*-key.pem
# sudo chmod 644 /usr/local/etc/mariadb/ssl/ca-cert.pem /usr/local/etc/mariadb/ssl/server-cert.pem

For Linux systems:

sudo mkdir -p /etc/mariadb/ssl
sudo cp ca-cert.pem server-cert.pem server-key.pem /etc/mariadb/ssl/
sudo chown mysql:mysql /etc/mariadb/ssl/*
sudo chmod 600 /etc/mariadb/ssl/*-key.pem
sudo chmod 644 /etc/mariadb/ssl/ca-cert.pem /etc/mariadb/ssl/server-cert.pem

Edit MariaDB configuration file:

# For macOS (Apple Silicon)
sudo nano /opt/homebrew/etc/my.cnf

# For macOS (Intel)
sudo nano /usr/local/etc/my.cnf

# For Linux (Ubuntu/Debian)
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# For Linux (CentOS/RHEL)
sudo nano /etc/my.cnf

Add SSL configuration:

For macOS (Apple Silicon):

[mysqld]
ssl-ca = /opt/homebrew/etc/mariadb/ssl/ca-cert.pem
ssl-cert = /opt/homebrew/etc/mariadb/ssl/server-cert.pem
ssl-key = /opt/homebrew/etc/mariadb/ssl/server-key.pem
bind-address = 0.0.0.0
port = 3306

For macOS (Intel):

[mysqld]
ssl-ca = /usr/local/etc/mariadb/ssl/ca-cert.pem
ssl-cert = /usr/local/etc/mariadb/ssl/server-cert.pem
ssl-key = /usr/local/etc/mariadb/ssl/server-key.pem
bind-address = 0.0.0.0
port = 3306

For Linux systems:

[mysqld]
ssl-ca = /etc/mariadb/ssl/ca-cert.pem
ssl-cert = /etc/mariadb/ssl/server-cert.pem
ssl-key = /etc/mariadb/ssl/server-key.pem
bind-address = 0.0.0.0
port = 3306

Restart MariaDB:

# For macOS (Homebrew)
brew services restart mariadb

# For Linux (systemd)
sudo systemctl restart mariadb

Test SSL Connection

mariadb -h localhost -u root -p

So that you'll be entering MariaDB CLI. You can also verify remote connection by replacing the localhost above with your server IP to connect. Check your SSL connection with:

\s

Seeing something like SSL: Cipher in use is TLS_AES_256_GCM_SHA384, cert is OK, so that the SSL connection is ready.

Or use command

SHOW STATUS LIKE 'Ssl_version';

You'll see something like:

+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.3 |
+---------------+---------+
1 row in set (0.006 sec)

Summary

You have successfully configured SSL for MariaDB:

Generated CA, server, and client certificates
Configured MariaDB with SSL settings
Tested secure connections from clients

Your MariaDB server now accepts encrypted connections only.

Features I Wish Postgres 🐘 Had but MySQL 🐬 Already Has 😼

Ayra Jett — Thu, 26 Jun 2025 07:20:30 +0000

At Bytebase, we work extensively with both Postgres and MySQL. In our previous post, we highlighted features where Postgres shines compared to MySQL. In this post, we'll examine where MySQL excels and Postgres falls short.

Performant Storage Engine for Write-Intensive Workloads

Postgres' heap-based storage with tuple versioning MVCC creates more overhead for write-intensive scenarios (write amplification). While Postgres has made improvements with features like HOT updates, it still generates more WAL (Write-Ahead Log) traffic and requires more frequent VACUUM operations under heavy write loads.

On the other hand, MySQL employs clustered index storage and undo logging, which is more performant especially for write heavy workload.

The comparison is detailed in Uber's switch From Postgres to MySQL and The Part of Postgres We Hate the Most

Postgres users need to deal with the infamous transaction ID wraparound, and the not-so-famous MultiXact member exhaustion. OrioleDB is tackling these issues, but it's still years until it reaches the maturity of MySQL's InnoDB.

Replication

MySQL's Group Replication provides both single-primary and multi-primary replication with automatic failover and conflict detection built-in. It handles network partitions gracefully with built-in quorum logic, automatically determining which partition remains writable.

Postgres relies on external tools like Patroni, pg_auto_failover for similar functionality. These solutions work but require non-trivial operational overhead and expertise to configure and maintain properly.

Postgres' logical replication, introduced in version 10, is also still catching up. Its most significant limitation is DDL operations are not replicated - they must be applied manually to subscribers.

Invisible Indexes

MySQL's invisible indexes allow you to test index effectiveness without impacting existing queries or disable indexes temporarily - a crucial feature for production database optimization.

-- MySQL: Create invisible index for testing
CREATE INDEX idx_orders_status ON orders (status) INVISIBLE;

-- Test queries with the invisible index
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Make index visible after confirming it helps
ALTER TABLE orders ALTER INDEX idx_orders_status VISIBLE;

They also help with index maintenance strategies. You can make an index invisible before dropping it, ensuring no queries break, then safely remove it later.

-- Make index invisible to see if it hurts
ALTER TABLE orders ALTER INDEX idx_orders_status INVISIBLE;

-- Then drop it if it doesn't break queries
DROP INDEX idx_orders_status ON orders;

Postgres doesn't provide built-in syntax to enable/disable an index. Though you can toggle indisvalid from the pg_index table as a workaround.

Online Schema Migration Tooling

MySQL's ecosystem provides mature, production-ready tools for online schema changes that Postgres still lacks.

If your table doesn't have foreign keys, you can use GitHub's trigger-less gh-ost, otherwise, Percona's trigger-based pt-online-schema-change is also a battle-tested solution.

The Postgres community has developed several solutions, but none has matched the adoption rate of MySQL's gh-ost or pt-online-schema-change.

Connection Handling

While Postgres' process-per-connection provides better isolation, it consumes more memory per connection, incurs context switching overhead, and is more prune to hit connection limit.

MySQL's thread-per-connection model is subject to entire server crashes caused by a query of death, but is more scalable.

There is a comprehensive discussion in the Postgres community about making the switch Let's make Postgres multi-threaded. But because the process model is too deeply coupled with the rest of Postgres, it will be quite a challenging task to tackle.

MySQL remains the most popular open source database today, serving as the de facto database of the internet era. Looking back, two pillars drove its popularity:

Pragmatic core team. The original MySQL team prioritized features based on real-world use cases.

Replication, the most critical building block for scalability and fast failover on commodity hardware, was introduced in version 3.23 in 2001—nearly a decade before Postgres added replication in 2010 (version 9.0).
When the core team lacked bandwidth to build a better storage engine, they designed a pluggable storage engine architecture that opened the door to InnoDB.

Vibrant community. When internet companies experienced hypergrowth and needed an open source database that was performant and scalable on commodity hardware, MySQL was the obvious choice. Google's initial ads system, Twitter, Facebook, Alibaba, and GitHub all chose MySQL. Though exceptions like Instagram chose Postgres, most successful internet-era companies selected MySQL and contributed enhancements like MHA, RocksDB, gh-ost, and replication improvements. This created a generation of MySQL experts advocating the technology and spawned dedicated consulting businesses like Percona, which provided educational resources and indispensable tools like the Percona toolkit.

Tides are changing, and Postgres has become the new hotness in the AI era. While MySQL retains several architectural advantages over Postgres, will it hold the fort or become obsolete within 10 years?

Features I Wish MySQL Had but Postgres 🐘 Already Has 😻

Ayra Jett — Tue, 10 Jun 2025 07:04:15 +0000

At Bytebase, we work extensively with PostgreSQL and MySQL. Both databases have pros and cons. In this post, we’ll highlight where PostgreSQL shines and MySQL falls short.

Transactional DDL

One of PostgreSQL's most underrated features is its support for transactional DDL (Data Definition Language). In PostgreSQL, you can wrap schema changes in transactions and roll them back if something goes wrong. Even better, PostgreSQL supports savepoints within DDL transactions for fine-grained control.

-- PostgreSQL: This works!
BEGIN;
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;
SAVEPOINT after_column_add;
CREATE INDEX idx_users_email_verified ON users(email_verified);
-- Oops, index creation failed, but we can keep the column
ROLLBACK TO SAVEPOINT after_column_add;
COMMIT;

In MySQL, DDL statements are auto-committed, meaning there's no going back once you execute them. This makes database migrations risky, especially in production environments.

Custom Types and Domains

PostgreSQL allows you to create custom data types and domains, providing better data modeling capabilities and stronger type safety.

-- PostgreSQL: Create custom types
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE DOMAIN email AS TEXT CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email email,  -- Custom domain with validation
    current_mood mood  -- Custom enum type
);

MySQL's ENUM support is limited and doesn't offer the same flexibility. Custom domains with built-in validation rules help enforce data integrity at the database level, reducing the need for application-level validation and ensuring consistency across all applications that access the database.

Array Types

PostgreSQL's native array support is incredibly powerful for storing and querying lists of values without requiring separate tables.

-- PostgreSQL: Arrays are first-class citizens
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[],  -- Array of tags
    view_counts INTEGER[]  -- Array of daily view counts
);

-- Query with array operations
SELECT * FROM articles
WHERE 'postgresql' = ANY(tags)
AND array_length(view_counts, 1) > 7;

It's worth noting that PostgreSQL arrays don't support referential integrity constraints yet - you can't have foreign key relationships on array elements. Despite this limitation, native array support eliminates the need for many-to-many relationship tables in certain scenarios and can significantly simplify data models.

Common Table Expressions (CTEs)

PostgreSQL has supported CTEs since version 8.4 (2009), while MySQL only added basic CTE support in version 8.0 (2018). PostgreSQL's implementation is more mature and feature-complete.

-- PostgreSQL: Recursive CTEs for hierarchical data
WITH RECURSIVE employee_hierarchy AS (
    -- Base case: top-level managers
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: subordinates
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

PostgreSQL's CTE implementation supports more advanced features like MATERIALIZED and NOT MATERIALIZED hints, giving you control over query optimization strategies.

Row Level Security (RLS)

PostgreSQL's Row Level Security allows you to implement fine-grained access control directly at the database level.

-- PostgreSQL: Enable RLS and create policies
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Policy for user-specific data
CREATE POLICY user_documents ON documents
    FOR ALL TO app_user
    USING (owner_id = current_setting('app.current_user_id')::INTEGER);

-- Policy for multi-tenant applications
CREATE POLICY tenant_isolation ON documents
    FOR ALL TO app_user
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER);

-- Users can only see their own documents automatically
SELECT * FROM documents;  -- Only returns user's documents in their tenant

While RLS is excellent for simple user_id or tenant_id filtering, it becomes complex for more sophisticated authorization rules involving roles, permissions, or dynamic relationships. However, it still serves as a valuable defense-in-depth mechanism in your database security chain, ensuring that even if application-level security fails, sensitive data remains protected.

MySQL lacks built-in RLS, forcing developers to implement access control in application code or through complex view structures.

Partial Indexes

PostgreSQL allows you to create indexes with WHERE clauses, making them smaller and more efficient for specific query patterns.

-- PostgreSQL: Index only active users
CREATE INDEX idx_active_users ON users (last_login)
WHERE status = 'active';

-- Index only pending orders
CREATE INDEX idx_pending_orders ON orders (created_at)
WHERE status = 'pending';

MySQL requires you to index the entire column, leading to larger indexes and potentially slower performance when you only need to optimize queries for a subset of data.

Spatial Support

While both databases support spatial data, PostgreSQL's PostGIS extension provides far more comprehensive geospatial capabilities.

-- PostgreSQL with PostGIS: Advanced spatial queries
SELECT name,
       ST_Distance(location, ST_MakePoint(-73.935242, 40.730610)) as distance_km
FROM restaurants
WHERE ST_DWithin(location, ST_MakePoint(-73.935242, 40.730610), 1000)  -- Within 1km
ORDER BY location <-> ST_MakePoint(-73.935242, 40.730610)  -- KNN search
LIMIT 10;

MySQL's spatial support, while improving, doesn't match the depth and performance of PostGIS. For applications requiring serious geospatial functionality, PostgreSQL with PostGIS is the clear winner.

Vector Support

With the rise of AI, PostgreSQL has embraced vector storage and similarity search through pgvector.

-- PostgreSQL with pgvector: Store and search embeddings
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)  -- OpenAI embedding dimension
);

-- Find similar documents using cosine similarity
SELECT content, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;

MySQL recently added vector support in version 9.0 with basic VECTOR data types and limited distance functions, but PostgreSQL's pgvector extension remains more mature with broader operator support, better indexing options (IVFFlat, HNSW), and a richer ecosystem of vector operations.

Parser

PostgreSQL adheres more closely to SQL standards and provides better error messages and more predictable behavior. MySQL's parser has historically been more lenient, which can lead to subtle bugs and makes it harder to write portable SQL.

For tooling developers, the libpg_query project extracts PostgreSQL's actual parser code, providing 100% compatibility with PostgreSQL's SQL parsing behavior. For MySQL, the most widely used parser is from TiDB, but it has compatibility differences since it was built specifically for TiDB's needs rather than pure MySQL compatibility.

Openness

PostgreSQL pairs a permissive PostgreSQL License (akin to BSD) with an extensible architecture, giving developers full freedom to embed, modify, or commercialize the database without license friction. Its community operates in the open: design discussions happen on the public pgsql-hackers mailing list, and every commit carries an explanatory message—see, for instance, the recent PG 18 additions of virtual generated columns and NOT VALID NOT NULL constraints.

By contrast, MySQL’s dual-licensing model (GPL plus commercial) restricts downstream choices, and its main public touchpoint is an opaque bug tracker—some issues are hidden from both the community and the original reporter.

As MongoDB’s CEO put it on the FY26 Q1 earnings call:

Open source, open standard, and most importantly, not owned by any one vendor.

PostgreSQL embodies that ideal in a way MySQL cannot.

Postgres 🐘 vs. MySQL 🐬: DDL Transaction Difference

Ayra Jett — Tue, 03 Jun 2025 13:30:00 +0000

Database schema changes are critical operations that require careful planning and execution. The ability to perform these changes safely and reliably is a key consideration when choosing a database management system. In this post, we'll compare how PostgreSQL 17 and MySQL 8 handle Data Definition Language (DDL) transactions, with a focus on atomicity and rollback capabilities.

Understanding DDL Transactions

Before diving into the comparison, let's clarify what we mean by DDL transactions. DDL statements can be grouped together and either committed as a unit or rolled back entirely if something goes wrong.

There are two important concepts to distinguish:

Transactional DDL: The ability to include DDL statements within a multi-statement transaction block, with the option to commit or roll back all statements together.
Atomic DDL: The guarantee that individual DDL statements are atomic (all-or-nothing), but not necessarily supporting their inclusion in multi-statement transactions.

PostgreSQL 17: True Transactional DDL

In PostgreSQL 17, DDL operations are fully transactional, meaning:

DDL statements can be included in transaction blocks alongside DML statements
Multiple DDL operations can be committed or rolled back as a single unit
Savepoints can be used within transactions that include DDL statements
If a transaction fails, all DDL changes are rolled back, leaving the database in its original state

There are only a few exceptions to this rule: operations on databases and tablespaces themselves (such as CREATE DATABASE or DROP TABLESPACE) cannot be rolled back. However, all other catalog operations are reversible.

MySQL 8: Atomic DDL

Prior to MySQL 8, DDL operations in MySQL were not atomic at all. If a DDL statement failed partway through execution—such as an ALTER TABLE operation that was adding multiple columns or indexes—the database could be left in an inconsistent, partially-modified state.

MySQL 8 introduced a feature called Atomic DDL which represents a significant improvement over previous versions but differs fundamentally from PostgreSQL's approach.

In MySQL 8, DDL statements are atomic at the statement level, meaning:

Individual DDL statements are either fully completed or fully rolled back
DDL statements implicitly commit any active transaction before execution
DDL statements cannot be part of a multi-statement transaction that can be rolled back
Crash recovery ensures statement-level atomicity

MySQL's atomic DDL is implemented through a special internal DDL_LOG table in InnoDB that tracks the creation of files and structures during DDL execution. This log is used at commit/rollback time to clean up properly, ensuring that no orphaned files or index trees remain after a crash.

It's important to note that atomic DDL is only supported in MySQL 8 InnoDB storage engine. For tables using other storage engines, partial updates may still occur.

Examples

To better understand these differences, let's run some DDLs that demonstrate how DDL transactions behave in both systems.

PostgreSQL 17

Our 1st example makes all DDL operations wrap in a transaction. When we execute the ROLLBACK command, all tables and indexes are removed as if they were never created.

-- Start a transaction block
BEGIN;

-- Create a simple table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- Add an index
CREATE INDEX idx_username ON users(username);

-- Oops! We made a mistake and want to roll back all changes
ROLLBACK;

-- Verify that the table was not created
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_name = 'users';
-- Should return no rows, as the transaction was rolled back

The 2nd example demonstrates how PostgreSQL allows partial rollbacks using SAVEPOINT, giving developers fine-grained control over schema changes.

BEGIN;

-- Create a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- Create a savepoint
SAVEPOINT after_users_table;

-- Alter the table to add a column
ALTER TABLE users ADD COLUMN email VARCHAR(100);

-- Oops! We only want to roll back the column addition
ROLLBACK TO after_users_table;

-- Add a different column instead
ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT TRUE;

-- Commit the transaction
COMMIT;

MySQL 8

In this MySQL example, despite wrapping the DDL statements in a transaction block, the ROLLBACK command does not undo the table creation. This is because each DDL statement implicitly commits the transaction before executing, making it impossible to roll back multiple DDL statements as a unit.

-- Try to use a transaction block (note: this won't work as expected for DDL)
START TRANSACTION;

-- Create a simple table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- Add an index
CREATE INDEX idx_username ON users(username);

-- Try to roll back all changes (won't work for DDL statements)
ROLLBACK;

-- Verify that the table was created despite the ROLLBACK
SHOW TABLES;
-- Will show 'users' table

However, MySQL 8 does provide statement-level atomicity:

-- This will either create all users or none
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1',
           'user2'@'localhost' IDENTIFIED BY 'password2';

-- This will either drop all tables or none
DROP TABLE IF EXISTS table1, table2, table3;

Conclusion

Let's examine the key differences between PostgreSQL 17 and MySQL 8 in how they handle DDL transactions:

Feature	PostgreSQL 17	MySQL 8
DDL Transaction Support	Full transactional DDL	Atomic DDL (statement-level only)
Multi-statement DDL Transactions	Yes	No
DDL Rollback in Transaction	Yes	No (implicit commit)
Atomicity of Single DDL Statement	Yes	Yes (InnoDB only)
Crash Recovery	Full transaction recovery	Statement-level recovery
Savepoints with DDL	Supported	Not supported
Exceptions	Database and tablespace operations	Database directory removal not part of atomic operation
Implementation Mechanism	Write-Ahead Log (WAL)	DDL_LOG in InnoDB

PostgreSQL's transactional DDL provides a higher level of safety for complex schema migrations. By wrapping multiple related changes in a transaction, administrators can ensure that the database remains in a consistent state, even if something goes wrong during the migration process. In contrast, MySQL 8 introduces atomic DDL at the statement level, meaning each individual DDL statement is executed as an all-or-nothing operation. While this is a notable improvement over earlier versions of MySQL, it still does not support multi-statement DDL transactions.

References

PostgreSQL Wiki: Transactional DDL in PostgreSQL: A Competitive Analysis
MySQL Documentation: Atomic Data Definition Statement Support
MySQL Blog: Atomic DDL in MySQL 8.0
MySQL Documentation: Statements That Cannot Be Rolled Back