Data quality SLOs are that discipline, borrowed directly from the way SRE teams run services: a small number of measured indicators per dataset, an explicit target for each, an error budget that says how much failure is tolerable before work stops, and alerts that fire on the rate at which the budget is burning rather than on every individual miss.

This post sets out the data quality SLOs we implement for customers running dbt and Airflow on PostgreSQL, ClickHouse, Snowflake, BigQuery or Databricks. It takes one dataset, an order-line fact table, and follows it through the five indicators we measure, the SQL that produces each, the SLO table that stores the targets and their owners, the burn-rate alerts that replace per-test noise, and the incident and review loop that keeps the targets honest. All the code runs on PostgreSQL 16 or later and dbt Core 1.8 or later; the ClickHouse variants are noted where they differ. Targets and figures are illustrative unless a measurement source is named.

An SLO is a promise to a consumer, so start with the consumer

The first mistake in data quality SLOs is writing them for the pipeline rather than for the people who depend on it. A freshness target of “loaded by 06:00” means nothing until someone says what breaks at 06:01. So each dataset gets a named consumer and a stated consequence: finance closes on fct_order_line at 07:00 on the third working day; the pricing service reads it every fifteen minutes; the board pack is built from it on the first Monday.

Those three consumers want different things, and the SLO records the strictest requirement each dimension actually has, with the consumer and the consequence written beside it. That is what makes the target defensible when the on-call engineer is woken at 03:00.

-- Data quality SLOs live in a table, per dataset and indicator, with owner and consequence
CREATE TABLE dq.slo (
    dataset            TEXT        NOT NULL,          -- 'marts.fct_order_line'
    indicator          TEXT        NOT NULL
                       CONSTRAINT dq_slo_indicator_chk
                       CHECK (indicator IN ('freshness','completeness','validity','consistency','lineage')),
    target             NUMERIC(6,4) NOT NULL,         -- 0.9950 = 99.5 % of evaluation windows meet the SLI
    window_days        SMALLINT    NOT NULL DEFAULT 28,
    sli_threshold      TEXT        NOT NULL,          -- '45 minutes', '0.999', '0.0010'
    consumer           TEXT        NOT NULL,          -- 'finance close', 'pricing service'
    consequence        TEXT        NOT NULL,          -- what breaks when the SLI misses
    owner_email        TEXT        NOT NULL,
    set_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT dq_slo_pk PRIMARY KEY (dataset, indicator)
);

INSERT INTO dq.slo VALUES
  ('marts.fct_order_line','freshness',   0.9950, 28, '45 minutes', 'pricing service',
   'pricing falls back to yesterday''s margins; measurable revenue impact per hour', 'data-platform-lead@example.com', now()),
  ('marts.fct_order_line','completeness',0.9990, 28, '0.999',      'finance close',
   'close reconciliation fails; controllers work manually', 'fpa-lead@example.com', now()),
  ('marts.fct_order_line','validity',    0.9990, 28, '0.0010',     'all',
   'downstream models produce nulls in margin', 'data-platform-lead@example.com', now()),
  ('marts.fct_order_line','consistency', 0.9900, 28, '0.0010',     'finance close',
   'ledger variance above tolerance blocks sign-off', 'fpa-lead@example.com', now()),
  ('marts.fct_order_line','lineage',     0.9990, 28, 'all upstream succeeded', 'all',
   'silently stale or partial upstream; every consumer affected', 'data-platform-lead@example.com', now());

Five rows of data quality SLOs for the most important table in the warehouse, and that is deliberate. Data quality SLOs are meant to be few: the twenty or thirty datasets that consumers actually depend on, five indicators each, and nothing else promised. The other four hundred models keep their dbt tests, but a failing test on an intermediate model is a build failure for the engineer, not a page, and not a breach of anything.

Five data quality SLOs indicators, and the query behind each

An SLI behind data quality SLOs is a measurement, taken on a schedule, that yields a pass or fail against a threshold. Data quality SLOs collapse the hundreds of possible checks on a dataset into five that between them cover what a consumer can be harmed by: the data is late, incomplete, malformed, inconsistent with another source of truth, or built on something that did not succeed. Each is computed by the platform from its own tables and written to one evaluation log, so the SLO attainment and the error budget are queries over that log rather than a dashboard someone assembles.

-- One evaluation log for every SLI observation
CREATE TABLE dq.sli_observation (
    dataset            TEXT        NOT NULL,
    indicator          TEXT        NOT NULL,
    observed_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    value              NUMERIC,                       -- minutes of lag, ratio, count
    passed             BOOLEAN     NOT NULL,
    detail             JSONB,                         -- what was compared, for the incident
    CONSTRAINT dq_sli_observation_pk PRIMARY KEY (dataset, indicator, observed_at)
);

-- 1. Freshness: age of the newest business event versus its own expected cadence
INSERT INTO dq.sli_observation (dataset, indicator, value, passed, detail)
SELECT 'marts.fct_order_line', 'freshness',
       EXTRACT(EPOCH FROM (now() - MAX(order_ts))) / 60                       AS lag_minutes,
       EXTRACT(EPOCH FROM (now() - MAX(order_ts))) / 60 = 0.999                                  AS passed,
       jsonb_build_object('mart_rows', m.n, 'source_rows', s.n, 'window', 'yesterday')
FROM (SELECT count(*) AS n FROM marts.fct_order_line
      WHERE order_ts >= CURRENT_DATE - 1 AND order_ts = CURRENT_DATE - 1 AND event_ts < CURRENT_DATE) AS s;

-- 3. Validity: share of rows violating the dataset's own invariants
INSERT INTO dq.sli_observation (dataset, indicator, value, passed, detail)
SELECT 'marts.fct_order_line', 'validity',
       count(*) FILTER (WHERE net_amount IS NULL OR landed_cost < 0 OR (customer_id IS NULL AND channel  'guest'))::NUMERIC
         / NULLIF(count(*), 0)                                                 AS invalid_ratio,
       count(*) FILTER (WHERE net_amount IS NULL OR landed_cost < 0 OR (customer_id IS NULL AND channel  'guest'))::NUMERIC
         / NULLIF(count(*), 0) = CURRENT_DATE - 1;

In data quality SLOs, freshness is measured against the business timestamp, not the load timestamp; a pipeline that runs on time and loads nothing new is late, and only the business timestamp shows it. Completeness compares against the source’s own count for the same window, which is the only comparison that catches a CDC connector silently dropping a partition. Validity checks the dataset’s invariants as a ratio rather than a boolean, so a single bad row in ten million does not fail the SLI while a thousand do.

-- 4. Consistency: reconciliation to an external truth, here the finance ledger for the last closed month
INSERT INTO dq.sli_observation (dataset, indicator, value, passed, detail)
SELECT 'marts.fct_order_line', 'consistency',
       ABS(m.net - g.net) / NULLIF(g.net, 0)                                   AS variance_ratio,
       ABS(m.net - g.net) / NULLIF(g.net, 0) = date_trunc('month', CURRENT_DATE - INTERVAL '1 month')
        AND order_ts <  date_trunc('month', CURRENT_DATE)) AS m,
     (SELECT SUM(amount) AS net FROM finance.gl_revenue_by_period
      WHERE period = date_trunc('month', CURRENT_DATE - INTERVAL '1 month')
        AND account_class = 'NET_REVENUE') AS g;

-- 5. Lineage integrity: every upstream model in this dataset's DAG succeeded in the run that produced it
-- (dq.run_result is loaded from dbt's run_results.json after every run)
INSERT INTO dq.sli_observation (dataset, indicator, value, passed, detail)
SELECT 'marts.fct_order_line', 'lineage',
       count(*) FILTER (WHERE status  'success')                            AS failed_upstream,
       count(*) FILTER (WHERE status  'success') = 0                        AS passed,
       jsonb_build_object('run_id', MAX(run_id),
                          'failed', jsonb_agg(node_name) FILTER (WHERE status  'success'))
FROM dq.run_result
WHERE run_id = (SELECT MAX(run_id) FROM dq.run_result WHERE node_name = 'marts.fct_order_line')
  AND node_name IN (SELECT upstream FROM dq.lineage WHERE downstream = 'marts.fct_order_line');

Lineage integrity is the data quality SLOs indicator most teams do not have and most incidents trace back to. A mart can build successfully from a staging model that failed silently and was skipped, or from a source that loaded yesterday’s file twice. Loading dbt’s run_results.json and manifest.json into two small tables after every run makes “did everything this table depends on actually succeed this time” a query, and it is the query that runs first when a consumer reports a wrong number.

Data quality SLOs attainment and error budget are queries, not opinions

With targets in one table and observations in another, data quality SLOs become arithmetic. Attainment over the window is the share of observations that passed. The error budget is the shortfall the target permits, and the budget remaining is how much of it has been spent. A freshness SLO of 99.5 percent over 28 days with an observation every fifteen minutes allows about thirteen failed observations, roughly three and a quarter hours of lateness, in the window. Once that is spent, the dataset is in breach and the policy in the next section applies.

-- Attainment and error budget remaining, per dataset and indicator, over each SLO's own window
SELECT
    s.dataset,
    s.indicator,
    s.target,
    count(o.*)                                                    AS observations,
    count(o.*) FILTER (WHERE o.passed)                            AS passed,
    round(count(o.*) FILTER (WHERE o.passed)::NUMERIC / NULLIF(count(o.*), 0), 5) AS attainment,
    round((1 - s.target) * count(o.*))                            AS budget_total_obs,
    round((1 - s.target) * count(o.*)) - count(o.*) FILTER (WHERE NOT o.passed) AS budget_remaining_obs,
    s.owner_email
FROM dq.slo AS s
LEFT JOIN dq.sli_observation AS o
       ON o.dataset = s.dataset AND o.indicator = s.indicator
      AND o.observed_at >= now() - make_interval(days => s.window_days)
GROUP BY s.dataset, s.indicator, s.target, s.owner_email
ORDER BY budget_remaining_obs ASC;

That view is the weekly data quality SLOs review. A dataset with budget to spare can take a risky migration this sprint; one with none cannot, and the change freeze is a number rather than an argument. On ClickHouse the same two tables and the same query work unchanged apart from make_interval, which becomes INTERVAL s.window_days DAY.

Alert on burn rate, not on every miss

The reason forty data quality Slack messages a day go unread is that each one is a single failed check with no sense of proportion. Data quality SLOs replace that with the SRE multi-window burn-rate alert: page when the budget is being consumed fast enough that it will be exhausted long before the window ends, and warn when it is being consumed steadily faster than the target allows. Two windows per alert, a long one for significance and a short one to confirm the problem is still happening, keep a transient blip from paging anyone and a slow bleed from being ignored.

-- Multi-window burn-rate alert for the freshness SLO (illustrative: page at 14.4x over 1h and 5m; warn at 6x over 6h and 30m)
WITH s AS (
    SELECT dataset, indicator, target, window_days FROM dq.slo
    WHERE dataset = 'marts.fct_order_line' AND indicator = 'freshness'
),
rates AS (
    SELECT
        (SELECT (1 - target) FROM s)                                                   AS allowed_fail_rate,
        AVG((NOT passed)::int) FILTER (WHERE observed_at >= now() - INTERVAL '1 hour')   AS fail_1h,
        AVG((NOT passed)::int) FILTER (WHERE observed_at >= now() - INTERVAL '5 minutes') AS fail_5m,
        AVG((NOT passed)::int) FILTER (WHERE observed_at >= now() - INTERVAL '6 hours')  AS fail_6h,
        AVG((NOT passed)::int) FILTER (WHERE observed_at >= now() - INTERVAL '30 minutes') AS fail_30m
    FROM dq.sli_observation
    WHERE dataset = 'marts.fct_order_line' AND indicator = 'freshness'
      AND observed_at >= now() - INTERVAL '6 hours'
)
SELECT
    CASE
        WHEN fail_1h / allowed_fail_rate >= 14.4 AND fail_5m  / allowed_fail_rate >= 14.4 THEN 'page'
        WHEN fail_6h / allowed_fail_rate >= 6.0  AND fail_30m / allowed_fail_rate >= 6.0  THEN 'warn'
        ELSE 'ok'
    END AS level,
    round(fail_1h / allowed_fail_rate, 1) AS burn_1h,
    round(fail_6h / allowed_fail_rate, 1) AS burn_6h
FROM rates;

For data quality SLOs, a burn rate of 14.4 over an hour means the dataset is failing at a pace that would exhaust a 28-day budget in about two days; that is worth waking someone for. A burn rate of six over six hours exhausts it in under five days and is worth a ticket in the morning. The constants are the ones Google’s SRE workbook popularised and they are a starting point, not a law; the right ones for a dataset come from looking at its own history of misses and asking which of them the consumer would have wanted to know about at 03:00.

Wiring data quality SLOs into dbt and Airflow

Data quality SLOs need no new platform. The five data quality SLOs queries are dbt models or singular tests that write to dq.sli_observation; the attainment view is a dbt model; the burn-rate query is an Airflow task that runs every five minutes and routes on its result. The one addition that matters is loading dbt’s run artefacts into the warehouse after every run, because that is what makes the lineage indicator and the incident investigation possible.

# Airflow 2.9+: burn-rate check every five minutes, routed by level; run artefacts loaded after every dbt run
from datetime import timedelta
from airflow.decorators import dag, task
from airflow.providers.postgres.hooks.postgres import PostgresHook

@dag(schedule=timedelta(minutes=5), catchup=False, max_active_runs=1, tags=["dq", "slo"])
def dq_burn_rate():

    @task
    def evaluate() -> list[dict]:
        hook = PostgresHook(postgres_conn_id="warehouse")
        rows = hook.get_records(open("/opt/airflow/sql/dq_burn_rate_all.sql").read())
        return [dict(dataset=r[0], indicator=r[1], level=r[2], burn_1h=r[3], owner=r[4]) for r in rows]

    @task
    def route(results: list[dict]) -> None:
        for r in results:
            if r["level"] == "page":
                page_oncall(service="data-platform", summary=f"{r['dataset']} {r['indicator']} burn {r['burn_1h']}x", owner=r["owner"])
            elif r["level"] == "warn":
                open_ticket(queue="data-quality", summary=f"{r['dataset']} {r['indicator']} burning {r['burn_1h']}x", owner=r["owner"])

    route(evaluate())

dq_burn_rate()

# After every dbt run: load run_results.json and manifest.json so lineage integrity is a query
dbt run --select +marts.fct_order_line
python load_dbt_artifacts.py --target-path target/ --schema dq   # writes dq.run_result and dq.lineage

The pager and ticket functions are whatever the estate already uses for data quality SLOs; the point is that the page carries the dataset, the indicator, the burn rate and the owner from the SLO table, so the person woken knows in the first line whether the pricing service is about to fall back to stale margins or a controller will have a bad morning.

The incident and the review are part of the SLO

A page on data quality SLOs is an incident and is run as one: acknowledged, mitigated, resolved, reviewed. The review has one question the pipeline-check world never asks, which is whether the SLO was right. If the freshness page fired and nobody downstream noticed the lateness, the target is stricter than the consumer needs and it should loosen.

If a consumer reported a wrong number and no indicator fired, an indicator is missing or a threshold is too loose, and the review adds or tightens it. Quarterly, the owner of every one of the data quality SLOs confirms the consumer and consequence still hold, and datasets nobody depends on any longer lose their SLOs rather than accumulating.

Where data quality SLOs go wrong

Three data quality SLOs failure modes recur. Over-instrumentation: a team promises SLOs on every model, the budget arithmetic becomes noise, and the pager fires for datasets nobody reads. Twenty to thirty datasets with data quality SLOs is the range we see work. Measuring the pipeline instead of the data: a freshness SLI on “last job run time” passes while the job loads nothing, which is why every SLI here reads the business data or an external truth. And SLOs without consequences: a target with no named consumer and no stated impact will be argued down the first time it pages, so the consequence column is not optional.

Working with MinervaDB on data quality SLOs

Data quality SLOs are the operating discipline inside our data governance consulting practice and the Data SRE layer of our data engineering work: a typical engagement selects the datasets that matter with their consumers, writes the five indicators and targets for each, wires the observation log and burn-rate alerts into the existing dbt and Airflow estate, and runs the first quarterly review with the owners. The same data quality SLOs evaluation-log pattern underpins the reconciliation tests in our metrics layer post and the freshness SLIs in our feature store architecture.

Under managed operations the burn-rate pager routes to our 24×7 teams under the standard S1 to S4 commitments, with an exhausted error budget on a finance-close dataset treated as an S2 and a lineage-integrity failure on a production model as an S1. As always: test every query, threshold and constant here against your own datasets and history before applying them to production, and keep the observation log itself under a restore posture; it is the evidence behind every promise.

Data Quality SLOs: 5 Proven Indicators, Error Budgets and Burn-Rate Alerts on dbt and Airflow appeared first on MariaDB.org

In this Q&A, Avi Vallarapu, CEO of HexaCluster, explains what the partnership changes for organizations planning an Oracle migration, why a database migration assessment is the single most valuable step, and how a joint engagement runs from first assessment through long-term support.

Quick answer: what is the Percona and HexaCluster partnership?

Percona and HexaCluster is a joint database migration partnership that moves organizations off Oracle, SQL Server, DB2 and Sybase ASE onto Percona-supported open source databases, primarily PostgreSQL. HexaCluster runs the assessment and migration engineering, using its own DMAT and HexaRocket tooling. Percona runs the resulting database long term through its distributions, operators and support.

• Problem it solves: disconnected migration tools and skipped assessments, which is where most Oracle migrations fail
• What HexaCluster does: database migration assessment, automatic schema conversion, data migration, and live replication, with validation & rollback
• What Percona does: the open source database destination, plus operators, distributions and expert support
• Typical use case: Oracle, SQL Server, DB2 or Sybase ASE exits, data center exits, and hybrid cloud migrations

Short version: HexaCluster gets you to open source. Percona keeps you running on it. Together, that’s one path from a proprietary database to a fully supported open source one.

Q: What does HexaCluster do differently?

Avi: HexaCluster comes from the same place Percona does. We’re open source contributors first. Our team has contributed to more than 50 popular PostgreSQL extensions, and over 90 percent of what we build is open source.

The best known example is Ora2Pg, the most widely used open source Oracle-to-PostgreSQL migration tool, created and maintained by our team. Google Cloud, Microsoft Azure and AWS have all pointed customers to it for complex schema conversions. We’ve also built extensions that emulate Oracle and SQL Server behavior inside PostgreSQL, making application code portable instead of rewritten.

That work taught us where migrations actually break, which is where HexaRocket came from. Almost every existing migration tool is partial: some handle schema conversion, some handle data migration and change data capture, very few do both, and almost none give a credible rollback. Customers end up stitching disconnected tools together, and the seams between them are exactly where migrations fail.

HexaRocket runs the whole path in one platform: schema migration with validation, data migration with validation, change data capture for live replication, and reverse replication so you can roll back if something goes wrong. Rollback is what lets a CIO approve a cutover date.

Q: Why does the partnership with Percona matter?

Avi: Because neither company covers the whole journey alone. Percona is the master of open source on the services and product side: Percona Distribution for PostgreSQL, Percona MySQL, Percona Software for MongoDB, the Percona Operators, and enterprise support. That’s hard to replicate.

What Percona hasn’t done is move workloads off Oracle, SQL Server, DB2 or Sybase in the first place. That’s the gap HexaCluster bridges.

Put simply: HexaCluster gets you to open source, Percona keeps you running on it. That’s the whole journey covered by two open source companies, not a general-purpose systems integrator learning your database on your budget. It works in reverse for us too: when customers ask what they’re landing on, pointing at Percona’s distribution, with built-in transparent data encryption, makes that conversation simpler.

Q: How did this relationship start?

Avi: It’s not new. I joined Percona in 2018 to start and lead the PostgreSQL practice there. The relationship continued through everything I’ve built since, first through MigOps and now through HexaCluster. This partnership formalizes a working relationship that’s existed for years.

Q: What’s actually driving companies to do this right now?

Avi: Three things, stacking on top of each other.

Cost optimization. Commercial database licensing is one of the largest and least defensible line items in most infrastructure budgets. When finance looks for structural savings, this is where they land.

Scale in the AI era. Organizations are scaling data volumes faster than planned. Per-core commercial licensing turns growth into a penalty; open source turns it into a hardware conversation instead of a procurement one.

Features and performance. PostgreSQL isn’t the pragmatic compromise anymore. It’s the target: extensions, JSON, logical replication, partitioning, and now transparent data encryption through Percona’s work on the Distribution. The question has shifted from whether to adopt PostgreSQL to how fast the rest of the estate can follow.

Q: Where do companies most often go wrong in migrations?

Avi: They skip the assessment.

Migrations fail long before cutover, at the point someone estimates the effort without knowing what’s actually inside the databases. Some carry genuine complexity: heavy PL/SQL, embedded business logic, dependencies nobody has read in a decade. Others are almost trivial. Without an assessment, you can’t tell those apart, so both estimates are wrong.

Two mistakes compound that one: choosing a partner who treats your migration as a learning exercise, and choosing disconnected tools that leave the integration risk with you. Assessment first. Everything else gets easier after that.

Q: What does a Percona and HexaCluster engagement look like for a customer?

Avi: It’s one team across the full lifecycle. The engagement starts the moment someone first thinks about migrating: assessment, then roadmap and sequencing, then schema and data migration with validation at every stage, then cutover with rollback available, then production, then support for as long as the database lives.

The customer isn’t managing a handoff between an assessment vendor, a tooling vendor, a migration integrator and a support provider. That’s where cost, delay and blame usually live. The result is cost-effective and easier, because the people who build the tools are the same people who support the databases afterward.

Q: Can you make that concrete? What has this looked like in practice?

Avi: Two examples.

A leading Middle East bank had a very large Oracle footprint. We assessed 120 Oracle databases in 30 minutes, then handed them a complete migration roadmap within two days. Individual databases ranged from 30 minutes to four days of work to them out of Oracle each, roughly 40 person-days across the whole estate. We also ran a proof of concept in two days and showed a working migration, not just a demo.

A global enterprise moved 800 TB of databases from Oracle Standard Edition to Amazon Aurora PostgreSQL, covering more than 6,000 customers, as part of an acquisition strategy, completed over two years with validation and direct customer coordination throughout.

Both examples share the same pattern: unknowns get resolved at the start, not discovered in the middle.

Q: So what does the customer actually get out of this partnership?

Avi: The most valuable thing is that the customer doesn’t have to invent migration from scratch. It’s a solved problem they can buy: no unknowns, because the assessment tells you what’s in the estate before you commit to a plan; validation at every stage, so schema and data are verified, not assumed; rollback through reverse replication, which is what makes teams willing to schedule a cutover; and committed timelines, which is often the real blocker for leadership.

Q: If I’m a CIO reading this, what should I be asking myself?

Avi: Three questions, in this order.

Did I pick the right database to migrate first? Starting with the hardest one is the most common self-inflicted wound. It burns budget and demoralizes leadership before any value is delivered.

Have we actually assessed our databases, so we know which ones are low-hanging fruit and which need real engineering? Without that, you’re guessing.

Did we pick the right tooling: a platform genuinely built for end-to-end migration, not a set of disconnected tools stitched together? Get those three right, and the migration is largely an execution problem.

Q: What are the biggest migration goals you’re seeing?

Avi: Four exits dominate the conversations we’re in: Oracle, SQL Server, DB2 and Sybase ASE. Sybase ASE deserves a special mention, since customers there are being pushed toward SAP or SQL Server, trading one proprietary database for another. Through this partnership, PostgreSQL becomes a realistic, cheaper target instead.

Many of these tie into data center exits and hybrid cloud programs, where a single platform pays off most. We cover homogeneous migrations (PostgreSQL to PostgreSQL, MySQL to MySQL, any platform to any platform), heterogeneous migrations (Oracle, SQL Server, DB2 and Sybase ASE to PostgreSQL), and same-engine platform moves, like Azure Managed SQL to SQL Server on VMs. A data center exit is rarely one type of migration. It’s usually all three at once, under a deadline set by a lease or a contract.

Q: What are the next steps for someone reading this?

Avi: Start with the assessment. It’s fast, it’s concrete, and it’s the step that de-risks everything after it.

We invite organizations to run a DMAT database migration assessment across their estate and get back a detailed roadmap: which databases to move first, what effort each one takes, and what the cost savings look like. From there, Percona and HexaCluster can take it through to production and support it long term.

You don’t need to commit to a migration to find out what one would cost you. That’s the point of assessing first.

To start an assessment or discuss an Oracle, SQL Server, DB2 or Sybase ASE exit, contact your Percona representative or reach out to HexaCluster directly.

The post Percona and HexaCluster: Faster, Safer Oracle Migration appeared first on Percona.

Percona and HexaCluster: Faster, Safer Oracle Migration appeared first on MariaDB.org

Continue reading “AWS renews Diamond sponsorship of MariaDB Foundation for a fourth consecutive year”

AWS renews Diamond sponsorship of MariaDB Foundation for a fourth consecutive year appeared first on MariaDB.org

AWS renews Diamond sponsorship of MariaDB Foundation for a fourth consecutive year appeared first on MariaDB.org

The current Percona 17 Docker documentation uses percona/percona-distribution-postgresql:17.11, requires POSTGRES_PASSWORD, and exposes PostgreSQL on container port 5432. Docker Desktop is the recommended way to obtain Docker Engine, the CLI, and Compose on Windows. Percona Docker guide · Docker Compose installation

1. Create the Compose project

Start Docker Desktop, open PowerShell, and make a uniquely named lab directory. The timestamp gives Compose a new project name, so an old named volume cannot silently turn a rerun into a test of stale data.

$ErrorActionPreference = "Stop"

docker version
if ($LASTEXITCODE -ne 0) { throw "Docker Engine is unavailable." }

docker compose version
if ($LASTEXITCODE -ne 0) { throw "Docker Compose is unavailable." }

$lab = "percona-restore-lab-{0}" -f (Get-Date -Format "yyyyMMdd-HHmmss")
New-Item -ItemType Directory -Path $lab | Out-Null
Set-Location $lab
New-Item -ItemType Directory -Path backups | Out-Null

Save this as compose.yaml. Separate named volumes prevent the recovery target from sharing source data; the archive will be copied through the Windows filesystem.

services:
 source:
 image: percona/percona-distribution-postgresql:17.11
 environment:
 POSTGRES_USER: drill
 POSTGRES_PASSWORD: lab-only-change-me
 POSTGRES_DB: drill
 ports:
 - "127.0.0.1:55432:5432"
 volumes:
 - source-data:/data/db
 healthcheck:
 test: ["CMD-SHELL", "pg_isready -q -U $${POSTGRES_USER} -d $${POSTGRES_DB}"]
 interval: 5s
 timeout: 5s
 retries: 12
 start_period: 10s

 target:
 image: percona/percona-distribution-postgresql:17.11
 environment:
 POSTGRES_USER: drill
 POSTGRES_PASSWORD: lab-only-change-me
 POSTGRES_DB: restore_drill
 ports:
 - "127.0.0.1:55433:5432"
 volumes:
 - target-data:/data/db
 healthcheck:
 test: ["CMD-SHELL", "pg_isready -q -U $${POSTGRES_USER} -d $${POSTGRES_DB}"]
 interval: 5s
 timeout: 5s
 retries: 12
 start_period: 10s

volumes:
 source-data:
 target-data:

This password is disposable; use proper secret handling outside a lab. Percona’s v17 Dockerfile sets /data/db as PGDATA. Compose does not otherwise equate “running” with “ready,” so health checks matter. Percona v17 Dockerfile · Docker startup-order guidance

docker compose config --quiet
if ($LASTEXITCODE -ne 0) { throw "Compose validation failed." }

docker compose pull
if ($LASTEXITCODE -ne 0) { throw "Image pull failed." }

docker compose up -d --wait --wait-timeout 60 source
if ($LASTEXITCODE -ne 0) { throw "Source failed to become healthy." }

$serverVersion = docker compose exec -T source psql -U drill -d drill -Atc "SELECT version();"
if ($LASTEXITCODE -ne 0) { throw "Version query failed." }
$serverVersion

Record the actual version string. Do not infer it from the tag alone.

2. Seed known data and prove the baseline

@'
CREATE TABLE orders (
 id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 customer text NOT NULL,
 amount numeric(10,2) NOT NULL CHECK (amount >= 0)
);
INSERT INTO orders (customer, amount)
VALUES ('Ada', 10.25), ('Grace', 20.00), ('Linus', 30.00);
'@ | docker compose exec -T source psql -U drill -d drill -v ON_ERROR_STOP=1
if ($LASTEXITCODE -ne 0) { throw "Seed step failed." }

$baseline = docker compose exec -T source psql -U drill -d drill -Atc "SELECT count(*) || '|' || sum(amount) FROM orders;"
if ($LASTEXITCODE -ne 0 -or $baseline.Trim() -ne "3|60.25") {
 throw "Baseline check failed: $baseline"
}

Pass: the command returns 3|60.25. Anything else is a stop condition.

3. Create and export the archive

pg_dump -Fc creates a custom-format archive for pg_restore; PostgreSQL describes this format as flexible and compressed by default. Write it inside the Linux container, inspect its table of contents, and copy it to Windows:

docker compose exec -T source pg_dump -U drill -d drill -Fc -f /tmp/drill.dump
if ($LASTEXITCODE -ne 0) { throw "pg_dump failed." }

docker compose exec -T source pg_restore --list /tmp/drill.dump
if ($LASTEXITCODE -ne 0) { throw "Archive inspection failed." }

docker compose cp source:/tmp/drill.dump .backupsdrill.dump
if ($LASTEXITCODE -ne 0) { throw "Archive export failed." }

$backup = Get-Item .backupsdrill.dump -ErrorAction Stop
if ($backup.Length -le 0) { throw "Backup file is empty." }
$backup | Select-Object FullName, Length, LastWriteTime
Get-FileHash .backupsdrill.dump -Algorithm SHA256

Copying avoids routing a binary archive through PowerShell’s output pipeline. docker compose cp transfers files between a service container and the local filesystem. The hash identifies this particular file; it is not a universal expected value. Two clean image-side validation runs each produced a 2,360-byte archive but different SHA-256 hashes because the archives contain run-specific metadata. Record the hash so the same file can be checked after another transfer. PostgreSQL pg_dump · Docker Compose cp

4. Inject failure, then recover

This is the destructive step. Confirm that the current timestamped lab directory is disposable before continuing.

docker compose exec -T source psql -U drill -d postgres -v ON_ERROR_STOP=1 -c "DROP DATABASE drill;"
if ($LASTEXITCODE -ne 0) { throw "Failure injection command failed." }

# Windows PowerShell 5.1 treats redirected native stderr as an error record.
# This one command is expected to fail; capture it, then restore strict handling.
$previousErrorActionPreference = $ErrorActionPreference
try {
 $ErrorActionPreference = "Continue"
 $missingDb = docker compose exec -T source psql -U drill -d drill -c "SELECT 1;" 2>&1 | Out-String
 $missingDbExit = $LASTEXITCODE
} finally {
 $ErrorActionPreference = $previousErrorActionPreference
}
$missingDb.Trim()
if ($missingDbExit -eq 0 -or $missingDb -notmatch 'databases+"drill"s+doess+nots+exist') {
 throw "Unexpected failure probe result: $missingDb"
}

docker compose stop source
if ($LASTEXITCODE -ne 0) { throw "Could not stop the failed source." }

docker compose up -d --wait --wait-timeout 60 target
if ($LASTEXITCODE -ne 0) { throw "Target failed to become healthy." }

docker compose cp .backupsdrill.dump target:/tmp/drill.dump
if ($LASTEXITCODE -ne 0) { throw "Archive import failed." }

docker compose exec -T target pg_restore -U drill --dbname=restore_drill --clean --if-exists --single-transaction /tmp/drill.dump
if ($LASTEXITCODE -ne 0) { throw "Restore failed." }

Failure check passes only when the post-drop connection fails specifically because drill does not exist. The regular expression tolerates whitespace because Windows PowerShell 5.1 can wrap native error text across lines. Stopping the now-unhealthy source keeps it out of the target’s readiness check. The target has a deliberately different database name, so the restore omits --create and loads directly into restore_drill. --clean --if-exists makes replacement explicit without missing-object noise. For this small lab, --single-transaction makes the restore all-or-nothing and implies exit-on-error. PostgreSQL pg_restore

5. Verify the recovered state

$verifySql = @'
SELECT concat_ws('|',
 (SELECT string_agg(id::text || ':' || customer || ':' || amount::text, ',' ORDER BY id) FROM orders),
 (SELECT count(*) FROM pg_constraint WHERE conrelid = 'public.orders'::regclass),
 (SELECT is_identity FROM information_schema.columns
 WHERE table_schema = 'public' AND table_name = 'orders' AND column_name = 'id'),
 (SELECT last_value || ':' || is_called FROM public.orders_id_seq)
);
'@

$restored = $verifySql | docker compose exec -T target psql -U drill -d restore_drill -At -v ON_ERROR_STOP=1
$expected = "1:Ada:10.25,2:Grace:20.00,3:Linus:30.00|2|YES|3:true"

if ($LASTEXITCODE -ne 0 -or $restored.Trim() -ne $expected) {
 throw "Restore verification failed: $restored"
}
"PASS: rows, constraints, identity definition, and sequence state recovered."

The drill passes only if readiness succeeds, the baseline matches, the archive is nonempty and parseable, the expected missing-database error is observed, pg_restore exits zero, and the final row, constraint, identity, and sequence signature matches exactly.

When a check fails

Treat a thrown error as evidence, not an invitation to skip ahead. If the source never becomes healthy, run docker compose ps and docker compose logs --no-color source; initialization and permission errors normally appear there. A “port is already allocated” error concerns the host side of 55432 or 55433, not container port 5432. If pg_restore --list fails, discard the archive and create a new one rather than attempting a hopeful restore.

Ownership errors usually mean that source and target roles differ. This lab intentionally creates the drill superuser in both containers, avoiding that variable; a production restore needs an explicit role and privilege plan. A final signature mismatch is also a failed restore even when pg_restore returned zero. Preserve the logs and archive, identify the difference, fix the procedure, and repeat from a new timestamped project. Do not label the backup usable until every check passes.

PowerShell and Docker Desktop gotchas

• Use docker compose, not the legacy docker-compose executable.
• Run from the directory containing compose.yaml. Keep Windows paths such as .backups separate from container paths such as /tmp.
• Keep $LASTEXITCODE checks immediately after native commands. -T disables pseudo-TTY allocation for noninteractive execution.
• Avoid PowerShell backtick line continuations: an invisible trailing space can break them.
• If host port 55432 or 55433 is occupied, change only the left side of that mapping. The container port remains 5432.
• docker compose down preserves named volumes. For this lab only, docker compose down -v deletes both disposable data volumes; never apply -v casually elsewhere. Docker Compose down

What this drill does not prove

This is a logical, single-database restore—not physical backup, point-in-time recovery, replication, failover, or a performance test. pg_dump does not capture cluster-wide roles and tablespaces; PostgreSQL directs those cases to pg_dumpall. A backup kept on the same laptop is not disaster-resilient. The lab also does not exercise application reconnects, large datasets, extensions, encryption, or remote object storage. Restore only archives from trusted sources: PostgreSQL warns that restoring can execute code chosen by source superusers. PostgreSQL pg_dump limitations and warning

Validation scope

On September 5, 2026, this workflow completed one clean, AI-assisted automated run on the author’s Windows 11 Pro 25H2 laptop (build 26200.9168), using Docker Desktop 4.89.0, Docker Engine 29.7.2, Compose 5.5.0, and Windows PowerShell 5.1.26100.9168. The run used a fresh Compose project and separate new source and target data volumes. Linux amd64 containers from percona/percona-distribution-postgresql:17.11 reported PostgreSQL 17.11 — Percona Server for PostgreSQL 17.11.1.

Plain docker compose pull succeeded. Both services became healthy within the article’s 60-second timeout. The source returned the 3|60.25 baseline, and pg_restore --list parsed the exported 2,360-byte archive. Both docker compose cp transfers passed through the Windows filesystem. The Windows archive and imported target copy had the same SHA-256: bb521cadcca1547c39d3d3a161a65228300a68f0905badf09b79011236c5311d.

After the source database was dropped, its connection probe returned exit 2 with the expected missing-database error. The source was stopped, pg_restore into the clean target exited zero, and the recovered signature matched exactly: 1:Ada:10.25,2:Grace:20.00,3:Linus:30.00|2|YES|3:true.

Execution used a PowerShell evidence wrapper around the article’s Compose layout and database commands, adding explicit project scoping, container ownership checks, command-output capture, and the imported-archive hash comparison. The expected-error probe was corrected for Windows PowerShell 5.1: temporary Continue handling preserves the native error and exit code, and a whitespace-tolerant pattern accepts line-wrapped error text. The saved transcript, command outputs, status record, and backup support these results. This was automated execution on the author’s Windows laptop; no manual command-by-command execution is claimed.

AI-assistance disclosure

AI assistance was used to structure this lab, cross-check commands against official documentation, edit the prose, and prepare and execute the automated PowerShell validation on the author’s Windows laptop. The author remains responsible for reviewing the evidence, correcting the article, and approving it for publication.

This post is part of the Percona Community Writers Program.

Prove the Backup: A Percona Distribution for PostgreSQL Restore Drill on Windows appeared first on MariaDB.org

What has happened so far

After MariaDB Corp. acquired Codership OY—and with it, Galera Cluster for MySQL—in May 2025 [ 1 ], then announced the discontinuation of support for Galera Cluster for MySQL 8.4 at the end of September 2026 [ 2 ], and subsequently stated that it would no longer support Galera Cluster (for MariaDB!) in the Community Edition [ 3 ], there was a loud outcry from the community. MariaDB Corp. then made a partial retreat (“We’ve thoroughly considered your feedback and decided that now is not the time for a major change,”). A clear commitment to continued support for Galera Cluster in the Community Edition sounds different.

Shortly thereafter, the MariaDB Foundation announced that it did not intend to fork Galera Cluster (“Thus the Foundation does not see a need for forking Galera and does not support or encourage such action.”) [ 4 ].

So, until Friday (2026-09-10), the future of Galera Cluster for MySQL was very uncertain!

Percona’s announcement yesterday

But fortunately, there’s still Percona with its Percona XtraDB Cluster (PXC), which is an improved branch/fork of both MySQL and Galera Cluster.

And Percona finally announced the release of Percona XtraDB Cluster 9.7 on Friday (September 10, 2026) [ 5 ]. For us, this was the long-awaited and hoped-for signal from Percona!

It would be nice if Percona could provide a clearer statement regarding Galera Cluster so that we can better plan for it in terms of timing…

Installation

Of course, we couldn’t resist and immediately downloaded the latest release [ 6 ] and set up a Percona XtraDB test cluster.

Due to our standard Galera installation process, we had to make a few adjustments here and there to ensure that the Percona XtraDB Cluster ran smoothly.

We’ve described the details here: Migration from MySQL Galera Cluster 8.4 to Percona XtraDB Cluster 9.7

Further tests will follow in the coming days and weeks…

Now would actually be a good time for commercially oriented Galera Cluster users to sponsor Percona…? For a vibrant and healthy MySQL open-source ecosystem: The Future of the MySQL Ecosystem.

Sources

• Percona XtraDB Cluster 9.7 Documentation

This page was translated using deepl.com.

Percona releases Galera Cluster Version 9.7 appeared first on MariaDB.org

The Problem

Suppose we need to find the customers in a certain age band who made a certain number of orders in the last 12 months. We have both these values in the same table, and we have an index covering both columns and nothing else. Do you expect the following query to use the index?

SELECT id, email
    FROM customer
    WHERE
        (date_of_birth BETWEEN DATE '1994-01-01' AND DATE '2004-01-01')
        AND (last_year_orders BETWEEN 5 AND 10)
;

Depending on which database you use, there are a few options:

• The index isn’t used at all.
• The index is used for the first column stored in the index, but to read the second the database makes additional reads from the data. Depending on your data distribution, this is probably slower than a full table scan (which is usually slow, unless the table is very small).
  • If the first column in the index has lower selectivity than the second, the query execution will be even slower than it should be.
• The whole index is scanned. This is probably as slow as a full table scan, or slightly faster.

Why? A B-Tree index optimises queries because it’s an ordered data structure. But the order of values doesn’t help making a search on multiple ranges. For more information about what a B-Tree can do, see Query Optimisation: Using indexes for WHERE with multiple conditions.

I call queries like this Double-Range Queries.

A Solution: GIS Data

Think of the data in the only way that feels natural for a human: a point chart where the axes represent the date of birth and the number of orders in the last year. The following image, created by Nano Banana 2, represents this way of visualising the data:

Each point represents a customer, and therefore a point we need to select. Conceptually, to select the right points we only need to draw a rectangle on the chart:

Now the concept should be clear, but we need to write the SQL. Let’s see the table definition, and then comment it:

CREATE TABLE customer (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date_of_birth DATE NOT NULL,
    last_year_orders INT NOT NULL,
    -- ...probably more columns here...
    dob_orders_chart POINT GENERATED ALWAYS AS (
        POINT(TO_DAYS(date_of_birth), last_year_orders)
    ) STORED NOT NULL,
    SPATIAL INDEX sp_idx_dob_orders (dob_orders_chart)
) ENGINE=InnoDB;

Things to note in the above snippet:

• We added a column called dob_orders_chart.
• The new column could have been of GEOMETRY type. That would work. But all the values we’re going to have are points, so we choose the more efficient POINT type instead.
• dob_orders_chart is a generated column. This means that when a row is inserted, or one of (date_of_birth, last_year_orders) is updated, a new value for dob_orders_chart is calculated automatically. Users cannot change these values manually.
• While last_year_orders is an INTEGER, date_of_birth is a DATE and its raw values cannot be used as coordinates for GIS data. The problem is easy to solve: TO_DAYS(date_of_birth) returns an INTEGER, so it’s a valid coordinate. More specifically, TO_DAYS() returns the number of days passed from the beginning of the Gregorian calendar (Friday, 15 October 1582).
• We index this column with a SPATIAL INDEX, which uses an R-Tree data structure. An R-Tree index partitions a space in nested areas with boundaries that depend on data distribution.

Now let’s see the query that will find the desired data from the above table:

SELECT id
    FROM customer
    WHERE MBRWITHIN(
        dob_orders_chart,
        ST_GEOMFROMTEXT(
            CONCAT(
                'POLYGON((',
                    TO_DAYS('1994-01-01'), ' 5, ',
                    TO_DAYS('2004-01-01'), ' 5, ',
                    TO_DAYS('2004-01-01'), ' 10, ',
                    TO_DAYS('1994-01-01'), ' 10, ',
                    TO_DAYS('1994-01-01'), ' 5',
                '))'
            )
        )
    )
;

Things to note about the above query:

• MBRWITHIN() can often be described as an approximate function, because MBR stands for Minimum Bounding Rectangle. For example, if you use it with a circle, it will return all points that are contained in the smallest square that can contain the circle – not just the circle itself. But in our case it’s used with a rectangle that is aligned with the space axis, so this function will return precise results.
• ST_GEOMFROMTEXT() accepts a text representation of a geometric shape and its coordinates. To build this representation, we can use CONCAT().
• We have 5 coordinates, and that is intended. The last coordinate matches the first, and it’s there to close the shape.

To concatenate strings, we can also use SFORMAT():

ST_PolyFromText(
    SFORMAT(
        'POLYGON(({0} {1}, {2} {1}, {2} {3}, {0} {3}, {0} {1}))',
        TO_DAYS('1994-01-01'), 
        5,
        TO_DAYS('2004-01-01'), 
        10
    )
)

These syntaxes are equivalent. Just use the one that you and your team find easier to understand. For more details, see How to compose strings in MariaDB.

Multi-Dimensional Charts

Most databases, including MariaDB and MySQL, can’t deal with multi-dimensional geospatial data.

Up to 4 Dimensions on PostgreSQL

PostgreSQL users are luckier here: PostGIS supports multi-dimensional data, and can include up to 4 dimensions in a GiST index, thanks to the idx_spatial_nd operator class. The syntax to use for creating such an index is the following:

CREATE INDEX idx_spatial_nd 
    ON customer_3d 
    USING GIST (geom gist_geometry_ops_nd)
;

PostGIS doesn’t require us to specify the list of columns to index.

More Than 3 Dimensions on Other Databases

If we have more than two range conditions and we use a database that limits spatial indexes to 2 dimensions (or doesn’t support GIS at all), we need to find an alternative solution.

In several cases, you can come up with a math expression that turns the columns you need to search into a single score of type INTEGER or FLOAT. However, this is beyond the scope of this article. If you want me to cover this topic in a future article, leave a comment here.

Conclusions

We’ve discussed the type of queries I call Double-Range queries and why they can’t make an optimal use of a B-Tree index. We’ve seen a solution that works well in most cases. Our example even covers the case when one of the ranges is a date. I have briefly mentioned which solutions exist for queries with more than two ranges – this might be a topic for a future article.

Federico Razzoli

Optimising Multi-Range Queries with Logical Charts appeared first on MariaDB.org

A similar realization came to me a few months ago, although this time was pretty different. Think of Percona Monitoring and Management (PMM) as the tennis racket and Percona Server for MySQL 8.0 as the tennis court. The measured result was a 95.0% reduction in average query time, from 54.17 ms to 2.71 ms. The only thing that changed was my perspective, not the setup.

The Setup

Very basic setup. One Percona Server for MySQL 8.0 primary, two EC2 read replicas, and PMM 2.x running in a Docker container, with QAN through performance_schema. Nothing special. The symptom was also standard: an internal analytics service joined three tables (orders, order_items, and users) and polled the primary every few minutes for recent order activity. None of the tables were big.

The screenshots and measurements in this walkthrough come from a lab environment created to reproduce the query pattern, not from a production incident.

In the baseline window, the query ran at 0.16 QPS and averaged 54.17 ms. It also lined up closely with CPU spikes and occasional replica-lag warnings.

My “power play” instinct kicked in, and I thought about increasing the resources for the primary, or moving the analytic workload totally over to a replica. Both ideas had potential, but at the end of the day, they would just hide the problem. None of the ideas actually would have helped me understand what the problem was.

Reading the Court: QAN Sorted by Load, Not Raw Query Time

The first real change in my mindset came from how I was sorting Query Analytics. The usual way to do this is to look for the query with the maximum or mean query time, and sort the results that way.

For this reason, PMM’s QAN dashboard has a Load column. Load is the average number of simultaneous executions of a query. Instead of looking at how long a query takes when executed, a better way to look at this is how much of the server’s attention is this query consuming, on average, right now. When sorting by Load, the three-table join jumped to the top. An average execution time of 54.17 ms does not look problematic until you realize the query is executed multiple times a minute, over and over again, continuously throughout the day.

That’s the first lesson: If a query never stops running, a query that is “fast enough” can still be your biggest rival.

Figure 1: PMM Query Analytics showing the three-table join at the top of the workload, with an average query time of 54.17 ms before optimization.

Film Review Before Stepping on the Court: EXPLAIN

Once QAN pointed at the query, the next step was to observe EXPLAIN, as a coach would observe game film: review it before making any changes in production.

The query, in simplified form, looked like this:

SELECT o.id, o.created_at, o.total_amount, u.email,
oi.product_id, oi.quantity, oi.price
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= NOW() - INTERVAL 15 MINUTE
ORDER BY o.created_at DESC;

EXPLAIN confirmed this was a perfect example for a full table scan on orders (type: ALL). MySQL had to read every row to filter for the time window, then it used a nested loop to join order_items and users, which exacerbated the original issue.

orders:
type: ALL
key: NULL
rows: 199875
Extra: Using where; Using filesort
users:
type: eq_ref
key: PRIMARY
order_items:
type: ref
key: idx_order_items_order_id

Thanks to PMM’s rows examined to rows sent ratio, the query’s inefficiency actually showed itself where it could not in raw EXPLAIN output. The query would even return a reasonably sized and useful result. The issue was that the ratio of rows examined and rows sent to the client was totally lopsided. Each execution examined about 201,370 rows while returning 819 rows, or 245.87 rows examined for every row sent. This would not trigger any system alerts, but it showed that the database was doing SO much work for a trivial result. I’m calling this “invisible” inefficiency, and the more the query becomes used, the more detrimental it becomes.

Figure 2: Before optimization, each execution examined about 201,370 rows while returning 819 rows, or 245.87 rows examined for every row sent. PMM also recorded a full scan and no index used on every execution.

The Fix: Small, Targeted Adjustments

This was actually the best example of tennis in the real world for me. The tendency in this sort of query optimization is to swing harder (a bigger instance, more read replicas, etc.), but this was more like lightly changing the grip by a few grams.

Two index changes, one query change, and that was it.

A composite index on orders that matches the access pattern:

CREATE INDEX idx_orders_created_at_id
ON orders (created_at, id);

The composite index on (created_at, id) allowed MySQL to use a range scan on the created_at predicate instead of scanning the entire orders table. In EXPLAIN, the orders access changed from type: ALL with roughly 199,875 rows estimated to type: range using idx_orders_created_at_id. The id column does not make this access covering, because the query still needs total_amount and user_id from the orders row.

A covering index on order_items:

CREATE INDEX idx_order_items_covering
ON order_items (order_id, product_id, quantity, price);

The covering index on (order_id, product_id, quantity, price) allowed MySQL to satisfy the join and requested item columns from the index itself. EXPLAIN reported Using index.

The last change was to split the users lookup from the main join. The first query returned user_id, and a second query used WHERE id IN (…) to fetch the email addresses from users.

None of these improvements required any downtime, schema redesigns, or having to talk about sizing the instances. Each improvement was the kind of adjustment where it looked like, on its own, nothing particularly important was actually done.

orders:
type: range
key: idx_orders_created_at_id
rows: 40
Extra: Using index condition; Backward index scan
users:
type: eq_ref
key: PRIMARY
order_items:
type: ref
key: idx_order_items_covering
Extra: Using index

The Results

The same normalized query remained at the top of the selected QAN workload after optimization, now running at 0.99 QPS and averaging 2.71 ms per execution.

Figure 3: The same normalized query after optimization, now averaging 2.71 ms per execution in the post-index sampling window.

Figure 4: After the changes, average query time fell to 2.71 ms and rows examined dropped to about 1,460 per execution. The rows-examined-to-rows-sent ratio fell to 1.67, and executions used range access instead of a full scan.

Regarding the query, QAN indicated that average query time decreased from 54.17 ms to 2.71 ms, yielding a 95.0% reduction and making the query about 20 times faster. Rows examined per query fell from 201.37k to 1.46k, a 99.27% reduction, or about 138 times fewer rows examined. The ratio of rows examined to rows sent fell from 245.87 to 1.67, a 99.32% reduction. Full scan and no index used disappeared from the optimized sampling window, while Select Range reached 1.00 per query.

Improvements were the result of a number of incremental changes rather than a single large scale change. Composite indexing of orders and covering indexing of order_items helped rationalize the query to a considerable extent. Together, the changes rationalized the query and had an impact that would otherwise have been easy to chase with additional hardware.

Shifting Your Mindset

Beyond the specific indexes, the key point of this case study is how the investigation was framed at the outset.

QAN was sorted by Load. This let me identify the query that consumed the most attention from the primary. Unlike the query that had the most problematic single execution time.

Taking time to analyze EXPLAIN and the rows-examined-to-rows-sent ratio before implementing the fix let me focus on the cause of the problem. A full scan on orders. Instead of a symptom, which was a CPU spike with a concurrent lag on the replica.

By implementing two specific indexes and a small query rewrite, I ended up with measurable, attributable, and repeatable results. And if something was negatively impacted after the implementation of the fix, I would be able to identify which specific change caused the problem.

The engineer that looks as if they are moving the least in a tennis match, because they have already anticipated the next three shots, is the one doing the most invisible work, and that is exactly what this kind of production database work looks like from the outside.

PMM didn’t perform the optimization, but it made the invisible work just visible enough to act on the changes, and the rest was patience, and a willingness to implement incremental changes rather than larger changes.

About the author

Shivank Pandey is one of two engineers managing PMM at his company. He works with Percona Server for MySQL 8.0 in a primary-replica setup on EC2 and writes about database observability and query optimization.

This post is part of the Percona Community Writers Program.

The Perfect Serve: What Tennis Taught Me About Reading PMM Like a Coach Instead of Guessing appeared first on MariaDB.org

PXC has a variable for exactly that case, pxc-encrypt-cluster-traffic, which handles SSL encryption for inter-node traffic, including State Snapshot Transfer (SST), Incremental State Transfer (IST), and the group communication the nodes use for replication.

The variable is not dynamic, and turning it on normally costs a full cluster restart since the node that encrypts traffic listens on ssl:// while its peers are still on tcp://. If a node joins the cluster with TLS enabled while remaining nodes don’t, the restarting node fails to reach out to the other peers with the following error:

2026-09-02T02:16:02.359992Z 0 [Note] [MY-000000] [Galera] Failed to establish connection: wrong version number

This is a common OpenSSL error seen when a client sends encrypted TLS traffic to a server that expects unencrypted plaintext.

Starting with PXC 8.0.28 and in all PXC 8.4 versions, there is a way to work around the full cluster restart, which relies on the Galera socket.dynamic option. In this post I’ll go through how this option works and how it can be used to enable TLS without downtime.

The socket.dynamic Option

This provider option lets a node establish communication with other peers using both encrypted and plaintext connections. The instance will initially open an encrypted connection, and if it fails since the other nodes are not using TLS, it will retry to establish an unencrypted connection.

The Joiner error log will show the following sequence:

2026-09-02T02:16:43.321209Z 0 [Note] [MY-000000] [Galera] Failed to establish connection: wrong version number
2026-09-02T02:16:43.321521Z 0 [Note] [MY-000000] [Galera] (565f0a92-8283, 'tcp://0.0.0.0:4567') connection established to 1c72fb64-ac57 tcp://172.18.0.3:4567
2026-09-02T02:16:43.321566Z 0 [Note] [MY-000000] [Galera] (565f0a92-8283, 'tcp://0.0.0.0:4567') connection established to 2001e9dd-a09a tcp://172.18.0.4:4567

The first line is the same error we saw previously: our node initiated a TLS handshake, but the peer responded with something that was not TLS. Without socket.dynamic, the node would stop at that point and never join, but with it, it retries (without encryption) and is able to establish a connection.

This Galera option can’t be changed at runtime, so you need to restart the node to enable it.

The Procedure

In this procedure, we consider a typical PXC architecture with 3 nodes. Enabling encryption requires two rolling restarts: the first turns TLS on while still allowing plaintext connections, and the second removes that option and forces only TLS connections.

1. Distribute the same certificate files across all nodes:

Whether the certificates are auto-generated by MySQL or issued by us, every member of the cluster has to use the same key and certificate files. It’s recommended to keep the certificates outside the data directory for security and configuration clarity, and to keep them clear of SST since a joiner wipes its data directory before a full state transfer, and only files matching the [sst] cpat pattern survive. That default covers *.pem, so the MySQL-generated names are safe, but a .crt or .key left in the data directory is deleted.

2. Update the my.cnf on every node:

– pxc-encrypt-cluster-traffic=ON, this switch adds TLS for group communication, IST, and SST.

– socket.dynamic=YES option inside the wsrep_provider_options variable, so each node can speak both protocols while the rollout is in progress.

– The ssl-* variables, in case we’re setting the certificates outside the default path, which is the data directory.

– An [sst] section with the encrypt=4 option and the ssl-* variables. Since pxc-encrypt-cluster-traffic enforces SST channel to encrypt=4, this is a safety measure for those that did not yet performed the change, so donor and joiner can communicate over TLS while the change has not been applied in all nodes. Even though nothing has been restarted, the SST script re-reads my.cnf on every transfer, so as soon as the file is in place the donors encrypt SST whether or not they have been restarted.

The my.cnf file shows the following values:

[mysqld]
pxc-encrypt-cluster-traffic=ON
wsrep_provider_options="socket.dynamic=YES"
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

[sst]
encrypt=4
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

3. Restart node #1 and node #2, one at a time:

Wait for each node to rejoin and report Synced before moving to the next. A restarted node will try TLS first and then fall back.

You can verify the change was correctly applied by running the following query. Since the SSL settings are in the wsrep_provider_options variable, which is a long string of key/value pairs, we pull out only the ones we are interested in:

mysql> SELECT
    -> REGEXP_SUBSTR(@@wsrep_provider_options, 'socket\.dynamic = [^;]+')  AS socket_dynamic,
    -> REGEXP_SUBSTR(@@wsrep_provider_options, 'socket\.ssl = [^;]+')      AS socket_ssl,
    -> REGEXP_SUBSTR(@@wsrep_provider_options, 'socket\.ssl_ca = [^;]+')   AS socket_ssl_ca,
    -> REGEXP_SUBSTR(@@wsrep_provider_options, 'socket\.ssl_cert = [^;]+') AS socket_ssl_cert,
    -> REGEXP_SUBSTR(@@wsrep_provider_options, 'socket\.ssl_key = [^;]+')  AS socket_ssl_keyG
*************************** 1. row ***************************
 socket_dynamic: socket.dynamic = YES
     socket_ssl: socket.ssl = YES
  socket_ssl_ca: socket.ssl_ca = /etc/mysql/certs/ca.pem
socket_ssl_cert: socket.ssl_cert = /etc/mysql/certs/server-cert.pem
 socket_ssl_key: socket.ssl_key = /etc/mysql/certs/server-key.pem
1 row in set (0.00 sec)

The provider option socket.ssl and ssl certificates are passed down from MySQL to the provider because pxc-encrypt-cluster-traffic is on.

That confirms the settings reached the provider. That said, the replication traffic is not encrypted yet.

We can check this by using tcpdump on node #1 to listen on the replication port (4567) packets coming from node #2:

$ tcpdump -i any -nn -A -s0 "tcp port 4567 and host node2" -c 2000 | grep -a table_test

On node #2 we create the table_test that will be replicated to the other nodes:

mysql> create table test.table_test (c1 integer primary key);
Query OK, 0 rows affected (0.10 sec)

Since in PXC DDLs are replicated as a statement, tcpdump on node #1 shows the create table command in plaintext:

...x..x.j.....y............................ ..E......std............test.create table test.table_test (c1 integer primary key).......

4. Restart the third node with socket.dynamic=NO:

Node #3 does not need the socket.dynamic option since the other two nodes are already capable of talking either TLS or plaintext.

We modify the my.cnf option file as below:

[mysqld]
wsrep_provider_options="socket.dynamic=NO"

And restart the node. This node will start and request only TLS from the other nodes, which they can respond to, since they already have all the changes in place.

The node will show reaching out to cluster peers over ssl://

2026-09-02T02:19:03.640009Z 0 [Note] [MY-000000] [Galera] (aac049fc-a401, 'ssl://0.0.0.0:4567') connection established to 8d5f826f-8dd1 ssl://172.18.0.3:4567
2026-09-02T02:19:03.640410Z 0 [Note] [MY-000000] [Galera] (aac049fc-a401, 'ssl://0.0.0.0:4567') connection established to 565f0a92-8283 ssl://172.18.0.2:4567

5. Restart node #1 and node #2, turning off the socket.dynamic option:

In order to disallow the non-encrypted option and to establish a TLS connection with the other nodes, nodes #1 and #2 should be restarted.

Similar to the previous step, when restarting, the nodes will establish communication on TLS.

Node #1

2026-09-02T02:20:12.565801Z 0 [Note] [MY-000000] [Galera] (d3d58399-9ea2, 'ssl://0.0.0.0:4567') connection established to aac049fc-a401 ssl://172.18.0.4:4567
2026-09-02T02:20:12.565855Z 0 [Note] [MY-000000] [Galera] (d3d58399-9ea2, 'ssl://0.0.0.0:4567') connection established to 8d5f826f-8dd1 ssl://172.18.0.3:4567

Node #2

2026-09-02T02:21:20.911318Z 0 [Note] [MY-000000] [Galera] (fc923848-a6d1, 'ssl://0.0.0.0:4567') connection established to d3d58399-9ea2 ssl://172.18.0.2:4567
2026-09-02T02:21:20.911339Z 0 [Note] [MY-000000] [Galera] (fc923848-a6d1, 'ssl://0.0.0.0:4567') connection established to aac049fc-a401 ssl://172.18.0.4:4567

To confirm the replication traffic is encrypted, we can perform the same check as in step 3, by running tcpdump on node #1 and listening on the replication port (4567) for packets coming from node #2:

$ tcpdump -i any -nn -A -s0 "tcp port 4567 and host node2" -c 2000 | grep -a table_test

On node #2 we drop the table_test previously created:

mysql> drop table test.table_test;
Query OK, 0 rows affected (0.04 sec)

tcpdump does not show a matching pattern since the replication traffic is now encrypted.

Disabling TLS

We can use the same mechanism to disable TLS for whatever reason. As with enabling it, this takes two rolling restarts. Starting from a cluster where every node has pxc-encrypt-cluster-traffic=ON we perform the following steps:

– Restart nodes #1 and #2 with socket.dynamic=YES, keeping pxc-encrypt-cluster-traffic=ON, so they accept plaintext connections again. 

– Restart node #3 with pxc-encrypt-cluster-traffic=OFF, it will come up in plaintext only, and the other two nodes will accept it because they are back to accepting both protocols.

– Restart nodes #1 and #2 with pxc-encrypt-cluster-traffic=OFF and without socket.dynamic, which leaves the whole cluster in plaintext.

– Finally, remove from the sst section the encrypt and SSL related variables.

Conclusion

Enabling TLS for inter-node traffic can be done without stopping the whole cluster. The socket.dynamic option lets a node accept both encrypted and plaintext connections during the transition, so the change can be rolled out one node at a time. Please note we’ll need at least two rolling restarts to achieve that the cluster fully communicates via TLS.

The post Enabling TLS in PXC without Downtime appeared first on Percona.

Enabling TLS in PXC without Downtime appeared first on MariaDB.org

Percona Operator for PostgreSQL 3.1.0 takes on three things that decide whether a PostgreSQL platform passes review: is the data encrypted at rest, can it serve reads without straining the primary, and are the logs there when you need them. This release answers all three inside the custom resource, so none of them is a bolt-on you maintain yourself.

The three headline features are transparent data encryption with pg_tde, logical replicas, and persistent logging. pg_tde encrypts your data on disk, including the write-ahead log. Logical replicas add a read-only copy inside the cluster for reporting and analytics. Persistent logging keeps PostgreSQL and pgBackRest logs across Pod restarts.

The operator is open source and runs on any CNCF-certified Kubernetes distribution. This release also widens where it runs, adding official Rancher Kubernetes Engine (RKE2) support and full ARM64 images. Much of what shipped here comes from requests on forums.percona.com and the public issue tracker.

In this post, you’ll learn about:

• Transparent data encryption with pg_tde
• Logical replicas for read-only workloads
• Persistent logging for PostgreSQL and pgBackRest
• Other improvements worth knowing about
  

Transparent data encryption with pg_tde

Encryption at rest is usually the line item that blocks a database from going into a regulated environment. Storage-level encryption from the cloud provider covers the disk, but it does not protect a copied volume, a leaked backup, or a stray WAL segment, and auditors increasingly want encryption the database itself controls. This release adds transparent data encryption through pg_tde, Percona’s open source TDE extension for PostgreSQL.

Why it matters

With pg_tde, the data in tables, indexes, temporary tables, and the write-ahead log stays encrypted on disk, and PostgreSQL decrypts it only in memory for a session that holds the key. That closes the gaps storage encryption leaves open: a snapshot of the volume, a backup shipped to object storage, or a WAL file replicated off-node is ciphertext without the key. Because the key lives in an external provider rather than next to the data, you separate who runs the database from who controls the keys. In practice this is what lets a team bring PostgreSQL on Kubernetes into scope for a standard like PCI DSS or HIPAA without moving the workload off the platform: the encryption the auditor asks about lives in the database, and key custody lives in Vault under a different team’s control.

How it works

The operator wires pg_tde to HashiCorp Vault as the key provider. You enable the extension in the custom resource and point it at your Vault instance and a token Secret. The operator handles loading the extension and configuring the key set on the PostgreSQL instances. WAL encryption is a separate switch, so you can encrypt table data and the write-ahead log together. pg_tde uses a two-tier key model: a principal key in Vault wraps the internal data keys, so rotating the principal key is a key-management operation in Vault rather than a full re-encryption of the database.

Wiring it up

apiVersion: pgv2.percona.com/v2
kind: PerconaPGCluster
metadata:
  name: cluster1
spec:
  extensions:
    pg_tde:
      enabled: true
      walEncryption: true
      vault:
        host: https://vault-service:8200
        mountPath: tde
        tokenSecret:
          name: pg-tde-vault-secret
          key: token
        caSecret:
          name: pg-tde-vault-secret
          key: ca.crt

enabled: true loads pg_tde and turns on encryption for the cluster, and walEncryption: true extends it to the write-ahead log. The vault block points at your key provider: host and mountPath locate the secrets engine, tokenSecret holds the Vault token, and caSecret carries the CA certificate so the operator trusts the Vault endpoint. Keep the Vault token and CA in Kubernetes Secrets, not in the manifest.

Note: pg_tde in 3.1.0 is available for PostgreSQL 17 and 18. Encryption applies to data written after you enable it, so plan enablement as part of provisioning a cluster rather than as a switch on a full production database.

Logical replicas for read-only workloads

A PostgreSQL cluster under the operator is a primary with streaming physical replicas that Patroni manages for high availability. Those replicas exist to take over on failover, not to be a stable place to point a reporting tool, because their role can change at any time. Teams that want a durable read endpoint for analytics have had to run a second cluster or wire up replication by hand. This release adds a logical replica you declare inside the same cluster.

Why it matters

Reporting and analytics queries have a different shape than transactional traffic: they scan more, run longer, and arrive in bursts when a dashboard refreshes or a nightly job starts. Pointed at the primary, they compete with the writes that keep the application responsive. A logical replica gives that traffic its own copy and its own compute, so a heavy analytics query slows down a chart, not a checkout. Because the endpoint is stable, you set the reporting tool’s connection string once and leave it.

How it works

A logical replica is a read-only copy with its own volume and its own Service, seeded from a pgBackRest backup and kept current through logical replication. Patroni does not manage it, so it never gets promoted and its endpoint stays stable: a reporting query or a dashboard can point at it and stay pointed at it. You can target specific databases or replicate all of them, and size the replica independently of the primary.

Wiring it up

spec:
  logicalReplicas:
  - name: analytics
    databases: []  # empty = all non-template databases except "postgres"
    bootstrapMethod: pgbackrest
    dataVolumeClaimSpec:
      accessModes:
      - ReadWriteOnce
      resources:
        requests:
          storage: 1Gi
    resources:
      limits:
        cpu: 2.0
        memory: 4Gi
    expose:
      type: LoadBalancer

name becomes the replica’s identity and the basis for its Service. databases selects what to replicate, where an empty list means every non-template database except postgres. bootstrapMethod: pgbackrest seeds the replica from a backup rather than from the live primary, which keeps the initial sync off the primary’s back. dataVolumeClaimSpec and resources size it for the read workload, and expose publishes the read endpoint.

Note: Logical replicas are a tech preview in 3.1.0 and require PostgreSQL 17 or later. Logical replication does not copy schema changes automatically, so treat DDL on the primary as something you coordinate with the replica.

Persistent logging for PostgreSQL and pgBackRest

Logs matter most right after something goes wrong, which is exactly when a Pod is most likely to have restarted and taken its logs with it. When PostgreSQL logs only to a container’s stdout, a crash-loop or a reschedule erases the evidence you need to explain it. The classic case is a crash-looping instance: by the time you exec into a fresh Pod, the stdout from the crash is gone, but an on-disk log still holds the panic and the queries around it. This release keeps PostgreSQL and pgBackRest logs on the instance data volume, so they survive Pod restarts.

How it works

The operator runs a Fluent Bit log collector as a sidecar that reads the on-disk logs and emits them as structured JSON lines. Because the logs live on the data volume, they persist across restarts and rescheduling. From there you can forward them off-cluster: the collector can ship to S3 or over OpenTelemetry to whatever aggregation stack you already run, configured through the custom resource. The same on-disk logs that help you debug an incident become the audit trail your security team retains, without a second logging agent to install.

Wiring it up

spec:
  logcollector:
    enabled: true
    image: docker.io/perconalab/fluentbit:main-logcollector
#    configuration: |
#      pipeline:
#        filters:
#          - name: record_modifier
#            match: "*"
#            record:
#              - cluster_name cluster1

enabled: true turns on the Fluent Bit collector, and image pins the collector build. The commented configuration block is a Fluent Bit pipeline you can supply to filter, enrich, or route logs: the example tags every record with a cluster_name, and the same mechanism adds an output to forward logs to S3 or an OpenTelemetry endpoint. You can also tune log rotation so retention matches your operational windows and compliance rules.

Note: Persistent logs consume space on the instance data volume. Set a rotation policy that fits the volume so logs do not compete with the database for storage.

Other improvements

Beyond the three headline features, 3.1.0 ships a set of enhancements that smooth day-two operations:

• Community PostgreSQL images and custom registries (K8SPG-1056): run Percona Distribution for PostgreSQL, community PostgreSQL, or your own images by setting spec.image, proxy.pgBouncer.image, and spec.backups.pgbackrest.image.
• Auto-growable pgBackRest disks (K8SPG-691): let backup repository volumes grow to a limit instead of filling up and failing a backup.
• Pause and resume pgBouncer (K8SPG-1115): set proxy.pgBouncer.paused to hold client traffic without dropping application connections.
• mTLS for pgBouncer (K8SPG-952): extend the pgBouncer trust bundle with your external CA through proxy.pgBouncer.additionalTrustedCAs while the operator keeps rotating cluster TLS.
• cert-manager Issuer and TLS policy (K8SPG-951, K8SPG-1045): point the operator at your own Issuer, and use certManagementPolicy to decide who owns certificate lifecycle.
• Extra volume mounts (K8SPG-440): mount extra ConfigMap, Secret, PVC, or emptyDir volumes into the PostgreSQL container through instances.extraVolumes.
• pg_cron and set_user are now built-in (K8SPG-1040): enable them through extensions without supplying a custom build.
• PostgreSQL 19 tech preview (K8SPG-1051) and full ARM64 support (K8SPG-881): evaluate the next major early, and run the operator natively on ARM.

One deprecation to plan for: this release removes PMM2 support (K8SPG-944), so move monitoring to PMM3. The extensions.builtin field is deprecated in favor of extensions..enabled; migrate before 3.4.0.

Conclusion

Percona Operator for PostgreSQL 3.1.0 tightens the parts of a PostgreSQL platform that reviews and on-call rotations care about most: pg_tde encrypts data at rest under keys you control, logical replicas give analytics a stable read endpoint without a second cluster, and persistent logging keeps the evidence when a Pod restarts. With RKE2 and full ARM64 support, more of that runs on the platforms teams actually use. If there is a workflow you still script around the operator, tell us on the forum, since that is where releases like this one come from.

Try Percona Operator for PostgreSQL 3.1.0

• Release notes: Percona Operator for PostgreSQL 3.1.0 Release Notes
• Documentation: Percona Operator for PostgreSQL docs
• GitHub: percona/percona-postgresql-operator
• Community Forum: forums.percona.com: share your feedback, ask questions, or report issues

The post Percona Operator for PostgreSQL 3.1.0: Transparent Data Encryption, Logical Replicas, and Persistent Logging appeared first on Percona.

Percona Operator for PostgreSQL 3.1.0: Transparent Data Encryption, Logical Replicas, and Persistent Logging appeared first on MariaDB.org

This post is the method I use for expensive queries on SQL Server 2025 (17.x, CU8 at the time of writing, database compatibility level 170) to find them both ways, read what the plan is really doing, and fix them with the index or the rewrite the evidence points at. Where SQL Server 2022 behaves differently I say so inline. Every query here runs against Query Store or the DMVs and is safe to run on a production instance; the sample outputs are illustrative and labelled as such, because the numbers that matter are yours.

Expensive queries are two problems, and the axis you pick decides the fix

For expensive queries, response time is what the user experiences: duration, including every wait the session accumulated while blocked, while waiting for a memory grant, or while the client drained a large result set. Resource efficiency is what the engine spent: CPU time, logical reads, tempdb pages and worker threads, per execution and per row returned. A statement can be slow and cheap, when it spends its life in LCK_M_X or ASYNC_NETWORK_IO. A statement can be fast and ruinously expensive, when a two-millisecond lookup runs five thousand times a second with two hundred logical reads per call.

Figure 1. Expensive queries on two axes. The top-right quadrant gets all the attention; the bottom-right quadrant is where the licence cores go.

The reason the distinction matters for expensive queries in practice is that the fixes do not overlap much. Waits are fixed with concurrency design, isolation level, batch sizing and the application’s fetch pattern. Resource cost is fixed with plan shape: predicates, statistics, indexes and, occasionally, a hint. If you rank by duration and the top entry is a query blocked behind a long transaction, adding an index to it changes nothing except the maintenance cost of every insert on that table.

Query Store is the evidence chain, and on SQL Server 2025 it is on everywhere

Query Store has been the right place to find expensive queries since SQL Server 2016, but two things changed in 2025 that make it the only place I look first. It is enabled by default for new databases, and it now runs on readable secondaries by default, so an Always On read-scale workload is no longer invisible. The runtime statistics are aggregated per plan per interval, which is exactly the granularity that separates response time from resource cost: duration, CPU, logical reads, rowcount, tempdb and DOP all sit in the same row.

Figure 2. The evidence chain for expensive queries. Every ranking resolves to a query_id and a plan_id, and those two numbers are what you act on.

Before ranking expensive queries, confirm Query Store is capturing what you need. QUERY_CAPTURE_MODE of AUTO skips trivial and infrequent statements, which is fine for finding expensive queries and wrong for auditing everything. Check the operation mode, the interval length and how much of the allocated space is used, because a Query Store that has flipped to READ_ONLY under space pressure is silently stale.

SELECT   actual_state_desc,
         desired_state_desc,
         readonly_reason,
         current_storage_size_mb,
         max_storage_size_mb,
         interval_length_minutes,
         query_capture_mode_desc,
         stale_query_threshold_days,
         wait_stats_capture_mode_desc
FROM     sys.database_query_store_options;

-- If actual_state_desc is READ_ONLY with readonly_reason 65536, storage is full:
-- ALTER DATABASE CURRENT SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2048);   -- online, no restart

Then rank the expensive queries. The query below is the one I keep in a snippet. It aggregates the last N hours of runtime intervals per query and plan and computes the four numbers the quadrant diagram asks for: total duration weighted by executions, total CPU, logical reads per row returned, and the coefficient of variation of duration. Sort by whichever question you are answering. The ORDER BY is the whole point of the query, so I leave all four options in and comment out three.

DECLARE @hours INT = 24;

WITH rs AS (
    SELECT   rs.plan_id,
             SUM(rs.count_executions)                                          AS executions,
             SUM(rs.count_executions * rs.avg_duration)        / 1000.0        AS total_duration_ms,
             SUM(rs.count_executions * rs.avg_cpu_time)        / 1000.0        AS total_cpu_ms,
             SUM(rs.count_executions * rs.avg_logical_io_reads)                AS total_logical_reads,
             SUM(rs.count_executions * rs.avg_rowcount)                        AS total_rows,
             SUM(rs.count_executions * rs.avg_tempdb_space_used)               AS total_tempdb_pages,
             MAX(rs.max_duration)                              / 1000.0        AS max_duration_ms,
             MAX(rs.stdev_duration)                            / 1000.0        AS max_stdev_duration_ms,
             MAX(rs.max_dop)                                                   AS max_dop
    FROM     sys.query_store_runtime_stats          AS rs
    JOIN     sys.query_store_runtime_stats_interval AS i
          ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
    WHERE    i.start_time >= DATEADD(HOUR, -@hours, SYSUTCDATETIME())
    GROUP BY rs.plan_id
)
SELECT   TOP (25)
         q.query_id,
         p.plan_id,
         p.is_forced_plan,
         p.compatibility_level,
         rs.executions,
         CAST(rs.total_duration_ms / NULLIF(rs.executions, 0) AS DECIMAL(18, 2))   AS avg_duration_ms,
         CAST(rs.max_duration_ms AS DECIMAL(18, 2))                                  AS max_duration_ms,
         CAST(rs.total_cpu_ms AS DECIMAL(18, 2))                                     AS total_cpu_ms,
         CAST(rs.total_logical_reads / NULLIF(rs.total_rows, 0) AS DECIMAL(18, 1))  AS reads_per_row,
         CAST(rs.max_stdev_duration_ms / NULLIF(rs.total_duration_ms / NULLIF(rs.executions, 0), 0) AS DECIMAL(9, 3)) AS duration_cv,
         rs.total_tempdb_pages,
         rs.max_dop,
         LEFT(qt.query_sql_text, 160)                                                AS query_text
FROM     rs
JOIN     sys.query_store_plan       AS p  ON p.plan_id  = rs.plan_id
JOIN     sys.query_store_query      AS q  ON q.query_id = p.query_id
JOIN     sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
ORDER BY rs.total_duration_ms DESC;       -- response time, weighted by how often it runs
-- ORDER BY rs.total_cpu_ms DESC;         -- resource cost: the core-count question
-- ORDER BY reads_per_row DESC;           -- efficiency: reads per row returned
-- ORDER BY duration_cv DESC;             -- variance: parameter sensitivity and plan flips

Two of those columns do more work for expensive queries than the rest. reads_per_row is the efficiency ratio: a query returning ten rows at 40,000 logical reads per execution is doing 4,000 reads for each row it hands back, and no amount of hardware makes that reasonable. duration_cv, the standard deviation of duration divided by the mean, is how you find parameter-sensitive expensive queries before a user does; a value above 1 on a frequently executed statement almost always means more than one plan shape is being used for the same query_id.

The output below is illustrative, not from a customer system, and shows the shape I am looking for: one query high on every axis, one high on CPU only, and one whose variance gives it away.

query_id plan_id executions avg_duration_ms max_duration_ms total_cpu_ms reads_per_row duration_cv max_dop
-------- ------- ---------- --------------- --------------- ------------ ------------- ----------- -------
   40217    9912      18240          412.8         8391.2     6204812.1        3892.4       1.812       8
   11203    2210    1928400            1.9           24.7     3517110.6         206.0       0.220       1
   40391   10044       2210         1290.5         2011.0       81023.9          12.3       0.140       4
...

Among those three expensive queries, query_id 40217 is the classic top-right quadrant: slow, expensive and unstable. query_id 11203 is the bottom-right one, two milliseconds a call and the second-largest CPU consumer on the instance because it runs 1.9 million times a day with 206 reads per row. Sorting by average duration would never have shown it. query_id 40391 is slow and steady with a modest read ratio, which usually means it is waiting rather than working, and the wait stats view settles that.

SELECT   ws.plan_id,
         ws.wait_category_desc,
         SUM(ws.total_query_wait_time_ms)                    AS total_wait_ms,
         MAX(ws.max_query_wait_time_ms)                      AS max_wait_ms
FROM     sys.query_store_wait_stats               AS ws
JOIN     sys.query_store_runtime_stats_interval   AS i
      ON i.runtime_stats_interval_id = ws.runtime_stats_interval_id
WHERE    ws.plan_id IN (9912, 2210, 10044)
AND      i.start_time >= DATEADD(HOUR, -24, SYSUTCDATETIME())
GROUP BY ws.plan_id, ws.wait_category_desc
ORDER BY ws.plan_id, total_wait_ms DESC;

If an expensive query’s duration is mostly Lock, Network IO or Memory waits, it belongs in the slow-but-cheap quadrant and the index conversation is over before it starts. On SQL Server 2025 there is a wait category worth new attention: the LCK_M_*_XACT waits that appear once optimized locking is enabled, where sessions wait on the transaction resource rather than on individual rows. Baseline lock waits before turning that feature on, or you will not be able to tell whether it helped.

Expensive queries from the plan cache DMVs: still useful for one thing

sys.dm_exec_query_stats still has a place, mainly on instances where Query Store is off or on ad hoc workloads where it captures little. Its numbers reset on plan eviction and restart, it has no interval history, and on a busy instance it undercounts anything that recompiles often. I use it for one thing Query Store does not do well: finding expensive queries by the memory grant they requested, which is the fastest route to the RESOURCE_SEMAPHORE waits that stall everything else.

SELECT   TOP (20)
         qs.execution_count,
         qs.max_grant_kb,
         qs.max_used_grant_kb,
         qs.max_ideal_grant_kb,
         qs.max_spills,
         qs.total_worker_time / 1000 / NULLIF(qs.execution_count, 0)  AS avg_cpu_ms,
         qs.total_logical_reads / NULLIF(qs.execution_count, 0)       AS avg_logical_reads,
         qs.query_hash,
         SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
                   ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
                     ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text
FROM     sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE    qs.max_grant_kb > 65536
ORDER BY qs.max_grant_kb DESC;

A max_used_grant_kb far below max_grant_kb is an over-estimate that starves other sessions; a max_spills above zero with used close to granted is an under-estimate that spilled. Both come back to cardinality, which is where reading the plans of expensive queries starts.

Reading the plan of an expensive query without flattering it

The actual execution plan is where a ranking of expensive queries becomes a diagnosis. Estimated plans are useful for review; they are useless for troubleshooting, because the whole problem is usually that the estimate was wrong. Query Store keeps the plan XML per plan_id, and on SQL Server 2019 and later the LAST_QUERY_PLAN_STATS database-scoped configuration keeps the last actual plan with runtime counters for cached statements, which is the cheapest way to get an actual plan for something you cannot re-run at will.

Figure 3. Five plan shapes that account for most expensive queries, and the number in the plan that exposes each.

Rather than opening every one of the expensive queries in a plan viewer, I pull the warnings and the operator inventory out of the XML for the top plan_ids in one pass. The query below flags the four things I want to know before I look at anything else: is there a key lookup, a spill, an implicit conversion, or a scan on a table the predicate should have seeked.

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT   p.plan_id,
         p.query_id,
         x.value('count(//IndexScan[@Lookup="true"])', 'INT')                       AS key_lookups,
         x.value('count(//RelOp[@PhysicalOp="Clustered Index Scan" or @PhysicalOp="Index Scan" or @PhysicalOp="Table Scan"])', 'INT')
                                                                                   AS scans,
         x.value('count(//Warnings/SpillToTempDb)', 'INT')                        AS spills,
         x.value('count(//Warnings/PlanAffectingConvert[@ConvertIssue="Seek Plan"])', 'INT')
                                                                                   AS implicit_conversions,
         x.value('count(//RelOp[@PhysicalOp="Table Spool" or @PhysicalOp="Index Spool"])', 'INT')
                                                                                   AS spools,
         x.value('(//StmtSimple/@StatementOptmEarlyAbortReason)[1]', 'NVARCHAR(50)') AS optimizer_abort_reason,
         x.value('(//QueryPlan/@CachedPlanSize)[1]', 'INT')                       AS cached_plan_kb
FROM     sys.query_store_plan AS p
CROSS APPLY (SELECT TRY_CONVERT(XML, p.query_plan)) AS c(x)
WHERE    p.plan_id IN (9912, 2210, 10044)
ORDER BY p.plan_id;

For the expensive queries that survive triage, the operator-level view is where the efficiency ratio lives. Two properties on every operator in an actual plan tell you what it cost: Number of Rows Read, which is how many rows the operator touched, and Actual Number of Rows, which is how many came out. A seek that reads 900,000 rows to output 40 is a seek in name only; it is a range scan with a residual predicate, and the residual is nearly always a non-SARGable expression or a column that should have been in the key. STATISTICS IO gives the same information per table when you can run the statement yourself.

SET STATISTICS IO, TIME ON;
SET STATISTICS XML ON;

DECLARE @customer_id INT = 48213, @from DATE = '2026-08-01';

SELECT   o.order_id, o.order_date, o.status, ol.sku, ol.qty, ol.line_total
FROM     dbo.orders      AS o
JOIN     dbo.order_lines AS ol ON ol.order_id = o.order_id
WHERE    o.customer_id = @customer_id
AND      CONVERT(DATE, o.order_date) >= @from        -- non-SARGable: function on the column
ORDER BY o.order_date DESC;

SET STATISTICS XML OFF;
SET STATISTICS IO, TIME OFF;

-- Illustrative STATISTICS IO line (not a measured result):
-- Table 'orders'. Scan count 1, logical reads 184112, ... lob logical reads 0
-- Table 'order_lines'. Scan count 61, logical reads 244, ...

That CONVERT on order_date is the single most common reason an otherwise well-indexed statement becomes one of the expensive queries on an instance. The optimizer cannot seek on a function of the column, so it scans the whole customer’s range, or the whole table, and filters afterwards. The rewrite is a half-open range on the raw column, and the reads collapse to whatever the index range actually covers. The same demotion happens with implicit conversions between NVARCHAR parameters and VARCHAR columns, with ISNULL(col, x) = y, with LIKE ‘%’ + @p, and with arithmetic on the column side of a comparison.

WHERE    o.customer_id = @customer_id
AND      o.order_date  >= CAST(@from AS DATETIME2(0))   -- compare the raw column, match its type
ORDER BY o.order_date DESC;

What SQL Server 2025 changed for expensive queries, and where 2022 differs

Intelligent Query Processing is the umbrella for the optimizer features that adjust plans from runtime feedback, and SQL Server 2025 extended it in ways that change how expensive queries behave after an upgrade. I pin each item to the version and the compatibility level it needs, because a database restored onto a 2025 instance at compatibility level 150 gets almost none of this.

The compatibility-level point deserves its own sentence. Microsoft’s rule is that cardinality estimator changes only activate at the default compatibility level of the version that introduced them, so a database left at 150 after a 2025 upgrade keeps its old plans and gains none of the feedback mechanisms above, which is sometimes exactly what you want for the first weeks and is never what you want permanently. Microsoft’s What’s new in SQL Server 2025 page is the reference for the table, and the Intelligent Query Processing details page carries the per-feature compatibility requirements.

Indexing expensive queries: the decision, then the proof

Only after the predicate is SARGable and the statistics are current does an index change for expensive queries make sense; an index built around a bad predicate fossilises the bad predicate. The design sequence I follow is short and I follow it in order every time, because the order is what decides whether the index seeks or scans.

Figure 4. Indexing expensive queries: key design from the predicate, cost checks from the DMVs, proof before CREATE INDEX reaches production.

The missing index DMVs are a starting point for expensive queries and nothing more. They propose one index per query shape, never consolidate, over-include, and ignore the write side entirely. I read them together with the usage stats, so every proposal is weighed against what the table already carries and how hard it is written.

SELECT   OBJECT_SCHEMA_NAME(mid.object_id) + '.' + OBJECT_NAME(mid.object_id)   AS table_name,
         mid.equality_columns,
         mid.inequality_columns,
         mid.included_columns,
         migs.user_seeks + migs.user_scans                                          AS would_have_used,
         CAST(migs.avg_total_user_cost * migs.avg_user_impact / 100.0
              * (migs.user_seeks + migs.user_scans) AS DECIMAL(18, 1))              AS improvement_measure,
         (SELECT COUNT(*) FROM sys.indexes i WHERE i.object_id = mid.object_id AND i.index_id > 0) AS existing_indexes,
         (SELECT SUM(us.user_updates) FROM sys.dm_db_index_usage_stats us
           WHERE us.object_id = mid.object_id AND us.database_id = DB_ID())          AS index_writes_since_restart
FROM     sys.dm_db_missing_index_details AS mid
JOIN     sys.dm_db_missing_index_groups  AS mig  ON mig.index_handle = mid.index_handle
JOIN     sys.dm_db_missing_index_group_stats AS migs ON migs.group_handle = mig.index_group_handle
WHERE    mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;

SELECT   OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id)   AS table_name,
         i.name                                                              AS index_name,
         i.type_desc,
         i.is_unique,
         i.has_filter,
         us.user_seeks, us.user_scans, us.user_lookups, us.user_updates,
         us.last_user_seek, us.last_user_scan,
         ps.used_page_count * 8 / 1024                                       AS size_mb
FROM     sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
       ON us.object_id = i.object_id AND us.index_id = i.index_id AND us.database_id = DB_ID()
JOIN     sys.dm_db_partition_stats AS ps
      ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE    i.index_id > 1                 -- nonclustered only
AND      i.is_primary_key = 0
AND      i.is_unique_constraint = 0
AND      OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY ISNULL(us.user_seeks, 0) + ISNULL(us.user_scans, 0) + ISNULL(us.user_lookups, 0) ASC,
         us.user_updates DESC;

Index usage stats reset on restart, so read them against the instance uptime from sys.dm_os_sys_info, and never drop an index on the strength of a counter that has only seen a fortnight; the quarter-end report that uses it is real. When I do add an index for one of the expensive queries, the DDL follows the design sequence exactly, and it goes in online and resumable so the operation can be paused if it starts hurting.

CREATE NONCLUSTERED INDEX ix_orders_customer_date
    ON dbo.orders (customer_id, order_date DESC)
    INCLUDE (status)
    WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES,
          SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

-- Pause and resume without losing progress if the window closes:
-- ALTER INDEX ix_orders_customer_date ON dbo.orders PAUSE;
-- ALTER INDEX ix_orders_customer_date ON dbo.orders RESUME;

-- Filtered variant when only open orders are ever queried this way:
CREATE NONCLUSTERED INDEX ix_orders_customer_date_open
    ON dbo.orders (customer_id, order_date DESC)
    INCLUDE (status)
    WHERE status IN ('NEW', 'PICKING', 'PACKED')
    WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);

Three notes on that DDL. The descending key on order_date lets the ORDER BY come straight off the index without a sort, which matters more than it looks on a statement that runs thousands of times an hour.

The filtered variant is smaller and cheaper to maintain, but it only serves queries whose predicate the optimizer can prove is contained in the filter, and a parameterised status predicate usually cannot be proved, so I check the plan rather than assume. And if the expensive query is an aggregation over millions of rows rather than a lookup of dozens, no rowstore key fixes it; a nonclustered columnstore index on the same table, with batch mode, is the right tool and does not interfere with the OLTP path.

The fix ladder: reversible first, permanent second

Figure 5. The loop for expensive queries: one change per iteration, verified against the same query_id in the next Query Store interval, with the rollback named before the change is made.

What makes SQL Server unusual among the engines I work on is how much of the fix ladder for expensive queries is reversible without a deployment. Query Store hints attach OPTION clauses to a query_id without touching application code; plan forcing pins a known-good plan_id while the real fix is engineered; and on 2025 ABORT_QUERY_EXECUTION stops a query shape outright. All three are undone with one procedure call. I use them as the first move and the index or rewrite as the second, because at 02:00 the reversible fix is the safe one, and because a forced plan buys the time to do the permanent fix properly.

-- 1. A hint without a code change: cap the memory grant and pin the estimator behaviour
EXEC sys.sp_query_store_set_hints
     @query_id = 40217,
     @query_hints = N'OPTION (MAX_GRANT_PERCENT = 10, USE HINT(''DISABLE_CE_FEEDBACK''))';

-- Verify it took: hint_id and the query_hints text
SELECT query_id, query_hint_text, source_desc FROM sys.query_store_query_hints WHERE query_id = 40217;

-- Undo
EXEC sys.sp_query_store_clear_hints @query_id = 40217;

-- 2. Force the plan that behaved, while the permanent fix is built
EXEC sys.sp_query_store_force_plan @query_id = 40217, @plan_id = 9877;

-- Verify: is_forced_plan = 1 and no force_failure_count growth
SELECT plan_id, is_forced_plan, force_failure_count, last_force_failure_reason_desc
FROM   sys.query_store_plan WHERE query_id = 40217;

-- Undo
EXEC sys.sp_query_store_unforce_plan @query_id = 40217, @plan_id = 9877;

-- 3. SQL Server 2025 only: stop a runaway query shape at compile time (circuit breaker, not a fix)
EXEC sys.sp_query_store_set_hints
     @query_id = 40217,
     @query_hints = N'OPTION (USE HINT(''ABORT_QUERY_EXECUTION''))';
-- Undo with sp_query_store_clear_hints as above

Two cautions from experience with expensive queries and forced plans. A forced plan that references an index you later drop fails silently to force and the query falls back to whatever the optimizer picks; force_failure_count is the only place that shows it, so I alert on it. And hints that disable feedback mechanisms should be dated and reviewed, because the whole reason the mechanism existed was to fix the class of problem the hint is masking.

Verification is the same ranking of expensive queries that found the problem, filtered to the query_id, one interval later. I want to see avg_duration_ms and reads_per_row move in the right direction and duration_cv fall, on the same executions count; if executions also collapsed, someone changed the application and I have not measured anything. I keep the before and after rows in the ticket, because the next time someone asks whether the index was worth its write cost, those two rows are the answer.

SELECT   i.start_time,
         rs.plan_id,
         rs.count_executions,
         CAST(rs.avg_duration / 1000.0 AS DECIMAL(18, 2))                                    AS avg_duration_ms,
         CAST(rs.avg_cpu_time / 1000.0 AS DECIMAL(18, 2))                                    AS avg_cpu_ms,
         CAST(rs.avg_logical_io_reads / NULLIF(rs.avg_rowcount, 0) AS DECIMAL(18, 1))        AS reads_per_row,
         CAST(rs.stdev_duration / NULLIF(rs.avg_duration, 0) AS DECIMAL(9, 3))               AS duration_cv,
         rs.max_dop
FROM     sys.query_store_runtime_stats          AS rs
JOIN     sys.query_store_runtime_stats_interval AS i ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN     sys.query_store_plan                   AS p ON p.plan_id = rs.plan_id
WHERE    p.query_id = 40217
AND      i.start_time >= DATEADD(DAY, -3, SYSUTCDATETIME())
ORDER BY i.start_time, rs.plan_id;

Expensive queries: fixes that did not help, and the ones I do not do any more

Adding MAXDOP hints to expensive queries that were slow because of a spill did nothing except make them serial and slower. Rebuilding indexes to fix an expensive query has, in my experience, fixed the query exactly as often as the rebuild happened to refresh statistics that were the actual problem, which is why I now update statistics with FULLSCAN first and rebuild only for fragmentation that the plan shows is hurting.

Turning off parameter sniffing instance-wide traded one set of expensive queries for another and made every plan mediocre; PSP optimization and OPPO exist precisely so that trade is no longer necessary on 2022 and 2025. And clearing the plan cache “to see if it helps” is a way of losing the evidence you needed while creating a compilation storm.

Where MinervaDB fits

MinervaDB delivers SQL Server consulting, 24×7 consultative support and remote DBA services for on-premises, Always On and Azure SQL estates. Work on expensive queries is a standing part of it: Query Store based performance health checks, 2019 and 2022 to 2025 upgrade assessments that stage the compatibility level jump behind Query Store and review optimized locking before it is enabled, and index estate reviews that remove the dead weight before adding anything. We work alongside your DBAs and your Microsoft support agreement rather than in place of either.

Test every query, hint, forced plan and index in this post on a non-production copy of your database with a representative workload before you apply it to production, keep backups and a tested restore path current so that every change has a rollback, and maintain your disaster recovery posture throughout. The method for expensive queries holds across workloads. Which of these fixes pays on yours is something only your Query Store can tell you.

SQL Server 2025 Expensive Queries: 7 Proven Ways to Find and Fix Them by Latency and Resource Cost appeared first on MariaDB.org

Static analysis cannot determine whether a migration is safe in production. It does not know table size, traffic, open transactions, data distribution, or how the migration runner executes the file. But it can catch a smaller class of mistakes where the SQL itself provides strong evidence of operational risk.

I built postgres-migration-preflight as an experiment in that narrower problem. It parses PostgreSQL SQL into an abstract syntax tree (AST), evaluates statements in source order, and exits non-zero when configured blocking rules fire. The examples and documentation references below use PostgreSQL 18.

The goal is not to certify a migration as production-safe. It is to make obvious hazards difficult to merge unnoticed.

Focus on risks visible from SQL

The first version deliberately limits itself to rules that can be explained from the migration text:

• regular CREATE INDEX and DROP INDEX operations;
• selected lock-sensitive ALTER TABLE operations;
• destructive DDL such as DROP TABLE and TRUNCATE;
• UPDATE or DELETE statements without a WHERE clause;
• DDL without a preceding non-zero session-level lock_timeout;
• SQL that the parser cannot understand.

These rules are heuristics, not proofs. A normal CREATE INDEX allows reads but blocks inserts, updates, and deletes while the index is built. CREATE INDEX CONCURRENTLY avoids blocking those writes, but PostgreSQL performs two table scans, waits for relevant transactions, does more total work, and can leave an invalid index after certain failures. It also cannot run inside a transaction block. A migration framework may create such a transaction even when the SQL file contains no BEGIN, so the finding asks the reviewer to check both the index operation and the runner.

A normal DROP INDEX acquires an ACCESS EXCLUSIVE lock on the table. Its concurrent form reduces that interference but cannot run in a transaction block, cannot use CASCADE, and cannot remove an index that supports a UNIQUE or PRIMARY KEY constraint.

ALTER TABLE is a family of operations rather than one risk category. A column type change normally rewrites the table, but PostgreSQL can avoid the rewrite for some binary-compatible conversions. SET NOT NULL ordinarily scans existing rows, but can skip the scan when a valid CHECK constraint proves that no NULL can exist. A foreign key added as NOT VALID can enforce new writes before a separate VALIDATE CONSTRAINT checks existing rows with a less disruptive lock.

The current lab still blocks every column type change conservatively because it has no catalog context. That severity is a policy choice for review, not proof that a particular conversion will rewrite a table.

Preserve order and fail closed

Some protections matter only when they appear before the statement they are meant to protect:

ALTER TABLE users
 ALTER COLUMN email TYPE varchar(320);

SET lock_timeout = '2s';

The timeout does not protect the preceding ALTER TABLE. The analyzer walks statements in source order and tracks the session-level lock_timeout. A non-zero value counts only for subsequent DDL, and setting it back to 0 disables the protection again.

This is a review signal, not a claim that a timeout makes DDL safe. lock_timeout limits only time spent waiting to acquire a lock. After the lock is acquired, an operation can continue much longer. An equal or shorter statement_timeout can also fire first.

The analyzer uses pgsql-ast-parser instead of regular expressions so supported rules operate on statement structure. It can distinguish an UPDATE with a predicate, a concurrent index declaration, and individual ALTER TABLE actions. An AST still does not provide catalog state or workload context. If the parser cannot understand valid PostgreSQL syntax, the tool emits SQL_PARSE_ERROR at blocker severity and exits non-zero. Parser failures are coverage gaps requiring review, not evidence that PostgreSQL would reject the migration.

Compare passing and blocked migrations

A deliberately small passing example is:

SET lock_timeout = '2s';
SET statement_timeout = '15min';

CREATE INDEX CONCURRENTLY idx_users_last_seen_at
 ON users (last_seen_at);

UPDATE users
SET archived = true
WHERE id = 42;

The checker parses four statements and reports no findings. That result does not make the migration automatically safe: the caller still has to ensure the concurrent index statement is not executed inside a transaction block.

The blocked example contains stronger static signals:

CREATE INDEX idx_users_email
 ON users (email);

ALTER TABLE users
 ALTER COLUMN email TYPE varchar(320);

DELETE FROM audit_events;

The checker reports five findings: two critical, one high, and two medium. The critical findings cover the column type change and unbounded delete. The regular index build is high severity, while the medium findings identify DDL without a preceding non-zero lock_timeout. The process exits with code 1, so CI can stop before deployment.

The examples test rule behavior, not the hardest PostgreSQL migration problems. A WHERE clause can still affect most rows of a large table, and a binary-compatible type change can be much cheaper than another statement with similar syntax. Production-scale changes still require workload-aware review.

Give CI a simple contract

The CLI exits with 0 when no blocking finding exists, 1 when the migration is blocked, and 2 for invalid command usage. A minimal workflow is:

- name: Install migration preflight
 run: npm ci

- name: Test the analyzer
 run: npm test

- name: Check the pending migration
 run: node src/check-migration.mjs path/to/migration.sql

JSON output can feed CI annotations or review tooling. The current test suite covers the passing case, the risky case, ordered timeout handling, and parser failure. I would introduce a gate gradually: start with rules supported by strong SQL evidence, measure false positives, and only then make broader organization-specific policies blocking.

The checker reports findings rather than rewriting migrations. Adding CONCURRENTLY can change transaction requirements and recovery steps, while splitting constraint creation and validation changes deployment sequencing. Those decisions still belong to the developer and reviewer.

Keep the boundary explicit

SQL text cannot reveal relation size, data distribution, long-running transactions, current lock holders, replica lag, WAL capacity, application traffic, or a migration framework’s transaction policy. PostgreSQL versions also differ in DDL behavior and available optimizations.

A passing preflight should therefore be followed by production-sized rehearsal and runtime verification for consequential migrations. That may include checking expected scans or rewrites, monitoring locks and replica lag, planning rollback procedures, and verifying constraints and indexes afterward. Following a failed concurrent index build, catalog state such as pg_index.indisvalid also needs inspection.

The value of a static gate is not that it understands production. Its value is that some mistakes do not require production knowledge to recognize. Catching those mistakes in CI leaves reviewers more time for the harder questions that static analysis cannot answer.

That is the boundary I want postgres-migration-preflight to maintain: a small, explainable first line of defense for PostgreSQL migrations, not a production-readiness oracle.

References

• PostgreSQL 18: Introduction to indexes
• PostgreSQL 18: CREATE INDEX
• PostgreSQL 18: DROP INDEX
• PostgreSQL 18: ALTER TABLE
• PostgreSQL 18: Client connection defaults, including lock_timeout
• PostgreSQL 18: pg_index catalog

This post is part of the Percona Community Writers Program.

A Fail-Fast PostgreSQL Migration Preflight for CI appeared first on MariaDB.org

Source

MariaDB Enterprise Server Maintenance Releases appeared first on MariaDB.org

SQL Server 2025 went generally available on 18 November 2025 and, as of this writing, sits at Cumulative Update 8 (build 17.0.4075.5, 13 August 2026). Strip away the AI features that took most of the launch coverage and there are four changes in this release that alter how a production SQL Server behaves under load and during a failover.

The first is optimized locking, which replaces per-row locks held to commit with a single transaction-ID lock. The second is tempdb space governance and ADR in tempdb, which turn the most common cause of a 3 a.m. outage into a per-workload error. The third is a set of Always On availability group knobs (a configurable group commit time, endpoint flow control, immediate failover on persistent health issues, full and differential backups on secondaries). The fourth is a new Standard edition ceiling of 32 cores and 256 GB of buffer pool with Resource Governor included, which is the largest scalability change for mid-market estates since 2016 SP1.

This post works through each of them for SQL Server 2025 performance, scalability and high availability, with the T-SQL to enable and verify them, and then lays out what is and is not available when the same engine runs in Azure SQL Managed Instance, Azure SQL Database, and Amazon RDS.

Everything here is version-pinned to 17.x and was checked against the current Microsoft Learn documentation. Where a feature is preview-only behind PREVIEW_FEATURES, I say so, because those are not production features however good the demo looks.

Which SQL Server 2025 build you should be on

The release cadence has settled into a monthly CU. The builds that matter for a production plan:

Two edition facts change the sizing conversation before any feature does. Standard edition now runs on the lesser of 4 sockets or 32 cores and addresses 256 GB of buffer pool, up from 24 cores and 128 GB in 2022, and it gets Resource Governor for the first time. Web edition is discontinued, and Express grows to a 50 GB database limit with the Advanced Services features folded in. A great many Enterprise licences in the field exist only because a 24-core or 128 GB ceiling was in the way; on SQL Server 2025 that argument needs re-examining with the actual workload numbers.

Performance: optimized locking is the feature to plan around

Optimized locking arrived in Azure SQL Database in 2023 and is on by default there. SQL Server 2025 brings it on-premises as an opt-in per database, and it is the single largest change to the engine’s concurrency behaviour since row versioning. It has two parts. Transaction ID (TID) locking: every row already carries the transaction ID of the last writer, so instead of holding an X lock on every modified row until commit, the engine takes an X lock on the transaction ID itself and releases the row and page locks as soon as each row is written; anyone who needs to wait for that row takes an S lock on the TID.

Lock after qualification (LAQ): with read committed snapshot isolation on, an UPDATE or DELETE evaluates its predicate against the latest committed row version without taking a U lock first, and only locks rows that actually qualify.

Enabling it on SQL Server 2025 is three statements, in this order, because optimized locking requires accelerated database recovery (the persistent version store is what makes releasing row locks early safe) and LAQ only works under RCSI:

-- SQL Server 2025: enable optimized locking on one database, in dependency order.
-- ADR is a prerequisite; RCSI is required for the LAQ half of the feature.
-- ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT needs exclusive access: schedule it.
ALTER DATABASE [ops] SET ACCELERATED_DATABASE_RECOVERY = ON;
ALTER DATABASE [ops] SET READ_COMMITTED_SNAPSHOT = ON WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [ops] SET OPTIMIZED_LOCKING = ON;

-- Verify all three; every column must be 1
SELECT name,
       is_accelerated_database_recovery_on,
       is_read_committed_snapshot_on,
       is_optimized_locking_on
FROM   sys.databases
WHERE  name = N'ops';

-- Rollback path: reverse order. Optimized locking must be off before ADR can be turned off.
-- ALTER DATABASE [ops] SET OPTIMIZED_LOCKING = OFF;
-- ALTER DATABASE [ops] SET ACCELERATED_DATABASE_RECOVERY = OFF;

What you see afterwards in sys.dm_tran_locks is a single XACT resource per writing transaction instead of a list of KEY and PAGE entries. Blocking shows up as the three new wait types, and the waiting session’s wait_resource reads XACT: followed by the transaction ID:

-- SQL Server 2025: who is holding the TID lock, and who is waiting on it
SELECT l.request_session_id  AS spid,
       l.resource_type,          -- XACT for TID locks
       l.request_mode,           -- X = writer, S = waiter
       l.request_status,
       l.resource_description,
       r.wait_type,              -- LCK_M_S_XACT_MODIFY / LCK_M_S_XACT_READ / LCK_M_S_XACT
       r.wait_time
FROM   sys.dm_tran_locks AS l
LEFT JOIN sys.dm_exec_requests AS r
       ON r.session_id = l.request_session_id
WHERE  l.resource_type = N'XACT'
ORDER  BY l.request_status, l.request_session_id;

-- Is LAQ being skipped on your workload? locking_stats fires every few minutes per database
CREATE EVENT SESSION [laq_watch] ON SERVER
ADD EVENT sqlserver.locking_stats,
ADD EVENT sqlserver.lock_after_qual_stmt_abort
ADD TARGET package0.event_file (SET filename = N'laq_watch')
WITH (STARTUP_STATE = ON);
ALTER EVENT SESSION [laq_watch] ON SERVER STATE = START;

Now the part that has to be in the change ticket. LAQ changes the answer a query can return under concurrent writes, because it evaluates the predicate against the last committed version rather than blocking on the uncommitted one. Microsoft’s own example is the honest one: transaction one runs UPDATE t SET b = 2 WHERE a = 1 and has not committed; transaction two runs UPDATE t SET b = 3 WHERE b = 2. Without LAQ, two blocks, then sees b = 2 and updates it. With LAQ, two reads the committed b = 1, the predicate fails, and it updates nothing.

Neither result is wrong under READ COMMITTED, but any application that silently depended on the blocking order will behave differently. If a workload relies on that order, it needs REPEATABLE READ or SERIALIZABLE on those statements, not a global rollback of the feature. LAQ also steps aside on its own for statements with UPDLOCK, XLOCK, HOLDLOCK or READCOMMITTEDLOCK hints, on tables with a columnstore index, for MERGE, and for statements with variable assignment or an OUTPUT clause; the locking_stats event above is how you find out how often that is happening.

My deployment stance: enable on one busy OLTP database at a time, after ADR has been on for at least a full business cycle so the persistent version store size is known, with the deadlock and blocking monitors already comparing against a pre-change baseline. Expect the first thing to disappear to be lock escalation on wide UPDATEs, and the wait type that appears in its place to be LCK_M_S_XACT_MODIFY where LCK_M_U used to be.

The rest of the query-processing list

Optional parameter plan optimization extends the parameter-sensitive plan machinery from 2022 to the WHERE (@p IS NULL OR col = @p) pattern that every reporting stored procedure has; it compiles separate plans for the NULL and non-NULL cases instead of one plan that is wrong for one of them. Degree of parallelism feedback, which was off by default in 2022, is now on: Query Store watches repeated executions and lowers MAXDOP for queries whose parallelism is not paying for itself.

Cardinality estimation feedback now covers expressions, not just join and containment assumptions. All three are compatibility level 170 behaviours with Query Store on, which is the pattern since 2022: the intelligent query processing features are Query Store features, so a database with Query Store off gets none of them.

The ABORT_QUERY_EXECUTION hint is small and useful. Attached through Query Store hints, it stops a known-bad query shape from executing at all until someone fixes it, which is the right answer to the runaway report that a business user re-runs every ten minutes:

-- Find the query_id from Query Store, then pin the hint to it. Nobody can run this shape now.
EXEC sys.sp_query_store_set_hints
     @query_id      = 4711,
     @query_hints   = N'OPTION (ABORT_QUERY_EXECUTION)';

-- Verify, and remove when the query is rewritten
SELECT query_id, query_hint_text, source_desc
FROM   sys.query_store_query_hints;

EXEC sys.sp_query_store_clear_hints @query_id = 4711;

tempdb: governance and ADR

Two tempdb changes in SQL Server 2025, and the first is the one I would put on every SQL Server 2025 instance on day one. Resource Governor can now cap the tempdb data space a workload group may use, and a session that crosses the cap gets error 1138 instead of filling the drive for everyone. The trap is that a percentage limit is only in force when the tempdb data files are either all capped with autogrow, or all uncapped with autogrow off; the default install (unlimited MAXSIZE, autogrow on) silently satisfies neither, and you get warning 10989 rather than a working limit. Use the MB form unless you have re-laid tempdb.

-- SQL Server 2025 tempdb governance: cap ad hoc reporting at 64 GB of tempdb data; leave the OLTP group unlimited
CREATE WORKLOAD GROUP [wg_reporting]
    WITH (GROUP_MAX_TEMPDB_DATA_MB = 65536,
          MAX_DOP = 8)
    USING [default];
ALTER RESOURCE GOVERNOR RECONFIGURE;

-- What each group is using right now, and how often the cap has fired
SELECT g.name,
       g.tempdb_data_space_kb / 1024                AS tempdb_mb_now,
       g.peak_tempdb_data_space_kb / 1024           AS tempdb_mb_peak,
       g.total_tempdb_data_limit_violation_count    AS cap_hits
FROM   sys.dm_resource_governor_workload_groups AS g
ORDER  BY g.peak_tempdb_data_space_kb DESC;

-- Error the offending session sees:
-- Msg 1138, Level 17: Could not allocate a new page for database 'tempdb'
-- because that would exceed the limit set for workload group 'wg_reporting'.

What is counted: temp tables, table variables, table-valued parameters, cursors, and every spill (sorts, hash, spools). What is not: the version store, including the ADR persistent version store, and the tempdb log. Global temp tables are charged to whichever group inserted the first row, which is a fairness problem you should know about before you cap a shared ETL group. Standard edition has Resource Governor now, so this applies to the mid-market estate too.

The second change is accelerated database recovery inside tempdb. Long rollbacks of temp-table-heavy batches used to hold the engine hostage exactly the way long rollbacks in user databases did before 2019; with ADR in tempdb they become instant. On Linux, tempdb can additionally sit on tmpfs, which is worth measuring on a memory-rich host before assuming NVMe is fast enough.

Backups: ZSTD and immutable targets

SQL Server 2025 backup compression gains a ZSTD option alongside the MS_XPRESS default and QAT. Microsoft’s claim is faster and smaller than MS_XPRESS; measure your own backup and restore windows before switching a fleet, because ZSTD trades CPU for ratio and a CPU-bound instance will notice:

BACKUP DATABASE [ops]
TO URL = N'https://${STORAGE_ACCOUNT}.blob.core.windows.net/backups/ops_full.bak'
WITH COMPRESSION (ALGORITHM = ZSTD),
     CHECKSUM,
     STATS = 5;

-- Compression ratio per backup, to justify the change with a number
SELECT TOP (20) b.database_name, b.backup_start_date,
       b.backup_size / 1048576.0            AS size_mb,
       b.compressed_backup_size / 1048576.0 AS compressed_mb,
       b.backup_size * 1.0 / NULLIF(b.compressed_backup_size, 0) AS ratio
FROM   msdb.dbo.backupset AS b
WHERE  b.type = 'D'
ORDER  BY b.backup_start_date DESC;

Backup to URL now works against immutable blob storage, which is the ransomware answer that used to require a third-party product, and on an availability group secondary it can now be a full or differential backup rather than copy-only, which changes how the backup preference on an AG should be set. More on that below.

Scalability: the Standard edition ceiling and read scale-out

The scalability story in SQL Server 2025 is less about a single engine change than about where the walls moved. A 32-core, 256 GB Standard edition instance is a serious OLTP server. Combined with Resource Governor to protect it from itself, tempdb governance, and optimized locking to keep lock memory flat, the workload that needed Enterprise for headroom reasons in 2022 frequently does not in SQL Server 2025.

The features that remain Enterprise-only and still matter for scale are the ones that always did: online index operations at full scope, partitioned table parallelism at its best, unlimited memory, and the full Always On availability group feature set (Standard is limited to basic availability groups with one database per group and no readable secondary).

For read scale-out on Enterprise, two small changes make readable secondaries first-class rather than a compromise. Query Store is now on by default for readable secondaries, so the plan regressions on the reporting replica are visible and fixable with the same hints mechanism as the primary. And SQL Server 2025 creates persisted statistics on readable secondaries; before this, statistics the secondary needed but the primary never built lived in tempdb on the secondary and evaporated on restart, which is why reporting on a secondary was slow every Monday morning after patching.

Columnstore gets ordered nonclustered columnstore indexes (the ordered clustered form arrived in 2022), online build for them, and a batch-mode path for a set of built-in functions and DATETRUNC. For an HTAP-style operational reporting table that is the combination that lets a single instance carry both the OLTP write path and the aggregate reads without a separate analytics copy:

-- SQL Server 2025 ordered NCCI, built online, on a hot OLTP table used for operational reporting.
-- ORDER controls rowgroup elimination on the reporting predicate; keep it to one or two columns.
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_order_line_reporting
    ON sales.order_line (order_date, region_id, sku_id, quantity, net_amount)
    ORDER (order_date)
    WITH (ONLINE = ON, MAXDOP = 4, COMPRESSION_DELAY = 10 MINUTES);

-- Rowgroup elimination evidence after a day of load
SELECT rg.state_desc, COUNT(*) AS rowgroups,
       MIN(rg.total_rows) AS min_rows, MAX(rg.total_rows) AS max_rows
FROM   sys.dm_db_column_store_row_group_physical_stats AS rg
WHERE  rg.object_id = OBJECT_ID(N'sales.order_line')
GROUP  BY rg.state_desc;

High availability: what changed in Always On

None of the availability group changes in SQL Server 2025 is a headline feature and together they are the most useful HA release since 2016. Each one is a knob for a specific failure we have all watched happen.

Availability group commit time is the one to test carefully, and the first thing to know is which way it works. The primary already batches commits for up to 10 milliseconds before shipping the log block to secondaries; that grouping is what keeps a busy AG from saturating the network with tiny sends. SQL Server 2025 exposes the window as an instance-level sp_configure option (default 0, meaning the built-in 10 ms), and the use case is lowering it: a latency-sensitive workload with modest transaction volume can shave up to 10 ms off synchronous commit at the cost of more, smaller sends.

On a high-volume system leave it alone; the batching is doing you a favour. Measure with HADR_SYNC_COMMIT waits per transaction before and after, at the same time of day:

-- SQL Server 2025 AG commit time: baseline first
SELECT wait_type, waiting_tasks_count, wait_time_ms,
       wait_time_ms * 1.0 / NULLIF(waiting_tasks_count, 0) AS avg_ms
FROM   sys.dm_os_wait_stats
WHERE  wait_type IN (N'HADR_SYNC_COMMIT', N'WRITELOG');

-- Then shorten the group commit window on the primary instance and re-measure
-- (advanced option; 0 = engine default of 10 ms; no restart required)
EXEC sys.sp_configure N'show advanced options', 1;  RECONFIGURE;
EXEC sys.sp_configure N'availability group commit time', 2;  RECONFIGURE;

-- Flow control between HADR endpoints, also instance-wide; the primary uses it to
-- detect a secondary falling behind. Check the option's current name and default
-- with sp_configure before scripting it: it is new in 17.x.
EXEC sys.sp_configure N'ucs_flow_control';

SQL Server 2025’s fast failover for persistent health issues closes a gap that has existed since 2012. When the AG resource’s health check fails, WSFC’s default is to try restarting the resource in place before failing it over, which on a node with a genuine problem adds a minute or more of unavailability while a restart that cannot succeed is attempted. Setting the restart threshold to zero on the AG resource tells the cluster to fail over immediately. This is set through the cluster, and the release notes document it alongside better health-check timeout diagnostics so that you can tell a genuine hang from a synchronisation stall:

# On any WSFC node: fail over the AG resource immediately on a persistent health failure
# instead of attempting an in-place restart first (default RestartThreshold is 3 within RestartPeriod)
Get-ClusterResource -Name 'ag_ops' | Set-ClusterParameter -Name RestartThreshold -Value 0

# Verify
Get-ClusterResource -Name 'ag_ops' | Get-ClusterParameter -Name RestartThreshold, RestartPeriod, HealthCheckTimeout

SQL Server 2025 backups on secondaries are no longer restricted to copy-only. That sentence changes the design of every Enterprise AG’s backup plan: the full and differential chain can now run entirely on a secondary, with the log backups wherever the backup preference sends them, and the primary’s I/O is left for the workload. Set AUTOMATED_BACKUP_PREFERENCE = SECONDARY_ONLY and make sure the backup job checks sys.fn_hadr_backup_is_preferred_replica before running, as it always should have.

The remaining items are operational quality of life: a listener IP can be removed without dropping the listener, read-only and read-write routing can be set to NONE to pull traffic back to the primary during maintenance, distributed availability groups now work between two contained availability groups (which makes the contained AG, introduced in 2022, usable for DR rather than only for HA), the asynchronous commit path in distributed AGs is less prone to saturating the link, and every replication and HA channel, including WSFC, AG endpoints, FCI, log shipping and linked servers, can run TDS 8.0 over TLS 1.3.

Databases that fail to read their persisted AG configuration during a network interruption now move to RESOLVING rather than staying in an ambiguous state, which is a small change that removes a confusing manual step from more than one runbook we maintain.

On-premises versus cloud: where each feature actually runs

The same 17.x engine appears in four shapes, and the feature list is not the same in each. The table is what I hand to clients deciding where a SQL Server 2025 workload should live.

The pattern is the one you would expect. Self-managed SQL Server 2025 gives you every knob in this post and makes you responsible for using them. Managed Instance gives you the engine-level features (optimized locking, the query processing improvements, columnstore changes) with the HA internals taken away and replaced with failover groups; if your reason for wanting SQL Server 2025 is the AG knobs, MI is not where you get them. Azure SQL Database had most of the performance features first and has never exposed an AG at all. RDS is a self-managed engine with a managed control plane: the database-scoped features work, the instance-level and cluster-level ones are AWS’s to configure, and the Azure-targeted integrations do not apply.

Where I would deploy SQL Server 2025, and where I would wait

As of September 2026, with CU8 out: for a new self-managed deployment, SQL Server 2025 on CU8 is the version, and the migration from 2019 in particular should not wait, given that 2019 has been on extended support only since February 2025. For an in-place upgrade of a stable 2022 estate, the case is strongest where the workload suffers from lock escalation or tempdb exhaustion, or where a Standard edition instance is capped at 24 cores or 128 GB; if none of those is true, 2022 is supported to 2033 and there is no urgency.

Turn on optimized locking one database at a time with the wait-stat baseline described above, put tempdb governance on every instance, and leave the preview features (vector indexes, Change Event Streaming, the fuzzy-matching functions, optimized sp_executesql) off in production until they lose the PREVIEW_FEATURES gate. In Azure, Managed Instance on the SQL Server 2025 update policy is the shape that keeps your on-premises and cloud feature surfaces aligned, which matters more than any single feature if you run both.

Test all of this against your own workload in a staging environment with production data volumes and production concurrency before it goes anywhere near production, keep a verified backup and a rehearsed restore in place before enabling any database-scoped option, and treat the LAQ semantic change as an application test item, not a database one. If you want a second pair of eyes on a SQL Server 2025 upgrade, an AG design, or a Standard-versus-Enterprise decision with the new ceilings, that is the work the MinervaDB SQL Server support team does every week, on-premises and in Azure and AWS.

References

What’s new in SQL Server 2025 · SQL Server 2025 release notes · SQL Server 2025 build versions · SQL Server 2025 lifecycle · Optimized locking · tempdb space resource governance · Editions and supported features of SQL Server 2025 · SQL Server 2025 is now generally available · Amazon RDS for SQL Server now supports SQL Server 2025 · Azure SQL Managed Instance update policy

SQL Server 2025 Performance, Scalability and High Availability: What Changed On-Premises and in the Cloud appeared first on MariaDB.org

Putting “one more component” in front of your servers to make them highly available just shifts the problem up by one layer.
From making the database HA, to making ProxySQL HA, to needing to make HAProxy HA and so on…

At some point, you may land on the common HA strategy, keepalived, but BGP ECMP is a powerful alternative worth considering.

What is BGP, what is ECMP?

As a brief summary, Border Gateway Protocol (BGP) is a routing protocol which operates over TCP. Equal-Cost Multi-Path (ECMP) defines that we want the traffic to be loadbalanced equally across the given routes.
Routers acting as BGP speakers are configured to accept routes for defined IP ranges and Autonomous System (AS) numbers, storing information about the networks that the router can reach in a Routing Information Base (RIB) table.
To benefit from Anycast, we will assign both/all ProxySQL nodes the same virtual IP address. BGP is then used to let the router know multiple routes to reach that IP. Configuring ECMP will cause the router to balance the traffic over these routes.

User defined health checks are executed by a BGP-speaking daemon, such as ExaBGP, running on the host to ensure that the router has the correct information about the status of a route.
As soon as a health check fails, ExaBGP will trigger a BGP update to withdraw the route. The router will remove the entry from its RIB table and stop forwarding packets to that host.
The traffic will be redistributed to the remaining healthy ProxySQL nodes.

For a more in depth guide about BGP please refer to Cisco press.

How ECMP based on BGP works

For our setup we can’t have the applications connect to the normal IPs of the ProxySQL nodes, as these are assigned to one node and fixed.
Instead we need a single separate anycast IP (let’s take 10.5.200.1/32) that we can assign to both ProxySQL nodes. This IP will be assigned to the loopback interface and has to be of a separate network, not overlapping with the IP-Range the normal ProxySQL node IPs are from.

When a packet with the destination set to the ProxySQL anycast IP (10.5.200.1) arrives at the router, the router checks its internal routing table to determine the next hop for the packet.
The router will see the two ProxySQL nodes in the cluster as potential next hop (as they both have the same anycast IP) and will pick one of the ProxySQL nodes, based on ECMP, to forward the packet to.
In ECMP the next hop is dynamically decided based on a 5-tuple hash from the packet header fields:

{ source IP address | destination IP address | protocol | source port | destination port }

Because the IP address and ports are in the hash, this ensures that the packets belonging to the same TCP stream are kept on the same path, to prevent packets of the same TCP connection ending up on multiple ProxySQL nodes.

You can visualise the setup like this:

By doing so, we have achieved high availability by leveraging BGP ECMP to loadbalance traffic in an active/active configuration across the ProxySQL nodes.
Additionally, the application config can be simplified, as only the single anycast IP (or DNS record for that IP) needs to be used for the ProxySQL cluster, and the logic of routing will be handled by the router.
Failures of a ProxySQL host are automatically handled by ExaBGP; a BGP update is sent to the router, and the route to the failed ProxySQL is withdrawn. The router redirects traffic to the remaining healthy ProxySQL, with no manual interventions required.
Extra infrastructure components (such as internal loadbalancers) can be avoided, eliminating additional network hops and improving network latency.

Alternative strategies for using BGP with databases

Of course, a ProxySQL cluster is not the only way to leverage BGP ECMP in order to achieve high availability for your databases. Some other strategies could be to use it for read-replica routing, local traffic routing, or for routing towards loadbalancers (e.g. haproxy).

Routing towards the database loadbalancer

Using BGP ECMP does not mean that you have to forego a database loadbalancer. You can configure your database servers to sit behind a database loadbalancer,
such as HAproxy or ProxySQL, and implement BGP ECMP in order to route traffic towards the loadbalancers and operate them highly available as true active/active pair.
If one of the loadbalancer instances dies, then BGP automatically takes care of routing the traffic to the remaining healthy peers for you.

Read replica routing

You can use BGP ECMP to distribute MySQL-Connections over multiple read replicas without using any Loadbalancer / ProxySQL at all. This saves you the additional latency and network hops of using a loadbalancer/ProxySQL.
If you need to ensure that you do not read from a replica which is lagging behind or has stopped replicating, you can implement this logic in the BGP health checks.

BGP Local Preference

If you have a multi-datacenter setup, you can choose to use local preference to keep traffic localised within the same datacenter. For example if you have an application server and a proxysql host in one datacenter (datacenter A), and an application server and proxysql host in a second datacenter (datacenter B),
you can tell the router to send traffic from the application to the proxysql within the same datacenter. The advantage of this is that it keeps network latency low, and avoids cross-site transit. Configuring this in BGP means that the application does not need to be aware of which datacenter it is running in. The BGP router handles localised routing for you.
If the local route would disappear, then the BGP router would automatically divert traffic from the application in datacenter A to the proxysql in the datacenter B.

Advantages of BGP

• One advantage of BGP over keepalived is that your anycast-nodes don’t need to be in the same subnet (especially useful for multi-datacenter setups). Keepalived instead requires the nodes to be part of the same Layer 2 Network.
• BGP ECMP supports active/active, unlike keepalived which only supports active/passive architectures.
• In BGP, the router has the overview of which node is healthy or unhealthy. In keepalived this knowledge resides in the keepalived process running on the node itself.
  As long as the nodes running keepalived can see each other, keepalived thinks everything is fine, but the nodes might have lost connection to the router.
  Whereas with BGP, health checks ensure that BGP is aware of the state of the route. In case there would be a network problem that would make the node unreachable, the route would disappear from the router.
• You can horizontally scale the nodes with BGP.
• BGP Local preference allows you to automatically route traffic within a datacenter, without the application needing to configure logic like “use ProxySQL-A when running in datacenter-A, otherwise ProxySQL-B.”
• You can set BGP to eliminate additional hops of infrastructure components, for example connecting to a pool of read replicas, without needing to connect over a loadbalancer.

Caveats with BGP

BGP ECMP is not connection-state aware, so if instances disappear/die or new instances join and the RIB table is rebuilt, the hashing algorithm will most likely forward packets for existing connections to a different instance than before. As that instance will not be aware of this TCP-Connection, it will send an RST-packet and the application will have to re-open its database connection.

In the next post, we will explain the technical details of setting up BGP ECMP for our ProxySQL cluster using OPNsense as Router.

This post is part of the Percona Community Writers Program.

ProxySQL HA with BGP ECMP Anycast appeared first on MariaDB.org

This is the MinervaDB Data Migration Team’s write-up of an Oracle Exadata to PostgreSQL 18 migration for an airline operations platform: crew scheduling, flight status, aircraft rotation and the reporting that hangs off them. The system left an Exadata X9M-2 quarter rack running Oracle 19c RAC and landed on community PostgreSQL 18 on bare metal in the airline’s own data centres, under Patroni, with pgBackRest for backup and point-in-time recovery. The client is anonymised and every figure below is rounded, but the method, the SQL, the PL/SQL mapping and the sizing arithmetic are exactly what we used.

The short version of the outcome: the workload met the p95 latency and peak-throughput targets we set from the Oracle AWR baselines, on three commodity servers, without a compatibility layer, and with the PL/SQL estate rewritten rather than emulated. The longer version, which is the useful one, is below. It is organised the way the engagement was: assessment, target selection, capacity planning and sizing, schema mapping, SQL mapping, PL/SQL to PL/pgSQL, data movement and cutover, and what we would do differently.

Starting state: the Exadata estate and the forcing constraint

The source was an Exadata X9M-2 quarter rack: two database servers, each with two 32-core Intel Xeon Platinum 8358 sockets and 1 TB of memory, and three High Capacity storage servers, each with sixteen-core Xeon storage CPUs, PMem in front of NVMe flash in front of 18 TB disks, all on the RoCE fabric. Oracle 19c ran as a two-node RAC with three pluggable databases, of which one, the operations PDB, was the migration scope. Allocated size was around 12 TB with roughly 9 TB of live data, the rest being free space inside tablespaces, undo and a large temp.

The workload profile that sized the PostgreSQL 18 target came from AWR, not from interviews. Peak periods (early morning rotation building and the evening crew-legality run) showed roughly 9,000 executions per second, of which about 70 percent were single-row lookups and short-range scans by flight, tail number or crew ID, the remainder being the legality and pairing logic that runs inside PL/SQL.

Around 2,400 concurrent sessions, most of them idle in application pools. Redo generation peaked near 40 MB/s. Physical reads were low because the buffer cache absorbed almost everything; the storage cells were doing very little smart scan for this PDB, which turned out to be the most important sizing fact of the whole engagement and I will come back to it.

The forcing constraint was commercial and contractual rather than technical: the Exadata support renewal and the Oracle licence position were coming up together, and the airline’s platform group had already standardised on PostgreSQL for new services. The technical question we were hired to answer was whether the operations platform, with its PL/SQL and its RAC dependency, could move without the performance regression everyone assumed came with leaving Exadata.

Assessment: inventory first, opinions later

Every Oracle Exadata to PostgreSQL 18 migration we run starts from the Oracle catalog, because interviews undercount PL/SQL by a factor of two or three. The inventory queries are short:

-- Object inventory for the schemas in scope
SELECT object_type,
       COUNT(*) AS objects
FROM   dba_objects
WHERE  owner IN ('OPS', 'CREW', 'FLT', 'RPT')
GROUP  BY object_type
ORDER  BY objects DESC;

-- PL/SQL volume by unit
SELECT owner, name, type,
       COUNT(*) AS loc
FROM   dba_source
WHERE  owner IN ('OPS', 'CREW', 'FLT', 'RPT')
GROUP  BY owner, name, type
ORDER  BY loc DESC;

-- Feature dependencies that have no direct community-PostgreSQL equivalent
SELECT owner, name, type, text
FROM   dba_source
WHERE  owner IN ('OPS', 'CREW', 'FLT', 'RPT')
AND    REGEXP_LIKE(UPPER(text),
       'PRAGMA AUTONOMOUS_TRANSACTION|DBMS_AQ|DBMS_SCHEDULER|UTL_FILE|DBMS_LOB|BULK COLLECT|FORALL|CONNECT BY|DBMS_SQL|UTL_HTTP|DBMS_RLS|FLASHBACK');

The rounded inventory for the operations PDB, and the conversion route we assigned to each class, is the matrix that the effort estimate came from:

Two findings from this phase changed the plan. First, the RAC dependency was an availability requirement, not a scale requirement; the second instance was there to survive a node failure, and inter-instance traffic showed it was carrying a fraction of the load. That maps to a Patroni-managed replica set, not to a distributed PostgreSQL. Second, the six ON COMMIT fast-refresh materialised views were load-bearing for the reporting schema and PostgreSQL 18 has no incremental refresh in core; they needed a design decision, not a conversion.

ora2pg 25.0 did the mechanical part of the assessment. Its migration-cost report is a useful floor for the estimate, not the estimate:

# ora2pg 25.0, assessment mode against the operations PDB
ora2pg -c ora2pg.conf -t SHOW_REPORT --estimate_cost --dump_as_html 
       -O "SCHEMA=OPS,CREW,FLT,RPT" > ops_pdb_assessment.html

# Object-level detail for the matrix
ora2pg -c ora2pg.conf -t SHOW_TABLE   -O "SCHEMA=OPS"
ora2pg -c ora2pg.conf -t SHOW_COLUMN  -O "SCHEMA=OPS"

Why community PostgreSQL 18, and what we rejected

We considered four targets and wrote down why three lost, because this section is what the client’s architecture board actually wanted to read.

EDB Postgres Advanced Server with its Oracle-compatible PL/SQL dialect would have cut the package rewrite substantially. We rejected it because the airline’s stated goal was to remove vendor lock-in, and EPAS trades Oracle lock-in for EDB lock-in. That is a legitimate trade when the rewrite budget is the binding constraint; here it was not, and 90,000 lines of PL/SQL is a rewrite a team of four can do in a quarter with the right tooling. We said so in writing and the board agreed.

PostgreSQL 17 was the safer-looking landing zone when we started scoping in early 2026, with 18 only a few minors old. We chose 18 for three reasons specific to a migration, not because it was newer. The pg_upgrade statistics carry-over matters for the next major upgrade rather than this cutover, but it means the client is not planning a stats-cold upgrade during stabilisation. PostgreSQL 18’s asynchronous I/O and the default effective_io_concurrency of 16 change how a multi-terabyte table scans on NVMe, which affected sizing. And 18’s end of life is November 2030, four years from cutover. PostgreSQL 19 was still in beta throughout the project and is not a production target until it has a couple of minors behind it.

A managed cloud PostgreSQL 18 was rejected on data residency and on the airline’s requirement to run the operations platform in its own two data centres. Distributed PostgreSQL (Citus, or a YugabyteDB-style fork) was rejected because the workload is not sharded by nature and, as the AWR numbers showed, does not need to be; a single well-sized primary with synchronous standbys covers it with headroom.

Capacity planning and sizing the PostgreSQL 18 stack

The mistake we see most often on an Exadata exit is sizing the PostgreSQL 18 hardware from Exadata’s CPU utilisation. Exadata CPU utilisation on the database servers understates the work because smart scan, storage indexes and flash cache absorb I/O and filtering on the cells. On a PostgreSQL 18 target every one of those bytes comes back to the database host. So we size from logical work, not from host utilisation. The inputs, all from AWR and ASH over a four-week window covering month-end and a schedule change:

CPU

Twenty-two Oracle CPU-seconds per second is about 22 fully busy cores at peak. We remove the RAC and ASM overhead (measured from the instance-level wait profile at around 10 percent), then add PostgreSQL’s own background work. Autovacuum on a 9 TB estate with a 40 MB/s write rate is real CPU, checkpointing is real CPU, and PostgreSQL’s connection processes carry per-process overhead that Oracle’s shared server does not.

Our working rule from previous exits is 1.3 to 1.5 times the corrected Oracle CPU figure for headroom on a single primary, so 28 to 30 cores at peak. We specified 64 cores per node (two 32-core sockets) to leave room for reporting queries that were about to lose smart scan, and because NVMe throughput on a modern two-socket box is wasted with fewer cores driving it.

Memory

The working set was measured, not assumed. We restored the PDB to a scratch server, ran the captured top-50 SQL plus the batch jobs against a cold cache with pg_buffercache and pg_stat_io sampling, and watched the buffer pool stabilise at roughly 380 GB of distinct blocks touched in a peak hour. That set the memory floor: 1 TB per node, with shared_buffers at 256 GB and the remainder left to the OS page cache, work_mem allocations and maintenance operations. We do not go past a quarter of RAM for shared_buffers on a mixed workload without a measured reason, and here the double-buffering cost of going higher was not worth the marginal hit-rate gain we saw in testing.

Storage

Nine terabytes of Oracle data does not become nine terabytes of PostgreSQL 18 data. NUMBER-to-numeric mapping, the absence of Oracle’s row-level compression on a few large tables, and PostgreSQL’s per-tuple header made the converted data about 10 percent larger; the index estate shrank because we dropped redundant indexes. We plan for the converted size times a 1.3 bloat and maintenance factor, plus WAL retention for the synchronous replica and PITR, plus space to rebuild the largest table’s indexes concurrently.

The specification per node was six 7.68 TB NVMe drives in RAID 10 (about 23 TB usable), a separate pair of 1.92 TB NVMe for WAL, and the pgBackRest repository on object storage at the DR site. The WAL device was sized for sustained 40 MB/s writes with fsync latency under a millisecond, which any current enterprise NVMe delivers; we measured it with pg_test_fsync before accepting the hardware.

Connections

Twenty-four hundred sessions cannot become 2,400 PostgreSQL 18 backends. PgBouncer in transaction pooling mode fronts the primary with a server pool sized from active sessions, not connected sessions: 60 active at peak became a default pool of 96 with a reserve, and max_connections on the server was set to 300 to leave room for replication, monitoring and administrative sessions. The application team had to remove session-state assumptions (temporary tables and SET commands that assumed a sticky session) that transaction pooling breaks, and PgBouncer 1.24 and later enable prepared statements by default, which the JDBC driver configuration had to account for.

The postgresql.conf that came out of the sizing

Only the parameters we changed from the PostgreSQL 18 defaults, with the reasoning and the restart requirement, because that is the format the client’s change board needed:

Two of those need a warning. random_page_cost at 1.1 is right for local NVMe and wrong for anything network-attached; it is the first thing we check when a client copies a config between environments. And io_workers at 12 was arrived at by measuring, starting from the default of 3, doubling, and watching pg_stat_io and the I/O wait events; it is not a formula, and on a smaller box 6 was enough.

Schema mapping: Oracle to PostgreSQL 18

The type mapping is the part of an Oracle Exadata to PostgreSQL 18 migration that ora2pg gets mostly right and that we still review column by column on the hot tables, because the defaults are conservative in a way that costs performance.

The crew assignment table is a good example of where PostgreSQL 18 gave us something Oracle did not have in the schema at all. Crew legality rules forbid overlapping assignments for the same crew member. On Oracle this was enforced by a trigger and a package. On PostgreSQL 18 it is a temporal primary key:

-- PostgreSQL 18: temporal primary key replaces the Oracle trigger + package pair
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE crew.assignment (
    assignment_id   bigint GENERATED ALWAYS AS IDENTITY,
    crew_member_id  bigint      NOT NULL,
    pairing_id      bigint      NOT NULL,
    duty_period     tstzrange   NOT NULL,
    base_iata       char(3)     NOT NULL,
    duty_hours      numeric(5,2)
        GENERATED ALWAYS AS (
            EXTRACT(EPOCH FROM (upper(duty_period) - lower(duty_period))) / 3600.0
        ) STORED,
    created_at      timestamptz NOT NULL DEFAULT now(),
    row_uid         uuid        NOT NULL DEFAULT uuidv7(),
    CONSTRAINT pk_assignment
        PRIMARY KEY (crew_member_id, duty_period WITHOUT OVERLAPS),
    CONSTRAINT fk_assignment_crew
        FOREIGN KEY (crew_member_id) REFERENCES crew.member (crew_member_id),
    CONSTRAINT ck_assignment_period
        CHECK (NOT isempty(duty_period))
);

-- range-partitioned tables (flight_leg, by operating_date) keep their monthly layout;
-- assignment stays unpartitioned so the temporal primary key can be a single GiST index
CREATE INDEX ix_assignment_pairing
    ON crew.assignment (pairing_id, crew_member_id);

The table is deliberately not partitioned. A unique or primary key on a partitioned table has to include the partition key, and a temporal key over a range column cannot do that cleanly, so the 38 partitioned tables in the estate are the big append-only ones (flight_leg by operating_date, the movement log, the audit tables) and the assignment table, at a few hundred million rows, stays whole. The WITHOUT OVERLAPS constraint removed a 300-line trigger and package pair, and a set of race conditions that had been patched around for years, because a constraint is checked under the same lock the row insert takes and the trigger was not.

Virtual Private Database policies became row-level security. This is the pattern for the nine policies, shown for the base-scoped one:

-- Oracle: DBMS_RLS.ADD_POLICY(... policy_function => 'sec.base_predicate' ...)
-- PostgreSQL 18: the predicate becomes a policy on the table

ALTER TABLE ops.flight_leg ENABLE ROW LEVEL SECURITY;
ALTER TABLE ops.flight_leg FORCE ROW LEVEL SECURITY;   -- applies to the table owner too

CREATE POLICY p_flight_leg_base ON ops.flight_leg
    FOR ALL
    TO ops_app, ops_reporting
    USING (
        origin_base = current_setting('app.base_iata', true)
        OR pg_has_role(current_user, 'ops_network_control', 'member')
    );

-- the application sets the context once per transaction through PgBouncer:
-- SET LOCAL app.base_iata = 'BLR';

The SET LOCAL is deliberate. Under transaction pooling a plain SET leaks to whichever client next gets the server connection; SET LOCAL dies with the transaction, which is the property VPD’s session context gave you for free and PgBouncer takes away.

Materialised views

The six ON COMMIT fast-refresh materialised views had no equivalent and we did not pretend otherwise. Three were replaced by summary tables maintained by statement-level triggers on the base tables (the same mechanism Oracle used underneath, made explicit). Two were replaced by ordinary views once we confirmed PostgreSQL 18 ran the underlying aggregate fast enough with parallel query and a covering index, which Oracle’s design predated. One, the crew-hours ledger, kept its trigger-maintained summary plus a nightly REFRESH MATERIALIZED VIEW CONCURRENTLY reconciliation so drift could be detected and corrected rather than assumed away.

The empty-string audit

Oracle treats '' as NULL; PostgreSQL 18 does not. Every migration has this, and it is a silent-corruption class rather than an error class, so we treat it as a test-suite item. The audit query against the Oracle source found the columns that could ever carry the ambiguity:

-- On Oracle: which VARCHAR2 columns are nullable and are compared to '' or wrapped in NVL in code
SELECT DISTINCT c.table_name, c.column_name
FROM   dba_tab_columns c
JOIN   dba_source s
       ON s.owner = c.owner
       AND REGEXP_LIKE(UPPER(s.text), '(NVLs*(s*' || c.column_name || '|' || c.column_name || 's*(=||!=)s*'''')')
WHERE  c.owner = 'OPS'
AND    c.data_type = 'VARCHAR2'
AND    c.nullable = 'Y';

Every column that came back got a CHECK (col '') constraint on the PostgreSQL 18 side and a load-time transform that turned empty strings into NULL, so the converted code could keep its IS NULL semantics without a per-predicate rewrite.

SQL mapping: the patterns that actually appeared

ora2pg converts the syntactic Oracle-isms. What it cannot do is tell you which converted statement will plan badly. We took the AWR top 50 by elapsed time and by executions, converted them, and ran each under EXPLAIN (ANALYZE, BUFFERS) (buffers are on by default in PostgreSQL 18’s EXPLAIN ANALYZE) against the restored data. The recurring patterns:

Old-style outer joins were the most common conversion, and ora2pg handles them. CONNECT BY appeared in the pairing engine for building multi-leg duty chains and became a recursive CTE; the performance was comparable because both are effectively iterative. ROWNUM pagination became FETCH FIRST n ROWS ONLY, but the fifteen queries that used ROWNUM as a row-number-in-order-of-arrival had to be checked one by one because that behaviour is undefined without ORDER BY in both engines and Oracle happened to be consistent about it.

The interesting case was the flight-status lookup, the single most executed statement in the system. On Oracle it used a composite index on (operating_date, carrier_code, flight_number, leg_sequence) and was usually called with all four columns. A second, heavily used variant called it without carrier_code because the caller had the flight number from a codeshare feed. Oracle satisfied that with an index skip scan. PostgreSQL before 18 would not, and the standard fix was a second index. PostgreSQL 18 added skip scan for multicolumn B-tree indexes, and the plan shows it:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT leg_id, status_code, eta_utc
FROM   ops.flight_leg
WHERE  operating_date = DATE '2026-09-07'
AND    flight_number  = 1207
AND    leg_sequence   = 1;

-- Index Only Scan using ix_flight_leg_lookup on flight_leg
--   Index Cond: ((operating_date = '2026-09-07'::date) AND (flight_number = 1207) AND (leg_sequence = 1))
--   Index Searches: 6          <-- one search per distinct carrier_code on that date: the skip scan
--   Heap Fetches: 0
--   Buffers: shared hit=31
-- (abridged; the Index Searches line is new EXPLAIN output in PostgreSQL 18)

Six index searches instead of a second 40 GB index. The condition for skip scan to pay off is a low-cardinality leading column being skipped, which carrier_code is (the airline plus a handful of codeshare partners). We checked the Index Searches count under load rather than trusting the plan shape; when the skipped column has thousands of distinct values, skip scan is worse than a dedicated index and the planner usually, but not always, knows it.

Hints were the other PostgreSQL 18 conversation. The PL/SQL estate carried around 80 /*+ ... */ hints, most of them stale. Community PostgreSQL 18 has no hints and we did not install pg_hint_plan; we fixed the underlying statistics problems instead, which in four cases meant CREATE STATISTICS on correlated columns (base and carrier, date and season) and in one case a partial index. PostgreSQL 19’s in-core plan advice will change this conversation for future migrations; it was not an option for this one.

PL/SQL to PL/pgSQL: how 110 packages became schemas and functions

Packages do not exist in community PostgreSQL 18. The mechanical mapping is one schema per package, one function or procedure per package member, and a decision about package state. Ninety of the 110 packages were stateless once we looked, which meant the mapping was mechanical. The other 20 held session state in package variables (the current legality rule set, cached lookup tables, a “current run ID” for the rotation engine). Those became either SET LOCAL custom GUCs read with current_setting() for scalar state, or a session-scoped unlogged table keyed by pg_backend_pid() for anything larger. The GUC route is the one that survives transaction pooling correctly, so it was the default.

Here is a cut-down member of the legality package as it ran on Oracle and as it runs on PostgreSQL 18. It computes the cumulative duty hours for a crew member inside a rolling window, using BULK COLLECT because that was the idiom:

-- Oracle PL/SQL (abridged), CREW.LEGALITY package
FUNCTION duty_hours_in_window (
    p_crew_member_id IN NUMBER,
    p_window_end     IN TIMESTAMP WITH TIME ZONE,
    p_window_days    IN NUMBER DEFAULT 7
) RETURN NUMBER IS
    TYPE t_hours IS TABLE OF NUMBER;
    l_hours  t_hours;
    l_total  NUMBER := 0;
BEGIN
    SELECT duty_hours
    BULK COLLECT INTO l_hours
    FROM   crew.assignment
    WHERE  crew_member_id = p_crew_member_id
    AND    duty_start >= p_window_end - p_window_days
    AND    duty_start <  p_window_end;

    FOR i IN 1 .. l_hours.COUNT LOOP
        l_total := l_total + l_hours(i);
    END LOOP;
    RETURN l_total;
EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN 0;
    WHEN OTHERS THEN
        crew.errlog.log_error('duty_hours_in_window', SQLERRM);   -- autonomous transaction inside
        RAISE;
END duty_hours_in_window;

-- PostgreSQL 18 PL/pgSQL, schema crew_legality (one schema per former package)
CREATE OR REPLACE FUNCTION crew_legality.duty_hours_in_window (
    p_crew_member_id bigint,
    p_window_end     timestamptz,
    p_window_days    integer DEFAULT 7
) RETURNS numeric
LANGUAGE plpgsql
STABLE
PARALLEL SAFE
AS $$
DECLARE
    l_total numeric := 0;
BEGIN
    -- BULK COLLECT + loop collapses to a set-based aggregate; the planner does the loop
    SELECT COALESCE(SUM(a.duty_hours), 0)
    INTO   l_total
    FROM   crew.assignment AS a
    WHERE  a.crew_member_id = p_crew_member_id
    AND    a.duty_period && tstzrange(p_window_end - make_interval(days => p_window_days),
                                      p_window_end, '[)');
    RETURN l_total;
EXCEPTION
    WHEN OTHERS THEN
        -- no autonomous transactions, and a STABLE function cannot write anyway:
        -- the NOTICE goes to the server log (shipped centrally); the durable errlog row
        -- is written by the calling procedure using the savepoint pattern below
        RAISE NOTICE 'crew_legality.duty_hours_in_window failed: % (%)', SQLERRM, SQLSTATE;
        RAISE;
END;
$$;

Three things in that conversion are the whole story of the PL/SQL work. The BULK COLLECT and loop became a single aggregate, which is what almost every BULK COLLECT in the estate wanted to be; the Oracle idiom exists to avoid context switches between the SQL and PL/SQL engines, and PL/pgSQL does not have that boundary in the same way. The NO_DATA_FOUND handler disappeared because SELECT ... INTO in PL/pgSQL leaves the variable NULL rather than raising when no row is found (the opposite of Oracle; this is the second most common behavioural trap after the empty-string one, and we grep for every SELECT INTO and decide whether it needs STRICT). And the autonomous-transaction error log is gone.

Autonomous transactions were the one Oracle feature with no clean answer, and we had 14 packages using them, all for the same purpose: write an error or audit row that survives the rollback of the enclosing transaction. We rejected dblink loopback connections for this, because under a 9,000-transaction-per-second workload a loopback connection per error is a connection storm waiting for a bad day. The pattern we used instead: the error row is inserted in the enclosing transaction, and the enclosing transaction is not rolled back on a business exception; the exception is caught at the procedure boundary, the work is undone with a savepoint, and the log row is kept.

For the two cases where the transaction had to abort entirely, the application logs, not the database.

-- PostgreSQL 18 savepoint pattern replacing PRAGMA AUTONOMOUS_TRANSACTION for audit-on-failure
CREATE OR REPLACE PROCEDURE crew_ops.apply_pairing (p_pairing_id bigint)
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        -- the unit of work that may fail
        PERFORM crew_ops.assign_pairing_members(p_pairing_id);
        PERFORM crew_legality.validate_pairing(p_pairing_id);
    EXCEPTION
        WHEN check_violation OR exclusion_violation THEN
            -- inner block acts as a savepoint: its writes are rolled back, the outer transaction survives
            INSERT INTO crew_ops.errlog (unit_name, sqlstate, message, ref_id, logged_at)
            VALUES ('crew_ops.apply_pairing', SQLSTATE, SQLERRM, p_pairing_id, clock_timestamp());
            RETURN;   -- caller commits: the audit row persists, the pairing does not
    END;
END;
$$;

The remaining constructs mapped as follows. DBMS_SCHEDULER jobs became pg_cron entries for anything that is a single SQL statement or procedure call, and moved to the airline’s existing application scheduler for the twelve job chains with dependencies, because pg_cron does not do chains and pretending otherwise produces fragile shell scripts. UTL_FILE writes (crew reports to a shared filesystem) moved to the application tier with COPY ... TO STDOUT feeding it; the database no longer touches the filesystem.

DBMS_LOB calls became ordinary text and bytea operations. REF CURSOR outputs became refcursor or, more often, set-returning functions, which the JDBC layer handles better. DBMS_AQ is covered above. Object types with methods became composite types plus functions taking the composite as the first argument, which PostgreSQL’s function-call syntax lets you write as value.method() anyway.

-- Partition maintenance that used to be a DBMS_SCHEDULER job, now pg_cron on PostgreSQL 18
SELECT cron.schedule(
    'flight_leg_partitions',
    '0 2 1 * *',                       -- 02:00 on the 1st, server time zone UTC
    $$ CALL ops_maint.create_month_partition('ops.flight_leg', date_trunc('month', now() + interval '2 months')) $$
);

-- Monthly reconciliation of the crew-hours ledger against its summary
SELECT cron.schedule(
    'crew_hours_ledger_refresh',
    '30 3 * * *',
    $$ REFRESH MATERIALIZED VIEW CONCURRENTLY rpt.crew_hours_ledger $$
);

Every converted unit was tested on PostgreSQL 18 against the Oracle original with a differential harness: the same inputs, both engines, results compared, and every mismatch classified as a defect in the conversion or a defect in the original that Oracle had been hiding. The second category was not empty.

Data movement and cutover

The nine terabytes moved twice: once in bulk for the parallel-run environment, and once more through change data capture for the cutover. Bulk load used ora2pg’s parallel COPY export straight into PostgreSQL 18 with indexes and foreign keys dropped, then rebuilt in parallel afterwards; on the target hardware the load ran at a rate bounded by the Oracle-side extract, not by PostgreSQL 18.

# ora2pg 25.0 bulk data export, direct to PostgreSQL 18, 16 parallel table jobs, 8 parallel per-partition jobs
ora2pg -c ora2pg.conf -t COPY 
       -O "SCHEMA=OPS" 
       -O "PG_DSN=dbi:Pg:dbname=ops;host=pg-node1;port=5432" 
       -O "PG_USER=${PG_MIG_USER}" -O "PG_PWD=${PG_MIG_PASSWORD}" 
       -O "JOBS=8" -O "ORACLE_COPIES=8" -O "PARALLEL_TABLES=16" 
       -O "DROP_INDEXES=1" -O "DROP_FKEY=1" -O "TRUNCATE_TABLE=1" 
       -O "DATA_LIMIT=20000" -O "BLOB_LIMIT=500" 
       -O "EMPTY_LOB_NULL=1" -O "REPLACE_ZERO_DATE=-INFINITY"

# then rebuild, in parallel, with the maintenance memory sized above
psql -h pg-node1 -d ops -c "SET maintenance_work_mem = '8GB'; SET max_parallel_maintenance_workers = 8;" 
     -f ops_indexes.sql

Validation after every load into PostgreSQL 18 was row counts per table plus a content checksum on the 40 critical tables, computed the same way on both sides so the numbers are comparable:

-- Oracle side
SELECT COUNT(*) AS rows_,
       SUM(ORA_HASH(leg_id || '|' || flight_number || '|' || TO_CHAR(std_utc, 'YYYYMMDDHH24MISS') || '|' || status_code)) AS chk
FROM   ops.flight_leg
WHERE  operating_date >= DATE '2026-01-01';

-- PostgreSQL 18 side: same columns, same formatting, a 64-bit hash folded to match ranges
SELECT COUNT(*) AS rows_,
       SUM(hashtextextended(leg_id::text || '|' || flight_number::text || '|' ||
                            to_char(std_utc AT TIME ZONE 'UTC', 'YYYYMMDDHH24MISS') || '|' || status_code, 0)) AS chk
FROM   ops.flight_leg
WHERE  operating_date >= DATE '2026-01-01';

The hash functions differ, so the checksum is compared per row on a sample and by count and aggregate on the whole; what matters is that the row-shape string is identical on both sides, including the timestamp formatting, because that is where the DATE-to-timestamptz mapping shows up if it is wrong.

The change-data-capture bridge was Debezium’s Oracle connector reading LogMiner, started from the SCN recorded at the bulk export, into Kafka (already in the estate) and then into PostgreSQL 18 through a sink with the type mapping applied in the sink. Three weeks of parallel run gave us three weeks of reconciliation reports and three weeks of the converted PL/SQL running against real change volume.

The cutover itself was the boring part: application pools drained, writes frozen on Oracle, CDC lag watched to zero, sequences on PostgreSQL 18 advanced past the Oracle high-water marks, connection strings switched at PgBouncer, and reverse CDC from PostgreSQL 18 to Oracle running so that a rollback in the first week would have been a connection-string change and not a data-loss event.

It was never needed. It was drilled twice.

-- Sequence advance at cutover: PostgreSQL 18 value must exceed every Oracle value ever issued
-- (Oracle CACHE means the last issued value can exceed LAST_NUMBER; use the data, not the catalog)
SELECT setval('ops.flight_leg_leg_id_seq',
              (SELECT COALESCE(MAX(leg_id), 0) + 1000 FROM ops.flight_leg),
              false);

-- Verification after, generated per owned sequence from pg_depend and run as one script:
-- every row must return ok = true before the connection strings are switched
SELECT 'ops.flight_leg_leg_id_seq' AS sequence_name,
       (SELECT last_value FROM ops.flight_leg_leg_id_seq)           AS seq_value,
       (SELECT MAX(leg_id) FROM ops.flight_leg)                     AS table_max,
       (SELECT last_value FROM ops.flight_leg_leg_id_seq)
         > (SELECT MAX(leg_id) FROM ops.flight_leg)                 AS ok;

Performance and scalability on the PostgreSQL 18 stack: what we measured

We do not publish the client’s numbers, so this section describes what was measured and how, and states the outcome against the targets rather than as absolute figures. The targets were set from Oracle: for each of the top 50 statements, the AWR p95 elapsed time in the peak hour, and for the system, the peak execution rate with 25 percent headroom. The PostgreSQL 18 side of the comparison came from pg_stat_statements with track_io_timing on, sampled at the same hours over the parallel run, and from the load test that replayed captured traffic at 1.25 times peak rate.

-- PostgreSQL 18 post-cutover top SQL, comparable to the AWR "SQL ordered by Elapsed Time" section (PG 17+ column names)
SELECT queryid,
       calls,
       ROUND(total_exec_time::numeric / 1000, 1)               AS total_s,
       ROUND(mean_exec_time::numeric, 3)                       AS mean_ms,
       ROUND((shared_blk_read_time + shared_blk_write_time)::numeric, 1) AS io_ms,
       shared_blks_hit,
       shared_blks_read,
       ROUND(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct,
       LEFT(query, 80)                                          AS query
FROM   pg_stat_statements
WHERE  dbid = (SELECT oid FROM pg_database WHERE datname = 'ops')
ORDER  BY total_exec_time DESC
LIMIT  50;

-- Where the time goes at the I/O layer (PostgreSQL 18: WAL I/O included here, per backend type)
SELECT backend_type, object, context,
       reads, read_time, writes, write_time, fsyncs, fsync_time
FROM   pg_stat_io
WHERE  reads > 0 OR writes > 0
ORDER  BY read_time + write_time DESC;

The outcome against the targets: all 50 statements met their p95 target at 1.25 times peak; the legality batch, which had been the item everyone expected to regress without Exadata, ran inside its window with margin once the BULK COLLECT loops were set-based; and the three reporting queries that had leaned on smart scan were the only ones that needed design work, which parallel query and one covering index resolved. The cache-hit ratio on pg_stat_io at peak sat where the working-set measurement predicted it would, which is the check that the sizing method was sound rather than lucky.

On scalability, the design has two levers left unpulled. Read scaling goes to the standbys through Patroni’s replica routing, and the reporting workload already runs there. Write scaling on a single primary is bounded by the 64 cores and the WAL device; at 1.25 times peak the primary was under half utilised on CPU and the WAL device was under a quarter of its measured throughput.

The airline’s growth plan does not reach the point where a single primary becomes the constraint inside the PostgreSQL 18 support window, and we wrote the trigger conditions (sustained CPU over 60 percent at peak, WAL write latency over 2 ms) into the handover so the conversation about partitioning across nodes starts from a measurement, not a feeling.

What we would do differently

Three things. We would run the working-set measurement before agreeing the hardware specification with procurement rather than in parallel with it; it confirmed the 1 TB nodes but it could have told us 768 GB was enough and saved money. We would convert the six materialised views before the PL/SQL rather than after, because the reporting team’s acceptance testing was gated on them and it became the critical path. And we would have put the AFTER-trigger role change in PostgreSQL 18 on the assessment checklist from the start; two audit triggers written for Oracle assumed the committing role, PostgreSQL 18 runs them as the role that queued the event, and we found it in test rather than in review.

One thing we would not change: rewriting the PL/SQL instead of emulating it. It cost a quarter of a four-person team and it produced code the airline’s own engineers can read, which is the point of leaving a proprietary platform.

Reproducibility and caveats

Versions, so the claims above can be checked: Oracle Database 19c on Exadata X9M-2; PostgreSQL 18.6 (the current minor at time of writing, released 13 August 2026); ora2pg 25.0; Patroni 4.1.x with etcd 3.5; pgBackRest 2.59; PgBouncer 1.25.2; pg_cron 1.6 or later; Debezium 3.x Oracle connector with LogMiner. Every sizing figure is rounded and the client is anonymised; the arithmetic applies to your estate only after you have replaced our AWR numbers with yours.

Test every conversion pattern here against your own PL/SQL and your own data in a staging environment that mirrors production topology, keep a verified backup and a rehearsed restore in place before any cutover, and keep the reverse CDC path warm until sign-off; a migration without a rollback path is a bet, not a plan.

If you are planning an Exadata exit and want the assessment matrix, the sizing method, or the PL/SQL conversion patterns applied to your estate, that is the work the MinervaDB PostgreSQL consulting team does, on-premises and in the cloud, with the same rule we apply to ourselves: the numbers come from the catalog and the AWR, never from the sales deck.

References

PostgreSQL 18 release notes · PostgreSQL release history · Multicolumn indexes and skip scan · Constraints, including WITHOUT OVERLAPS · Resource consumption parameters (io_method, io_workers) · pg_stat_io and cumulative statistics · Row security policies · ora2pg · Patroni · pgBackRest · PgBouncer · pg_cron · Debezium Oracle connector · Oracle Exadata X9M · Exadata X9M-2 data sheet

Oracle Exadata to PostgreSQL 18 Migration: An Airline Operations Case Study from the MinervaDB Data Migration Team appeared first on MariaDB.org

This post walks through each of those for MongoDB 8.3 high availability and scalability work, on-premises and in Atlas, and ends with where I would and would not deploy MongoDB 8.3 today.

One thing to get out of the way first, because it changes the whole decision: MongoDB 8.3 is a minor release, not the next long-term line. That has consequences on-premises that Atlas customers never see.

Which MongoDB 8.x are you actually running?

MongoDB’s cadence since 8.0 has two tracks. Major releases (7.0, 8.0) arrive roughly every two years, run on Atlas and on-premises, and carry a five-year lifecycle. Minor releases (8.1, 8.2, 8.3) arrive quarterly-ish and until 8.2 were Atlas-only. Starting with 8.2 the minor track is downloadable for Community and Enterprise Advanced, which is how MongoDB 8.3 ends up on an on-prem server in the first place.

The support policy for minors is the part people miss: once a new minor ships, the previous one stops receiving patches. 8.2 reached end of life on 31 July 2026, ten months after it was released. If you deployed 8.2 on-premises last autumn you are already off support unless you have moved to MongoDB 8.3.

Three more rules from the versioning policy that shape a self-managed upgrade plan. Minor-to-minor upgrades are strictly sequential, so an 8.1 cluster goes through 8.2 before it can reach MongoDB 8.3, and each hop is a binary upgrade plus an FCV bump. Going from a minor back to a major is a downgrade, and binary downgrades are not supported on Community Edition at all. And two features are explicitly unsupported on the minor track: Atlas Live Migration and mongosync. If you have a cluster-to-cluster sync running for DR, or you are planning a migration into Atlas next year, a MongoDB 8.3 source takes those tools off the table.

The 8.0 line, by contrast, is the one that receives backports. The connection rate limiter that I cover below is documented as available in 8.0.12, and the initial sync index memory controls in 8.0.13. Percona Server for MongoDB, which is what most of our self-managed customers run, is built on the 7.0 and 8.0 lines only; the 8.0.29-13 build from 20 August 2026 carries the same CVE fixes as upstream 8.0.29. There is no Percona build of MongoDB 8.3.

MongoDB 8.3 scalability: what changed in the sharded cluster lifecycle

Config shards, and the three-shard rule

Since 8.0 a sharded cluster no longer needs a dedicated config server replica set. One shard can carry the cluster metadata alongside application data; MongoDB calls this a config shard or embedded config server. On a small cluster that removes three nodes of infrastructure, which is the difference between nine mongod processes and six for a two-shard deployment.

The documentation’s guidance is blunt, and I agree with it from operating both shapes: use a config shard at three shards or fewer, and move to a dedicated CSRS beyond that, or earlier if the workload is latency-sensitive enough that you do not want metadata reads and refreshes competing with user I/O on the same WiredTiger cache. Queryable Encryption collections and on-prem queryable backups also require a dedicated config server.

In Atlas the decision is made for you. Atlas-Managed Config Servers is on by default for every 8.0+ sharded cluster: at five shards or fewer Atlas runs an embedded config server, and when you add a sixth shard it transitions to a dedicated config server automatically, draining user data off config-0 with chunk migrations and moveCollection, waiting out orphanCleanupDelaySecs, and then adding a replacement shard to keep your shard count.

That transition is online but it is not free: receiving shards see elevated CPU, memory and I/O for the duration, you cannot change the cluster tier while it runs, and Atlas tells you not to cancel it. On a multi-terabyte cluster it can run for days. Clusters using Atlas Search, unsharded time series collections or Queryable Encryption are pinned to whichever config type they started with.

MongoDB 8.3: replica set to sharded cluster in one rolling restart

This is the change I am most pleased about for self-managed estates. Before MongoDB 8.3, converting a replica set into a sharded cluster with a config shard meant first turning it into a dedicated config server and then transitioning, which was awkward because a dedicated CSRS is not supposed to hold user data. MongoDB 8.3 adds --replicaSetConfigShardMaintenanceMode, which relaxes the startup checks so the members can be restarted as --configsvr while still carrying application data.

The sequence, on a three-member replica set rs0, secondaries first:

# Step 1: rolling restart, one member at a time, in maintenance mode
mongod --config /etc/mongod.conf --configsvr --replicaSetConfigShardMaintenanceMode
# wait for rs.status() to show the member back in SECONDARY before the next one

// Step 2: on the primary, flag the replica set as a config server
var conf = rs.conf();
conf.configsvr = true;
conf.version += 1;
rs.reconfig(conf);

// confirm every member has applied it before going further
db.aggregate([
  { $documents: rs.status().members },
  { $group: { _id: null, allConfigSvr: { $min: { $eq: ["$configsvr", true] } } } }
]);
// expected: { _id: null, allConfigSvr: true }

# Step 3: second rolling restart WITHOUT the maintenance flag
mongod --config /etc/mongod.conf --configsvr

# Step 4: start a router pointing at the set
mongos --config /etc/mongos.conf   # sharding.configDB: rs0/host1:27017,host2:27017,host3:27017

// Step 5: through mongos, make rs0 both config server and first shard
db.adminCommand({ transitionFromDedicatedConfigServer: 1 });
// { ok: 1 }

sh.isConfigShardEnabled();          // enabled: true
db.adminCommand({ listShards: 1 }); // shard _id "config" is rs0

Two things to write into the runbook before you run this. First, the application connection string changes from the replica set seed list to the mongos address, so plan the cutover with the application team rather than discovering it during the change window. Second, and this is new in MongoDB 8.3: a replica set that has been a sharded cluster cannot be converted back to a plain replica set. The shard identity document survives, and clearing it is a support-assisted procedure. Treat the conversion as one-way and rehearse it on a restored backup first.

Draining a shard without removeShard

removeShard has been the same opaque call since 2.x: issue it, poll it, and hope the balancer keeps moving. MongoDB 8.3 deprecates it and splits the operation into four commands, all run through mongos with the clusterManager role.

// 1. begin draining; the balancer does the actual chunk moves, so it must be enabled
db.adminCommand({ balancerStatus: 1 });          // mode: "full"
db.adminCommand({ startShardDraining: "shard04" });

// 2. poll; this is the part removeShard never gave you cleanly
db.adminCommand({ shardDrainingStatus: "shard04" });
/* abridged from the MongoDB 8.3 reference:
{
  shard: "shard04",
  remainingCriticalSectionChunks: 0,
  totalChunksToDrain: 15,
  chunksLeftToDrain: 8,
  databases: [ { name: "orders", isPrimary: true, collections: [ ... ] } ],
  ok: 1
} */

// 3. anything the balancer cannot move has to be moved by hand:
//    databases that have shard04 as primary shard ...
db.adminCommand({ movePrimary: "orders", to: "shard03" });
//    ... and unsharded collections that live on shard04
db.adminCommand({ moveCollection: "orders.fx_rates", toShard: "shard03" });

// 4. commit only when chunksLeftToDrain is 0 and databases is empty
db.adminCommand({ commitShardRemoval: "shard04" });

// change of plan? draining is reversible until you commit
db.adminCommand({ stopShardDraining: "shard04" });

The operational gain is not the syntax; it is that draining is now an inspectable, stoppable state. On a cluster where the balancer window is restricted to nights, you can start the drain on Monday, watch chunksLeftToDrain fall over the week, and commit on Friday without ever holding the old command open. Two constraints carry over from removeShard and are worth restating: you cannot take a cluster backup while a drain is in progress, and open change stream cursors may close and not resume across it. None of these commands exist on Atlas, where shard removal is a cluster-configuration change that Atlas executes for you.

Moving data without changing the shard key

The rest of the data-movement toolbox arrived in 8.0 and is unchanged in MongoDB 8.3, but it is what makes the draining workflow usable, so it belongs here. moveCollection relocates an unsharded collection to a named shard. unshardCollection collapses a sharded collection onto one shard. And reshardCollection with forceRedistribution: true rebalances a collection across the current shard set on its existing key, which is how you spread a collection onto a newly added shard without waiting for the balancer’s chunk-by-chunk pace.

// spread orders.events across all shards on the same key after adding shard05
db.adminCommand({
  reshardCollection: "orders.events",
  key: { tenant_id: "hashed" },
  forceRedistribution: true
});

// progress: remainingOperationTimeEstimatedSecs is -1 until the clone phase has run long enough to extrapolate
db.getSiblingDB("admin").aggregate([
  { $currentOp: { allUsers: true, localOps: false } },
  { $match: { type: "op", "originatingCommand.reshardCollection": "orders.events" } },
  { $project: { shard: 1, desc: 1, totalOperationTimeElapsedSecs: 1, remainingOperationTimeEstimatedSecs: 1 } }
]);

All three run on the resharding machinery, so all three inherit its preconditions, and these are the numbers I check before approving one on a production cluster: each participating shard needs free storage of at least twice the collection size plus indexes divided by the shard count, I/O utilisation under 50 percent, CPU under 80 percent, and writeConcernMajorityJournalDefault set to true. The operation has a floor of about five minutes even for a tiny collection, and it ends with a critical section where writes to the collection are blocked for roughly two seconds. Applications with aggressive client-side timeouts notice that. Index builds started during the operation can fail silently, so freeze DDL on the namespace for the duration.

The orphan cleanup change that kills your reporting queries

This one is easy to miss in the 8.2 notes and it is the change most likely to show up as a mystery ticket after an upgrade. After a chunk migration the donor shard has to delete the range it gave away. Two defaults changed in 8.2. orphanCleanupDelaySecs went from 900 to 3600 seconds, so orphans now sit on the donor for an hour before deletion. And terminateSecondaryReadsOnOrphanCleanup was introduced with a default of true: when the range deletion finally runs, any long-running read on a secondary that could still be reading the orphaned range is terminated first.

The intent is correctness, because a secondary read that spans a range deletion could return partial results. The effect is that hour-long analytics queries pinned to secondaries with readPreference: secondary die at unpredictable times after every migration. The counter to watch is serverStatus().metrics.operation.killedDueToRangeDeletion; if it climbs, either move those queries to a dedicated analytics node with a balancer window that avoids them, or raise orphanCleanupDelaySecs beyond the longest legitimate query. Note that a new value only applies to range deletions created after the change; existing ones keep the old delay until you step the primary down.

MongoDB 8.3 high availability: closing the post-election gap

Elections in MongoDB have been fast for years; a healthy replica set picks a new primary in well under ten seconds. The availability loss that actually hurts is what happens in the two or three minutes afterwards: the new primary has a cold cache, every application pool reconnects at once, and the retry logic in drivers and in mongos hammers whichever node answered first. The changes in MongoDB 8.2 and MongoDB 8.3 target exactly that window.

Targeted mirrored reads (8.2)

Mirrored reads have been on by default since 4.4: the primary forwards one percent of eligible reads (find, count, distinct, and the filter portion of update and findAndModify) to electable secondaries, fire-and-forget, so their caches are not stone cold when one of them wins an election. The limitation was that it sprayed evenly across every electable member and could not reach hidden nodes. 8.2 adds targetedMirroring, which mirrors to members matching a replica set tag, at its own sampling rate, and hidden members are allowed.

// tag the member you intend to fail over to (a DR-site member, or the one with priority 2)
var cfg = rs.conf();
cfg.members[2].tags = { warm: "standby" };
cfg.version += 1;
rs.reconfig(cfg);

// on the primary: keep the 1% general mirroring, and additionally mirror 25% of reads to the tagged member
db.adminCommand({
  setParameter: 1,
  mirrorReads: {
    samplingRate: 0.01,
    maxTimeMS: 1000,
    targetedMirroring: { tag: { warm: "standby" }, samplingRate: 0.25, maxTimeMS: 1000 }
  }
});

// confirm it is doing something
db.serverStatus({ mirroredReads: 1 }).mirroredReads;

A few cautions from running this. The setting lives on the primary only, so after a failover the new primary has whatever mirrorReads value it was started with; put it in mongod.conf under setParameter on every member, not just the current primary. Only one tag can be supplied, and every member carrying that tag is targeted. Mirrored reads consume connections from a pool capped by mirrorReadsMaxConnPoolSize (default 4, new in 8.2), so a high targeted sampling rate on a busy primary will start dropping mirrors rather than slowing the primary, which is the right failure mode but means the warm-up is best-effort.

And it warms the WiredTiger cache, not the filesystem cache or the plan cache in any guaranteed way; on a member with much less RAM than the primary it cannot do much.

Connection establishment rate limiting (8.2, in 8.0.12)

Of everything in this post, this is the change I would enable first on a production replica set, and the fact that it is backported to 8.0.12 means you do not need the minor track to get it. When a primary steps down, every application server’s pool detects the topology change and reconnects. A fleet of 400 application pods with 50-connection pools is 20,000 TLS handshakes and SCRAM exchanges landing on the new primary inside a few seconds, and on a mid-sized node that alone can push CPU to saturation and delay the very operations the pools are reconnecting to run.

Before 8.2 the only knobs were maxIncomingConnections, which is a hard cap, and driver-side jitter, which you do not control across every team.

# mongod.conf: admit 500 new connections/sec, absorb a 4-second burst above that,
# queue up to 2000 more, reject the rest with a retryable error
setParameter:
  ingressConnectionEstablishmentRateLimiterEnabled: true
  ingressConnectionEstablishmentRatePerSec: 500
  ingressConnectionEstablishmentBurstCapacitySecs: 4
  ingressConnectionEstablishmentMaxQueueDepth: 2000

// what to graph after enabling it
var s = db.serverStatus();
printjson({
  queued:   s.connections.queuedForEstablishment,
  rejected: s.connections.establishmentRateLimit.rejected,
  exempted: s.connections.establishmentRateLimit.exempted,
  avgQueuedMicros: s.queues.ingressSessionEstablishment.averageTimeQueuedMicros,
  tlsHandshakeMicros: s.metrics.network.averageTimeToCompletedTLSHandshakeMicros,
  authMicros:         s.metrics.network.averageTimeToCompletedAuthMicros
});

The numbers above are a starting shape, not a recommendation; size the rate from your own measured reconnect behaviour. The way to get that number is to look at averageTimeToCompletedTLSHandshakeMicros and averageTimeToCompletedAuthMicros during a planned stepdown in staging with the limiter off, then set the rate so the queue drains in a few seconds rather than letting the handshakes starve the oplog appliers. The default MaxQueueDepth of 0 rejects anything that would queue, which is almost never what you want once the limiter is on; pick a real depth. And test the driver behaviour: a rejected connection surfaces as a network error the driver retries with backoff, which is fine for modern drivers and not fine for anything hand-rolled.

Overload-aware server selection (8.3)

MongoDB 8.3 adds overloadAwareServerSelectionEnabled, off by default. It changes how a mongos or a mongod acting as an internal client picks a target when an operation fails with an error labelled SystemOverloadedError: instead of retrying against the same member it prefers one that has not recently reported overload. Alongside it, MongoDB 8.3 gives the internal retry path a token bucket (shardRetryTokenBucketCapacity, shardRetryTokenReturnRate) and explicit backoff controls (defaultClientRetryAttempts, default 3, plus base and max backoff in milliseconds).

Together these are the sharded-cluster equivalent of the connection limiter: they stop a mongos fleet from converting one slow shard secondary into a cluster-wide retry storm. I have not yet run this one under a production-shaped load test, so I am reporting what it does rather than how much it helps; it is on my list for the next MongoDB 8.3 lab pass.

Rebuilding a member faster

Initial sync is the other place where HA quietly degrades: while a replaced member is syncing, a three-member set is running with a majority of two out of two live members, and a second failure means read-only. 8.2 lets index builds during initial sync use a percentage of RAM (initialSyncIndexBuildMemoryPercentage, default 10, bounded by initialSyncIndexBuildMemoryMinMB 200 and MaxMB 16384) instead of the old fixed budget. On a 256 GB node that is 16 GB for index builds instead of a few hundred megabytes, and it is backported to 8.0.13 and 7.0.26. Raise it during a rebuild and drop it back afterwards; 40 percent on a member that is otherwise idle during sync is a reasonable ceiling.

// on the syncing member, before it reaches the index build phase
db.adminCommand({ setParameter: 1, initialSyncIndexBuildMemoryPercentage: 40 });

// catch-up visibility, new in MongoDB 8.3
db.serverStatus().metrics.repl.network.oplogFetcherLagSeconds;
db.serverStatus().metrics.repl.network.oplogGetMoresProcessed;

What “majority” means since 8.0

One 8.0 change still surprises teams moving from 7.0 and it matters for how you read HA metrics. w: "majority" now acknowledges when a majority of members have written the oplog entry, not when they have applied it. Writes get faster; a read from a secondary immediately after an acknowledged write, without causal consistency, can still miss it. rs.status() exposes the distinction per member as optimeWritten alongside optime, and if you alert on replication lag you should decide which of the two you mean. For application correctness, sessions with causal consistency or readConcern: "majority" behave exactly as before.

MongoDB 8.3 high availability and scalability on-premises versus Atlas

The same server code runs in both places, but the operational surface for MongoDB 8.3 high availability and scalability is different enough that the runbooks do not transfer. The table is how I frame it for customers deciding where a new sharded workload should live.

The honest summary is that Atlas removes the sharding lifecycle work, which is where most self-managed sharded clusters go wrong, and in exchange takes away the server-parameter surface that lets you tune the post-election window. If your failure mode is “we mis-sized a shard migration”, Atlas is the safer place. If your failure mode is “a reconnect storm took the primary to 100 percent CPU”, self-managed 8.0.12+ with the connection limiter is the more controllable one. For most of the estates we run, a self-managed 8.0.x line on Percona builds with the backported controls enabled is where the risk is lowest today.

Where I would deploy MongoDB 8.3, and where I would not

As of September 2026: on Atlas, take MongoDB 8.3 on non-production and on production clusters that do not depend on Live Migration, and keep production sharded clusters pinned to the 8.0 major until you have read the no-revert rule twice. On-premises, stay on the 8.0 line for anything that has to be supportable in 2028, and reach for MongoDB 8.3 only when a feature forces it: the direct replica set to config shard conversion, the shard draining workflow, or the search, vector search and Queryable Encryption capabilities that are the stated reason the minor track exists on-prem at all.

If you do go to MongoDB 8.3 self-managed, budget for a binary upgrade every quarter, because the patch window closes the day the next minor ships, and check the Linux kernel: 8.2 carried a TCMalloc incompatibility with kernels 6.19 through 7.0.13 that crashed the process, and the fix was to move the kernel, not the database.

Whichever version you land on, MongoDB 8.3 high availability and scalability still come down to operations. The four things that decide availability in most MongoDB incidents I get called into are not version features. They are an oplog window shorter than the longest maintenance operation, a balancer running during peak hours, secondaries with less RAM than the primary they were expected to replace, and nobody having rehearsed the failover. MongoDB 8.3 gives you better tools for the aftermath of an election; it does not replace the drill.

Everything above should be tested against your own workload in a staging environment that mirrors production topology before any of it is applied to production, with a verified backup and a rehearsed restore in place. If you want a second pair of eyes on a sharded cluster design, an 8.0 to MongoDB 8.3 upgrade plan, or a failover drill, that is the kind of work the MinervaDB MongoDB consulting team does every week, on-premises and in Atlas.

References

Release Notes for MongoDB 8.3 · Release Notes for MongoDB 8.2 · MongoDB Versioning · Config Shard · Convert Replica Set to an Embedded Config Shard · startShardDraining · moveCollection · Reshard a Collection · Server Parameters · Atlas: Transition to Dedicated Config Servers · Atlas Architecture Center: Scalability · MongoDB Software Lifecycle Schedules · Percona Server for MongoDB 8.0.29-13

MongoDB 8.3 High Availability and Scalability: What Changed On-Premises and in Atlas appeared first on MariaDB.org

Continue reading “Rethinking PAM”

Rethinking PAM appeared first on MariaDB.org

Rethinking PAM appeared first on MariaDB.org

Continue reading “From a Chocolate Wrapper to Concurrent InnoDB Page Splits”

From a Chocolate Wrapper to Concurrent InnoDB Page Splits appeared first on MariaDB.org

From a Chocolate Wrapper to Concurrent InnoDB Page Splits appeared first on MariaDB.org

We will show, on a real PostgreSQL 16 and a real ClickHouse 26 build, why the same table and the same question cost 1.79 GB of I/O in one engine and 22 MiB in the other, why sixty-four writers queue behind a single row no matter how many cores you buy, why eventually consistent platforms exist at all, and why a vector index is a different kind of object from anything in the relational world. Then we lay out the polyglot persistence decision frame a CTO or an investor should apply before believing any “one database for everything” story, including ours.

What polyglot persistence actually claims

Martin Fowler gave the pattern its name in 2011: polyglot persistence means using different data storage technologies for different data, chosen by how the application reads and writes that data. The idea of polyglot persistence predates the term. Every large internet company arrived at polyglot persistence independently, usually after an outage, and usually after trying very hard not to.

The version of polyglot persistence that matters to a board is narrower. A business that serves consumers on the internet runs four or five workload classes with incompatible physics: transactions that must be exactly right, interactive traffic that must be fast everywhere, analytics that must scan everything, retrieval that must be semantically close, and the elastic cloud substrate underneath all of it. No single engine is optimal for more than one or two of those. Each additional engine is a real cost, so the decision is about which contradictions you can afford to paper over and which you cannot.

Figure 1. The polyglot persistence landscape: five workload classes, five storage contracts.

The OLTP engine is keeping a promise, not hitting a wall

A transaction-processing database sells one thing: ACID. Atomicity, consistency, isolation and durability are not marketing features; they are mechanisms with costs that show up in specific catalog views. Durability is a synchronous write to the write-ahead log before a commit is acknowledged. Isolation is a row-level lock, or in MVCC engines a tuple version chain plus a lock on the row being updated. Atomicity is the ability to roll every one of those back. In PostgreSQL’s MVCC implementation, two sessions that update the same row serialise on that row by design, because that is the only way to keep the second update from clobbering the first.

The cleanest way to see the cost is to measure it. The runs below are on a two-vCPU sandbox with PostgreSQL 16.13, synchronous_commit = off to remove disk latency from the picture, and pgbench driving a single-statement transaction for ten seconds. This is a demonstration of a mechanism, not a benchmark; the absolute numbers are meaningless outside this box, and the shape of the curve is the point.

CREATE TABLE inventory (
    sku_id   INT PRIMARY KEY,
    on_hand  INT NOT NULL
);
INSERT INTO inventory
SELECT g, 1000000000
FROM generate_series(1, 1000) g;

-- hot.sql: every client decrements the same SKU
UPDATE inventory SET on_hand = on_hand - 1 WHERE sku_id = 1;

-- spread.sql: clients decrement a random SKU
set sku random(1, 1000)
UPDATE inventory SET on_hand = on_hand - 1 WHERE sku_id = :sku;

$ for c in 1 8 32 64; do pgbench -n -f hot.sql -c $c -j 2 -T 10; done
clients=1    latency average = 0.065 ms   tps = 15298
clients=8    latency average = 0.260 ms   tps = 30790
clients=32   latency average = 2.020 ms   tps = 15838
clients=64   latency average = 5.871 ms   tps = 10900

$ for c in 1 8 32 64; do pgbench -n -f spread.sql -c $c -j 2 -T 10; done
clients=1    latency average = 0.066 ms   tps = 15238
clients=8    latency average = 0.190 ms   tps = 42089
clients=32   latency average = 0.932 ms   tps = 34339
clients=64   latency average = 2.080 ms   tps = 30774

Both workloads saturate two cores by eight clients, so the interesting comparison is what happens after that. The spread workload holds three quarters of its peak at sixty-four clients. The hot-row workload loses two thirds of its peak and its latency grows ninety-fold. A snapshot of pg_stat_activity during the sixty-four-client hot-row run shows where the time went.

SELECT wait_event_type,
       wait_event,
       COUNT(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND query LIKE 'UPDATE inventory%'
GROUP BY 1, 2
ORDER BY 3 DESC;

 wait_event_type |  wait_event   | count
-----------------+---------------+-------
 Lock            | tuple         |    38
 LWLock          | BufferContent |     7
 LWLock          | LockManager   |     6
 Lock            | transactionid |     6
                 |               |     5
 Client          | ClientRead    |     2

Figure 2. Wait events under hot-row contention, the first measurement behind polyglot persistence.

Fifty of sixty-four sessions are queued on the tuple lock or the transaction ID of the session holding it. Nothing here is a bug, a missing index or a tuning gap. The engine is serialising updates to one row because you asked it to guarantee that no decrement is lost. Sharding does not change the arithmetic for a single hot key; it only spreads the keys that are not hot. This is the first fundamental limit that forces polyglot persistence: an ACID row store’s throughput on a contended key is bounded by the serial critical section, and that is exactly what a consumer flash sale, a viral post’s like counter or a global leaderboard produces.

What the consumer internet changed

Enterprise applications of the previous era had a bounded number of users, a business-hours load curve and a tolerance for a few hundred milliseconds. Consumer applications have none of those properties. The traffic is spiky and global, the read-to-write ratio is often a thousand to one, and the product team measures latency at the 99th percentile in a region the database was never deployed in.

Amazon documented the consequences in the Dynamo paper. When a network partition happens, and it will, a system can keep accepting writes or it can keep every replica in agreement, but not both. A shopping cart that refuses to accept an item because a replica is unreachable costs more than a cart that briefly shows a stale item. Cassandra, DynamoDB, Riak and the key-value tier of most large platforms descend from that decision, and polyglot persistence at internet scale starts with it. The write contract becomes “this will converge”, enforced by quorum arithmetic and a conflict rule, rather than “this is true now”, enforced by a lock.

The trade is explicit and tunable. In Cassandra, a read at LOCAL_QUORUM against a replication factor of three touches two of three replicas in the local datacenter and returns the newest timestamped value it sees; the consistency documentation spells out the read-plus-write-greater-than-replication-factor rule that makes that read see the latest acknowledged write. What you give up is any notion of a multi-row transaction, a join or an ad hoc query, which is why the data model is designed backwards from the queries.

CREATE KEYSPACE consumer
WITH replication = {
    'class'      : 'NetworkTopologyStrategy',
    'us-east-1'  : 3,
    'ap-south-1' : 3
};

-- One partition per user, newest activity first, bounded by TTL.
CREATE TABLE consumer.activity_feed_by_user (
    user_id      UUID,
    event_ts     TIMEUUID,
    event_type   TEXT,
    payload      TEXT,
    PRIMARY KEY ((user_id), event_ts)
)
WITH CLUSTERING ORDER BY (event_ts DESC)
 AND default_time_to_live = 2592000
 AND compaction = {
    'class'                : 'TimeWindowCompactionStrategy',
    'compaction_window_unit': 'DAYS',
    'compaction_window_size': 1
 };

-- The application reads at LOCAL_QUORUM and writes at LOCAL_QUORUM:
-- 2 + 2 > 3, so a read observes the latest acknowledged write in-region.
CONSISTENCY LOCAL_QUORUM;
SELECT event_ts, event_type, payload
FROM consumer.activity_feed_by_user
WHERE user_id = 7a3f1c2e-4d5b-4e6f-8a9b-0c1d2e3f4a5b
LIMIT 50;

Notice what is missing. There is no foreign key to a users table, no join to the events catalog, no way to ask “which users had the most events this week” without a full cluster scan or a second table maintained by the application. That is the mirror image of the OLTP limit. An eventually consistent platform buys availability and horizontal write scale by refusing every feature that would require global coordination. Polyglot persistence is what happens when you stop pretending one of these contracts can substitute for the other.

Analytics is columnar because the questions are columnar

An analytical question is a function of a few columns over a very large number of rows. A row store answers it by reading every byte of every row, because the tuple is the unit of storage. A column store keeps each column in its own file, so a scan opens only the columns the query names, and compresses each column with a codec suited to that column’s distribution. This is the second fundamental limit behind polyglot persistence, and it is easy to measure.

The lab table is a 5-million-row, 24-column orders table with realistic width: two free-text addresses, a UUID session ID, a referrer URL, optional notes. It was generated in PostgreSQL 16.13, exported to CSV, and loaded unchanged into ClickHouse 26.7 through chdb, so both engines hold identical data. The question is the one every product dashboard asks first: revenue and order count by region and channel for the last ninety days.

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT region,
       channel,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
WHERE order_ts >= now() - interval '90 days'
GROUP BY region, channel
ORDER BY revenue DESC;

 Sort (actual rows=20 loops=1)
   Sort Key: (sum(amount)) DESC
   Buffers: shared hit=123593 read=105740
   I/O Timings: shared read=219.788
   ->  Finalize GroupAggregate (actual rows=20 loops=1)
         ->  Gather Merge (actual rows=60 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ...
               ->  Partial HashAggregate (actual rows=20 loops=3)
                     ->  Parallel Seq Scan on orders (actual rows=408674 loops=3)
                           Filter: (order_ts >= (now() - '90 days'::interval))
                           Rows Removed by Filter: 1257993
                           Buffers: shared hit=123574 read=105740
 Execution Time: 674.381 ms

-- pg_class.relpages for orders: 229314  (229314 x 8 KB = 1.79 GB heap)

The planner did nothing wrong. There is an index on order_ts, but a quarter of the table matches, so a parallel sequential scan is the right plan, and it is the right plan in every row store. The cost is structural: to sum one NUMERIC column across 1.2 million qualifying rows, the executor pulled all 229,314 heap pages through shared buffers, 105,740 of them from the operating system, because ship_address, session_id and notes live in the same 8 KB pages as amount.

CREATE TABLE lab.orders
(
    order_id        UInt64,
    customer_id     UInt64,
    order_ts        DateTime64(6, 'UTC'),
    region          LowCardinality(String),
    channel         LowCardinality(String),
    status          LowCardinality(String),
    currency        FixedString(3),
    amount          Decimal(12, 2),
    -- ... 16 further columns identical to the PostgreSQL table
    carrier         LowCardinality(String),
    updated_at      DateTime64(6, 'UTC')
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(order_ts)
ORDER BY (region, order_ts, order_id)
SETTINGS index_granularity = 8192;

EXPLAIN indexes = 1
SELECT region,
       channel,
       count()     AS orders,
       sum(amount) AS revenue
FROM lab.orders
WHERE order_ts >= now() - INTERVAL 90 DAY
GROUP BY region, channel
ORDER BY revenue DESC;

Aggregating
   Keys: region, channel
   Aggregates: count(), sum(amount)
   ReadFromMergeTree (lab.orders)
      Parts: 4 | Granules: 166
      Output: region, channel, amount
      Prewhere filter column: order_ts >= '2026-06-07 04:05:17'
      Indexes:
         Partition   Parts: 4/4    Granules: 173/173
         Min-Max     Parts: 4/13   Granules: 173/652
         PrimaryKey  Parts: 4/4    Granules: 166/173

-- 20 rows returned in 0.032 s on the same 2-vCPU sandbox

SELECT column,
       formatReadableSize(sum(column_data_compressed_bytes))   AS compressed,
       formatReadableSize(sum(column_data_uncompressed_bytes)) AS uncompressed
FROM system.parts_columns
WHERE database = 'lab' AND table = 'orders' AND active
  AND column IN ('region','channel','amount','order_ts','ship_address','session_id')
GROUP BY column
ORDER BY sum(column_data_compressed_bytes) DESC;

   ┌─column───────┬─compressed─┬─uncompressed─┐
1. │ ship_address │ 185.17 MiB │ 290.35 MiB   │
2. │ session_id   │ 76.61 MiB  │ 76.29 MiB    │
3. │ amount       │ 19.25 MiB  │ 38.15 MiB    │
4. │ channel      │ 2.73 MiB   │ 4.78 MiB     │
5. │ order_ts     │ 190.29 KiB │ 38.15 MiB    │
6. │ region       │ 26.85 KiB  │ 4.78 MiB     │
   └──────────────┴────────────┴──────────────┘

Figure 3. Row store versus column store for one aggregate, the second measurement behind polyglot persistence.

The four columns the query needs compress to about 22 MiB for the entire table, and the partition, min-max and primary-key indexes cut that to 166 of 652 granules before a byte of amount is decoded. The twenty columns the query does not need, including the 185 MiB of addresses, are never opened. Thirty milliseconds versus seven hundred is not ClickHouse being clever; it is the MergeTree storage layout being shaped like the question. The same layout is why ClickHouse is a poor system of record: a single-row UPDATE is a mutation that rewrites parts, and there is no row lock to serialise two of them.

Cloud-native data platforms changed the unit of scale

The third shift in polyglot persistence is not a data model but an operating model. Aurora, AlloyDB, Spanner, Snowflake, BigQuery and ClickHouse Cloud separate compute from storage, put the storage on a replicated log or an object store, and let capacity change in minutes. For a founder the argument is time to market: a team of four can stand up a multi-AZ PostgreSQL-compatible cluster with automated failover before lunch. For a CFO the argument is that capacity becomes an operating expense that tracks demand.

The trade-offs are just as concrete, and a vendor-neutral practice has to name them. Managed services diverge from the open-source engine they are compatible with: extension allow-lists, superuser removal, version lag behind community releases, and storage layers whose performance characteristics (Aurora’s quorum writes, Spanner’s TrueTime commit wait) differ from the engine’s documentation.

Egress and cross-region replication are priced per byte, which matters precisely when the polyglot persistence topology is moving change streams between stores. And the exit cost is asymmetric: getting a terabyte in is a weekend, getting it out with zero downtime is a project. None of this argues against cloud platforms. It argues for choosing them per workload, which is polyglot persistence applied to the operating model, with the same rigour as the engine itself.

Vector platforms and RAG are a different kind of object

A relational index answers “is this key present” exactly. A B-tree, a hash, a bitmap: the answer is deterministic and complete. A vector index answers “which stored vectors are nearest to this one” approximately, because exact nearest-neighbour search in a thousand dimensions is a full scan. HNSW and IVF indexes trade recall for latency with explicit knobs, and a retrieval-augmented generation pipeline lives or dies on that trade plus the metadata filters applied before or after the approximate search.

That makes vector search a distinct workload class in any polyglot persistence design rather than a feature bolted onto an existing engine, even when it ships inside one. pgvector 0.8 on PostgreSQL 16+ is an excellent choice while the corpus fits in memory, the filter predicates are selective and the embedding refresh rate is modest, because it keeps the vectors transactionally next to the rows they describe. A dedicated platform such as Milvus 2.6 earns its place when index build and query serving need to scale independently, when collections reach hundreds of millions of vectors, or when the workload needs GPU indexes, tiered storage and multi-tenant isolation that a general-purpose engine will not prioritise.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE support_chunks (
    chunk_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   INT          NOT NULL,
    ticket_id   BIGINT       NOT NULL,
    chunk_text  TEXT         NOT NULL,
    embedding   VECTOR(1024) NOT NULL,
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

-- Recall/latency trade-off is explicit: m and ef_construction at build time,
-- hnsw.ef_search at query time. Test on your corpus before fixing these.
CREATE INDEX idx_support_chunks_embedding_hnsw
    ON support_chunks
 USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 128);

SET hnsw.ef_search = 80;
SELECT chunk_id,
       ticket_id,
       1 - (embedding  $1::vector) AS cosine_similarity
FROM support_chunks
WHERE tenant_id = $2
ORDER BY embedding  $1::vector
LIMIT 8;

import psycopg
from openai import OpenAI   # any embedding provider; keep it in-VPC for regulated data

EMBED_MODEL = "text-embedding-3-large"
client = OpenAI()

def embed(text: str) -> list[float]:
    return client.embeddings.create(model=EMBED_MODEL, input=text).data[0].embedding

def retrieve(conn: psycopg.Connection, tenant_id: int, question: str, k: int = 8):
    qvec = embed(question)
    with conn.cursor() as cur:
        cur.execute("SET LOCAL hnsw.ef_search = 80")
        cur.execute(
            """
            SELECT chunk_id, ticket_id, chunk_text,
                   1 - (embedding  %s::vector) AS score
            FROM support_chunks
            WHERE tenant_id = %s
            ORDER BY embedding  %s::vector
            LIMIT %s
            """,
            (qvec, tenant_id, qvec, k),
        )
        return cur.fetchall()

# The ticket text itself is owned by the OLTP schema; this table is rebuilt
# from it when the embedding model changes. Treat it like a materialised view.

Two operational facts follow for any polyglot persistence estate. First, an embedding model change invalidates every vector, so the vector store must be rebuildable from the system of record; it is a derived index, not a database of record. Second, recall is a measured quantity. A RAG pipeline that has never had its retrieval recall measured against a labelled set is not in production, whatever the dashboard says.

The polyglot persistence decision frame

Executives do not need to memorise storage internals. They need a frame that turns “which database” into a question about the workload, so that the answer can be checked against measurement. These are the dimensions that decide it in practice.

Read the rows, not the columns. If a workload needs two cells that live in different columns, that is a second engine, and the honest polyglot persistence question is how the data gets from the first to the second and how stale it is allowed to be on arrival.

What a polyglot persistence topology looks like when it works

Figure 4. Reference polyglot persistence topology with one system of record and derived stores.

The polyglot persistence pattern that survives contact with production has one rule: exactly one store owns each fact, and every other store holds a derived, purpose-shaped copy that can be rebuilt from the owner. The system of record stays an ACID engine, usually PostgreSQL or MySQL with proper HA, because ledgers, orders and inventory are the facts a regulator will ask about. Change data capture through logical decoding and Kafka fans those facts out. The analytics store receives them into a ReplacingMergeTree keyed by the primary key so that CDC updates collapse on merge. The vector store receives the subset that needs embedding. The lakehouse receives everything for retention and model training.

CREATE TABLE analytics.orders_cdc
(
    order_id     UInt64,
    customer_id  UInt64,
    order_ts     DateTime64(6, 'UTC'),
    region       LowCardinality(String),
    channel      LowCardinality(String),
    status       LowCardinality(String),
    amount       Decimal(12, 2),
    _version     UInt64,          -- Debezium source.lsn or ts_ms
    _deleted     UInt8            -- 1 when op = 'd'
)
ENGINE = ReplicatedReplacingMergeTree(
    '/clickhouse/tables/{shard}/analytics/orders_cdc',
    '{replica}',
    _version,
    _deleted
)
PARTITION BY toYYYYMM(order_ts)
ORDER BY (region, order_ts, order_id)
SETTINGS index_granularity = 8192;

-- Dashboards read with FINAL or through a materialised view that
-- pre-aggregates; the raw table is never the source of truth.
SELECT region,
       channel,
       sum(amount) AS revenue
FROM analytics.orders_cdc FINAL
WHERE order_ts >= now() - INTERVAL 90 DAY
  AND _deleted = 0
GROUP BY region, channel
ORDER BY revenue DESC;

The cost of this architecture is real and should be stated to a board plainly. Every arrow is a replication lag to monitor, a schema contract to version, a backfill procedure to rehearse and an on-call surface to staff. Polyglot persistence multiplies the number of things that can be wrong at three in the morning, which is why the number of stores must be the smallest that the workload contradictions force, and never one more.

Questions a CIO, a founder or an investor should ask about polyglot persistence

The technical argument for polyglot persistence above reduces to a handful of questions that a non-specialist can put to any engineering team or any vendor, and that a diligence process should insist on getting answered with evidence.

Which store owns each fact, and can every other store be rebuilt from it without downtime? What is the measured replication lag between the system of record and the analytics store at peak, and who is paged when it exceeds the agreed staleness? What is the hot-key throughput ceiling of the transactional engine on its current hardware, measured the way we measured it above, and how far is peak traffic from that ceiling? What does the cloud bill look like per workload class, including egress between stores, and what is the exit plan for each managed service? What is the recall of the retrieval pipeline against a labelled set, and when was it last measured?

A team that can answer those with catalog views, system.* tables and dated measurements is running polyglot persistence deliberately. A team that answers with a vendor’s architecture slide is running it by accident, and the accident is usually discovered during a growth spike, an audit or an acquisition.

Why a polyglot persistence partner has to be vendor-neutral

Every engine in a polyglot persistence estate is sold by a company whose revenue depends on you choosing it for as much of your estate as possible. The transactional vendor will add columnar indexes and vector types and tell you the second and third engines are unnecessary. The columnar vendor will add row-level updates and tell you it can be the system of record. The cloud vendor will bundle all of them and tell you the topology question is solved. Each claim is partly true, and each is a conflict of interest, because the vendor is paid for the engine and not for the outcome.

MinervaDB is paid for the outcome. We are data platform practitioners rather than a product company: our engineers have run PostgreSQL, MySQL, SQL Server, MongoDB, Cassandra, Redis and Valkey, ClickHouse, Milvus and the managed cloud editions of all of them in production, across more than 900 enterprises, and we have no licence to sell.

That independence is what lets us tell a client that pgvector is enough for their corpus, that their Cassandra cluster should be a PostgreSQL partition, or that the columnar migration they were sold will not fix a hot-row problem. The measurements in this post are the kind of evidence we bring to every architecture review, and the polyglot persistence decision frame above is the one we apply.

If you are designing, funding or acquiring a business that runs on a consumer-facing data platform, the polyglot persistence conversation is the one to have before the growth spike rather than after it. Talk to MinervaDB about an independent architecture review of your data platform, from the system of record to the retrieval layer. As always: test every change on your own workload before it reaches production, and keep a rehearsed disaster-recovery posture for every store in the topology, derived copies included.

Frequently asked questions about polyglot persistence

Is polyglot persistence just over-engineering for a startup? Usually, at first. A single well-run PostgreSQL with a cache in front of it carries most products to meaningful scale. Polyglot persistence becomes necessary when a specific measurement, such as the hot-row ceiling or the analytics scan cost shown above, contradicts a specific product requirement. Add the second store when the measurement says so, not when a slide does.

Can a multi-model database replace polyglot persistence? A multi-model engine reduces the operational surface of polyglot persistence, which is valuable, but it does not change the physics. A row store with a columnar index still reads tuples for transactional work and still serialises on a hot key; a columnar engine with row updates still performs mutations. Evaluate multi-model features by measuring the specific workload against a dedicated engine and pricing the difference.

Does the cloud make the polyglot persistence decision for me? No. Cloud platforms make each engine easier to provision and scale, and they make moving data between engines more expensive. The workload contradictions are the same on-premises and in the cloud; what changes is the operating model and the cost structure of the arrows between stores.

Where does the vector database sit in a polyglot persistence design? As a derived, rebuildable index over facts owned by the system of record. Embedding model changes and recall regressions are routine, so the pipeline that rebuilds the vector store must be as tested as the backup that restores the ledger.

Lab notes for the polyglot persistence measurements: PostgreSQL 16.13 (Ubuntu build), shared_buffers 1 GB, synchronous_commit off, two vCPUs, 7 GB RAM; ClickHouse 26.7.2 embedded via chdb on the same host; 5,000,000 rows generated with generate_series and exported unchanged between engines; pgbench 10-second runs; all outputs are verbatim with trims marked. Figures are mechanism demonstrations from a sandbox and are not vendor benchmarks. Reproduce on your own hardware before drawing capacity conclusions.

Polyglot Persistence: The Proven Case for 5 Data Models appeared first on MariaDB.org

Continue reading “Launching MariaDB’s Database Survey 2026”

Launching MariaDB’s Database Survey 2026 appeared first on MariaDB.org

Launching MariaDB’s Database Survey 2026 appeared first on MariaDB.org

Continue reading “MariaDB Plugins Beyond C++: What the Community Told Us”

MariaDB Plugins Beyond C++: What the Community Told Us appeared first on MariaDB.org

MariaDB Plugins Beyond C++: What the Community Told Us appeared first on MariaDB.org

This post shows how to write that BYOC database security standard so it can be scored, how to collect evidence from the database and the cloud account rather than from a questionnaire, and how to turn the evidence into a percentage and a verdict that behave sensibly. Everything below is runnable. The evaluator output shown later is a real run against a hand-built evidence file, not a mock-up.

What BYOC actually splits, and why BYOC database security has to be scored differently

Bring Your Own Cloud (BYOC) is a specific deployment model, and the security argument hinges on its shape. The vendor’s control plane, meaning the orchestrator, the tenant console, billing and support tooling, runs in the vendor’s account. The data plane, meaning the database nodes, the block storage, the object storage holding data and backups, the network boundary and the KMS keys, runs in your account. ClickHouse describes its BYOC data plane as running entirely in your cloud account with the control plane in ClickHouse’s VPC; Redpanda’s BYOC architecture follows the same split, and so do most of the other vendors offering the model.

The bridge between the two halves is a cross-account IAM role the control-plane agent assumes, plus whatever break-glass path the vendor’s operators use when something needs hands.

That split is what makes BYOC database security scoreable at all. In a fully managed service you can only attest: read the SOC 2 report, accept the shared-responsibility matrix, move on. In BYOC, every object in the data plane is queryable through your own cloud APIs and your own database catalog. You can measure it, and if you can measure it you can score it. The control plane stays opaque, and the standard has to be honest about that boundary rather than pretending a SOC 2 PDF is the same kind of evidence as a security-group rule you read from the API.

Writing a BYOC database security standard that produces a number

Most BYOC database security policies fail the measurability test for a mundane reason: their controls are sentences, not predicates. “Access to production databases must be appropriately restricted” cannot be scored. A control becomes scoreable when it has four properties. It is atomic, testing exactly one thing. It binds to exactly one evidence key, a value some collector can produce without a human interpreting it. It carries an expectation expressed as an operator and a value. And it carries a weight, because a wildcard IAM policy on the control-plane agent and a SOC 2 report that is thirteen months old are not the same size of problem.

The BYOC database security standard below has seven domains and eighteen controls. It is deliberately short. A standard with two hundred controls, half of which nobody can collect evidence for, produces a number nobody trusts. Eighteen controls with real collectors produce a number you can put in a board pack. Add controls only when a collector exists for them.

# MDB-SEC-2026-09-BYOC  --  BYOC database security standard, v1.0
# Every control is atomic, has exactly one evidence key, and carries a weight.
# gate: true means a FAIL caps the whole target at "non-conformant" regardless of score.
standard: MDB-SEC-2026-09-BYOC
version: "1.0"
weights: {critical: 5, high: 3, medium: 1}

domains:
  - id: IAM
    name: Identity and access
    controls:
      - id: IAM-01
        title: No shared superuser / default admin account enabled for application use
        severity: critical
        gate: true
        evidence: db.superuser_logins_last_30d
        expect: {op: eq, value: 0}
      - id: IAM-02
        title: Database authentication is federated (IAM / OIDC / cert), not password-only
        severity: high
        evidence: db.password_only_roles
        expect: {op: eq, value: 0}
      - id: IAM-03
        title: Vendor cross-account role uses an ExternalId and is scoped to the data-plane account only
        severity: critical
        gate: true
        evidence: cloud.vendor_role_external_id_enforced
        expect: {op: eq, value: true}

  - id: NET
    name: Network boundary
    controls:
      - id: NET-01
        title: Database endpoints have no 0.0.0.0/0 ingress on any port
        severity: critical
        gate: true
        evidence: cloud.public_ingress_rules
        expect: {op: eq, value: 0}
      - id: NET-02
        title: Client access only through PrivateLink / Private Service Connect / peering
        severity: high
        evidence: cloud.private_connectivity_only
        expect: {op: eq, value: true}
      - id: NET-03
        title: Egress from the data plane is allow-listed (vendor control-plane CIDRs + object storage only)
        severity: high
        evidence: cloud.egress_allowlisted
        expect: {op: eq, value: true}

  - id: ENC
    name: Encryption
    controls:
      - id: ENC-01
        title: Storage volumes and object-storage buckets encrypted with a customer-managed key
        severity: critical
        gate: true
        evidence: cloud.cmk_encryption
        expect: {op: eq, value: true}
      - id: ENC-02
        title: TLS 1.2+ enforced on every client-facing listener
        severity: high
        evidence: db.tls_min_version
        expect: {op: gte, value: 1.2}
      - id: ENC-03
        title: Backups encrypted with the same or a dedicated CMK, never vendor-default keys
        severity: high
        evidence: cloud.backup_cmk
        expect: {op: eq, value: true}

  - id: CPL
    name: Control-plane access
    controls:
      - id: CPL-01
        title: Vendor operator access to the data plane is break-glass, ticketed, and time-boxed
        severity: high
        evidence: vendor.breakglass_ttl_hours
        expect: {op: lte, value: 8}
      - id: CPL-02
        title: Control-plane agent runs with least-privilege IAM (no iam:*, no kms:ScheduleKeyDeletion)
        severity: critical
        gate: true
        evidence: cloud.agent_policy_wildcards
        expect: {op: eq, value: 0}
      - id: CPL-03
        title: Vendor SOC 2 Type II report reviewed within the last 12 months
        severity: medium
        evidence: vendor.soc2_age_days
        expect: {op: lte, value: 365}

  - id: LOG
    name: Audit and logging
    controls:
      - id: LOG-01
        title: Database audit log (DDL, grants, auth failures) shipped to a SIEM the vendor cannot write to
        severity: high
        evidence: db.audit_log_shipped
        expect: {op: eq, value: true}
      - id: LOG-02
        title: Cloud API audit trail (CloudTrail / Audit Logs) enabled for the data-plane account
        severity: high
        evidence: cloud.api_audit_enabled
        expect: {op: eq, value: true}

  - id: BKP
    name: Backup and recovery
    controls:
      - id: BKP-01
        title: Backups stored in a bucket the vendor control plane cannot delete
        severity: critical
        gate: true
        evidence: cloud.backup_bucket_vendor_delete_denied
        expect: {op: eq, value: true}
      - id: BKP-02
        title: Restore drill executed and evidenced within the last 90 days
        severity: high
        evidence: ops.last_restore_drill_days
        expect: {op: lte, value: 90}

  - id: CFG
    name: Configuration hardening
    controls:
      - id: CFG-01
        title: No database-level access from the public internet (listen / pg_hba / ClickHouse listen_host)
        severity: high
        evidence: db.listens_public
        expect: {op: eq, value: false}
      - id: CFG-02
        title: Version within vendor support window and no unpatched CVE older than 30 days
        severity: medium
        evidence: db.oldest_unpatched_cve_days
        expect: {op: lte, value: 30}

Three design choices in that file matter more than the specific controls, and they are what make BYOC database security measurable rather than merely documented. First, the gate: true flag. Six controls are gates; a failure on any of them makes the target non-conformant regardless of the percentage. Without gates, weighted scoring produces the pathology where a deployment with an internet-exposed listener still reads as “81%, mostly fine”.

Second, every control’s evidence key is a dotted path into a JSON document, so the standard never references a database engine, a cloud provider or a vendor by name. The same file scores a ClickHouse BYOC cluster on AWS and a PostgreSQL BYOC deployment on GCP; only the collectors differ. Third, the file has an ID and a version in the MinervaDB document convention, MDB-SEC-2026-09-BYOC v1.0, because a score is meaningless unless the reader knows which standard it was scored against. Changing a weight changes every historical number, so weights change through a versioned pull request, never in place.

Collecting BYOC database security evidence from the database, not from a questionnaire

The evidence for the db.* keys in a BYOC database security assessment comes from the database’s own catalog. The point of writing BYOC database security collectors as SQL is that the SQL is the audit trail: anyone can rerun the query and get the same answer. Two collectors follow, one for ClickHouse and one for PostgreSQL, each producing the raw values the standard expects. Version pinning matters here; several of these system tables changed shape recently.

ClickHouse BYOC database security collector (24.8 LTS and later)

-- BYOC database security: ClickHouse collector for db.* keys.  Tested syntax on ClickHouse 24.8 LTS and later.
-- Run as a user holding SHOW USERS, SHOW GRANTS and access to system.query_log.

-- IAM-01  logins as the built-in `default` user in the last 30 days
--         (query_log is on by default; session_log is not, so we count from query_log)
SELECT count() AS superuser_logins_last_30d
FROM system.query_log
WHERE user = 'default'
  AND type = 'QueryStart'
  AND event_date >= today() - INTERVAL 30 DAY;

-- IAM-02  roles that can still log in with a password only
--         auth_type became an Array when multiple auth methods per user
--         landed (24.9+); on older builds drop the hasAny() and compare directly
SELECT count() AS password_only_roles
FROM system.users
WHERE hasAny(auth_type,
             ['plaintext_password', 'sha256_password',
              'double_sha1_password', 'bcrypt_password'])
  AND NOT hasAny(auth_type, ['ldap', 'kerberos', 'ssl_certificate', 'jwt', 'http']);

-- CFG-01  is the server listening on anything but private / loopback interfaces?
--         (system.server_settings, 23.x+)
SELECT name, value, changed
FROM system.server_settings
WHERE name IN ('listen_host', 'tcp_port', 'http_port',
               'tcp_port_secure', 'https_port');

-- ENC-02  plaintext listeners left open count as a FAIL for TLS enforcement
SELECT countIf(name IN ('tcp_port', 'http_port') AND value != '') AS plaintext_listeners
FROM system.server_settings;

-- CPL-01 / audit trail  grants issued outside the change window
SELECT event_time, user, query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_kind IN ('Grant', 'Revoke', 'Create', 'Drop', 'Alter')
  AND event_date >= today() - INTERVAL 7 DAY
ORDER BY event_time DESC
LIMIT 50;

Two notes on these queries. system.query_log is the right place to count logins by the built-in default user because it is on by default and system.session_log usually is not; if you enable session_log you get a cleaner signal, including failed logins. And the auth_type column on system.users became an array when multiple authentication methods per user landed, so the hasAny() form is the one that survives upgrades. On BYOC deployments you typically cannot edit config.xml directly, but you can read system.server_settings, and that is enough to score the listener posture.

PostgreSQL BYOC database security collector (16 and later)

-- BYOC database security: PostgreSQL collector for db.* keys.  Tested syntax on PostgreSQL 16 and 17.
-- Run as a role holding pg_read_all_settings and pg_read_all_stats.

-- IAM-01  login-capable superusers other than the bootstrap role
SELECT count(*) AS extra_login_superusers
FROM pg_roles
WHERE rolsuper
  AND rolcanlogin
  AND rolname  'postgres';

-- IAM-02  pg_hba entries that authenticate with a password or nothing at all
--         (federated methods: cert, gss, sspi, ldap, radius, oauth in 18+)
SELECT count(*) AS password_only_rules
FROM pg_hba_file_rules
WHERE auth_method IN ('trust', 'password', 'md5', 'scram-sha-256')
  AND error IS NULL;

-- CFG-01 / ENC-02  listener and TLS posture in one pass
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('listen_addresses', 'ssl', 'ssl_min_protocol_version',
               'log_connections', 'password_encryption');

-- LOG-01  is pgaudit loaded and shipping DDL / role changes?
SELECT name, setting
FROM pg_settings
WHERE name IN ('shared_preload_libraries', 'pgaudit.log', 'log_destination');

pg_hba_file_rules is the collector that most often surprises people. It shows the rules PostgreSQL actually loaded, including ones with parse errors, so filter on error IS NULL or you will count rules that are not in effect. On PostgreSQL 18 the oauth method joins the federated list. A superuser that can log in and is not the bootstrap role is the PostgreSQL analogue of the ClickHouse default user problem: it is where application credentials end up when nobody is looking.

Cloud account BYOC database security collector (AWS shown, read-only)

The cloud.* keys are where BYOC database security differs from every other model, because the objects that matter live in your account. The collector below reads security groups, the vendor’s cross-account role trust policy, the agent role’s attached policies, EBS key management and CloudTrail state. It runs under a read-only audit role and writes nothing. The GCP and Azure equivalents are structurally identical: replace the IAM trust-policy check with a Workload Identity binding or a service-principal check, and the EBS/KMS check with CMEK on persistent disks or managed disks.

#!/usr/bin/env python3
"""
collect_aws.py -- BYOC database security: produce the cloud.* evidence keys for one BYOC data-plane account.

Run with a READ-ONLY role in the data-plane account:
    AWS_PROFILE=byoc-audit python3 collect_aws.py 
        --vendor-role ClickHouseBYOCControlPlane 
        --tag Key=byoc-target,Value=analytics-ch-prod > evidence/cloud.json

The script only reads. It writes nothing to AWS.
"""
import argparse
import json

import boto3

WILDCARD_DENYLIST = {"iam:*", "kms:*", "kms:ScheduleKeyDeletion", "s3:*", "ec2:*", "*"}


def public_ingress_rules(ec2, tag_key, tag_val) -> int:
    """NET-01: security-group ingress rules open to the world on data-plane ENIs."""
    sgs = ec2.describe_security_groups(
        Filters=[{"Name": f"tag:{tag_key}", "Values": [tag_val]}])["SecurityGroups"]
    hits = 0
    for sg in sgs:
        for rule in sg["IpPermissions"]:
            hits += sum(1 for r in rule.get("IpRanges", []) if r["CidrIp"] == "0.0.0.0/0")
            hits += sum(1 for r in rule.get("Ipv6Ranges", []) if r["CidrIpv6"] == "::/0")
    return hits


def vendor_role_external_id_enforced(iam, role_name) -> bool:
    """IAM-03: the trust policy must carry a StringEquals on sts:ExternalId."""
    doc = iam.get_role(RoleName=role_name)["Role"]["AssumeRolePolicyDocument"]
    for stmt in doc["Statement"]:
        cond = stmt.get("Condition", {}).get("StringEquals", {})
        if "sts:ExternalId" not in cond:
            return False
    return True


def agent_policy_wildcards(iam, role_name) -> int:
    """CPL-02: count denylisted wildcard actions across inline + attached policies."""
    docs = []
    for name in iam.list_role_policies(RoleName=role_name)["PolicyNames"]:
        docs.append(iam.get_role_policy(RoleName=role_name, PolicyName=name)["PolicyDocument"])
    for att in iam.list_attached_role_policies(RoleName=role_name)["AttachedPolicies"]:
        ver = iam.get_policy(PolicyArn=att["PolicyArn"])["Policy"]["DefaultVersionId"]
        docs.append(iam.get_policy_version(PolicyArn=att["PolicyArn"], VersionId=ver)["PolicyVersion"]["Document"])
    hits = 0
    for doc in docs:
        for stmt in doc["Statement"]:
            if stmt.get("Effect") != "Allow":
                continue
            actions = stmt["Action"] if isinstance(stmt["Action"], list) else [stmt["Action"]]
            hits += sum(1 for a in actions if a in WILDCARD_DENYLIST)
    return hits


def cmk_encryption(ec2, kms, tag_key, tag_val) -> bool:
    """ENC-01: every tagged EBS volume encrypted with a key whose KeyManager is CUSTOMER."""
    vols = ec2.describe_volumes(Filters=[{"Name": f"tag:{tag_key}", "Values": [tag_val]}])["Volumes"]
    if not vols:
        return False
    for v in vols:
        if not v["Encrypted"]:
            return False
        if kms.describe_key(KeyId=v["KmsKeyId"])["KeyMetadata"]["KeyManager"] != "CUSTOMER":
            return False
    return True


def api_audit_enabled(ct) -> bool:
    """LOG-02: at least one multi-region trail that is logging and log-file-validated."""
    for t in ct.describe_trails()["trailList"]:
        if t.get("IsMultiRegionTrail") and t.get("LogFileValidationEnabled"):
            if ct.get_trail_status(Name=t["TrailARN"])["IsLogging"]:
                return True
    return False


if __name__ == "__main__":
    ap = argparse.ArgumentParser()
    ap.add_argument("--vendor-role", required=True)
    ap.add_argument("--tag", required=True, help="Key=,Value= identifying data-plane resources")
    a = ap.parse_args()
    tag_key, tag_val = [kv.split("=", 1)[1] for kv in a.tag.split(",")]

    ec2, iam, kms, ct = (boto3.client(s) for s in ("ec2", "iam", "kms", "cloudtrail"))
    print(json.dumps({"cloud": {
        "public_ingress_rules": public_ingress_rules(ec2, tag_key, tag_val),
        "vendor_role_external_id_enforced": vendor_role_external_id_enforced(iam, a.vendor_role),
        "agent_policy_wildcards": agent_policy_wildcards(iam, a.vendor_role),
        "cmk_encryption": cmk_encryption(ec2, kms, tag_key, tag_val),
        "api_audit_enabled": api_audit_enabled(ct),
    }}, indent=2))

The IAM-03 check deserves emphasis. A vendor cross-account role whose trust policy lacks a sts:ExternalId condition is the textbook confused-deputy setup: any tenant of that vendor who learns your role ARN can ask the vendor’s control plane to act on it. Every serious BYOC vendor issues an ExternalId during onboarding. The control exists because onboarding scripts get copied between accounts, and the condition gets dropped.

The BYOC database security scoring engine

The evaluator that turns evidence into a BYOC database security score is short on purpose. Its rules are the interesting part, and each one is a decision about what the number should mean. The score is the weighted share of applicable controls that pass.

A control whose evidence key is absent is UNKNOWN and scores as a fail; unproven is treated as unsafe, which is the only rule that stops “we did not run the collector” from inflating the number. A control the evidence marks as not applicable leaves the denominator entirely. And any failing gate control caps the verdict at NON-CONFORMANT while still reporting the score, so the percentage remains useful for tracking progress even when the verdict is red.

#!/usr/bin/env python3
"""
byoc_score.py -- BYOC database security: score one target against a control standard.

    python3 byoc_score.py byoc_security_standard.yaml evidence/analytics-ch-prod.json

Rules the number is built on (keep these stable; changing them changes history):
  * score = sum(weight of PASS) / sum(weight of applicable controls)
  * a control with missing evidence is UNKNOWN and scores as FAIL -- unproven is unsafe
  * a control marked not_applicable in evidence is removed from the denominator
  * any gate control that fails caps the verdict at NON-CONFORMANT, whatever the score
"""
import json
import operator
import sys
from collections import defaultdict

import yaml

OPS = {"eq": operator.eq, "ne": operator.ne, "gte": operator.ge, "lte": operator.le}


def lookup(evidence: dict, dotted: str):
    """evidence['db']['tls_min_version'] for 'db.tls_min_version'; None if absent."""
    node = evidence
    for part in dotted.split("."):
        if not isinstance(node, dict) or part not in node:
            return None
        node = node[part]
    return node


def evaluate(standard: dict, evidence: dict) -> dict:
    weights = standard["weights"]
    results, by_domain = [], defaultdict(lambda: {"earned": 0, "possible": 0})
    earned = possible = 0
    gate_failures = []

    for domain in standard["domains"]:
        for ctl in domain["controls"]:
            w = weights[ctl["severity"]]
            value = lookup(evidence, ctl["evidence"])
            if value == "not_applicable":
                status = "N/A"
            elif value is None:
                status = "UNKNOWN"
            else:
                exp = ctl["expect"]
                status = "PASS" if OPS[exp["op"]](value, exp["value"]) else "FAIL"

            if status != "N/A":
                possible += w
                by_domain[domain["id"]]["possible"] += w
                if status == "PASS":
                    earned += w
                    by_domain[domain["id"]]["earned"] += w
                elif ctl.get("gate"):
                    gate_failures.append(ctl["id"])

            results.append({"id": ctl["id"], "domain": domain["id"], "severity": ctl["severity"],
                            "weight": w, "status": status, "observed": value,
                            "expect": ctl["expect"], "title": ctl["title"]})

    score = round(100 * earned / possible, 1) if possible else 0.0
    verdict = "NON-CONFORMANT" if gate_failures else ("CONFORMANT" if score >= 90 else "PARTIAL")
    return {"standard": f'{standard["standard"]} v{standard["version"]}',
            "target": evidence.get("target", "?"),
            "score": score, "verdict": verdict, "gate_failures": gate_failures,
            "domains": {d: round(100 * v["earned"] / v["possible"], 1) if v["possible"] else None
                        for d, v in by_domain.items()},
            "controls": results}


def print_report(rep: dict) -> None:
    print(f'{rep["target"]}  vs  {rep["standard"]}')
    print(f'score {rep["score"]}%  verdict {rep["verdict"]}'
          + (f'  (gates failed: {", ".join(rep["gate_failures"])})' if rep["gate_failures"] else ""))
    print("-" * 78)
    for c in rep["controls"]:
        flag = " GATE" if c["status"] == "FAIL" and c["id"] in rep["gate_failures"] else ""
        print(f'{c["id"]:7} {c["status"]:8} w={c["weight"]}  observed={c["observed"]!r:6}%')


if __name__ == "__main__":
    with open(sys.argv[1]) as f:
        std = yaml.safe_load(f)
    with open(sys.argv[2]) as f:
        ev = json.load(f)
    report = evaluate(std, ev)
    print_report(report)
    with open(sys.argv[2].replace(".json", ".report.json"), "w") as f:
        json.dump(report, f, indent=2)

Here is a hand-built BYOC database security evidence file for a ClickHouse BYOC cluster. The values are illustrative; the file shape is exactly what the collectors above produce once merged. Note the empty ops block: nobody recorded a restore drill, and that absence is going to be scored.

{
  "target": "analytics-ch-prod (ClickHouse BYOC, AWS eu-central-1)",
  "collected_at": "2026-09-03T10:14:00Z",
  "db": {
    "superuser_logins_last_30d": 0,
    "password_only_roles": 2,
    "tls_min_version": 1.2,
    "audit_log_shipped": true,
    "listens_public": false,
    "oldest_unpatched_cve_days": 12
  },
  "cloud": {
    "vendor_role_external_id_enforced": true,
    "public_ingress_rules": 0,
    "private_connectivity_only": true,
    "egress_allowlisted": false,
    "cmk_encryption": true,
    "backup_cmk": true,
    "agent_policy_wildcards": 1,
    "api_audit_enabled": true,
    "backup_bucket_vendor_delete_denied": true
  },
  "vendor": {
    "breakglass_ttl_hours": 4,
    "soc2_age_days": 210
  },
  "ops": {}
}

And the run, unedited:

$ python3 byoc_score.py byoc_security_standard.yaml evidence/analytics-ch-prod.json
analytics-ch-prod (ClickHouse BYOC, AWS eu-central-1)  vs  MDB-SEC-2026-09-BYOC v1.0
score 77.4%  verdict NON-CONFORMANT  (gates failed: CPL-02)
------------------------------------------------------------------------------
IAM-01  PASS     w=5  observed=0            No shared superuser / default admin account 
IAM-02  FAIL     w=3  observed=2            Database authentication is federated (IAM / 
IAM-03  PASS     w=5  observed=True         Vendor cross-account role uses an ExternalId
NET-01  PASS     w=5  observed=0            Database endpoints have no 0.0.0.0/0 ingress
NET-02  PASS     w=3  observed=True         Client access only through PrivateLink / Pri
NET-03  FAIL     w=3  observed=False        Egress from the data plane is allow-listed (
ENC-01  PASS     w=5  observed=True         Storage volumes and object-storage buckets e
ENC-02  PASS     w=3  observed=1.2          TLS 1.2+ enforced on every client-facing lis
ENC-03  PASS     w=3  observed=True         Backups encrypted with the same or a dedicat
CPL-01  PASS     w=3  observed=4            Vendor operator access to the data plane is 
CPL-02  FAIL     w=5  observed=1            Control-plane agent runs with least-privileg GATE
CPL-03  PASS     w=1  observed=210          Vendor SOC 2 Type II report reviewed within 
LOG-01  PASS     w=3  observed=True         Database audit log (DDL, grants, auth failur
LOG-02  PASS     w=3  observed=True         Cloud API audit trail (CloudTrail / Audit Lo
BKP-01  PASS     w=5  observed=True         Backups stored in a bucket the vendor contro
BKP-02  UNKNOWN  w=3  observed=None         Restore drill executed and evidenced within 
CFG-01  PASS     w=3  observed=False        No database-level access from the public int
CFG-02  PASS     w=1  observed=12           Version within vendor support window and no 
------------------------------------------------------------------------------
IAM     76.9%
NET     72.7%
ENC    100.0%
CPL     44.4%
LOG    100.0%
BKP     62.5%
CFG    100.0%

Read the output the way the CISO will. The headline is not 77.4%; it is NON-CONFORMANT with CPL-02 named as the reason, meaning the vendor’s control-plane agent holds a wildcard action in your account. That single line is the conversation to have with the vendor this week.

The 77.4% is the second sentence, and it decomposes: two password-only ClickHouse roles, egress from the data plane not allow-listed, and a restore drill nobody can evidence. The domain breakdown shows CPL at 44.4% and BKP at 62.5%, which is where the next quarter’s work goes. Encryption, logging and configuration hardening are at 100% and can be left alone.

What a BYOC database security percentage means, and what it does not

A BYOC database security conformance percentage is a statement about a standard, not about risk. Two things follow for anyone reporting BYOC database security upward. First, the number is only as good as the weights, and weights are a policy decision, so publish them and version them; a team that quietly changes critical from 5 to 3 to hit a target has broken the metric, not improved security. Second, the number can be gamed by removing controls, which is why the standard’s control count and version travel with every score. “92% against v1.0” and “92% against v1.3” are different claims, and the report.json carries both.

There is also a boundary the standard has to admit. The vendor.* keys are attestations: a SOC 2 report’s age, a break-glass TTL the vendor documented. You are recording that the vendor said something, not measuring it. That is why those controls carry lower weights and no gates. If a vendor offers an API for its break-glass audit log, and some do, promote that control from attestation to measurement and raise its weight in the next version of the standard. The direction of travel for BYOC database security is precisely that: moving controls from the hatched region of Fig. 1 into the measurable one.

Running BYOC database security scoring continuously

A BYOC database security score taken once during onboarding is a photograph. The value of a measurable standard comes from running it on a schedule and treating a drop as an incident. The pipeline in Fig. 2 runs nightly from CI and on two additional triggers: any change to the standard file, and any change in the vendor’s agent version, because agent upgrades are when IAM policies quietly grow. Store each report.json with its timestamp, plot the score per target, and alert when it decreases. A decrease means something in your account changed, or the vendor changed something in your account, and in a BYOC deployment those are the two things you most want to know about.

Open one BYOC database security ticket per FAIL and per UNKNOWN, tagged with the control ID, and close them by re-running the evaluator rather than by hand. UNKNOWN BYOC database security tickets are usually the cheapest to close and the most revealing: they are the controls nobody has ever actually checked.

Where to start with BYOC database security scoring

If you run one BYOC database today, you can have a first BYOC database security score by the end of the week. Take the standard above as v0.1, delete the controls you cannot yet collect evidence for, run the SQL and cloud collectors by hand into a single evidence file, and score it. Then add BYOC database security collectors back one at a time. The first number will be low and the first verdict will probably be NON-CONFORMANT; that is the standard doing its job. A deployment you thought was fine and a deployment you can prove is 77.4% conformant with one named gate failure are different things, and only one of them survives contact with an auditor.

MinervaDB handles BYOC database security reviews for teams running PostgreSQL, ClickHouse, MySQL, MongoDB and Kafka across all three clouds under vendor-neutral 24×7 consultative support and remote DBA engagements, and building a scoreable BYOC database security standard for a specific vendor and estate is a common first deliverable. If you want the collectors extended to your platform, or a review of a standard you have already written, book a working session.

BYOC Database Security: A Measurable Standard in 7 Domains appeared first on MariaDB.org

Continue reading “MariaDB Connector/C Compatibility: An Update on CONC-821”

MariaDB Connector/C Compatibility: An Update on CONC-821 appeared first on MariaDB.org

MariaDB Connector/C Compatibility: An Update on CONC-821 appeared first on MariaDB.org

Combined with what 9.7 LTS already carried across from Enterprise (the hypergraph optimizer, OpenTelemetry, the Group Replication components), the Community versus Enterprise line has moved further in two releases than in the previous ten years.

This post walks through the features that actually change performance, scalability and high availability outcomes, with the configuration and the measurement queries you need to verify each one on your own hardware. It also lists what is still Enterprise-only, because that list is now short enough to reason about honestly. Everything here is version-pinned against the release notes; where Oracle has published performance figures, they are labelled as Oracle’s, not ours.

MySQL 26.7, 9.7 LTS and 8.4 LTS: which one goes to production

Three supported lines shipped in the July 2026 drop, and they are not interchangeable. MySQL 9.7 was the last release to use sequential version numbers; everything after it is YY.M.P, so 26.7.0 is simply the July 2026 Innovation release. Innovation releases are supported only until the next one appears. They have no Extended Support window. MySQL 26.7 is therefore the right choice for a fleet that upgrades quarterly and wants the Change Stream Applier or the Community thread pool today, and the wrong choice for anything that needs to sit still for three years.

The ladder below is the one Oracle supports. In-place upgrades between LTS lines are adjacent-only, so an 8.0 estate cannot jump to 9.7; it goes through 8.4 first. MySQL 8.0 entered Sustaining Support on 21 April 2026, which means no new patches and no new security fixes for on-premises installations (the HeatWave extension to April 2027 does not apply outside OCI).

One practical consequence of calendar versioning that Ronald Bradford flagged in his early-access write-up: any tooling that sorts version strings lexically now believes 26.7 is older than 9.7. Check your inventory scripts, your package pinning and your monitoring dashboards before the first MySQL 26.7 host reports in. Oracle added MYSQL_PREVIOUS_LTS_VERSION and MYSQL_PREVIOUS_LTS_VERSION_ID to mysql_version.h for exactly this reason.

Thread Pool in MySQL 26.7 Community: connection scalability without the Enterprise licence

For fifteen years the Thread Pool plugin was the single most-cited reason to buy MySQL Enterprise Edition. MySQL 26.7.0 ships it in Community. Percona Server has had its own thread pool implementation since 5.5, so the capability is not new to the ecosystem, but this is the first time Oracle’s implementation, with its priority queues, stall detection and the TP_CONNECTION_ADMIN escape hatch, is available without a subscription. On MySQL 9.7 LTS it is still Enterprise-only; the Community transfer is a 26.7 change.

The mechanism is worth understanding before you turn it on, because it changes the shape of your latency curve rather than just shifting it. The default one-thread-per-connection model creates a kernel thread for every session. At two or three thousand active connections the cost is not memory, it is the OS scheduler and the InnoDB mutexes that all those runnable threads contend on. The thread pool replaces that with thread_pool_size thread groups (default 16), each owning a listener thread, a high-priority queue, a low-priority queue and a small set of worker threads. Connections are assigned to groups round-robin.

The pool tries to keep exactly one thread executing per group and only spawns another when a statement exceeds thread_pool_stall_limit (default 60 ms).

Loading it on MySQL 26.7 is a startup option, not a dynamic change, so it needs a restart and a maintenance window:

# /etc/my.cnf  (MySQL 26.7.0 Community; restart required)
[mysqld]
plugin-load-add               = thread_pool.so
thread_handling               = pool-of-threads
thread_pool_size              = 16        # start at physical cores, not vCPUs
thread_pool_stall_limit       = 60        # ms; raise for OLAP-style statements
thread_pool_max_unused_threads = 32       # 26.7 / 9.7.2 default (was 2)
thread_pool_prio_kickup_timer = 1000      # ms before a low-priority stmt is promoted
thread_pool_max_transactions_limit = 0    # 0 = no cap; set only with TP_CONNECTION_ADMIN granted

Two of those defaults changed in this release cycle. thread_pool_max_unused_threads moved from 2 to 32 in both 26.7.0 and 9.7.2, which reduces thread churn on bursty workloads at the cost of a few idle stacks. Verify the plugin loaded and confirm all four components are active (the plugin registers itself plus three Performance Schema tables):

SELECT plugin_name,
       plugin_status,
       plugin_type
  FROM information_schema.plugins
 WHERE plugin_name LIKE 'thread_pool%'
    OR plugin_name LIKE 'TP_%';

Then measure instead of guessing. The statistics table is the one to watch; the ratio of queued to executed statements per group tells you whether thread_pool_size is too low, and a rising stall count with acceptable throughput tells you the stall limit is too aggressive for your statement mix:

SELECT tp_group_id,
       connections_started,
       queries_executed,
       queries_queued,
       stalled_queries_executed,
       threads_started,
       prio_kickups
  FROM performance_schema.tp_thread_group_stats
 ORDER BY tp_group_id;

The honest framing for anyone planning to enable this on MySQL 26.7: the thread pool improves p99 under connection storms and protects InnoDB from thundering-herd contention. It does not raise peak throughput on a workload that was already CPU-bound at moderate concurrency, and on some short-transaction OLTP profiles it costs a few percent of throughput at low connection counts. Run a sysbench sweep from 64 to 4,096 connections with and without it, on your schema, before committing. We covered the plugin’s Community arrival in more detail in MySQL Thread Pool comes to Community in 26.7.

Change Stream Applier: the MySQL 26.7 replication applier built for backlog

This is the largest piece of new replication engineering since the multi-threaded applier gained writeset dependency tracking in 8.0. The Change Stream Applier (CSA) is an opt-in, per-channel alternative to the classic MTA, selected with APPLIER_VERSION = 2. It supports between 1 and 1,024 worker threads per channel, compared to the MTA’s coordinator-bound model, and, more importantly, it separates apply progression from commit progression.

Why that separation matters: in the MTA, a worker that finishes applying a transaction parks until every earlier transaction has committed when replica_preserve_commit_order is ON. Under a write-heavy backlog with long-tail transactions, workers spend more time parked than applying. In the CSA design, later independent transactions keep moving through the scheduler while earlier ones wait on their dependencies; commit order is still preserved, but the avoidable parking is gone. Relay-log reads are parallel and events are released after use, which is what makes the new per-channel memory budget possible.

Enabling it on a MySQL 26.7 replica is a channel-level change and requires the channel to be stopped:

-- MySQL 26.7.0 replica; run per channel, outside peak, with a rollback plan.
STOP REPLICA FOR CHANNEL 'ch_primary';

CHANGE REPLICATION SOURCE TO
    APPLIER_VERSION             = 2,
    APPLIER_WORKER_COUNT        = 64,
    APPLIER_EVENT_MEMORY_LIMIT  = 1073741824,   -- 1 GiB per-channel event cache
    REQUIRE_ROW_FORMAT          = 1,
    GTID_ONLY                   = 1
FOR CHANNEL 'ch_primary';

START REPLICA FOR CHANNEL 'ch_primary';

-- Verify the channel is running with the new applier.
SELECT channel_name,
       applier_version,
       applier_worker_count,
       applier_event_memory_limit
  FROM performance_schema.replication_applier_configuration;

Rollback is symmetric: stop the channel, set APPLIER_VERSION = 1, start it. The existing performance_schema.replication_applier_status_by_worker and replication_applier_status_by_coordinator views keep working, so your lag dashboards do not need to change. APPLIER_EVENT_MEMORY_LIMIT replaces the global replica_pending_jobs_size_max for CSA channels and is silently raised to replica_max_allowed_packet if you set it lower.

The prerequisites are strict and they are where most first attempts fail. The source must run with gtid_mode = ON, the channel must be GTID_ONLY = 1 and REQUIRE_ROW_FORMAT = 1, and statement or mixed binlog formats are rejected outright. File-and-position replication, SOURCE_DELAY, sql_replica_skip_counter, IGNORE_SERVER_IDS and most START REPLICA ... UNTIL forms are unsupported. If you run a delayed replica for operator-error recovery, that channel stays on the MTA.

Oracle’s engineering post reports sysbench oltp_write_only results with 64 workers where the CSA reached roughly 10.5k TPS against 7.2k for the MTA on the default durability profile with ten updates per transaction, and a wider gap with relaxed durability. Those are Oracle-reported numbers on Oracle’s hardware.

The pattern they show is plausible given the design (the gain grows with transaction size and with backlog depth), but the only number that matters for you is your own replica’s Seconds_Behind_Source recovery time after an induced backlog, measured with APPLIER_VERSION = 1 and 2 on the same channel. A clean way to induce one is to STOP REPLICA SQL_THREAD for a fixed interval under production-shaped load, restart it, and record time-to-zero-lag from replication_applier_status_by_worker. Run it at least three times per configuration and report the median.

Group Replication in MySQL 26.7: the MYSQL communication stack becomes the default

MySQL 26.7 changes the default of group_replication_communication_stack from XCOM to MYSQL, deprecates the variable itself along with group_replication_ip_allowlist, and emits deprecation warnings on the XCom-specific options. The MYSQL stack has existed since 8.0.27; what changed is that it is now the path Oracle is committing to, and new clusters bootstrapped on 26.7 will use it unless told otherwise.

Under the MYSQL stack, group communication rides the server’s own listener. group_replication_local_address must be one of the IP:port pairs the server is already bound to, the allowlist is ignored, and access control becomes ordinary MySQL authentication: the replication user needs GROUP_REPLICATION_STREAM and CONNECTION_ADMIN on top of REPLICATION SLAVE and BACKUP_ADMIN. TLS for group traffic is taken from the distributed-recovery settings, and require_secure_transport must agree with group_replication_ssl_mode on every member. Network namespaces work, which they never did under XCom.

The operational point is that this is a topology change, not a parameter change. A cluster cannot run mixed stacks; members on the wrong one simply fail to join, without a helpful error. Moving an existing XCom cluster to the MYSQL stack requires stopping Group Replication on every member and re-bootstrapping, so it is a planned outage or a ClusterSet failover, not something to attempt with SET PERSIST on a live primary. The procedure below is the one we would put in a runbook, with its gates:

-- Phase 0: pre-flight on EVERY member (MySQL 26.7.0). Record the output.
SELECT @@group_replication_communication_stack,
       @@group_replication_local_address,
       @@bind_address,
       @@require_secure_transport,
       @@group_replication_ssl_mode;

SELECT member_host, member_port, member_state, member_role, member_version
  FROM performance_schema.replication_group_members;

-- Phase 1: grant the MYSQL-stack privileges (all members, binlog off).
SET SQL_LOG_BIN = 0;
GRANT GROUP_REPLICATION_STREAM ON *.* TO '${GR_USER}'@'%';
GRANT CONNECTION_ADMIN         ON *.* TO '${GR_USER}'@'%';
SET SQL_LOG_BIN = 1;

-- Phase 2: stop the group, secondaries first, primary last.
STOP GROUP_REPLICATION;

-- Phase 3: switch stack and re-point the local address to a bound port.
SET PERSIST group_replication_communication_stack = 'MYSQL';
SET PERSIST group_replication_local_address       = '10.0.1.11:3306';
SET PERSIST group_replication_group_seeds         =
    '10.0.1.11:3306,10.0.1.12:3306,10.0.1.13:3306';

-- Phase 4: bootstrap ONCE, on the former primary only. CONFIRMATION GATE:
-- a second bootstrap creates a split brain. Verify no member is ONLINE first.
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;

-- Phase 5: join the remaining members, then validate.
START GROUP_REPLICATION;
SELECT member_host, member_state, member_role
  FROM performance_schema.replication_group_members;

Firewall rules, load-balancer health checks and MySQL Router bootstrap configuration all encode the port model, so a stack migration on MySQL 26.7 touches more than the database hosts. If you are bootstrapping a new cluster, let the new default stand and skip the migration entirely. If you run an existing XCom cluster and are not moving to 26.7 this quarter, there is no urgency: the deprecation is a warning in 26.7, and 9.7 LTS keeps the XCom default. Plan the switch into the next LTS upgrade rather than as a standalone change.

Two smaller HA items in the same release: the Clone plugin now understands calendar versions and permits cloning from an LTS to the next LTS (but not backwards, and not across non-sequential LTS lines), and the 9.7 LTS transfer already put the Group Replication flow-control statistics, resource manager and primary-election components into Community. For a deeper treatment of write throughput on InnoDB Cluster, see our InnoDB Cluster write-performance analysis.

Optimizer and InnoDB features MySQL 26.7 inherits from 9.7 LTS

Everything in 9.7.0 is in MySQL 26.7, and a few of those features change day-to-day performance work more than anything new to 26.7.

Hypergraph optimizer, now free, still off by default

The hypergraph join optimizer moved from Enterprise to Community in 9.7.0. It is disabled by default and enabled with the optimizer_switch flag, either globally or per statement:

-- Per-statement trial: no global blast radius.
SELECT /*+ SET_VAR(optimizer_switch = 'hypergraph_optimizer=on') */
       o.customer_id,
       SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders      AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  JOIN products    AS p  ON p.product_id = oi.product_id
 WHERE o.created_at >= '2026-08-01'
 GROUP BY o.customer_id
 ORDER BY revenue DESC
 LIMIT 50;

-- Compare plans. Hypergraph only emits EXPLAIN FORMAT=TREE.
EXPLAIN FORMAT=TREE
SELECT /*+ SET_VAR(optimizer_switch = 'hypergraph_optimizer=on') */ ...;

The adoption blocker is the EXPLAIN restriction: hypergraph does not produce TRADITIONAL or JSON output, only TREE. Any plan-diffing tooling built around JSON EXPLAIN stops working the moment you enable it. Note also that MySQL 9.5 already changed the default explain_format to TREE and explain_json_format_version to 2, so scripts that parsed the old tabular output broke one release earlier. Both are dynamic and can be reverted with SET GLOBAL explain_format = 'TRADITIONAL'. Measure before and after with the digest statistics rather than with anecdotes:

SELECT digest,
       LEFT(digest_text, 80)                          AS stmt,
       count_star                                     AS execs,
       ROUND(sum_timer_wait / count_star / 1e9, 3)    AS avg_ms,
       ROUND(quantile_99 / 1e9, 3)                    AS p99_ms,
       sum_rows_examined / count_star                 AS rows_examined_avg
  FROM performance_schema.events_statements_summary_by_digest
 WHERE schema_name = 'app'
 ORDER BY sum_timer_wait DESC
 LIMIT 20;

Foreign keys moved to the SQL layer (9.6)

MySQL 9.6 moved foreign-key enforcement and cascade handling out of InnoDB and into the SQL layer, and MySQL 26.7 inherits that. The stated reason is completeness of the binary log: cascaded deletes and updates now appear as row events. That is good news for CDC pipelines (Debezium and friends finally see cascades) and a surprise for capacity planning, because binlog volume and replication row traffic go up on schemas with deep cascade chains. innodb_native_foreign_keys reverts to the old behaviour. Track the delta with SHOW BINARY LOGS growth per hour before and after the upgrade.

Defaults that changed underneath you

Several InnoDB and replication defaults shifted between 9.3 and 9.5, and every one of them is live on a fresh MySQL 26.7 install. The ones that alter observable behaviour:

Container-aware sizing

Since 9.3 the server reads cgroup limits rather than host totals when auto-sizing innodb_buffer_pool_size, innodb_buffer_pool_instances, innodb_page_cleaners, innodb_purge_threads, the read and parallel-read thread counts and temptable_max_ram. MySQL 26.7 adds the 9.7 refinement for cpuset-cpus. If you run MySQL on Kubernetes, this is the release where a pod with a 16 GiB limit stops trying to allocate a buffer pool sized for the 256 GiB node underneath it. Confirm what the server actually chose after startup with SELECT @@innodb_buffer_pool_size, @@innodb_buffer_pool_instances, @@server_memory; and compare against the pod spec.

The 9.6 undo-truncation work, which in 26.7 persists progress in the tablespace header instead of separate log files, is the other InnoDB item worth noting: long undo truncations now survive a crash cleanly.

Post-quantum TLS in MySQL 26.7

When built against OpenSSL 3.5 or later, MySQL 26.7 negotiates post-quantum key-exchange groups on TLS 1.3 connections. Fourteen new variables control it across five channels: client, admin, asynchronous replication, Group Replication and X Plugin. Each channel has a *_tls_kex list, a *_force_pqc boolean and a *_use_pqc_sign boolean, all defaulting to OFF, so nothing changes until you opt in. The negotiated result is visible per session in Tls_key_exchange_algorithm and Tls_sign_algorithm.

-- Opt replication traffic into a hybrid PQC group first; clients later.
SET PERSIST replication_tls_kex   = 'X25519MLKEM768:X25519';
SET PERSIST replication_force_pqc = ON;

-- Verify on an established replication session.
SHOW SESSION STATUS LIKE 'Tls_key_exchange_algorithm';

The performance angle is real but small: ML-KEM handshakes carry a larger key share, so the cost lands on connection establishment, not on the data path. For a pooled application it is noise. For a workload that opens thousands of short-lived TLS connections per second it is measurable, and it stacks with the doubled caching_sha2_password_digest_rounds. Watch Connections per second against CPU before turning on force_pqc for the client channel. The build dependency matters too: distro packages built against OpenSSL 3.0 silently lack the capability, so check SHOW VARIABLES LIKE 'tls_version' alongside @@version_compile_os and the linked OpenSSL version reported at startup.

What is still Enterprise-only in MySQL 26.7 and 9.7

After the 9.7.0 transfer (hypergraph, telemetry, the three Group Replication components, replication applier metrics, JSON duality-view DML) and the 26.7 thread-pool transfer, the Enterprise value proposition has moved from performance and HA to security, backup and AI. That is worth stating plainly because it changes the renewal conversation. This is the remaining list as of the July 2026 releases:

MySQL Enterprise Monitor is absent from that table because it reached end of life on 1 January 2025. Estates still running it should be on a Percona Monitoring and Management migration path regardless of edition. The renewal audit we run before offering any opinion is short: SHOW PLUGINS, the information_schema.plugins rows with a non-null library, the audit% and keyring% variables, and whether MEB appears in the backup jobs. In most Enterprise estates the answer is MEB plus Audit, and both have substitutes; on 26.7 specifically, the thread pool is no longer a reason to renew.

Upgrade checklist before the first MySQL 26.7 host

Whether the target is 9.7 LTS or MySQL 26.7, the pre-flight is the same, because the landmines arrived in 9.3, 9.5 and 9.6 and 26.7 inherits all of them. Run these on every source and replica and keep the output with the change record:

-- 1. Replication defaults that flip on upgrade (9.5+).
SELECT @@gtid_mode,
       @@enforce_gtid_consistency,
       @@binlog_format;

SELECT channel_name,
       ssl_allowed,
       ssl_verify_server_cert
  FROM performance_schema.replication_connection_configuration;

-- 2. Variables removed in 9.3 that abort startup if left in my.cnf.
SELECT variable_name
  FROM performance_schema.global_variables
 WHERE variable_name IN ('innodb_undo_tablespaces',
                         'innodb_log_file_size',
                         'innodb_log_files_in_group',
                         'replica_parallel_type');

-- 3. Authentication plugins that no longer load by default (8.4+).
SELECT user, host, plugin
  FROM mysql.user
 WHERE plugin IN ('mysql_native_password', 'sha256_password');

-- 4. Functions moved out of core in 9.6 (MD5, SHA1 -> classic_hashing component).
SELECT table_schema, table_name, column_name, generation_expression
  FROM information_schema.columns
 WHERE generation_expression REGEXP 'MD5\(|SHA1\(';

# 5. Oracle's upgrade checker (MySQL Shell). 26.7 adds live progress reporting.
mysqlsh -- util check-for-server-upgrade 
    ${MYSQL_USER}@${MYSQL_HOST}:3306 
    --target-version=26.7.0 
    --output-format=JSON 
    --config-path=/etc/my.cnf > /var/tmp/upgrade-check-$(hostname).json

Then capture the baseline you will compare against after the upgrade: the top-50 digests by total wait from events_statements_summary_by_digest, replication lag distribution over a representative week, binlog growth per hour, and connection and CPU counters. Without the baseline, the post-upgrade argument about whether the hypergraph optimizer or the change-buffer default helped or hurt is opinion. Test on a clone with production-shaped load, keep a downgrade path inside the LTS series, and treat the Innovation track as something you can roll forward from but not back.

Where MySQL 26.7 fits in a production estate

MySQL 26.7 is the most substantial Innovation release since the track was introduced, and for once the interesting parts are not previews. The Community thread pool solves a concurrency problem that used to cost an Enterprise subscription or a switch to Percona Server. The Change Stream Applier is a genuinely new replication architecture, and any estate that has fought replica lag with replica_parallel_workers tuning should benchmark it. The MYSQL communication stack default and post-quantum TLS are both changes you can defer, but not ignore.

The deployment answer for most fleets is still 9.7 LTS for anything that has to stay put, with MySQL 26.7 on replicas and on quarterly-refreshed tiers where the CSA and the thread pool pay for the operational cadence. Whichever way you go, the version ladder is fixed: 8.0 to 8.4 to 9.7, adjacent only, and 8.0 is already out of patches.

If you want a second pair of eyes on an upgrade plan, a Change Stream Applier benchmark or a Group Replication stack migration, our MySQL consulting and MySQL upgrade and migration teams do this work every week across Community, Enterprise and Percona builds. As always: test every change here on a non-production copy first, and keep the DR posture verified before the upgrade window, not after.

References

MySQL 26.7.0 release notes · MySQL 9.7.2 release notes · Oracle: MySQL July 2026 GA releases · Oracle: Introducing the Change Stream Applier · Thread pool operation, MySQL 26.7 reference manual · Group Replication communication stacks · MySQL end-of-life notice · Ronald Bradford: a first look at MySQL 26.7 Early Access

MySQL 26.7 and 9.7 LTS: 7 Powerful Features for Performance, Scalability and High Availability appeared first on MariaDB.org

Source

MariaDB R2DBC Connector 1.4.2 now available appeared first on MariaDB.org

One day, my friend Martín told me about a problem he and his team were dealing with. 

Every time they needed to run a pre-production test, they had to restore a copy of the production database into their test cluster. That process alone takes about three hours because of the size of the database.

If something went wrong during the test and they needed to run it again, they had no choice but to sit through the entire restore cycle again.

Three hours. Twice (or more) just to run the pre-production test.

That’s what motivated me to build mongorewind, a terminal UI tool that watches your MongoDB cluster for changes and lets you instantly undo all of them, inserts, updates, replaces, and deletes. In reverse order, without touching your backup.

How It Works

mongorewind opens a cluster-wide change stream and records every data-modifying operation to a local log file. When you press R to rewind, it applies the inverse of each recorded operation in reverse chronological order:

To undo updates and deletes correctly, mongorewind needs to know what the document looked like before the change. 

It captures this automatically using MongoDB’s changeStreamPreAndPostImages feature, which it enables on every collection it finds — and on any new collection the moment it is created.

Requirements

• Go 1.24+ and MongoDB 6.0+ running as a replica set or sharded cluster (change streams are not available on standalone instances, can be a 1 node replica set)

Installation

git clone https://github.com/zelmario/mongorewind.git
cd mongorewind
go build -o mongorewind .

Or install directly:

go install github.com/zelmario/mongorewind@latest

Running It

Start mongorewind pointing at your cluster before running any tests:

mongorewind --uri "mongodb://host1:27017,host2:27017,host3:27017/?replicaSet=rs0"

While mongorewind is running, you will see a terminal dashboard showing the operations it has recorded:

The status indicator shows ● watching (green) while the change stream is active. When you press R, it switches to ○idle (yellow) while the rewind is in progress.

Once the test run is complete and something went wrong, you can press R and mongorewind undoes every change it recorded — bringing the data back to exactly the state it was in before the test started. No restore, no waiting.

Using It in CI Pipelines

If you are running automated tests in a CI environment, mongorewind also supports a non-interactive mode. You can start the watcher in a terminal or the background and trigger rewinds from your scripts:

bash

# Start the watcher in the background

mongorewind --uri "mongodb://..." &

# Run your test suite

run_tests




# Rewind all changes and run again

mongorewind --rewind

run_tests

mongorewind –rewind exits with code 0 on success and 1 on error, so it integrates naturally into any CI pipeline.

If you use a custom log path, pass the same –log value to both commands so they share the same socket:

mongorewind --log /tmp/mytest.log --uri "mongodb://..." &

mongorewind --log /tmp/mytest.log --rewind

A Few Things to Keep in Mind

Replica set required. Change streams need a replica set or sharded cluster. If you are working locally, you can start a single-node replica set with:

mongod --replSet rs0

And then run rs.initiate() in the mongo shell.

The log file is scoped to a session. The file is truncated on startup and after a successful rewind, so each test session starts clean. System databases (admin, local, config) and system.* collections are ignored automatically.

Pre-images are enabled automatically. mongorewind polls every 2 seconds to catch newly created collections and enables changeStreamPreAndPostImages on them. You don’t need to configure anything manually.

Going Back to Martín’s Problem

With mongorewind, Martín’s team does the restoration once. After that, every time a test fails and they need to start over, they just run mongorewind –rewind. The database goes back to its original state in seconds, and the test runs again. Three hours become just a few seconds.

Contributions are welcome! Since I’m not a developer, your feedback is valuable. If you are a developer and notice any mistakes or want to enhance the script, please feel free to contribute!

The post Mongorewind: Rewind Your MongoDB Test Data Without Restoring a Backup appeared first on Percona.

Mongorewind: Rewind Your MongoDB Test Data Without Restoring a Backup appeared first on MariaDB.org

Yuchen has discovered cases where using ARRAY index for reads causes different query results from the same query not using the index. Looks like a bug (or maybe two). Sometimes one has to choose between being compatible with MySQL and producing correct query results.

On how MySQL implements JSON ARRAY indexing appeared first on MariaDB.org

Oracle offers the same category of functionality, but its server-side plugin is part of the paid MySQL Enterprise Edition. Percona’s implementation is open source and adds three capabilities the Enterprise plugin does not provide: automatic signing-key synchronization from a JWKS endpoint, IdP group-to-role mapping, and proxy-user support. This article explains how the plugin works and why those differences matter in practice.

What OpenID Connect Brings to MySQL Authentication

OpenID Connect is an identity layer built on top of the OAuth 2.0 authorization framework [5]. Whereas OAuth 2.0 governs delegated access to resources, OIDC adds a standardized way for a client to establish who a user is. After a user signs in to an Identity Provider, the IdP issues a signed JSON Web Token (JWT), called an ID token, that carries the user’s identity and attributes in a verifiable, tamper-evident form.

Using that model for MySQL authentication brings several practical advantages over password-based accounts:

• Alignment with single sign-on. Users authenticate once with their IdP and can reuse that session context across OIDC-aware applications, including databases. User lifecycle and password management remain centralized.
• No long-lived secrets on the wire. ID tokens are short-lived and cryptographically signed, so there is no static password to steal, rotate, or accidentally commit to a configuration file.
• Support for hybrid deployments. Organizations that run MySQL on-premises while hosting applications in the cloud can still authenticate through the same identity plane on both sides.
• Broad interoperability. Because OpenID Connect is a widely adopted standard, the plugin can work with any compliant provider, including Keycloak, Okta, Microsoft Entra ID, and Google Identity.

None of that is unique to Percona; Oracle makes a similar value proposition for the Enterprise plugin. The real difference lies in how much operational burden the plugin removes from the administrator, which becomes clear in the next sections.

How OpenID Connect Authentication Works

Once the plugin and its configuration are in place, the authentication path is the same regardless of which IdP issued the token:

1. The user authenticates to the IdP and receives a signed ID token.
2. The token is written to a local file that only the client operating system account can read.
3. The MySQL client uses an option that causes the client-side OIDC plugin to load and read the token from the file. The token is sent to the server as part of the authentication handshake.
4. The server validates the secure channel and decodes the token. It then verifies the token signature using the selected IdP’s public key, checks the expiration time, and validates the configured claims.
5. The server resolves the final identity as either a personal account or a group-based proxy target. The plugin may also return roles mapped from the user’s group membership.

Configuring Trusted Providers and Letting the Plugin Manage the Keys

Identity Providers rotate their signing keys periodically as a basic security measure. If a key is ever compromised, limiting its lifetime reduces the potential impact, and regular rotation also lowers the long-term value of any one key as a target. In practice, rotation is gradual: a new key is published and accepted before it starts signing tokens, and an old key remains valid for a period after it stops signing so that tokens already in flight can still be verified.

Public keys are exposed through the standard JWKS (JSON Web Key Set) endpoint, which applications can use to verify tokens issued by the IdP [6].

The Percona OpenID Connect authentication plugin can download public keys from a configured JWKS endpoint when the plugin is loaded, typically during installation and server startup, and store them in a cache. It also provides a User Defined Function (UDF) that can refresh the cache on demand or periodically through the Event Scheduler.

By contrast, Oracle’s counterpart plugin requires signing keys to be configured statically through the authentication_openid_connect_configuration server variable, supplied either as an inline JSON string or as a path to a JSON file. There is no retrieval or refresh from the JWKS endpoint, so keeping keys current after each rotation remains a manual task for the administrator. In the window just after a rotation, tokens signed with the previous key are still valid but cannot be verified until the configuration is updated. Percona’s plugin supports static key configuration as well, but that mode is better suited to testing or temporary setups than to production.

Example

Using the feature requires two simple steps. First, JWKS endpoint URL must be set in the plugin’s configuration. For example, the below configuration defines IdP named as example-keycloak (pay attention to jwks-url element):

{
  "example-keycloak": {
  "issuer-name": "https://keycloak.example.com/realms/master",
  "jwks-url": "https://keycloak.example.com/realms/master/protocol/openid-connect/certs",
  "audiences": [ "mysql-oidc" ]
  }
}

The second step is ensuring the MySQL event scheduler is running and creating an event updating the keys. For example, to enable updating the keys for example-keycloak every hour run from MySQL client:

CREATE EVENT update_oidc_keys
  ON SCHEDULE EVERY 1 HOUR
  DO SELECT update_jwks("example-keycloak");

Benefits of Using IdP Groups

This is where Percona’s plugin diverges most clearly from the Enterprise implementation.

Groups are managed by the corporate Identity Provider and group membership may be carried by ID tokens. OIDC does not define a standard claim for that, but most IdP implementations allow adding a group claim to the tokens. The Percona’s plugin allows the administrator to configure the group claim name so that it matches the token format used by the chosen IdP.

There are two practical ways to take advantage of this feature:  group-to-role mapping and proxy users.

Group-to-Role Mapping

Membership in a group can automatically translate into MySQL roles and therefore privileges across multiple MySQL servers at the same time. On a single server, the flow looks like this:

1. The administrator creates roles and grants them privileges.
2. The administrator defines the IdP group-to-MySQL role mapping in the plugin configuration file.
3. When the user connects, the plugin returns the roles that match the user’s groups, and the server automatically grants those roles to the user.
4. The user can activate any granted role and exercise the privileges assigned to it.

Please note, that group-to-role mapping still requires an account created for each user, but automates managing user privileges.

Example

To create roles and grant them some privileges one may run:

CREATE ROLE accounting;
GRANT ALL PRIVILEGES ON accounting_database.* TO accounting;
CREATE ROLE sales;
GRANT ALL PRIVILEGES ON sales_database.* TO sales;

Then, to to define the mapping add to IDP configuration:

"group-claim": "groups",
"group-role": [
  { "/accounting": "accounting" },
  { "/marketing": "marketing" }
]

Any user connecting with an ID token containing claim “groups”:[“/accounting”] will be granted with role accounting and effectively obtain access to accounting_database and so on.

Proxy Users

The proxy capability in MySQL allows an authentication plugin to request that the connecting external user be logged in as a different MySQL user. In this model, the external identity is the proxy user and the mapped MySQL account is the proxied user. The purpose is to let multiple users share accounts with the same privilege set, avoiding the need to create a separate personal database account for every individual.

This feature must be supported by the authentication plugin, whose job is to choose the proxied user according to the specifics of the authentication method. In the Percona OIDC plugin, that selection is based on the group claim in the token and works as follows:

1. The administrator creates a proxy user identified by the OIDC plugin. This can be either a single anonymous account (”@”) without a specific group name, referred to as anonymous proxying, or multiple group-related accounts, referred to as named group proxying.
2. The administrator creates proxied users for each group. These accounts should not use a login plugin, so nobody can connect to them directly. The username must match the group name.
3. The administrator grants the PROXY privilege for each proxy user on all related proxied users.
4. When a user connects, in the anonymous proxying case the plugin returns the user’s first group as the proxied username. In the named group proxying case, the plugin checks whether the user belongs to the group and returns that group as the proxied username.
5. The server verifies that the requested proxied account exists and that the proxy user has the required PROXY privilege on it. If both checks succeed, the session runs with the proxied account’s privileges.

Other Features

Supported signing algorithms include RSASSA-PKCS1-v1_5, RSASSA-PSS, and ECDSA with SHA-256, SHA-384, and SHA-512 hashing functions.

The Percona approach uses the client-side OpenID Connect plugin from upstream MySQL, which ensures compatibility with the standard Oracle client.

Both client-side and server-side OpenID Connect plugins ensure that the token is sent via a secure channel. Accepted protocols are TCP protected by TLS, Unix sockets, and shared memory.

What OpenID Connect Authentication Does Not Do

There are some limits worth knowing.

The first comes from MySQL’s authentication design: any authentication plugin is used at connection time only. In the case of OIDC, the token is validated when the user connects, and a session that stays open may outlive the ID token that opened it. There is no out-of-the-box mechanism to force re-authentication after some time (except for idle connection timeout).

A similar situation applies to group-role mapping. The roles tied to the user’s groups in the ID token are granted or revoked at connection time. As a result, if a user is added to or removed from an IdP group, they must reconnect to Percona Server for the change to be reflected in their granted roles.

The proxying mechanism uses group membership claim instead of the token’s subject, so any token signed by a configured IdP that carries the required group is accepted. Group membership is your trust boundary in those modes, so treat it that way.

The current proxying implementation assumes the proxied user’s name matches the group name. This can be a problem when a group name isn’t a valid MySQL username (for example, it’s too long or contains disallowed characters), or when multiple groups need to map to a single account. We plan to add group-to-proxied-account mapping in future releases to address this.

The client-side plugin doesn’t verify the ID token (for example, check whether it has expired) before connecting, and the server doesn’t report the reason for access being denied (for security reasons). A good practice is to obtain a fresh token before connecting.

Conclusion

Functionally, Percona’s OpenID Connect plugin covers the same core ground as the counterpart in MySQL Enterprise Edition: signed ID tokens, claim validation, subject matching, and secure-transport enforcement.

It goes further in several important areas:

• It is open source.
• Keys can stay current automatically through JWKS synchronization.
• Group-to-role mapping allows IdP group membership to drive MySQL role grants for the lifetime of the session.
• Proxy-user support allows many IdP identities to share a smaller set of MySQL accounts.

Our OIDC implementation is suitable for real-world identity operations at scale. It can automatically map identities and groups managed by an IdP to database users and roles, and synchronize cryptographic keys.

References

1. Percona Server for MySQL documentation: OpenID Connect authentication.
2. Percona Server for MySQL documentation: Get started with OpenID Connect authentication.
3. MySQL 9.7 Reference Manual: OpenID Connect Pluggable Authentication.
4. MySQL 9.7 Reference Manual: Proxy Users.
5. OpenID Foundation: How OpenID Connect Works
6. auth0 Docs: JSON Web Key Sets.

Written by Michal Jankowski. Reviewed by Dennis Kittrell and Oleksiy Lukin.
Percona® is a registered trademark of Percona LLC. MySQL® is a registered trademark of Oracle Corporation.

The post OpenID Connect Authentication for MySQL, Now Fully Open Source appeared first on Percona.

OpenID Connect Authentication for MySQL, Now Fully Open Source appeared first on MariaDB.org

The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.

In the inconceivable case that you find a bug in the MyEnv please report it to our public repository on Codeberg.

Any feedback, statements and testimonials are welcome as well! Please send them to us.

Upgrade from 2.x to 3.0

Please check the MyEnv Installation Guide: Upgrading MyEnv.

In MyEnv v3.0.1 tpl/aliases.conf.template has changed. Thus you should replace the MyEnv aliases.conf as follows:

$ cp /etc/myenv/aliases.conf /etc/myenv/aliases.conf.$(date '+%Y-%m-%d')
$ cp myenv/tpl/aliases.conf.template /etc/myenv/aliases.conf

Changes in MyEnv 3.0.1

MyEnv

• Bug in setMyEnv.php fixed, socket was not set correctly.
• Some more aliases added for log tracking.
• Prompt port was always delayed by one switch, fixed.
• Missing port in PS1 prompt fixed again (issues/12).
• up/down for PostgreSQL should work correctly now.
• PostgreSQL should be evaluated correctly now in up.
• up should show PostgreSQL status correctly now (issues/4).
• Environment variables refactored and PostgreSQL specific variables added (issues/3).
• Discrepancies between my.cnf and myenv.conf leads now to abort if relevant configuration variables are affected (issues/5).
• Database seems OK after start which is wrong, timing issue (issues/2).
• hideschema works better now (issues/13).

MyEnv Installer

• Wrong default for socket fixed in installer.
• Nasty error message when installing PostgreSQL suppressed.
• basedir filter implemented.
• Instance type mariadb was added to MyEnv installer (issues/10).
• MySQL 26.7 is recognized correctly now (issues/1).

MyEnv Utilities

• insert_test.sh made more PostgreSQL friendly.
• test table commands made more comfortable for PostgreSQL.

PostgreSQL

• See MyEnv, MyEnv Installer and MyEnv Utilities.

General

• Cosmetic fixes.
• Some bugs and feature requests moved from TODO to …

MariaDB/MySQL Environment MyEnv 3.0.1 has been released appeared first on MariaDB.org

Source

MariaDB 12.3 InnoDB-Accelerated Binary Logging: Delivering 1.50x Higher Throughput appeared first on MariaDB.org

Continue reading “MariaDB Foundation Newsletter – September 2026”

MariaDB Foundation Newsletter – September 2026 appeared first on MariaDB.org

MariaDB Foundation Newsletter – September 2026 appeared first on MariaDB.org

In this post we will discuss performing a same-CA renewal: replacement certificates for server, member, and client leaf are issued by the existing trusted CA, and the X.509 attributes used for cluster membership do not change. In this scenario, the rotateCertificates command reloads TLS material for new connections without restarting mongod or mongos.

Important: Do not apply this hot-reload procedure when replacing the issuing CA, changing a certificate subject DN, or changing cluster-membership attributes. Those are not ordinary renewals.

What rotates, and what does not

Percona Server for MongoDB can reload the files configured through the following TLS options:

net:
  tls:
    mode: requireTLS
    certificateKeyFile: /etc/mongod/tls/server.pem
    CAFile: /etc/mongod/tls/ca.pem
    clusterFile: /etc/mongod/tls/cluster.pem

The certificateKeyFile contains the certificate and private key presented to normal clients. The clusterFile holds the certificate and key that a mongod or mongos process presents when connecting to other cluster members. If clusterFile is not configured, certificateKeyFile is also used for member authentication.

The rotateCertificates command affects new TLS connections. It does not terminate established client sessions or force a replica-set election.

Before the maintenance window

Begin this process well in advance of the certificate expiry, and avoid performing your first attempt in a production environment.

1. Inventory every process and client certificates. Include all mongod members, all mongos routers, application drivers, mongosh hosts, backup jobs, monitoring, and automation tools.
2. Confirm this is a same-CA renewal. The issuer chain trusted by every participant stays the same, and the O, OU, and DC attributes used for default internal X.509 membership matching remain unchanged.
3. Create a new PEM file for every server and client that needs rotation. A PEM file referenced by certificateKeyFile or clusterFile must include both the certificate and its matching private key. The file must strictly contain the key first, followed by the certificate, including their encapsulation boundaries.
4. Verify the new certificate details and validate the cert against the CA before copying to the production TLS directory

Stage a renewed server or member certificate

There are a few limitations for rotating certificates online:

• Each new certificate must have the same filename and same filepath as the certificate it is replacing.
• If the TLS Certificate is password-protected, its password must be the same as the old certificate it is replacing.

If CAFile, a CRL, or another configured TLS input is being renewed as part of the same operation, replace it before invoking the reload command. The command reloads the configured TLS inputs as a set; a missing or invalid input causes the reload to fail.

Luckily, incorrect certificate files will cause the rotation to fail, but will not invalidate the existing configuration or have any other side effects.

Reload one process

Connect directly to the specific mongod or mongos with an administrative user and execute the following command:

db.getSiblingDB("admin").runCommand({rotateCertificates: 1, message: "Renewed TLS certificate"})'

Immediately validate a new TLS connection to that process with a renewed client certificate. Also inspect the log for the successful certificate-rotation message and any TLS errors. Check our documentation for guidelines to perform the procedure on a replica set or sharded cluster.

Final validation and cleanup

After completing the procedure, it is a good idea to reconfirm the expiry date and SANs of the certificate presented by every mongod and mongos. Retain the old certificates only for the approved overlap period, then remove or revoke them. Don’t forget to record the new expiry dates and create alerts with enough lead time before the expiration date of the new certificates.

When the CA or member identity changes

A different procedure is required when any of the following changes:

• The issuing CA or trusted CA chain.
• The subject DN used by a MONGODB-X509 client user.
• The O, OU, or DC values used for default intra-cluster X.509 membership matching.
• net.tls.clusterAuthX509.attributes or net.tls.clusterAuthX509.extensionValue.

This is a topic for another time.

The post Rotating Expiring X.509 Certificates in Percona Server for MongoDB with Minimal Service Interruption appeared first on Percona.

Rotating Expiring X.509 Certificates in Percona Server for MongoDB with Minimal Service Interruption appeared first on MariaDB.org

MariaDB at Percona Live Amsterdam 2026: Ecosystem, Plugins, Security, and Community appeared first on MariaDB.org

MariaDB at Percona Live Amsterdam 2026: Ecosystem, Plugins, Security, and Community appeared first on MariaDB.org

In July our community lead Laura Czajkowski published Introducing Mountaineers: A Way to Say Thank You. That program is why we built the board. Laura wanted contribution to stop disappearing into noise: the bug filed late at night, the forum thread that sat unanswered, the PR, the hour telling engineering what’s broken. Mountaineers tracks that energy and gives something back: recognition, access, swag. Points, Basecamp, and the reward ladder are in her post and on the Mountaineers page. The leaderboard is the public face; my job was to make the numbers trustworthy enough for that program to work.

Why the board exists

Laura’s “what counts” list is what the board has to measure. It isn’t only code. GitHub issues, PRs, and merges count. Forum topics, replies, and accepted solutions count. Blog posts and tutorials count, including work through the Community Writers Program. Direct feedback to engineering counts too. Show up in more than one place in the same month and the climb goes faster. The public leaderboard is how that becomes visible outside the team.

A board only helps if the community trusts it. That’s where the engineering problem starts. The store holds 600+ Percona GitHub repositories; in 2026 staff alone were active in 100+ of them (~150 employee contributors). Staff and community work in the same channels – GitHub and the forum – so without filters you cannot tell whose work you’re looking at. Separating community PRs from staff PRs, and spotting forum questions from community members that still have no answer, isn’t something you do by scrolling notifications. You need a place where that signal is collected, filtered, and easy to inspect.

That’s what the internal dashboards are for. They aren’t the public site. They’re how the community team finds the work worth recognizing and the threads that still need a human.

In 2026 so far community activity is already substantial. On GitHub: ~750 pull requests and issues from 270+ contributors, including ~80 merged PRs. On the forum: ~290 active community users, ~400 active topics, and ~900 community posts. The public board for that year already has 498 people on it (257 GitHub, 240 forum, 5 content). Staff also post heavily in the same places, which is why the dashboards filter staff vs community before anything is scored for Mountaineers. Without that step, employee noise would bury the people the program exists to thank.

To turn that activity into a comparable ranking, we use a simple, fixed set of weights:

Content is still thin on the board. We only recently launched the Community Writers Program, so most climbing today is GitHub and forum. That will change as more posts land.

Those numbers and weights are the why. The rest of the post is the how: private dashboards, a public JSON feed, PSMDB, and a recipe you can reuse.

Private dashboards, public site

The architecture is a bit unusual on purpose. The dashboards live in our private corporate cloud. They talk to APIs, write to PSMDB, and show rich views for staff. The community website is a static Hugo site on GitHub Pages. It has no backend and no path into that cloud.

So we needed a bridge that doesn’t couple the two. Once a day the leaderboard job scores the periods we care about (month, quarter, year), writes community-only JSON, and publishes it to a public GitHub repository: percona/community-leaderboard. The Hugo page embeds a JS widget that fetches those files from raw GitHub URLs. No VPN. No private API. If the internal app is offline for maintenance, the last published feed still works.

We call that lifehack GitHub as a database. It’s a boring, cacheable public artifact. An object store would work the same way. For a static site, a daily JSON dump is often simpler and safer than exposing your analytics database.

The application breaks into a few components. Data sources are polled by indexer jobs and land in PSMDB. The same database feeds internal dashboards (explore, filter staff vs community) and a leaderboard component that builds period reports and publishes JSON to GitHub. The community site only talks to that public feed.

flowchart TB
subgraph sources["Data sources"]
GH["GitHub"]
Forum["Forum"]
Content["Blog / content"]
end
subgraph private["Private app · corporate cloud"]
Idx["Indexer jobs"]
Mongo[("PSMDB")]
Dash["Dashboards"]
LB["Leaderboard component"]
end
subgraph public["Public"]
Feed["JSON on GitHub"]
Site["Community site · Hugo widget"]
end
GH --> Idx
Forum --> Idx
Content --> Idx
Idx -->|"index · upsert"| Mongo
Mongo -->|"read · filter · charts"| Dash
Mongo -->|"score · export"| LB
LB -->|"publish rankings"| Feed
Feed -->|"fetch JSON"| Site
style Mongo fill:#e8f5e9
style Feed fill:#fff8e1
style Site fill:#e1f5ff
style private fill:#f9f7ff

Indexers and the leaderboard component run on a schedule (cron). The diagram above is the data path; the schedule is just when each box wakes up.

On the site, the widget is more than a dumped table. You can switch periods and categories (global, GitHub, forum), open a person and see how they scored, and browse leaders without leaving Hugo. The layout had to feel like part of Community Ascent, not like an admin export pasted into a page. There is also a Global Summit view (top 10 on a mountain) for the people who climb furthest.

Public feed: JSON the widget reads

The Hugo widget does not guess rankings. It loads plain JSON from the public repo. On first paint it fetches meta.json for available periods and categories, then {category}/{period}.json for the table (for example global/2026.json). When you open someone in the modal, it loads users/{period}/{user_key}.json for the breakdown.

The layout is simple:

meta.json
global/{period}.json
github/{period}.json
forum/{period}.json
users/{period}/{user_key}.json

meta.json is the index. The widget uses it to build the period picker:

{
 "generated_at": "2026-08-31T04:11:39+00:00",
 "default_period": "2026",
 "periods": [
 { "key": "2026", "label": "2026", "type": "year" },
 { "key": "2026-Q3", "label": "Q3 2026", "type": "quarter" },
 { "key": "2026-08", "label": "August 2026", "type": "month" }
 ],
 "categories": ["global", "github", "content", "forum"]
}

Each ranking file is one period and one category. The table reads top30:

{
 "period": "2026",
 "period_type": "year",
 "period_label": "2026",
 "category": "global",
 "top30": [
 {
 "rank": 1,
 "user_key": "gh-example",
 "display_name": "Alex Contributor",
 "avatar_url": "https://avatars.githubusercontent.com/u/12345?v=4",
 "github_login": "alex-contrib",
 "forum_username": null,
 "points_total": 595,
 "points_github": 500,
 "points_forum": 95,
 "issues_created": 2,
 "prs_created": 5,
 "prs_merged": 6,
 "topics_created": 1,
 "replies": 4,
 "solutions": 1
 }
 ]
}

The per-user file adds contribution lists for the detail view:

{
 "user_key": "gh-example",
 "display_name": "Alex Contributor",
 "period": "2026",
 "points_total": 595,
 "github_prs": [
 {
 "title": "Fix replication lag in operator",
 "url": "https://github.com/percona/example/pull/42",
 "repo": "percona/example",
 "date": "2026-07-10",
 "merged": true
 }
 ],
 "forum_replies": [
 {
 "title": "Slow queries after upgrade",
 "url": "https://forums.percona.com/t/example/12345",
 "date": "2026-06-15"
 }
 ]
}

That is all the static site needs. Live files are in percona/community-leaderboard; the widget base URL is set in the Hugo layout (window.LB_BASE).

What runs every day

Scheduled indexer jobs poll the APIs we care about, one family of sources each, and upsert documents into PSMDB. Another scheduled job builds the rankings and pushes the public feed. The web UI is how we see the store: date ranges, charts, activity tables, and a leaderboard toggle for community, staff, or all. Staff numbers matter internally. Who answered on the forum this week? Which PRs came from outside? Which community questions are still waiting? The public Mountaineers board stays community-only. Same data, different filter.

One person is often three strings in the data. GitHub login, forum username, and a blog byline under a real name rarely match. Without linking, the same Mountaineer shows up as three rows and their points never add up. Identity mapping is how we stitch that together. You can link accounts by hand in the dashboard. When a display name or handle lines up across sources, the UI also proposes a merge for approval. That is semi-automatic, not a silent auto-join. Only confirmed maps feed the public ranking.

We develop against Percona Server for MongoDB in Docker locally. Same engine shape in production. I run docker compose up, connect from the app, and start indexing. For browsing collections I use MongoDB Compass on localhost:27018 (or whatever port you map). It is the fastest way to check that indexers wrote what you expect before you trust a public export.

services:
 psmdb:
 image: percona/percona-server-mongodb:8.0
 volumes:
 - ./data:/data/db
 environment:
 MONGO_INITDB_ROOT_USERNAME: root
 MONGO_INITDB_ROOT_PASSWORD: changeme
 MONGO_INITDB_DATABASE: dashboard
 ports:
 - "27018:27017"

Pick the image tag for your CPU (8.0-arm64 on Apple Silicon, 8.0 / amd64 elsewhere). No cloud dependency just to try an idea.

MongoDB as the use case

If you like databases, this is the interesting middle of the story. We run Percona Server for MongoDB (PSMDB), but the fit is the document model. Community activity arrives as heterogeneous JSON. Issues, pull requests, forum posts, user profiles, and blog metadata don’t share one neat relational schema. Nested objects are normal, and APIs grow new fields over time. GitHub can suddenly add reactions; the forum can expose a new trust_level or group list. In MongoDB that usually means new keys on the document, not a migration that blocks indexing.

PSMDB fits that. We keep sources in separate collections and store documents close to what the API returned, then add what reporting needs: stable ids and comparable dates for range queries. Upserts by source id make re-runs safe. Staff signals such as forum groups or trust level can stay on the user document while rules evolve. Indexes on the date fields keep month and quarter scans practical. Open one document and you can explain why an event counted or not.

PSMDB is the system of record for events and profiles. Rankings are derived. The public site never connects to it. That split is useful beyond our case: a rich private store for organizers, a dumb public snapshot for the website.

We looked at paid leaderboard products first. They were rigid on sources, weak on staff versus community, or awkward with a static site. Building around PSMDB let us encode our rules instead of bending the program to a SaaS schema.

A few mistakes we deliberately avoided: exposing a public API that talks to the private database; scoring staff and community in one undifferentiated stream for the public board; asking Hugo to compute rankings at build time. The site only fetches JSON. Everything heavy stays behind the corporate network.

Building it without hundreds of hours

I could have written this stack myself. I’ve been in web development for many years and I’ve built similar pipelines before: cron jobs, API clients, admin UIs, JSON exports, frontend widgets. None of it is magic. I know how long the boring parts take when you do them by hand: wiring indexers, shaping documents, iterating on the Hugo widget layout, debugging JavaScript, mapping fields from noisy API payloads. That is often weeks of calendar time that never shows up in a program announcement.

In about a week we had a working path from APIs to PSMDB to rankings to a public widget. An AI assistant did a lot of that scaffolding for me: boilerplate API clients for GitHub and the forum, first-pass mapping of those payloads into MongoDB collections, and early prototypes of the Preact widget (layout, period picker, loading states). I still owned the architecture and the rules; the agent compressed construction I would otherwise have typed line by line. That difference is real, and I notice it because I’m not new to this work. Judgment stayed with the community team and with Laura’s brief: what counts, who is staff, how identities merge, what the public is allowed to see, and how the board should feel on the Ascent pages.

I used a similar approach for semantic search on this site with Postgres and pgvector. Different store, same idea: know what you want, let the assistant handle the repetitive build.

If you want something like this

You don’t need our private cloud. You need the same separation of concerns.

Ask for scheduled indexer jobs that poll your APIs, upsert into Percona Server for MongoDB in Docker, keep documents close to each API’s shape, and add stable ids and dates for range queries.

Ask for a private UI over that database with tables, charts, date filters, and an explicit community / staff / all view, behind basic auth or SSO, so you can verify fairness before anything is public. Use it to find outside PRs and unanswered community questions, not only to draw a ranking.

Ask for a scoring job that builds period rankings, publishes community-only JSON to a public feed (GitHub raw files or object storage), and a small JS widget for your site that only reads that feed.

Collect, understand, publish, render. Honest collection, careful filters, a simple public publish path.

If you try it and get stuck, write to me. I’m happy to talk through what worked and what we threw away. To see the result in production, open the Community Leaderboard. To join the program behind it, start with Laura’s Mountaineers announcement or the program page.

Building Useful Community Software with MongoDB Using AI Agents appeared first on MariaDB.org

We built a vector-bench to stop guessing. You name the engines you want, build them from pinned versions, put each one in the same container on the same cores with the same data, run the same measurements against all of them, and write a report. This post is about how it measures.

If you work with databases but haven’t touched vectors yet, the first half is the part you need.

What’s being indexed

An embedding is a fixed-length array of floats that comes out of a model. The useful property is that semantically similar inputs land close together when you measure the distance between them.

Two distance measures cover almost everything. L2 is an ordinary straight-line distance, the Pythagorean one, extended to however many dimensions you have. Cosine Similarity  measures the angle between two vectors and ignores their length. Which one applies is decided by the model that produced the embeddings. It isn’t a choice you get to make at query time, and getting it wrong is a good way to produce nonsense.

So the query you want is “the 10 rows whose vectors are nearest this one”:

SELECT id FROM documents ORDER BY distance(embedding, ?) LIMIT 10;

That 10 is k.

Now the problem. Answering that exactly means computing the distance from your query vector to every single row, then sorting. No B-tree or hash index helps, because neither one can order a million points by proximity in 1536 dimensions. Exact vector search is a full table scan with a lot of arithmetic bolted on.

A vector index gives up exactness to avoid that. It looks at a few thousand promising candidates instead of every row and returns the best it found. That’s the approximate nearest neighbour search, or ANN. It’s usually right.

“Usually” is doing a lot of work in that sentence, and pinning it down is most of what this benchmark does.

To score that you need to know the right answer in the first place. That’s the ground truth: the true nearest neighbours for every query, computed once by brute force with no index involved. The public ANN datasets ship theirs alongside the vectors, and without it you couldn’t score an approximate index at all.

This is the number that makes everything else meaningful, and it’s the one most vector search claims leave out. That omission is the reason this project exists.

The two kinds of vector index

Almost every database that has added vector search picked one of two designs. They attack the same problem from opposite ends, and which one you have decides what you’re allowed to tune.

HNSW

HNSW stands for Hierarchical Navigable Small World, which is a mouthful for something fairly intuitive. If you’ve ever implemented a skip list, you already have the shape of it.

It’s a graph of vectors built in layers. Every vector is a node, linked to some number of its nearest neighbours. The top layer has few nodes and its links jump long distances across the data. Each layer below has more nodes and shorter links. A search starts at the top and keeps hopping to whichever neighbour is closer to the query. When nothing is closer, it drops a layer and carries on, until it runs out of layers.

Two settings matter:

• M is how many links each node keeps. It’s fixed when the index is built. Higher M means a better-connected graph and better recall, at the cost of a slower build and a bigger index.
• ef_search is how many candidates the search keeps track of while it walks. It’s a session variable, so you can change it per query. Turn it up and the search visits more nodes, gets better recall, and runs slower.

There’s ef_construction too, the same idea applied while the index is being built. Not every engine lets you set it, which turns out to matter when you try to compare them fairly.

IVF

IVF stands for Inverted File. It partitions the data instead of linking it, not unlike list partitioning on a table.

At build time it groups the vectors into nlist clusters, each with a representative vector at its centre. At query time it compares the query against those representatives, picks the closest nprobe clusters, and searches only inside them. It builds much faster than HNSW and uses less memory, but usually gives worse recall at the same speed. It misses when the true neighbour happens to sit just outside the clusters it looked in.

We only test engines running HNSW, which is what most databases shipped. Putting an IVF engine on the same chart would mostly measure the gap between two algorithms rather than how well anybody implemented one, so IVF-only engines get their own bucket.

Why one number is never enough

Recall isn’t a property of an engine. It’s a setting, and ef_search is the dial.

Here’s one HNSW index on one machine, same data, same queries. The only difference is that on the first row the search tracks 10 candidate nodes as it walks the graph, and on the second it tracks 800:

ef_search=10 3,678 queries/sec recall 0.9593
ef_search=800 409 queries/sec recall 0.9987

Keeping 800 candidates instead of 10 finds a better answer and takes nine times as long. Both rows are honest measurements of the same index on the same hardware.

Which is why “our database does 3,678 vector queries a second” tells you nothing. You don’t know how often it was handing back the wrong rows, and the person quoting it may not know either. The reverse is just as empty: recall with no throughput next to it is free, because recall 1.0 is always available if you turn the index off and scan the table.

Every measurement here is a pair. If you take one thing from this post, take that.

What the harness puts on each engine

One table per engine. An id, an integer tag column used only by the filtered tests, the vector, and an HNSW index on it at a configured M.

CREATE TABLE t1 (
id INTEGER PRIMARY KEY,
tag INTEGER NOT NULL,
v VECTOR(1536)
);

Then two queries, plain top-k and the same search restricted to a subset of rows:

SELECT id FROM t1 ORDER BY distance(v, ?) LIMIT 10;
SELECT id FROM t1 WHERE tag < ? ORDER BY distance(v, ?) LIMIT 10;

tag holds values 0 to 99 spread evenly, so tag < 10 passes about 10% of rows and tag < 1 about 1%. That’s how we control selectivity.

Every engine writes all of this differently. Some declare the index inside CREATE TABLE, others want a separate CREATE INDEX, and the distance functions have different names everywhere. Translating that is the driver’s job, and the drivers are the only engine-specific code in the whole harness.

Every engine also has at least one setup detail that will quietly wreck your numbers. PostgreSQL, for instance, stores oversized values out of line in what it calls TOAST, and a 1536-dimension vector counts as oversized. Unless the column is set to STORAGE PLAIN, every single distance comparison pays for an extra fetch. It’s one line of DDL. Miss it and you publish PostgreSQL looking slow for a reason that has nothing to do with its vector search, and you’d never know from the results.

What we measure

Recall against throughput. Iterate ef_search against a fixed index, record recall and QPS at each point, repeat at a few values of M. k=10 throughout. The query vectors come from the dataset’s own held-out query set, never from the rows we loaded, because searching for a vector that’s already in the index is a much easier problem and would flatter everybody equally.

The two settings behave completely differently, and it shapes how long a run takes. ef_search is a session variable, so iterating it reuses the index that’s already built and each extra point costs almost nothing. M is baked into the index, so every value of M means dropping the table and loading the entire dataset again. On a million 1536-dimension vectors that’s hours per value. Hence many ef_search points and very few M values.

Build cost. Wall time, rows per second, index size on disk, peak memory.

This is the easiest place in the whole benchmark to publish a misleading number, because engines don’t build the index the same way. Engines can build indexes either incrementally, bulk, or both. What does that mean? 

Incremental. The graph is updated on every INSERT. Loading is slow, but when the last row lands the index is finished and the table is ready to query.

Bulk. All the rows load first, then the whole graph gets built in one pass. Much faster in total, but the table can’t answer a vector query until the build finishes.

Those are two different operations. One engine in our set does both, and its bulk path loaded 18 times more rows per second than its own incremental path. Same engine, same data, same machine, 18x apart.

So a bulk number from one engine put next to an incremental number from another doesn’t compare engines at all. It compares two ways of building an index, and the ratio looks impressive enough that people quote it anyway. We measure both paths on any engine that has both, and the report says which is which.

Peak memory comes from the server’s container, with the database as the only thing running in it. The harness runs in a separate container and reaches the server over a private network.

That separation matters more than it sounds. The client holds the entire dataset in memory, several GB of Python arrays. If it shared a container with the database, the container’s memory accounting would count those arrays as database memory, and every memory figure we published would be inflated by whatever the client happened to be holding.

Concurrency. QPS and latency percentiles from 1 to 32 clients. Engines cache their graphs in quite different ways and none of that shows up until clients start competing for the same cache. We report how much of the ideal speedup each engine actually got alongside raw QPS, because an engine that stops gaining throughput at 2 clients while its p99 gets 15 times worse is doing something very different from one that keeps scaling, and a throughput column on its own hides that completely.

Filtered search, at several selectivities down to 1% of rows passing. This is the case that’s supposed to justify keeping vectors in your database instead of a dedicated store, so it deserves more attention than it usually gets.

Filtering changes what “correct” means. The true top 10 among rows where tag < 10 is not the true top 10 overall, so for every selectivity we recompute ground truth by brute force over only the rows that pass. Score filtered results against the unfiltered ground truth that shipped with the dataset and every engine gets a recall near zero. We know, because we did exactly that for a while.

Some queries come back with fewer than 10 rows. In one run, 81 out of 200 did. This is not the data running out. At 10% selectivity about 99,000 rows pass the filter, so there are always at least 10 to find. The cause is the order of the operations. HNSW searches by distance first, then applies the WHERE clause. It gathers a few thousand candidates, the filter throws most of them away, and sometimes fewer than 10 are left. (If a filter really did match fewer than 10 rows, the ground truth shrinks too, and the engine still scores 1.0.) Recall already handles this. A row the engine did not return counts as a miss, so six correct rows score 0.6. We report the count because two different problems score the same. “10 rows, four of them wrong” and “six rows, all correct” are both 0.6. The first needs a wider search. The second needs iterative scanning. The count tells you which one you have. It also means the throughput is flattered, since six rows is less work than ten.

Churn. Recall and throughput before and after deleting and reinserting part of the corpus, since deletions leave graph edges pointing at rows that are gone. Whether rebuilding the index recovers what’s lost, we don’t know yet. It’s the obvious next thing to test and we haven’t done it.

Keeping the comparison fair

Everything runs twice.

The normalized pass gives every engine identical CPU, memory and cache budgets, so a difference in the results belongs to the implementation rather than to who was handed more RAM. The tuned pass lets each engine use the settings its own documentation recommends. Tuned is more realistic and less controlled, which is exactly why it doesn’t replace the first one. A result that survives both passes is about the engine. One that flips between them is interesting for a completely different reason.

Cores are pinned explicitly. One logical CPU per physical core, because SMT siblings share execution units and two threads on one core don’t behave like two cores. Never a mix of P-cores and E-cores on hybrid chips either, since migration between core types adds more variance than several of the effects we’re trying to measure. Durability is relaxed the same way everywhere, or we’d be comparing default fsync policies and calling it vector search.

Some differences can’t be equalised at all, so we write them down instead of pretending. A knob only one engine exposes goes unused in the normalized pass, because using it would hand that engine a tuning axis nobody else has. An engine that insists on a particular isolation level gets it set for everyone. And defaults that are obviously placeholders get sized from a shared budget — one family of engines still ships a 16 MiB graph cache, which is nothing, and judging an engine on a value its own vendor expects you to change measures absolutely nothing. All of these land in a “known asymmetries” section above the results.

One hardware note that catches people out. Several of these implementations ship hand-written AVX-512 code for the distance maths, where a single instruction does the arithmetic for 16 floats at once. The same index on a CPU without AVX-512 is effectively a different benchmark, and the slowdown isn’t the same for every engine, so you can’t even scale the numbers to compensate. The CPU model and its feature flags go into every run’s manifest for that reason, along with engine versions and commits, image IDs, and the resource limits as they are actually resolved rather than as we requested them. No manifest, no report.

Reading the results

Read the validity section before you look at a single chart. Our reports go environment, then validity, then known asymmetries, then results, in that order on purpose. A failed phase, an engine returning short result sets, a CPU missing the instruction set the engines wanted — all of it lands in front of you before you’ve formed an opinion.

The thing to watch for is the silent full scan.

Any of these engines will quietly stop using the vector index and scan the table instead. A scan returns exact results, slowly, so in the output it looks like high recall and low throughput. That’s indistinguishable from a conservatively tuned index unless you go and read the query plan.

It happens for thoroughly boring reasons. One engine’s optimizer costs the vector index against a table scan and takes the scan once the LIMIT is above roughly a quarter of the table, and we still haven’t found a setting that moves it. Another falls back with no error and no warning when the query asks for a different distance than the index was built for — build the index for cosine, write the query with the L2 operator, and you get a sequential scan and a sort, with nothing anywhere to tell you.

So every driver runs EXPLAIN for each configuration and checks the index name appears in the plan.

WARNING: vector index NOT used (k=10, filtered=True). Plan: …Seq Scan…

Anything that is scanned goes into validity. This is far and away the easiest way to produce impressive vector benchmark numbers by accident, and if a benchmark doesn’t mention checking for it, we’d want to know why before believing anything in it.

For recall against throughput, the useful presentation is a curve rather than a number. Iterate ef_search, plot recall against QPS, keep the best points: for each level of accuracy, the highest throughput anything reached at it. One engine beats another only where its curve sits above the other’s at the same recall. If the curves cross, then the answer genuinely depends on how accurate you need to be, and saying so is a result rather than a dodge.

Curves do invite comparing shapes instead of heights at one point, so there are bar charts as well, QPS at recall floors of 0.90, 0.95 and 0.99. Pick the accuracy you’d actually accept and read across.

Things that went wrong while we built this

Worth listing, partly because they’re the reason to trust anything else here, and partly because anyone building something similar will walk into them.

Our first ingest numbers were garbage. The load path was doing one INSERT per network round trip with autocommit on, and we measured 88 rows a second. Batching 500 rows per transaction took the same engine to 373. Publishing the first number would have been benchmarking our own client and calling it a database.

Filtered search and churn were scored against full-corpus ground truth even on runs that used a subset of rows. Every engine looked bad and the bug was entirely ours. Ground truth is now keyed on dataset, k, row count and selectivity.

Both resource passes shared one results directory, and the ANN runner skips configurations that already have results. So the tuned pass quietly skipped everything the normalized pass had computed, and our tuned numbers were mostly normalized numbers wearing a different label. That one took an embarrassingly long time to notice.

Readiness probes lie. One engine’s standard “are you accepting connections” check returns success before the database it’s supposed to create actually exists. The probe passed, the first query failed, and we spent a while convinced it was an engine problem.

The most recent one, on a 1536-dimension corpus. The ANN runner holds the whole dataset in memory twice, once in the parent process and again in a forked worker, and the copies aren’t shared. That’s roughly 12 GB for a million embeddings, on top of whatever the server is using, in a container we’d sized for the server alone. The kernel killed the worker. The runner doesn’t check worker exit codes, so it logged “Terminating 1 workers”, exited successfully and wrote no results — which looks exactly like a run that had nothing left to do. Three hours to fail, and it failed silently.

Adding a database

This is the part we cared most about getting right, because the whole point was to avoid rebuilding the apparatus every time somebody ships vector search. Each engine needs:

• a Dockerfile producing a runtime image and a test image from a pinned version
• a config declaring ports, credentials, and which server settings map onto the normalized CPU and memory budget
• a module for the recall and throughput side
• a driver: create index, load, query, filtered query, index size, and the EXPLAIN check

What’s next

Results, published with the manifests and the raw per-configuration records, so you can check them instead of taking our word for it.

Everything is at https://github.com/Percona-Lab/vector-bench harness, drivers, Dockerfiles, docs. If we’re measuring something wrong, or being unfair to an engine you know better than we do, tell us.

The post Benchmarking vector indexes appeared first on Percona.

Benchmarking vector indexes appeared first on MariaDB.org

This performance investigation aims to look into the read/write performance of Percona Server for MySQL 8.4 and how it changed between versions released in 2026:

• 8.4.8-8 released on 12 March 2026
• 8.4.10-10 released on 30 June 2026
• 8.4.11-11 released on 20 August 2026

We want to see if there are improvements in scalability and performance in OLTP read/write operations, where the improvements are most noticeable and how they were achieved. For some readers this material might help with making the decision whether upgrading to a newer version is worth the effort.

An important note is that the new features or security patches will not be taken into consideration.

Measuring Latency (Percentiles) and Resource Utilization (CPU, RAM, I/O) is not in the scope of this post.

2. Configuration and Methodology

The configuration was as follows:

The benchmarks were done across the following dimensions:

One of the points in benchmarking was to create combinations of similar Buffer to Data Ratios, but with the different Database Sizes. This gives us the following possible combinations of innodb_buffer_pool_size and Database Size:

We should be able to see how efficiently the server manages an increasingly larger number of rows while keeping the Buffer to Data Ratio the same.

Execution of the benchmarks was done as follows:

Important Database Configuration options (the actual config files with specific settings for each run can be downloaded from the interactive graphs):

In the high concurrency scenario when all CPU cores are working under maximum load the performance fluctuations might appear out of the ability of a specific CPU crystal to work at a specific sustainable maximum frequency. Intel Xeon Gold 6230 processors installed in the test servers have a base frequency of 2100 MHz and maximum turbo frequency of 3900 MHz. However, such turbo frequency can only be achieved for a short period of time on an isolated core. The load and the heat production of the physical core neighbours limit the frequency of the whole CPU. Some CPU’s were able to hold 2530 MHz on all cores for 20+ hours of intense load, others could only reach 2420 MHz. For consistency of the tests the turbo frequency was capped to 2400 MHz from the beginning on all servers. It helped to eliminate the struggle between turbo mode trying to increase the frequency beyond sustainable levels and the CPU thermal protection bringing the clock down. More stable hardware performance reduced the measurement fluctuations during the benchmark runs regardless if they were done on the same or a different physical server.

3. Results

First, let’s check the I/O bound scenario where the InnoDB Buffer to Data Size is the smallest (1:12).

The graph shows the configurations with innodb_buffer_pool_size=8G and Data Size 96G (or 20M rows per table, 400M rows in total):

[ INTERACTIVE GRAPH ][ TABLE ]

The first thing that catches the eye is the hugely superior performance of the version 8.4.11-11 over 8.4.10-10 and 8.4.8-8 in the high thread numbers. In the situations when the number of physical cores (80) is smaller than the number of threads (128+) the versions 8.4.10-10 and 8.4.8-8 have a steep performance degradation. However, the TPS for 8.4.11-11 keeps growing. This is due to the optimization done to InnoDB LRU pages flushing algorithm. The optimization specifically targeted the scenario when the data size is larger than the available server buffers and the server has many concurrent connections doing random read-write operations. The optimizations in 8.4.11-11 deserve a separate explanation and they will be published in another blog post.

The less noticeable, but important difference can be spotted between the TPS for 8.4.8-8 and 8.4.10-10.

The version 8.4.10-10 shows better performance (especially at the saturation point with 64 threads), which should mostly be attributed to the introduction of Performance Guided Optimization (PGO). 

More information on PGO can be found here:

https://docs.percona.com/percona-server/8.4/pgo.html

With the smaller data and buffer sizes the performance difference gives an almost identical picture:

4G buffer, 48G data [ INTERACTIVE GRAPH ][ TABLE ]

2G buffer, 24G data [ INTERACTIVE GRAPH ][ TABLE ]

Now let’s review what happens with the ratio 1:2.
This time the buffer pool size also plays a more significant role and the performance difference is not characterized by the Buffer / Data size ratio.

With innodb_buffer_pool_size=12G and 24G data size the performance gap between 8.4.11-11 and older versions is still huge as can be seen on the graph:

[ INTERACTIVE GRAPH ][ TABLE ]

However, setting innodb_buffer_pool_size=24G and 48G data size reduces the gap. The superiority of 8.4.11-11 is still visible:

[ INTERACTIVE GRAPH ][ TABLE ]

Moving to innodb_buffer_pool_size=48G and 96G data size shrinks the gap even more:

[ INTERACTIVE GRAPH ][ TABLE ]

In this post we are not going to talk about mechanisms behind shrinking performance gaps in 1:2 Buffer / Data size ratio.

Holding the entire data set in memory is not the most common thing for the database server, but in some cases it happens. Therefore, we are covering such situations as well.

[ INTERACTIVE GRAPH ][ TABLE ]

As the above graph shows, 8.4.10-10 is slightly ahead of 8.4.11-11, but the gap is very small.

This behavior is consistent with other data sizes for fully buffered data:

innodb_buffer_pool_size=64G and 48G Data Size:

[ INTERACTIVE GRAPH ][ TABLE ]

innodb_buffer_pool_size=128G and 96G Data Size:

[ INTERACTIVE GRAPH ][ TABLE ]

Again, we will not go into details about why this happens. Though it is worth noting that both 8.4.10-10 and 8.4.11-11 do better than 8.4.8-8 in all runs and configurations.

The table interpretation of the results is available as well.

4. Comparing with Upstream MySQL 8.4.11.

The performance improvements in Percona Server for MySQL 8.4.11-11 are not a part of the Upstream MySQL 8.4.11. The patch was specifically designed to address the issue of Percona Server being slower than MySQL in I/O bound scenarios.

Also, the patch eliminated the abrupt performance degradation in the higher thread count after reaching the saturation point at 64 threads:

[ INTERACTIVE GRAPH ][ TABLE ]

As the graph shows – Percona Server 8.4.8-8 / 8.4.10-10 was slower than MySQL in lower thread count. Although it was still faster in 128+ threads, the Percona Server was still subject to a substantial slow-down. That is where Percona Server 8.4.11-11 really shines.

However, with the fully buffered data MySQL goes faster than any Percona Server:

[ INTERACTIVE GRAPH ][ TABLE ]

5. Summary

The Performance of the Percona Server 8.4 for MySQL is progressing well from older to newer version offering significant performance improvements especially in the version 8.4.11-11. This version shows very significant improvements in performance on the data sets that require I/O. Also, it outperformed the upstream MySQL 8.4.11.

With fully buffered data sets the version 8.4.10-10 is slightly better than 8.4.11-11. MySQL Server in this case shows the fastest performance.

The PGO had a positive impact demonstrating the version 8.4.10-10 being faster in all tests on all configurations than 8.4.8-8.

The performance depends not only on the ratio between the buffer and the data size, but also on the buffer size.

The post Performance Progression of Percona Server for MySQL 8.4 appeared first on Percona.

Performance Progression of Percona Server for MySQL 8.4 appeared first on MariaDB.org

The posts are also honest about the cost. The work is experimental. The worst bug found was silent data loss on every replicated transaction: the two-phase commit code for mixed-engine transactions dropped prepared DuckDB transactions before they committed, so replication reported success while no rows persisted. The test harness caught it and it was fixed. The row-by-row applier also cannot keep up with bulk writes on the primary.

I want to add a second road to the same place, one that changes nothing inside mysqld. Disclosure first: I build dbtrail, the Apache 2.0 open source tool used below, so read this as one more point in the design space, from someone who is not neutral. Everything here ran on stock Percona Server 8.0 from Docker Hub and stock DuckDB from Homebrew, and the commands at the end reproduce all of it.

Your binlog is already a columnar feed

The storage engine approach puts the column store inside the server, so the server must cooperate: a patched build, a new engine in the commit path, replica tables created by hand with ENGINE=DuckDB. The other road starts from what MySQL already provides. With binlog_format=ROW and binlog_row_image=FULL, the binary log is a complete, ordered feed of every row change, with full before and after images. Any process can consume that feed as a replication client, the way a replica does, and install nothing on the server.

dbtrail is that process. It does two things with the feed:

1. It keeps a short, searchable window of recent events in a plain MySQL table, for investigation and recovery.
2. It moves closed hours out to Parquet files, the open columnar format every analytical engine can read. DuckDB reads it natively.

The pipeline is mysqld -> binlog -> Parquet -> DuckDB. No patched server, no plugin, nothing new in the commit path. And because the feed is history rather than current state, dbtrail can answer a question no replica can: what did this row look like before, and how do I undo what happened to it?

The demo

One container for the source, stock image, with the settings any change-capture consumer needs (plus one line to keep the demo’s own index out of the binlog, since source and index share one server here):

docker run -d --name demo -p 13310:3306 -e MYSQL_ROOT_PASSWORD=demo 
 percona/percona-server:8.0 
 --gtid-mode=ON --enforce-gtid-consistency=ON 
 --binlog-rows-query-log-events=ON --binlog-ignore-db=bintrail_index

binlog_format=ROW and binlog_row_image=FULL are already the 8.0 defaults. The schema is a small shop: customers, orders, order_items. Three short commands point dbtrail at it (init, snapshot, stream; they are in the last section), and from there the work happens in dbtrail’s web console. Then I replayed a five-hour synthetic workload: 3,265,000 row events in all. 2.7 million INSERTs, 510 thousand UPDATEs, 55 thousand DELETEs. (A tip for demo builders: SET TIMESTAMP = in the writing session backdates the binlog event timestamps, so a fast replay spreads across past hours and you can watch retention behave as it would in real life.)

The console’s Status view answers the first question an operator should ask of any change-capture pipeline: did we miss anything?

The green banner is a verdict, not a guess: dbtrail tracks continuity across the captured range, and it says plainly that this is not a liveness check. On my MacBook the capture stream held about 18,000 events per second while it tailed the binlog. That answers the applier problem from the first post: dbtrail does not push rows through the storage engine API one at a time, it batches them into an index, so bulk writes on the primary are absorbed rather than queued.

From hot partitions to Parquet

The index table is range-partitioned by hour. dbtrail archives each closed hour to zstd-compressed Parquet, checks the result, and only then drops the partition. Retention on the expensive tier becomes hours, not months. The demo moved all 3.26 million events out in 14 seconds, and the Status view above already showed the result: 7 archive files, 26 MB. Here is that number next to what the same events cost in InnoDB:

Do not take that 100x as a general truth. Synthetic demo data repeats itself and compresses far too well, and the InnoDB figure includes the primary key and secondary indexes that make the hot tier searchable. On production data expect about one order of magnitude. The direction matches what Evgeniy measured at 500 GB, where the DuckDB engine held TPC-H in 26 percent of the raw CSV size and InnoDB needed 135 percent. Column formats fit this data. Row formats do not.

One mydumper pass adds the state side: a baseline snapshot of the tables themselves, also stored as Parquet. For the demo shop that was 2.6 million rows in 8 MB, done in under four seconds. dbtrail uses baselines to rebuild full tables and single rows at a point in time; here they also give DuckDB something current to query.

Stock DuckDB, no plugins

The archive is just files, so the query engine does not have to be dbtrail. One command, bintrail views (bintrail is the name of dbtrail’s CLI binary), writes a single SQL file of DuckDB view definitions over the layout it knows about: an events view across every archived partition, and one state__

bintrail views --index-dsn "$IDX" --baseline-dir /data/baselines --out views.sql
duckdb
D .read views.sql

That is the whole integration. dbtrail never opens DuckDB and never runs what it prints; the file is plain SQL you can read before you use it. From there, use any DuckDB you like: the CLI, a notebook, a BI tool’s connector. Same laptop, same data, same queries on both engines:

Now the fine print, because a benchmark without it is just marketing. The chart has two groups. The first group queries the event history: those scans hit a 2.8 GB index with the container’s stock 128 MB buffer pool, so InnoDB paid for disk reads, and a bigger pool narrows that gap. The second group runs over current state and is the fair comparison: I grew the pool to 4 GB, both tables sat fully in cache, and the 11x and 20x that remain come from the design of the engine, not from the disk. Both engines returned the same results, down to the last decimal. That is a free integrity check: two independent systems read the same history and agreed.

These are laptop numbers, and I did not run TPC-H. For the ceiling of what columnar execution does at 500 GB, read Evgeniy’s second post. My point is the floor: 2.8 GB of freshly rotated history sat on my laptop as 26 MB of open files, and a stock engine answers questions over them in tens of milliseconds. Nobody patched anything to get here.

The part a replica cannot do

A DuckDB replica holds current state. dbtrail holds what happened. This is the same data the analytics just scanned, now in the console’s Events view, filtered to one order:

Three events tell the row’s whole story: created, cancelled, deleted, each with its GTID and the connection that did it. The expanded UPDATE shows the full before and after images dbtrail keeps for every change.

Here is the detail that surprises people: when I took that screenshot, the MySQL side of the index held zero rows. Rotation had dropped every partition. The console read the answer from the Parquet tier in under 200 milliseconds. The Undo button works from there too:

One click wrote reversal.sql: a script that puts the row back exactly as it was, tagged with the GTID it reverses. Nothing runs on its own; you read the script, then you apply it. The same Restore view takes a whole table and a time window: the demo’s 5,000 deleted orders came back as 5,000 INSERT statements, generated in 0.2 seconds, from files, while the database that held that history no longer existed.

A column store fed by your binlog does not have to be a replica. It can be a time machine.

Trade-offs, honestly

Neither road wins outright.

Where the storage engine is better: its DuckDB copy is always current, and it speaks MySQL protocol. A DuckDB engine on a replica runs seconds behind the primary, and existing BI tools connect to it unchanged. dbtrail’s capture also runs seconds behind, and its console and CLI query that fresh index directly. What waits is the copy DuckDB reads: the events view covers the hours already rotated to Parquet, and the state views show the latest baseline, so that side is as current as your rotation and baseline schedule. If you need a BI tool on MySQL protocol reading a complete, current copy, Evgeniy’s architecture, or a product like HeatWave, aims at exactly that.

Where staying outside the server is better: risk and history. The hardest place to change a database is the commit path, and a storage engine lives there. The 2PC bug shows the kind of failure that layer produces, and it took a dedicated test harness to find it, because it was silent. A replication client cannot corrupt a commit. Its failure modes are lag and gaps, and dbtrail reports gaps as a first-class verdict rather than hoping. History is the other half: audit, point-in-time rebuilds, and row-level undo all come from the same files the analytics read.

Shared constraints. Both roads need ROW format with FULL row images. Both need primary keys to apply or reverse UPDATEs and DELETEs. Both leave the source of truth in InnoDB, untouched. On maturity: the engine posts describe an experiment and say so; dbtrail is released and versioned (v0.65.0 as I write this) but young, and you should question my numbers the same way I question everyone else’s.

Both posts and this one agree on the base facts: row stores are the wrong shape for analytical scans, DuckDB fits MySQL-shaped data well, and the open question is where the column store should live. Evgeniy shows what you gain when the server cooperates. dbtrail shows what you get when you leave the server alone.

Reproduce it

dbtrail binaries are on the releases page; DuckDB comes from your package manager. The web console ships as its own binary, bintrail-console, and can also run capture and console together as one daemon (bintrail-console watch).

# 1. A source with binlogs (ROW + FULL are 8.0 defaults)
docker run -d --name demo -p 13310:3306 -e MYSQL_ROOT_PASSWORD=demo 
 percona/percona-server:8.0 --gtid-mode=ON --enforce-gtid-consistency=ON 
 --binlog-rows-query-log-events=ON --binlog-ignore-db=bintrail_index

# 2. Create your schema, then point dbtrail at it
bintrail init --index-dsn "$IDX"
bintrail snapshot --source-dsn "$SRC" --index-dsn "$IDX" --schemas shop
bintrail stream --source-dsn "$SRC" --index-dsn "$IDX" --server-id 4444 --schemas shop &
bintrail-console serve --index-dsn "$IDX" --baseline-dir /data/baselines &

# 3. Run your workload, then tier the history out and take a baseline
bintrail rotate --index-dsn "$IDX" --retain 7d --archive-dir /data/archives
bintrail dump --source-dsn "$SRC" --output-dir /data/dump --schemas shop
bintrail baseline --input /data/dump --output /data/baselines

# 4. Hand the whole thing to DuckDB
bintrail views --index-dsn "$IDX" --baseline-dir /data/baselines --out views.sql
duckdb -c ".read views.sql" 
 -c "SELECT table_name, event_type, COUNT(*) FROM events GROUP BY 1,2;"

If you try dbtrail, tell me where it fails as much as where it works well. Issues and pull requests are open at github.com/dbtrail/dbtrail, and I am around in the Percona Community Slack.

DuckDB Speed on MySQL with dbtrail, Without a New Storage Engine appeared first on MariaDB.org

This shift highlights the combination of PostgreSQL and ClickHouse as a compelling solution for modern data platforms, with Postgres serving as the authoritative transactional system, and ClickHouse operating as a high-performance analytical platform for processing massive datasets in real time. Together, they function as complementary components connected through continuous Change Data Capture (CDC) synchronization. Before we get into the common architectural patterns, let’s briefly look at why Postgres + ClickHouse.

Why Postgres + ClickHouse?

PostgreSQL excels at Online Transaction Processing (OLTP). Utilizing mature ACID compliance, MVCC, and advanced indexing, it serves as the operational system of record for managing concurrent transactions like accounts, finance, and inventory.

In contrast, analytical workloads like BI dashboards and fraud detection require scanning millions or billions of historical records. Running these massive sequential scans continuously on a production OLTP system increases CPU, memory, and latency, ultimately degrading application performance.

ClickHouse solves this issue as a column-oriented Online Analytical Processing (OLAP) database designed for rapid queries over massive datasets. Instead of replacing PostgreSQL, ClickHouse complements it by offloading complex analytical processing.
Consequently, architectural focus has shifted from choosing between the two platforms to determine how they can work together effectively. This reflects a trend toward polyglot persistence, where specialized databases collaborate to handle distinct transactional and analytical workloads.

Pattern 1: Postgres to ClickHouse Real-Time Analytics

The widely adopted PostgreSQL and ClickHouse architecture uses PostgreSQL as the transactional source of truth while continuously replicating data to ClickHouse for analytical processing. This separates workloads without complex ETL pipelines, allowing applications and BI tools to query ClickHouse directly. Because ClickHouse is optimized for large scans and aggregations, dashboards load significantly faster while keeping production PostgreSQL tables responsive and isolated from heavy reporting risks.

This approach is ideal for real-time systems like SaaS, fintech, and IoT platforms, where fast dashboard updates are a key part of the product experience.

Architecture Overview

In a typical PostgreSQL and ClickHouse deployment, applications run transactional operations on PostgreSQL to ensure data consistency. Committed transactions are then replicated to ClickHouse via a Change Data Capture pipeline, enabling analytical queries to execute independently from the OLTP workload.

By separating the transaction plane from the analytics plane, organizations allow each database to focus on the workload for which it was designed, improving scalability, reducing resource contention, and simplifying performance tuning.

Keeping Analytics in Sync with Change Data Capture (CDC)

This architecture uses Change Data Capture (CDC) to continuously synchronize transactional changes from PostgreSQL to ClickHouse. By capturing inserts, updates, and deletes directly from PostgreSQL’s Write-Ahead Log (WAL) via logical decoding and replication, CDC avoids periodic ETL jobs, minimizing latency for near real-time operational dashboards and analytics.

Enabling this requires configuring the correct PostgreSQL WAL level and exposing individual tables via publications to stream changes.

ALTER SYSTEM SET wal_level = logical;

CREATE PUBLICATION app_events_pub
FOR TABLE
    orders,
    order_events,
    account_events;

CDC services (such as ClickHouse, ClickPipes, or PeerDB) continuously replicate PostgreSQL changes to ClickHouse, reducing data delays and operational overhead compared to traditional batch ETL.

However, monitoring operational factors like replication slots, WAL retention, schema changes, connectivity, backfills, and replication lag is essential, making latency a critical component of real-time dashboard SLOs.

Operational Readiness Checklist

Pre-production deployment of PostgreSQL alongside ClickHouse requires robust operations: identifying authoritative source tables, validating CDC pipelines, and setting replication lag alerts before dashboards rely on the data.

Schema design must transform normalized PostgreSQL tables into denormalized ClickHouse models, defining clear update/delete semantics for append-optimized engines like ReplacingMergeTree. Independently test backfills and run regular reconciliation jobs to detect sync issues early.

Finally, set clear data freshness objectives and monitor metrics like ingestion throughput, WAL growth, slot utilization, and latency, treating CDC as a production service to ensure a reliable platform.

Pattern 2: Hot / Cold Time-Series Data

In this architecture, PostgreSQL stores only recent operational data for transactional processing, while ClickHouse holds the complete historical record. A continuous CDC pipeline synchronizes changes into ClickHouse, enabling PostgreSQL to safely expire older data after verified replication.

Time-Series Data Lifecycle

Combining PostgreSQL and ClickHouse separates operational data from long-term analytical data. Organizations can store only day-to-day operational records in PostgreSQL, while continuously archiving historical data in ClickHouse via a Change Data Capture (CDC) pipeline.

In a typical deployment, PostgreSQL serves as the system of record for transactional workloads, storing recent data, e.g., the last 30 days, to support low-latency OLTP operations. Simultaneously, committed transactions are streamed to ClickHouse via logical replication and a CDC connector such as Debezium, PeerDB, or native decoding. ClickHouse maintains a complete historical archive optimized for analytical queries without affecting operational database performance.

Pattern 3: Federated Query with pg_clickhouse

Instead of forcing applications and BI tools to communicate directly with ClickHouse, PostgreSQL remains the primary SQL endpoint. The pg_clickhouse extension transparently pushes supported analytical queries to ClickHouse while preserving PostgreSQL compatibility.

Federated Query Execution Flow

Upon reaching PostgreSQL, a query is processed by the parser and query planner. The pg_clickhouse extension then automatically determines if it is a transactional (OLTP) or analytical (OLAP) workload. This routing happens before execution and is completely transparent to the application.

OLTP queries, such as inserting orders or retrieving records by primary key, execute locally within PostgreSQL to maintain ACID guarantees, MVCC concurrency control, and low-latency transaction processing.

Analytical queries, like large aggregations or historical reporting scanning millions of rows, are forwarded to ClickHouse, leveraging its columnar storage engine, vectorized execution, and compression for significantly faster performance.

Pattern 4: Embedded Analytics in SaaS Applications

Customer-facing dashboards require analytics to become part of the production application itself. Every customer request may trigger analytical queries while transactional operations continue independently. This makes CDC freshness, query latency, and workload isolation part of the application’s reliability requirements.

Embedded Analytics Operational Components

While PostgreSQL and ClickHouse serve different workloads, the success of a hybrid analytics platform depends on the operational components that ensure data consistency, low latency, and service reliability. The CDC pipeline is only one part of the architecture and operators must also continuously monitor the health of the entire data flow.

End-to-end observability is critical for production deployments. Monitoring should include PostgreSQL replication health, CDC connector status, ClickHouse ingestion throughput, query performance, storage utilization, and dashboard latency. A centralized monitoring platform enables operators to correlate issues across the entire pipeline, reducing mean time to detection (MTTD) and mean time to recovery (MTTR).

Pattern 5: Hybrid

Many production environments combine three specialized database platforms. PostgreSQL serves as the transactional system of record for business-critical operations like user management, orders, accounts, and billing. Its ACID compliance, MVCC model, and mature ecosystem suit Online Transaction Processing (OLTP) workloads requiring strong consistency.

Meanwhile, TimescaleDB handles operational time-series workloads like application metrics, IoT readings, and telemetry. Hypertables, native compression, continuous aggregates, and automated retention enable efficient storage and querying while maintaining full PostgreSQL compatibility.

For analytical processing, ClickHouse offers a column-oriented database optimized for Online Analytical Processing (OLAP). It runs complex queries across billions of rows for dashboards, BI, and trend analysis. Isolating analytical workloads prevents reports and insights from impacting operational application performance.

Postgres vs. TimescaleDB vs. ClickHouse Decision Tree

To optimize performance, scalability, and operational efficiency, a simple decision process helps determine whether PostgreSQL, TimescaleDB, or ClickHouse is the best fit for a particular use case.

Operating Postgres + ClickHouse in Production

Previously, we explored how PostgreSQL and ClickHouse complement each other through real-time operational analytics, hot/cold storage, and federated queries using pg_clickhouse. However, successfully operating them in production requires understanding Change Data Capture (CDC) behavior under failure conditions, monitoring health, preparing runbooks, and establishing clear ownership across all layers. I’ll look at the operational perspective, examining how to keep the architecture healthy, diagnose common problems, and run a reliable production platform.

Native CDC for Operators

Change Data Capture (CDC) is the foundation of PostgreSQL and ClickHouse synchronization, ensuring ClickHouse operates as a near-real-time analytical platform rather than an outdated copy. For operators, understanding its internal mechanics is essential.

In PostgreSQL, CDC utilizes the Write-Ahead Log (WAL), logical decoding, and logical replication instead of scheduled ETL jobs. PostgreSQL continuously records committed transactions in the WAL, which logical replication decodes into inserts, updates, and deletes. The process starts with an initial snapshot of existing data before transitioning into continuous streaming, keeping ClickHouse perfectly synchronized.

While modern services like ClickPipes and PeerDB simplify deployment over traditional Kafka- or Debezium-based architectures, native CDC still requires operational responsibility. Complexity merely shifts to monitoring, validation, and operational governance.

Crucially, operators must monitor logical replication slots. If a CDC consumer stops, PostgreSQL retains unconsumed WAL files, causing uncontrolled disk growth that can exhaust storage and disrupt the primary database.

A useful operational query for monitoring replication slots is shown below:

SELECT
    slot_name,
    plugin,
    active,
    restart_lsn,
    confirmed_flush_lsn
FROM pg_replication_slots;

This information allows operators to verify whether replication slots remain active and downstream consumers acknowledge changes.

Distinguishing between the initial snapshot and continuous replication is critical. Initial snapshots transfer gigabytes or terabytes of data, where replication lag is expected and should not trigger alerts. Afterward, continuous replication must meet defined freshness objectives, limiting delay to seconds or minutes.

ClickHouse managed CDC services require direct PostgreSQL connectivity and do not support proxy layers like PgBouncer, Amazon RDS Proxy, or Supabase Pooler. This must be considered during network, firewall, and infrastructure deployment.

Operational Failure Modes

The primary challenge in PostgreSQL and ClickHouse architectures lies in system interactions, making an effective troubleshooting strategy vital when incidents span multiple layers.

The most frequent operational issue is CDC (Change Data Capture) lag, which leads to stale dashboards and visible delays for users even if PostgreSQL remains healthy. Operators must monitor a range of metrics, including replication lag, slot status, WAL generation rates, ClickHouse throughput, and end-to-end freshness, rather than just basic database health.

Schema evolution also presents difficulties, as application releases regularly modify PostgreSQL tables. Because ClickHouse schemas are denormalized for analysis, schema updates require precise transformations to avoid pipeline interruptions or data gaps.

Furthermore, updates and deletes require deliberate management. While PostgreSQL modifies rows directly, ClickHouse is built for append-heavy workloads. Managing updates demands strategies like ReplacingMergeTree, version columns, or deduplication, while deletes rely on tombstones, soft-delete flags, or scheduled merges.

Query routing errors frequently impact performance; analytical queries on PostgreSQL exhaust resources, whereas point lookups on ClickHouse introduce latency. Federated query setups add further complexity, as performance hinges on whether execution is successfully pushed to ClickHouse or falls back to PostgreSQL.

Finally, security and governance grow more complex because user accounts, roles, authentication, and auditing differ between the platforms. Replicated analytical data often requires distinct access controls, encryption, and logging compared to the source transactional system.

Hybrid Operations with ClusterControl

As organizations adopt specialized databases like PostgreSQL, ClickHouse, TimescaleDB, Redis, Valkey, MongoDB, MySQL, and MariaDB, operational complexity grows. Rather than managing each technology independently, platform teams require unified tooling to support these heterogeneous environments.

ClusterControl fits this strategy by delivering unified lifecycle management, including deployment, monitoring, backup, recovery, and automation, for multiple open-source databases across on-premises and cloud environments. As PostgreSQL and ClickHouse architectures grow more common, its operational management extends well beyond simple server provisioning.

PostgreSQL management involves deployment automation, high availability, backups, PITR, replication monitoring, and upgrade planning. ClickHouse adds backup strategies, merge monitoring, storage capacity planning, and query optimization. The connecting CDC pipeline also requires production-level monitoring, health checks, and incident response.

Support teams must prepare operational runbooks before production deployment. These should document reference architectures, failure modes, CDC troubleshooting, reconciliation workflows, schema migrations, and team escalation paths.

Recommended Reference Architectures

Although PostgreSQL and ClickHouse can be combined in numerous ways, several architectural patterns have consistently emerged across production deployments.

Architecture A: SaaS Operational Analytics

This architecture positions PostgreSQL as the transactional system of record while ClickHouse powers customer-facing dashboards through near-real-time CDC replication. Optionally, pg_clickhouse provides SQL compatibility for existing applications. This pattern is particularly suitable for SaaS platforms, product analytics, billing systems, and customer usage reporting.

Architecture B: Hot / Cold Time-Series Data

Recent operational data remains inside PostgreSQL while historical records migrate into ClickHouse after successful replication. Data expiration is governed by replication watermarks rather than fixed retention schedules, ensuring historical data remains protected.

Architecture C: Federated Analytics

Applications continue connecting to PostgreSQL while pg_clickhouse pushes analytical execution into ClickHouse. This minimizes migration effort while improving analytical performance.

Architecture D: Hybrid Cloud Analytics

Transactional PostgreSQL clusters remain within customer-controlled environments while ClickHouse operates as a managed analytical platform in the cloud. Secure connectivity is established through VPNs, private networking, or dedicated links.

Production Readiness Checklist

Before deploying PostgreSQL and ClickHouse into production, organizations should validate both technical implementation and operational preparedness. Successful production environments depend as much on operational discipline as on architectural design.

Before Deployment

• Identify authoritative source-of-truth tables.
• Define which datasets require CDC.
• Enable logical replication.
• Design ClickHouse analytical schemas.
• Define update and delete handling.
• Establish naming conventions.
• Validate firewall and network connectivity.
• Review proxy limitations.
• Plan initial snapshots and historical backfills.

During Rollout

• Execute the initial snapshot.
• Enable continuous CDC.
• Compare row counts and business metrics.
• Benchmark representative analytical queries.
• Test failover scenarios.
• Validate schema migrations.
• Verify dashboard freshness.
• Ensure retention policies do not remove data prematurely.

After Go-Live

• Monitor replication lag continuously.
• Observe WAL growth.
• Monitor ClickHouse insert throughput.
• Watch merge activity.
• Reconcile PostgreSQL and ClickHouse data regularly.
• Review schema drift after every application release.
• Maintain operational documentation and escalation procedures.

Conclusion

Modern data platforms use specialized architectures where each database handles specific workloads. PostgreSQL serves as a reliable transactional system of record, while ClickHouse enables high-performance real-time analytics without impacting transactional performance.
Operating this architecture requires managing Change Data Capture, replication health, schema evolution, and data reconciliation. In hybrid database environments, platforms like

ClusterControl provide valuable unified management across multiple database technologies.
Ultimately, the future involves combining both technologies into a cohesive platform. Organizations that invest in both the architecture and its supporting operational processes will ensure long-term scalability and production reliability.

Install ClusterControl and try Postgres and ClickHouse free for 30 days

Script Installation Instructions

The installer script is the simplest way to get ClusterControl up and running. Run it on your chosen host, and it will take care of installing all required packages and dependencies.

Offline environments are supported as well. See the Offline Installation guide for more details.

On the ClusterControl server, run the following commands:

wget https://severalnines.com/downloads/cmon/install-cc
chmod +x install-cc
sudo ./install-cc     # omit sudo if you run as root

After the installation is complete, open a web browser, navigate to https:///, and create the first admin user by entering a username (note that “admin” is reserved) and a password on the welcome page. Once you’re in, you can deploy a new database cluster or import an existing one.

The installer script supports a range of environment variables for advanced setup. You can define them using export or by prefixing the install command.

See the list of supported variables and example use cases to tailor your installation.

The post Postgres + ClickHouse Architectural Patterns appeared first on Severalnines.

Postgres + ClickHouse Architectural Patterns appeared first on MariaDB.org

Continue reading “MariaDB Server 12.3, 11.8, 11.4 and 10.11 – Q3 2026 Maintenance Releases, and Goodbye 10.6”

MariaDB Server 12.3, 11.8, 11.4 and 10.11 – Q3 2026 Maintenance Releases, and Goodbye 10.6 appeared first on MariaDB.org

MariaDB Server 12.3, 11.8, 11.4 and 10.11 – Q3 2026 Maintenance Releases, and Goodbye 10.6 appeared first on MariaDB.org

Continue reading “MariaDB Contribution Statistics, January-June 2026”

MariaDB Contribution Statistics, January-June 2026 appeared first on MariaDB.org

MariaDB Contribution Statistics, January-June 2026 appeared first on MariaDB.org

Source

MariaDB Community Server Q3 2026 maintenance releases appeared first on MariaDB.org

Oracle Exadata cost optimization has two honest paths. The first is to keep Exadata and cut what you pay for it: prune unused options, enable fewer cores, and negotiate before the 19c Extended Support uplift lands. The second, and the one this MinervaDB whitepaper engineers in detail, is a staged data transformation onto a fully open source data infrastructure stack — PostgreSQL 18 for OLTP, ClickHouse 26.3 LTS for analytics, Apache Kafka 4.3 with Debezium 3.6 for change data capture, and Valkey 9.1 for caching — that removes the recurring license and support line entirely while holding performance, scalability, availability, and reliability at Exadata-class SLOs.

The finding, stated up front: on a representative Exadata X11M quarter-rack estate, the annual software support bill alone (licenses at 22% of net) exceeds the five-year hardware plus subscription-support cost of the replacement open source stack, and the performance characteristics that justify Exadata — Smart Scan, Storage Indexes, RAC, Hybrid Columnar Compression — each have a measured open source equivalent when the workload is routed to the right engine rather than forced through one. The rest of this paper shows the arithmetic, the architecture, the code, and the failure modes.

1. Where Oracle Exadata spend actually goes

Most Exadata cost optimization conversations start with the rack. That is the wrong place to look. The rack is a one-time capital line; the recurring line is software licensing and its 22% annual support, and on Exadata that line is amplified by three mechanisms: every enabled database-server core is licensed at the Oracle core factor, the storage tier carries its own per-disk software license, and the features that make Exadata fast (RAC, Partitioning, Advanced Compression, In-Memory) are separately licensed options rather than part of Enterprise Edition.

The list prices below are taken from Oracle’s published Technology Global Price List and Engineered Systems Price List (June/August 2026 editions). Real contracts carry discounts, frequently in the 40–70% range; the ratios between line items are what matter for the model, and the support percentage applies to net, not list.