Continue reading “Extending MariaDB with Native Aggregate Plugins: Laying the Groundwork for HyperLogLog”

Extending MariaDB with Native Aggregate Plugins: Laying the Groundwork for HyperLogLog appeared first on MariaDB.org

Extending MariaDB with Native Aggregate Plugins: Laying the Groundwork for HyperLogLog appeared first on MariaDB.org

Continue reading “MariaDB Foundation Advances TAF with HammerDB 6.0 and xt_reservoir Integration”

MariaDB Foundation Advances TAF with HammerDB 6.0 and xt_reservoir Integration appeared first on MariaDB.org

MariaDB Foundation Advances TAF with HammerDB 6.0 and xt_reservoir Integration appeared first on MariaDB.org

Source

MariaDB Community Server 10.6.28 now available appeared first on MariaDB.org

So we tried it. The first run failed, and it failed in a way that is easy to miss: the replica took every transaction, reported success, and stored nothing. We tracked down why, fixed it, and the whole test suite passes now. This post is what we tested, how we checked it, the bug we found, and where it stands.

It’s still an experiment, not production software. The code and the test harness are on GitHub under GPLv2: https://github.com/Percona-Lab/ducksdb-mysql-engine.

Why replicate into DuckDB

A DuckDB table on one server is already useful. The analytical queries get fast and the application does not change. But almost nobody runs their reports on the primary – they run them on a replica, so the big scans stay out of the way of the OLTP traffic.

So the shape of it is simple. The primary stays InnoDB and takes the writes. The replica has the same tables, only marked ENGINE=DuckDB. Row-based replication ships the changes across, the replica writes them into the column store, and the reports run there. You get an analytics replica out of the replication you already run.

Row events are engine-agnostic on purpose. The primary logs the row changes, not the SQL, and the replica applies them through the storage-engine API. On paper, then, the replica should not care that one side is InnoDB and the other DuckDB. We wanted to see the paper version hold up on a running server.

The setup

Two containers from the same image, one primary and one replica. It’s all in Docker, so it repeats cleanly.

• Primary: InnoDB, binlog_format=ROW, GTID on.
• Replica: same server, GTID on, tables made with ENGINE=DuckDB.
• Replication uses SOURCE_AUTO_POSITION=1.

One thing you have to get right before any data moves. Create the replica tables as ENGINE=DuckDB yourself. A CREATE TABLE … ENGINE=InnoDB on the primary goes into the binlog with the ENGINE word still in it, and the replica runs it exactly as written, so you would end up with an InnoDB table there, not a DuckDB one. There is no automatic mapping. Pre-create the DuckDB tables on the replica, and let the row changes flow into them.

-- primary (InnoDB)
CREATE TABLE t1 (id BIGINT PRIMARY KEY, region INT, amount DECIMAL(12,2)) ENGINE=InnoDB;

-- replica (same columns, DuckDB)
CREATE TABLE t1 (id BIGINT PRIMARY KEY, region INT, amount DECIMAL(12,2)) ENGINE=DuckDB;

The other rule is a primary key on the replica table. UPDATE and DELETE row events find the row by its old image, and the engine needs the key for that. INSERT works without one, but put a key on it anyway.

One script drives all of this: bench/tb/07-replication-spike.sh. It starts both containers, wires up replication, runs every scenario below, and prints PASS or FAIL for each.

What we tested, and how

The part that matters is the checking. Row counts are not enough – the replica can hold the right number of rows and still have the wrong data in them. So after each step the script dumps the whole table on both sides, ordered by primary key, and compares an md5 of the two dumps. One byte off is a FAIL. And rather than sleep between steps, it waits on WAIT_FOR_EXECUTED_GTID_SET(), so the checks do not race the replica.

Here is what went through it.

Basic DML. Insert, update a row, delete a row, compared after each one.

All the column types, in a single wide table: signed and unsigned integers, DECIMAL, DOUBLE, DATE, DATETIME, TIMESTAMP, CHAR, VARCHAR, TEXT, BLOB, a few NULLs, and a unicode string. Insert it, update it, compare byte for byte. Blobs get their own note below.

DDL. ALTER TABLE ADD COLUMN, ALTER TABLE ADD INDEX, and DROP TABLE against a DuckDB replica table. These arrive as statements. We check that the column shows up, the index shows up, the old rows survive, and the drop removes the table.

Transactions. A transaction with two inserts and an update has to land on the replica as one unit. A transaction the primary rolls back has to leave nothing behind. We also open a transaction straight on the replica and both roll it back and commit it, to check the engine’s own commit and rollback.

Bulk load. 5000 rows through LOAD DATA on the primary, has to arrive and match.

Durability. Two cases, and the second is the hard one.

• Clean restart. Stop the replica properly, write on the primary while it is down, start it again, and see it pick up from its GTID position.

• Crash. Apply some rows, then SIGKILL the replica. No clean shutdown, no checkpoint. Bring it back, write more on the primary, and check one exact thing: every row present once. Nothing lost – DuckDB has to replay its write-ahead log when it opens the file – and nothing applied twice, which means the saved position has to line up with the data that actually reached disk.

The bug: multi-engine transactions lost data

The first full run fell down on the wide-table test. Zero rows on the replica, and then everything after it failed too. The applier had stopped with HA_ERR_KEY_NOT_FOUND. It went to UPDATE a row that was not there, because the INSERT before it had returned success and written nothing.

When a scenario fails, the harness saves the applier error, both server logs, and both schemas. The replica log had the line that mattered:

[Warning] Combining the storage engines InnoDB and DuckDB is deprecated, but the
statement or transaction updates both the InnoDB table mysql.slave_worker_info and the
DuckDB table rpl.wide.

That line is the whole thing. A replica does not only write your data. In the same transaction it also writes its own position into InnoDB system tables – mysql.slave_worker_info, the relay-log info, gtid_executed. So every applied transaction touches two engines at once: InnoDB for the position, DuckDB for the data. Two engines means MySQL runs a real two-phase commit: prepare, then commit.

Our prepare was wrong. It took the open DuckDB transaction, moved it into a registry meant for external XA COMMIT, and cleared the per-connection state. Then commit looked at that state, found it empty, and committed nothing. The position went into InnoDB, the GTID advanced, the binlog moved on, and the DuckDB rows were thrown away. No error anywhere. The replica looked healthy while it dropped every write.

We cut it down to the smallest case, with no replication at all. One server, one transaction into a DuckDB table and an InnoDB table:

BEGIN;
INSERT INTO duck VALUES (1,10),(2,20),(3,30);   -- DuckDB
INSERT INTO inno VALUES (1,10),(2,20),(3,30);   -- InnoDB
COMMIT;
-- duck: 0 rows   inno: 3 rows

InnoDB kept its three rows, DuckDB kept none, and COMMIT said it was fine. A DuckDB-only transaction was fine as well, because with one engine MySQL skips the prepare step. It only broke with a second engine in the transaction. And on a replica, that is every transaction.

The fix

Small change, in the engine’s transaction code. prepare now remembers which prepared transaction belongs to the connection, and commit finishes that one instead of an empty state. External XA is untouched. It went out as v0.2.3.

With that in place the reproducer keeps three rows in both tables, and the full run comes back clean, crash test included:

[8]  data integrity: all column types, NULL / unicode / negatives ....... PASS
[9]  DDL replication (ALTER ADD COLUMN / ADD INDEX / DROP) .............. PASS
[10] transactions (atomic commit, rollback, engine commit/rollback) ..... PASS
[11] bulk LOAD DATA on master -> replica ................................ PASS
[12] durability: graceful restart, then SIGKILL crash recovery .......... PASS

VERDICT: PASS=24  FAIL=0

The crash case is the important one. After a SIGKILL in the middle of applying, the replica came back with every committed row exactly once, matching the primary. Committed transactions survive the kill, and the position stays in step with them.

We left two tests behind so this cannot slip back in quietly: an MTR test, txn_mixed_engine, that runs a mixed DuckDB+InnoDB transaction on every build, and scripts/repro-2pc-dataloss.sh, which you can point at any published image to check it.

What works, and what doesn’t yet

Where it stands on v0.2.3, for an InnoDB primary feeding a DuckDB replica:

The things to keep in mind:

• Create the replica tables as ENGINE=DuckDB yourself. A replicated CREATE TABLE keeps the primary’s engine, so it will not turn into DuckDB on its own.

• Replica tables need a primary key for UPDATE and DELETE.

• The applier goes row by row. That is fine for a normal OLTP change stream. It is not fine for keeping up with a primary that bulk-loads at full speed – the replica will fall behind.

• Committed transactions are crash-safe, with one small gap. The engine holds a prepared-but-not-committed transaction in memory only, so a crash in the short window between prepare and commit can lose that single transaction. The applier commits right away, so the window is small, but it is not zero.

• Blobs behave differently over replication than through a direct statement. A plain UPDATE of a BLOB or TEXT column has a known limit in the engine and does not apply. Over replication it does apply, because the row event carries a full before-and-after image instead of the shared buffer the direct path uses.

And the obvious one. This is an experiment. It is a functional result from a test harness on small data, not an HA or failover benchmark. We did not test multi-source replication, filters, or a real write rate.

Where it stands

An InnoDB primary feeding a DuckDB replica works on v0.2.3. Inserts, updates, deletes, every common type, schema changes, transactions, bulk load – they all replicate and match, and it comes back clean from both a graceful restart and a hard kill. The one real bug, silent data loss on every replicated transaction, is found, understood, fixed, and covered by tests.

It is not production-ready, and we do not treat it as such. But the idea holds up. Point normal MySQL replication at a DuckDB replica, and you get an analytics copy that keeps itself in sync.

The post Replicating from InnoDB into a DuckDB storage engine appeared first on Percona.

Replicating from InnoDB into a DuckDB storage engine appeared first on MariaDB.org

Continue reading “MariaDB 13.1 Feature in Focus: JSON Operators and JSON_TABLE Improvements”

MariaDB 13.1 Feature in Focus: JSON Operators and JSON_TABLE Improvements appeared first on MariaDB.org

MariaDB 13.1 Feature in Focus: JSON Operators and JSON_TABLE Improvements appeared first on MariaDB.org

This is an important step for anyone planning infrastructure refreshes around the latest Linux releases, and it’s especially notable for teams evaluating ARM to cut infrastructure spend without sacrificing performance.

What’s new in 8.0.28-12

Starting with this release, Percona Server for MongoDB packages are available for:

• RHEL 10 and derivatives like Oracle Linux 10, Rocky Linux 10, AlmaLinux 10 on x86_64 and ARM (aarch64)
• Debian 13 “Trixie” on x86_64 and ARM64 (aarch64)

Additionally, starting this release, we’ve included Software Bills of Materials (SBOMs) and Vulnerability Exploitability Exchange (VEX) for every release. SBOMs improve software supply chain transparency by documenting the components and dependencies included in a build. They are generated automatically as part of the release pipeline in the industry-standard CycloneDX format. OpenVEX files are published on GitHub Pages and provide the exploitability status of known vulnerabilities. For comprehensive information, refer to our [documentation](../sbom.md).

To learn more, see the full release notes of Percona Server for MongoDB 8.0.28-12.

Ahead of upstream on Debian 13

As of this writing (August 2026), upstream MongoDB Community/Enterprise Server does not yet officially package or support Debian 13. Trixie isn’t in MongoDB’s supported platforms list, and the documented community workaround is to install the Debian 12 “Bookworm” build on Trixie hosts, since a native Trixie server build hasn’t landed yet. PSMDB closes that gap now, with native Debian 13 packages rather than a buggy Bookworm build running out-of-distro.

Why ARM is worth a serious look for MongoDB workloads

We have heard multiple times from you directly, via our forum, or on Reddit about the Interest in ARM for database workloads. Over the last few years, adoption has moved well past the experimental phase to resilient production readiness. Our adoption telemetry data show nearly 3x as many ARM instances over the last 12 months!

I can see a number of benefits and reasons why our community users and customers adopted ARM over AMD or Intel CPU architectures:

• Lower infrastructure costs. Cloud ARM instances, such as AWS Graviton, are commonly cited as running 20–40% cheaper than comparable x86 instances at similar or better performance (sanj.dev, “ARM vs x86 Cloud: Which Architecture is Cheaper in 2025?”), and benchmarking write-ups report up to 60% lower energy consumption for compute-intensive workloads on Graviton compared to x86 equivalents (nOps, “Are you missing out on AWS Graviton Cost Savings?”).
• Competitive, and often better, throughput. RDS PostgreSQL Graviton” benchmark analysis shows Graviton4 delivering up to 40% better performance for OLTP-style workloads versus the previous Graviton3 generation. Apache Doris Delivers 70% Better Value on AWS Graviton. I can see MongoDB database achieving a similar level of performance-to-cost gain. 
• Memory bandwidth is a real differentiator. Graviton3’s memory bandwidth (cited at roughly 115–120 GB/s) is reported to significantly outpace typical Intel Xeon configurations (roughly 60–70 GB/s) and AMD EPYC (roughly 80–90 GB/s) in independent comparisons (byteiota, “ARM vs x86 Cloud: 2025 Performance & Cost Benchmark”), which matters for memory-hungry workloads like MongoDB’s WiredTiger cache and in-memory working sets.

(Note: The above figures come from third-party blogs and vendor case studies rather than peer-reviewed benchmarks. Treat them as directional evidence that ARM is worth evaluating, not a guarantee of results for your specific workload. For the official recommendation based on your workload, reach out to Percona)

Netflix has publicly stated that it saves over $15 million annually after migrating video encoding workloads to Graviton, while also seeing faster processing times, and other large-scale AWS customers have reported double-digit percentage reductions in compute costs after moving meaningful portions of their backend fleets to ARM (byteiota; sanj.dev).

What to watch out for

One RHEL 10 detail to keep in mind when planning a migration: Red Hat raised the CPU baseline for x86_64 to the x86-64-v3 microarchitecture level, meaning the processor needs to support instruction sets such as AVX2 (Red Hat, RHEL 10 architecture documentation; vInfrastructure Blog). On the ARM side, RHEL 10 targets the ARMv8.0-A baseline (Red Hat documentation). This applies equally to Oracle Linux 10, Rocky Linux 10, and AlmaLinux 10, since they build from the same upstream sources. I highly recommend checking your current infrastructure before planning the move, especially for older bare-metal fleets.

In general, but especially on ARM, remember that performance is workload-dependent. Some code paths and workloads leaning on x64-specific instruction extensions may not see the same gains as throughput-oriented, multi-threaded workloads on ARM. Benchmarking your own query patterns and index-heavy operations before a full cutover is essential. General benchmarks are a good signal, not a guarantee.

Percona can help you get there

Migrating a production MongoDB deployment to a different infrastructure is a project with real decision points. There are a number of questions to answer around: Hardware or instance selection, driver and tooling compatibility, benchmarking against your actual workload, and a rollback plan. The Percona Services team helps customers plan and execute exactly this kind of migration. We start from initial architecture assessment and proof-of-concept benchmarking and go through to production cutover and post-migration tuning.

If you’re weighing a move to ARM, or just want to get onto RHEL 10 or Debian 13 without surprises, reach out to Percona to talk through your environment.

The post Percona for MongoDB: RHEL 10, Its Derivatives, and Debian 13 – On Both x86_64 and ARM appeared first on Percona.

Percona for MongoDB: RHEL 10, Its Derivatives, and Debian 13 – On Both x86_64 and ARM appeared first on MariaDB.org

Since I’m not quite up to speed on this topic yet, I like to look up certain terms (parameters, functions, etc.) every now and then to see exactly what they mean or how they work (RTFM!). That’s exactly why links were originally invented—the very thing that first made Gopher and later the Internet/WWW (http) so popular.

Unfortunately, however, these links are often missing from the documentation in question, which disrupts the flow of reading.

Fortunately, though, PostgreSQL is an open-source project, and contributions are highly encouraged! So instead of just grumbling about the documentation, I could add the missing links myself. But how exactly do I go about doing that in an ecosystem that’s new to me and therefore still a bit unfamiliar? An article by Elizabeth Christensen from Crunchy Data titled Contributing to Postgres 101: A Beginner’s Experience helped me get started.

Since I have absolutely no programming experience myself, I see improving the documentation as a great opportunity to actively contribute to the project and help out…

Improving the PostgreSQL Documentation

The PostgreSQL documentation is stored directly in the server repository. So, first, let’s download the Git repository from the PostgreSQL server:

$ git clone http://git.postgresql.org/git/postgresql.git

The next challenge is finding the right file:

$ cd postgresql/doc/src/sgml

The grep command, in all its forms, comes in handy here:

$ grep -r 'Planning for High Availability' *.sgml
high-availability.sgml: <title>Planning for High Availability</title>

The correct document appears to be high-availability.sgml. The PostgreSQL documentation itself is written in SGML, which is similar to HTML and not particularly difficult to learn.

These SGML files can be easily read and edited using your editor of choice with appropriate code highlighting.

Next, we need to check the individual keywords to see if they’ve already been correctly marked up and, if so, add links to them:

Note: Keep in mind that keywords are written with an “_” (underscore) and links with a “-” (hyphen).

While building the documentation, it was also noticed that some link targets (id) hadn’t been set at all, so these had to be adjusted as well:

 <row>
- <entry role="func_table_entry"><para role="func_signature">
+ <entry id="pg-backup-start" role="func_table_entry"><para role="func_signature">
 <indexterm>
 <primary>pg_backup_start</primary>

Quality Assurance

Once all changes have been made, it’s time for quality control. To do this, build the documentation locally:

$ cd postgresql
$ ./configure
$ cd doc
$ make

Exact details on how this works are described here.

If the build finds any errors, they will be displayed and the build will be aborted. If everything runs smoothly, you can now use your browser of choice to check whether everything actually works as intended:

$ firefox src/sgml/html/warm-standby.html

Something else I discovered later:

Building the documentation can take very long. But there is a method to just check the correct syntax of the documentation files, which only takes a few seconds: [ 5 ]

$ make check
make -C ../src/backend generated-headers
make[1]: Entering directory '/home/oli/fromdual/postgresql/docu/postgresql/src/backend'
make -C ../include/catalog generated-headers
make[2]: Entering directory '/home/oli/fromdual/postgresql/docu/postgresql/src/include/catalog'
make[2]: Nothing to be done for 'generated-headers'.
make[2]: Leaving directory '/home/oli/fromdual/postgresql/docu/postgresql/src/include/catalog'
make -C nodes generated-header-symlinks
make[2]: Entering directory '/home/oli/fromdual/postgresql/docu/postgresql/src/backend/nodes'
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/home/oli/fromdual/postgresql/docu/postgresql/src/backend/nodes'
make -C utils generated-header-symlinks
make[2]: Entering directory '/home/oli/fromdual/postgresql/docu/postgresql/src/backend/utils'
make -C adt jsonpath_gram.h
make[3]: Entering directory '/home/oli/fromdual/postgresql/docu/postgresql/src/backend/utils/adt'
make[3]: 'jsonpath_gram.h' is up to date.
make[3]: Leaving directory '/home/oli/fromdual/postgresql/docu/postgresql/src/backend/utils/adt'
make[2]: Leaving directory '/home/oli/fromdual/postgresql/docu/postgresql/src/backend/utils'
make[1]: Leaving directory '/home/oli/fromdual/postgresql/docu/postgresql/src/backend'
rm -rf '/home/oli/fromdual/postgresql/docu/postgresql'/tmp_install
/usr/bin/mkdir -p '/home/oli/fromdual/postgresql/docu/postgresql'/tmp_install/log
make -C '..' DESTDIR='/home/oli/fromdual/postgresql/docu/postgresql'/tmp_install install >'/home/oli/fromdual/postgresql/docu/postgresql'/tmp_install/log/install.log 2>&1
make -j1 checkprep >>'/home/oli/fromdual/postgresql/docu/postgresql'/tmp_install/log/install.log 2>&1
PATH="/home/oli/fromdual/postgresql/docu/postgresql/tmp_install/usr/local/pgsql/bin:/home/oli/fromdual/postgresql/docu/postgresql/doc:$PATH" LD_LIBRARY_PATH="/home/oli/fromdual/postgresql/docu/postgresql/tmp_install/usr/local/pgsql/lib:$LD_LIBRARY_PATH" INITDB_TEMPLATE='/home/oli/fromdual/postgresql/docu/postgresql'/tmp_install/initdb-template initdb --auth trust --no-sync --no-instructions --lc-messages=C --no-clean '/home/oli/fromdual/postgresql/docu/postgresql'/tmp_install/initdb-template >>'/home/oli/fromdual/postgresql/docu/postgresql'/tmp_install/log/initdb-template.log 2>&1

Submitting the Patch

If everything works as intended and to your satisfaction, you can then proceed to create the patch and submit it:

$ git commit -m 'some references on variables and functions added'
$ git format-patch -1 HEAD

This creates a file containing the commit comment: 0001-some-references-on-variables-and-functions-added.patch.

Apparently, in the PostgreSQL project, you don’t create a merge request to incorporate the patch back into the source code; instead, the patch must be sent to the appropriate mailing list and then merged into the main branch by a developer with merge/commit privileges. I’ve now agreed with “my” committer that we’ll hold the discussion about my patch on the pgsql-docs mailing list.

Let’s see how things go from here and how far I get with my patch…

This page was translated using deepl.com.

Making the PostgreSQL Documentation Even Better appeared first on MariaDB.org

Continue reading “MariaDB Foundation is pleased to welcome Auree as a Silver Sponsor.”

MariaDB Foundation is pleased to welcome Auree as a Silver Sponsor. appeared first on MariaDB.org

MariaDB Foundation is pleased to welcome Auree as a Silver Sponsor. appeared first on MariaDB.org

MySQL and MariaDB High Availability vs. Disaster Recovery: What’s the Difference (and Why It Matters) appeared first on MariaDB.org

Source

MariaDB Node.js Connector 3.5.4 and 3.4.7 now available appeared first on MariaDB.org

Percona Search for MongoDB runs as a separate mongot process alongside Percona Server for MongoDB. The deployment topology determines how many mongot instances are required and how search requests are routed. Applications and users continue to connect to mongod in a replica set, or to mongos in a sharded cluster – never directly to mongot.

On behalf of the entire product and engineering team for MongoDB at Percona, I’m pleased to share that we’re starting a Technical Preview with version 1.70.3-1.

The way is open. Search should be too.

Before anything else, credit where it is due. MongoDB Inc. released full-text and vector search for self-managed deployments as GA in July 2026, and published the source for mongot – the same search engine that powers MongoDB Atlas Search. Opening up the engine behind a flagship commercial service is a significant step and precisely what makes this Technical Preview possible. 

What we want to add is the next layer of openness: freedom to choose your embedding model, to run inference where your data already lives, and to operate search with the same automated backup, monitoring, and Kubernetes tooling you already expect from every other tier of your database. That is the Percona way, and this post is our map for getting there.

What we found in mongot

We went through the current release, reviewing everything needed to run it the way you want in production. Below is what we found, stated as plainly as we can, with the Percona plan attached to each item. None of these is a defect. They describe where today’s release draws the line between the search engine and the operational layer around it. The operational layer is exactly where Percona has always done its work.

Automatic embeddings and model choice 

This is the big one, and it needs a little setup to see properly.

Vector search doesn’t search text. It searches vectors. If a user wants to find a document, they need to type a query that is first run through an embedding model and turned into an array of numbers. That conversion is not a one-time import step, either – it has to keep pace with your data, because a document whose text changed while its vector didn’t is now quietly unfindable. No errors are raised. Query results simply get worse over time.

There are two ways to handle it.

Manually

You generate embeddings yourself and write the vectors into the document. This path is completely open, with no restrictions. It is also where a lot of vector search projects stall, because you have just taken ownership of an embedding pipeline: something has to watch inserts and updates, batch them, call a model, handle failures and retries, backfill the whole corpus when you change models, and guarantee every vector still matches the text next to it. That is a distributed-systems problem bolted onto a database that already solved distributed-systems problems. For a fixed corpus, you index once and forget – it is fine. For live operational data, it becomes a permanent tax on the team. In my humble opinion, it isn’t the way to go for a production deployment at scale.

Automatically

You declare which field holds your text and which model to use – the autoEmbed type in the index definition – and the database generates the embeddings, keeps them in sync as the data changes, and accepts plain text at query time. This exists precisely because the manual path does not scale. It is the path the documentation leads with, the path every tutorial will use, and for most teams running search on data that changes, it is the only realistically maintainable option.

Today, automatic embeddings are supported only with Voyage AI models: voyage-4-large, voyage-4, voyage-4-lite, and voyage-code-3. Three practical consequences follow from that.

• Your data travels to a third-party service. Every document you index and every query your users type are sent to Voyage AI’s cloud for processing. The support tickets, the patient notes, the contracts, the internal wiki – whatever you actually store – are handled outside your perimeter, from a database you self-host on hardware you own. Voyage AI does offer an on-premises deployment, which comes with its own licensing and costs. Without it, an air-gapped deployment cannot use automatic embeddings, and neither can teams working under data-residency obligations, which covers most of regulated Europe.
• It’s metered. There is a free tier – 200 million tokens to get you started, less for specialized models – but it is capped on both volume and velocity, with requests and tokens per minute throttled. Beyond that, it runs roughly $0.02 to $0.12 per million tokens, on every reindex and every query your application serves.
• The model is chosen for you. Not the one that performs best in your language. Not the domain model your data science team fine-tuned. Not a smaller open-weights model that is good enough for your use case.

The Percona plan

We want automatic embeddings to be open, so you have a genuinely unlimited choice of models suited to your needs. We will start with everything that speaks to the OpenAI-compatible embeddings API, which already covers a large and growing ecosystem:

• Ollama – local, free, 100+ open models including nomic-embed-text, mxbai-embed-large, and all-minilm
• vLLM – self-hosted GPU inference
• llama.cpp server – local CPU or GPU inference
• LocalAI and LM Studio
• Hugging Face Text Embeddings Inference (TEI)

Over time, we intend to widen that further, toward the 25,000-model catalog the open ecosystem has already built. Cloud providers remain available to teams that prefer them. They just stop being the only option.

Reranking

Reranking is the second half of how serious retrieval works. Vector search is fast because the query and the documents are embedded separately and never actually compared – the model sees your query, sees a document, and never sees them side by side. That approximation is what makes it possible to search millions of documents in milliseconds, and it is also why the top result is often merely in the right neighborhood rather than right. A reranker fixes that: it takes the top 50 or 100 candidates and runs each through a model that reads the query and the document together, scoring genuine relevance rather than vector proximity. In practice, this is usually the single largest accuracy improvement available in a retrieval pipeline, and it matters most for RAG, where the language model only ever sees the top handful of results. If the passage that answers the question is sitting at rank eight, your application behaves as though the answer does not exist.

The $rerank stage is available only on MongoDB Atlas.

The Percona plan

We intend to open reranking as well. Our initial target is BAAI/bge-reranker-large, a strong cross-encoder text-ranking model from the Beijing Academy of Artificial Intelligence, published on Hugging Face under the permissive MIT license.

Contextual chunking and multimodal pipelines

Contextual chunking matters because embedding models have fixed context windows, so anything longer than a few paragraphs has to be split before it can be indexed. Split it naively on a character count, and you shred the meaning: a clause reading “this must be renewed within 30 days” is worthless when “this” was defined two chunks earlier. Contextual and late-chunking techniques embed each chunk with awareness of the surrounding document, so the retrieved passage still makes sense on its own. This is the difference between a RAG system that cites something useful and one that confidently quotes a fragment.

Multimodal pipelines embed text and images into a single vector space, so a search for “worn leather armchair, mid-century” can match a photograph with no caption. Product catalogs, media archives, scanned paperwork, engineering diagrams – anywhere the information lives in the picture rather than the metadata.

The Percona plan

For both of these, the path available today is a Voyage cloud API, called and paid for per token, with your content leaving your network. Meanwhile, the open-weights ecosystem offers excellent cross-encoder rerankers such as BGE-M3 from BAAI, and CLIP- and SigLIP-class multimodal encoders that run comfortably on a single GPU, or on CPU if you are patient. None of them is wired in yet. We would like to change that.

Search-index backup, restore, and recovery

This is documented rather than absent, and it is worth reading closely to understand what it asks of you.

mongot is not your primary data store, so a lost index can always be rebuilt from mongod. The docs note the trade-off in the same breath: index builds “can be slow and in some cases can take days to complete.” For anything with a recovery-time objective, days of degraded search after a disk failure need a faster answer.

That faster answer is a filesystem snapshot, and here is the whole procedure. Stop mongot, then snapshot its data directory with the tool of your choice – the docs provide a working LVM example. To restore, put the directory back, generate a fresh server identity, and restart. mongot then resumes replication from mongod and catches up.

It works. It is also entirely yours to build, and there are a few properties worth planning around:

• No orchestration or scheduling, and no coordination with your database backup – so no consistent point-in-time across mongod and mongot.
• No object storage integration.
• The search process is stopped while the copy is taken.
• The snapshot has a shelf life. A mongot backup is valid only for as long as the change stream can carry it forward, so a snapshot older than your oplog retention window is detected as having fallen off the oplog and triggers the full rebuild you took the snapshot to avoid.
• On Kubernetes, MongoDB Controllers for Kubernetes does not back up or restore mongot volumes, and the docs recommend planning this with your storage platform.

The Percona plan

Automated, scheduled, verified backups are a problem the open-source community solved for databases a long time ago, and search indexes deserve the same treatment. Percona Backup for MongoDB and Percona Operator for MongoDB are a natural fit: PBM to orchestrate search-index snapshots alongside the database backup it already handles, with fast index initialization from object storage – S3, Azure, GCS, MinIO – instead of a full change-stream replay. The goal is for search-index recovery to be something you configure once, rather than script.

Observability

There is a /metrics endpoint that exposes a great deal. What isn’t there yet is anything built on top of it. Atlas has a Search Metrics UI; for self-managed deployments, dashboards are, in the documentation’s own phrasing, “not provided in a UI component.” Alerting is yours to define, as are log retention and diagnostic-data rotation.

To be concrete about what “yours to define” involves: the upstream docs publish a genuinely thoughtful set of seventeen recommended alerts across three severity tiers, each with example PromQL to adapt to your environment and thresholds to tune to your workload. The recommended approach is to implement the paging tier first, run it for a week, tune out false positives, then add the other two. That is good advice. It is also multi-week work, repeated for every deployment, before you have the monitoring that a hosted service provides on day one.

Some of the behaviors worth alerting on are genuinely subtle. mongot enforces three disk thresholds internally, and the docs note they take effect whether or not you are monitoring:

1. Level 1: at 85% full, new index builds remain in PENDING. 
2. Level 2 – at 90%, steady-state replication is disabled – existing indexes stop receiving change events, and search begins serving stale results while the database itself reports healthy.
3. Level 3: at 95%, the process stops and requires disk space to be freed before it restarts cleanly. The middle threshold is the one worth wiring up carefully, because it doesn’t announce itself.

The Percona plan

Percona Monitoring and Management is where this belongs. We believe that collecting these metrics, presenting them on turnkey dashboards, and shipping alert rules with sensible thresholds is exactly the kind of work that should be done once and shared, rather than rebuilt by every team. Sync lag, heap and JVM health, index build progress, executor queue depth, disk headroom – including an alert for the case above, so you learn that replication stopped before your users do.

About the license

mongot is published under the Server Side Public License, and so is our distribution. You can read every line of it on GitHub, and we add no restrictions of our own on top.

SSPL is source-available rather than OSI-approved open source, and we would rather say so than blur the term. What we can commit to is the part we control:

• Capabilities stay yours.
• Self-hostable.
• Air-gappable.
• No metered API on the critical path.
• Software remains open and free.

Choice of automation, choice of model, choice of where the inference happens. That is the freedom we are working toward.

Getting started

Percona Search for MongoDB requires Percona Server for MongoDB 8.3, which we shipped as a Technical Preview last week – the first Percona release carrying the $search, $searchMeta, $vectorSearch, $rankFusion and $scoreFusion stages that the search process plugs into.

• Packages: grab Percona Search for MongoDB 1.70.3-1 from percona.com/downloads.
• Installation and configuration: see the Percona Search for MongoDB documentation.
• On Kubernetes: search is supported in Percona Operator for MongoDB 1.23.0, released last week. Enable spec.search – the enabled flag, a mongot image, size: 1, and a sized PVC – and the Operator deploys the search process, wires up its authentication and internal TLS, and keeps the index in sync for both replica sets and sharded clusters, one mongot per shard. Details in the 1.23.0 announcement.

Before you deploy it

This is a Technical Preview. Please don’t run it in production yet.

Specifically, this version may not fully work with the rest of the Percona software for MongoDB:

• Percona Backup for MongoDB (PBM) doesn’t yet cover search indexes.
• Percona Operator for MongoDB search support, recently released in 1.23.0, is currently in tech preview and limited to 1 search node. More automation is coming in the next version.
• Percona Monitoring and Management doesn’t have search dashboards yet, but they’re coming!

Point it at a copy of your data. Then share with us where it breaks for you.

Tell us what you need

Everything above is a position, which means it can be wrong. If we’ve missed a limitation, picked the wrong first target, or left out the embedding provider you actually use, we would like to hear it:

• Forum: forums.percona.com
• Source: github.com/percona/percona-mongot

Search and AI on your own data, on your own hardware, with the model you chose. That is what we are building, and that is what we mean by openness.

If you’re not using Percona for MongoDB yet but you’re interested in Percona Search for MongoDB, you might like to read how Sailthru by Zeta cut more than $1 million a year by migrating to Percona Server for MongoDB.

The way is open.

Disclaimer: Roadmap items are intentions, not delivery commitments. Scope and sequencing may change, and the fastest way to change them is to tell us what you need.

The post The open way of Percona Search for MongoDB appeared first on Percona.

The open way of Percona Search for MongoDB appeared first on MariaDB.org

Here is what came out. InnoDB finished 18 of the 22 queries and spent more than 28 hours of query time on them. Four never finished. Our engine ran all 22 in about three minutes. It loaded the data 25 times faster than InnoDB, and it used 5 times less disk. On the queries it stays close to plain DuckDB, and on a few it is ahead.

It’s still an experiment, not production software. Code and the benchmark harness are on GitHub under GPLv2: https://github.com/Percona-Lab/ducksdb-mysql-engine.

The machine, and how we ran it

• One server, 80 cores, 187.5 GB RAM.
• SF500: about 500 GB of raw CSV, 3,000,028,242 lineitem rows.
• Three engines, one at a time: InnoDB, our engine, native DuckDB.
• All of it through the harness in the repo (bench/tb), in Docker.

Two details about how we ran it change how the numbers read.

The load streams. We generate a chunk of CSV, load it, delete it, then generate the next one. So the disk never holds more than one 20 GB chunk, which is the only reason 500 GB fits on the box at all.

And “native DuckDB” is not a second copy of the data. It opens the engine’s own DuckDB file read-only and queries that. Same bytes on both sides. That keeps the comparison honest, and it means there is no separate native load time to report.

Loading the data

InnoDB took 25.5 times longer. The engine hands LOAD DATA straight to a DuckDB COPY instead of going row by row through the handler, so the three billion lineitem rows go in in about nineteen minutes, and the whole set in thirty-six. InnoDB inserts row by row and builds the primary key as it goes. That is where the rest of the fifteen hours goes.

Storage on disk

DuckDB stores columns and compresses them, so 500 GB of CSV comes down to 132 GB. InnoDB stores rows and carries the index with them, and it ends up bigger than the CSV it came from: 673 GB, five times the DuckDB file. The InnoDB lineitem.ibd on its own is 446 GB. That is more than three times our entire database.

Storage, lower is better. The DuckDB engine holds all of SF500 in 132 GB.

Query time

All 22 queries. Warm runs, minimum of a few, in seconds. InnoDB had a two-hour cap per query; the ones that hit it are marked DNF.

SF500, all 22 queries, log scale, lower is better. Hatched InnoDB bars did not finish inside the cap.

Two things to take from this.

InnoDB is far behind, which is no surprise. Scanning three billion rows for a wide GROUP BY or a six-way join is the wrong job for a row store. Four queries (Q9, Q13, Q17, Q20) did not finish at all, and the eighteen that did add up to more than 28 hours. This is the exact problem the engine is for. It is not a mark against InnoDB, which is doing the transactional job it was built for.

The comparison worth reading is our engine against plain DuckDB, since both are the same DuckDB reading the same file. Over all 22 they are close: 186 seconds for ours, 153 for native. Query by query it goes both ways. On the selective ones ours is often faster — Q6 (1.3 vs 4.1), Q19 (2.4 vs 8.6), Q17, Q20. On the biggest joins native wins — Q18 (27 vs 12), Q21, Q1. That gap comes from settings, not data: the memory limit, the thread count, and running inside mysqld versus a bare CLI. Either way, both are around a thousand times faster than the row store.

Correctness

We checked the answers, not only the clock. For every query we compared our engine’s output to native DuckDB’s, numbers rounded to four decimals and the order ignored. 21 of 22 matched exactly. None mismatched. One was skipped because a result file came back empty on one side. So the engine gives the same answers as plain DuckDB.

What this means, and where it stops

At 500 GB the small-scale picture holds and gets sharper. Analytical queries that took hours on InnoDB, or never finished, come back in seconds on the DuckDB engine. The load is far quicker, and the footprint is far smaller. All of it inside one MySQL server, with the tables queried the normal way.

The limits are the same as before:

• It is for analytics, not OLTP. Point lookups and single-row work stay on the row path, where an index seek is the right tool.
• DuckDB runs inside mysqld, so a heavy query under a tight memory limit can go over budget. DUCKSDB_MEMORY_LIMIT and DUCKSDB_TEMP_DIR let it spill to disk instead of failing. We set a limit here so the big CTEs spill rather than get OOM-killed.
• Some queries still fall back to normal MySQL and run on the row path.
• It is one workload on one machine. The result is strong, but the engine is still an experiment, not something for production traffic.

Try it

Pull the image and run your own queries:

docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=secret
  perconalab/ducksdb-mysql-engine:latest

The engine, the patches, and the harness that produced these numbers are on GitHub: https://github.com/Percona-Lab/ducksdb-mysql-engine. The per-query numbers and the method are in the repo. If it breaks, or your hardware gives different numbers, open an issue.

The post The DuckDB MySQL engine at 500 GB appeared first on Percona.

The DuckDB MySQL engine at 500 GB appeared first on MariaDB.org

Ours just happened to be a mountain goat.

Fresh off a brand-new Percona branding reveal — complete with a rugged mountain goat mascot, a custom-made goat cake, and even a (surprisingly inspirational) LEGO mountain goat — we set out to bring our global Software Engineering team together in Turkey.

And while planning an offsite for a fully remote, globally distributed team sounds simple enough… just wait until you try to do it.

We’re talking about 100 people, 30+ countries, coordinating over 200,000 miles of travel, multiple time zones, passports, dietary restrictions, flight delays, and the occasional “wait, what do you mean my passport expires in three months?” (you know who you are) moment. Suddenly, you’re not just planning a trip — you’re orchestrating a small traveling universe.

Welcome to the step-by-step story of how we brought a global Percona team together in Turkey, and what it really takes to make something like this happen.

Step One: Accept That Details Are Your New Personality

When planning an offsite of this scale, you quickly learn one thing: there is no such thing as being “too detailed.”

There is also no such thing as too much information, or having too much collaboration, when it comes to working through the countless moving pieces.

Success at an event like this doesn’t come from starting from scratch. It comes from leaning heavily on the people who have done it before. Their experiences, lessons learned, and even their past mistakes are incredibly valuable.

We needed to actively seek out those insights, ask questions, and build on what already existed rather than reinventing the wheel. If previous events weren’t successful, you probably wouldn’t be doing it again. Lean into previous experiences and successes.

At the same time, it’s not just about repeating what’s been done — it’s about refining it. Each offsite should be a better, more thoughtful version of the last. That means taking previous experiences, identifying what worked and what didn’t, and making deliberate improvements across logistics, communication, and attendee experience.

None of this happens last minute. To truly manage expectations and execute smoothly, planning conversations need to start early — ideally 6–8 months in advance (even though the Percona Turkey retreat was accomplished in just four months). That runway gives teams the time to align on goals, pressure-test ideas, collaborate across functions, and work through the inevitable complexities that come with an event of this scale.

Success lies in thinking through every single touchpoint of the attendee experience. From the moment attendees board their flight, train, car, or motorcycle to the final farewell high-five, it’s an experience.

Paying close attention to each touchpoint shows up in many moments, including:

• Branded coasters placed on every table setting during business meetings (yes, people notice)
• Signage throughout the venue so no one ends up in a yoga class instead of a database workshop
• Check-in gifts that reinforce the Percona brand (and maybe include a surprise or two… goat-related, naturally)
• Carefully planned cocktail hours where “networking” magically turns into genuine connection
• Group dinners where teammates finally meet the humans behind the Slack avatars
• A candy swap featuring treats from every country represented — it doesn’t need to be formal. Everyone piles their local candies on a table for the masses
• A myriad of team-building activities spanning four days, including a competition to build the tallest free-standing printer-paper tower using only paper and tape (sidenote: be prepared for every single team to think that they won and the other teams cheated)

But what people remember most isn’t just the agenda, t-shirts, stickers, goat swag, candy, or coffee breaks. It’s the combination of these thoughtful touches woven into every moment.

It’s not just logistics that make the trip — it’s using those details and that collaboration to master storytelling.

Step Two: Time May Not Be on Your Side (But Deadlines and Effective Communication Are)

A trip like this doesn’t come together overnight. But sometimes it needs to come together pretty close to overnight. That means a very well thought-out internal communications plan and schedule that answers as many questions as possible before they are even asked.

But no matter how solid a formal communications plan is, there will be moments when questions get asked that were already addressed — which is why finding other ways to communicate, collaborate, and involve stakeholders and attendees is crucial. Think dedicated Slack channels with full transparency and readily available information, weekly touchpoints with department heads who can convey updates and reminders to their teams, and frequent conversations with finance, human resources, and legal throughout.

Behind the scenes, you’re looking at 100–150 hours of internal planning — all before the first suitcase is packed.

With that level of complexity, time becomes both a constraint and a forcing function. Deadlines matter, timelines matter, and how you communicate along the way matters even more. Clear, consistent communication is what keeps everything moving forward when there are dozens of parallel workstreams and stakeholders involved.

Internally, communication has to be frequent, detailed, and often repetitive. There’s no room for assumptions or “I thought someone else had that covered.” Every update, decision, dependency, and small detail needs to be shared openly and documented so nothing slips through the cracks.

The more visibility the team has, the more aligned everyone stays.

That means being proactive, not reactive. Attendees need consistent touchpoints leading up to the trip: detailed itineraries, travel guidance, visa reminders, packing expectations, local logistics, and clear points of contact. Information should be centralized, easy to access, and reinforced multiple times so no one is left guessing.

During the event, communication doesn’t stall. In fact, it accelerates — with adaptability, pivots, and clear, concise explanations.

Real-time updates, schedule reminders, transportation details, and contingency plans all need to be communicated clearly and quickly. When done right, attendees feel taken care of, confident, and fully present in the experience rather than worrying about logistics.

At the end of the day, strong communication — both internally and externally — is what turns a complex, global operation into something that feels seamless. It’s not just about sharing information; it’s about creating clarity, building trust, and ensuring every single person knows exactly where they need to be and when.

This also means over-communicating by design. Weekly check-ins turn into twice-weekly syncs as the event gets closer. Quick updates become detailed run-of-show documents. Slack threads involving all attendees, shared docs, and status trackers become the backbone of execution. It may feel like a lot, but that level of transparency is what prevents last-minute surprises.

Oh yeah — then there’s vendor coordination and communication.

Hotels. Transportation. AV teams. Catering. Swag production. Signage. Shipping. Local experiences. Backup plans for your backup plans.

Each one comes with:

• Deadlines
• Dependencies
• Payment schedules
• And the occasional “we need final numbers by tomorrow or your menu disappears” situation

Miss a deadline? That’s not just a small hiccup — it can mean:

• Increased costs
• Limited availability
• Or a last-minute scramble that no one wants to experience

Precision, effective communication, and timing aren’t optional — they’re everything.

Step Three: Build the Experience, Not Just the Agenda

You can have the best presentations in the world, but that’s not what people will remember.

What they will remember — and what they will certainly talk about afterwards:

• The first time they met a teammate in person after years of working together online
• The laughs over dinner
• The spontaneous conversations during coffee breaks
• The shared “we made it here” energy

For a company like Percona — 100% remote, spanning 50+ countries — these experiences and moments aren’t just nice-to-haves. They’re essential.

An offsite like this creates:

• Stronger team bonding across regions and roles
• Real human connections that make collaboration smoother
• Cross-functional understanding that Slack threads simply can’t replicate
• A sense of belonging that transcends time zones

In short: it turns coworkers into teammates. As a remote-only company, Percona has a uniqueness with nearly 14% of employees who have been with the company for 10+ years. This isn’t an accident — it’s a direct result of the kind of networking, partnership, and friendships that these types of retreats create.

The agenda for an offsite retreat should be intentionally designed to educate, inspire, and elevate the overall experience — not just fill time on a schedule. Every presentation needs to be thoughtfully curated to add real value, ensuring it contributes meaningfully rather than feeling like content for content’s sake.

Step Four: Expect the Unexpected (and Pack Snacks)

No matter how well you plan, something will go sideways.

Flights will be delayed. Luggage will take a scenic tour of another country. Someone will forget something important (realistically, multiple someones).

The key is not avoiding problems, but trusting yourself, your team, and your planning to be ready for them.

A few survival tips:

• Always have contingency plans (plural). Seriously. Always. And seriously — plural. Hurricane hitting the beach on closing outdoor dinner night? Have a space ready to go and an action plan for communicating the pivot. All your swag held up in customs? Find the local flea market and craft shops and make your gifts local. Speaker forgot a slide advancer and the venue’s failed and the venue’s backup failed? Keep one on the ready with you at all times.
• Over-communicate everything. You can’t tell people important things too many times.
• Build buffer time into the schedule. Imagine trying to get your three-year-old out the door early in the morning. It’s like that, but with 100 adults. Give yourself some cushions.
• Keep a sense of humor handy — it’s arguably your most valuable tool. You can diffuse a lot of tension with a little humorous quip. Humor brings the tension down, eases the panic, and grounds everyone. Even the most panic-stricken, reactionary executive you’ve ever met enjoys a good laugh in tense moments.
• Slow down — panic happens when speed outmaneuvers thought. It’s not a race, and you don’t have to beat everyone to a solution. Trust yourself and trust your team, and slow down enough to think through clearly without the heaviness of undue pressure.

Step Five: Remember Why You’re Doing It

After the spreadsheets, the emails, the vendor calls, and the 47th revision of the rooming list, it’s easy to forget the bigger picture.

But then the event starts.

People arrive. Conversations spark. Teams connect. Ideas flow. Energy builds.

And suddenly, all 100–150 hours (and then some) make perfect sense.

What you’ve created isn’t just an offsite — it’s an experience that strengthens your team in ways no virtual meeting ever could.

Don’t lose sight of your objective.

Final Thoughts: Keep Climbing

Bringing 100 people across the world to Turkey wasn’t easy — but it was worth every detail, every deadline, and every late-night planning session.

When you return home from executing a trip like this, you will want to shut off all notifications and take a few days to yourself without having to resolve anything. But also make sure you ponder what you would have liked to see done differently. Would arranging transfers from the airport to the resort be more cost-effective and smooth than relying on taxis? Most likely. Would building in more leisure time and opportunities for attendees to explore the local surroundings be worth sacrificing one or two sessions of training? Absolutely.

Learn from that and adapt for next time.

Because at the end of the day, investing in your people — especially in a remote-first world — is one of the most impactful things you can do.

And if that investment comes with a mountain goat mascot, a LEGO companion, custom cakes, coasters, t-shirts, drinks, food, and out-of-shape pickup basketball games, then you will find a team that fully leans into the journey and the message.

We came as individuals from across the globe.

On the day the retreat ended, I found myself in a rental car with a colleague I’d never met in person before. Two co-workers from different sides of the globe, different cultures, and two very different paths that led us to that spot. We drove for a full day, exploring site after site and chatting and laughing about life, work, and music.

This interaction and moment doesn’t happen without retreats like this.

We came together as members of the Software Engineering group at a place we all worked, and left as a unified team of Perconians — and as friends.

That’s your real ROI.

Herding Goats Across Continents: How We Took 100 People From Around the World to Turkey for an Offsite (and Lived to Tell the Tale) appeared first on MariaDB.org

Wherever you run ClusterControl — on-premises, in any cloud, or hybrid — you can now deploy ClickHouse either as a standalone OLAP workload or as the analytics tier of a comprehensive database stack, operated alongside your transactional databases under a single control plane. Your environment, your infrastructure, your data.

This release also delivers a major upgrade to PostgreSQL backup workflows, introduces built-in usage metering for consumption-based operations, and brings back a user-favorite capability from ClusterControl v1. Let’s dig in

ClickHouse: analytics on your terms, in any environment

Analytical workloads aren’t nice-to-have anymore. Whether it’s real-time dashboards, log analytics, or feeding features to AI systems, the OLAP tier is becoming as operationally critical as the transactional tier — and it deserves the same automation, monitoring, and sovereignty guarantees. ClickHouse support means you can run that tier on your own infrastructure, under your own control, with full lifecycle automation.

But running ClickHouse yourself has meant either adopting a vendor’s cloud — with your analytical data leaving your environment — or hand-rolling deployment, monitoring, and operations. ClusterControl 2.5.0 gives you a third option: full lifecycle automation for ClickHouse on infrastructure you control, whether that’s a single analytics node or the OLAP tier of your entire database estate.

With 2.5.0 you can:

• Deploy automatically — single-node instances or replicated clusters with embedded Keeper, provisioned through the same workflow you already use for MySQL, PostgreSQL, MongoDB, and Redis
• Monitor and alert through ClusterControl’s unified dashboards — one pane of glass across your transactional and analytical estate
• Back up and restore your ClickHouse clusters
• Scale as analytical workloads grow
• Import existing ClickHouse clusters into ClusterControl management, and drive operations from the s9s CLI and API as well as the UI
• Secure inter-node communication — SSL-encrypted links between cluster nodes with per-node certificates

Run it standalone if analytics is all you need. Or run it as one tier of a comprehensive stack — ClickHouse for OLAP next to MySQL, PostgreSQL, MongoDB, and Redis for OLTP, with load balancers, backups, and access control managed the same way across all of them. Same workflow, same alerting, same operational muscle memory, in whatever environment your requirements dictate.

What’s next for ClickHouse in ClusterControl

This release is the foundation, and the roadmap builds directly on it. Planned improvements include:

• Replication from MySQL and PostgreSQL into ClickHouse — feed your analytics tier directly from your operational databases, targeted for the next release
• Sharded ClickHouse cluster deployments for horizontally scaled analytical workloads
• ClickHouse user and role management from the UI

PostgreSQL backups

Two significant improvements land for PostgreSQL in 2.5.0:

Streaming backups directly to S3. ClusterControl now streams pg_basebackup output straight to S3-compatible object storage — Amazon S3, MinIO, Google Cloud Storage (S3 mode), DigitalOcean Spaces, Wasabi, and others. No local disk staging, no oversized temp volumes on your database hosts, and a shorter backup-to-cloud pipeline overall.

Native incremental backups (PostgreSQL 17+). PostgreSQL 17 introduced incremental backup support in pg_basebackup, and ClusterControl 2.5.0 puts it to work — including S3 upload. For large databases, that means dramatically smaller and faster backups between full baselines.

Usage metering and operator billing (Pay-As-You-Go)

For operators, MSPs, and platform teams running database services on a consumption basis, 2.5.0 introduces built-in usage metering:

• Hourly usage snapshots collected per controller across your managed estate
• On-demand billing reports — estate-wide or filtered by tag or cluster — with cryptographic sealing and independent verification
• A dedicated operator billing page in the multi-controller UI, with JSON/CSV export

It’s the foundation for Pay-As-You-Go commercial models on infrastructure you control — a natural fit for Sovereign DBaaS operations. The feature is off by default and only surfaced when metering is enabled.

Cluster-wide configuration management is back

By popular demand from ClusterControl v1: change a database parameter across every node in a MySQL cluster in a single action. Dynamic parameters are applied at runtime — no per-node edit-and-restart cycle, and less downtime for routine configuration changes in production.

PostgreSQL database user management

reate and manage users and roles from the UI, with fine-grained privileges down to schema and table level, lock/disable/enable lifecycle operations, pg_hba.conf editing with tracked changes, and user search and filtering

Other noteworthy improvements

• Scalable controllers pool hardening — UI-driven upgrades of remote pool members, automatic alarms on version mismatch, safer pool joins, and better resilience for large fleets
• Enhanced Kubernetes database support — clusters and backup schedules can now be deployed through GitOps as reviewable Git pull requests, plus structured logging, metrics, and default dashboards for observability, more reliable cluster health reporting, and operator compatibility and security updates
• Custom pg_hba configuration — define custom pg_hba.conf rules at deployment time, plus UI editing of entries — useful for multi-datacenter PostgreSQL topologies
• Audit log filtering and export in the UI
• ProxySQL management promoted from a pop-up dialog to a dedicated page
• Faster database deployments — multithreaded installation provisions 4 nodes in parallel for PostgreSQL and MySQL clusters, with optional additional customization.
• Redis / Valkey Sentinel logs now included in error reports for easier failover debugging
• Content-Security-Policy headers in the web UI, and improved resilience for long-running backup jobs

Get started today

Get ClusterControl v2.5.0 as a new user by signing up for a free 30-day trial or upgrade your CC deployment to practically deploy ClickHouse or bring your current deployment under ClusterControl’s management and access the other game-changing capabilities. In the meantime, full details can be found in the Release Notes.

Questions about running ClickHouse or any other engine with ClusterControl? Talk to us.

Install ClusterControl in 10-minutes!
Free 30-day Enterprise trial included

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://<ClusterControl_host>/, 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 ClusterControl 2.5.0 brings ClickHouse support to on-prem, cloud and hybrid environments appeared first on Severalnines.

ClusterControl 2.5.0 brings ClickHouse support to on-prem, cloud and hybrid environments appeared first on MariaDB.org

MySQL 8.0.17 GTID Crash Safety Improvement appeared first on MariaDB.org

Source

What Is Data Sovereignty and How Does MariaDB Protect Sovereignty in Cloud Databases? appeared first on MariaDB.org

Continue reading “Hear Ye, Hear Ye: A Guide to MariaDB’s Governance Model”

Hear Ye, Hear Ye: A Guide to MariaDB’s Governance Model appeared first on MariaDB.org

Hear Ye, Hear Ye: A Guide to MariaDB’s Governance Model appeared first on MariaDB.org

Often, we are tempted — or pushed — to just

Understanding MySQL Replication “fatal error 1236”: “Replica has more GTIDs than the source has, using the source’s SERVER_UUID” appeared first on MariaDB.org

Continue reading “Wirekite becomes Silver Sponsor of MariaDB Foundation”

The post Wirekite becomes Silver Sponsor of MariaDB Foundation appeared first on MariaDB.org.

Wirekite becomes Silver Sponsor of MariaDB Foundation appeared first on MariaDB.org

This investigation began as a performance comparison for different memory allocators. However, during benchmarking, I discovered unexpected effects deserving a more detailed explanation. I hope you find these findings both interesting and useful.

Imagine you need to set up a MySQL database server. Every detail is planned: the operating system, the CPU architecture, the number of cores, the amount of RAM, the storage capacity and speed. On paper the hardware looks like it can handle the workload. But in reality, things rarely go exactly as planned. So, conducting a thorough stress test is the next thing to do.

2. Realities of stress testing

You configure your MySQL server setting the innodb_buffer_pool_size to 70-80% of your available RAM. This creates a large fast buffer for your data and indexes, reducing the need for slower disk input/output.

After a warmup period and a few hours of testing, everything looks great. The server is working at a steady pace, performance is stable. You tick the box – the server has passed the basic stress test. Thinking everything is fine, you consider leaving the test running over the weekend, expecting only minor fluctuations in performance.

However, when you check the status the next morning, you find that the CPU is idle and the mysqld process has vanished. Did it crash? You check the server error logs, but there is no record of a crash or a shutdown—not even a core dump. Then, you look at the system logs and find something unexpected:

journalctl -k -g mysqld

Jun 09 07:29:14 beast-node7.tp.int.percona.com kernel: Out of memory:
Killed process 3936620 (mysqld) total-vm:194627592kB, anon-rss:183047480kB, file-rss:640kB, shmem-rss:0kB,
UID:955676158 pgtables:355860kB oom_score_adj:0

It appears that mysqld ran out of memory and was terminated by the OOM (Out of Memory) killer after running for about 16 hours.

We will focus on Resident Set Size (RSS), which is the subset of Virtual Memory Size (VSZ). RSS is the most significant part of VSZ and other parts like swap (only 8Gb) do not make notable contributions.

The RSS reached 183GiB, significantly higher than the initial 145GiB (with the innodb_buffer_pool_size set to 135G). The mysqld process had grabbed nearly 40GiB of extra memory, which at first looked like a memory leak. I ran my stress tests on different versions of MySQL and Percona Server and found a recurring pattern: memory usage climbed steadily until the system killed the process.

I won’t dive into the leak diagnosis here, but the result was clear: mysqld wasn’t leaking memory in the traditional sense. However, we still had to explain that 40GiB growth.

3. Configuration and methodology

The configuration was as follows:

The testing was done as follows:

What we wanted to achieve:

• Create conditions when memory allocations and deallocations inside the database server are frequent.
• Utilize as much of physical memory as possible (at least 80%) by giving it to InnoDB Buffer Pool.
• Use all available CPU resources in the most efficient way to prevent threads contesting for execution time (the number of connections should match the number of logical CPU cores).
• Eliminate any layers that add overhead and get in the way of direct measuring of allocators frequency and efficiency. The connections will be established using a socket file.

Servers configuration file:

# Make sure data dir is on NVMe
datadir=/nvme/data

# Thread Pool is enabled only for Percona Server
plugin-load-add=thread_pool.so
thread_pool_size=16
thread_pool_max_threads=5000
thread_pool_stall_limit=500

# Disable binary logging
skip-log-bin

# Connection settings
max_connections = 200

# Logging
log-error = /home/bogdan.degtyariov/servers/data/mysql-error.log
pid-file = /home/bogdan.degtyariov/servers/data/mysql.pid

# Socket
socket = /tmp/mysql-alloc-test.sock

# Disable SSL requirement
require_secure_transport = OFF

# Other settings
sql_mode = ""
wait_timeout = 288000        # 80 hours
interactive_timeout = 288000 # 80 hours

# Table settings
default-storage-engine = InnoDB

# InnoDB redo log configuration
innodb_redo_log_capacity = 32G

# Minimize flush overhead (not crash-safe, but optimal for testing)
innodb_flush_log_at_trx_commit = 0

# Memory configuration
innodb_buffer_pool_size = 150G # Configurable down to 80G
innodb_buffer_pool_instances = 16
innodb_io_capacity = 20000

# Performance optimizations
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 256M
innodb_doublewrite = OFF

# Transparent Huge Pages can be turned ON or OFF for the testing
large-pages = ON

4. Where did the memory go?

Memory management is complex, so let’s simplify. Applications rarely talk directly to the Linux kernel because the kernel typically works in 4KB pages, which is inefficient for developers. Instead, applications use allocators like glibc malloc, jemalloc, or tcmalloc. These tools handle memory operations by minimizing overhead, managing bookkeeping, and preventing fragmentation. Most importantly, they use caching.

When a program frees memory, the allocator rarely returns it to the OS immediately. Instead, it moves that memory into an internal “free-list” cache. Reusing memory from this cache is much faster than requesting new memory from the kernel.

Also, the Percona Server for MySQL and upstream MySQL Server use their own implementation of the memory arena allocator called MEM_ROOT. Historically MEM_ROT was architected decades ago when the standard Linux implementation of glibc memory allocator was slow and prone to lock contention in multithreaded programs.

Enabling memory profiling revealed that MEM_ROOT allocations for cursor metadata in stored routines was responsible for most of the additional memory acquired by the server process:

sp_head::execute_procedure           (TPC-C stored procedure)
   └─ sp_instr_copen::execute        (OPEN <cursor> statement)
       └─ sp_cursor::open
           └─ mysql_open_cursor
               ├─ Materialized_cursor::send_result_set_metadata  87831 MB (94.3%)
               └─ Query_result_materialize::start_execution      5311 MB  (5.7%)
                   └─ MEM_ROOT::Alloc / AllocBlock / ForceNewBlock

NOTE: 87G is a significant growth of memory allocation considering that in that run the server initially allocated ~85G with Innodb_buffer_pool_size=80G.

The problem happens regardless of the data size because the actual issue is in stored routines cursor metadata. When the stored procedure is called the memory allocated for cursor metadata is not freed. Over the course of many repeated calls to the same stored procedure the cumulative amount of memory for the cursor can reach any value.

The following graph demonstrates the memory growth in Percona Server 8.4.8-8 from ~80G to over ~180G in RSS and over 200G VSZ over the period of 24 hours.

Thus, a bug was reported for Percona Server: https://perconadev.atlassian.net/browse/PS-11472

With Percona Server for MySQL 9.7.0-1 the RSS/VSZ growth was at a slower rate, but still noticeable and it was not flattening towards a stable horizontal line (the server was configured with a small amount of memory for innodb_buffer_pool_size=4G and run for 5 hours instead of 20).

Memory profiling showed the new allocations in version 9.7.0-1 were in the same place where cursor metadata is handled:

sp_head::execute_procedure           (TPC-C stored procedure)
   └─ sp_instr_copen::execute        (OPEN <cursor> statement)
       └─ sp_cursor::open
           └─ mysql_open_cursor
               ├─ Materialized_cursor::send_result_set_metadata
               └─ Query_result_materialize::start_execution      
                   └─ MEM_ROOT::Alloc / AllocBlock / ForceNewBlock 4,025.8 MB (99.6%)

5. Possible workarounds

My tests showed that OOM crashes happened consistently under two specific conditions:,

1. Connections are never closed and stay open permanently
2. Connections ran queries at maximum speed without any pauses

Also, when the connection lifetime was limited and users were made to close connection and reconnect after 1M transactions, the memory exhaustion stopped, and memory was freed correctly – all with only a minor impact on performance. To minimize the delays associated with creating a new connection thread on the server I used the connection pool functionality in HammerDB. When the connection lifetime is ended, the actual connection is not closed, but “reset” and reused. This frees the context accumulated during the connection activity and stimulates returning memory to the OS. This connection pool mechanism is more efficient than the open/close cycle for maintaining the connection lifetime. 

I had two runs with reconnecting users: with and without connection pool. The graph demonstrates that using the pool improves the performance in this test.

Also, during another experiment with unlimited connection lifetime, adding a 0.5ms pause after a few transactions prevented the crashes, though performance dropped slightly more.

The memory graphs have consistent periodic oscillations that never reach into the dangerous zone.

6. Summary

To sum it up: the observed MySQL’s memory bloating is caused by a problem in the server cursor implementation not freeing metadata memory. 

Under heavy, constant load, that memory accumulates to the amount which eventually causes an OOM crash. Capping how long connections stay active or adding a short pause between transactions, gives the server time to clean itself up. Normally the client side processing adds such pauses without need to do it on purpose.

Finally, it is important to remember that the best benchmark results do not always guarantee the best real-life performance.

The post Stored Procedures memory consumption in Percona Server for MySQL appeared first on Percona.

Stored Procedures memory consumption in Percona Server for MySQL appeared first on MariaDB.org

Why this release matters

MongoDB 8.3 Community went GA in May 2026. This was the fourth significant MongoDB release in nearly 2 years. I have observed that the upstream MongoDB Community project is now moving faster than most organizations’ upgrades (adoption telemetry data later in this blog post). Nonetheless, Percona’s job is to ensure that the free, enterprise-grade path does not fall behind, and you still can get the performance of the current release without giving up data-at-rest encryption with KMIP, HashiCorp Vault, or OpenBao, audit logging, external LDAP authentication, OpenID Connect, File copy-based initial sync, in-memory engine, or audit log with log redaction. All of that stays in Percona Server for MongoDB 8.3, and it stays free and open. This is the Percona way.

I’ll not discover America by writing that the data layer now has to move at AI speed. Application teams are shipping agentic workloads today, and that’s why performance and functional requirements are growing exponentially. Two years ago, we experienced occasional query retries and recall storms, critical security patches, and multi-region deployments could trade compliance for latency. Now, this is bread-and-butter we need to deal with every day. 

I’m happy to share that Percona has published Percona Server for MongoDB 8.3 in Technical Preview today. 8.3 is not one release forward from 8.0 – it is three. Everything that landed in the two minor releases (and 8.1 Rapid Release) in between arrives at once. Percona Server for MongoDB 8.3 is the most performant release so far! And besides the performance boost and many functional improvements, I’ve described below, I’m personally most excited about the fact that this release enables full-text search and vector search capabilities, so you can finally equip your applications with AI power using the same MongoDB. More about that in my next blog post – brace yourself!

What’s new compared to 8.0

Because we’re skipping 8.1 and 8.2 releases, the delta is large. I grouped the highlights by what you’d actually notice.

Performance

Percona Server for MongoDB inherits performance optimization from the upstream MongoDB Community:

• Up to 195% higher throughput on time-series bulk insertions
• Up to 40% on match filter queries
• Up to 20% on queries against documents with arrays
• Up to 10% on in-cache read workloads
• Up to 5% reduction in CPU utilization

Moreover, Percona Server for MongoDB 8.3 now offers faster initial sync, faster time-series bulk inserts, and reduced multi-planning costs for queries. As always, your mileage depends entirely on the shape of your workload.

Query planning

The Cost-Based Ranker (CBR) is now the default plan selection mechanism for eligible queries. Multi-planning gets a short trial period; if it can’t settle on a plan, CBR evaluates each plan node by estimated cost. New serverStatus counters under metrics.query.cbr and new explain output let you see when CBR was used and how often it won. 

Query language and aggregation

• Native hybrid search (full-text search and vector search) is enabled with Percona Search for MongoDB (fork of mongodb/mongot project) via $scoreFusion and $rankFusion
• Array element indexes are now accessible in $map, $filter, and $reduce via the new arrayIndexAs field and the $$IDX system variable
• New expressions: $subtype, $createObjectId, $hash, $hexHash, $serializeEJSON, $deserializeEJSON, $currentDate
• $convert gains a base argument (base 2/8/10/16) and can convert strings representing arrays and objects, plus BinData numeric arrays in both directions
• $mergeObjects now works inside $setWindowFields
• $concatArrays and $setUnion accumulators, the $listClusterCatalog stage, and $lookup across multiple encrypted collections for CSFLE and Queryable Encryption

Storage and operations

The most operationally useful change for anyone running in containers is the WiredTiger cache size, which can now be set as a percentage of available memory via –wiredTigerCacheSizePct / storage.wiredTiger.engineConfig.cacheSizePct, instead of a fixed GB value. If you run Percona Server for MongoDB on Kubernetes via our Percona Operator for MongoDB. This alone is worth the upgrade!

Also worth knowing:

• zstd negative compression levels – the supported range widens to -7 through 22, trading ratio for speed
• Initial-sync index builds now use 10% of available RAM by default, tunable, and bounded
• terminateSecondaryReadsOnOrphanCleanup (on by default) terminates long-running secondary reads that started before a chunk migration was committed. Previously, those reads continued and could silently return incomplete results with no error. orphanCleanupDelaySecs moves from 900 to 3600 to accommodate this.

Sharding

• removeShard is deprecated, replaced by four commands that give you granular control over draining and removal: startShardDraining, stopShardDraining, shardDrainingStatus, commitShardRemoval. A parallel set exists for the embedded-to-dedicated config server transition.
• Sharded clusters: DDL operations and applyOps can now run only on mongos across all sharded clusters.
• New mongod –replicaSetConfigShardMaintenanceMode converts a replica set primary directly into an embedded config shard, skipping the dedicated config server replica set step.
• Shard-level query stats now include queries that originated on mongos. Previously, most forwarded queries were invisible in shard-level stats.

Security

• Software Bills of Materials (SBOMs) are attached to our deliverables across all release distribution channels. SBOMs improve software supply chain transparency by documenting the components and dependencies included in a build. They are generated automatically as part of the release pipeline in the industry-standard CycloneDX format.
• New pre-auth connection resource limits (capMemoryConsumptionForPreAuthBuffers, preAuthMaximumMessageSizeBytes, messageSizeErrorRateSec)
• Ingress connection establishment rate limiting and per-application exemptions from ingress request rate limiting. See ingressRequestRateLimiterApplicationExemptions. for more.

Observability

• Query memory tracking: inUseTrackedMemBytes and peakTrackedMemBytes in $currentOp, profiler output, slow query logs, explain results, and $planCacheStats
• Slow in-progress query logs: a lightweight entry emitted once per query when it exceeds slowOpInProgressThreshold, so you can see a slow query while it is still running rather than after it finishes
• FTDC now collects connPoolStats for mongod
• Standardized disk-spill metrics (spills, spilledBytes, spilledRecords, spilledDataStorageSize) in explain output
• New TTL, replication lag, and admission control metrics in serverStatus

What’s changed: Read this before you upgrade

Upgrade path. To go from 8.0 directly to 8.3, your 8.0 deployment must have featureCompatibilityVersion set to 8.0. Verify with db.adminCommand({ getParameter: 1, featureCompatibilityVersion: 1 }). All cluster members must be running before you start.

Geospatial indexes may need rebuilding. Index generation now prioritizes GeoJSON over legacy numeric coordinates when a document contains both. If your documents have legacy numeric coordinates preceding GeoJSON coordinates and your existing indexes depend on the old ordering, rebuild them and re-verify your geospatial query results. Separately, 2dsphereIndexVersion now defaults to 4.

Error codes changed. Exceeding the $facet 100 MB limit now returns ExceededMemoryLimit (146) instead of 4031700. Upserts producing an oversized BSON object return 10334 BSONObjectTooLarge instead of 17419/17420. Anything in your stack that string-matches error codes needs updating.

$text sorted-by-score queries can now fail. The TextOr stage is capped at 100 MB. With allowDiskUse: true, it spills; with false the query errors out. Previously, it was unbounded, which is to say, previously, it could OOM your node instead.

Pre-epoch date arithmetic shifts by one second. $dateAdd and $dateSubtract, with a non-millisecond unit, on dates before 1970-01-01 now return a result that is one second greater. This propagates into $setWindowFields and $densify.

Monitoring integrations will need attention. The service field is removed from serverStatus output, and cpuNanos is moved from operationMetrics into $queryStats (Linux only).

Other behavior changes: $$CLUSTER_TIME now throws an error in standalone deployments. db.collection.validate({full: true}) no longer implicitly enables checkBSONConformance. explain() against a non-existent database on a sharded cluster no longer creates the database. Time series collections reject a timeField starting with $ and reject an index named or hinted “_id_”.

One-way doors from sharding to the replica set. A replica set that was previously a sharded cluster cannot be converted back into a sharded cluster – residual sharding metadata blocks it. And downgrading from 8.3 requires you to first drop 2dsphere version 4 indexes and update or drop any views, validators, or collection validation rules that use 8.3-only expressions.

Don’t stay behind: What the telemetry says about version adoption

We analyzed anonymous product telemetry from Percona Server for MongoDB over the last 12 months to assess the share of active database instances. Major version adoption takes more than a year for many users and organizations. PSMDB 8.0 went from roughly a fifth of the installed base to nearly half over twelve months. That is healthy, and it is also slower than a release cadence of four significant upstream versions in 19 months. The gap between how quickly MongoDB ships and how quickly the installed base moves is why we are publishing a Technical Preview instead of delaying until a GA build. Also, that’s a strong signal to move to version 8.0 if you haven’t already, and then explore 8.3. 

Moreover, if you run (or consider running) your Percona Server for MongoDB in a container deployment – Containerized deployments now account for about 24% of active instances, up from roughly 21% a year ago – so still Docker and Kubernetes for MongoDB are the thing, and this release improvement of percentage-based WiredTiger cache sizing is probably the most immediately useful thing.

Technical Preview: What does that actually mean

A Technical Preview build is complete enough to install, benchmark, and evaluate. It meets equal quality and packaging requirements as any other 8.0 patch. However, it has not undergone the full release qualification we require for a GA release with respect to ecosystem compatibility:

• Percona Backup for MongoDB (PBM): logical backup and restore against an 8.3 server may cause some issues. Do not rely on it to protect anything you care about. Current known limitations are around logical backup and restore, and PITR. If you’re interested in details and progress on those, follow our Jira tickets.
• Percona ClusterSync for MongoDB (PCSM): replication to or from an 8.3 cluster is unvalidated.
• Percona Monitoring and Management (PMM): metric collection may be incomplete or fail outright, particularly given the serverStatus and cpuNanos changes described above.

To be clear about what this is and isn’t: upstream MongoDB Community 8.3 is a stable, production-suitable release with support through October 2029. The Technical Preview label applies to Percona’s build, not to MongoDB 8.3 itself. It reflects where we are in qualifying our surrounding tooling against this version, and not a judgment about upstream stability.

So if you need a fully-integrated Percona Server for MongoDB today, 8.0 is still the answer. The current release is 8.0.26-11. When our 8.3 build reaches GA, that changes.

How to start

We recommend installing (or upgrading) Percona Server for MongoDB using the official Percona repositories via the percona-release repository management tool and your system’s package manager. For further instructions, start with the quickstart guide for the fresh installation or an upgrade procedure from 8.0.

Feedback needed

This is the part that matters. A Technical Preview is only worth shipping if it yields findings.

We are specifically interested in:

• Percona-specific feature behavior: data-at-rest encryption with KMIP or Vault, audit logging, external LDAP authentication, OpenID Connect, file copy-based initial sync, hot backup, and audit logging with log redaction.
• PBM, PCSM, and PMM interactions. We know these might be limited. Knowing how they fail helps us prioritize and fix them within the next release cycle.
• Upgrade friction from 8.0, especially on sharded clusters and around the geospatial index and removeShard changes.

Post your findings in the Percona Server for MongoDB forum. Include your topology, your workload shape, and the exact version you tested. Engineering reads that category directly, and feedback from this preview will shape what the GA build looks like.

If you find a security issue, please report it through Percona Security disclosure process rather than posting publicly.

Percona Server for MongoDB is a free, source-available, drop-in replacement for MongoDB Community Edition with enterprise-grade features. Telemetry figures in this post are derived from anonymous product telemetry.

The post Percona Server for MongoDB 8.3 Technical Preview Is Now Available appeared first on Percona.

Percona Server for MongoDB 8.3 Technical Preview Is Now Available appeared first on MariaDB.org

A little history about this, Baron Schwartz, a MySQL Legend who is not involved in the community anymore, compared using the TIMESTAMP type to

MySQL Best Practice : not using date / time types, nor ENUM appeared first on MariaDB.org

Source

MariaDB Java Connector 3.5.10, 3.4.4, 3.3.6, and 2.7.15 now available appeared first on MariaDB.org

The post From a Production Problem to MariaDB: Headout’s Open-Source Contribution Journey appeared first on MariaDB.org.

From a Production Problem to MariaDB: Headout’s Open-Source Contribution Journey appeared first on MariaDB.org

Source

MariaDB 12.3: Faster Vector Search with Matryoshka Optimization appeared first on MariaDB.org

This led us to wonder whether a physical remote backup is also possible using the “new” MySQL Server Clone feature, which was introduced in MySQL 8.0.17 (July 2019).

The MySQL Clone operation can be used to create both a local and a remote copy of the database. The original idea behind this feature was likely to automatically create nodes in an InnoDB Cluster (similar to Percona XtraDB Cluster SST).

Terminology used in the clone operation:

• Donor (source database)
• Recipient (destination database)

The clone operation is initiated from the recipient. The data can be cloned to the recipient’s own directory or, alternatively, to a different directory.

Preparations

The plugin must be installed on both the Donor and the Recipient.

SQL> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.01 sec)

SQL> SELECT PLUGIN_NAME, PLUGIN_STATUS
 FROM INFORMATION_SCHEMA.PLUGINS
 WHERE PLUGIN_NAME = 'clone'
;
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| clone | ACTIVE |
+-------------+---------------+

If you want to force the plugin to load on restart, it must be configured as follows in the MySQL configuration file (my.cnf):

[mysqld]
plugin_load_add = mysql_clone.so
clone = FORCE_PLUS_PERMANENT

Local Backup Using Clone

This method can serve as a replacement for a physical backup solution (xtrabackup or MySQL Enterprise Backup (mysql_backup)). On the database acting as the recipient in this case, execute the following command:

SQL> CLONE LOCAL DATA DIRECTORY = '/mnt/backup/mysql_clone';

The following items are still missing for the clone operation:

• All TLS keys (*.pem files).
• The auto.cnf file, which contains the server_uuid.
• The mysqld-auto.cnf file, which contains dynamically modified, persistent server configuration variables.
• The mysql_upgrade_history.
• The MySQL configuration file (my.cnf) as well as
• The binary logs.

$ cp ${datadir}/*auto.cnf ${datadir}/mysql_upgrade_history ${datadir}/*.pem /mnt/backup/mysql_clone/

Restoring the database is quite simple:

$ systemctl stop mysql
$ rm -rf ${datadir}/*
$ cp -a /mnt/backup/mysql_clone/* ${datadir}/
$ chown -R mysql: ${datadir}/*
$ systemctl start mysql

The #clone folder is created by the clone operation and can be ignored, but must not be deleted.

$ ls -lad /mnt/backup/mysql_clone/*
...
drwxr-x--- 2 dba dba 4096 Jul 27 14:52 '#clone'
...

It is automatically removed when the MySQL database is started. If you delete it anyway, you will receive the following error messages:

[System] [MY-013576] [InnoDB] InnoDB initialization has started.
[System] [MY-013577] [InnoDB] InnoDB initialization has ended.
mysqld: Can't create/write to file './performance_schema/clone_status_385.sdi' (OS errno 2 - No such file or directory)
mysqld: Can't create file './performance_schema/clone_status_385.sdi' (errno: 2 - No such file or directory)
[ERROR] [MY-013272] [Clone] Plugin Clone reported: 'Client: PFS table creation failed.'
[ERROR] [MY-010202] [Server] Plugin 'clone' init function returned error.

The binary log position required for point-in-time recovery can be determined as follows:

SQL> SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;
+-------------------------------+-----------------+
| BINLOG_FILE | BINLOG_POSITION |
+-------------------------------+-----------------+
| boss_percona-84_binlog.000003 | 1231898 |
+-------------------------------+-----------------+

Remote Backup Using Clone

To create a remote backup using the clone functionality, a minimally functional MySQL database is required on the remote system. Unfortunately, a simple process or tool is not sufficient for this.

On the donor server, you need a user with the following privileges:

SQL> CREATE USER 'backup_user'@'%' IDENTIFIED BY 'secret';
SQL> GRANT BACKUP_ADMIN ON *.* TO 'backup_user'@'%';

In addition, the potential donor must be specified on the recipient server:

SQL> SET GLOBAL clone_valid_donor_list = '192.168.1.129:3306';

The remote backup is then performed as follows:

SQL> CLONE INSTANCE FROM 'backup_user'@'192.168.1.129':3306 IDENTIFIED BY 'secret'
DATA DIRECTORY = '/mnt/backup/mysql_clone';

The missing files described above must now also be copied somehow:

$ scp mysql@192.168.1.129:${datadir}/*auto.cnf /mnt/backup/mysql_clone/
$ scp mysql@192.168.1.129:${datadir}/mysql_upgrade_history /mnt/backup/mysql_clone/
$ scp mysql@192.168.1.129:${datadir}/*.pem /mnt/backup/mysql_clone/

Restoring the database is done in the same way as described above.

Conclusion

The MySQL Clone operation is a cool feature that I neglected for a long time because it never occurred to me that it could also be used for backup purposes.

I wouldn’t be surprised if the MySQL developers took a cue from PostgreSQL’s pg_basebackup when they implemented this feature.

Unfortunately, to my knowledge, this feature is still completely missing in MariaDB. Too bad!

Sources

• General: The Clone Plugin
• There are a few minor limitations for the clone backup, which are described here: Clone Plugin Limitations.
• Monitoring the clone backup is described here: Monitoring Cloning Operations.
• Tuning the clone backup is described here: Clone System Variable Reference

Backups Using the MySQL Clone Operation appeared first on MariaDB.org

The post Adobe Commerce Chooses MariaDB as Its Default Database Platform appeared first on MariaDB.org.

Adobe Commerce Chooses MariaDB as Its Default Database Platform appeared first on MariaDB.org

Continue reading “ScalaHosting Becomes a Gold Sponsor of MariaDB Foundation”

The post ScalaHosting Becomes a Gold Sponsor of MariaDB Foundation appeared first on MariaDB.org.

ScalaHosting Becomes a Gold Sponsor of MariaDB Foundation appeared first on MariaDB.org

Finally, I thought it was futile to look for a universal solution that would yield human-friendly outputs in command-line sessions.

So, why not have a nice graphical interface to visualize replica sets, sharding state, connections, and more instead? I looked for available GUI tools for MongoDB and came to the impression that almost all of them are built for developers. They are great for browsing collections, editing documents, or composing queries, but when it comes to typical DBA daily tasks — checking replication health, inspecting replica set configuration, or understanding what is really going on inside a sharded cluster — we are really down to the mongo shell. There is nothing wrong with the shell client, of course, but some things are simply easier to digest when presented visually. Here, of course, PMM does allow that, but it has a bit of a different purpose – long-term monitoring, and has to be installed and set up first. Besides, it does not do everything I wanted.

So, encouraged to experiment with vibe coding in Percona, I decided to experiment with filling this gap myself and started a small personal project: a lightweight DBA-oriented GUI interface called MClusterAdmin.

Project description

MClusterAdmin is a tool designed to be self-hosted, with a backend written in Go, while the frontend is in HTML and JavaScript. It offers a web UI interface featuring typical DBA perspective dashboards. The whole application ships as a single, small binary, with no agents, no external services, and no internal database of its own.

In order to try it, you need to copy the binary to a host that has access to the MongoDB servers to be monitored. I suggest running first locally on some test environment, like one created using mlaunch, anydbver, mongo_terraform_ansible, or a similar sandbox tool. For Docker environments, you can use the already existing image, just make sure your container shares the same network as the MongoDB cluster.

The tool has no authentication on its own. Very similarly to mongosh, the MongoDB URI and credentials you provide determine what it will be able to offer. You can connect a standalone instance, a replica set member, or a mongos router. The tool will discover the rest of the topology automatically and will establish individual connections to each member. No SSH access to the database hosts is needed.

With its tiny footprint, it should fit well into your cloud/K8S MongoDB deployments.

Let me be clear about what MClusterAdmin is not: it is not a data browser — you will not view or edit your collections’ documents with it. There are plenty of other tools for that! It is, again, not a long-term monitoring or alerting solution; for that, I strongly recommend Percona Monitoring and Management (PMM). MClusterAdmin is designed for quick, interactive cluster inspection and simple administrative operations that a DBA performs many times a day.

I will point out the key features that I decided to implement further below, but I guess watching a quick demo presentation will allow you to judge the tool much faster:

I don’t think it makes sense to describe all the features I was able to implement so far in detail. I hope everything is intuitive enough so that you can see for yourself. The documentation is available on GitHub if needed.

In short, my aim was to provide useful views covering replication topology and settings, sharding members and routers, but also overall database size statistics and their distribution among shards. This includes per-collection detailed sharding stats, data, and index sizes, etc. In addition to that, for each discovered MongoDB instance, you should be able to see basic WiredTiger usage stats, oplog details, including an on-demand breakdown of which collections changes generated the most recent oplog traffic, as well as some other most important server settings and usage summaries.

You will also find slow queries profiling, with the query explain module, as well as a quite functional users and roles management section.

The last security-focused section allows you to check whether authentication is enabled on each host, as well as what is the usage of each authentication mechanism.

Getting Started

Running MClusterAdmin takes a few seconds. Just download the suitable binary from the release page, or compile it yourself:

git clone https://github.com/PrzemekMalkowski/mclusteradmin.git
cd mclusteradmin
go build -o mca .
./mca

Then open the relevant address (by default http://localhost:8787) in your browser and paste your MongoDB connection URI to connect. There is also a --view-only flag, which disables all mutating operations both in the UI and at the API level — handy when you just want a safe, read-only window into a cluster. TLS mode for the web service interface is supported as well.

Kubernetes integration

It is extremely easy to add MClusterAdmin as a diagnostic pod to your existing MongoDB cluster in Kubernetes. Just use the available Docker image from the GitHub repository!

An example MClusterAdmin.yaml configuration can be as simple as:

apiVersion: apps/v1
kind: Deployment
metadata:
 name: MClusterAdmin
spec:
 replicas: 1
 selector:
 matchLabels:
 app: MClusterAdmin
 template:
 metadata:
 labels:
 app: MClusterAdmin
 spec:
 containers:
 - name: MClusterAdmin
 image: ghcr.io/przemekmalkowski/mclusteradmin:latest
 ports:
 - containerPort: 8787

A Word of Caution

MClusterAdmin is currently in beta, and it is a personal side project, so please do not point it at your production clusters just yet — test it in a safe environment first. The connected MongoDB user needs privileges matching the dashboards you want to use; the documentation breaks these down per feature, so you can follow the least-privilege approach instead of simply granting root.

Summary

MClusterAdmin was created as an attempt to fill the gap I found to be the case in the MongoDB community: a lightweight, replication- and sharding-aware GUI for MongoDB DBAs, free from any data-browsing ballast. The project is open source (GPLv3), and the code is available on GitHub. If you find it useful, miss a feature, or hit a bug, I would love to hear from you. Feedback is very welcome!

The article was created by a human.

Introducing 🍃MClusterAdmin: A Lightweight GUI Tool for MongoDB DBAs appeared first on MariaDB.org

Continue reading “The Queen and the “Half That Wasn’t Told””

The post The Queen and the “Half That Wasn’t Told” appeared first on MariaDB.org.

The Queen and the “Half That Wasn’t Told” appeared first on MariaDB.org

Continue reading “IBM continues as a Platinum Sponsor of MariaDB Foundation”

The post IBM continues as a Platinum Sponsor of MariaDB Foundation appeared first on MariaDB.org.

IBM continues as a Platinum Sponsor of MariaDB Foundation appeared first on MariaDB.org

The post Say the Name: MariaDB, MySQL, and the Ecosystem We Share appeared first on MariaDB.org.

Say the Name: MariaDB, MySQL, and the Ecosystem We Share appeared first on MariaDB.org

The three headline features are Percona ClusterSync for MongoDB, vector search, and PVC snapshot backups. ClusterSync clones and continuously replicates a live source into an operator-managed cluster. Vector search brings semantic queries to Percona Server for MongoDB. PVC snapshot backups move backups off the network path and onto the storage layer.

This release also widens where you can run it, adding official Rancher Kubernetes Engine (RKE2) support and full ARM64 images. Much of what shipped here traces back to requests on forums.percona.com and the public issue tracker.

In this post, you’ll learn about:

• ClusterSync migration and replication
• Vector search for semantic queries
• PVC snapshot backups
• Other improvements worth knowing about
  

Zero-Downtime Migration with Percona ClusterSync

Moving a live MongoDB database onto the operator has always been the awkward first step. Dump-and-restore needs a maintenance window sized to your data, and hand-built replication between a source and a target is fragile to set up and easy to get wrong. This release introduces Percona ClusterSync for MongoDB (PCSM) as an operator-managed component, so the migration path is via a Kubernetes object rather than a runbook.

Why it matters

The common case is migrating a hosted MongoDB service, for example MongoDB Atlas, for an operator-managed Percona Server for MongoDB cluster you control end to end. A typical trigger in production is a hosted-service bill that climbs with the workload, or a compliance requirement to keep data inside your own VPC and region: a team running a user-profile store on Atlas points PCSM at it, lets the target catch up over a day or two while the application keeps serving from Atlas, then cuts over in a maintenance window measured in seconds. PCSM clones the existing data, then tracks ongoing changes through MongoDB change streams, so the target stays current while you validate it. When you are ready, you cut the application over during a short window rather than a long one. The same mechanism keeps a continuously updated replica for non-production use or a hybrid-cloud copy.

How it works

PCSM runs as its own container, deployed and managed through a new PerconaServerMongoDBClusterSync custom resource. It performs an initial clone from the source connection string, then consumes change stream events to apply subsequent writes to the target. A mode field controls the lifecycle: running starts or resumes replication, paused holds it, and finalized stops replication.

Wiring it up

apiVersion: psmdb.percona.com/v1
kind: PerconaServerMongoDBClusterSync
metadata:
  name: my-cluster-sync
spec:
  clusterName: my-target-cluster-name
  image: percona/percona-clustersync-mongodb:0.9.0
  # mode controls the PCSM lifecycle intent. Allowed values:
  #   running   - start/resume replication (default)
  #   paused    - pause an active replication
  #   finalized - stop replication
  mode: running
  source:
    uri: mongodb://source-cluster-mongos.source-namespace.svc.cluster.local:27017
    credentialsSecret: my-cluster-sync-source
  # excludeNamespaces lists MongoDB namespaces (db or db.collection) to skip.
  # excludeNamespaces:
  #   - admin
  #   - local

clusterName names the operator-managed target that receives the data. source.uri and source.credentialsSecret point at the database you are migrating from, which can be Atlas, a self-managed replica set, or another operator cluster. mode is the control you drive the cutover with: run to catch up, pause to hold, then finalize once the application points at the new cluster. The optional excludeNamespaces list skips databases or collections you do not want to copy.
 

Cutover and rollback

The cutover is yours to time, not the operator’s. During the running replication, the target trails the source by the change-stream lag, which you watch until it is small and steady. You then stop writes on the source, let the last events drain, and repoint the application at the target cluster. Because the source keeps serving until you move the application, a rollback before cutover is simply leaving the application where it is. After cutover, treat the move as one-way once writes flow to the target, so verify the target thoroughly during the sync window rather than after.

Note: The PCSM component ships at version 0.9.0 with this release. Test the full migration and cutover against a staging copy before you run it on production data, and keep the source available until you have verified the target.

Vector search for semantic queries

Vector search retrieves results by meaning rather than exact keyword match, which is the retrieval pattern behind semantic search and retrieval-augmented generation for AI applications. Teams that already store their data in MongoDB have had to copy vectors into a separate engine to do this, which adds a system to run and a pipeline to keep in sync. In production, this is the pattern behind a support tool that surfaces past tickets describing the same problem in different words, a product catalog that returns items by intent rather than exact keywords, and a RAG service that grounds a model on internal documents. This release lets you store and query vector data alongside your regular documents in Percona Server for MongoDB, so those workloads query one system instead of two.
 

How it works

The operator deploys and manages the mongot search process, wires its authentication and TLS to the rest of the cluster, and keeps the search index synchronized for both replica set and sharded deployments. Applications query the index through the same MongoDB connection they already use, so you add semantic search without a second client, a second driver, or a second set of credentials. You do not stand up or secure a separate search tier; the operator treats mongot as another managed component of the cluster.
 

Wiring it up

Enable the search component in the custom resource:

spec:
  search:
    enabled: true
    image: perconalab/percona-server-mongodb-operator:main-mongot
    size: 1
    storage:
      persistentVolumeClaim:
        resources:
          requests:
            storage: 10Gi
    resources:
      requests:
        cpu: "2"
        memory: 2Gi

size sets how many search nodes to run, and storage gives the search index its own PersistentVolumeClaim so it does not compete with the database volume. Size the resources block to your index: vector indexes are memory-sensitive, so give mongot enough headroom for the corpus you intend to query.

Note: Vector search is a tech preview in 1.23.0 and is not recommended for production yet. It requires Percona Server for MongoDB 8.3 or later.

PVC snapshot backups

Logical and streamed physical backups both push data across the network to object storage, and for a multi-terabyte cluster, that path is the bottleneck. Backups run long, restores run longer, and both compete with production traffic for CPU and bandwidth. This release adds backups built on PersistentVolumeClaim snapshots, which takes the storage layer directly.

Why it matters

A PVC snapshot is a point-in-time copy of your data volumes taken at the storage layer through the Kubernetes VolumeSnapshot API. Because the operator asks the storage provider for a snapshot instead of streaming bytes out, a backup typically completes in seconds or minutes regardless of database size, and a restore is correspondingly fast. Two production situations show the difference: a nightly backup that no longer fits its window as a cluster grows past a few terabytes, and a staging refresh that ties up resources for hours while it restores a streamed copy. A storage-layer snapshot turns both into a near-instant operation. The speed comes from how the storage layer implements snapshots: instead of copying the whole volume, most backends record only the blocks that changed since the previous snapshot and reference the rest, so the cost tracks your change rate rather than the total database size. Snapshots also work with encrypted and TLS-enabled clusters, and they use fewer cluster resources because there is no long-running data-transfer job.

Wiring it up

The operator takes snapshot backups in two ways: on demand through a PerconaServerMongoDBBackup object, or on a schedule through a backup task. The scheduled form looks like this, using the external type and a VolumeSnapshotClass:

spec:
  backup:
    tasks:
      - name: daily-snapshot
        enabled: false
        schedule: "0 0 * * *"
        retention:
          count: 1
          type: count
          deleteFromStorage: true
        type: external
        volumeSnapshotClass: YOUR-VOLUME-SNAPSHOT-CLASS

type: external tells the operator to take a storage-layer snapshot rather than stream a backup, and volumeSnapshotClass names the VolumeSnapshotClass your CSI driver provides. The retention block prunes old snapshots on the schedule you set. Your storage provider must support the Kubernetes VolumeSnapshot API for this to work.

Snapshot backups complement the streamed and logical backups the operator already supports; they do not replace them. Snapshots usually live in the same storage account and region as the volumes they copy, so keep a streamed backup to object storage for off-site and cross-region disaster recovery. A practical policy pairs frequent fast snapshots for quick local recovery with a less frequent streamed backup for durability, and the operator runs both from the same backup.tasks list.

Note: PVC snapshot backups are a tech preview in 1.23.0 and are not recommended for production yet. Snapshot portability and retention semantics depend on your CSI driver, so test restores before you rely on them.

Other improvements

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

• Operator-generated connection string Secrets (K8SPSMDB-1537): the operator now publishes a ready-to-use MongoDB connection string (URI) in a Kubernetes Secret for the databaseAdmin user. An application can read that one Secret and connect to it, instead of building the URI itself from Pod names, Services, TLS settings, and credentials.
• Workload Identity for GCS backups (K8SPSMDB-1602): back up to Google Cloud Storage without storing a service-account JSON key in a Secret.
• Oracle Cloud Infrastructure Object Storage (K8SPSMDB-1644) and Alibaba Cloud OSS (K8SPSMDB-1519): two more native backup destinations.
• Restore a collection under a different name (K8SPSMDB-1603): use selective.nsFrom and nsTo to restore one collection alongside the live one for inspection or recovery.
• External nodes as arbiters (K8SPSMDB-1031): set arbiterOnly: true on an external node to place a tie-breaker vote in a third location without a data-bearing member.
• cert-manager ClusterIssuer and TLS policy (K8SPSMDB-1413, K8SPSMDB-1458): point the operator at an existing ClusterIssuer, and use certManagementPolicy to keep certificate lifecycle fully under your control.
• Tunable reconciliation interval (K8SPSMDB-1571): set RECONCILE_INTERVAL to reduce Kubernetes API load on large fleets (default 5s).
• Query Analytics via mongolog for PMM (K8SPSMDB-1546): choose mongolog as the QAN source in Percona Monitoring and Management.
• Custom sidecar health probes (K8SPSMDB-1701, K8SPSMDB-1728) and StatefulSet revisionHistoryLimit (K8SPSMDB-1572): finer control over probes and rollout history. 

For the full list, including bug fixes, see the release notes linked below.

Conclusion

Percona Operator for MongoDB 1.23.0 covers the arc from getting data in to keeping it safe: ClusterSync brings a live database onto the operator with a short cutover, vector search lets one system serve both documents and semantic queries, and PVC snapshot backups take the network out of the backup path. With RKE2 and full ARM64 support added, 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 MongoDB 1.23.0

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

The post Percona Operator for MongoDB 1.23.0: ClusterSync Migration, Vector Search, and PVC Snapshot Backups appeared first on Percona.

Percona Operator for MongoDB 1.23.0: ClusterSync Migration, Vector Search, and PVC Snapshot Backups appeared first on MariaDB.org

If you run MySQL Galera Cluster today, that gives you a real decision to make, and not much time to make it. The good news is that you have more than one option, and the one most teams overlook keeps you on MySQL.

You have two paths, not one

The deadline forces a move, but it does not force you onto MariaDB. There are two realistic destinations, and the difference between them is larger than it first appears, because one is a database engine change and the other is not.

The pattern holds across every row. MariaDB Galera Cluster moves you to a different database and asks you to rebuild around it, while PXC keeps the database you already have and changes what sits underneath it. That is what makes the move below a distribution change rather than a re-platforming.

“In-place” is still a database migration

MariaDB describes its path as near-zero downtime and in place. That is fair for the cluster mechanics, but it understates what is changing underneath. By MariaDB’s own migration documentation, moving to MariaDB Galera Cluster means a different system table structure, a fundamentally different data dictionary, and user accounts and privileges that are not mapped one-to-one and must be recreated by hand.

In other words, you are not upgrading MySQL Galera Cluster. You are moving to a different database that also happens to use Galera. For many teams that is a larger project than the “in-place” label suggests, with application testing, account re-creation, and a new server to operate and support afterward.

Why PXC is the natural landing spot

PXC treats this as continuity rather than conversion. It is MySQL, not a fork of it.

• It is built on Percona Server for MySQL, a drop-in compatible build of MySQL, so your schema, system tables, and user accounts carry over as they are.
• Its clustering uses the same Galera write-set replication model you already run, on Percona’s own open Galera fork, which we maintain and ship on our own schedule and on terms we control.
• It keeps strong binary compatibility with MySQL and Percona Server for MySQL, and integrates with Percona XtraBackup, Percona Monitoring and Management, and both Kubernetes and traditional deployments.

For a MySQL Galera Cluster user, that means the move is a server and distribution change inside the MySQL family, not a migration to a new database.

For a fuller version of this argument, see Marco Tusa’s personal take: The Galera Crossroads: Why PXC is the Lifeline for MariaDB Community Users.

Why PXC, not just the easier migration

Staying on MySQL is the practical argument. There is also a case for choosing PXC on the merits, independent of how much migration effort each path takes.

• It is genuinely MySQL, not a relative of it. PXC is Percona Server for MySQL, a drop-in compatible build of MySQL. MariaDB began as a MySQL fork but has diverged over the years and is no longer a drop-in replacement for MySQL. With PXC, your MySQL knowledge, queries, tooling, and application compatibility carry forward. With MariaDB, some of that has to be revisited.
• Open source, with nothing held back. Percona ships its software, including PXC and our Galera fork, as open source, with no enterprise-only tier gating the features you depend on. MariaDB operates as a commercial vendor, with proprietary and enterprise components alongside the community server. If freedom from lock-in is part of why you run open source databases, that difference matters.
• A steward with no competing database to sell you. This one is worth stating plainly. The company retiring the MySQL build of Galera is the same company recommending you move onto its own database. MariaDB owns Codership, the maintainer of Galera, and has set the end-of-life date while pointing those users to MariaDB Galera Cluster. Percona does not sell a competing database. Our interest is in keeping you successful on MySQL, which is the same interest you have.
• A long track record in the MySQL ecosystem. Percona has maintained MySQL-focused software for years, including Percona Server for MySQL, Percona XtraBackup, Percona Monitoring and Management, and Percona XtraDB Cluster, all under long-term support. Supporting MySQL users is not a new direction for us.

Taken together, the question is not only which migration is easier. It is which project is built around keeping MySQL open, compatible, and independent, and which one benefits from MySQL Galera coming to an end.

What the migration looks like

Because PXC stays within the MySQL ecosystem, the migration is a distribution change rather than a re-platforming exercise, so the work is mostly planning, testing, and a controlled cutover.

Before you start. Inventory your current cluster: the exact MySQL and Galera versions, node topology, wsrep settings, and any custom configuration. Confirm the PXC version that lines up with your MySQL version, so you are moving across a compatible boundary rather than changing major versions at the same time. Stand up a staging cluster that mirrors production, and capture a baseline backup with Percona XtraBackup before you touch anything.

The right approach depends mainly on how much downtime you can tolerate, ranging from a straightforward binary swap during a maintenance window to a near-online cutover for systems that must stay available. The full, step-by-step guide lives on docs.percona.com, where we keep it current as the tooling improves, and we will link it here once it is published.

Whichever path you take, the same disciplines apply: rehearse the whole thing on staging first, validate application behavior and query performance against PXC before production, and keep a tested rollback (a verified backup or an untouched source cluster) until you are confident. Plan any cutover for a low-traffic window and watch cluster and replication health closely for the first hours afterward.

Start before the deadline

September 30, 2026 is when maintenance and binary releases stop for MySQL Galera Cluster. Running an unmaintained cluster past that point means no security patches and no bug fixes, which is not where you want a mission-critical system to sit. The time it takes to test and validate a move now is worth far more than the risk of waiting.

If you are weighing your options, the short version is this: you do not have to leave MySQL to keep a supported, open source Galera cluster. PXC is here, it is maintained, and it is the closest thing to staying exactly where you are.

Talk to us

If you want help mapping out a migration path, sizing the work, or pressure-testing your high availability strategy, reach out to your Percona contact, post in the Percona community forums, or connect with our team directly. We are happy to walk through it with you.

Written by Dennis Kittrell. Reviewed by Michal Nosek and Marco Tusa.

MySQL, MariaDB, and Galera Cluster are trademarks of their respective owners. Percona is not affiliated with, sponsored by, or endorsed by these owners.

The post How to Migrate from MySQL Galera Cluster to Percona XtraDB Cluster appeared first on Percona.

How to Migrate from MySQL Galera Cluster to Percona XtraDB Cluster appeared first on MariaDB.org

Continue reading “Deploying the MariaDB Privacy-First Stack Anywhere with Terraform”

The post Deploying the MariaDB Privacy-First Stack Anywhere with Terraform appeared first on MariaDB.org.

Deploying the MariaDB Privacy-First Stack Anywhere with Terraform appeared first on MariaDB.org

Continue reading “From PostgreSQL 12 to MariaDB 11: A Gradual Fintech Migration with 23% Lower TCO”

The post From PostgreSQL 12 to MariaDB 11: A Gradual Fintech Migration with 23% Lower TCO appeared first on MariaDB.org.

From PostgreSQL 12 to MariaDB 11: A Gradual Fintech Migration with 23% Lower TCO appeared first on MariaDB.org

Continue reading “MariaDB 13.1 Feature in Focus: Validate Your Configuration Before Starting the Server”

The post MariaDB 13.1 Feature in Focus: Validate Your Configuration Before Starting the Server appeared first on MariaDB.org.

MariaDB 13.1 Feature in Focus: Validate Your Configuration Before Starting the Server appeared first on MariaDB.org

Using AI to modernize a Java project appeared first on MariaDB.org

Agenda

Let me start by making something clear: this is a complex topic, and this will be a long blog post.

I decided to simplify several things to the level where it is accurate enough, but still relatively easy to understand.
I also won’t go into implementation details, the maths behind statistics, and I won’t focus on generic topics like how to do low-noise benchmarking.

I am already planning on writing separate blog posts about some of these details in the future, but if you are interested in some of them, don’t hesitate to ask!
Feedback like that helps me figure out what topic to cover next.

As for this blog post, I want to cover the following topics:

• A generic introduction about the cost of encryption on modern CPUs
• A short description of how transparent data-at-rest encryption typically integrates into PostgreSQL
• An explanation of why relation encryption (encrypting the database objects) typically has no cost at all in most workloads, except a few specific operations
• And finally showcasing how the typical way of implementing WAL encryption in TDE solutions can cause performance degradation in high WAL-churn scenarios

Test setup

Benchmarks heavily depend on the computer used for running them.
All of my tests were performed on an AMD Threadripper 3970X (32 cores), using an Intel Optane P5800X SSD.

While some of the measurements can be reproduced on typical desktop hardware, not all of them can.
Some tests require many parallel workers and high memory bandwidth.
The more interesting tests all measure write performance, which is difficult on typical M.2 SSDs:
while some of them have peak write performance similar to Optane disks, they can only keep up with high write speeds for short bursts, quickly turning an otherwise CPU or memory limited test into an IO limited one.

Encryption is cheap… in isolation

When talking about encryption performance, technical people usually make one of two assumptions:

• Encryption is complex math we have to compute, so of course it will degrade our performance!
• We are using things like full filesystem encryption (BitLocker, LUKS), swap encryption or TLS all the time, it’s barely noticeable – encryption is cheap!

And both groups are kind of right:
Encryption is complex math, and it will degrade performance on older hardware.
But because it is so common and required for everything, modern hardware has a specialized instruction set (AES-NI) that highly optimizes it for most workloads.

The following table shows some single threaded measurements on my test computer, produced with small C benchmarks:

The three encryption modes mentioned above are commonly used in many scenarios:

• XTS is commonly used for full disk encryption, and also by some TDE implementations
• CTR is commonly used by TDE implementations, especially for WAL encryption
• GCM is an authenticated encryption algorithm which can provide both encryption and data integrity validation for TDE and other solutions

While the table doesn’t mention it, I also want to point out that all reads and writes are sequential using a 4kB block size.
This is important, because the performance of all of them degrades if we start using them differently: if we start encrypting much smaller blocks at a time, or keep reinitializing the stream with different parameters, the encryption numbers can degrade quickly.

We also have to remember that our goal isn’t to encrypt a random stream in memory – we have to integrate encryption into an existing database system with its own established architecture.
The above numbers showcase our bandwidth to encrypt or decrypt data if a CPU core is only working on that task.
In reality, the CPU will also be doing other things at the same time, and can’t spend all the time on encryption.

However, this won’t necessarily make things worse.
Most database workloads are not CPU bound, the bottleneck is usually either disk or memory bandwidth.
With AES-NI, encryption operations often don’t require additional memory bandwidth, which means that if a workload is already disk or memory limited, but we have free CPU cycles, we might get encryption for free or at little cost.

The real question isn’t how quick encryption is, but how optimally we can integrate it into PostgreSQL.

What are we encrypting exactly?

That means we no longer have a single question.
I can’t give a single answer to how fast is pg_tde, or any other data-at-rest encryption implementation?

Because a database does many things, reads and writes many different file types, and each of those has to be implemented differently.

In the case of pg_tde, we have two main areas:

• the encryption of database (relation) files
• and the encryption of the write ahead log

Other TDE implementations might encrypt other files too, for example temporary files, but I want to focus on the two areas supported by pg_tde, as these are the most significant from a performance perspective.

So let’s look into the details of these separately.

The relation files

The quick summary for those only interested in the numbers:
the impact for this is very little – for most operations, in the very difficult to measure category.
The only exception to this is a few single threaded write heavy workloads, such as CREATE TABLE AS SELECT ..., VACUUM FULL, an UPDATE that updates all or most rows, or an ALTER TABLE that rewrites the entire table. In these, we can measure a 5-30% performance drop.

For other operations, such as typical sysbench workloads, or even specific tests like single worker sequential reads, that number is 2% or less.
Typical measurements usually have some noise, a few percent even for properly configured setups, and much more for “let’s just quickly execute sysbench”.
Something in the 1-2% range can only be measured with specific server and hardware configuration, not in real-world setups.

The reason behind this is quite simple:
Relation file reads and writes usually happen in entire blocks, with an 8k default size for PostgreSQL – it is very similar to OS level file system encryption.

A typical data-at-rest encryption implementation usually operates at the IO level: it encrypts immediately before we write a dirty buffer to disk, and it decrypts immediately after we read something from disk.

Decryption only happens when we actually have to read from disk. If the requested pages are already in the shared buffers, they are already decrypted, so there’s no effect there.
Encryption only happens when we are writing to disk. In a workload that isn’t very write-heavy, all writes happen in the checkpointer and background writer, and backend processes never perform page writes directly, so encryption won’t affect the QPS numbers at all.
Even if a workload is so write-heavy that the server has to move some of the writes to the backend process, most likely the backend isn’t CPU-bound, since most database workloads are either memory or IO limited.
Unless we are writing a huge volume of data, such as the examples mentioned above, encryption won’t be noticeable at all in these scenarios.

This read-write behavior is similar to full file system encryption, where the OS caches behave similarly, unless we keep calling fsync explicitly for writes.

Because the effect of encryption is so little, we have to be really careful how we set up our test environment, even for the heavy write scenarios where we can notice a somewhat larger difference.
It’s very easy to get this wrong, and then just measure noise.

For example, let’s say that we don’t want to spend too much time on initializing the dataset, and we only generate a 20GB dataset.
We also set the shared buffers to 16GB because our test PC has more than enough RAM.
This seems reasonable, but the problem is, once the data is loaded into the shared buffers, it will be permanently decrypted there, and 80% of our dataset fits into the buffers.
Most read operations won’t have to go to the disk, they find the requested page in the buffers, so we are not measuring encryption performance for them.

We might decide to keep the small dataset, and just use a small number for shared buffers, but then we are moving away from a production-like setup: is it realistic to run PostgreSQL with 128MB shared buffers in a real environment?
Can the reduced shared buffer size affect the performance in some other ways?

And then we have to think about similar questions for writes.

Regardless of what we do, as I mentioned above, this is basically the same use case as file system encryption, and we already know that that’s fast.
If we are testing an encryption implementation, and we can see a significant performance degradation with only data file encryption, we found a bug, and should report it.

Why is CTAS so slow?

At the beginning of the previous section, I mentioned a few examples where we can measure a difference.

In my tests, I see the following worst-case performance degradation with them:

These are all worst case numbers I was able to produce. This doesn’t mean that all full table UPDATE operations will take 10% longer.

Also, in the case of UPDATE, the 10% measurement includes the time of the CHECKPOINT, as depending on the exact server configuration, some or most of the writes still happen in the checkpointer and background writer.
The actual execution time of the query in the backend process was the same with and without encryption in all of my UPDATE measurements, unless I intentionally misconfigure the server.

A CTAS that simply copies a table is the worst performer because it doesn’t have to do anything else.
The other operations all have to do something extra with the data, but if we just duplicate one database table, then we normally only perform IO:
read a page, write the page, repeat.

Except in our case, we have to read a page, decrypt the page, encrypt the page, write the page.
We have to call encryption operations twice for every page: with AES-128-CTR at 8 GB/s, decrypting and then encrypting each page halves the effective bandwidth to around 4 GB/s, well below the disk speeds in my test setup.
A CTAS operation is normally IO limited, but because of this doubled encryption work, it becomes encryption limited with TDE, even with the AES-NI instruction set.

I also want to explicitly repeat an important note from the beginning:
this result requires a sustained disk IO that is faster than the CPU’s encryption speed.
Consumer grade SSDs can’t sustain such speeds for long workloads, only for short bursts.
It is only possible to reproduce this measurement on them with small datasets.
With larger tables, the test will be limited by disk IO, not encryption.

The write ahead log

This is where things get more interesting.
In my previous tde related blog post, I mentioned that we are working on some encryption benchmarking, and that was about WAL performance.
Also, one of the main changes in the upcoming pg_tde 2.2.3 release will be a significant improvement in this area.

To start with a similar summary:

• in my most-performant benchmark prototype, I can’t measure a significant degradation even in a worst-case scenario workload
• with pg_tde before 2.2.3, the same test results in only 60% throughput, or a 40%+ performance degradation
• with pg_tde 2.2.3, we were able to improve that test to the 80% range, reducing the performance degradation to 20%

The 2.2.3 change (already merged, the release is upcoming) improves our current WAL encryption approach. The complete fix described later in this post is still in development.

This list also needs two important footnotes:

First, this is a “worst-case scenario”, not something typically executed in production workloads.
It is a synthetic scenario exactly to stress test WAL bottlenecks.
In a simple OLTP read-write scenario, there’s only a few percent measurable difference for all pg_tde versions, less than 10%.

Second, this is a concurrency issue, and reproducing it requires a high core count test PC.
In my test setup, I have 32 cores available.
On different hardware, the results will be different.
With 100+ core monster hardware, and even more threads, the performance degradation is most likely even worse for most implementations of WAL encryption, but I didn’t perform tests on such setups.

Understanding the issue

To understand why this is so different from data file encryption, we have to look into how WAL works:

1. When we perform any WAL logged write, the changes are first written to WAL, that’s why it’s called a write ahead log.
2. Even more specifically, the backend process (the server’s handler for the specific client session) first constructs what it wants to write into the log
3. After that, in PostgreSQL we only have a single WAL log, and only one backend can flush it at a time.
   The backend process has to request a lock on the WAL, preventing other processes from interacting with it at the same time.
4. After writing the already constructed data to the in-memory buffer, we have to write it to disk and then immediately flush the buffer to disk, to make sure that it is durable, since this is the log we are using for crash recovery.
5. It can release that lock only after that write/flush was done.
6. After releasing the lock, another backend can take it, repeating the process from (3)

sequenceDiagram
participant B1 as Backend 1
participant B2 as Backend 2
participant L as Single WAL lock
B1->>B1: construct WAL record
B2->>B2: construct WAL record
B1->>L: acquire
Note over B1,L: encrypt + write + flush
(everyone else waits)
B2--xL: blocked
L-->>B1: release
B2->>L: acquire
Note over B2,L: encrypt + write + flush
L-->>B2: release

The above information is a bit oversimplified, as WAL writes are more complex than this, but it is already enough to spot the concurrency issue hidden in it:
only one process can write/flush the WAL at a time, so no matter how many cores we have, it won’t get faster.
In fact it is the opposite, since higher core count CPUs usually have worse single-thread performance.

Most WAL encryption implementations, including pg_tde, implement it similarly to data file encryption:
we encrypt the WAL data immediately before writing it to disk, at the time when the backend already acquired the WAL lock.
Not only do we do additional computations for the current session, we also prevent other sessions from doing anything during this extra time.

In my test setup with the above numbers, WAL writes were already CPU bound without encryption.
Even if encryption is relatively cheap, if we don’t have spare cycles, it will show up.
That’s bad enough already with a good implementation, but if we also manage to introduce a performance-hurting bug in this area of the code, we can easily end up with quite bad numbers.

Is this completely fixable?

As I hinted at the beginning of the WAL section, this issue is fixable.
WAL encryption doesn’t inherently require encrypting more data than data file encryption does. In fact, if done correctly, most of the time we’ll have to encrypt even less.

The problem is that we have to do it in a more challenging part of the code.

From the above description, it might already be clear:
we should encrypt the data after we constructed it, before taking the WAL lock!

Current: encrypt inside the lock

flowchart LR
A[construct record] --> B[acquire lock]
subgraph lock [lock held, serialized]
direction LR
C[encrypt] --> D[write + flush]
end
B --> C
D --> E[release lock]

Fixed: encrypt before the lock

flowchart LR
A[construct record] --> C[encrypt]
C --> B[acquire lock]
subgraph lock [lock held, serialized and shorter]
direction LR
D[write + flush]
end
B --> D
D --> E[release lock]

That solves both the concurrency limitation and another problem hidden there:
imagine that we are writing many small records, one per transaction.
A single WAL record might be less than 100 bytes, but a WAL page is 8kB.

Even if we do it at flush time, we don’t have to encrypt the entire page, only what we filled so far, but on average even that results in 4kB data per flush.
With a 100-byte WAL record, we can fit around 80 records into a single WAL page.
We have written 8kB of real data, and encrypted 320kB of WAL to do it.
There are of course some possible optimizations there, for example we completely ignored group commit, which will likely make that 320kB number much smaller, but it still remains significantly more.

While moving the encryption before the lock might seem like an easier choice, it has different challenges.
For example, there’s one I already mentioned in the beginning: if we start encrypting small blocks (and also changing the stream configuration, which is also related to this), we get worse encryption performance.

The naive implementation of this approach performs even worse than our earlier pg_tde implementation.
However, if we apply some optimizations to it, it can reach similar “unmeasurable” levels as the data page encryption.

This improvement is not yet included in pg_tde, as it requires a completely different approach to WAL encryption compared to our previous implementations, and we haven’t yet finished testing and measuring it.
Mainly, in this blog post I only focused on the performance of writing the WAL, but we have to remember that we also have to read it in some cases.
While the speed of crash recovery isn’t that important in a happy scenario, when something bad happens it does matter how quickly we can get our server running again.

What’s the test scenario?

Similarly to the write bottlenecks above, a normal OLTP read-write workload won’t showcase significant degradation because of WAL encryption.
This is exactly why I wrote such a long post: the interesting behavior only shows up in scenarios a quick benchmark never exercises.

We could simply publish a nice graph showing the typical numbers, claiming that our encryption is fast.
We could treat the performance problem we fixed as a small footnote in our changelog, as it doesn’t show up at all in that measurement.

But that wouldn’t be honest, because the test setup and test scenarios do matter, and when we talk about performance, we should mention worst-case numbers.

To reach these bad numbers, we have to do one thing:
generate a huge amount of WAL across many sessions.
This is doable in many different ways, ours is basically the following:

1. create a few tables, each with a few million rows
2. start executing UPDATE t SET c=c+1 WHERE id IN (SELECT id FROM t ORDER BY random() LIMIT 100);
3. run step 2 in many sessions

The query above is a simplified illustration. The actual test used sequential IDs, with the 100 random IDs generated by the benchmark tool and inserted directly into a prepared statement, so selecting the rows stays cheap and the workload is dominated by the WAL writes.

PostgreSQL has an option called full_page_writes, which is enabled by default.
With it, when we modify any database page for the first time after a checkpoint, it doesn’t only WAL log the modified row, instead it logs the entire page, 8kB.
In the above query, for every update, we modify 100 randomly selected rows in a table.
Because they are randomly selected from millions of rows, they have a good chance of being on different pages.
Let’s say that with a specific dataset, on average we hit a 10% new page rate – one update has to do 10 full page writes.
That means every single UPDATE we execute generates more than 80kB of WAL.

This is again an oversimplification, but it is a good enough approximation without going into details too much.

With 15,000 QPS, that’s around 1.2 GB/s of WAL.
From the table at the beginning, on my test PC AES-128-CTR has around 8 GB/s single core bandwidth.
1.2 GB/s is 15% of that – and that assumes no context switching or other losses, meaning no matter how good the integration into the database is, if we do encryption at this volume while holding a global lock, we have to expect at least 15% performance drop with these numbers.
In practice it’s a bit more than that.

Summary

I hope this explanation was more useful than a single graph showcasing how good our performance with pg_tde is.
The worst-case scenarios I described here are corner cases, most servers don’t generate sustained gigabytes per second of WAL, even in production.

But I still think this is important to mention, as it is an easily overlooked detail caused by the combination of PostgreSQL’s WAL architecture and the simple way most vendors add encryption to it.

I also didn’t go into detail on many parts of this post.
If I tried to explain everything in absolute detail, this would be ten times longer, and much harder to follow and understand.
I do plan to touch on some of these subjects in separate blog posts, where we can focus only on those topics in more depth.
If you have questions or suggestions about the topic, don’t hesitate to reach out using our community forums!

TDE performance in PostgreSQL appeared first on MariaDB.org

Schema design in ClickHouse is one of those tasks that appears to be a simple, one-time setup activity, but it often becomes a recurring operational concern. When the schema is designed poorly, problems tend to surface over time, including slow queries, oversized partitions, and mutation jobs that run for hours while production traffic continues to grow. 

In this article, we will cover the core concepts of ClickHouse, systematically walk through design decisions, from core concepts and operational patterns to monitoring and evolution, with the goal of giving you a framework for making and maintaining schema decisions in production.

Core Concepts for ClickHouse Schema Design

Distributed Tables, Local Tables, Shards, and Replicas

Before writing a single CREATE TABLE, it helps to have a clear mental model of how ClickHouse actually stores and serves data across a cluster. ClickHouse divides data across shards with each shard holding a horizontal slice of the total dataset. Each shard can have one or more replicas for fault tolerance. The replicas within a shard hold identical data; the shards themselves hold different data.

The two table types you’ll work with constantly are:

• Local tables (ReplicatedMergeTree and its variants): the actual storage layer. Each node stores its own local table containing its shard’s data. Queries against a local table only see that node’s data.
• Distributed tables (Distributed engine): a logical routing layer that sits on top of the local tables. When you query a distributed table, ClickHouse fans the query out to all shards, collects the results, and merges them. Distributed tables don’t store data themselves.

N.B. schema changes need to be applied to local tables on every node, and the distributed table definition needs to match. It sounds obvious, but it is a common source of confusion when onboarding teams who are used to a single-server database.

Shard key selection matters for data distribution. A poorly chosen shard key (or rand() used as a lazy default) can lead to uneven data distribution, e.g. one shard holding 60% of the data while others hold 20% each — this creates hot spots and makes capacity planning unreliable. The shard key should distribute data evenly and, ideally, align with how you query, if most queries filter by tenant_id, sharding by tenant_id means queries for a single tenant hit one shard instead of all of them.

Partition Key and Primary Key (Sparse Index)

These two concepts trip up almost everyone coming from a relational background, because they sound like the same thing but serve entirely different purposes in ClickHouse. The partition key controls how data is physically divided into separate directories on disk. Each partition is stored and managed independently, which means:

• Queries that filter on the partition key can skip entire partitions without reading them (partition pruning)
• Old data can be dropped by dropping a partition, instant, no heavy delete operation
• Background merges only happen within a partition, not across them

For time-series data, partitioning by month (toYYYYMM(event_time)) is the most common pattern. It gives you clean data lifecycle management (drop old months instantly) and good pruning behavior for time-bounded queries.

The primary key in ClickHouse is not a uniqueness constraint, it’s a sparse index. ClickHouse stores one index entry per 8192 rows (one granule), not one per row. This makes it memory-efficient even at billions of rows, but it means the primary key is designed for range scans and filtering, not point lookups.

The ORDER BY clause defines the physical sort order of data on disk, and the primary key must be a prefix of ORDER BY. This is worth saying clearly: the sort order is what makes your queries fast or slow. If your most common query filters by (tenant_id, event_type, event_time), your ORDER BY should reflect that. Data is stored sorted by those columns, so ClickHouse can skip irrelevant granules efficiently.

Here’s a concrete example that puts these together:

CREATE TABLE events_local
(
    tenant_id     UInt32,
    event_time    DateTime,
    event_type    LowCardinality(String),
    user_id       UInt64,
    session_id    UUID,
    properties    String,
    ingested_at   DateTime DEFAULT now()
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/events',
    '{replica}'
)
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_type, event_time)
SETTINGS index_granularity = 8192;

A few decisions that can be taken as below:

• LowCardinality(String) for event_type, if this column has fewer than 10,000 distinct values, this encoding dramatically reduces storage and speeds up filtering.
• PARTITION BY toYYYYMM(event_time), monthly partitions, suitable for a 12–18 month hot data retention window.
• ORDER BY (tenant_id, event_type, event_time), optimized for queries that filter by tenant first, then by event type, then narrow by time range.
• The ZooKeeper path uses {shard} and {replica} macros so the same DDL can be run on every node without modification.

Materialized Views and Aggregated Tables

Materialized views in ClickHouse are not the same as in PostgreSQL. They are real-time incremental aggregations; every time data is inserted into the source table, the materialized view processes those rows and writes the aggregated result to a target table. There’s no scheduled refresh and it happens synchronously with the insert.

This makes them powerful for pre-computing aggregations that would otherwise require scanning billions of rows at query time. A common pattern is to maintain hourly or daily rollup tables alongside the raw events table. 

For example, create the target table for aggregated counts and later create the MATERIALIZED VIEW with aggregation.

CREATE TABLE events_hourly_agg
(
    tenant_id    UInt32,
    event_type   LowCardinality(String),
    hour         DateTime,
    event_count  AggregateFunction(count, UInt64)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (tenant_id, event_type, hour);



CREATE MATERIALIZED VIEW events_to_hourly
TO events_hourly_agg
AS
SELECT
    tenant_id,
    event_type,
    toStartOfHour(event_time) AS hour,
    countState() AS event_count
FROM events_local
GROUP BY tenant_id, event_type, hour;

Operationally, materialized views add write amplification, every insert into the source table triggers a write to the view’s target table. For high-ingestion workloads, this is worth monitoring. They also need to be maintained when the source schema changes, which is often forgotten until something breaks.

Operational Design Patterns

Time-Series and Event Analytics Schemas

The vast majority of ClickHouse deployments are built around time-series or event data clickstreams, application logs, metrics, IoT sensor readings. This is where ClickHouse’s design shines, and there are well-established patterns to follow.

The core principle is time as the primary organizing dimension. Partition by time (monthly or weekly depending on data volume), and include event_time in the ORDER BY so range scans are efficient. Keep raw events immutable and resist the temptation to update them in place.

For retention management, the TTL clause handles automatic expiry without manual intervention:

TTL event_time + INTERVAL 90 DAY DELETE

TTL event_time + INTERVAL 30 DAY TO DISK 'cold_storage'

The first script automatically deletes rows older than 90 days while the second scripts move cold data to a cheaper storage tier. This is operationally cleaner than scheduled delete jobs, which in ClickHouse would trigger heavy mutations.

Bulk Ingestion vs. Real-Time Streaming

How data arrives significantly affects schema and operational behavior. ClickHouse handles both, but they stress the system differently.

Bulk ingestion, i.e. large batch inserts; for example, nightly ETL from a data warehouse, is relatively forgiving. ClickHouse is designed for large INSERT batches, each batch creates one or a few data parts, and the background merge process handles compaction. 

The risk is inserting too many small batches in rapid succession, which creates a flood of tiny parts that overwhelm the merge queue. The rule of thumb is: batch size matters more than frequency. Aim for inserts of at least 10,000 –100,000 rows per batch.

Real-time streaming via Kafka requires more care. The ClickHouse Kafka table engine or tools like Vector/Benthos handle ingestion, but the operational concern is the same: small, frequent inserts create merge pressure. Configure consumers to buffer and batch messages before inserting, and monitor system.parts for signs of part accumulation.

SELECT
    table,
    count() AS part_count,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS disk_size
FROM system.parts
WHERE active = 1
GROUP BY table
ORDER BY part_count DESC;

A healthy table has tens to low hundreds of active parts. Thousands of parts is a warning sign that inserts are too small or merges are falling behind.

Multi-Tenant Schema Isolation

If your ClickHouse cluster serves multiple tenants, you need to decide early how to isolate their data. The main options are:

• Database-per-tenant: each tenant gets their own database (and potentially their own set of tables). Clean isolation, simple access control, but doesn’t scale past a few dozen tenants without becoming a management burden.
• Table-per-tenant: all tenants share a database, each with their own table. Works at moderate scale but schema changes need to be applied to every tenant table, which is operationally painful at hundreds of tenants.
• Shared table with tenant_id column: all tenant data in one table, filtered by tenant_id. This is the most operationally maintainable pattern at scale. The key requirement is that tenant_id must be the leading column in ORDER BY so that per-tenant queries efficiently skip irrelevant data without a full scan.

ORDER BY (tenant_id, event_type, event_time)

With this sort order, a query filtering on tenant_id = 42 skips all granules that don’t contain that tenant’s data, making it effectively as fast as if the table contained only that tenant’s rows.

Schema Evolution and Operational Impact

Adding Columns, Partitions, and Handling Mutations

Schema changes in ClickHouse are generally safer than in OLTP databases, but they are not without operational cost. Adding a column is fast and non-blocking. ClickHouse uses lazy evaluation, the new column returns a default value for existing rows without rewriting data on disk. It is one of the rare DDL operations you can run in production without much anxiety:

ALTER TABLE events_local ON CLUSTER my_cluster
ADD COLUMN geo_country LowCardinality(String) DEFAULT '';

Dropping a column triggers a background data rewrite (mutation) to remove that column from existing parts. This is heavier and can be slow on large tables. Mutations are expensive in ClickHouse. For example when you run the following command, ClickHouse does not update the row in place but finds all parts with the matching condition, creates new versions of those parts with the modification already applied, replaces old parts after processing and continues serving queries while mutations run in the background.

ALTER TABLE events_local
UPDATE status = 'processed'
WHERE id = 123; 

The guidance here is simple: avoid mutations in hot paths. For data corrections, prefer inserting corrected rows and using a ReplacingMergeTree or CollapsingMergeTree engine to handle deduplication, rather than updating rows in place.

If you must run a mutation, monitor its progress:

SELECT
    command,
    parts_to_do,
    is_done,
    latest_fail_reason
FROM system.mutations
WHERE table = 'events_local' AND is_done = 0;

Monitoring Schema-Related Issues

Identifying Slow Queries and Partition Problems

The most useful table in ClickHouse for day-to-day schema health monitoring is system.query_log. Queries that are reading an unexpectedly high number of rows relative to what they return are usually a sign of poor partition pruning or an ORDER BY that doesn’t align with the filter. Skipping indexes (secondary indexes in ClickHouse) are often added with good intentions but not actually used. Check whether they’re being utilized by execute the following:

SELECT
    table,
    name,
    type,
    expr
FROM system.data_skipping_indices
WHERE database = 'mydb';

Then cross-reference with system.query_log to see if queries against that table are actually benefiting, if read_rows remains high after adding an index, it may not be matching the query pattern.

Capacity Planning for Growth

Schema decisions have long-term storage implications that are not always obvious at design time. A few metrics are worth tracking regularly, such those included in this storage growth per table over time monitoring query below:

SELECT
    table,
    formatReadableSize(sum(bytes_on_disk)) AS total_size,
    sum(rows) AS total_rows,
    count() AS part_count,
    max(modification_time) AS last_modified
FROM system.parts
WHERE active = 1 AND database = 'mydb'
GROUP BY table
ORDER BY sum(bytes_on_disk) DESC;

Track the table with total size, rows, partition count on a weekly basis and plot the trend. A table that grows 20% month-over-month with a 90 day TTL will eventually reach a stable size but a table with no TTL and unbounded growth will eventually cause disk pressure that affects the entire cluster.

Partition-level granularity is also useful for anticipating when TTL drops will occur and what storage they will free:

SELECT
    partition,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(rows) AS rows,
    count() AS parts
FROM system.parts
WHERE active = 1 AND table = 'events_local'
GROUP BY partition
ORDER BY partition DESC;

The above query shows the size per partition for the ClickHouse table events_local. 

Integrating with Your Multi-Database Environment

Data Flow from OLTP to ClickHouse

Most ClickHouse deployments exist downstream of an OLTP database. Orders come in through PostgreSQL, user events flow through MySQL, and ClickHouse ingests and aggregates that data for analytics. This pipeline introduces a class of schema problems that don’t exist in single-database setups.

The OLTP schema and the ClickHouse schema should not be the same schema. OLTP tables are normalized, they are designed to minimize write amplification and enforce referential integrity. ClickHouse schemas are deliberately denormalized, trading write efficiency for read efficiency. A join that’s trivial in PostgreSQL can be expensive in ClickHouse at scale, so the right pattern is to resolve joins at ingestion time, pushing denormalized, enriched records into ClickHouse rather than replicating normalized tables and joining at query time.

This means the ingestion pipeline, whether it’s Kafka, Debezium CDC, Airbyte, or a custom ETL, is also a transformation layer. Fields get renamed, types get cast, related records get joined and flattened, and low-cardinality string fields get encoded appropriately. Operationally, this pipeline is part of the schema: changes to it have the same impact as changes to the table definition.

Managing Model Changes, Versioning, and Rollback

When the upstream OLTP schema changes eg: a new column added to orders, a field renamed in users. The downstream ClickHouse schema and the ingestion pipeline both need to change in a coordinated way. Without a versioning discipline, these changes become brittle and difficult to roll back — a few practices that hold up well in production:

• Treat DDL as code: The schema changes should live in version-controlled migration files (tools like Flyway or a custom migration runner), not applied ad-hoc from a SQL client. Every ALTER TABLE that went to production should be traceable to a commit.
• Add before you remove: When renaming a column or changing a type, add the new column first and allow both the old and new column to coexist during a transition window. Update the ingestion pipeline to write to both, then cut over queries to the new column, then drop the old one. This avoids a hard cutover that can’t be rolled back.
• Schema rollback is hard therefore plan for it: Dropping a column or partition key change is not easily reversible. Before applying significant schema changes, take a backup of the affected table (or at minimum its most recent partition) so that recovery is possible without a full cluster restore.
• Document the lineage: For each ClickHouse table, maintain a short document describing where the data comes from, what transformations are applied, and what downstream queries or dashboards depend on it. When a schema change is proposed, this lineage makes the blast radius obvious before anything is applied.

Conclusion

ClickHouse schema design is not something that you set once, but is something you evolve over time. The implication is that choices you make when creating a table today do not just affect today’s queries but quietly shape how your system performs months down the line, from how efficiently queries run to how painful or painless future schema changes turn out to be.

Some points worth keeping in mind when designing the schema and data modeling: design your ORDER BY for readers, not writers; structure your sort key around how people query the data, not around the order it arrives in; partition by time and but avoid slicing things so finely that merge overhead becomes its own problem; Be deliberate with data types. LowCardinality and AggregateFunction are powerful tools, but only when applied with clear intent. Reaching for them out of habit rather than purpose tends to backfire. Your ingestion pipeline is part of your schema — how data flows in isn’t separate from how it’s stored, think of them as one connected system. 

And remember, keep an eye on the correct metrics from the start. Schema issues seldom make themselves known in an obvious way. Identifying them through regular monitoring is much less expensive than dealing with the aftermath. The central idea here is that decisions regarding schemas have a cumulative effect. Positive choices subtly simplify all other aspects, while negative ones discreetly complicate them.

The post ClickHouse Schema Design and Data Modeling appeared first on Severalnines.

ClickHouse Schema Design and Data Modeling appeared first on MariaDB.org

At Percona, we genuinely love getting out of our daily routine to learn what’s new. It is incredibly refreshing to escape the daily grind of sprints and stand-ups just to listen and absorb. Hearing how other teams are tackling scale and complexity reminds us that we are all solving different flavors of the same core problems. These events act as a catalyst for innovation, sparking conversations that inevitably push our own boundaries. Ultimately, taking a couple of days to zoom out and see where the industry is heading helps us build what Sandra calls a mental index – concepts that might not solve today’s ticket, but will absolutely pay off six months from now.

While we took notes across a huge variety of topics, one common thread stood out above the rest. This post is our attempt to share that index with you, with links so you can dig deeper into whatever catches your eye.

Spoiler: if you played buzzword bingo with “agentic AI,” you’d have won in the first hour.

The one big theme: Agentic AI (surprise!)

Every conference right now has an AI theme, but WAD went deep: adoption stories, best practices, building and optimizing RAG pipelines, and – importantly – what happens to security when agents write and ship code.

Our personal top takeaways:

• Security matters more than ever in the age of AI. More on that below – this one deserves its own section.
• Agents are only as good as their context. Intent, mission, purpose, style, goals, success metrics – the teams winning with AI are the ones writing this down for their agents.
• Understand why you do what you do. Agents won’t think about that for us. The engineering judgment moves up the stack; it doesn’t disappear.

“The SDLC is dead” – the Agentic Assembly Line keynote

Thomas Dohmke (CEO of Entire, previously CEO of GitHub) opened with a demo-filled keynote about where developers and agents are headed. A few numbers that made the whole room sit up:

• Teams using coding agents ship 5× more code (measured by PRs), and PRs are 3× bigger than 18 months ago.
• 20%+ of AI-generated changes are accepted without human review. (Brave? Terrifying? Discuss.)
• Some products – Codex, notably – are now written only by AI.

His conclusion: the Software Development Lifecycle as we know it is dead. The DevOps loop is evolving into what he called “The Ralph Loop” – a much faster path from code to production, with developers acting as verifiers, because agents still fail often. The winning team, in his view, is the one whose agents understand the company’s mission, values, and purpose.

One practical problem he highlighted: when you code with agents, context fragments across chats, prompts, sessions, and branches. Tools like entire.io now store the chat sessions behind each PR right in the GitHub repository – so the intent behind a change doesn’t evaporate. You may want to check this tool out! Let’s see how the GitHub, we know today, evolves over the next decade.

Retrieval is the weakest link in your RAG

One of our favorite technical talks, by Tomek Porozynski (deepsense.ai), tackled the “R” in RAG (Retrieval Augmented Generation). General embedding models are trained on public data – they know general language, not your business.

Basic retrieval falls short: keyword search misses semantic context, and vector search misses exact terms, multi-step logic, document-wide context, and internal jargon. There’s no single fix – you pick the right tool for the job.

For domain-specific knowledge, the fix is to fine-tune the embedding model on your own domain, so the vector space itself shifts to reflect your terminology and the real relationships between your terms.

The mechanics are surprisingly approachable: reshape the vector space through relative distances (pull matching pairs closer, push mismatched pairs apart), using either triplet loss or MultipleNegativesRankingLoss – and with the Sentence Transformers toolkit, the latter is literally one import away.

If you want to try it yourself, the speaker shared hands-on Colab notebooks.

The security wake-up call: surviving the “Vulnpocalypse”

Adrian Mouat (Chainguard) delivered the talk that stuck with us the most. Advanced AI models can now autonomously discover and weaponize zero-day vulnerabilities at machine speed – effectively erasing the traditional patch window. Especially with the rise of Anthrophic Mythos model, this might lead to Vulnpocalypse!

This is very serious for open source: attackers can point LLMs at public codebases, while underfunded maintainers face an overwhelming volume of newly discovered bugs. As people who live and breathe open source databases, this hits close to home.

The defenses he proposed:

• Fight AI with AI – proactively scan your own infrastructure and find vulnerabilities before attackers do.
• Minimize your attack surface – fewer dependencies, and consider AI-written snippets over pulling in vulnerable third-party libraries.
• Strict hygiene – immediate patching and eliminating long-lived access tokens are non-negotiable.
• Industry coalitions – rapid-response groups like Athena share mitigations at machine speed, while “Akrites” safely funnels fixes back into upstream open source projects.

At Percona, we’re already evaluating joining these coalitions – stay tuned!

Related: Isha Salania (Microsoft) showed how confidential computing extends encryption to data in use – your prompts, retrieved chunks, and keys living in encrypted memory. For anyone building sovereign RAG systems on top of databases, this end-to-end view of data protection is worth understanding – and it’s going to raise expectations for queryable encryption across the whole database ecosystem.

“MCP doesn’t suck – your agent does”

Best talk title of the conference, courtesy of Jan Curn (Apify). The problem: most agents load all available tool schemas into the context window upfront, causing context rot, slow performance, and rapidly burning tokens. Their answer is mcpc – a lightweight CLI that enables progressive tool discovery: the agent fetches only the tool schemas it needs, on demand, and chains workflows through native code execution. Add OAuth 2.1 and sandboxed proxy connections, and you get a much saner MCP setup.

More gems worth your time

• From SDLC to ADLC. Marcin Wawryszczuk (Andersen) argued that AI speeds up coding but not the release cycle – the industry needs an Agentic Delivery Lifecycle where agents help with requirements, architecture, docs, and pipelines, while engineers keep authority over architecture, governance, and validation.
• Don’t lock in your AI tooling too early. Angie Jones shared how Block bought access to many tools and let engineers run with them – what works for a web developer doesn’t work for a mobile or JVM developer, and that diversity of feedback is gold. Standardize when you see workflows succeeding repeatedly, not because a vendor made a good pitch.
• LLMs in the wild. GetYourGuide’s data scientist Giampaolo Casolla and MLOps engineer Steven Mi walked through keeping an AI-driven recommendation system alive in production. Real numbers, real trade-offs.
• Platform-as-a-Product. Dominik Schmidle (Giant Swarm) on why internal platforms fail: happy users won’t save your platform if the C-level sees it as pure cost. Know your user and your decision-maker – and do internal marketing.
• Werner Vogels (CTO, Amazon) fireside chat. Invisible work is important and worth sharing. Stay curious, never stop learning – and he recommended the book Ask Your Developer by Jeff Lawson.

The expo floor

Between the talks, we’ve also hung out at the Percona booth – yes, we’ve been there the entire two days and chatting with 100+ visitors about what we love the most – databases!

We’ve also visited our neighbours at the expo hall and had great conversations with them, too – but there was one that stood out:

Qodo (formerly CodiumAI): AI code review that indexes your entire repository, so reviews understand the architectural “why” behind a change – and it’s free for open source projects. We’re excited to try it on our own projects.

Parting words

Two quotes from the WeAreDevelopers founders stayed with us. From CPO Thomas Pamminger:

AI didn’t make me worse at my job – it made it easier to be worse without noticing. Charles Eames, asked what he’d delegate, said: never the understanding.

And from CEO Sead Ahmetović:

Someone, somewhere, will depend on what you ship next. That’s not a burden – that’s the whole point, because your work matters. Let’s do it well.

That’s a pretty good summary of why we go to these events: to keep understanding, not just shipping.

If any of the topics above sparked something – RAG fine-tuning, supply chain security, vector search in databases – chat with us on the Percona Community Forum or just drop a comment here. We’d love to hear what you took away from WAD if you were there.

See you at the next event!

Perconians at WeAreDevelopers World Congress 2026: Agents Everywhere, Security Wake-Up Calls, and Buzzword Bingo appeared first on MariaDB.org

That’s what Mountaineers are. It’s how we recognize the time and energy you put into this community — and reward it.

Why we built this

Every contribution to this community costs you something: time, expertise, patience. Writing reproduction steps for a bug isn’t free. Neither is answering the same kind of question for the fifth newcomer this month, or sitting down with our engineering team to walk through how you actually use Percona Operators in production.

We see that. Mountaineers are our way of tracking it properly and giving something back — recognition, access, and yes, swag.

What counts

This isn’t just about code. If you’ve assumed that contributing means opening a pull request or nothing, that’s not how this works. Points come from:

• GitHub — issues, PRs, and merged contributions
• Forum — starting discussions, replying, and accepted solutions
• Content — blog posts, tutorials, and video appearances, including through our Community Writers Program, where you also get paid for published posts
• Direct feedback — 1:1 sessions with our engineering team and survey responses

That last one matters more than people think. If you want to tell us what works, what doesn’t, and how you’re actually using our tools day to day, we want that conversation. Talk to engineering directly, or write it up for the blog. Either way, it counts.

How the climb works

Everyone starts at Basecamp. From there, the more you contribute — and the more places you contribute — the higher you climb. Show up across GitHub, the forum, and content in the same month, and your points multiply. We’re not trying to make this complicated: more engagement, more recognition, faster.

Points convert into real rewards. Stickers and digital badges at the entry tier. T-shirts and water bottles as you climb. Hoodies and tech accessories further up. All those who begin the climb will receive a serialized Challenge Coin — the kind of thing you can’t buy, only earn.

The people who consistently show up across the board get invited to take part in Percona Live: roadmap sessions, early access, time with the people building the tools you use.

You don’t need a long resume to start

If you’ve filed one bug report with clear reproduction steps, answered one forum question, or have an opinion about a tool you use that you’ve never told us — you already have something to bring. We built Mountaineers to recognize the full range of ways people show up, not just the most visible ones.

Sign up for Mountaineers and your GitHub and forum activity start counting from day one.

Want the full detail on points, rungs, and rewards? Read the Mountaineers program page.

Introducing Mountaineers: A Way to Say Thank You appeared first on MariaDB.org

Source

Multi-Cluster Replication, FIPS Mode, and More with MariaDB Enterprise Kubernetes Operator 26.06 appeared first on MariaDB.org

What exactly do we want to check?

• Tables without a Primary Key
• Duplicate indexes
• Partially redundant indexes
• Unused indexes

MariaDB, MySQL, and Percona Server

Tables without a Primary Key

SQL> SELECT DISTINCT t.table_schema, t.table_name
 FROM information_schema.tables AS t
 LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema AND t.table_name = c.table_name
 AND c.column_key = "PRI"
 WHERE t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
 AND c.table_name IS NULL AND t.table_type NOT IN('VIEW', 'SEQUENCE')
 AND t.table_schema = 'testtest'
;
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| testtest | archived |
+--------------+------------+
1 row in set

Source: Tables without a Primary Key

Duplicate indexes

SQL> SELECT table_name, redundant_index_name, redundant_index_columns, dominant_index_name, dominant_index_columns, sql_drop_index
 FROM sys.schema_redundant_indexes
 WHERE redundant_index_columns = dominant_index_columns
 AND table_schema = 'testtest'
;
+------------+----------------------+-------------------------+---------------------+------------------------+------------------------------------------------------+
| table_name | redundant_index_name | redundant_index_columns | dominant_index_name | dominant_index_columns | sql_drop_index |
+------------+----------------------+-------------------------+---------------------+------------------------+------------------------------------------------------+
| archived | dupl2 | category_id | dupl1 | category_id | ALTER TABLE `testtest`.`archived` DROP INDEX `dupl2` |
+------------+----------------------+-------------------------+---------------------+------------------------+------------------------------------------------------+
1 row in set

Source: Duplicate and redundant indices

Partially redundant indexes

SQL> SELECT table_name, redundant_index_name, redundant_index_columns, dominant_index_name, dominant_index_columns, sql_drop_index
 FROM sys.schema_redundant_indexes
 WHERE table_schema = 'testtest'
;
+-------------------+----------------------+-------------------------+---------------------+---------------------------------+-------------------------------------------------------------------+
| table_name | redundant_index_name | redundant_index_columns | dominant_index_name | dominant_index_columns | sql_drop_index |
+-------------------+----------------------+-------------------------+---------------------+---------------------------------+-------------------------------------------------------------------+
| access | customer | customer | customer_2 | customer,callerid_internal | ALTER TABLE `testtest`.`access` DROP INDEX `customer` |
| access | customer | customer | customer_3 | customer,callerid_external | ALTER TABLE `testtest`.`access` DROP INDEX `customer` |
| active_customers | uniqueid | uniqueid | PRIMARY | uniqueid,scustomer | ALTER TABLE `testtest`.`active_customers` DROP INDEX `uniqueid` |
| analytics_include | analytics | analytics | PRIMARY | analytics,feature,dtype,dnumber | ALTER TABLE `testtest`.`analytics_include` DROP INDEX `analytics` |
| archived | dupl2 | category_id | dupl1 | category_id | ALTER TABLE `testtest`.`archived` DROP INDEX `dupl2` |
...
| texts_media | uniqueid | uniqueid | PRIMARY | uniqueid,filename | ALTER TABLE `testtest`.`texts_media` DROP INDEX `uniqueid` |
| unlimited_access | customer | customer | customer_2 | customer,callerid_internal | ALTER TABLE `testtest`.`unlimited_access` DROP INDEX `customer` |
| unlimited_access | customer | customer | customer_3 | customer,callerid_external | ALTER TABLE `testtest`.`unlimited_access` DROP INDEX `customer` |
+-------------------+----------------------+-------------------------+---------------------+---------------------------------+-------------------------------------------------------------------+
26 rows in set

Source: Duplicate and redundant indices

Unused indexes

SQL> SELECT object_name, index_name
 FROM sys.schema_unused_indexes
 WHERE object_schema = 'testtest'
;
+------------------------+------------------------+
| object_name | index_name |
+------------------------+------------------------+
| access | customer_3 |
| access | customer_2 |
| actions | class |
| actions | action |
| active | channel |
...
| urls | customer |
| voucher_batches | customer |
| vouchers | batch |
+------------------------+------------------------+
413 rows in set

Note:

• For MariaDB, the PERFORMANCE_SCHEMA must be enabled first.
• The information is accurate as of the last database restart. If an index was last used BEFORE the most recent restart, it will be shown here as unused.

Source: Unused indexes

And now with PostgreSQL

Tables without a Primary Key

SQL> SELECT tab.table_schema, tab.table_name
 FROM information_schema.tables tab
 LEFT JOIN information_schema.table_constraints tco
 ON tab.table_schema = tco.table_schema
 AND tab.table_name = tco.table_name 
 AND tco.constraint_type = 'PRIMARY KEY'
 WHERE tab.table_type = 'BASE TABLE'
 AND tab.table_schema NOT IN ('pg_catalog', 'information_schema')
 AND tco.constraint_name IS NULL
 ORDER BY table_schema, table_name
;
 table_schema | table_name 
--------------+------------
 public | archived
(1 row)

Source: Find tables without primary keys (PKs) in PostgreSQL database

Duplicate indexes

Based on the MySQL sys schema:

SQL> WITH schema_flattened_keys AS (
 SELECT sai.relid, sai.indexrelid
 , sai.schemaname AS table_schema, sai.relname AS table_name, sai.indexrelname AS index_name
 , CASE pi.indisunique WHEN 'f' THEN 1 ELSE 0 END AS non_unique
 , index_columns.columns AS index_columns
 FROM pg_stat_all_indexes AS sai
 JOIN pg_index AS pi ON pi.indexrelid = sai.indexrelid
 JOIN (
 SELECT attrelid, string_agg(attname, ',' ORDER BY attnum ASC) AS columns
 FROM pg_attribute GROUP BY attrelid
 ) AS index_columns ON index_columns.attrelid = sai.indexrelid
 WHERE sai.schemaname NOT IN ('pg_toast', 'pg_catalog')
)
SELECT redundant_keys.table_schema AS table_schema, redundant_keys.table_name AS table_name, redundant_keys.index_name AS redundant_index_name
 , redundant_keys.index_columns AS redundant_index_columns, redundant_keys.non_unique AS redundant_index_non_unique
 , dominant_keys.index_name AS dominant_index_name, dominant_keys.index_columns AS dominant_index_columns, dominant_keys.non_unique AS dominant_index_non_unique
 , CONCAT('ALTER TABLE ', redundant_keys.table_schema, '.', redundant_keys.table_name, ' DROP INDEX ', redundant_keys.index_name, '') AS sql_drop_index
 FROM schema_flattened_keys redundant_keys
 JOIN schema_flattened_keys dominant_keys ON redundant_keys.table_schema = dominant_keys.table_schema AND redundant_keys.table_name = dominant_keys.table_name
 WHERE (redundant_keys.index_name <> dominant_keys.index_name
 AND ((redundant_keys.index_columns = dominant_keys.index_columns)
 AND ((redundant_keys.non_unique > dominant_keys.non_unique) OR (redundant_keys.non_unique = dominant_keys.non_unique))
 )
 OR ((POSITION(CONCAT(redundant_keys.index_columns,',') IN dominant_keys.index_columns) = 1) AND (redundant_keys.non_unique = 1))
 OR ((POSITION(CONCAT(dominant_keys.index_columns,',') IN redundant_keys.index_columns) = 1) AND (dominant_keys.non_unique = 0))
 )
 AND redundant_keys.index_columns = dominant_keys.index_columns
;
 table_schema | table_name | redundant_index_name | redundant_index_columns | redundant_index_non_unique | dominant_index_name | dominant_index_columns | dominant_index_non_unique | sql_drop_index 
--------------+------------+----------------------+-------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------------------------------------
 public | archived | dupl1 | category_id | 1 | dupl2 | category_id | 1 | ALTER TABLE public.archived DROP INDEX dupl1
 public | archived | dupl2 | category_id | 1 | dupl1 | category_id | 1 | ALTER TABLE public.archived DROP INDEX dupl2
(2 rows)

Source: Duplicate and redundant indices

Partially redundant indexes

Based on the MySQL sys schema:

SQL> WITH schema_flattened_keys AS (
 SELECT sai.relid, sai.indexrelid
 , sai.schemaname AS table_schema, sai.relname AS table_name, sai.indexrelname AS index_name
 , CASE pi.indisunique WHEN 'f' THEN 1 ELSE 0 END AS non_unique
 , index_columns.columns AS index_columns
 FROM pg_stat_all_indexes AS sai
 JOIN pg_index AS pi ON pi.indexrelid = sai.indexrelid
 JOIN (
 SELECT attrelid, string_agg(attname, ',' ORDER BY attnum ASC) AS columns
 FROM pg_attribute GROUP BY attrelid
 ) AS index_columns ON index_columns.attrelid = sai.indexrelid
 WHERE sai.schemaname NOT IN ('pg_toast', 'pg_catalog')
)
SELECT redundant_keys.table_schema AS table_schema, redundant_keys.table_name AS table_name, redundant_keys.index_name AS redundant_index_name
 , redundant_keys.index_columns AS redundant_index_columns, redundant_keys.non_unique AS redundant_index_non_unique
 , dominant_keys.index_name AS dominant_index_name, dominant_keys.index_columns AS dominant_index_columns, dominant_keys.non_unique AS dominant_index_non_unique
 , CONCAT('ALTER TABLE ', redundant_keys.table_schema, '.', redundant_keys.table_name, ' DROP INDEX ', redundant_keys.index_name, '') AS sql_drop_index
 FROM schema_flattened_keys redundant_keys
 JOIN schema_flattened_keys dominant_keys ON redundant_keys.table_schema = dominant_keys.table_schema AND redundant_keys.table_name = dominant_keys.table_name
 WHERE (redundant_keys.index_name <> dominant_keys.index_name
 AND ((redundant_keys.index_columns = dominant_keys.index_columns)
 AND ((redundant_keys.non_unique > dominant_keys.non_unique) OR (redundant_keys.non_unique = dominant_keys.non_unique))
 )
 OR ((POSITION(CONCAT(redundant_keys.index_columns,',') IN dominant_keys.index_columns) = 1) AND (redundant_keys.non_unique = 1))
 OR ((POSITION(CONCAT(dominant_keys.index_columns,',') IN redundant_keys.index_columns) = 1) AND (dominant_keys.non_unique = 0))
 )
;
 table_schema | table_name | redundant_index_name | redundant_index_columns | redundant_index_non_unique | dominant_index_name | dominant_index_columns | dominant_index_non_unique | sql_drop_index 
--------------+-----------------------+------------------------------------------+-------------------------+----------------------------+-------------------------------------------------+-----------------------------------------+---------------------------+---------------------------------------------------------------------------------------------
 public | numbers | numbers_customer_idx | customer | 1 | numbers_customer_text_dtype_text_dnumber_idx | customer,text_dtype,text_dnumber | 1 | ALTER TABLE public.numbers DROP INDEX numbers_customer_idx
 public | numbers | numbers_customer_idx | customer | 1 | numbers_customer_fax_dtype_fax_dnumber_idx | customer,fax_dtype,fax_dnumber | 1 | ALTER TABLE public.numbers DROP INDEX numbers_customer_idx
 public | numbers | numbers_customer_idx | customer | 1 | numbers_pkey | customer,stype,snumber | 0 | ALTER TABLE public.numbers DROP INDEX numbers_customer_idx
 public | numbers | numbers_dtype_idx | dtype | 1 | numbers_dtype_dnumber_idx | dtype,dnumber | 1 | ALTER TABLE public.numbers DROP INDEX numbers_dtype_idx
 public | number_callers | number_callers_dtype_idx | dtype | 1 | number_callers_dtype_dnumber_idx | dtype,dnumber | 1 | ALTER TABLE public.number_callers DROP INDEX number_callers_dtype_idx
 public | prefixes | prefixes_customer_idx | customer | 1 | prefixes_customer_dtype_dnumber_idx | customer,dtype,dnumber | 1 | ALTER TABLE public.prefixes DROP INDEX prefixes_customer_idx
 public | number_times | number_times_dtype_idx | dtype | 1 | number_times_dtype_dnumber_idx | dtype,dnumber | 1 | ALTER TABLE public.number_times DROP INDEX number_times_dtype_idx
 public | phones | phones_customer_idx | customer | 1 | phones_customer_callerid_location_idx | customer,callerid_location | 1 | ALTER TABLE public.phones DROP INDEX phones_customer_idx
 public | phones | phones_customer_idx | customer | 1 | phones_customer_callerid_external_idx | customer,callerid_external | 1 | ALTER TABLE public.phones DROP INDEX phones_customer_idx
 public | phones | phones_customer_idx | customer | 1 | phones_customer_callerid_internal_idx | customer,callerid_internal | 1 | ALTER TABLE public.phones DROP INDEX phones_customer_idx
 public | phones_hardware | phones_hardware_phone_idx | phone | 1 | phones_hardware_phone_hardware_address_idx | phone,hardware_address | 0 | ALTER TABLE public.phones_hardware DROP INDEX phones_hardware_phone_idx
 public | speeddials | speeddials_stype_idx | stype | 1 | speeddials_stype_snumber_idx | stype,snumber | 1 | ALTER TABLE public.speeddials DROP INDEX speeddials_stype_idx
 public | speeddials | speeddials_dtype_idx | dtype | 1 | speeddials_dtype_dnumber_idx | dtype,dnumber | 1 | ALTER TABLE public.speeddials DROP INDEX speeddials_dtype_idx
 public | mailbox_destinations | mailbox_destinations_context_mailbox_idx | context,mailbox | 1 | mailbox_destinations_pkey | context,mailbox,dcustomer,dtype,dnumber | 0 | ALTER TABLE public.mailbox_destinations DROP INDEX mailbox_destinations_context_mailbox_idx
 public | outgroup_times | outgroup_times_outgroup_idx | outgroup | 1 | outgroup_times_outgroup_name_idx | outgroup,name | 0 | ALTER TABLE public.outgroup_times DROP INDEX outgroup_times_outgroup_idx
 public | ingroup_times | ingroup_times_ingroup_idx | ingroup | 1 | ingroup_times_ingroup_name_idx | ingroup,name | 0 | ALTER TABLE public.ingroup_times DROP INDEX ingroup_times_ingroup_idx
 public | active_customers | active_customers_uniqueid_idx | uniqueid | 1 | active_customers_pkey | uniqueid,scustomer | 0 | ALTER TABLE public.active_customers DROP INDEX active_customers_uniqueid_idx
 public | access | access_customer_idx | customer | 1 | access_customer_callerid_external_idx | customer,callerid_external | 1 | ALTER TABLE public.access DROP INDEX access_customer_idx
 public | access | access_customer_idx | customer | 1 | access_customer_callerid_internal_idx | customer,callerid_internal | 1 | ALTER TABLE public.access DROP INDEX access_customer_idx
 public | unlimited_access | unlimited_access_customer_idx | customer | 1 | unlimited_access_customer_callerid_external_idx | customer,callerid_external | 1 | ALTER TABLE public.unlimited_access DROP INDEX unlimited_access_customer_idx
 public | unlimited_access | unlimited_access_customer_idx | customer | 1 | unlimited_access_customer_callerid_internal_idx | customer,callerid_internal | 1 | ALTER TABLE public.unlimited_access DROP INDEX unlimited_access_customer_idx
 public | texts | texts_dcustomer_idx | dcustomer | 1 | texts_dcustomer_dtype_dnumber_idx | dcustomer,dtype,dnumber | 1 | ALTER TABLE public.texts DROP INDEX texts_dcustomer_idx
 public | texts_media | texts_media_uniqueid_idx | uniqueid | 1 | texts_media_pkey | uniqueid,filename | 0 | ALTER TABLE public.texts_media DROP INDEX texts_media_uniqueid_idx
 public | number_calleridgroups | number_calleridgroups_dtype_idx | dtype | 1 | number_calleridgroups_dtype_dnumber_idx | dtype,dnumber | 1 | ALTER TABLE public.number_calleridgroups DROP INDEX number_calleridgroups_dtype_idx
 public | analytics_include | analytics_i | analytics | 1 | analytics_include_pkey | analytics,feature,dtype,dnumber | 0 | ALTER TABLE public.analytics_include DROP INDEX analytics_i
 public | archived | dupl1 | category_id | 1 | dupl2 | category_id | 1 | ALTER TABLE public.archived DROP INDEX dupl1
 public | archived | dupl2 | category_id | 1 | dupl1 | category_id | 1 | ALTER TABLE public.archived DROP INDEX dupl2
(27 rows)

Source: Duplicate and redundant indices

Unused indexes

SQL> SELECT relid::regclass AS table, indexrelid::regclass AS index
 , pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size
 , idx_tup_read, idx_tup_fetch, idx_scan
 FROM pg_stat_user_indexes 
 JOIN pg_index USING (indexrelid) 
 WHERE idx_scan = 0 
 AND indisunique IS FALSE
;
 table | index | index_size | idx_tup_read | idx_tup_fetch | idx_scan 
------------------------+-----------------------------------------------------------------+------------+--------------+---------------+----------
 customers | customers_prefix_idx | 16 kB | 0 | 0 | 0
 customers | customers_parent_idx | 16 kB | 0 | 0 | 0
 customers | customers_email_idx | 16 kB | 0 | 0 | 0
 customers | customers_affiliate_customer_idx | 16 kB | 0 | 0 | 0
 customers | customers_bill_ref_idx | 16 kB | 0 | 0 | 0
...
 analytics_include | analytics_i | 8192 bytes | 0 | 0 | 0
 archived | dupl1 | 8192 bytes | 0 | 0 | 0
 archived | dupl2 | 8192 bytes | 0 | 0 | 0
(413 rows)

Sources:

• Unused Indexes
• Postgresql: Monitor unused indexes

PG Assistant

At the Swiss PGDay2026(s), Bertrand Hartwig presented his tool PG Assistant. In that context, I wanted to try it out right away…

PG Assistant was able to find missing Primary Keys and duplicate indexes. It didn’t show me any partially redundant or unused indexes, but that might just be on my end…

Intallation of PG Assistant

$ apt update
$ apt install python3 python3.13-venv unzip pip
$ wget https://github.com/beh74/pgassistant-community/archive/refs/heads/main.zip
$ unzip main.zip 
$ cd pgassistant-community-main/
$ python3 -m venv env
$ source env/bin/activate
$ pip3 install -r requirements.txt
$ export FLASK_APP=run.py
$ flask run --host=0.0.0.0 --port=80

Then connect to the displayed URL using a web browser.

A user must be created in the database first:

SQL> CREATE ROLE pgassistant WITH LOGIN SUPERUSER PASSWORD 'secret';

and the pg_hba.conf file must be adapted.

Addendum

You can find unused indexes using the PG Assistant as follows: Database Objects ➜ Indexes ➜ Status: Unused ➜ “NO INDEX ACTIVITY”

Database Index Optimizer appeared first on MariaDB.org

SELECT * FROM employees;

But very soon, another world opens up inside psql — a set of commands that don’t look like SQL, don’t end with semicolons.

These are PostgreSQL Meta Commands, and they quietly power the daily workflow of almost every experienced DBA.

Meta commands are not about querying data — they are about navigating, inspecting, and controlling the PostgreSQL session/database efficiently.

What exactly are Meta Commands?

Meta commands are special instructions interpreted by psql, not PostgreSQL itself.

That means:

• They are not SQL
• They execute instantly on the client side
• They are specific to the psql terminal tool
• They do not end with semicolon like SQL statements
• The main focus area for meta commands is database interaction and not the interaction with the data in the database.

Cheat Sheet (Quick Reference) 

The most commonly used meta commands are as follows. There are many more apart from these, however, below are the most frequently used ones:

Connect and Manage Sessions

These commands help discover databases, establish connections, and verify the current session.

Please find the example of the commands used to connect and manage sessions in the screenshot below:

Inspect Database Objects                

The
d  family of commands is one of the most powerful features of
psql . These commands can be used to discover database objects, inspect their definitions, and view additional metadata.

Using object names and wildcards

Most object-inspection commands accept object names, schema-qualified names, and wildcard patterns.

For example:

dt

Lists all tables in the current search path.

dt public.*

Lists all tables in the public schema.

The same pattern matching is supported by several other meta-commands, including di, df, and the d family.

Please find the example of the d family commands in the screenshot below:

Format Query Results

Several meta-commands are available to improve the readability of query output, particularly when working with wide result sets.

Monitor Query Executions

These commands assist in measuring query performance and repeatedly executing queries for monitoring purposes.

Execute and Automate tasks

These commands simplify repetitive tasks and enable integration between psql, SQL scripts, and the operating system

Get Help

Built-in help commands provide quick access to both psql meta-command documentation and PostgreSQL SQL syntax without leaving the terminal.

What is .psqlrc?

.psqlrc is a startup file in the home directory that psql reads when a session begins. It can hold meta-commands and SQL that run before the first prompt. The main benefit is consistent defaults — timing, formatting, and a custom prompt — without repeating setup each time, which speeds daily work and reduces connection mistakes across databases.

A minimal .psqlrc might look like this:

timing on 
x auto

These settings load automatically on every new psql session as highlighted below:

Conclusion

PostgreSQL is powerful because of SQL — but for DBAs, psql meta commands make daily management far easier and more efficient.

Most developers use only a handful like dt or d. But experienced DBAs rely on a much broader toolkit to:

• Investigate production issues faster
• Navigate systems efficiently
• Reduce reliance on repetitive SQL
• Repetitive tasks can be automated
• Debug complex problems quickly

An easy way to understand the relationship between SQL and PostgreSQL meta commands is to compare them to driving a car.

SQL is like driving the car — it is the primary means of reaching a destination. It is used to retrieve, insert, update, and delete data, enabling applications and users to interact with the information stored in the database.

Meta commands, on the other hand, are like the car’s dashboard. While the dashboard does not move the vehicle, it provides essential information such as speed, fuel level, engine health, navigation status, and warning indicators. Driving without a dashboard is certainly possible, but it would mean operating with limited visibility into the vehicle’s condition and performance.

Similarly, SQL is responsible for manipulating and retrieving data, whereas PostgreSQL meta commands provide valuable insight into the database environment itself. They help administrators inspect database objects, navigate schemas, monitor sessions, examine roles and privileges, review object definitions, and perform numerous administrative tasks efficiently.

In essence, SQL enables interaction with the data, while meta commands enable interaction with the PostgreSQL environment. Together, they form a complementary toolkit that allows database professionals to work more effectively, troubleshoot issues faster, and administer PostgreSQL with greater confidence.

The post PostgreSQL Meta Commands that save time every day appeared first on Percona.

PostgreSQL Meta Commands that save time every day appeared first on MariaDB.org

Following the End-of-Life (EOL) status of MySQL 8.0, this subsequent LTS release is designed to provide enhanced stability alongside significant architectural innovations.

Let’s discuss each of these features below with some examples and usage.

Flow-control monitoring in Group Replication

Flow control monitoring has been improved and provides more granularity by introducing the additional status variables listed below.

• Gr_flow_control_throttle_count : It denotes the number of transactions that have been throttled.
• Gr_flow_control_throttle_time_sum :It denotes the time in microseconds that transactions have been throttled.
• Gr_flow_control_throttle_active_count :It denotes the number of transactions currently being throttled.
• Gr_flow_control_throttle_last_throttle_timestamp : It denotes the most recent date and time that a transaction was throttled.

To use these status variables, we must install the “Group Replication Flow Control Statistics”  component.

mysql> Install component 'file://component_group_replication_flow_control_stats';

After the component is installed, the statistics will be visible.

mysql> SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME LIKE 'Gr_flow_control%';
+--------------------------------------------------+----------------+
| VARIABLE_NAME                                    | VARIABLE_VALUE |
+--------------------------------------------------+----------------+
| Gr_flow_control_throttle_active_count            | 0              |
| Gr_flow_control_throttle_count                   | 0              |
| Gr_flow_control_throttle_last_throttle_timestamp |                |
| Gr_flow_control_throttle_time_sum                | 0              |
+--------------------------------------------------+----------------+

Multi-threaded applier extended statistics

We now have additional verbosity for the Applier threads for both Asynchronous and Group Replication topologies. This means we can get more details of the transactions or potential misbehaviours during the transactions applier stage. This feature is particularly useful for troubleshooting performance bottlenecks in multi-threaded replication environments, where understanding the specific cause of lag can be challenging.

This requires installing the “Replication Applier Metrics” component.

mysql> Install component 'file://component_replication_applier_metrics';

Upon successful installation of the requisite component, the performance schema tables facilitate tracking of transaction details and various performance metrics during the replication applier phase. For instance, monitoring the table “replication_applier_metrics” enables observing channel-specific operations.

mysql> SELECT * FROM performance_schema.replication_applier_metrics where CHANNEL_NAME='group_replication_applier'G;
*************************** 1. row ***************************
                                CHANNEL_NAME: group_replication_applier
                  TOTAL_ACTIVE_TIME_DURATION: 0
                          LAST_APPLIER_START: 0000-00-00 00:00:00
                TRANSACTIONS_COMMITTED_COUNT: 0
                  TRANSACTIONS_ONGOING_COUNT: 0
                  TRANSACTIONS_PENDING_COUNT: 0
       TRANSACTIONS_COMMITTED_SIZE_BYTES_SUM: 0
    TRANSACTIONS_ONGOING_FULL_SIZE_BYTES_SUM: 0
TRANSACTIONS_ONGOING_PROGRESS_SIZE_BYTES_SUM: 0
         TRANSACTIONS_PENDING_SIZE_BYTES_SUM: NULL
                      EVENTS_COMMITTED_COUNT: 0
            WAITS_FOR_WORK_FROM_SOURCE_COUNT: 0
         WAITS_FOR_WORK_FROM_SOURCE_SUM_TIME: 0
            WAITS_FOR_AVAILABLE_WORKER_COUNT: 0
         WAITS_FOR_AVAILABLE_WORKER_SUM_TIME: 0
      WAITS_COMMIT_SCHEDULE_DEPENDENCY_COUNT: 0
   WAITS_COMMIT_SCHEDULE_DEPENDENCY_SUM_TIME: 0
         WAITS_FOR_WORKER_QUEUE_MEMORY_COUNT: 0
      WAITS_FOR_WORKER_QUEUE_MEMORY_SUM_TIME: 0
              WAITS_WORKER_QUEUES_FULL_COUNT: 0
           WAITS_WORKER_QUEUES_FULL_SUM_TIME: 0
             WAITS_DUE_TO_COMMIT_ORDER_COUNT: 0
          WAITS_DUE_TO_COMMIT_ORDER_SUM_TIME: 0
        TIME_TO_READ_FROM_RELAY_LOG_SUM_TIME: 0

In addition to aggregate metrics, MySQL 9.7 provides a way to inspect the progress of individual worker threads via monitoring stats in the “replication_applier_progress_by_worker” table. This level of detail helps administrators identify if a single transaction is monopolising a specific worker, causing overall replication delay.

mysql> SELECT * FROM performance_schema.replication_applier_progress_by_workerG;
*************************** 1. row ***************************
                          CHANNEL_NAME: group_replication_applier
                             WORKER_ID: 0
                             THREAD_ID: 62
              ONGOING_TRANSACTION_TYPE: UNASSIGNED
   ONGOING_TRANSACTION_FULL_SIZE_BYTES: 0
ONGOING_TRANSACTION_APPLIED_SIZE_BYTES: 0
*************************** 2. row ***************************
                          CHANNEL_NAME: group_replication_applier
                             WORKER_ID: 1
                             THREAD_ID: 63
              ONGOING_TRANSACTION_TYPE: UNASSIGNED
   ONGOING_TRANSACTION_FULL_SIZE_BYTES: 0
ONGOING_TRANSACTION_APPLIED_SIZE_BYTES: 0
*************************** 3. row ***************************
                          CHANNEL_NAME: group_replication_applier
                             WORKER_ID: 2
                             THREAD_ID: 64
              ONGOING_TRANSACTION_TYPE: UNASSIGNED
   ONGOING_TRANSACTION_FULL_SIZE_BYTES: 0
ONGOING_TRANSACTION_APPLIED_SIZE_BYTES: 0
*************************** 4. row ***************************
                          CHANNEL_NAME: group_replication_applier
                             WORKER_ID: 3
                             THREAD_ID: 65
              ONGOING_TRANSACTION_TYPE: UNASSIGNED
   ONGOING_TRANSACTION_FULL_SIZE_BYTES: 0
ONGOING_TRANSACTION_APPLIED_SIZE_BYTES: 0

Automatic eviction & rejoin

The Group Replication resource manager now provides auto-eviction functionality, which we can configure using the available options. This basically ensures that the unhealthy node is removed from the Group to maintain the cluster’s high availability and overall performance.

This requires installing the “group replication resource manager” component.

mysql> INSTALL COMPONENT 'file://component_group_replication_resource_manager';

Once the component is available,  we can use various options to decide the node expulsion policy.

1) Applier channel

We can set the applier channel replication lag threshold values using the configuration parameter below.

mysql> set global group_replication_resource_manager.applier_channel_lag = <value>;

If lag exceeds  “applier_channel_lag” threshold 10 times or more in a row, this server is expelled from the group. The status variable below is used for tracking the lag exceed rate.

mysql> show global status like 'Gr_resource_manager_applier_channel_lag';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| Gr_resource_manager_applier_channel_lag | 0     |
+-----------------------------------------+-------+

2) Recovery Channel

Similarly, we can define a threshold for the group member recovery process to attempt to rejoin the cluster. 

mysql> set global group_replication_resource_manager.recovery_channel_lag = <value>;

If the secondary’s recovery lag exceeds “recovery_channel_lag”, 10 times or more in succession, the server is expelled from the group. 

mysql show global status like 'Gr_resource_manager_recovery_channel_lag';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Gr_resource_manager_recovery_channel_lag | 0     |
+------------------------------------------+-------+

3) Memory/Resource Usage

We can also define an expelled condition based on the group member’s memory or resource usage %.

mysql> set global group_replication_resource_manager.memory_used_limit = 10;

If the memory usage exceeds memory_used_limit % by 10 or more consecutive times, the node will be expelled from the group.

mysql> show global status like 'Gr_resource_manager_memory_used%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| Gr_resource_manager_memory_used | 78    |
+---------------------------------+-------+
1 row in set (0.002 sec)

In addition to the discussed options above, we can also track various server status variables to monitor group replication and the resource manager component.

mysql> select * from performance_schema.global_status where variable_name in ('Gr_resource_manager_applier_channel_threshold_hits','Gr_resource_manager_applier_channel_eviction_timestamp','Gr_resource_manager_recovery_channel_threshold_hits','Gr_resource_manager_recovery_channel_eviction_timestamp','Gr_resource_manager_memory_threshold_hits','Gr_resource_manager_memory_eviction_timestamp');
+---------------------------------------------------------+----------------+
| VARIABLE_NAME                                           | VARIABLE_VALUE |
+---------------------------------------------------------+----------------+
| Gr_resource_manager_applier_channel_eviction_timestamp  |                |
| Gr_resource_manager_applier_channel_threshold_hits      | 0              |
| Gr_resource_manager_memory_eviction_timestamp           |                |
| Gr_resource_manager_memory_threshold_hits               | 6703           |
| Gr_resource_manager_recovery_channel_eviction_timestamp |                |
| Gr_resource_manager_recovery_channel_threshold_hits     | 0              |
+---------------------------------------------------------+----------------+
6 rows in set (0.003 sec)

The expelled node can attempt to automatically rejoin based on the value of the group_replication_autorejoin_tries variable.

mysql> show variables like '%group_replication_autorejoin_tries%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| group_replication_autorejoin_tries | 3     |
+------------------------------------+-------+
1 row in set (0.006 sec)

If the node cannot join, it will perform the behaviour specified in the group_replication_exit_state_action variable.

mysql> show variables like '%group_replication_exit_state_action%';
+-------------------------------------+--------------+
| Variable_name                       | Value        |
+-------------------------------------+--------------+
| group_replication_exit_state_action | OFFLINE_MODE |
+-------------------------------------+--------------+
1 row in set (0.005 sec)

After a server is evicted from the group (for whatever reason), it gets a grace period (group_replication_resource_manager) when it rejoins. During this period, the Resource Manager won’t immediately kick it out again, even if it’s still lagging or breaching the defined threshold as discussed above.

mysql> show variables like '%group_replication_resource_manager.quarantine_time%';
+----------------------------------------------------+-------+
| Variable_name                                      | Value |
+----------------------------------------------------+-------+
| group_replication_resource_manager.quarantine_time | 3600  |
+----------------------------------------------------+-------+

Up-to-date aware Primary election

The Primary election process is more mature and cohesive. The Group Replication Manager now uses the most up-to-date status as a criterion for selecting the new primary.

Here is how the Group Replication Manager performs the most up-to-date primary selection prior to MySQL v9.7.

1. The lowest MySQL version is checked for each member.
2. If more than one member is running the lowest MySQL Server version, each member’s weight is determined by the “group_replication_member_weight” system variable.
3. If there is more than one member running the lowest MySQL Server version, and also more than one of those members has the highest member weight, the third factor considered is the lexicographical order of the generated server UUIDs “server_uuid” of each group member. The member with the lowest server UUID is chosen as the new primary.
   

In MySQL version 9.7, “group_replication_elect_prefers_most_updated” was introduced, so the failover will be determined by how many transactions are in the secondary backlog. Basically the secondary with the least backlog will be selected as Primary.

Now, it will consider the “most up-to-date” node first, then “weight” and then “UUID”. 

To use “group_replication_elect_prefers_most_updated”, we need to install the “Group Replication Primary Election” component listed below on each Group Member.

mysql> Install component 'file://component_group_replication_elect_prefers_most_updated';

By default, the most up-to-date group member selection is enabled. We need to make sure it’s enabled on all Group Members. 

mysql> select @@group_replication_elect_prefers_most_updated.enabled;
+--------------------------------------------------------+
| @@group_replication_elect_prefers_most_updated.enabled |
+--------------------------------------------------------+
|                                                      1 |
+--------------------------------------------------------+
1 row in set (0.007 sec)

In the event that a new primary is elected via the most up-to-date selection mechanism, this metric represents the transaction processing differential between the newly designated primary and the secondary node with the highest level of synchronisation.

mysql> show status like 'Gr_latest_primary_election_by_most_uptodate_members_trx_delta';
+---------------------------------------------------------------+-------+
| Variable_name                                                 | Value |
+---------------------------------------------------------------+-------+
| Gr_latest_primary_election_by_most_uptodate_members_trx_delta | 0     |
+---------------------------------------------------------------+-------+

Also, we can track the timestamp of the most recent primary election on the most up-to-date node.

mysql> show status like 'Gr_latest_primary_election_by_most_uptodate_member_timestamp';
+--------------------------------------------------------------+-------+
| Variable_name                                                | Value |
+--------------------------------------------------------------+-------+
| Gr_latest_primary_election_by_most_uptodate_member_timestamp |       |
+--------------------------------------------------------------+-------+
1 row in set (0.005 sec)

The database logs also tell exactly what criteria the primary member selected during failover.

2026-06-14T10:04:02.243809Z 0 [System] [MY-015575] [Repl] Plugin group_replication reported: 'Member with uuid 00021702-2222-2222-2222-222222222222 was elected primary since it was the most up-to-date member with 2755 transactions more than second most up-to-date member 00021703-3333-3333-3333-333333333333. In case of a tie member weight and then uuid lexical order was used over the most updated members.'

MySQL JSON duality views

With the introduction of JSON duality views, we can leverage a single unified JSON document for both relational and hierarchical JSON data. This provides a common, structured JSON format for the application, allowing it to perform both read and write operations.

Let’s see a quick scenario below on how it works.

Below are two relational tables from which we obtain aggregated information in JSON format. 

mysql> CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_type VARCHAR(100)
);

mysql> CREATE TABLE products_details (
  product_detail_id INT PRIMARY KEY,
  product_id INT,
  name VARCHAR(100),
  active varchar(10)
);

mysql> INSERT INTO products (product_id,product_type) VALUES (1,'IT'), (2,'TEL');
mysql> INSERT INTO products_details (product_detail_id,product_id,name,active) VALUES (1,1,'Laptop','Yes'), (2,2,'Mobile','Yes');

Here is the exact Json View which fetch the columns from the relation table based on the join condition. Each of those relational table columns is mapped with a JSON data structure (_id,v_product_type,v_product_type ), and the complete details of the product details table are fetched into the (product) array.

mysql> CREATE JSON RELATIONAL DUALITY VIEW view_product AS
SELECT JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE,DELETE)
    '_id': product_id,
    'v_product_type': product_type,
    'product': (
        SELECT JSON_ARRAYAGG(
            JSON_DUALITY_OBJECT(WITH(INSERT,UPDATE,DELETE)
                'v_product_detail_id': product_detail_id,
                'v_name': name,
                'v_active': active
                
            )
        )
        FROM products_details
        WHERE products_details.product_id = products.product_id
    )
)
FROM products;

mysql> select * from view_product;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"_id": 1, "product": [{"v_name": "Laptop", "v_active": "Yes", "v_product_detail_id": 1}], "_metadata": {"etag": "313642c2aa24f0571264332afa140715"}, "v_product_type": "IT"}  |
| {"_id": 2, "product": [{"v_name": "Mobile", "v_active": "Yes", "v_product_detail_id": 2}], "_metadata": {"etag": "3d229ada02ac660f9f6cac994b44831a"}, "v_product_type": "TEL"} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.002 sec)

Once the duality view is created, we can perform both read/write operations.

Reading the duality view

mysql> select * from view_product;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"_id": 1, "product": [{"v_name": "Laptop", "v_active": "Yes", "v_product_detail_id": 1}], "_metadata": {"etag": "313642c2aa24f0571264332afa140715"}, "v_product_type": "IT"}  |
| {"_id": 2, "product": [{"v_name": "Mobile", "v_active": "Yes", "v_product_detail_id": 2}], "_metadata": {"etag": "3d229ada02ac660f9f6cac994b44831a"}, "v_product_type": "TEL"} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Writing the underlying table in the duality view

mysql> UPDATE view_product
SET data = JSON_SET(
    data,
    '$.product[0].v_name',
    'Notepad'
)
WHERE JSON_EXTRACT(data, '$._id') = 1;

mysql> select * from products_details;
+-------------------+------------+---------+--------+
| product_detail_id | product_id | name    | active |
+-------------------+------------+---------+--------+
|                 1 |          1 | Notepad | Yes    |
|                 2 |          2 | Mobile  | Yes    |
+-------------------+------------+---------+--------+

After performing the above write operations, we can see that the view now shows the updated data.

mysql > select * from view_product;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"_id": 1, "product": [{"v_name": "Notepad", "v_active": "Yes", "v_product_detail_id": 1}], "_metadata": {"etag": "72c4368420cdc698842d0ab4bd9315ab"}, "v_product_type": "IT"} |
| {"_id": 2, "product": [{"v_name": "Mobile", "v_active": "Yes", "v_product_detail_id": 2}], "_metadata": {"etag": "3d229ada02ac660f9f6cac994b44831a"}, "v_product_type": "TEL"} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Hypergraph Optimizer

With the Hypergraph Optimiser, we now have more advanced optimisation for complex queries and a broader set of Join plans than the older traditional method, missing earlier. By using “Join hypergraph”, the optimiser now has better reach to all tables in the join condition.

Hypergraph Optimiser is OFF

mysql> SET optimizer_switch='hypergraph_optimizer=off';

mysql> SELECT t1.k, COUNT(*) AS cnt
FROM sbtest1 t1
JOIN sbtest2 t2 ON t1.id = t2.id
JOIN sbtest3 t3 ON t1.id = t3.id
WHERE t1.k BETWEEN 200000 AND 500000
GROUP BY t1.k
ORDER BY cnt DESC
LIMIT 100;

Output:

| 498870 | 119 |
| 498729 | 119 |
| 497668 | 119 |
| 498076 | 119 |
+--------+-----+
100 rows in set (4.000 sec)

Explain output:

-> Limit: 100 row(s)
    -> Sort: cnt DESC, limit input to 100 row(s) per chunk
        -> Stream results  (cost=1.22e+6 rows=175136)
            -> Group aggregate: count(0)  (cost=1.22e+6 rows=175136)
                -> Nested loop inner join  (cost=1.1e+6 rows=493200)
                    -> Nested loop inner join  (cost=601547 rows=493200)
                        -> Filter: (t1.k between 200000 and 500000)  (cost=99122 rows=493200)
                            -> Covering index range scan on t1 using k_1 over (200000 <= k <= 500000)  (cost=99122 rows=493200)
                        -> Single-row covering index lookup on t2 using PRIMARY (id = t1.id)  (cost=0.919 rows=1)
                    -> Single-row covering index lookup on t3 using PRIMARY (id = t1.id)  (cost=0.919 rows=1)

Hypergraph Optimiser is ON

mysql> SET optimizer_switch='hypergraph_optimizer=on';

mysql> SELECT t1.k, COUNT(*) AS cnt
FROM sbtest1 t1
JOIN sbtest2 t2 ON t1.id = t2.id
JOIN sbtest3 t3 ON t1.id = t3.id
WHERE t1.k BETWEEN 200000 AND 500000
GROUP BY t1.k
ORDER BY cnt DESC
LIMIT 100;

Output:

| 499721 | 119 |
| 499052 | 119 |
| 498870 | 119 |
| 498384 | 119 |
+--------+-----+
100 rows in set (0.498 sec)

Explain output:

-> Sort: cnt DESC, limit input to 100 row(s) per chunk  (cost=1.96e+6..1.96e+6 rows=100)
    -> Table scan on <temporary>  (cost=1.87e+6..1.9e+6 rows=175136)
        -> Aggregate using temporary table  (cost=1.87e+6..1.87e+6 rows=175136)
            -> Inner hash join (t2.id = t3.id)  (cost=990754..1.44e+6 rows=493200)
                -> Covering index scan on t3 using k_1  (cost=0.312..308240 rows=986400)
                -> Hash
                    -> Inner hash join (t1.id = t2.id)  (cost=370988..824021 rows=493200)
                        -> Covering index scan on t2 using k_1  (cost=0.312..308240 rows=986400)
                        -> Hash
                            -> Filter: (t1.k between 200000 and 500000)  (cost=0.416..205287 rows=493200)
                                -> Covering index range scan on t1 using k_1 over (200000 <= k <= 500000)  (cost=0.359..176877 rows=493200)

We can see that with “hypergraph_optimizer=enabled”, the query execution time is almost 8x faster.

The performance difference might not be noticeable with a few joins or a smaller table’s data set, but with more complex joins, it can yield better performance. In the above example, we can see that when “hypergraph_optimizer=enabled”, the optimiser replaces “Nested loop inner join” with “Inner hash join”, which is generally better for large datasets. 

Higher version source allowed

Now, it’s possible that a lower version replica can connect to a higher version source when the major versions differ. That means we don’t have to rely on all replicas being upgraded in one go; we can just upgrade the source, verify it, and later perform rolling upgrades on lower-version replicas as per our own timelines and convenience.

Of course, we have to be cautious not to run any such feature or change on the source that doesn’t support lower-version replicas.

Please note – This won’t be applicable to previous releases, say (8.4, 8.0), as they didn’t restrict such replication connectivity. It would be useful for 9.7 or the next major release.

To enable this functionality, we need to ensure the following variable is enabled on the Replica. By default its enabled on 9.7

mysql> show variables like 'replica_allow_higher_version_source';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| replica_allow_higher_version_source | ON    |
+-------------------------------------+-------+
1 row in set (0.008 sec)

Summary

The above discussion highlights key advancements in MySQL 9.7 LTS, ranging from some innovative or operational improvements to developer-centric features such as “JSON Duality” Views. Also, the “Hypergraph Optimiser” is now available for community release, which was previously exclusive to MySQL Heatwave/Enterprise.  As a Long-Term Support (LTS) release, MySQL 9.7 is structured to provide a stable and consistent environment, prioritising architectural reliability over frequent experimental changes.

One more important mention here: It’s suggested to use MySQL 9.7.1, or the next sub-releases, as 9.7.0 has some higer severity CVE’s. If you are using Percona Server for MySQL (PS), we skipped 9.7.0 and are shipping the fixed 9.7.1 version directly.

Still, it’s highly recommended to test any new component or changes in your lower/staging environment before deploying in production to better assess the overall impact on existing workload, queries, and database behaviour.

The post Inside MySQL 9.7 LTS Features appeared first on Percona.

Inside MySQL 9.7 LTS Features appeared first on MariaDB.org

Continue reading “MariaDB Hidden Gem: Online Schema Change without pt-osc”

The post MariaDB Hidden Gem: Online Schema Change without pt-osc appeared first on MariaDB.org.

MariaDB Hidden Gem: Online Schema Change without pt-osc appeared first on MariaDB.org

In recent releases, the locking architecture was further standardized under a single overarching option: --sync-thread-lock-mode. Along with this modernization came a powerful new safety feature designed to give you lock-free thread synchronization without risking silent inconsistency: SAFE_NO_LOCK (merged in PR #2031).

Let’s explore the new thread-synchronization landscape and break down when you should use each mode.

What is --sync-thread-lock-mode?

Previously, flags like -k, --no-locks or --lock-all-tables dictated how MyDumper behaved. These have now been deprecated in favor of --sync-thread-lock-mode, which accepts five core values: AUTO, FTWRL, LOCK_ALL, GTID, NO_LOCK, and the newly added SAFE_NO_LOCK.

As a multi-threaded tool, MyDumper’s main challenge is ensuring that every single worker thread establishes its database snapshot at the exact same point in time. The sync mode you choose completely alters how MyDumper orchestrates this point-in-time synchronization.

Understanding SAFE_NO_LOCK

MyDumper fires off START TRANSACTION WITH CONSISTENT SNAPSHOT across its threads. It captures the binary log position at the very beginning of the process and compares it after the worker threads have attempted to synchronize.

When using NO_LOCK, if the threads don’t actually hit the same point in time—meaning they fail to synchronize—MyDumper simply logs a warning and continues backing up. This results in an inconsistent backup, which is a massive gamble for production systems.

SAFE_NO_LOCK adds a strict transactional safety net. If MyDumper detects any differences or drift in the binlog position among the threads during the synchronization phase, it immediately stops the backup. This prevents you from generating a corrupted, out-of-sync backup that will fail or cause data anomalies during a later restore.

Choosing the Right Mode

Depending on your architecture, uptime requirements, and database vendor, here is the breakdown of when to use each mode:

AUTO (The Default)

What it does: MyDumper automatically evaluates the database vendor, version, and capabilities to choose the safest, least-intrusive method.

When to use it: The vast majority of standard backups. It removes the guesswork and adapts dynamically if your database infrastructure upgrades.

FTWRL (Flush Tables With Read Lock)

What it does: It is the traditional method. It issues a global read lock via FLUSH TABLES WITH READ LOCK on the main connection, forces all threads to establish their consistent snapshot at that exact freeze frame, and then releases the lock.

When to use it:

• When you have non-transactional tables (like MyISAM or ARCHIVE) that must be consistently backed up alongside InnoDB tables.
• When your database lacks advanced snapshot-tracking capabilities (older MySQL versions).

Downside: It blocks writes across the entire instance during synchronization, which can cause a queue cascade on a busy production server.

GTID

Leverages a specific server variable in Percona Server called binlog_snapshot_gtid_executed to instantly verify if all threads are watching the exact same transaction state.

When to use it: If you are running Percona Server with GTID enabled and want a lightning-fast, lockless synchronization method that is guaranteed to be transactionally accurate.

SAFE_NO_LOCK

What it does: Uses transaction isolation to sync threads without global locks, but immediately aborts the backup if binlog positions diverge during initialization.

When to use it:

• On highly sensitive production systems, where global write locks are absolutely forbidden due to strict SLAs.
• When you are entirely utilizing transactional engines (InnoDB).
• When you want a lock-free backup but require absolute certainty that your backup is 100% consistent.

Downside: In high-throughput write environments, threads may fail to align within the retry window, causing the backup job to abort. (Though an abort is always preferable to an inconsistent backup!).

NO_LOCK

What it does: Attempts lockless synchronization but logs a warning and proceeds even if consistency fails.

When to use it: Rarely, if ever, in the production primary server. It is acceptable for staging environments, development seeding, or scratch pads where data accuracy and point-in-time consistency are entirely secondary to getting a quick data dump without locking the server.

LOCK_ALL

What it does: Explicitly issues a LOCK TABLE command for every single table being exported.

When to use it: Primarily a fallback mode. Use this only when FLUSH TABLES WITH READ LOCK is completely unavailable due to restricted cloud permissions (certain restricted PaaS environments) or specific database limitations.

Conclusion

The addition of --sync-thread-lock-mode=SAFE_NO_LOCK bridges a long-standing gap in logical MySQL backups: achieving a completely lockless synchronization state without flying blind. By implementing a strict fail-fast policy, MyDumper ensures that database administrators never have to sacrifice backup integrity for system availability.

The post MyDumper Locking Mechanisms Revisited: Introducing SAFE_NO_LOCK appeared first on Percona.

MyDumper Locking Mechanisms Revisited: Introducing SAFE_NO_LOCK appeared first on MariaDB.org

It’s an experiment, not production software. It patches mysqld and has rough edges, which we list at the end. Source is on GitHub under GPLv2:     https://github.com/EvgeniyPatlan/ducksdb-mysql-engine.

Why we made it

MySQL is great for transactions and slow at analytics. A wide GROUP BY over a few hundred million rows, or a six-way join, takes minutes on InnoDB. The usual fix is to copy the data into a column store and keep it in sync, so now you’re running two systems and the pipeline between them.

We wanted the table itself to be the column store, with the heavy queries offloaded for you. Mark it ENGINE=DuckDB, query it the way you always have, and DuckDB does the analytical work.

What it actually is

DuckDB is an in-process columnar query engine, basically SQLite for OLAP. It stores data by column and it’s built for scans and aggregations, which is exactly what a row store is bad at.

We’re not the first to put it behind a relational table. Alibaba’s AliSQL has had a built-in DuckDB engine for a while. MariaDB shipped MariaDB DuckDB about a week ago, while we were already building ours. AliSQL got there first; MariaDB and we landed on the same idea independently, around the same time, them on MariaDB and us on stock MySQL 9.7. Their engine is the closest comparison to ours, so it’s in the benchmarks below.

How it hooks into MySQL

MySQL doesn’t have a select_handler, the API MariaDB uses to grab a whole SELECT and run it inside an engine. We added our own: a handlerton::pushdown_select hook.

The pushdown path. Either the whole query renders to DuckDB SQL and runs columnar, or it declines and the normal row path handles it.

The engine is compiled into mysqld, and each schema is one DuckDB file under the datadir. Three patches do the integration, and all three are generic, so they’ll fire for any engine that exposes the hook:

• The hook runs at the end of JOIN::optimize(). If every base table in the block is one engine that has the hook, that engine looks at the optimized JOIN, and if it can translate the whole query it sets JOIN::override_executor_func (which the executor already checks in sql_union.cc). The query gets regenerated as DuckDB SQL, prepared once, run, and the aggregated result is staged into a temp table. EXPLAIN is left alone.
• A server-side LOAD DATA INFILE goes into a DuckDB COPY instead of crawling through write_row row by row. At 600M rows that’s a 20-minute load instead of 80.
• For single-engine statements we clear OPTIMIZER_SWITCH_SEMIJOIN in prepare, so IN, EXISTS, NOT IN and NOT EXISTS stay as subqueries the builder can render instead of getting rewritten into semijoin nests it can’t recognize.

The builder only renders a node when the output is provably identical to what MySQL would return. If it can’t, it declines and MySQL runs the query unchanged. Literals are bound as parameters. Collation, NULL ordering and decimal scale are matched on purpose, and an unmapped collation or a REAL literal is enough to make it back off. With that in place, all 22 TPC-H queries push down and match InnoDB row for row.

Getting started

The fastest way in is the image:

docker run -d --name mysql-duckdb -p 3306:3306 
-e MYSQL_ROOT_PASSWORD=secret 
-v mysql-duckdb-data:/var/lib/mysql 
evgeniypatlan/test-images:mysql-9.7-duckdb-v0.2.0

Make a table, put a few rows in, run an aggregate:

CREATE DATABASE shop; USE shop;
CREATE TABLE sales (id INT PRIMARY KEY, region INT, amount DECIMAL(12,2)) ENGINE=DuckDB;
INSERT INTO sales VALUES (1,1,100),(2,1,200),(3,2,50);

SELECT region, SUM(amount) FROM sales GROUP BY region;
-- region | SUM(amount)
-- 1 | 300.00
-- 2 | 50.00

Nothing about that query is special, and that is the point. To check it actually went to DuckDB rather than down the row path, watch the Ducksdb_pushdown_count status variable:

SELECT region, SUM(amount) FROM sales GROUP BY region; -- offloaded
SHOW STATUS LIKE 'Ducksdb_pushdown_count'; -- counter goes +1

SELECT * FROM sales WHERE id = 3; -- point lookup
SHOW STATUS LIKE 'Ducksdb_pushdown_count'; -- counter unchanged

The single-row lookup stays on the row path deliberately. For one row an index seek beats spinning up a DuckDB result, so there is no reason to offload it. OLTP keeps its path, analytics get the column store, and you do not pick by hand.

If you would rather build it, you need the MySQL 9.7 tree under vendor/mysql-server/ and a DuckDB prefix, then:

ln -s ../../engine vendor/mysql-server/storage/duckdb
scripts/build-server.sh # applies the 3 patches, builds mysqld + clients

Does it actually go fast?

All 22 TPC-H queries, were executed in a Docker on one laptop (20 cores, 62 GiB RAM), the same data loaded into four engines: InnoDB, our MySQL+DuckDB, MariaDB+DuckDB, and standalone DuckDB as the reference. Warm wall-clock, minimum over a few runs, in seconds.

SF10, around 60 million lineitem rows

A handful of rows here; the whole table is in docs/tpch_engine_comparison.md:

SF10, all 22 queries, log scale (lower is better). Hatched InnoDB bars did not finish inside 180 s. The three DuckDB engines sit in a tight band near the floor.

InnoDB is somewhere between 100 and 340 times slower per query, and on 6 of the 22 it never finished inside the 180-second cap (the correlated subqueries and the heaviest scans). It burned 1317 seconds on just the 16 it did finish. The three DuckDB engines get through all 22 in about 15 seconds, and ours lands right between MariaDB and plain DuckDB. The gap is so big there is not much else to say about it.

SF100, around 600 million lineitem rows

At this size InnoDB is out of the running (a copy of the data alone is about 100 GB and queries run for hours), so it is the three DuckDB engines only, run one at a time:

SF100, three DuckDB engines, log scale (lower is better). Q15 for our engine is shown at its matched-memory time (~4 s); the capped run measured 1309 s, explained below. MariaDB errored on Q10 and Q13.

At 600 million rows ours is still correct on all 22 and stays close to plain DuckDB. MariaDB’s engine drops two queries (Q10, and Q13 on its column-list syntax) and is a lot slower on the big joins – Q9 took 115 seconds against our 21 and native’s 20.

One honest word on Q15 at SF100, because in the full table it shows an ugly number for our engine. It is not a real loss. We capped DuckDB’s memory so it spills to disk instead of getting OOM-killed inside mysqld, and under that cap Q15’s CTE spills a lot. Give it the memory MariaDB had and it runs in about 4 seconds, like native. The answer was always right; only the clock was bad.

And one number we did not expect: loading those 600 million rows took about 20 minutes with our engine (the COPY shortcut) versus about 80 minutes with MariaDB, which loads row by row on a single core. Roughly four times faster to get the data in.

Try it, then tell us

If any of this sounds useful, pull the image and throw your own queries at it:

docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=secret 
evgeniypatlan/test-images:mysql-9.7-duckdb-v0.2.0

The source is on GitHub (GPLv2), patches and benchmark harness included: https://github.com/EvgeniyPatlan/ducksdb-mysql-engine. The full per-query benchmark and how we measured it live in docs/tpch_engine_comparison.md.

The post Running DuckDB as a MySQL 9.7 storage engine appeared first on Percona.

Running DuckDB as a MySQL 9.7 storage engine appeared first on MariaDB.org

Continue reading “MariaDB 13.1 Feature in Focus: BLOB, TEXT, JSON and GEOMETRY Support in the HEAP Engine”

The post MariaDB 13.1 Feature in Focus: BLOB, TEXT, JSON and GEOMETRY Support in the HEAP Engine appeared first on MariaDB.org.

MariaDB 13.1 Feature in Focus: BLOB, TEXT, JSON and GEOMETRY Support in the HEAP Engine appeared first on MariaDB.org

Source

An Easy Path from MySQL to MariaDB: Introducing MariaDB Migrator appeared first on MariaDB.org

Source

Using MariaDB Serverless Cloud Deployment for Uneven AI Workloads appeared first on MariaDB.org

Continue reading “MariaDB Foundation Sea Lion Champions Nominees: Federico Razzoli”

The post MariaDB Foundation Sea Lion Champions Nominees: Federico Razzoli appeared first on MariaDB.org.

MariaDB Foundation Sea Lion Champions Nominees: Federico Razzoli appeared first on MariaDB.org

The short version: open_pg_tde needs the exact same modified PostgreSQL that pg_tde does – TDE isn’t possible without those upstream changes.
The only difference is delivery: they ship those changes as a patch file users have to apply by hand, while we provide a ready-made branch.

Upstreaming TDE!

The main point of open_pg_tde is that it is more open, as it is not gated behind a vendor fork.
A bit later I’ll go into details why I think that’s incorrect from a technical point of view, but before that, I want to make something clear:
we do not want to keep TDE in our fork, we are actively working on upstreaming it!

The LinkedIn post also linked to a pgedge blog post written at the end of May, Why Postgres Lacks Transparent Data Encryption.

While it seems to be optimistic about the future of encryption in the community version, it also missed that we had not one, but two sessions about it at pgconf.dev, the first one organized by Kai Wagner from Percona, and the second one by Ants Aasma from Cybertec.

We had some really good discussions during those sessions, and ended up with lots of “homework”:
things we wanted to explore and benchmark before continuing the discussion on pgsql-hackers, where the discussion will soon continue.

With this I want to emphasize that Percona is 100% behind making PostgreSQL transparent data encryption open, as part of the community.

Our fork was born out of necessity, not because we wanted to have our own version.
In fact, if you look back into the git history of pg_tde, or at our earlier blog posts, we first tried to make it work without any upstream patch. Unfortunately, that didn’t work out.

Why do we have our fork?

The open_pg_tde documentation publishes the following comparison table:

The table is clearly AI generated, and has some misleading and/or inconsistent elements.
Here is what we think a more honest comparison looks like:

I’ll leave out the discussion about temporary files in this blog post.
That’s a complex topic by itself, it raises many questions, and we have good reasons why we are still thinking about it instead of shipping a quick version prototyped with Claude.
It is something I plan to talk about later, on its own.

Back to the comparison: it claims that pg_tde is vendor locked because it only works with our fork.
I don’t think that’s the case:
Our fork is completely open source, anybody can go to our GitHub and cherry-pick our patches manually.

open_pg_tde doesn’t remove the need for these patches: it can’t, TDE requires them.
It just ships them as a patch file users apply manually, rather than as a branch.
Both approaches modify PostgreSQL identically.

The fork exists for one sole reason:
convenience, we want to make the life of our users easier.
Checking out a fork is easier than manually applying patch files.

So the difference between pg_tde and open_pg_tde is not whether you patch PostgreSQL, you do, either way.
It’s how those patches reach you.

PERCONA_API_VERSION

The open_pg_tde commit that removes “vendor lock-in” is mainly a documentation change:
it adds the patch file, and asks users to apply it manually.

It has one single code change other than that:
in our upstream fork we define PERCONA_API_VERSION and check against it, and open_pg_tde removes both.

While it has PERCONA in its name, and because of that it has been misinterpreted as vendor lock-in, that isn’t why we added it:
it’s to prevent accidents.

And with encryption, which modifies the storage of data, accidents might mean data corruption.

PostgreSQL normally is very stable:
minor versions contain only bugfixes, the API/ABI is very stable, a minor upgrade is considered easy and safe.

However, when you start applying patches manually, you break this promise:
is your patch as stable as PostgreSQL itself?

The reality is that it’s not.
pg_tde is backported to earlier major versions. Both pg_tde and open_pg_tde support PostgreSQL 16, and we could backport it to even earlier versions.

This means that the patch has to apply to multiple major versions, and if we have to make a breaking change in it?
Then we have to make that change in all major versions!

Our API version isn’t about locking users to our fork, it’s a safety net.
It is there to make sure that upgrades happen correctly, and our users don’t accidentally mix pg_tde and PostgreSQL packages that aren’t 100% compatible, but seem to work.

Imagine this:

1. You have a working PostgreSQL + pg_tde installation
2. There’s an update, you upgrade PostgreSQL
3. What you didn’t notice is that we also updated pg_tde.
   You forgot to update that package, or for some reason intentionally didn’t update it yet, and because we didn’t break the API boundary, everything seems to work.
   Except, we did change some internal details of how our patch works.
   It doesn’t cause any visible issues at first, but slowly some pages of your database are becoming unreadable, or you start getting segmentation faults when accessing a table…

The above of course is a hypothetical scenario, we didn’t release any dangerous update like that.

But the point is, even if we have to, in pg_tde, we have safeguards.
In our solution the API version check catches this: at the 3rd step, instead of a slow data corruption, we present you with an early error stating that you should fix your system.

In the above scenario, let’s say that in the first step you have a working installation where both the server and pg_tde have PERCONA_API_VERSION=1.
After that, you only update the server, and the new version now has PERCONA_API_VERSION=2.
Since the extension is still at the previous version 1, the server will fail immediately at startup, reminding you that these two packages are not compatible.

Maybe we could have called it something different, TDE_PATCH_VERSION, or something like that.
But the goal is still the same, it is an important safeguard, please do not remove it!
Currently open_pg_tde doesn’t protect against this type of mismatch.

In fact, in our latest release, not yet included in open_pg_tde, we did increment PERCONA_API_VERSION because of a slightly incompatible change.
We don’t expect any data corruption possibilities from it, it was a very minor API change, but we want to play things safe, as keeping the data of our users secure is our first priority.

Patches welcome!

If we look into the git history of open_pg_tde, it’s mostly (AI written) documentation changes.
Other than the removal of the API check I explained above, there are two actual code changes:

• One is the addition of the AES-XTS algorithm for encrypting relation pages
• Another is the support for temporary file encryption

We would be happy to start a discussion of any of these features, or even others.
If you want to contribute, please do not hesitate, and contact us.

This is the strength of open source: it allows contributions and open discussions.
While the team behind open_pg_tde, or anybody else, can of course fork pg_tde this way (because we are open and do not gatekeep features), the energy spent on that could be used to make pg_tde better in a shared community effort.

You can open a Pull Request, or a GitHub Issue, or even use our Jira, all options are equally good!

Even an issue/PR stating “please call PERCONA_API_VERSION differently”, we are not against changing that if the consensus is that it should be something different.

But please don’t create forks without even reaching out to your “upstream”.
That only increases fragmentation, and makes the life of our common audience harder.

What we ask for is simple: let’s make PostgreSQL better with our work, not harder to use.

pg_tde: our fork is temporary, our commitment to open TDE is not appeared first on MariaDB.org

Source

Updated MariaDB C++ and ODBC Connectors now available appeared first on MariaDB.org

Percona Operator for MySQL 1.2.0 is out, and it closes three gaps that platform teams hit once a MySQL deployment grows past a single cluster. Picture a fleet that has outgrown one region: you want a warm replica cluster in a second data center, backups in object storage that pass an auditor’s encryption check, and volumes that grow before they fill at 3 a.m. Until now, each of those meant scripting around the operator. This release brings all three into the custom resource.

The three headline features are cross-site replication for Group Replication, encrypted backups, and automatic storage scaling. Each one turns a manual, error-prone procedure into a declarative field you set once and let the operator reconcile.

The operator is open source and runs on any CNCF-certified Kubernetes distribution. Many of the changes in this release come straight from what users asked for on forums.percona.com and in the public issue tracker, from disaster-recovery topologies to backup encryption to storage that keeps up with data growth.

In this post, you’ll learn about:

• Cross-site replication for Group Replication clusters
• Encrypted backups to S3, GCS, and Azure
• Automatic storage scaling for MySQL data volumes
• Other improvements worth knowing about

Cross-site replication for Group Replication

Group Replication gives you a self-healing, multi-primary-capable cluster inside one Kubernetes cluster. What it does not give you on its own is a second site. If the region hosting your cluster goes down, Group Replication cannot fail over to hardware it does not know about. Teams have solved this by hand-wiring asynchronous replication between clusters and babysitting it, which is exactly the kind of stateful glue an operator should own.

Why it matters

A disaster-recovery topology is only useful if it is reproducible and observable. Hand-built replication links drift: someone changes a credential, a channel stalls, and nobody notices until the failover that was supposed to save you does not work. Declaring the topology as a Kubernetes object means the operator reconciles it continuously, and the same manifest recreates it in staging, in a runbook test, and in the real event.

How it works

The operator adds a new custom resource, PerconaServerMySQLClusterSet, that groups two or more Group Replication clusters into a single set with one primary. The operator drives MySQL Shell to build the InnoDB ClusterSet, wires the replica clusters to the primary, and tracks the topology in the resource’s status. A replica cluster provisions from the primary using a chosen recovery method, so you do not stage data manually.

apiVersion: ps.percona.com/v1
kind: PerconaServerMySQLClusterSet
metadata:
  name: my-cluster-set
  finalizers:
    - percona.com/clusterset-dissolve
spec:
#  unsafeFlags:
#    forcedFailover: false
#    forcedClusterRemoval: false
  primaryCluster: pscluster1
  credentialsSecret:
    name: ps-cluster1-secrets
    key: clusterset
  sslMode: AUTO
  createReplicaClusterOptions:
    recoveryMethod: clone
  clusters:
    - innodbClusterName: pscluster1
      endpoints:
      - host: ps-cluster1-mysql-primary.default.svc.cluster.local
    - innodbClusterName: pscluster2
      endpoints:
      - host: ps-cluster2-mysql-0.ps-cluster2-mysql.default.svc.cluster.local
  mysqlshellRunner:
    image: percona/percona-server:8.4.10-10.1

primaryCluster names the source of truth. Each entry under clusters points at a Group Replication cluster by its InnoDB cluster name and reachable endpoints, so the clusters can live in separate namespaces or separate Kubernetes clusters joined by routable DNS. createReplicaClusterOptions.recoveryMethod: clone tells the replica to seed itself with a full clone. The percona.com/clusterset-dissolve finalizer ensures the operator tears the ClusterSet down cleanly instead of leaving orphaned replication channels behind.
 

Failover and cleanup

Once the set exists, the operator keeps the replica clusters attached to the primary and surfaces the topology in the resource’s status, so you can see which cluster is primary and whether every replica is connected without shelling into MySQL Shell. A planned switchover promotes a replica to primary. The unsafeFlags block gates the disruptive paths for when the primary is already gone.

Note: The unsafeFlags block gates disruptive operations such as forced failover and forced cluster removal. Leave these off for normal operation and reach for them only in a controlled recovery, since a forced failover can diverge history if the old primary comes back.

Encrypted backups

Backups are the copy of your data most likely to leave the cluster’s security boundary. They land in an object-storage bucket, get replicated across a provider’s regions, and often live longer than the database that produced them. If they are not encrypted before they leave the pod, a bucket misconfiguration or a leaked credential exposes the whole dataset. This release lets the operator encrypt backup data as it is

How it works

Backups in the operator run on Percona XtraBackup. XtraBackup encrypts the stream with its xbcrypt component before uploading, so the data is ciphertext at rest in the bucket and stays that way until you restore it with the same key. You supply the key through a Kubernetes Secret and reference that Secret from the backup configuration. The operator never bakes the key into a manifest or an image.

Wiring it up

Point a storage target at an encryption-key Secret with encryptionKeySecret:

apiVersion: ps.percona.com/v1
kind: PerconaServerMySQL
metadata:
  name: cluster1
spec:
  backup:
    storages:
      s3-us-west:
        type: s3
        encryptionKeySecret:
          key: encryptionKey
          name: my-s3-encryption-key-secret
        s3:
          bucket: S3-BACKUP-BUCKET-NAME-HERE
          credentialsSecret: cluster1-s3-credentials
          region: us-west-2

The same encryptionKeySecret field works under S3, GCS, and Azure storage targets, so a multi-cloud backup policy uses one consistent mechanism. You can also set an encryptionKeySecret at the backup level to apply one key across every storage target instead of repeating it per bucket. The referenced Secret holds the key under the encryptionKey data field.

Restoring an encrypted backup

Encryption is transparent on the way back in. When you restore, the operator reads the same Secret, hands the key to XtraBackup, and decrypts the stream before it prepares the data directory. The only hard requirement is that the key still exists: the restore fails fast if the Secret is missing or holds a different key than the one that produced the backup. Encryption also composes with backup compression, so you keep the smaller footprint and the ciphertext-at-rest guarantee at the same time.

Note: Keep the encryption key safe and versioned outside the cluster. A backup encrypted with a key you have lost is not recoverable. Treat the key with the same care as the backups themselves.

Automatic storage scaling

Running out of disk is one of the fastest ways to take a database down, and it rarely happens at a convenient hour. The operator has supported manual volume expansion since an earlier release: you raise resources.requests.storage, apply, and the operator grows the PersistentVolumeClaim for you. That still requires a human to notice the trend and act. Version 1.2.0 adds automatic scaling that watches usage and grows the volume on its own.

Why it matters

Storage growth is predictable in aggregate and unpredictable in timing. A batch import, a retention change, or an unexpected traffic spike can eat headroom faster than an on-call engineer can respond. Letting the operator resize before the volume fills turns a page someone at 3 a.m. incident into a log line, as long as your storage class supports online expansion.

How it works

You enable volume expansion, then define an autoscaling policy. The operator monitors each data PVC and, when usage crosses the threshold, grows the volume by a fixed step up to a ceiling you set. Because it builds on the Kubernetes volume-expansion API, the underlying storage class must have AllowVolumeExpansion: true.

Wiring it up

apiVersion: ps.percona.com/v1
kind: PerconaServerMySQL
metadata:
  name: cluster1
spec:
  enableVolumeExpansion: true
  storageScaling:
    enableVolumeScaling: true
    autoscaling:
      enabled: true
      growthStep: 2Gi
      maxSize: 10Gi
      triggerThresholdPercent: 80

triggerThresholdPercent is the fill level that triggers a resize (default 80, allowed range 50 to 95). growthStep is how much capacity each resize adds (default 2Gi), and maxSize caps total growth so a runaway workload cannot expand a volume without bound. The operator validates the relationship between these fields: autoscaling cannot be enabled unless enableVolumeScaling is on. For teams that prefer an external controller to own resizing, enableExternalAutoscaling hands that responsibility off instead.

The operator records each resize in the cluster status under storageAutoscaling, including the count of resizes and the timestamp of the last one. That gives you an audit trail and a signal worth alerting on: a volume that keeps hitting its growthStep is telling you the workload has changed, and a volume approaching maxSize is telling you to plan capacity before the ceiling stops the next resize.

Note: PVC expansion is one-way. Kubernetes can grow a volume but cannot shrink it, so set maxSize deliberately. Confirm your storage class allows expansion before you rely on this in production.

 

Other improvements

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

• Dedicated root user Secret (K8SPS-689): the operator publishes root connection details in a dedicated Secret, so applications and tooling read one predictable object instead of parsing several.
• Disable NodePort allocation for LoadBalancer Services (K8SPS-496): set allocateLoadBalancerNodePorts: false to stop Kubernetes from opening NodePorts you never use behind a cloud load balancer.
• Custom cluster naming for PMM (K8SPS-627): give a cluster a stable display name so multi-region and multi-namespace fleets stay legible in Percona Monitoring and Management.
• Vault encryption Secret validation (K8SPS-487): the operator validates the Vault Secret and reports problems in status immediately, instead of failing later during an operation.
• Concurrent reconciliation (K8SPS-434): tune how many clusters the operator reconciles at once through an environment variable, which helps a single operator manage a larger fleet.
• Independent mysql-monit sidecar resources (K8SPS-742): set CPU and memory for the monitoring sidecar separately from the database container.
• Orchestrator API authentication (K8SPS-19): the Orchestrator API now requires valid credentials.
• Binlog storage configuration in restore objects (K8SPS-716): point a restore at the binlog storage it needs for point-in-time recovery.

For the full list, including bug fixes, see the release notes linked below.

Conclusion

Percona Operator for MySQL 1.2.0 extends the operator across the parts of the lifecycle that used to need custom glue: replication across sites, encryption of data that leaves the cluster, and storage that keeps up with growth. Platform teams running MySQL fleets on Kubernetes get declarative control over disaster recovery, a cleaner path through a security review, and one less 3 a.m. page. If there is a topology or a control you still have to script around, tell us on the forum, since that feedback is where releases like this one come from.

Try Percona Operator for MySQL 1.2.0

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

The post Percona Operator for MySQL 1.2.0: Cross-Site Replication, Encrypted Backups, and Automatic Storage Scaling appeared first on Percona.

Percona Operator for MySQL 1.2.0: Cross-Site Replication, Encrypted Backups, and Automatic Storage Scaling appeared first on MariaDB.org

Practical migration paths from the Crunchy Data PostgreSQL Operator to the Percona Operator for PostgreSQL are described here. 

1. Migration to standby cluster utilizing the same pgBackRest repository

In this method, the Percona cluster is created as a standby and points to the same pgBackRest repository used by the Crunchy Data PostgreSQL Operator cluster. This means the object storage and the path are exactly the same for both clusters.

With the above configuration, the Percona standby restores the initial backup from the shared repository and replays archived WAL. The method is described here.

2.Migration to Standby Cluster with Streaming Replication

  In this approach, the standby cluster initiates a pg_basebackup for the initial restore and uses native postgres streaming for sync. The method is described here.

3.Backup and Restore 

In this method, a backup is taken from the Crunchy cluster and restored to a cluster managed by the Percona Operator for PostgreSQL.

4.Migration by reusing the persistent volume

In this method, the existing Crunchy Data PostgreSQL Operator primary’s PGDATA persistent volume is used. The process is: stop writes to the Crunchy Data PostgreSQL Operator cluster, delete the cluster while retaining the persistent volume, clear the old persistent volume claim reference, and create a Percona cluster whose PVC selector binds to that same retained PV. PostgreSQL then starts on the existing data directory without a restore.

Each method works, but each one is suitable for a different operational scenario. A migration strategy that is ideal for a small development database may be risky for a large production system with heavy write traffic. Similarly, a method that gives the lowest downtime may require more preparation and validation.

This post compares the four migration approaches so users can choose the most suitable approach.

Factors to Consider Before Migration

There is no single best migration strategy for every PostgreSQL workload. A production migration usually has to balance several requirements:

• How Much Downtime is Acceptable?
• What is the Network Connectivity Status Between the Clusters?
• What is the RTO/RPO When Something Goes Wrong?
• How Large is the Database?
• How Write-Heavy is the Workload?
• How Quickly Must the System be Rolled Back?
• Is the Migration Happening Across Namespaces, Clusters, Storage Classes, or Cloud?

Comparison of Migration Methods

Conclusion

There is no “one-size-fits-all” solution for migrating PostgreSQL workloads on Kubernetes. Choosing the right strategy requires a careful balance between acceptable downtime, operational complexity, and business continuity requirements. For example, below there are some scenarios which list the suitable approaches

• For scenarios where zero downtime is not strictly required but minimal operational impact is preferred, migration via a shared pgBackRest repository could be a feasible solution.
• For environments where network latency is low and real-time synchronization is feasible, Streaming Replication could be a feasible solution.
• For simpler migrations where network connectivity between clusters is not feasible, a standard Backup and Restore will work.
• For rapid migrations involving massive datasets where storage mobility is not required, reusing the existing persistent volume can significantly reduce migration time.

Before proceeding, we recommend conducting a dry run in a staging environment to validate your chosen method against your specific network topology and workload requirements. By carefully evaluating these trade-offs, you can ensure a secure, efficient transition to the Percona Operator for PostgreSQL.

The post Comparing Migration Methods from the Crunchy Data PostgreSQL Operator to the Percona Operator for PostgreSQL appeared first on Percona.

Comparing Migration Methods from the Crunchy Data PostgreSQL Operator to the Percona Operator for PostgreSQL appeared first on MariaDB.org

Continue reading “TAF 3.0 — Results Backend With Automated Performance Change Detection”

The post TAF 3.0 — Results Backend With Automated Performance Change Detection appeared first on MariaDB.org.

TAF 3.0 — Results Backend With Automated Performance Change Detection appeared first on MariaDB.org

This replica cluster needs to stay in sync with the primary, remain protected from accidental writes, and be ready to take over when you need to move traffic, either as a planned operation or during an outage.

InnoDB ClusterSet addresses this by linking multiple MySQL clusters into a single disaster-recovery topology. One cluster handles writes, while the others stay synchronized as read-only replicas.

Starting from v1.2.0, the Percona Operator for MySQL adds a new custom resource, PerconaServerMySQLClusterSet, which allows managing InnoDB ClusterSets. Creating the ClusterSet, adding replicas, switching the primary, and performing a forced failover are all handled declaratively by updating the Kubernetes spec and letting the operator reconcile the desired state.

This post explains how ClusterSet works, how to set it up with the Percona Operator, and how planned switchovers and emergency failovers work in practice.

Understanding InnoDB ClusterSet

Any disaster recovery design usually comes down to two important numbers:

• Recovery Point Objective, or RPO, is how much data you can afford to lose. For example, an RPO of five seconds means the business can tolerate losing up to five seconds of writes.
• Recovery Time Objective, or RTO, is how long the system can be unavailable before service must be restored.

The way you design and operate a ClusterSet directly affects both. To understand why, it helps to first look at the architecture.

An InnoDB ClusterSet is built from two or more InnoDB Clusters. Each InnoDB Cluster is a Group Replication group. In other words, it is the same kind of highly available MySQL cluster that the Percona Operator for MySQL can already deploy and manage.

A ClusterSet adds another layer on top of those clusters. One cluster is the primary cluster and accepts writes, while the others are replica clusters and remain read-only. The primary sends its changes to each replica using asynchronous replication over a dedicated replication channel.

This gives us two layers of replication, each solving a different problem.

Inside each cluster, Group Replication protects against the loss of individual MySQL nodes. Members are expected to be closer together, usually within the same region or availability zone group. Writes are coordinated by the group, which helps keep the local cluster consistent and highly available.

Between clusters, asynchronous replication protects against the loss of an entire site. Replica clusters can be located in another region, another Kubernetes cluster, or another cloud provider. Because this replication is asynchronous, long-distance network latency does not slow down writes on the primary cluster.

But the tradeoff here is that a replica cluster may be slightly behind the primary. The amount of lag depends on write volume, network latency, and the health of the replication channel. If the primary site is lost, any writes that had not yet reached the replica are lost. That lag is the practical data-loss window during an emergency failover. Any transactions that had not replicated before failover could be lost.

Before building a ClusterSet with the operator, there are a few important requirements to keep in mind:

• Every cluster in the ClusterSet must use the Group Replication topology. The operator also supports asynchronous replication with Orchestrator for standalone clusters, but that topology cannot be part of an InnoDB ClusterSet.
• You need MySQL 8.0.27 or later
• Clusters are linked by network address, not by Kubernetes references. A replica cluster only needs to be reachable and managed by an operator. It does not need to live in the same Kubernetes cluster as the primary.

With the model in place, let’s build a simple cross-site disaster recovery setup.

Setting up ClusterSet

We’ll create the simplest useful ClusterSet: two Group Replication clusters named dc1 and dc2.

In this example:
dc1 is the primary cluster.
dc2 is the read-only replica cluster.

In a real deployment, these would usually run in separate Kubernetes clusters, regions, or cloud environments. The steps are mostly the same. The main requirement is that the endpoints listed in the ClusterSet spec must be routable between sites.

Creating a primary cluster

The primary cluster dc1 is a regular Group Replication cluster. There is nothing ClusterSet-specific about it at this stage.

apiVersion: ps.percona.com/v1
kind: PerconaServerMySQL
metadata:
 name: dc1
spec:
 mysql:
 clusterType: group-replication
 # ... the rest of a normal cluster spec

You can find a complete YAML here. Apply it and wait for it to come up the way you normally would, just as you would for any normal Percona Operator-managed MySQL cluster.

Creating the replica cluster

The replica cluster dc2 is also a Group Replication cluster, but with one important difference:

apiVersion: ps.percona.com/v1
kind: PerconaServerMySQL
metadata:
 name: dc2
spec:
 mysql:
 clusterType: group-replication
 bootstrap:
 mode: manual  # <- set this!

Normally, when the operator creates a Group Replication cluster, the first MySQL pod bootstraps the group as soon as it starts. Subsequent pods then join that group.

For a ClusterSet replica, that is not what we want. We do not want dc2 to form an independent empty cluster. Instead, we want it to receive data from the primary cluster and then join the ClusterSet as a replica.

With bootstrap.mode: manual, the first pod starts but does not bootstrap its own Group Replication group. It waits until the ClusterSet process adopts it, clones data from the primary, and then forms the replica cluster. During this stage, the first dc2 pod may remain in a NotReady state until it is a part of the ClusterSet.

Sharing cluster credentials

The operator automatically creates a clusterset MySQL user in every cluster and stores its password in the cluster secret.

The operator uses this user to orchestrate ClusterSet operations, so the password must be the same across all clusters in the ClusterSet. When your clusters are deployed separately, copy the clusterset value from the primary cluster secret into the replica cluster secret before linking them.

For example, if dc1 is the primary, copy the clusterset password from the dc1 secret into the corresponding secret for dc2.

Linking the clusters

Once both clusters are applied, create a PerconaServerMySQLClusterSet custom resource.

apiVersion: ps.percona.com/v1
kind: PerconaServerMySQLClusterSet
metadata:
 name: my-cluster-set
 finalizers:
 - percona.com/clusterset-dissolve
spec:
 primaryCluster: dc1
 credentialsSecret:
 name: dc1-secrets
 key: clusterset
 sslMode: AUTO
 createReplicaClusterOptions:
 recoveryMethod: clone
 clusters:
 - innodbClusterName: dc1
 endpoints:
 - host: dc1-mysql-primary.default.svc.cluster.local
 - innodbClusterName: dc2
 endpoints:
 - host: dc2-mysql-0.dc2-mysql.default.svc.cluster.local
 mysqlshellRunner:
 image: perconalab/percona-server-mysql-operator:main-psmysql8.4

The most important fields are:

• primaryCluster defines which cluster currently accepts writes. The value must match one of the entries under clusters.
• clusters lists every member of the ClusterSet and the endpoint the operator should use to reach it. These endpoints are plain network addresses, which is what allows members to run in different Kubernetes clusters or regions.
• credentialsSecret points to the secret that contains the clusterset user password.
• recoveryMethod: clone tells the replica cluster to take a full copy of the primary data when it joins the ClusterSet. The alternative is an incremental recovery method, which uses existing binary logs instead of cloning the full dataset.
• mysqlshellRunner defines the helper pod image used by the operator to run MySQL Shell operations.

After you apply this resource, the operator starts a MySQL Shell runner pod and creates the ClusterSet on dc1. It then joins dc2, which clones the data, starts replication, and brings up the remaining pods in the replica cluster.

At this point, dc1 serves reads and writes, while dc2 acts as a live read-only copy.

Seeding large replica clusters

In this example, the replica cluster is created with recoveryMethod: clone, so MySQL Shell provisions the first replica member by copying a physical snapshot from an existing ClusterSet member. That is convenient for medium/small datasets, but it can be fragile across WAN links or very large databases.

A full clone can take hours, consume significant bandwidth, add load to the donor, run into network interruptions, and become expensive to retry if the operation fails partway through. It can also not be the best fit when the primary is busy or when cross-region egress cost is a concern.

The operator makes it possible to seed the replica cluster from an existing backup of the primary cluster instead. Create a PerconaServerMySQLBackup on the primary, restore that backup into the replica cluster with PerconaServerMySQLRestore, and then add the replica to the ClusterSet using recoveryMethod: incremental. You can find the exact restore procedure in the documentation.

At that point, the replica already has the primary’s data and GTID history, so ClusterSet only needs to catch it up from the primary’s binary logs instead of transferring the full dataset again.

Verifying it worked

The simplest way to confirm that the ClusterSet is working is to write data to the primary cluster and read it from the replica.

For example:

• Connect to dc1.
• Create a test table or insert a row.
• Connect to dc2.
• Confirm that the same data appears there.

If the row appears on dc2, the asynchronous replication channel is running and the replica cluster is receiving changes from the primary.

Planned Switchover

A planned switchover is used when both clusters are healthy and you intentionally want to move writes from one site to another. This is useful for regional maintenance, Kubernetes cluster upgrades, cloud migrations, or controlled DR testing.

To move the primary role from dc1 to dc2, update the primaryCluster field:

kubectl patch ps-clusterset my-cluster-set --type=merge 
 -p '{"spec":{"primaryCluster":"dc2"}}'

The operator notices that the desired primary cluster no longer matches the current primary. It then uses MySQL Shell to perform a clean switchover.

Because both clusters are available, the operator can make sure the replica has caught up before changing roles. After the switchover completes, dc2 becomes the writable primary and dc1 becomes a read-only replica.

Emergency Failover

An emergency failover can be used when the primary cluster is unreachable and a clean handover is no longer possible.

This is the disaster recovery case: the Kubernetes cluster, region, or network path to the primary may be down, and you need to promote a surviving replica so the application can resume writes.

To fail over to dc2, update primaryCluster and explicitly set the forced failover flag:

kubectl patch ps-clusterset my-cluster-set --type=merge 
 -p '{"spec":{"primaryCluster":"dc2","unsafeFlags":{"forcedFailover":true}}}'

The operator only follows this path when it can confirm that the current primary cluster is unreachable. It then promotes dc2, allowing it to accept writes.

The explicit flag is important because failover can cause data loss. Replication between clusters is asynchronous, so any writes that reached the old primary but had not yet replicated to dc2 are not present on the new primary. Once dc2 is promoted, those missing writes become unrecoverable through normal ClusterSet recovery.

The risk of data loss is why the field is named unsafeFlags.forcedFailover.

Another important point is that when the old primary comes back, it does not automatically resume as primary. After a forced failover, the recovered cluster must be explicitly reintroduced into the ClusterSet as a replica.

Adding and removing clusters

Adding or removing clusters follows the same declarative pattern: update the custom resource spec and let the operator reconcile the difference.

To add another replica cluster, add a new entry under clusters:

apiVersion: ps.percona.com/v1
kind: PerconaServerMySQLClusterSet
metadata:
 name: my-cluster-set
spec:
 # .. existing spec
 clusters:
 # .. existing clusters
 - innodbClusterName: dc3
 endpoints:
 - host: dc3-mysql-primary.default.svc.cluster.local

The operator joins the new cluster in the same way it joined dc2: it clones data from the primary, configures replication, and brings the cluster into the ClusterSet as a read-only replica.

To remove a cluster, delete its entry from the clusters list. You can update your manifest and reapply it, or use a JSON patch:

kubectl patch ps-clusterset my-cluster-set --type=json 
 -p '[{"op":"remove","path":"/spec/clusters/1"}]'

If the cluster is healthy, the operator detaches it cleanly and it becomes a normal standalone cluster again.

If the cluster being removed is unreachable, you can force its removal:

kubectl patch ps-clusterset my-cluster-set --type=json -p '[
 {"op":"remove","path":"/spec/clusters/1"},
 {"op":"add","path":"/spec/unsafeFlags/forcedClusterRemoval","value":true}
]'

Like forced failover, forced removal is gated behind an unsafe flag because the operator should not make this decision silently. Removing an unreachable cluster from a ClusterSet is an operational decision with consequences, and it should be made explicitly.

Wrapping up

The Percona Operator for MySQL allows extending Group Replication beyond a single site by managing InnoDB ClusterSet through a custom resource PerconaServerMySQLClusterSet. A primary cluster handles writes, replica clusters stay synchronized, and the operator manages switchovers, failovers, and membership changes declaratively.

For planned maintenance, switchover moves the primary role safely with no data loss. For outages, forced failover promotes a surviving replica, with the expected risk of losing any writes that had not yet replicated. That replication lag is the practical RPO, so it should be monitored and tested as part of the DR plan.

With the Percona Operator for MySQL, disaster recovery becomes repeatable, Kubernetes-native, and easier to operate across regions or clusters.

Further reading

• InnoDB ClusterSet docs
• Cross-site replication in Percona Operator for MySQL

Cross-site Disaster Recovery with Percona Operator for MySQL appeared first on MariaDB.org

Continue reading “MariaDB Server Plugins: disabled functions”

The post MariaDB Server Plugins: disabled functions appeared first on MariaDB.org.

MariaDB Server Plugins: disabled functions appeared first on MariaDB.org

Continue reading “MariaDB 13.1 Feature in Focus: DENY / Negative Grants”

The post MariaDB 13.1 Feature in Focus: DENY / Negative Grants appeared first on MariaDB.org.

MariaDB 13.1 Feature in Focus: DENY / Negative Grants appeared first on MariaDB.org

Continue reading “Continuent joins MariaDB Foundation as a Silver Sponsor”

The post Continuent joins MariaDB Foundation as a Silver Sponsor appeared first on MariaDB.org.

Continuent joins MariaDB Foundation as a Silver Sponsor appeared first on MariaDB.org

Continue reading “Lowering the Barrier for MariaDB Plugin Development: Plugins in More Languages”

The post Lowering the Barrier for MariaDB Plugin Development: Plugins in More Languages appeared first on MariaDB.org.

Lowering the Barrier for MariaDB Plugin Development: Plugins in More Languages appeared first on MariaDB.org

Continue reading “Nextcloud renews its Silver sponsorship of MariaDB Foundation”

The post Nextcloud renews its Silver sponsorship of MariaDB Foundation appeared first on MariaDB.org.

Nextcloud renews its Silver sponsorship of MariaDB Foundation appeared first on MariaDB.org

This is the point of ELS. When a major version reaches End of Life (EOL), the community stops shipping patches, but the databases running on it don’t stop mattering. ELS keeps critical bug and security fixes coming for versions that are past their EOL date, so you can stay on 5.7 or 8.0 on your own timeline instead of a deadline someone else set.

What we did

These CVE fixes landed upstream outside the normal cadence. Under ELS, customers are entitled to security fixes for the versions they run, so we pulled the patches into the 5.7 and 8.0 builds and re-released them. ELS customers can pull the updated builds from the usual private repository.

Why this matters if you’re still on 5.7 or 8.0

Percona Server for MySQL 5.7 reached EOL in October 2023. Percona Server for MySQL 8.0 reached EOL in April 2026. Plenty of production systems are still on both, and not every migration can happen on the upstream’s schedule. Running an unpatched database past EOL is where the real risk sits: no security fixes, no bug fixes, and no support when something breaks at 2:00 a.m.

ELS closes that gap. You keep getting the critical fixes, including out-of-schedule security patches like these, while you plan an upgrade on terms that work for your team.

Where to go from here

If you’re on 5.7 or 8.0 and don’t have ELS in place, now is a good time to look at it. The fixes we just shipped are exactly what the program is for. See the details for your version: Extended Lifecycle Support for MySQL 8.0 or Extended Lifecycle Support for MySQL 5.7. Or reach out via percona.com or the Percona Community Forum to discuss coverage for your environment.

Written by @Dennis Kittrell – Reviewed by @Matthew Boehm & @Varun Nagaraju

The post Still on MySQL 5.7 or 8.0? Those high-severity CVE fixes are covered appeared first on Percona.

Still on MySQL 5.7 or 8.0? Those high-severity CVE fixes are covered appeared first on MariaDB.org

Introduction

Vacuum, or more precisely autovacuum, is the most important automatic maintenance task in PostgreSQL. It is key for performance, but also for long-term database survival. If it runs too often, it can damage performance. If it does not run often enough, performance can suffer. With too few workers, it takes too long. With too many, it consumes resources. If the maintenance work memory is not enough, the load can multiply due to multiple index scans. If you disable it completely, it will rise from the dead and run without limits.

I guess you get it. It is critical to understand what autovacuum does and how it does it.

Autovacuum is triggered when certain row count thresholds are crossed. In the final part of this post we describe a benchmark we run to validate if modified rows is the right approach to trigger automatic vacuum execution or we should consider something different like page based thresholds. We will also measure the impact index in vacuum.

This blog post explains how autovacuum works, but some previous basic understanding of PostgreSQL internals is required.

Here are the terms you’ll need, feel free to skip if you already know them:

• MVCC (multi-version concurrency control): Rather than overwrite a row, PostgreSQL keeps multiple versions of it. This is used to provide consistent views to the different transactions running at the same time, which is why obsolete row versions pile up when there are long running transactions and/or tables are not vacuumed. MVCC is used by transactions to determine row visibility.
• Tuple: One on-disk version of a row. A row updated three times leaves behind three tuples.
• Dead tuple: A tuple that is not visible to any transaction. Reclaiming these is the vacuum’s main job.
• Heap: A table’s main structure, where the tuples live. Indexes are separate structures.
• Page (block): The 8 KB unit PostgreSQL reads and writes. A heap is an array of pages. If a page is dirty, it means it contains data that hasn’t been written to disk yet.
• TID: A tuple’s address: which page, which slot. Inside of the pages there is an array that points to the actual row position in the page, the slot is the position in that array. This way row space inside of the page can be reorganized without changing the TID. Index entries are TIDs pointing into the heap.
• Vacuum: The operation that removes dead tuples (and does a few things more).
• Autovacuum: Vacuum that PostgreSQL runs for you, in the background, on its own schedule.
• Visibility map (VM): A small per-table bitmap flagging which pages have all tuples visible or frozen (see freezing).
• Freezing: Stamping old tuples as permanently visible, so their transaction IDs are no longer relevant (see Transaction ID wraparound). Here permanent is a bit misleading, if the row is modified, the permanent tuple will become a dead tuple and will be removed by vacuum.
• Transaction ID (XID) wraparound: Each transaction is assigned an ID. This ID identifies which transactions modified which rows and is thus critical for visibility. The problem is that the transaction counter is finite and eventually wraps around. To avoid problems, older rows must be marked as permanently visible (frozen), this way their transaction id becomes irrelevant.
• Bloat: Space allocated by dead tuples, as dead tuples are not visible, it is wasted space. Vacuum works to reduce it, but can’t always reverse it.
• Shared buffers: PostgreSQL’s in-memory page cache. Nearly all reads and writes pass through it.
• WAL (write-ahead log): Every change is logged here before it touches a data page, so the database can recover after a crash.
• Checkpoint: The point at which modified (“dirty”) pages in shared buffers are written out to the data files.

Launcher & Worker Architecture

The autovacuum launcher is a background process that starts autovacuum workers. Its goal is to start one worker per database every autovacuum_naptime seconds (default: 1 min). With N databases, this means the launcher starts a new worker roughly every autovacuum_naptime / N seconds, round-robin across databases. But it is not the launcher that starts the workers. It requests the postmaster to fork an autovacuum worker for the chosen database.

Workers, once spawned, run independently until they finish all eligible tables in their assigned database and then exit. Up to autovacuum_max_workers (default 3) workers can run concurrently, and there is no restriction on how many of those may be in the same database. If a database has many tables that need vacuuming, it can run multiple concurrent vacuum workers. In this case, workers coordinate to avoid vacuuming the same table.

A database approximately has a worker assigned every “nap time seconds” or later. A worker is assigned even if there are no tables requiring vacuum.

Table Selection & Prioritization

This is the process inside a worker:

1. Scans pg_class to enumerate all tables in the database, then fetches per-relation statistics (dead tuple counts, etc.) from the cumulative statistics system (pgstat) for each one.
2. Compares each table’s dead-tuple count against the vacuum threshold (see the formula below).
3. Also checks if the table needs an ANALYZE (separate threshold).
4. Also checks for anti-wraparound: if pg_class.relfrozenxid age exceeds autovacuum_freeze_max_age (default 200M transactions), or if pg_class.relminmxid age exceeds autovacuum_multixact_freeze_max_age (default 400M), the table is vacuumed regardless of all thresholds and even autovacuum_enabled = off on the table.

Prioritization

There is no table-level priority sorting in a database. A worker vacuums tables in the order they are collected from the pg_class scan. do_autovacuum() in src/backend/postmaster/autovacuum.c iterates the table_oids list directly. The worker claims each table sequentially by marking it as “mine” in shared memory (this triggers a brief lock to the memory structure so two workers don’t pick the same table at the same time), and calls table_recheck_autovac() to re-read catalog/pgstat and confirm the table still needs work (as it could have been already vacuumed by another worker). Anti-wraparound urgency is handled one level up, at database selection: the launcher’s do_start_worker() preferentially dispatches a worker to whichever database is closest to the wraparound limit. So there is no dead-tuple-count-based ordering of tables. Within a database, processing order is effectively catalog order.

How Vacuum Finds Pages to Process

Once a table is selected, the worker doesn’t blindly scan every page. It uses the visibility map (VM) to skip pages that do not need vacuuming.

The Visibility Map

Every table has an associated visibility map, a bitmap with two bits per heap page:

1. All-visible bit: every tuple on the page is visible to all current and future transactions. During a normal (non-aggressive) vacuum, this page can generally be skipped. There are no dead tuples to reclaim. However, even all-visible pages may be visited in some cases, such as for eager freezing or readahead optimization (pages are read sequentially even if some of them are not needed).
2. All-frozen bit: every tuple on the page is frozen, marked with the HEAP_XMIN_FROZEN infomask bits (since PostgreSQL 9.4, the value of xmin is preserved for forensics rather than physically overwritten with FrozenTransactionId although a lot of people still think the xmin is changed). The page can be skipped even during aggressive/anti-wraparound vacuum. An aggressive vacuum must visit all pages that are not all-frozen to freeze as many tuples as possible.

The VM makes vacuuming efficient because it reduces the number of pages to visit while searching for dead tuples. If a 10GB table has dead tuples on only 50 pages, vacuum reads the VM (around 320KB for a 10GB heap, 2 bits per 8KB page) and then focuses on those 50 pages rather than the full 10GB. We already mentioned that a normal vacuum can also visit some additional pages for eager freezing or readahead, but the VM still eliminates the vast majority of random I/O.

Visibility-map bits are cleared by backends running DML statements and usually set by autovacuum workers or manually triggered vacuum operations.

The VM is also used by index-only scans to determine whether visiting the heap page to validate tuple visibility is needed. If the page in the VM is marked as “all-visible,” then visibility checks are not required and we don’t need that extra access, improving performance significantly.

The Scan Process

The worker performs a sequential scan of the heap, but guided by the VM:

1. Read the VM to identify pages that are NOT all-visible and may contain dead tuples.
2. For each such page, read it into shared buffers (if not already there).
3. Examine each tuple’s header (t_xmin, t_xmax, t_infomask) to determine if the tuple is dead, meaning it was deleted or updated, and no running transaction can see it anymore.
4. Dead tuples are collected into an in-memory dead-TID store, since PG17 a TidStore, a compact adaptive-radix-tree keyed by block number that replaced the old sorted ItemPointer array and its hard 1 GB cap. Its size is bounded by autovacuum_work_mem (default -1, which falls back to maintenance_work_mem, default 64MB). For manual VACUUM, maintenance_work_mem is used directly.
5. If the work memory fills up before the table is fully scanned, the worker pauses the heap scan, processes the accumulated dead tuples (index cleanup + heap cleanup), then resumes the heap scan from where it stopped. This means a single vacuum of a large, heavily updated table may involve multiple passes through the indexes.

Limiting Cache Impact with the Buffer Ring

The step 2 above says “read the heap page into shared buffers”), but if vacuum has to pull every page it scans into shared_buffers, vacuuming a large table would evict the pages that other queries depend on, trashing the cache during a maintenance task. PostgreSQL prevents this with a buffer access strategy, commonly called a ring buffer.

Rather than allocating pages all over the shared pool, vacuum uses a small ring of shared pool pages that it reuses circularly: when it needs a buffer for a new page, and the ring is full, it recycles the oldest buffer in the ring instead of claiming another from shared_buffers. If vacuum needs a page already in the shared pool, that page is not added to the ring. The ring size is set by vacuum_buffer_usage_limit, default 2 MB in PG18 (256 buffers of 8 KB), ranges from 128 kB to 16 GB, with a limit of 1/8 of shared_buffers (you can set it higher, but it will limited to that value). A value of 0 disables the ring entirely, letting vacuum use as much of shared_buffers as it needs. The same limit applies to ANALYZE and to autovacuum (which runs the same vacuum code). The VACUUM command accepts a per-statement BUFFER_USAGE_LIMIT option.

The ring has consequences: When the buffer being recycled is still dirty, vacuum must write it out before reusing the slot. As WAL is written before the page, we have to flush any outstanding WAL for that page first. So once vacuum dirties more pages than the ring can hold, it begins doing its own writes inline rather than leaving them all for the checkpointer or background writer. This may look as a trade-off as the ring caps vacuum’s cache footprint, but requires vacuum to perform some of its own write-back (and WAL flushing) as it runs. But if a page was read into the ring, probably that pages was not very active and will not be read again soon. Raising vacuum_buffer_usage_limit (or setting it to 0) relaxes the limit: a faster vacuum. But a faster vacuum that will evict more active pages and later will require more work by the checkpointer.

Determining Tuple Liveness

For each tuple on a non-all-visible page, vacuum checks:

• t_xmin (inserting transaction identified): Check whether it committed. If the inserting transaction aborted, the tuple is dead immediately.
• t_xmax (deleting/updating transaction identifier): Check whether it committed and is older than the oldest running transaction (OldestXmin). If so, no active transaction can see this tuple version and the tuple is dead.
• Vacuum, like the other backends, reads pg_xact (the commit log / CLOG) to determine transaction commit status, and sets hint bits (HEAP_XMIN_COMMITTED, HEAP_XMAX_COMMITTED, etc.) on tuple headers so future accesses don’t need to re-check pg_xact. Changing the hint bits marks the page as dirty, but does not save that change in the WAL, unless specified in the configuration (checksums enabled or wal_log_hints). The purpose of the hint bits is help future transactions know the outcome of the inserting/modifying transactions without checking the commit log.

OldestXmin is the oldest transaction ID that any running transaction might still need to see, also known sometimes as the xmin horizon. Tuples deleted or replaced by transactions newer than OldestXmin cannot be vacuumed because some active transactions might still need them. This is why long-running transactions limit the space that vacuum can reclaim.

What Happens to Heap Pages

Once dead tuples are identified on a page:

1. Dead tuple line pointers are set to LP_DEAD during the heap scan phase. Later, after index cleanup removes all dangling index references, vacuum performs a second heap pass that converts these to LP_UNUSED, making the slots available for reuse. (For tables with no indexes, vacuum can mark LP_UNUSED immediately since there are no index pointers to worry about.)
2. The page is compacted. Live tuples are shuffled toward the high end of the page, and free space is consolidated in the middle (between the line pointer array and the tuple data area). This is called page pruning/defragmentation. It updates the page’s pd_lower (end-of-line pointers) and pd_upper (start-of-tuple data) to reflect the new free space.
3. The page is marked dirty in shared buffers. It will be written back to disk by the background writer, at the next checkpoint or if the ring buffer is full and vacuum needs that space. This is the vacuum_cost_page_dirty cost event (adds 20 to the cost global cost of running vacuum operations).
4. The VM is updated. If, after removing dead tuples, every remaining tuple on the page is visible to all transactions, the all-visible bit is set. During aggressive/anti-wraparound vacuum, if all tuples are also frozen, the all-frozen bit is set.
5. The FSM (Free Space Map) tree is updated periodically (every VACUUM_FSM_EVERY_PAGES pages or after heap/index cleanup pass, not after every individual page is added to the FSM) to advertise newly available space, so future DML operations can reuse it.

Heap Truncation

After processing all pages, vacuum checks whether the last pages of the heap file are entirely empty (all dead tuples were removed and there are no live tuples). If so, it truncates the file, physically shrinking it and returning disk space to the OS. This is the only situation where vacuum reduces the on-disk size of a table. The space reclaimed in the middle of the file is reused, not returned to the filesystem.

Truncation requires an AccessExclusiveLock lock during the truncation, which can cause a short stall on concurrent access. Table truncation can be disabled per-table or globally with vacuum_truncate = off.

Index Cleanup

Index cleanup is also required and is often an expensive part of vacuum. Indexes must be cleaned because they contain pointers (TIDs) to heap tuples. If the corresponding heap tuple is dead, the index entry becomes a dangling pointer and must be removed.

The Process