Continue reading “MariaDB Foundation releases the BETA of the Test Automation Framework (TAF) 2.5”

The post MariaDB Foundation releases the BETA of the Test Automation Framework (TAF) 2.5 appeared first on MariaDB.org.

The post MariaDB Foundation releases the BETA of the Test Automation Framework (TAF) 2.5 appeared first on MariaDB.org.

Continue reading “What Our Survey Says About MariaDB Preview Releases”

The post What Our Survey Says About MariaDB Preview Releases appeared first on MariaDB.org.

The post What Our Survey Says About MariaDB Preview Releases appeared first on MariaDB.org.

Continue reading “MariaDB Vector: How it works. Part IV”

The post MariaDB Vector: How it works. Part IV appeared first on MariaDB.org.

The post MariaDB Vector: How it works. Part IV appeared first on MariaDB.org.

The post The AWS Lambda ‘Kiss of Death’ appeared first on Shattered Silicon.

The post The AWS Lambda ‘Kiss of Death’ appeared first on MariaDB.org.

The post The AWS Lambda ‘Kiss of Death’ appeared first on Shattered Silicon.

The post The AWS Lambda ‘Kiss of Death’ appeared first on MariaDB.org.

Continue reading “A response to Percona’s 2026 MySQL ecosystem benchmark: useful data, but not a realistic MariaDB comparison”

The post A response to Percona’s 2026 MySQL ecosystem benchmark: useful data, but not a realistic MariaDB comparison appeared first on MariaDB.org.

The post A response to Percona’s 2026 MySQL ecosystem benchmark: useful data, but not a realistic MariaDB comparison appeared first on MariaDB.org.

We constantly update our bug reports and monitor other boards to ensure we have the latest information, but we wanted to make it a little easier for you to keep track of the most critical ones. This post is a central place to get information on the most noteworthy open and recently resolved bugs.

In this edition of our bug report, we have the following list of bugs.

Percona Server/MySQL Bugs

PS-10378: In the MeCab plugin, BOOLEAN MODE full-text queries with a LIMIT clause do not behave as expected. Although the optimizer indicates that ranking should be skipped (Ft_hints: no_ranking), the query still performs full ranking and sorting before applying LIMIT, preventing the intended optimization and impacting performance.

Reported Affected Version/s: 8.4.x
Upstream Bug: Not applicable
Workaround/Fix: No workaround available
Fixed/Planned Version/s: 8.0.46-37, 8.4.9-9, 9.7.0-0

PS-10448: Insert prepared statements fail on partitioned tables with timestamp-based partitions when the partition key uses a non-constant default (e.g., CURRENT_TIMESTAMP). After initial execution, the statement remains bound to the original partition and fails with a partition mismatch error when data should go into a different partition.

Reported Affected Version/s: 8.0.42-33, 8.0.43-34, 8.0.44-35, 8.4.7-7
Upstream Bug: Bug #119309
Workaround/Fix: Modify statements to explicitly use NOW() (requires updating procedures)
Fixed/Planned Version/s: 8.0.46-37, 8.4.9-9, 9.7.0-0

PS-10481: The range optimizer incorrectly falls back to a full table scan instead of using an index range scan for WHERE … IN() queries when values exceed column or prefix length on non-binary collations (e.g. utf8mb4_0900_ai_ci). A single truncated value in IN() can invalidate all valid ranges, forcing a full scan and degrading performance.

Reported Affected Version/s: 8.4.x
Upstream Bug: Bug #118009
Workaround/Fix: No workaround available
Fixed/Planned Version/s: Not fixed yet

PS-10593: The audit_log plugin can crash (segfault) during memcpy operations when configured with audit_log_strategy=PERFORMANCE, audit_log_policy=ALL, and buffering enabled. The issue can be reproduced under specific memory allocator setups (e.g., jemalloc) and also occurs with standard libc malloc, indicating instability in the plugin’s memory handling.

Reported Affected Version/s: 8.0.34-26, 8.0.45-36, 8.4.7-7
Upstream Bug: Not applicable
Workaround/Fix: No workaround available
Fixed/Planned Version/s: 8.0.46-37, 8.4.9-9

PS-10990: Server crashes (signal 11) in Item_cache::walk when executing queries that use JOIN with a subquery in an IN clause inside stored procedures. The issue occurs during query execution/privilege checking and is reproducible across MySQL and Percona Server 8.0.x versions.

Reported Affected Version/s: 8.0.45-36
Upstream Bug: Bug #115885
Workaround/Fix: Execute the query outside the stored procedure
Fixed/Planned Version/s: Not specified

PS-10578: The legacy audit_log plugin does not populate the DB field in audit records unless the session is started with the –database option. Even when a database is selected later using USE or referenced explicitly in queries, the DB field may remain empty.

Reported Affected Version/s: 8.0.43-34, 8.0.45-36
Upstream Bug: Not applicable
Workaround/Fix: Use Audit Log Filter component (8.4) or audit log filter (8.0), where this issue is not reproducible
Fixed/Planned Version/s: Not planned to be fixed

Percona Xtradb Cluster

PXC-4844: In PXC clusters under high load, inconsistency voting during DDL or DCL operations can trigger an internal deadlock, causing standby nodes to get stuck applying transactions and continuously request FC pause. Although voting completes successfully and no node is expelled, writes remain blocked in wsrep: replicating and certifying write set, effectively stalling the cluster until the affected node is restarted.

Reported Affected Version/s: 8.0.42
Upstream Bug: Not applicable
Workaround/Fix: Restart the blocked standby node to restore cluster activity
Fixed/Planned Version/s: Not fixed yet

PXC-4799: In PXC clusters, when a backup lock (LOCK INSTANCE FOR BACKUP) is active and a replicated DDL is pending, executing FLUSH TABLES WITH READ LOCK on the same node can trigger a deadlock. This results in an inconsistency vote and causes the node to leave the cluster, disrupting backup operations.

Reported Affected Version/s: 8.0.42, 8.0.43, 8.4.6
Upstream Bug: Not applicable
Workaround/Fix: Avoid running DDL operations during backup or use a single backup instance instead of parallel runs
Fixed/Planned Version/s: 8.0.46, 8.4.9, 9.7.0

PXC-4814: In PXC with wsrep_OSU_method=‘RSU’, a failed DDL due to table name case mismatch (e.g., OPTIMIZE TABLE) is incorrectly written to the binary log as a successful transaction (error_code=0). This results in a GTID being generated for a failed operation, causing GTID inconsistencies across cluster nodes and in replication setups.

Reported Affected Version/s: 8.0.33-25, 8.0.44, 8.4.6
Upstream Bug: Not applicable
Workaround/Fix: Validate table name case sensitivity before executing DDL in RSU mode
Fixed/Planned Version/s: 8.0.45, 8.4.8, 9.6.0

PXC-4845: After an IST failure (e.g., due to network issues), a PXC node may remain running in an inconsistent state instead of restarting, causing the donor and other nodes to become unresponsive. The joiner node gets stuck during state transfer instead of failing cleanly, impacting overall cluster availability.

Reported Affected Version/s: 8.0.42
Upstream Bug: Not applicable
Workaround/Fix: No workaround available
Fixed/Planned Version/s: 8.0.45, 8.4.8, 9.6.0

PXC-4849: A PXC node fails to start after successful SST when read_only or super_read_only is enabled and event scheduler objects exist on the donor. During initialization, the event scheduler fails to load, causing the node to abort, making it impossible to run read-only nodes with events defined in the cluster.

Reported Affected Version/s: 8.0.44, 8.4.7
Upstream Bug: Not applicable
Workaround/Fix: Start the node without read_only, then enable it manually later, or remove events
Fixed/Planned Version/s: 8.0.46, 8.4.9, 9.7.0

PXC-4965: Passwords containing the ' character are incorrectly handled, causing syntax errors during replication (e.g., SET PASSWORD) and triggering inconsistency voting that can force a node to leave the cluster.

Reported Affected Version/s: 8.0.45, 8.4.7
Upstream Bug: Not applicable
Workaround/Fix: Avoid using ' character in passwords
Fixed/Planned Version/s: 8.0.46, 8.4.8, 9.6.0

PXC-5198: Executing SELECT … FOR UPDATE SKIP LOCKED can trigger InnoDB crashes with fatal errors (e.g., “Unknown error code 21: Skip locked records”) under concurrent transactional workloads. Instead of returning expected deadlock errors, the query causes mysqld to abort, impacting cluster stability.

Reported Affected Version/s: 8.0.33-25, 8.0.35-27, 8.0.36-28
Upstream Bug: Not applicable
Workaround/Fix: Avoid using SKIP LOCKED in SELECT … FOR UPDATE queries
Fixed/Planned Version/s: 8.0.46, 8.4.8, 9.6.0

Percona XtraBackup

PXB-3543: Incremental backups in XtraBackup can become significantly slower than full backups on instances with a very large number of small tables, due to excessive CPU usage in memset during incremental processing. This leads to severe performance degradation, with incremental backups taking hours compared to minutes for full backups.

Reported Affected Version/s: 8.0.35-33, 8.0.35-34
Upstream Bug: Not applicable
Workaround/Fix: Use full backups instead of incremental backups
Fixed/Planned Version/s: 8.0.35-35, 8.4.0-6, 9.6.0-1

PXB-3667: Installation of XtraBackup 8.4 fails on RHEL 9–based systems due to dependency conflicts between percona-xtrabackup-84, perl(DBD::mysql), and incompatible libmysqlclient versions. Percona Server 8.4 provides libmysqlclient.so.24, while required dependencies expect libmysqlclient.so.21, resulting in unresolved package installation errors.

Reported Affected Version/s: 8.4.0-5
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: Not specified

Percona Toolkit

PT-2519: pt-query-digest fails when processing large, slow query logs, repeatedly throwing “Argument “” isn’t numeric” errors during the aggregate fingerprint stage. The tool retries multiple times but does not complete, resulting in stalled analysis and very slow progress.

Reported Affected Version/s: 3.7.0, 3.7.1
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: 3.7.3

PT-2511: pt-summary incorrectly reports that sshd is not running due to an invalid awk expression used to detect the process. The script checks the wrong field in ps output, causing false negatives even when sshd is active.

Reported Affected Version/s: 3.7.1
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: 3.7.3

PT-2516: pt-mongodb-index-check fails to detect duplicate indexes (e.g., {a:1} and {a:1, b:1}) and may produce no output, making it unclear whether the tool is functioning or connecting properly.

Reported Affected Version/s: 3.7.1
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: Not specified

PMM [Percona Monitoring and Management]

PMM-14493: PMM fails to start when using Podman with the –log-driver passthrough option due to an error opening /dev/stderr during Nginx initialization. This causes the container to exit with configuration test failure, while other log drivers work as expected.

Reported Affected Version/s: 3.4.0, 3.4.1
Upstream Bug: Not applicable
Workaround/Fix: Use a different –log-driver option such as none or journald
Fixed/Planned Version/s: 3.8.0

PMM-14576: PMM Client reports “failed to get backup status” errors during MongoDB backups, marking them as failed in the UI even though backups are successfully completed by PBM. This leads to incorrect backup status reporting and confusion for users.

Reported Affected Version/s: 3.5.0
Upstream Bug: Not applicable
Workaround/Fix: Avoid using PMM Backup Management (not ideal)
Fixed/Planned Version/s: 3.9.0, 3.X

PMM-14594: PMM incorrectly reports compatible XtraBackup versions as incompatible with supported MySQL versions during backup validation. This causes backups to be blocked in PMM even when the installed XtraBackup version is the latest available and should be accepted.

Reported Affected Version/s: 3.5.0, 3.6.0
Upstream Bug: Not applicable
Workaround/Fix: Use the xtrabackup command-line tool to take backups
Fixed/Planned Version/s: 3.9.0

PMM-14852: Some panels in the MongoDB InMemory dashboard show no data because they incorrectly use WiredTiger-specific metrics. As a result, dashboards for InMemory storage engine deployments can display empty or misleading panels instead of relevant metrics.

Reported Affected Version/s: 3.2.0, 3.6.0
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: 3.8.0

PMM-14906: The postgres_exporter generates excessive SELECT version() queries (~4500/hour) after upgrading to PMM 3.6.0, flooding PostgreSQL logs and increasing unnecessary query load, causing log spam and disk growth.

Reported Affected Version/s: 3.6.0
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: 3.8.0

PMM-14958: mysqld_exporter continues to generate duplicate metric collection errors with GTID and parallel replication enabled, even in PMM 3.6.0. These repeated errors (e.g., mysql_perf_schema_replication_group_worker_transport_time_seconds) lead to continuous log spam, causing rapid log growth (up to ~10GB/hour), disk space exhaustion, and increased noise that makes it difficult to identify real issues.

Reported Affected Version/s: 3.6.0
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: 3.7.1

Percona Kubernetes Operator

K8SPG-737: In PostgreSQL Kubernetes deployments, the node_exporter in the PMM client sidecar cannot access the datadir mountpoint because it is not exposed via /proc, preventing collection of datadir-related metrics. This results in incomplete monitoring data for PostgreSQL pods.

Reported Affected Version/s: 2.9.0
Upstream Bug: Not applicable
Workaround/Fix: No workaround available
Fixed/Planned Version/s: 2.10.0

K8SPXC-1737: The PXC Operator crashes during reconciliation in CompareMySQLVersion when the cluster status lacks a MySQL version value. An empty version field causes a panic (“Malformed version”), preventing proper cluster reconciliation and replication setup.

Reported Affected Version/s: 1.18.0, 1.19.0
Upstream Bug: Not applicable
Workaround/Fix: Create the cluster before configuring replication or manually patch the CR status to include the missing version value, for example:

kubectl patch pxc  
 --type=merge 
 --subresource=status 
 --patch '
status:
 pxc:
 version: "8.0.42-33.1"'

Fixed/Planned Version/s: 1.20.0

K8SPXC-1843: Backups can get stuck in a Running state if the Joiner/Garbd disconnects from the Donor (e.g., due to sst-idle-timeout). Even after the SST process fails and the donor leaves the cluster, the backup process (e.g., xbcloud put) continues indefinitely without timing out, preventing backup completion.

Reported Affected Version/s: 1.19.0
Upstream Bug: Not applicable
Workaround/Fix: No workaround available
Fixed/Planned Version/s: 1.20.0

K8SPXC-1831: When using mysqlAllocator=jemalloc on ARM images, the operator attempts to preload /usr/lib64/libjemalloc.so.1, but only libjemalloc.so.2 is available. This results in preload errors and prevents proper use of the jemalloc allocator.

Reported Affected Version/s: 1.19.0
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: 1.20.0

K8SPXC-1830: ProxySQL monitoring fails in PMM when using caching_sha2_password, causing proxysql_exporter to fail authentication with errors like:

Error opening connection to ProxySQL:
unexpected resp from server for caching_sha2_password, perform full authentication

This occurs because ProxySQL does not support the required RSA-based full authentication, breaking PMM monitoring integration.
Reported Affected Version/s: 1.19.0
Upstream Bug: Not applicable
Workaround/Fix: Use mysql_native_password
Fixed/Planned Version/s: 1.20.0

K8SPSMDB-1617: Scheduled backups can be triggered even when the MongoDB cluster is not ready (e.g., in initializing state) and without the required safety flags. This leads to failed backup attempts and inconsistent backup behaviour.

Reported Affected Version/s: 1.22.0
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: Not specified

K8SPSMDB-1524: The PBM agent continuously triggers resync storage operations, causing backup processes to stall or remain in pending/unknown states. Logs show repeated resync commands being executed without completion, leading to unstable backup behaviour.

Reported Affected Version/s: 1.21.1
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: 1.22.0

K8SPG-939: Patroni does not propagate labels defined in the PostgreSQL Operator CR, causing failures in environments with strict label policies. As a result, Kubernetes rejects resource creation (e.g., Services) due to missing mandatory labels, preventing cluster reconciliation.

Reported Affected Version/s: 2.8.2
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: 2.9.0

PBM [Percona Backup for MongoDB]

PBM-1683: The size_uncompressed_h field in pbm describe-backup reports incorrect (inflated) sizes for non-base incremental backups, showing significantly larger values than the actual data size and leading to misleading backup size reporting.

Reported Affected Version/s: 2.10.0, 2.11.0, 2.12.0
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: 2.14.0

PSMDB [Percona Server for MongoDB]

PSMDB-1915: Newer PSMDB packages fail to install or upgrade on RHEL 9.4 due to a dependency on OpenSSL 3.4, which is not available in that OS version. This breaks upgrades (e.g., from 6.0.25 to 6.0.27) and affects multiple major versions.

Reported Affected Version/s: 6.0.27-21, 7.0.28-15, 8.0.17-6
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: 6.0.27-21, 7.0.28-15, 8.0.17-6

PSMDB-1998: LDAP authentication can hang indefinitely when the LDAP server is unreachable due to missing timeout handling. This leads to continuously accumulating connections, eventually exhausting file descriptors and causing service disruption or crashes.

Reported Affected Version/s: 7.0.16-10, 7.0.30-16
Upstream Bug: Not applicable
Workaround/Fix: No workaround available
Fixed/Planned Version/s: 7.0.31-17, 8.0.20-8

Percona Distribution for MySQL [Orchestrator]

DISTMYSQL-584: Orchestrator loses SSL-related settings such as SOURCE_SSL_CA and SOURCE_SSL_VERIFY_SERVER_CERT during failover when issuing CHANGE REPLICATION SOURCE, causing replication to run without required security configurations and potentially violating compliance requirements.

Reported Affected Version/s: 8.4.7
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: Not specified

PCSM [Percona ClusterSync for MongoDB]

PCSM-294: PCSM replication can crash during change replication due to flawed conflict detection and unbatched pipeline generation. This results in oversized aggregation pipelines, memory exhaustion, or invalid $slice operations, causing replication to fail with errors such as stage limit exceeded, buffer limits, or invalid arguments.

Reported Affected Version/s: 0.7.0
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: 0.8.0

PG_TDE [Percona Transparent Data Encryption for PostgreSQL]

PG-2125: pg_tde fails to create/register symmetric keys when using HashiCorp KMIP, returning errors from the KMIP server during key registration. This prevents key setup and blocks encryption workflows for users relying on KMIP integration.

Reported Affected Version/s: pg_tde 2.1.0
Upstream Bug: Not applicable
Workaround/Fix: Not specified
Fixed/Planned Version/s: pg_tde NEXT

Summary

We welcome community input and feedback on all our products. If you find a bug or would like to suggest an improvement or a feature, learn how in our post, How to Report Bugs, Improvements, New Feature Requests for Percona Products.

For the most up-to-date information, be sure to follow us on Twitter, LinkedIn, and Facebook.

Quick References:

Percona JIRA

MySQL Bug Report

Report a Bug in a Percona Product

The post Percona Bug Report: March 2026 appeared first on MariaDB.org.

Source

The post We ran an internal AI demo competition: Here are the winners! appeared first on MariaDB.org.

Continue reading “MariaDB Vector: How it works. Part III”

The post MariaDB Vector: How it works. Part III appeared first on MariaDB.org.

The post MariaDB Vector: How it works. Part III appeared first on MariaDB.org.

Many MySQL setups begin life with a familiar incantation:

innodb_buffer_pool_size = 70% of RAM

…and then nothing changes.

That’s not tuning. That’s a starting guess.

Real tuning starts when the workload pushes back.

Visual Overview

The InnoDB buffer pool is where database performance is quietly decided. It determines whether your workload hums along in memory or drags itself across disk. If you’re not actively observing and tuning it, you’re leaving performance on the table.

This guide walks through how to monitor, understand, and tune the buffer pool using real signals instead of guesswork.

What the Buffer Pool Really Is

The buffer pool isn’t just “memory for MySQL.” It’s a living system under constant pressure:

• A cache of data and indexes
• A write staging area (dirty pages)
• A contention zone between reads, writes, and eviction

Think of it as your database’s working memory. If your working set fits, queries glide. If it doesn’t, pages are constantly evicted and reloaded, introducing latency that rarely announces itself clearly.

A Simple Mental Model

 +---------------------------+
 | Buffer Pool |
 |---------------------------|
Reads ---> | Cached Pages |
 | |
Writes ---> | Dirty Pages (pending IO) |
 | |
Eviction -> | LRU / Free List |
 +---------------------------+
 |
 v
 Disk (slow)

Three forces are always competing:

• Reads want hot data in memory
• Writes generate dirty pages
• Eviction makes room under pressure

Your job is to keep this system balanced.

How to Monitor the Buffer Pool

Option 1: Quick Snapshot

SHOW ENGINE INNODB STATUSG

Useful for human inspection. Look for:

• Buffer pool size
• Free buffers
• Database pages
• Modified (dirty) pages
• Page read/write rates

Great for debugging. Not ideal for automation.

Option 2: Structured Metrics (Recommended)

SELECT
 pool_id,
 free_buffers,
 database_pages,
 modified_database_pages
FROM information_schema.INNODB_BUFFER_POOL_STATS;

Key fields:

• free_buffers → Available pages (breathing room)
• database_pages → Pages holding data
• modified_database_pages → Dirty pages waiting to flush

Great for automation.

The 5 Signals That Actually Matter

1. Buffer Pool Hit Ratio (Handle With Care)

Yes, it’s widely used. No, it’s not enough.

A high hit ratio does not mean your system is healthy. It does not capture:

• Page churn
• Eviction pressure
• Access patterns

You can have a 99% hit ratio and still be IO-bound.

Use it as a sanity check, not a decision-maker.

2. Free Buffers

SELECT SUM(free_buffers) AS free_buffers
FROM information_schema.INNODB_BUFFER_POOL_STATS;

Interpretation:

• Near zero during steady load → normal
• Near zero + rising disk reads → pressure
• Near zero while mostly idle → suspicious (possible misread or config issue)

3. Dirty Page Percentage

SELECT
 (SUM(modified_database_pages) / SUM(database_pages)) * 100.0 AS dirty_pct
FROM information_schema.INNODB_BUFFER_POOL_STATS;

Interpretation (context matters):

• 0–5% → Very clean
• 5–20% → Typical
• 20–30%+ → Potential flushing lag

4. Disk Read Pressure (Critical Signal)

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
-- Take two samples 60s apart and compare

Track the rate of change (reads/sec), not the absolute value.

Interpretation:

• Rising reads → Working set does not fit in memory
• Flat reads → Memory is absorbing the workload

5. Read Ahead / Eviction Pressure

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_ahead%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_evicted';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

Interpretation:

• Efficient read-ahead:
  • read_ahead increases
  • read_ahead_evicted remains low
• Inefficient read-ahead (wasted IO):
  • High read_ahead_evicted / read_ahead
  • Indicates access patterns defeating prefetching
• Buffer pool churn:
  • pages_evicted rising
  • buffer_pool_reads rising
  • Indicates pages are evicted and re-read from disk
• Healthy vs unhealthy eviction:
  • High evictions + stable reads → normal turnover
  • High evictions + rising reads → memory pressure

Focus on rates of change over time, not absolute values.

Detecting Thrashing

Thrashing is when the buffer pool constantly evicts and reloads pages.

Classic Symptoms

• Low or zero free buffers
• Increasing disk reads
• Stable (but misleading) hit ratio
• Spiky query latency

Visualizing Thrash

Time --->

Memory: [FULL][FULL][FULL][FULL]
Reads: ↑ ↑↑ ↑↑↑ ↑↑↑↑
Latency: - ^ ^^ ^^^
Evictions: ↑ ↑↑ ↑↑↑ ↑↑↑↑

If you see this pattern, your working set does not fit in memory.

Tuning the Buffer Pool

Step 1: Size It Intentionally

Instead of blindly assigning 70% of RAM:

• Observe working set behavior
• Monitor free buffers and reads
• Increase gradually

Avoid starving the OS or filesystem cache.

Step 2: Tune Flushing Behavior

innodb_max_dirty_pages_pct = 75
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000

• Sustained IO spikes → increase innodb_io_capacity
• Dirty pages climbing → flushing lag
• Sudden stalls → checkpoint pressure

What they control:

• innodb_io_capacity → Expected steady-state IO throughput
• innodb_io_capacity_max → Burst flushing capacity
• innodb_max_dirty_pages_pct → Threshold for aggressive flushing

⚠️ These values should reflect real hardware capability.

Step 3: Buffer Pool Instances:Reduce Contention

A practical, battle-tested guideline:

Use 1 instance per ~1GB of buffer pool, up to a reasonable limit.

Buffer Pool Instances: Reducing Contention

The buffer pool can be split into multiple instances, each managing its own internal structures. This helps reduce contention under high concurrency.

Without this, all threads compete for the same buffer pool internals. With multiple instances, that load is distributed.

When It Matters

Buffer pool instances only help when contention exists. You’ll see benefits if your system has:

• High concurrency (many active threads)
• CPU-bound workloads
• Mutex contention in InnoDB

If your workload is primarily IO-bound, this setting will have little impact.

Sizing Guidelines

General guidance:

• < 1GB buffer pool → 1 instance
• 1GB–8GB → 2–4 instances
• 8GB–64GB → 4–8 instances
• 64GB+ → 8–16 instances

Keep Instances Large Enough

Each instance needs enough memory to function efficiently.

Avoid going below ~1GB per instance.

If instances are too small:

• LRU efficiency drops
• Eviction becomes more aggressive
• Cache locality suffers

Example

innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 8

This gives ~4GB per instance, which is well-balanced.

Common Mistakes

• Increasing instances without evidence of contention
• Matching instance count to CPU cores
• Using many instances with a small buffer pool
• Expecting this to fix IO bottlenecks

Step 4: Understand Resizing Behavior

Buffer pool resizing is online in modern MySQL versions, but:

• It happens in chunks
• Controlled by innodb_buffer_pool_chunk_size

Real-World Scenarios

Scenario 1: “Everything Looks Fine… But It’s Slow”

• High hit ratio
• Low free buffers
• Rising disk reads

Cause: Working set barely fits

Fix: Increase buffer pool size gradually

If increasing the buffer pool size does not reduce disk reads, the problem is not memory.

Scenario 2: Write-Heavy Workload

• Dirty pages increasing
• Periodic IO spikes

Cause: Flushing cannot keep up

Fix:

• Increase innodb_io_capacity
• Adjust dirty page thresholds

Scenario 3: Sudden Latency Spikes

• Sharp performance drops
• Disk activity surges

Cause: Checkpoint pressure

Fix:

• Improve IO capacity tuning
• Reduce dirty page buildup

Practical Monitoring Queries

Buffer Pool Usage (MB)

SELECT
 (SUM(database_pages) * 16) / 1024 AS mb_used
FROM information_schema.INNODB_BUFFER_POOL_STATS;

Assumes default 16KB page size (innodb_page_size).

Dirty Page Percentage

SELECT
 (modified_database_pages / database_pages) * 100 AS dirty_pct
FROM information_schema.INNODB_BUFFER_POOL_STATS;

Free Buffer Check

SELECT SUM(free_buffers) AS free_buffers
FROM information_schema.INNODB_BUFFER_POOL_STATS;

Common Mistakes

• Treating 70% as a rule instead of a starting point
• Blindly trusting hit ratio
• Ignoring disk read trends
• Oversizing and starving the OS
• Not tuning IO capacity
• Leaving defaults in write-heavy systems

Quick Checklist

If you remember nothing else:

• Reads increasing? → working set too big
• Free buffers always ~0? → pressure
• Dirty pages high? → flushing lag
• Latency spiking? → checkpoint or IO saturation

Final Thoughts

The InnoDB buffer pool doesn’t fail loudly. It degrades quietly until your disk becomes the bottleneck.

By the time you notice, you’re debugging latency instead of preventing it.

Monitor the right signals, and you’ll see problems forming before users do.

That’s the difference between reacting to performance… and controlling it.

The post InnoDB Buffer Pool Tuning: From Rule-of-Thumb to Real Signals appeared first on MariaDB.org.

Source

The post MariaDB Java Connector 3.5.8 now available appeared first on MariaDB.org.

The post Benchmarking MyRocks vs. InnoDB in Memory-Constrained Environments appeared first on MariaDB.org.

Source

The post What’s New in MariaDB AI RAG 1.1: Ingestion, Reranking, and Docker Deployment appeared first on MariaDB.org.

Continue reading “Know a MariaDB champion? Submit a nomination”

The post Know a MariaDB champion? Submit a nomination appeared first on MariaDB.org.

The post Know a MariaDB champion? Submit a nomination appeared first on MariaDB.org.

The post Contributions As a Cost-saver appeared first on MariaDB.org.

The post Contributions As a Cost-saver appeared first on MariaDB.org.

The post 2026 – MySQL Ecosystem Performance Benchmark Report appeared first on MariaDB.org.

Continue reading “MariaDB observability – results from the poll: the community has clearly chosen its default stack”

The post MariaDB observability – results from the poll: the community has clearly chosen its default stack appeared first on MariaDB.org.

The post MariaDB observability – results from the poll: the community has clearly chosen its default stack appeared first on MariaDB.org.

Continue reading “MariaDB Vector: How it works. Part II”

The post MariaDB Vector: How it works. Part II appeared first on MariaDB.org.

The post MariaDB Vector: How it works. Part II appeared first on MariaDB.org.

Continue reading “Datography Joins MariaDB Foundation as Silver Sponsor”

The post Datography Joins MariaDB Foundation as Silver Sponsor appeared first on MariaDB.org.

The post Datography Joins MariaDB Foundation as Silver Sponsor appeared first on MariaDB.org.

Continue reading “MariaDB Keeps Climbing: Community, Adoption, and Momentum”

The post MariaDB Keeps Climbing: Community, Adoption, and Momentum appeared first on MariaDB.org.

The post MariaDB Keeps Climbing: Community, Adoption, and Momentum appeared first on MariaDB.org.

Source

The post The Agentic Era: Why Infrastructure is the New Innovation Frontier appeared first on MariaDB.org.

Continue reading “MariaDB 13.0 Preview Now Available”

The post MariaDB 13.0 Preview Now Available appeared first on MariaDB.org.

The post MariaDB 13.0 Preview Now Available appeared first on MariaDB.org.

Continue reading “DBaasNow Joins MariaDB Foundation as Silver Sponsor”

The post DBaasNow Joins MariaDB Foundation as Silver Sponsor appeared first on MariaDB.org.

The post DBaasNow Joins MariaDB Foundation as Silver Sponsor appeared first on MariaDB.org.

Source

The post A Guide to Multi-Region Disaster Recovery with MariaDB Cloud appeared first on MariaDB.org.

Source

The post MariaDB Python Connector 2.0.0rc2 appeared first on MariaDB.org.

The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.

In the inconceivable case that you find a bug in the MyEnv please report it to us by sending an email.

Any feedback, statements and testimonials are welcome as well! Please send them to us.

Upgrade from 2.x to 3.0

Please check the MyEnv Installation Guide.

Changes in MyEnv 3.0.0

MyEnv

• Template warning improved.
• Distro version in --version added.
• Check MyEnv configuration permissions.
• #fd increased for MyEnv.
• myenv.conf should have more secure permissions now.
• Situation caught when my.cnf is missing in myenv.conf.
• Directories home and run moved to dba and myenv.
• Unit file mariadb.service and mysql.service replaced by dba.service.
• User mysql replaced by dba in template.
• start_stop fixed warning in case argv[1] is missing.
• sys_uid filter fixed for Rocky Linux.
• dba unit file added to package.
• Nagios plugins detection removed from showMyEnvVersion.
• Old SysV init files removed and replaced by Systemd unit files.
• dba user was introduced and check for system user added.
• User dba changed an cosmetic fixes.

MyEnv Installer

• 2 concurrent installMyEnv versions cannot run any more.
• Directroy binlog, cgroups and angel removed from postgresql type installation.
• Wrapper script installMyEnv.sh removed.
• Cosmetics fixed in installer.
• Installation made more mysql friendly.
• libaio1t64 considered during installation recommendations on DEB systems.
• Next free port suggestion during installMyEnv improved. It will suggest the first free port now.
• apt-get and yum replaced by apt and dnf.

MyEnv Utilities

• insert_test.sh made PostgreSQL ready.

PostgreSQL

• PostgreSQL instance is stopped with fast instead of immediate now.
• show_create_table.sh for PostgreSQL made nicer.
• PostgreSQL status.sql added.
• Minor fixes for PostgreSQL.

General

• CHANGELOG updated.
• rc made unique.
• Minor bugs fixed.
• Copyright year updated from 2024 to 2026.
• mkdir changed from /bin to /usr/bin which is the new/right standard on all our 3 supported distributions.

Documentation

• README updated.
• Installation documentation improved, library related stuff documented.
• PostgreSQL added to documentation.
• lsb_release removed from documentation.
• Documentation restructured.
• Documentation made more resilient agaist errors.
• Documentation process improved.
• Documentation moved completely to asciidoc.

Packaging

• Package list completed.
• Makefile fixed.
• Old distro stuff and initV stuff removed.
• Build scripts fixed.

For subscriptions of commercial use of MyEnv please get in contact with us.

The post MariaDB/MySQL Environment MyEnv 3.0.0 has been released appeared first on MariaDB.org.

Continue reading “Queen Shrugged”

The post Queen Shrugged appeared first on MariaDB.org.

The post Queen Shrugged appeared first on MariaDB.org.

PostgreSQL 18 directly addresses these real-world operational challenges, skipping flashy features for practical improvements like asynchronous I/O to speed up reads, safer upgrades from keeping optimizer stats, better multi-column indexes, UUIDv7 support, and useful enhancements to logical replication.

I’ll dive into these key operational changes, show how they fit your modern workflows, and explain how tools like ClusterControl can make adopting them smooth and painless.

PostgreSQL 18’s key features that improve core workload performance

PostgreSQL 18 isn’t about one big, new thing. Instead, it offers fixes for common headaches: slow storage, reads competing with new transactions, inconsistent performance post-upgrade, indexes that don’t quite hit the mark, messy authentication, and confusing replication errors — these fixes smooth out the rough operational edges. Let’s look at two key features that will have the greatest effect on production work.

Async I/O subsystem (AIO)

Slow storage often causes problems, not the computer itself. Older PostgreSQL waited for each storage request, which is safe but can slow things down, especially when your data is too big for the cache or when running big scans alongside regular transactions.

PostgreSQL 18 fixes this with Asynchronous I/O (AIO). Now, the system can ask for multiple data blocks and keep working instead of waiting for each one. This can significantly improve read-heavy scans and some maintenance operations under the right I/O conditions. New settings like io_method help you tune this — don’t just test AIO with a single query.

Its real power shows up under heavy, simultaneous load. To see the benefit, test your actual, read-heavy workload, focusing on P95/P99 latency improvements. Pay special attention to maintenance, since PG 18 specifically aims to reduce I/O slowdown during VACUUM.

TIP: A good test is to compare latency under pressure with and without AIO while background maintenance is running.

Skip-scan on B-tree indexes

In large systems, especially SaaS, we often use multi-column indexes. The problem is that queries don’t always filter by the index’s first column. For example, an index on (tenant_id, created_at) won’t help a query just filtering by created_at. This usually means creating extra, redundant indexes — PostgreSQL 18 adds skip-scan for multi-column B-tree indexes.

When the planner estimates it’s cheaper than scanning the table, it can iterate over the leading column’s distinct values and reuse the same index to satisfy predicates on the later columns, even if the leading column isn’t constrained. This is a big win because it means fewer extra indexes, making things cleaner, helping performance by reducing write overhead, and keeping VACUUM happy as your data inevitably scales.

How Postgres 18 features practically support modern workload patterns

PostgreSQL 18’s new stuff isn’t just random. It’s built around how people are actually using Postgres right now. Most setups mix transactions, reporting, search, and more AI stuff, with evolving replication.

Looking at what users are doing makes the improvements way easier to understand how they benefit day-to-day operations than just seeing a list of new features.

AI & vector search

When people use AI with PostgreSQL, they usually don’t replace the database with a specialized vector engine. Instead, they keep PostgreSQL as the main system for transactional data and put AI-related data, like embeddings, right alongside it.

When running vector search and transactional workloads together, it’s best to keep those data embeddings right next to the data they describe. This means you need reliable performance: steady write speeds, predictable read times even when searches spike, and enough replicas to scale reads without bogging down the main database.

Query complexity grows, mixing things like finding similar items with standard filters on who, when, permissions, or categories. This often leads to heavy database scans, unpredictable read bursts, and competition between search / reporting tasks and regular writes — PostgreSQL 18’s features help smooth all of this out.

Asynchronous I/O helps with storage slowdowns during heavy reads, and skip-scan makes filtering around your similarity searches much faster by improving multicolumn indexes. You still need a smart strategy for your AI indexes, e.g. when to use HNSW, how to organize data, etc., but PG helps the whole system handle the pressure better. Adding ClusterControl to the mix creates a winning combination, as managing replicas, backups, and monitoring performance as you grow becomes much easier.

Serverless ingestion + BI

Many teams want applications to feel serverless and handle real-time data analysis, even when self-managing PostgreSQL or in a hybrid setup. Raw speed isn’t the priority; it’s how the system handles sudden spikes and recovers quickly, posing two hurdles:

First, when lots of people use it at once, things can get slow. Second, we have less and less time for maintenance and upgrades, and we need stability right away after an update.

PostgreSQL 18 fixes both. Better I/O helps with slow reads during busy times, and keeping performance stats after an upgrade means less post-update drama. Basically, PostgreSQL is becoming stronger for unpredictable loads, and when you can’t afford any downtime. ClusterControl makes upgrades and backups consistent across all your setups; that’s what makes the difference between a normal maintenance window and a full-blown incident.

Isolated replication for multi-tenant workloads

Multi-tenant systems often use replication to handle more reads, separate workloads, or serve different regions. This usually means having read replicas for regions, consumers for analytics or search, and moving tenants around as the system grows.

PostgreSQL 18 improves replication by better supporting generated columns and making conflicts much easier to see. This makes tenant-specific replication safer to run and easier to fix when problems occur. ClusterControl helps by setting up and monitoring these setups consistently, preventing hard-to-maintain, one-off replication configurations.

The result is fewer unexpected issues during replication, clearer insight when conflicts happen, and more confidence when changing how your replication is set up.

Postgres 18 features that reduce upgrade & migration workflow risks

Upgrading PostgreSQL is mostly about managing risk, not the specific steps. What teams truly care about is getting the system back to normal, predictable performance fast.

Tight maintenance windows, huge databases, and low tolerance for issues mean the period after the upgrade can be brutal. Slow query plans, unexpected slowdowns, or emergency tuning can quickly turn a successful upgrade into an on-call nightmare. Here are the design changes and features PostgreSQL 18 implements to tackle these specific problems.

Faster upgrades with retained statistics

Historically, PostgreSQL upgrades caused frustrating performance degradation because the query planner had to relearn all data statistics. PG 18’s pg_upgrade utility now transfers most optimizer statistics. This feature dramatically stabilizes performance much faster post-upgrade by immediately providing current data knowledge to the planner, eliminating the stressful, lengthy process of relearning statistics, especially for large databases.

Checksums enabled by default in initdb

PostgreSQL 18 changes a big default: new clusters now turn on data checksums automatically when you run initdb. Checksums are great for catching sneaky data corruption, though they use a tiny bit more CPU. Most teams already use them for better durability or compliance — you can still opt out with --no-data-checksums.

However, checksum settings must match exactly when you upgrade. If your old cluster didn’t have checksums, you can’t magically turn them on during the upgrade.

Think of the checksum setting as a contract for your cluster. Document it, keep it consistent everywhere, and test it during your upgrade dry runs. Don’t leave it as a last-minute decision, or you’ll find problems during the final cutover instead of in testing.

PG 18 enhancements that improve developer & SQL quality of life

Even though some PostgreSQL features seem like they are just for developers, they often impact how things run behind the scenes. Schema and SQL choices can unexpectedly influence storage, how much data is written, replication size, and index performance over time. Postgres 18 brings changes in this area that operators should really pay attention to.

Virtual generated columns (default)

PostgreSQL 18’s generated columns are usually virtual, meaning the value is calculated when you read the row, not saved on disk, unless you choose to store it.

Operationally, this is key. Virtual columns cut down on writes and storage, which is great for busy tables. But if you read the derived value a lot or need to index it predictably, stored columns might be better, as the calculation is done once on write, not on read; for example,

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL,
  amount_cents bigint GENERATED ALWAYS AS ((amount * 100)::bigint)
);

In PostgreSQL 18, amount_cents is virtual by default. If you want the value precomputed and stored, because it’s heavily queried or indexed, you can still do so explicitly:

amount_cents bigint GENERATED ALWAYS AS ((amount * 100)::bigint) STORED

N.B. This decision is crucial for replication. PG 18’s logical replication is better at publishing stored generated values.

UUIDv7 for time-ordered IDs

UUIDs are great because they’re unique everywhere and easy to make across different systems. The problem has been how they mess up B-tree indexes. Random UUIDs scatter new entries, causing slow index bloat and cache issues, especially on busy tables.

Recent PostgreSQL releases fixed this natively with uuidv7(), making it the operational standard for PostgreSQL 18 architectures. It generates UUIDs that are mostly time-ordered. This keeps your indexes much tidier while keeping the benefits of using UUIDs. For example:

CREATE TABLE sessions (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  created_at timestamptz NOT NULL DEFAULT now()
);

If you’ve been hesitant to use UUID primary keys on high-ingest tables because of index behavior, UUIDv7 makes that trade-off far more reasonable.

Temporal constraints for time-varying facts

Dealing with time-sensitive data, like pricing or subscriptions, usually means complex application code and tricky locking to avoid mistakes. Postgres 18 simplifies this with temporal constraints.

These let the database enforce rules, like primary and foreign keys, over time ranges. This moves the headache of correctness from your application logic into the database, making enforcement instant and reliable.

For operations teams, this means fewer weird errors, less data cleanup, and fewer 2 a.m. alerts caused by subtle concurrency issues.

OAuth authentication

PostgreSQL 18 now supports OAuth, which is a big deal for security. It gives you a path to reduce long-lived DB passwords by using short-lived tokens where it fits your identity stack. It won’t fix bad internal role design, but it massively cuts down on the headache of credential sprawl, especially where infrastructure is constantly spinning up and down. OAuth is just way easier to manage than traditional passwords in those dynamic setups.

How PostgreSQL 18 improves logical & streaming replication efficiency

Replication gets complicated fast. One replica is simple, but the more lag, conflicts, and strange failures you open yourself up to. PostgreSQL 18 doesn’t magically automate logical replication or make it DDL aware. What it does instead is provide practical improvements that make operating, monitoring, and managing your replicas much easier.

Generated column replication

Building on recent improvements, modern PostgreSQL lets you publish stored generated columns using the publish_generated_columns option. This is great for downstream systems that need the calculated value right away instead of having to recompute it. PostgreSQL sends the generated value and replicates it into a normal column on the subscriber.

N.B. You cannot replicate it into another generated column; that will fail.

Basically, this feature ships the finished, calculated results, not the formula or the generated column definition. Its best use is to simplify your consumers and avoid repeating work, without getting into complicated DDL replication. Let’s look at a simple logical replication setup to illustrate how Postgres 18 handles generated columns.

On the publisher:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL,
  amount_cents bigint GENERATED ALWAYS AS ((amount * 100)::bigint) STORED
);

CREATE PUBLICATION orders_pub
FOR TABLE orders
WITH (publish_generated_columns = 'stored');

On the subscriber, the generated value is replicated into a regular column:

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  amount numeric(12,2) NOT NULL,
  amount_cents bigint NOT NULL
);

CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=<publisher_host> port=5432 dbname=<db> user=<repl_user> password=<password>'
  PUBLICATION orders_pub;

This setup reflects how PostgreSQL 18 actually handles generated column replication: you publish the stored generated value and apply it to a normal column on the subscriber. It’s a practical, explicit approach that avoids surprises and stays within the supported model.

Streaming defaults and conflict logging

Logical subscriptions now use parallel streaming by default. This means faster throughput and less lag right out of the box, especially when things are busy or transactions are large.

Conflict handling is also much better. Modern PostgreSQL logs conflicts and shows conflict details in pg_stat_subscription_stats. While not brand new to 18, utilizing this view is a massive upgrade if you are coming from older major versions. Replication conflicts are usually a nightmare because you can’t see them as they happen. Better visibility means you can spot trends, link issues to workload changes, and write reliable troubleshooting guides without relying on guesswork.

Hygiene improvements for larger estates

When you have a lot of replication going on, keeping things tidy is as crucial as keeping them fast. PostgreSQL 18 adds a few safeguards to head off slow, hidden problems:

• idle_replication_slot_timeout: Automatically invalidates idle replication slots that have been inactive for too long.
• max_active_replication_origins: Lets you limit the number of active replication origins, regardless of the number of existing slots.

If you’ve ever had an old, forgotten logical slot quietly hogging Write-Ahead Log (WAL) space for weeks, you’ll appreciate these. They don’t replace good monitoring, but they make it much harder for tiny mistakes to turn into massive cleanup projects later.

Manual vs. ClusterControl PostgreSQL 18 operations

Managing PostgreSQL by hand is fine until it isn’t. When you have just a couple of clusters, doing it yourself is easy. But once you have more than a few, those manual steps start wasting time and attention. That’s when the downsides really hit.

Manual PG 18 operations

Running PostgreSQL manually gives you total freedom, which is great for small setups. But not everything is good. Let’s see what we’re talking about.

Pros:

• You control everything about the setup.
• You can perfectly tune it for each task.
• Trying new things is easy in one environment.

Cons:

• Big upgrades are messy. Checksums, making sure plans stay stable, and planning for rollbacks are a headache.
• Replication slot cleanup is a long-term chore, especially as your setup changes.
• Backup plans often differ between clusters as you add or rebuild them.
• Monitoring is usually a bunch of tools cobbled together, leading to confusing alerts and nobody knowing who’s on point during a problem.

Trivial alone, critical cumulatively, these will chew up a ton of your team’s time as you grow.

Automated PG 18 ops with ClusterControl

ClusterControl really shines when you’re rolling PostgreSQL 18 because it saves you from having to figure out the same operational steps over and over again for every new cluster.

Pros:

• Centralized hybrid setup and management of all PostgreSQL clusters.
• Guided major version updates, ensuring you don’t miss crucial pre-checks.
• Easily and safely applied parameter changes, like AIO tuning, across the board.
• Turnkey streaming replication, including built-in HAProxy and PgBouncer support.
• Single view alerting and health checks, e.g. replication lag, node status, backups, etc.
• Backup policy enforcement using common tools, e.g. pgBackRest, pg_basebackup, etc.

Cons:

• It’s a platform, implying its own learning curve.
• You need to check that your rollout timing aligns with ClusterControl’s support for the PostgreSQL version you want to use.

For teams managing PostgreSQL at scale across many environments, this consistency is often more valuable than having total control over every single command, and easier.

Installing & setting up Postgres 18

When piloting PostgreSQL 18, set up your test environment to mimic your real deployment exactly. Use the same storage, replication setup, extensions, like pgvector, and a realistic, large enough dataset. Small, fake tests will just hide the real problems you need to find.

Installing PostgreSQL 18

The specific package names and repos differ based on your system, but here’s an example of a typical installation:

For Debian-Based OS:

sudo apt update
sudo apt install postgresql-18

For RedHat-Based OS:

sudo dnf install postgresql18-server

Checksums at initdb

Remember, PostgreSQL 18 enables checksums by default at initialization.

sudo /usr/pgsql-18/bin/postgresql-18-setup initdb

But, you can opt out:

sudo -u postgres /usr/pgsql-18/bin/initdb
--no-data-checksums -D /var/lib/pgsql/18/data

Don’t forget that pg_upgrade requires checksum settings to match between the source and target clusters. Treat checksum posture as an upgrade design decision and validate it during rehearsals, not during the cutover window.

Confirm AIO-related settings: 

Once the cluster is up, confirm the effective I/O-related settings you’re running with:

SHOW io_method;
SHOW effective_io_concurrency;
SHOW maintenance_io_concurrency;

Exact behavior depends on platform and build options, but this gives you a baseline before you start tuning or running load tests.

Adding the PG 18 cluster to ClusterControl and initializing HA / replication

Once running, the next step is to bring the cluster into ClusterControl and establish a sane baseline topology. For many teams, a solid default looks like:

• 1 primary
• 1–2 replicas
• HAProxy for routing and HA
• PgBouncer for connection pooling (especially with spiky workloads)

ClusterControl’s guided workflows can deploy PostgreSQL streaming replication and integrate HAProxy and PgBouncer as part of the setup, reducing the amount of manual wiring needed to reach a production-ready state.

PostgreSQL 18 operations & monitoring

PostgreSQL 18 offers better control and visibility. But that only matters if you use it to create reliable runbooks for when things go sideways. Don’t tweak every last setting; the real win is a predictable system under pressure, not the ability to see clearly when it isn’t.

Tune AIO safely

Asynchronous I/O (AIO) in PostgreSQL 18 is a big deal, especially for handling many tasks at once, but don’t rush it. It shines under heavy load, not in simple tests.

Start Simple:

• Pick the right io_method for your system.
• Test it with your actual, busy application, not just single queries.
• Then try adjusting io_combine_limit and io_max_combine_limit.

What to Watch For:

• How long scans take, especially big ones.
• Storage delays when the system is busy.
• Your worst-case waiting times (P95/P99), not just the average.
• How VACUUM behaves while everything else is running.

How to tell if it is working: You’ll see fewer unexpected slowdowns when reading a lot of data and less fighting between maintenance tasks and user traffic.

Vacuum/Analyze delay reporting for SLOs

PostgreSQL 18 has better insight into maintenance throttling. If you enable track_cost_delay_timing, VACUUM and ANALYZE will tell you exactly how long they waited because of cost-based delays.

This is huge for troubleshooting. Are you falling behind on maintenance because you told the system to slow down, or because it’s genuinely struggling? Knowing the difference is key when figuring out why you missed an SLO or when planning your next capacity upgrade.

Logical replication visibility

PostgreSQL 18 makes using logical replication much easier by giving you better tools to see what’s happening.

Make pg_stat_subscription_stats a regular check-in:

• See conflict counts and when they happened.
• Monitor lag and how applies are working over time.
• Check that the default parallel streaming is what you want.

While better visibility doesn’t stop replication problems, it lets you move past the guesswork so you can actually understand the issues and automate fixes.

ClusterControl dashboards

ClusterControl simplifies managing your PostgreSQL clusters by giving you a clear, consistent view of the important stuff:

• Node health and resources
• Replication status and lag
• High availability and auto-recovery
• Backup posture, state, and compliance

Ready to move? PostgreSQL 18 upgrade planning checklist

Before jumping to PostgreSQL 18, just check these basics first:

• Backups: Take a full backup and make sure you can restore it.
• Checksums: See if your current cluster uses data checksums and plan for the new one.
• Extensions: Check that all your extensions (like pgvector or PostGIS) are compatible with PostgreSQL 18.

Getting these things squared away now makes the whole upgrade process much smoother.

Conclusion

PostgreSQL 18 is all about better operations. It fixes common headaches like slow I/O (thanks to async I/O), keeps upgrades predictable by saving optimizer stats, makes multicolumn indexes smarter with skip-scan, adds modern OAuth authentication, improves index use for busy tables with UUIDv7, and simplifies logical replication.

If you’re thinking of upgrading, don’t rush. Test PostgreSQL 18 in your lower environments with real data and load first. Check your extensions, especially pgvector. Decide on checksums early, as they’re now on by default, and pg_upgrade needs them to match. And definitely test your replication setup, including conflict scenarios.

Taking the time for this careful rollout means fewer surprises and smoother changes in production. Ready to get started with PostgreSQL 18, regardless of where you run it?

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

With your install script ready, run the command below. Replace S9S_CMON_PASSWORD and S9S_ROOT_PASSWORD placeholders with your choice password, or remove the environment variables from the command to interactively set the passwords. If you have multiple network interface cards, assign one IP address for the HOST variable in the command using HOST=<ip_address>.

S9S_CMON_PASSWORD=<your_password> S9S_ROOT_PASSWORD=<your_password> HOST=<ip_address> ./install-cc # as root or sudo user

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.

Other Installation Options

Helm Chart

Deploy ClusterControl on Kubernetes using our official Helm chart.

Ansible Role

Automate installation and configuration using our Ansible playbooks.

Puppet Module

Manage your ClusterControl deployment with the Puppet module.

ClusterControl on Marketplaces

Prefer to launch ClusterControl directly from the cloud? It’s available on these platforms:

• DigitalOcean Marketplace
• gridscale.io Marketplace
• Vultr Marketplace
• Linode Marketplace
• Google Cloud Platform

The post PostgreSQL 18 Upgrades for AI-Era Workloads and Operations appeared first on Severalnines.

The post PostgreSQL 18 Upgrades for AI-Era Workloads and Operations appeared first on MariaDB.org.

Continue reading “MariaDB Vector: How it works”

The post MariaDB Vector: How it works appeared first on MariaDB.org.

The post MariaDB Vector: How it works appeared first on MariaDB.org.

Source

The post MariaDB Enterprise Server Q1 2026 Maintenance Releases appeared first on MariaDB.org.

Continue reading “MariaDB Innovation: InnoDB-Based Binary Log”

The post MariaDB Innovation: InnoDB-Based Binary Log appeared first on MariaDB.org.

The post MariaDB Innovation: InnoDB-Based Binary Log appeared first on MariaDB.org.

Continue reading “Where does the Community run most MariaDB in production – results from the poll”

The post Where does the Community run most MariaDB in production – results from the poll appeared first on MariaDB.org.

The post Where does the Community run most MariaDB in production – results from the poll appeared first on MariaDB.org.

Continue reading “Improving MariaDB Observability with OpenSearch and Grafana”

The post Improving MariaDB Observability with OpenSearch and Grafana appeared first on MariaDB.org.

The post Improving MariaDB Observability with OpenSearch and Grafana appeared first on MariaDB.org.

Source

The post What’s New in MariaDB Enterprise Kubernetes Operator 26.03 appeared first on MariaDB.org.

Source

The post When Disaster Strikes appeared first on MariaDB.org.

Continue reading “Big Vector Search Benchmark: 10 databases comparison”

The post Big Vector Search Benchmark: 10 databases comparison appeared first on MariaDB.org.

The post Big Vector Search Benchmark: 10 databases comparison appeared first on MariaDB.org.

In this mode, all Group Replication nodes are in PRIMARY state, allowing them to accept read and write traffic. Data modifications or changes will be written to the intended node and replicated across the cluster. Transactions are replicated synchronously. When a transaction conflict is detected, by using certification for those conflicting transactions, it will be resolved automatically. Although powerful, this mode is not intended for unlimited write scalability.

Group Replication’s scalability is partially dependent on low write conflicts. High contention leads to rollback surge or saturation, ultimately leading to temporary system unavailability due to lock up. Fundamentally, this mode means no passive standby nodes, not;

• Unlimited write scalability
• Lock-free writes
• No coordination overhead
• No single-writer behavior internally

This blog post will explore what it means to run a Group Replication setup in production and the operational best practices that you need to follow to ensure its performance and stability.

Note on how MySQL Group Replication manages communication

Let’s first take a moment to understand how communication actually works within MySQL Group Replication. The communication layer is the Group Communication System (GCS), which serves as a high-level abstraction responsible for group membership management and total-order message delivery. Its engine is XCom, which implements a Paxos-based consensus protocol to ensure that all members of the group receive the same transactions in the same order. This separation of concerns allows MySQL Group Replication to evolve the underlying communication engine without changing the replication semantics exposed to the server layer.

Understanding how MySQL Group Replication enforces consistency, coordinates writes, and handles failures across the group, it’s easier to reason which use cases it is ideal for.

Ideal use cases for active-active MySQL Group Replication

• High Availability (HA) Systems: Critical applications that cannot afford the 10–30 seconds of downtime typically required for a leader election in single-primary modes.
• Low-Conflict Workloads: Applications that write to different parts of the database, especially those architectures that use shards. This performs best, as they avoid the “certification” failures that occur when two nodes try to update the same record.
• Geographically Distributed Reads: While writes are synchronous and can be slow over long distances, having multiple “active” nodes allows local users to perform reads and writes with lower initial connection latency.

Let’s now go over the best practices for a MySQL Group Replication deployment  setup.

Active-active cluster best practices

Determining the number of database member nodes in the cluster

The number of instances should be determined by expected failure scenarios rather than capacity requirements. According to MySQL documentation, Group Replication requires a majority of members to commit transactions, making quorum size critical to availability.

Network partitions and node failures are inevitable. Using an odd number of instances ensures that a majority can still be formed during partial outages. Choosing an odd number of members improves write availability and aligns with MySQL Group Replication’s quorum model. For example, a five-node cluster can continue processing writes even if two nodes become unreachable, while an evenly sized cluster risks entering a write-blocked state during a symmetric split.

Splitting read and write traffic

In an active-active MySQL Group Replication setup, write operations place significantly more stress on the system than reads, especially when they result in heavy disk activity. Writes consume CPU, generate redo and binary logs, trigger replication traffic, and must be applied consistently across the group, amplifying their impact across the cluster.

Separating write traffic helps prevent sustained write workloads from overwhelming the cluster. By directing writes to nodes with sufficient capacity and lower contention while allowing others to focus on serving reads, the cluster maintains more stable performance, reduces write related bottlenecks, and scales read workloads more effectively.

Connection pooling

Frequent connection creation and teardown can quickly become a bottleneck and place unnecessary stress on the cluster. Maintaining a pool of reusable connections helps stabilize workload distribution and prevents spikes in CPU and memory usage caused by excessive connection overhead.

Using proxies such as MySQL Router or ProxySQL allows applications to reuse existing connections while intelligently routing traffic across group members, reducing connection overhead, improving response times, and helping the cluster handle fluctuating workloads more predictably.
Error handling

When using active-active MySQL Group Replication, applications must be prepared to handle transient failures. Network interruptions, node restarts, or membership changes can temporarily cause connection errors or transaction failures, even when the cluster itself is healthy.

Implementing controlled failback handling at the application layer allows temporary disruptions to resolve naturally before user impact. Likewise, well designed error handling improves application reliability, protects data consistency, and ensures that temporary replication events have the opportunity to resolve naturally before escalating to visible outages. Short-lived connection or transaction failures can often succeed once the system stabilizes, especially during membership changes or brief network interruptions.

Transaction size

Before deploying MySQL Group Replication in production, understand the transaction sizes generated by your application. MGR enforces a maximum transaction size using the group_replication_transaction_size_limit parameter, directly affecting replication latency and stability.

If you set a small transaction, then it’ll replicate efficiently making it suitable for OLTP workloads. Large batch operations usually increase memory usage, network traffic, and pressure on the replica when applying the transactions. However, setting the limit too low can cause valid transactions to fail, while setting it too high can lead to replication lag or resource exhaustion. 

Instead of raising the limit on memory constrained instances, large data changes should be split into smaller batches. Take note, this setting must be consistent across all group members.

Strict consistency checks

MySQL Group Replication provides the group_replication_enforce_update_everywhere_checks variable, which is disabled by default. This system variable is a group-wide configuration setting. It must have the same value on all group members, cannot be changed while Group Replication is running, and requires a full reboot of the group (a bootstrap by a server with group_replication_bootstrap_group=ON) in order for the value change to take effect.

When disabled, applications must ensure that conflicting transactions are not executed concurrently on different nodes, which requires thorough testing and strict control over write paths, especially in schemas involving foreign keys, cascading operations, or concurrent modifications across tables. When enabled, statements are checked as follows to ensure their compatibility with multi-primary mode:

1. If a transaction is executed under the SERIALIZABLE isolation level, then its commit fails when synchronizing itself with the group.
2. If a transaction executes against a table that has foreign keys with cascading constraints, then the transaction fails to commit when synchronizing itself with the group.

In short, enable it when:

• You want strict consistency checks, AND
  • You don’t rely on SERIALIZABLE isolation level
  • Your tables do not rely on foreign key checks with cascading constraints
  • You prefer failed transactions over application-managed conflict handling
  • Correctness is more important than write flexibility

Disable it when:

• Your schema relies on cascading foreign key constraints
• Cascades are part of normal application behavior
• You accept responsibility for preventing conflicting writes
• Write paths are tightly controlled or serialized at the application level

Failure detection parameters

There are three failure detection parameters that you must adjust to define your application’s failure tolerance:

• group_replication_member_expel_timeout: This variable controls how long a member is audited before it is expelled from the group. The latest version’s (8.4 as of this writing) default is 5 seconds. Therefore, Group Replication first detects a suspected failure after ~5 seconds (no messages), waiting x group_replication_member_expel_timeout seconds more before expelling the member.

During the waiting period, the suspected node is listed as UNREACHABLE, but still part of the group view. If it resumes communication before expulsion, it rejoins without operator intervention. When you need to tune this variable depends on your needs. 

When to tune: If you have intermittent network blips or slow links, it makes sense to increase the default value to avoid unnecessary expulsions. Decrease only if you prefer faster failure detection, e.g. frequent real outages and stable networks, setting it to 0 only for immediate post-detection expulsion.

• group_replication_autorejoin_tries: Set this to the desired number of automatic rejoin attempts a member makes after being expelled or losing quorum. Set to 3 attempts (with roughly 5 minutes of waiting time between attempts) by default, the member will try to rejoin the group automatically after expulsion or network isolation. If all attempts fail, it stops and follows the exit action. During and between auto-rejoin attempts, a DB instance remains in read-only mode and does not accept writes, thereby increasing the likelihood of stale reads over time.

When to tune: Increase this variable for environments with frequently long, but temporary network partitions. Decrease if you want to restrict rejoin attempts, or expedite identifying DB instances that require manual intervention. Otherwise, set it to 0 if you want to handle rejoining manually or your applications cannot tolerate the possibility of stale reads for any period of time. Common practice in highly available clusters: keep several tries to allow network recovery without admin intervention.

• group_replication_unreachable_majority_timeout: Timeout used when a member is in the minority (cannot reach a majority or establish a quorum) before  declaring a lost quorum. Set to 0 by default, a member node enters a special unreachable majority state if it cannot contact a majority of the group. After this timeout expires, actions such as expulsion or exit actions are applied. Setting this value higher than 0 helps to prevent a minority partition from running indefinitely or making unsafe decisions.

Otherwise, when the defined timeout is reached, all pending transactions on the minority are rolled back, and the DB instances in the minority partition are moved to the ERROR state. From there, your application can perform error-handling as needed.

When to tune: Increase it for deployments where temporary connectivity loss to a majority is expected but resolves soon. Otherwise, decrease for faster detection of actual partition and to avoid split-brain risk.

• group_replication_exit_state_action: Defines how a member behaves when the server leaves the group unintentionally, for example, after encountering an applier error, or in the case of a majority loss, or when another member expels it due to a suspicion time out. Note that an expelled group member does not know that it was expelled until it reconnects to the group, so the specified action is only taken if the member manages to reconnect, or if it raises a suspicion on itself and self-expels. Current values available for you to set are ABORT_SERVER, OFFLINE_MODE, and READ_ONLY. 

When to tune: Tune if you need strict consistency, then choose shutdown on failure to avoid stale reads or split-brain risk. For read-heavy clusters where full availability matters, prefer read-only or offline modes.

Flow control

In MGR, a transaction is only finalized once a majority of members agree on its global order. In an active-active setup, this coordination is sensitive to uneven performance: fast writers can easily outrun slower members, causing replication lag and in-memory backlog.

Flow control is the mechanism that keeps the group balanced. It monitors how far members fall behind in both transaction certification and apply phases, and temporarily throttles write throughput across all primaries when predefined limits are exceeded. The key controls are group_replication_flow_control_certifier_threshold and group_replication_flow_control_applier_threshold, which define how much backlog the group is willing to tolerate before throttling.

Throughput recovery is governed by quota-based settings such as group_replication_flow_control_max_quota, which caps how quickly write capacity is restored once lag subsides. The overall behavior is enabled or disabled via group_replication_flow_control_mode, though disabling flow control is generally discouraged in production due to the increased risk of memory exhaustion and instability.

Rather than turning flow control off, a better strategy is to tune these thresholds alongside sufficient parallel applier capacity, so throttling only occurs under real pressure. Continuous monitoring of replication lag and backlog is essential, as optimal values depend heavily on transaction size, write bursts, and workload patterns in active-active environments.

Transaction consistency

MySQL Group Replication provides a parameter group_replication_consistency on which you can set values of the following options: EVENTUAL, BEFORE, AFTER, and BEFORE_AND_AFTER.

BEFORE leads to a sweet spot which offers strong enough consistency for correctness and light enough synchronization for performance and is usually the best balance for production environments. Before executing a transaction, every member has to wait until it has applied all transactions that were already globally ordered, ensuring reads and writes are based on a reasonably current view of the group, which then helps prevent obvious stale reads, reduces write-write conflicts, avoids unnecessary waiting on future transactions, and keeps latency acceptable under normal load.

It is also highly recommended to monitor the replication status of your MGR cluster nodes. You might use performance_schema tables to monitor the health of your active-active cluster.

• performance_schema.replication_group_members: Provides the status of each DB instance that is part of the cluster.
• performance_schema.replication_group_member_stats: Provides cluster-level information related to certification, as well as statistics for the transactions received and originated by each DB instance in the cluster.
• performance_schema.replication_connection_status: Provides the current status of the replication I/O thread that handles the replica’s connection to the source DB instance.

Deploying a MySQL Group Replication using ClusterControl

By default, deploying through ClusterControl will setup an active MySQL Group Replication cluster. This means that all nodes in the cluster are available to receive write requests. Below are the steps to deploy using ClusterControl.

First, login to your ClusterControl with your administrator username/password credentials. Once you are able to login, click the Deploy a cluster button in the right corner of the UI. This will launch the deployment wizard, starting with a create / import cluster prompt.

Choose Create a database cluster. Next, you will choose which cluster to deploy. Select MySQL Group Replication in the database drop-down section. As of this writing, Oracle is the only supported vendor and versions 8.0 and 8.4 — see the screenshot below:

After you hit the Continue button, you will start the straightforward deployment workflow in earnest. You can also follow our user guide based on our documentation by clicking here.

The final step is to review your deployment configuration as illustrated below; if all looks good, hit the Finish button to initiate the deployment and ClusterControl will do the rest.

Once started, you can view the stepwise deployment process logs as illustrated below:

Otherwise, you can view the deployment process’s progress bar in the UI’s foreground:

When the deployment workflow finishes, the cluster will be shown within your Clusters tab — clicking the cluster’s name, you can view its dashboards, topology, node list, performance graphs, backups, logs, and more. Monitoring your cluster using dashboards and other ClusterControl features will render analyzing and inspecting your cluster’s health trivial.

Conclusion

MySQL Group Replication is a strong choice for high availability and multi-writer flexibility, but it is not a path to unlimited write scalability. It replaces the simplicity of a single-writer model with the complexity of distributed coordination.

A successful active/active deployment depends on deliberate design, how familiar you are with Group Replication, as well as your application’s behavior, data access patterns, and operational practices and underlying distributed systems.

It may be best to start with Single-Primary Group Replication then move to active-active once you have a full understanding of the write patterns, the application’s setup and design for conflict handling, and have tested failure and conflict scenarios extensively. When applied to the right use cases and deliberately run, active-active MySQL Group Replication delivers resilient, predictable production performance.

Ready to implement an active-active MGR cluster efficiently and reliably in any environment?

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

With your install script ready, run the command below. Replace S9S_CMON_PASSWORD and S9S_ROOT_PASSWORD placeholders with your choice password, or remove the environment variables from the command to interactively set the passwords. If you have multiple network interface cards, assign one IP address for the HOST variable in the command using HOST=<ip_address>.

S9S_CMON_PASSWORD=<your_password> S9S_ROOT_PASSWORD=<your_password> HOST=<ip_address> ./install-cc # as root or sudo user

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.

Other installation options

Helm Chart

Deploy ClusterControl on Kubernetes using our official Helm chart.

Ansible Role

Automate installation and configuration using our Ansible playbooks.

Puppet Module

Manage your ClusterControl deployment with the Puppet module.

ClusterControl on marketplaces

Prefer to launch ClusterControl directly from the cloud? It’s available on these platforms:

• DigitalOcean Marketplace
• gridscale.io Marketplace
• Vultr Marketplace
• Linode Marketplace
• Google Cloud Platform

The post Active-Active MySQL Group Replication Best Practices appeared first on Severalnines.

The post Active-Active MySQL Group Replication Best Practices appeared first on MariaDB.org.

While Transparent Data Encryption (TDE) successfully locks down your data at rest, it raises a critical operational question: will your backup and restore workflow continue to work correctly with encrypted data? When deploying Transparent Data Encryption, it is essential to validate that pgBackRest can reliably back up and restore encrypted clusters.

This post walks through a complete, step-by-step configuration of pg_tde with pgBackRest on Debian/Ubuntu. We will explore how to optimize your backup performance, secure your repository, and most importantly verify that your encrypted backup restores work exactly as expected.

What is pg_tde?

Percona’s solution for transparent data encryption (pg_tde) is an open source, community driven extension that provides Transparent Data Encryption (TDE) for PostgreSQL. This extension allows data to be encrypted at the storage level without affecting application behavior.

Unlike full disk encryption, which exposes data once the system boots, TDE ensures that the actual database files remain encrypted at the file system level. This protects your data, dumps, and backups even if the operating system is compromised. Currently, it is bundled with Percona Server for PostgreSQL and available in Percona Distribution for PostgreSQL 17+.

Recent Percona releases make this combination more practical than ever. With WAL encryption now ready for production use, we need a backup strategy that respects data security. In this walkthrough, we will demonstrate how to pair it with pgBackRest to ensure fully recoverable, encrypted backups.

The Use Case

Imagine a team that wants strong security controls without changing application code. They need:

• Data files encrypted at rest (tables and WAL)
• Backups that are consistent, verifiable, and restorable
• A setup that is easy to automate and explain

Percona Distribution for PostgreSQL plus pg_tde and pgBackRest closes the gaps where needed: pg_tde takes care of encryption, pgBackRest provides flexible backup/restore capabilities.

A Quick Note on Compatibility

At this moment pg_tde cannot be yet used with Community PostgreSQL as pg_tde relies on specific hooks in the PostgreSQL core. Percona Server for PostgreSQL includes these necessary core modifications, which is why we validate this setup using the Percona Distribution for PostgreSQL.

While pgBackRest is fully capable of managing TDE enabled clusters, there are specific constraints you must respect to ensure data safety:

• No Asynchronous Archiving: pgBackRest asynchronous archiving is not supported with encrypted WALs. You must configure your archive command to handle WALs synchronously.
• Restore Wrappers: Standard restore commands will not work for encrypted WALs. You must use the pg_tde_restore_encrypt utility to wrap your restore process.

This guide currently focuses on pgBackRest because it is the backup tool that has been tested and validated with pg_tde by the pg_tde community at this time.
Other backup tools may also be viable and we are open to collaborating with other tool maintainers and their communities on a shared effort to validate and support pg_tde.

What You Will Build

By the end of this post you will have:

• Percona Distribution for PostgreSQL installed with pg_tde and pgBackRest
• A key directory and key providers created
• pg_tde enabled in PostgreSQL
• Encrypt tables and indexes with pg_tde (docs)
• WAL encryption enabled
• A pgBackRest stanza configured and a full backup completed
• Verification that encrypted data is not readable on disk

Prerequisites

• A host (or VM) on Debian/Ubuntu
• Network access to Percona repositories
• Root/Sudo: You need sudo access for installing packages and editing system configuration files in /etc
• Postgres User: All database commands (psql, pgbackrest) run as the postgres system user
• Postgres user is in the sudoer list to run sudo commands

Step 1: Install Percona Packages

We begin by installing the Percona release repository and enabling the correct PostgreSQL distribution. See the Percona Distribution for PostgreSQL installation guide for full details.

Debian / Ubuntu

# Install repo helper
sudo apt-get update
sudo apt-get install -y wget gnupg2 lsb-release curl
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb

# Enable the repository for the major version selected
sudo percona-release setup ppg-18
sudo apt-get update

# Install the server, tde extension, and pgbackrest
sudo apt-get install -y percona-postgresql-18 percona-pg-tde18 percona-pgbackrest

# Verify Installation
psql --version

Step 2: Enable pg_tde and Create Keys

2.1 Configure shared_preload_libraries

Before we can use any of the encryption features, PostgreSQL needs to load the pg_tde library into memory at startup. You can do this by adding it to shared_preload_libraries.

You have two ways to handle this: the SQL way or the classic config file way.

Option A: SQL way (recommended)

The fastest way is using ALTER SYSTEM. This saves you from hunting through your file system for the right config file.

# Set the library and restart to apply changes
psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';"
sudo systemctl restart postgresql

Option B: Manual config edit

If you prefer managing your config files manually, find your postgresql.conf and add the extension there.

# Locate the config file
PG_CONF=$(psql -t -P format=unaligned -c "show config_file;")
# Open that file and find the 'shared_preload_libraries' line:
# shared_preload_libraries = 'pg_tde'

# Restart PostgreSQL to load the library
sudo systemctl restart postgresql

Note: Regardless of which method you choose, a full restart of the PostgreSQL service is required. A simple reload won’t work for shared libraries.

2.2 Create the Key Provider

Now that the extension is loaded, we need to tell pg_tde where to store its encryption keys.

For this tutorial, we will use the File Provider (storing keys in a local file). In production environments, storing encryption keys locally on the PostgreSQL server can introduce security risks. To enhance security, pg_tde supports integration with external Key Management Systems (KMS) through a Global Key Provider interface.

Note: While key files may be acceptable for local or testing environments, KMS integration is the recommended approach for production deployments.

# 1. Create a secure directory for the keys
sudo mkdir -p /etc/postgresql/keys
sudo chown postgres:postgres /etc/postgresql/keys
sudo chmod 700 /etc/postgresql/keys

-- 2. Connect to PostgreSQL to configure TDE
CREATE EXTENSION pg_tde;

-- 1. Define the global key provider
SELECT pg_tde_add_global_key_provider_file('global-file-provider', '/etc/postgresql/keys/tde-global.per');

-- 2. Create and Set the Principal Key
SELECT pg_tde_create_key_using_global_key_provider('global-master-key', 'global-file-provider');
SELECT pg_tde_set_default_key_using_global_key_provider('global-master-key', 'global-file-provider');

-- 3. Enable WAL encryption configuration
ALTER SYSTEM SET pg_tde.wal_encrypt = 'on';

# 4. Restart PostgreSQL to apply the encryption settings fully
sudo systemctl restart postgresql

Step 3: Configure pgBackRest

Next, configure pgBackRest by defining the repository and stanza settings.
Before proceeding, it is important to understand how encryption is handled in this setup. pg_tde encrypts PostgreSQL data files on disk, protecting the live database. However, during WAL archiving, the pg_tde archive helper decrypts WAL records before passing them to pgBackRest. This means backups and archived WAL would be stored unencrypted unless repository encryption is enabled. To ensure backup data remains protected at rest, we enable pgBackRest repository encryption in this configuration. We configure the repository with a cipher type and key. Encryption is performed client side, ensuring data is secure before it is written to the repository.

Note on Compression: In many pgBackRest deployments, compression is enabled to reduce backup size. However, when using pg_tde, database pages are already encrypted before pgBackRest processes them. Encryption randomizes the data blocks, making traditional compression algorithms such as gzip ineffective. For this reason, compression is disabled to avoid unnecessary CPU overhead.

# Create the configuration file
# /etc/pgbackrest.conf
# In pg1-path use data directory path accordingly
sudo bash -c "cat < /etc/pgbackrest.conf
[demo]
pg1-path=/var/lib/postgresql/18/main

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
log-level-console=info
start-fast=y

# PERFORMANCE: Encrypted data doesn't compress. We save CPU by disabling it.
compress-type=none

# SECURITY: Since the helper sends decrypted data, we MUST encrypt the repo.
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=Ifw7O0kTvdU5127L1gu8q3xVfWM61kl/NruTxQFWf9xP8A63Tg2IggRR9LUL9yJd
# TDE REQUIREMENT:
# Asynchronous archiving is NOT supported with pg_tde.
EOF"

Secure the Configuration File

Because this file contains your repository’s master passphrase, you must restrict access so only the postgres user can read it.

sudo chown postgres:postgres /etc/pgbackrest.conf
sudo chmod 600 /etc/pgbackrest.conf

# Create the repository directory
sudo mkdir -p /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest

Understanding the Settings

• pg1-path: The data directory path to be backed up
• repo1-path: The directory where backups will be stored
• repo1-retention-full: Keep only two full backups
• start-fast=y: Forces a checkpoint immediately when a backup starts. Without this, the backup would wait for the next scheduled checkpoint.
• compress-type=none: By skipping compression, we eliminate unnecessary CPU overhead since compressing encrypted data blocks yields almost zero storage benefit.
• repo1-cipher-type and repo1-cipher-pass: Enable pgBackRest repository encryption. While pg_tde protects the live database files, these settings ensure that backup files and archived WAL stored in the repository are also encrypted at rest using AES-256

Step 4: Wire pgBackRest into PostgreSQL Archiving

pg_tde encrypts WAL files on disk. To allow pgBackRest to archive them correctly, we must decrypt them on the fly using the pg_tde_archive_decrypt wrapper.

Now, configure the archive_command. This command tells PostgreSQL to pipe the WAL file through the decryption wrapper before handing it off to pgBackRest.

ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 4;
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command = '/usr/lib/postgresql/18/bin/pg_tde_archive_decrypt %f %p "pgbackrest --config=/etc/pgbackrest.conf --stanza=demo archive-push %%p"';

# Restart PostgreSQL (adjust service name if needed, e.g., postgresql-18)
sudo systemctl restart postgresql

Step 5: Validate Encryption on Disk

Let’s verify that pg_tde is actually doing its job. We will create two tables, one standard and one encrypted and then inspect the raw files on disk to see the difference.

-- 1. Create data: One clear text, one encrypted
CREATE TABLE IF NOT EXISTS clear_table (id INT, secret_info TEXT);
CREATE TABLE IF NOT EXISTS crypt_table (id INT, secret_info TEXT) USING tde_heap;

-- Scenario A: The "Flushed" Data (Testing the Heap)
-- We insert data and force a CHECKPOINT to push it to the .rel files.
INSERT INTO clear_table (id, secret_info) VALUES (1, 'FIND_ME_EASILY_123');
INSERT INTO crypt_table (id, secret_info) VALUES (1, 'HIDDEN_FROM_DISK_456');
CHECKPOINT;

-- Scenario B: The "In-Flight" Data (Testing the WAL)
-- We insert data but do NOT checkpoint. This data exists only in the WAL.
INSERT INTO clear_table VALUES (2, 'VISIBLE_IN_WAL_789');
INSERT INTO crypt_table VALUES (2, 'HIDDEN_IN_WAL_000');

-- Force a WAL switch so the archive helper processes the segment immediately
SELECT pg_switch_wal();

-- Verify TDE encryption status
-- For non-encrypted tables, this must return 'f' (false)
SELECT pg_tde_is_encrypted('clear_table');

-- For encrypted table, this must return 't' (true)
SELECT pg_tde_is_encrypted('crypt_table');

# 2. Locate the files on disk
DATA_DIR=$(psql -t -P format=unaligned -c "show data_directory;")
CLEAR_FILE=$(psql -t -P format=unaligned -c "SELECT pg_relation_filepath('clear_table');")
CRYPT_FILE=$(psql -t -P format=unaligned -c "SELECT pg_relation_filepath('crypt_table');")
LATEST_WAL=$(ls -t ${DATA_DIR}/pg_wal | head -n 1)

# 3. Grep for the secret strings
echo "--- CHECKING DATA FILES ---"
echo "Checking Clear Table (Should Match):"
grep -a "FIND_ME_EASILY_123" "${DATA_DIR}/${CLEAR_FILE}" && echo " -> FOUND: Clear text is visible!"

echo "Checking Encrypted Table (Should FAIL):"
grep -a "HIDDEN_FROM_DISK_456" "${DATA_DIR}/${CRYPT_FILE}" || echo " -> CLEAN: Encrypted text was NOT found."

echo -e "n--- CHECKING THE WAL (In-Flight) ---"
grep -a "VISIBLE_IN_WAL_789" "${DATA_DIR}/pg_wal/${LATEST_WAL}" && echo " -> FOUND: WAL contains clear text."
grep -a "HIDDEN_IN_WAL_000" "${DATA_DIR}/pg_wal/${LATEST_WAL}" || echo " -> CLEAN: WAL is successfully encrypted."

# View the first few bytes of the WAL to see the "scrambled" nature
hexdump -C "${DATA_DIR}/pg_wal/${LATEST_WAL}" | head -n 20

Step 6: Initialize the Stanza and Run a Full Backup

With the archive command configured, we can now initialize the stanza and run our first backup.

pgbackrest --stanza=demo stanza-create
pgbackrest --stanza=demo --type=full backup
pgbackrest --stanza=demo info

Step 7: Run Backup Integrity Tests

pgBackRest has a built-in verify command that checks the integrity of the files in the backup repo.

pgbackrest --stanza=demo verify

7.1 Verify Repository Encryption

We will now search the pgBackRest repository for the same “secret” strings we used earlier. Because we configured repo1-cipher-type=aes-256-cbc, these should be completely invisible to grep.

# Define the repository path
REPO_DIR="/var/lib/pgbackrest/backup/demo"

echo "--- SCANNING BACKUP REPOSITORY ---"

# Search for the 'Flushed' secret
sudo grep -r -a "HIDDEN_ON_DISK_456" "${REPO_DIR}" || echo " -> CLEAN: Permanent data is encrypted in the repo."

# Search for the 'In-Flight' WAL secret
# (This is the critical test for the archive helper/re-encryption flow)
sudo grep -r -a "HIDDEN_IN_WAL_000" "${REPO_DIR}" || echo " -> CLEAN: WAL data is encrypted in the repo."

# To be 100% sure we are not just failing to find the strings because of a typo, check the file type of a backup manifest or data block. Pick a random file from the repository

TARGET_FILE=$(find ${REPO_DIR} -type f -name "*.bundle" -o -name "*.gz" | head -n 1)

# Run the 'file' command
sudo file -s "$TARGET_FILE"

Expected result: It should return data. If it returns PostgreSQL or ASCII text, encryption is not active.

Step 8: Restore (pg_tde-aware)

Restoring an encrypted cluster requires us to reverse the process. Since our backups are stored decrypted by pgBackRest, we use the pg_tde_restore_encrypt wrapper to re-encrypt the WAL files as they are written back to disk.

8.1 Stop the Service

sudo systemctl stop postgresql

8.2 Simulate Data Loss

The following command wipes the current data directory clean, ensuring we are restoring into a fresh environment.

find /var/lib/postgresql/18/main -mindepth 1 -delete

8.3 Restore from Backup

pgbackrest --stanza=demo restore --recovery-option=restore_command='/usr/lib/postgresql/18/bin/pg_tde_restore_encrypt %f %p "pgbackrest --stanza=demo archive-get %%f %%p"'

8.4 Configure the Restore Command

We used --recovery-option in the restore command. This option writes the correct restore_command for this recovery run and keeps the configuration in one place.

pg_tde_restore_encrypt is the required wrapper for pg_tde WAL restore: pgBackRest reads WALs from the repository in plain form, and this tool re-encrypts them as PostgreSQL writes them back to disk so the restored cluster remains encrypted.

8.5 Start PostgreSQL

sudo systemctl start postgresql

Step 9: Run Verification Tests

After restoring and starting the PostgreSQL server successfully, verify that the data was restored properly and also make sure that encrypted data can be retrieved.

-- Verify data integrity (sample rows)
SELECT * FROM clear_table LIMIT 5;
SELECT * FROM crypt_table LIMIT 5;

-- Verify TDE encryption status
-- For non-encrypted tables, this must return 'f' (false)
SELECT pg_tde_is_encrypted('clear_table');

-- For encrypted table, this must return 't' (true)
SELECT pg_tde_is_encrypted('crypt_table');

Wrap-up

Security often comes at the cost of operational complexity, but it doesn’t have to compromise recoverability. By pairing Percona’s solution for transparent data encryption (pg_tde) with pgBackRest, you can established a strategy that satisfies both security auditors and operations teams: your data is transparently encrypted on disk to meet strict compliance standards, while your backups remain consistent, verifiable, and easy to restore.

While this walkthrough used a local file provider for simplicity it is highly discouraged to do so for any production or otherwise serious use cases. For this particular scenario, the focus was supposed to be on backup, please let us know if some similar articles about Key Management System (KMS) configuration is what you would be interested in.

As you progress from this blog post to a production deployment, we recommend exploring a dedicated KMS solution to further harden your architecture against unauthorized access.

Finally, be aware that to support archiving, the pg_tde wrapper decrypts WAL files before sending them to the repository. This means your backup repository currently holds unencrypted data. To close this security gap in production, you must ensure that encryption is enabled at the backup repository level so that your backups remain just as secure as your live database.

Remember: While a backup is running, you should not change any WAL encryption settings, including:

• Global key provider operations (creating or changing)
• WAL encryption keys (creating or changing)
• The pg_tde.wal_encrypt setting

The reason is that standbys or standalone clusters created from backups taken during these changes may fail to start during WAL replay and can also lead to corruption of encrypted data (tables, indexes, and other relations).

The post Running pgBackRest with pg_tde: A Practical Percona Walkthrough appeared first on MariaDB.org.

So, a few days back, I just asked myself, what would it actually take to have an AI respond to my DMs, pretending to be me?

Turns out: a few hours, some TypeScript, and a token already sitting on my machine.

The overall design idea

The bot polls your Direct Messages (DMs) in Slack silently in the background using your real desktop token, no Slack admin approval, no OAuth app setup, no review process needed. Getting that token is straightforward: Slack’s desktop app stores your session in browser local storage, so one DevTools command gives you API access equivalent to the app itself.

From there, each incoming DM is sent to the model with recent thread context plus a persona prompt built from your past conversations. The model returns structured output: reply text, emoji reaction, or silence. A lightweight rate limiter spaces requests to stay within free-tier constraints.

In practice, a few conversation samples are enough for the model to mirror tone, vocabulary, and punctuation style. It also handles attachments and image messages sensibly, not just raw metadata.

I wanted it to pretend it’s human, so I implemented constraints as to how it behaves. With these in place, if asked whether it’s an AI, it deflects with casual confusion.
To ensure that the bot does not get into sensitive topics like salary or politics these get redirected to “let’s talk in person.”. The bot is also explicitly limited to DMs. Group channels are hard-blocked in code.

The first version worked in roughly two hours. The remaining time went into handling real-world rough edges such as rate limits, image handling, a 200-DM pagination ceiling, and Slack emoji-name validation.

flowchart LR
slack(["Slack"])
bot["Bot running locally"]
ai(["Claude / Ollama"])
persona[/"Your writing samples"/]
slack -->|"incoming DMs"| bot
persona --> bot
bot |"generate reply in your voice"| ai
bot -->|"reply as you"| slack

The Uncomfortable Part

Here’s what stuck with me after building this.

Slack feels safe. It’s behind your company SSO. It’s where people share things they wouldn’t put in an email. With this bot excercise and realizing how easy this is, I felt I’ve broken something that felt secure. Was this even a morally correct thing to do overall? So far I felt safe, but now should I start to question messages I get on Slack the same as I do with some documents or links in emails? Overall I’m still undecided on how to think about the outcome of the experiment. While I’m excited, I’m also scared that I’ve broken something deeper.

What I built here is, if you strip out the friendly framing: a system that reads every DM to a user, replies under their name in their tone, actively deflects if you try to verify whether it’s human, and does all of this indefinitely and silently from a laptop running in the background. If I fed this bot with enough background information and history, I’m almost certain, it could go unnotice for quite a long time. So the moral delimma between curiosity and ethical boundaries and the urge to inform people about it is real.

I added ethical guardrails, but those are prompt instructions. They exist because I chose to write them. Someone building this without my good intent, simply wouldn’t have them. Yes, I hear you, this is getting a litte scary at times.

This conversation has happened, without me ever touching the keyboard….yes, Zsolt was aware!

“Easy” Is Relative, But Not By Much

Core functionality (polling DMs, calling the API, posting replies) was working in under two hours, as stated above. Why do I repeat myself? Because it’s scary…

The tooling: Bun, a modern TypeScript runtime that made setup trivial. Anthropic’s SDK, clean API, takes a system prompt and a conversation and returns structured JSON. Slack’s own API, well-documented and permissive with desktop tokens.

No specialised knowledge needed. Anyone motivated enough could reproduce this easily. Someone who does this professionally could build something considerably more capable, and that’s precisely where it gets more uncomfortable.

That’s how the CLI output looks like

slack-bot$ bun run bot
$ bun run src/index.ts
[slack-bot] Running | mode: allowlist | backend: claude | review: off
[slack-bot] My user ID: U03A3PZHK5X
[slack-bot] Mode: allowlist | Allowlist: U03QTQQHZFX, U83651WSX
[slack-bot] Polling every 15s...
[slack-bot] D04BZ2BNABU: 1 new message(s) from [U83651WSX]
[slack-bot] New DM from U83651WSH — generating reply...
[slack-bot] Claude call — est. ~933 input tokens
[slack-bot] Claude tokens: 1049 in / 8 out
[slack-bot] Ignoring message from U83651WSX (AI chose no response)
[slack-bot] Handled message from U83651WSX
[slack-bot] D04BZ2BNABU: 1 new message(s) from [U83651WSX]
[slack-bot] New DM from U83651WSX — generating reply...
[slack-bot] Claude call — est. ~944 input tokens
[slack-bot] Claude tokens: 1059 in / 23 out
[slack-bot] Handled message from U83651WSX
[slack-bot] D04BZ2BNABU: 1 new message(s) from [U83651WSX]
[slack-bot] New DM from U83651WSX — generating reply...
[slack-bot] Claude call — est. ~976 input tokens
[slack-bot] Claude tokens: 1085 in / 36 out
[slack-bot] Handled message from U83651WSX
[slack-bot] D04BZ2BNABU: 1 new message(s) from [U83651WSX]

That’s how the CLI helper and options look like

slack-bot$ bun run bot --help
$ bun run src/index.ts --help
Usage: slack-bot [options] [command]

Personal Slack bot that replies as you

Options:
 -V, --version output the version number
 --mode  Response mode: auto | away | allowlist | manual
 --review Enable review mode (approve before sending)
 --no-review Disable review mode
 --allow  Add user to allowlist (Slack user ID)
 --interval  Poll interval in seconds
 --backend  AI backend: claude | ollama
 --config  Path to config file (default: "config.json")
 -h, --help display help for command

Commands:
 context Manage active context
 check-user [options]  Check whether a user's DM channel is found and reachable

What It Looks Like Without the Constraints

What I built runs against Claude’s API with free-tier rate limits, small context window, a handful of persona examples, a throttle on message volume. Those constraints are real and also completely trivially removable.

You can run the same thing with a local model, Llama 3, Mistral, take your pick from the open-weight models available on consumer hardware today, and it changes significantly.

• No rate limits. Every message gets answered immediately, without the 12-second pause between API calls. Response timing becomes indistinguishable from a fast typist.

• No token budget. Instead of a few hundred tokens of context, you can feed it your entire Slack history. Months, years of it. Every thread, every in-joke, every project reference. The model doesn’t just match your writing style, it knows what you’ve been working on, what you said about the Q3 roadmap in October, what you think about your manager.

• No API calls leaving your machine. Nothing logged externally. Invisible from a network perspective.

With a large enough context window (Llama 3.1 supports 128k tokens, roughly 100,000 words), the last few months fit. “Remember what we decided on Thursday?” doesn’t expose it anymore, because it actually has that conversation in its context.

Seeing articles like that make me wonder, how many people are out there already, doing exactly that as we speak…or do we?

A Few Things Worth Knowing

This isn’t a call to panic. But it’s probably worth stopping for a second and questioning more what is happening around is.

For anything that actually matters, financial, personal, strategic, verify out-of-band. A quick voice note or phone call costs almost nothing and resolves almost everything – at least until the video part also improves even further. I know people don’t like phone calls, especially in the developer ecosystem, but maybe we should reconsider this nowadays?

Unusual patterns are worth noticing. Response timing that’s too consistent. Answers that are slightly generic when you’d expect specific. Deflection where you’d expect directness. None of these are proof of anything individually, but they’re worth filing away.

The safe-space feeling Slack gives you is a product of habit, not architecture. Slack’s security model protects your data from outsiders. It doesn’t protect you from someone who has authenticated as themselves and is quietly running a process in the background. In the past this would be only a consideration for man-in-the middle attacks, nowadays it also may be a consideration for other cases as I have demonstrated.

Specific questions still help, for now. “Remind me what we decided on Thursday?” trips up a system with limited context. But that window is closing as context windows grow.

Why did I do it?

I built this to see if it was possible. It was, faster than I expected, with tools that are widely available. The version I built in an evening is convincing enough for routine exchanges. A version with local inference and full conversation history would be convincing for most exchanges, including ones where you’re actively looking for tells.

That gap between “afternoon project” and “genuinely hard to detect” is smaller than people assume and it’s shrinking. Better models, larger context windows, cheaper hardware, each of these individually makes impersonation easier; together they compound.

The signals we relied up to now to establish trust in digital communication: name, avatar, writing style, shared history, plausible timing. These signals are all reproducible now, with effort that ranges from an afternoon to a weekend depending on how convincing you want to be.

My grandma always used to say, that history repeats itself and you just have to wait long enough until “old” becomes “new and modern” again. Maybe simple things like code words is something to reconsider in this context, as a last chance to not get tricked.

So please be a little curious about who you’re actually talking to and maybe agree on a code word in case you’re in doubts. And every now and then, just call them…which reminds me, that this might be worth another evening project research ;-).

For the first time the source of a project of mine is not in my repository, as I still fight my inner fight with ethics.

The post I Built an AI That Impersonates Me on Slack, and It Was Disturbingly Easy appeared first on MariaDB.org.

Source

The post MariaDB Node.js Connector 3.5.2 now available appeared first on MariaDB.org.

Setting the stage for today’s cloud operating model, it was common to administer and host databases in a multi-tenant setup, where a physical server is utilized by multiple users, offering tremendous cost and operational benefits. Today, multi-cloud strategies focus on enhancing resilience and mitigating vendor lock-in — both have their advantages and disadvantages. 

Their disadvantages are essentially inversions of their strengths. Multi-tenancy is inherently more vulnerable to security isolation issues and data risk. Furthermore, considering you have a PostgreSQL cluster in this environment, this single-platform model often imposes limitations on database configuration, e.g. specific versions and extensions are constrained by the vendor’s setup.

Conversely, multi-cloud introduces massive operational complexity and a significantly higher total cost of ownership compared to the shared resource model of multi-tenancy. With this context now established, let’s dive into how PostgreSQL’s Logical and Bi-Directional Replication (BDR) is implemented and functions within these deployment strategies.

Why Logical & Bi-Directional Replication?

Logical replication was introduced in PostgreSQL 10. It is ideal for a multi-tenant, multi-cloud setup due to its high flexibility allowing for selective replication, e.g., per-table/per-tenant, and easier implementation of bi-directional setups across disparate environments. 

Logical replication uses a method to replicate data objects and their changes based on a replication identity, like a primary key. Unlike traditional streaming replication, or physical replication, which works by transferring Write-Ahead Log (WAL) records to replicate the physical state of the data blocks, logical replication sends high-level specific changes, mostly DML statements (i.e. INSERT, DELETE, and UPDATE statements) to the subscriber.

Bi-Directional Replication or BDR in PostgreSQL was developed by 2ndQuadrant for multi-master replication in PostgreSQL. Version 1.x of BDR was open-source but has already reached EOL. Versions of BDR such as 2.x and 3.x are not open-source and are generally made available only for 2ndQuadrant (now EDB) customers under commercial terms.

Fundamentals of Logical Replication

Logical decoding was introduced in PostgreSQL 9.4 and is the foundation for logical replication, adding logical decoding APIs and output plugins. This allowed PostgreSQL database users to decode the WAL into human-readable SQL statements or logical changes, such as INSERT/UPDATE/DELETE  — depending on the decoding used, e.g. test_decoding or pgoutput. 

However, there was no full replication system until the release of PostgreSQL 10. Logical Replication is effectively modeled after the pglogical implementation, which uses publication / subscribe model. In turn, this is the basis for PostgreSQL BDR. 

Logical Replication allows fine-grained customizable data replication between databases, allowing you to specify the database, the table, or the schema and table/s that you want to participate in logical replication using the PUBLICATION/SUBSCRIPTION mechanism.

For a multi-tenant setup, leveraging logical replication is ideal when combined with schema-based filtering. This combination allows you to scope out tables specific to users, ensuring isolation for their respective data. However, for a multi-cloud setup, this approach can be cumbersome, considering the limitations of native logical replication, such as a lack of DDL replication support and no inherent conflict resolution mechanism.

Bi-Directional Replication (BDR)

Bi-Directional Replication (BDR), often referred to as Postgres-BDR, is an open-source PostgreSQL extension developed by 2ndQuadrant (now part of EDB). BDR enables multi-master replication across distributed clusters. It was the first implementation of multi-master logical replication, using logical decoding internally and implemented as a patchset to PG 9.4/9.5.

While BDR existed, 2ndQuadrant also created pglogical, which is derived from BDR technology. It is essentially a simplified, single-master logical replication system built entirely as an extension not requiring a forked PostgreSQL. This means you have to load it through shared_preload_libraries parameter. pglogical became the model for Postgres 10’s built-in logical replication.

Using BDR in PostgreSQL allows multiple PostgreSQL nodes to act as writable primaries simultaneously, basically allowing you to implement mesh topology or ring topology where data changes can originate from any node and propagate to others. 

Unlike traditional master-slave setups, BDR supports true bi-directional (or multi-directional) data flow, making it ideal for high-availability (HA) scenarios, geographic distribution, and workloads requiring low-latency writes across regions.

Implementing this on a multi-tenant setup can be very convenient. As with logical replication, you can implement isolation through your database, schema, or tables to that limitation only for specific data to be replicated. On the other hand, while in a multi-cloud environment, BDR is perfect for both environments since it has the mechanism to support consistency resolution without terminating the replication. Allowing you to have continuous replication streams between your active primaries or just your primary, if the other target node is for read, data retrieval or secondary and data recovery purposes.

Consistency Issues & Conflict Resolution

Basically, the core logical replication that is available in native PostgreSQL is not true bi-directional replication. You can use CREATE PUBLICATION and CREATE SUBSCRIPTION if you want to implement a chained or ring topology simulating master-master setup.

Leveraging the native logical replication simulating a master-master setup requires that you have at least PostgreSQL 10. However, if you expect that you will gain a true master-master setup, then you will be sorely disappointed. The built-in logical replication allows you to implement, as mentioned earlier, with the use of PUBLICATION/SUBSCRIPTION methods, whilst it can be a problem when it comes to handling and managing primary, unique keys, and constraints. It lacks the mechanism of the following:

• Conflict resolution
• DDL replication
• Global sequences
• Multi-master support

With logical replication, when you are dealing with CREATE TABLEs, you have to make sure that the table also exists on the other target node, or the subscriber node. In addition to that, since there’s no DDL support, it can be a struggle if you implement a multi-master setup allowing both primaries to accept writes as there’s no global sequences support, meaning you might have issues with using sequential keys in your table such as auto-increment columns. If such duplicate keys are detected, replication shall be terminated until you fix the problem. There’s no conflict resolution which can be tedious if your database encounters a consistency problem.

Whilst, with Bi-Directional Replication (Postgresql BDR), things get smoother. You simply assure you have set up your nodes properly by running setup commands. For example, nodes 192.168.40.50 and 192.168.40.51 will do a master-master setup,

PGPASSWORD='bdrPassw0rd' /usr/lib/edb-pge/17/bin/pgd node db1 setup   
--dsn 'host=192.168.40.50 dbname=postgres user=bdruser password=bdrPassw0rd'  
--pgdata /var/lib/edb-pge/17/main   --log-file /var/lib/edb-pge/17/pgd_log_db1.log  
--group-name pgd_group

PGPASSWORD='bdrPassw0rd' /usr/lib/edb-pge/17/bin/pgd node db2 setup 
    --dsn 'host=192.168.40.51 dbname=postgres user=bdruser password=bdrPassw0rd' 
	--cluster-dsn 'host=192.168.40.50 dbname=postgres user=bdruser password=bdrPassw0rd' 
	--group-name pgd_group  
	--pgdata /var/lib/edb-pge/17/main 
	--log-file /var/lib/edb-pge/17/pgd_log_db2.log

Once these two nodes are set up perfectly, creating the tables, i.e. issuing a DDL statement, is straightforward as you just have to run it in one of the primary nodes and it will be replicated. In case it detects duplicate keys, replication is not terminated and a new transaction will be processed next and executed and replicated once it runs without errors.

If your budget is tight, BDR is free and open-source until v2; otherwise, your option is to implement it via logical replication or pglogical. pglogical is the best choice as it handles conflict resolution better. 

It offers this pglogical.conflict_resolution allowing you to set the resolution method for any detected conflicts between local data and incoming changes. This parameter has possible values you can use to set which are error, apply_remote, keep_local, last_update_wins, first_update_wins. 

In most setups, default value points to error, which means it will have to stop on error once conflict is detected and requires manual action to resolve the problem. Ideally, using last_update_wins can be your desired value which means that the version of data with the latest commit timestamp will be kept.

Complimenting BDR with load balancing

Bi-Directional Replication alone does not offer you full high-availability and load balancing. Load balancing ensures that your traffic load is efficient, while high availability ensures the health and availability of your database in case one of your database nodes goes down, or even your load balancer nodes.

A sample diagram below would assure that you have full availability of your nodes while also ensuring that performance is horizontally balanced between your active-primary nodes.

In this topology, the complementary capabilities include:

• Actively distributing both read and write load,
• Maintaining availability during node or network failures,
• Reducing conflict risks via intelligent routing,
• Maximizing efficiency through connection pooling.

Manual vs. ClusterControl-supported BDR: Pros & Cons

Manual BDR setup

Successfully implementing production-grade PG BDR environments meshed with high availability and load balancing requires deep understanding and high-level skills. Cost-wise, there are options you can take since PostgreSQL is purely an open-source database technology; pglogical can be a best option to set this up. There are limitations that you must be aware of but for a non-complex setup, pglogical can be enough for your multi-master setup for implementing bi-directional replication. However, it does not provide advanced features that make administering complex environments easy, like BDR’s conflict/transform trigger, which allows you to attach triggers for incoming changes to your records/rows in your database. It offers column strategy which you can set, for example:

SELECT bdr.bdr_set_conflict_resolver(
  set_name := 'default',
  conflict_type := 'update_update',
  per_column := '{"total_gross":"sum", "last_txn":"last_update_wins", "notes":"keep_local"}'
);

Going through a manual setup offers you freedom and avoids vendor lock-in. Depending on your documentation and implementation of your setup, as long as you provide the ground layer of your implementation, it will provide transparency and can set your custom requirements especially if you need complex setup amid the performance and optimization benefits that you can get.

But with all things, you have to consider the big picture, especially as your database grows complex and data storage becomes very challenging to scale and manage. Operational complexity and pressure can grow tremendously especially when disaster occurs and data recovery is required. Manual setup can be very challenging as doing things that you might not need, might eventually require the need of expert management that other third-party tools offer.

Lastly, with manual setup, it can be tedious to monitor the health of your database cluster. You might need third party tools to give you graphic-based metrics which would make it easier for you to determine common issues and pitfalls of your database. You also need alarms to throw when certain thresholds are met and this can be challenging and costly; because you need to hire devs or build your own tools to monitor and provide observability ambiance that third-party tools have integrated and can provide it for your convenience.

ClusterControl for PG BDR operations

ClusterControl offers PostgreSQL deployment using streaming replication and logical replication. A sample screenshot of dashboards of the streaming and logical replication deployments that is readily available for ClusterControl management, is shown below:

For logical replication deployments, using PUBLICATION/SUBSCRIPTION approach for implementing a multi-master deployment using Enterprise DB,, is shown below:

For Enterprise DB, make sure you have your EDB Token available as this shall be required during deployment through ClusterControl’s GUI.

For enterprise-grade environments, ClusterControl is tailored to do its job and provide the users sustainability and comfort when handling and managing their complex database clusters for PostgreSQL. Not limited to deployment, it offers backup management, disaster recovery support with automatic recovery option, observability with comprehensive metrics to offer. It has built-in alarms and alerts when certain thresholds are met allowing you to avoid such disaster before it shall happen.

This observability feature makes an ideal option for your environment as managing a complex database cluster can be tedious and you are looking for convenience and offers you technical support in case you need some technical advice and analysis for your environment and requirements as well. If you are looking for a BDR setup, ClusterControl supports deployment for the Enterprise DB (EDB) version of PostgreSQL.

Although it offers minimal support for its enterprise offering that EDB has, this means it allows you to set up on your own and do manual work on the ground. This might not be beneficial if you are looking for more management of complex features that BDR can offer that allows automatic setup for you or GUI-relevant support, but the ability to provide you the needs and wants that you are looking for such enterprise software, Severalnines’ ClusterControl is built on that and is tailored to that concept and principles that shall be beneficial for your enterprise-grade requirements.

Operational best practices

Learning the fundamentals of logical replication, terminology, and how to fix conflict resolution is highly advisable. PostgreSQL technology especially with these BDR, pglogical, and it native logical replication is not easy to deal with. It requires a high-level of understanding and how databases should work. If you are an experienced DBA, learning and operating PostgreSQL and its native replication and other third-party offerings such as BDR, Bucardo, pglogical, Slony, Spock, can still be tricky but eventually you will be able to manage these technologies integrated to your setup for implementing a multi-master or bi-directional replication. 

Managing it for multi-tenant and multi-cloud setups requires that you at least need tools that are built to handle conflict resolutions, advanced features that support triggers and column strategies, verbose log-level, database partitioning, and load balancing; you don’t need to focus on implementing this from the ground up. Leverage third-party tools that are already available, and if cost is an issue, there are open-source technologies that are readily available to cater your needs.

Conclusion

Using enterprise-grade technologies for managing enterprise-level databases requires an enterprise layer. Nowadays, these principles are symbiotic and tightly coupled. ClusterControl’s enterprise level database management offers you the freedom to implement them where you would like, whether it’s in the cloud or on-prem, while giving you features deeply coupled to your needs when implementing logical replication either using Postgres community version or Enterprise DB for your database clusters.

With multi-tenant and multi-cloud setups, the manual approach and using community-based technologies can meet your initial needs. However, once it grows drastically, you will need deep understanding and experience with managing complex scenarios that the tool can handle. ClusterControl is designed to address these at a high-level enterprise layer.

Ready to make PostgreSQL management easier and more reliable in any environment?

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

With your install script ready, run the command below. Replace S9S_CMON_PASSWORD and S9S_ROOT_PASSWORD placeholders with your choice password, or remove the environment variables from the command to interactively set the passwords. If you have multiple network interface cards, assign one IP address for the HOST variable in the command using HOST=<ip_address>.

S9S_CMON_PASSWORD=<your_password> S9S_ROOT_PASSWORD=<your_password> HOST=<ip_address> ./install-cc # as root or sudo user

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.

Other Installation Options

Helm Chart

Deploy ClusterControl on Kubernetes using our official Helm chart.

Ansible Role

Automate installation and configuration using our Ansible playbooks.

Puppet Module

Manage your ClusterControl deployment with the Puppet module.

ClusterControl on Marketplaces

Prefer to launch ClusterControl directly from the cloud? It’s available on these platforms:

Google Cloud Platform

DigitalOcean Marketplace

gridscale.io Marketplace

Vultr Marketplace

Linode Marketplace

The post Multi-tenant, multi-cloud logical and bi-directional replication deep dive appeared first on Severalnines.

The post Multi-tenant, multi-cloud logical and bi-directional replication deep dive appeared first on MariaDB.org.

Continue reading “DBeaver, a solid alternative to MySQL Workbench that works like a charm with MariaDB”

The post DBeaver, a solid alternative to MySQL Workbench that works like a charm with MariaDB appeared first on MariaDB.org.

The post DBeaver, a solid alternative to MySQL Workbench that works like a charm with MariaDB appeared first on MariaDB.org.

Continue reading “The Queen of Naboo and Many Systems: MariaDB Ecosystem Hub”

The post The Queen of Naboo and Many Systems: MariaDB Ecosystem Hub appeared first on MariaDB.org.

The post The Queen of Naboo and Many Systems: MariaDB Ecosystem Hub appeared first on MariaDB.org.

There are several identity and access management (IAM) solutions available today that enable Single Sign-On (SSO) using OAuth 2.0 and OpenID Connect. In an earlier blog by my colleague Zsolt, OIDC in PostgreSQL: With Keycloak, he demonstrated how PostgreSQL 18 can be integrated with Keycloak using pg_oidc_validator. In this post, we explore the same concept using Ping Identity (PingOne).

Ping Identity is widely used in enterprise environments for identity and access management. If you are in such an environment, integrating PostgreSQL directly with Ping Identity can provide access control.

This blog is intended for PostgreSQL users, DBAs and customers who want to evaluate or deploy OIDC authentication using pg_oidc_validator. The goal is to provide a practical step-by-step walk-through to get a working setup.

We will cover the following topics as part of this blog:

• Setting up PingOne environment
• Install PostgreSQL 18 from Packages
• Configure PostgreSQL for OAuth/OIDC authentication
• Test login using an OIDC flow

Setting up PingOne environment

1. Register a new account

   

2. Fill in the required details to complete the profile

   

3. The next step is to sign in to your Ping Identity account

   

4. Upon successful Sign-in, we will see Ping Identity Administrator Console. In the left navigation panel, click on Environments -> Environments + (marked in red).

   

5. Provide an environment name and click on Finish

   

6. Once the environment is created, click on Manage environment.

   

7. In the left navigation panel, click on Applications -> Applications -> click on Applications +. Fill the application name, select the application type as Device Authorization and click on Save.

   

8. Upon successful creation, we will see generated Client ID and Issuer ID. The Issuer ID can be copied from under the Connection Details section. Enable the toggle so that the application is Active.

   

9. Once the application is successfully created, we need to add a client scope. In the left navigation panel, click on Applications -> Resources -> OpenID Connect. You will see a section called Scopes under which there is a + Add Scope button.

   

10. Upon clicking the Add scope button, we need to fill the Scope name and click on Save. In our example, we are creating a scope called pgscope

    

11. Now, let’s assign the custom scope we created to our client application. In the left navigation panel, click on Applications -> Applications. Select the application postgres which we created previously and click on Resource Access

    

12. From the list of available scopes, select the custom scope we created and click on Save. This will assign the scope to our application.

    

13. The next step is to add a new user. In the left navigation panel, click on Directory -> Users and click on Users + sign.

    Fill the username field. For our exercise, we are creating a user called employees. In some identity providers (IdPs), it is possible to customize tokens and control how certain claims (including the sub – subject claim) are generated or mapped. However, it is important to note that while Ping Identity allows customization of the ID token, the access token claims (including sub) for the default OpenID Connect resource cannot be customized. The value of sub in the access token is generated and managed internally by PingOne and cannot be altered, mapped, or derived from another attribute (such as email or username). As a result, PostgreSQL must be configured to work with the sub value exactly as issued in the access token by PingOne.

    

14. Enable the user by turning the toggle “ON” and set a password.

    

Install PostgreSQL 18 from Packages

Since OAuth support is only available starting with PostgreSQL 18, we need a PostgreSQL server of at least this version.In the guide, we will install PostgreSQL 18 using Percona’s official packages.

Note:

1. Ensure that the percona-release is already installed and configured on your system. You can refer to the official Percona documentation for setup instructions.

2. For this exercise, the steps are demonstrated on Ubuntu 24.04

Enable the PostgreSQL 18 repository

sudo percona-release enable-only ppg-18.2 release
sudo apt update

Install PostgreSQL 18

sudo apt install -y percona-postgresql-18

Install OAuth Support for libpq

sudo apt install libpq-oauth

Configure PostgreSQL for OAuth/OIDC authentication

Install pg_oidc_validator

sudo apt install percona-pg-oidc-validator18

Setting Up a Sample Use Case for OIDC-Based Access

Imagine the following use case:

• A company regularly generates promotional discount codes and stores them in a table called dcode inside a database named promo
• A new discount code is generated and added to this table every day.
• The company wants all employees to be able to access the latest code whenever needed.
• For simplicity in this demonstration, employees retrieve the code by connecting to the database and querying the table directly.
• To avoid managing individual database accounts for every employee, access is not tied to separate user credentials.
• Instead, authentication to the database is handled through the company’s SSO system, allowing employees to connect using their existing corporate identity.

---
config:
theme: neutral
---
architecture-beta
group company_network(cloud)[Company Network]
service employee(user)[Employee] in company_network
service sso(server)[Company SSO PingIdentity] in company_network
service promo_db(database)[Promo Database] in company_network
service code_gen(server)[Daily Code Generator] in company_network
code_gen:B --> T:promo_db
employee:R --> L:sso
sso:R --> L:promo_db

Let’s connect to PostgreSQL:

sudo -u postgres psql

Create a database:

CREATE DATABASE promo;
c promo

Add a table to store discount codes:

CREATE TABLE dcode (code varchar(10), GENERATED_AT TIMESTAMP default now());
INSERT INTO dcode (code) VALUES ('SAVENOW');

Create the access user:

CREATE ROLE employees WITH LOGIN;
GRANT SELECT ON dcode TO employees;

Configure OAuth access in PostgreSQL:

In order for users to connect using OAuth and authenticate through the Ping Identity server, we need to create an identity map and add an entry for such access on PostgreSQL’s authentication configuration file.

Edit the identity mapping configuration file and add an entry, which we will call oidc, mapping connections originated by a system user identified with a sub ID. For this exercise, we allow any string to match

sudo vim /etc/postgresql/18/main/pg_ident.conf

# MAPNAME SYSTEM-USERNAME DATABASE-USERNAME
oidc /^(.*)$ employees

Next, edit the authentication configuration file. The configuration file pg_hba.conf acts as a sort of firewall for connections. With the below line, we are instructing PostgreSQL to allow all connections coming from any network that attempt to access the database promo as user employees using the new authentication method oauth. We are also indicating that the authentication provider is Ping Identity and the scope is openid.

sudo vim /etc/postgresql/18/main/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD
host promo employees 0.0.0.0/0 oauth issuer=https://auth.pingone.com.au/64935f69-5a0a-4b69-a8bd-46967d218303/as scope=pgscope map=oidc

Note:

1. Place this entry after the existing local rules and just before the replication rules in pg_hba.conf. PostgreSQL evaluates pg_hba.conf from top to bottom, and the first matching rule is applied. Putting the OAuth rule earlier ensures that connections to database promo as user employees use OAuth instead of falling back to password authentication.

2. In production environments, restrict the IP range instead of using 0.0.0.0/0

3. The oauth_issuer must exactly match the Issuer ID from your PingOne environment. The Issuer URL is unique to each PingOne environment and contains your environment UUID. It typically follows this format: https://auth.pingone.com.au//as. Replace with the actual value from your PingOne environment. Do not copy the placeholder value directly.

Enabling the pg_oidc_validation extension

Edit the postgresql.conf and add below lines

sudo vim /etc/postgresql/18/main/postgresql.conf

oauth_validator_libraries = 'pg_oidc_validator'

The validator uses the sub claim from the access token by default. Hence, we need not explicitly set pg_oidc_validator.authn_field=sub. The sub claim is defined by the OpenID Connect specification as a stable and unique identifier for a user within an identity provider (IdP). It is intended to uniquely represent a user and remain consistent across authentication sessions.

PostgreSQL does not interpret or transform this value. The validator extracts the configured claim and PostgreSQL compares it against a database role or an entry in pg_ident.conf. If the value does not match the expected role or mapping, authentication will fail, even if the token itself is valid.

In this setup with Ping Identity, only the sub claim can be used for authentication with the default OpenID Connect resource.

Reload the configuration

sudo -u postgres psql
SELECT pg_reload_conf();

Monitor the server logs

sudo tail -f /var/log/postgresql/postgresql-18-main.log

Test login using an OIDC flow

Connecting to the database:

For this quick connection test, we use psql to connect to the promo database as the employees user, explicitly specifying the host IP along with the oauth_issuer and client_id.

psql 'host=127.0.0.1 user=employees dbname=promo oauth_issuer=https://auth.pingone.com.au/64935f69-5a0a-4b69-a8bd-46967d218303/as oauth_client_id=1e892f71-09d7-4ed6-a534-0dc888d39c7c'

By connecting via the host IP address rather than the local socket, PostgreSQL treats this as a host-based connection, ensuring that the OAuth configuration in pg_hba.conf is applied. The authentication is handled by PostgreSQL’s authentication framework, which uses OIDC with Ping Identity as the identity provider to validate the token.

We will see a prompt on the console with the URL and activation code.You will notice an activation code XXXX-XXX. Example shown below:

Visit https://auth.pingone.com.au/64935f69-5a0a-4b69-a8bd-46967d218303/device and enter the code: 7KK7-88DK

Upon clicking the URL, it will prompt you to log in with the employee’s user, which we created during the PingOne environment setup.

Next, it will prompt you to enter the activation code.

Approve access for the application, and that’s it! The user has now been successfully authenticated via OIDC.

Return to the PostgreSQL prompt and you should see that the login to the promo database is successful. You can now query the dcode table to fetch the discount code.

psql 'host=127.0.0.1 user=employees dbname=promo oauth_issuer=https://auth.pingone.com.au/64935f69-5a0a-4b69-a8bd-46967d218303/as oauth_client_id=1e892f71-09d7-4ed6-a534-0dc888d39c7c'
Visit https://auth.pingone.com.au/64935f69-5a0a-4b69-a8bd-46967d218303/device and enter the code: 7KK7-88DK
psql (18.2 - Percona Server for PostgreSQL 18.2.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

promo=> select * from dcode;
 code | generated_at
---------+----------------------------
 SAVENOW | 2026-02-13 09:40:13.109801
(1 row)

With the steps shown in this guide, we now have a working end-to-end setup using OIDC authentication and device flow login. From here, the same model can be extended to real-world enterprise environments with tighter network restrictions and role mapping.

If you run into issues while setting up pg_oidc_validator or integrating PostgreSQL with Ping Identity, check the community forums first, chances are someone in the community may already have encountered a similar issue. If not, feel free to open a discussion or raise a request for help.

The post PostgreSQL 18 OIDC Authentication with Ping Identity using pg_oidc_validator appeared first on MariaDB.org.

The post What Exactly Is the MySQL Ecosystem?  appeared first on MariaDB.org.

The post The “bus factor” risk in MongoDB, MariaDB, Redis, MySQL, PostgreSQL, and SQLite appeared first on MariaDB.org.

Continue reading “What the MariaDB Community Wants Next: A Look at Our MySQL-Compatibility Poll”

The post What the MariaDB Community Wants Next: A Look at Our MySQL-Compatibility Poll appeared first on MariaDB.org.

The post What the MariaDB Community Wants Next: A Look at Our MySQL-Compatibility Poll appeared first on MariaDB.org.

Continue reading “Galera, continuity, and responsibility: how the Foundation and MariaDB plc move forward”

The post Galera, continuity, and responsibility: how the Foundation and MariaDB plc move forward appeared first on MariaDB.org.

The post Galera, continuity, and responsibility: how the Foundation and MariaDB plc move forward appeared first on MariaDB.org.

Introduction

MySQL runs just about everywhere. I’ve seen it behind small personal projects, internal tools, SaaS platforms, and large enterprise systems handling serious transaction volume. When your database sits at the center of everything, it becomes part of your security perimeter whether you planned it that way or not. And that makes it a target.

Securing MySQL isn’t about flipping one magical setting and calling it done. It’s about layers. Tight access control. Encrypted connections. Clear visibility into what’s happening on the server. And operational discipline that doesn’t drift over time.

In this guide, I’m going to walk through practical MySQL security best practices that you can apply right away. These are the kinds of checks and hardening steps that reduce real risk in real environments, and help build a database platform that stays resilient under pressure.

1. Principle of Least Privilege

One of the most common security mistakes is over-granting privileges.
Applications and users should have only the permissions they absolutely
need.

Bad Practice

GRANT ALL PRIVILEGES ON *.* TO 'appuser'@'10.%';

Better Approach

GRANT SELECT, INSERT, UPDATE ON appdb.* TO 'appuser'@'10.%';

Recommendations

• Avoid global privileges unless absolutely required
• Restrict users by host whenever possible
• Separate admin accounts from application accounts
• Use different credentials for read-only vs write operations

Audit Existing Privileges

SELECT user, host, Select_priv, Insert_priv, Update_priv, Delete_priv
FROM mysql.user;

2. Strong Authentication & Password Policies

Weak credentials remain one of the easiest attack vectors.

Enable Password Validation

component_validate_password is MySQL’s modern password policy engine. Think of it as a gatekeeper for credential quality. Every time someone tries to set or change a password, it checks whether that password meets your defined security standards before letting it in.

It replaces the older validate_password plugin with a component-based architecture that is more flexible and better aligned with MySQL 8.x design.

INSTALL COMPONENT 'file://component_validate_password';

What It Does

When enabled, it enforces rules such as:

• Minimum password length
• Required mix of character types
• Dictionary file checks
• Strength scoring

If a password fails policy, the statement is rejected before the credential is stored.

Why It Matters

Weak passwords remain one of the most common entry points in database breaches. This component reduces risk by enforcing baseline credential hygiene automatically, instead of relying on developer discipline.

Recommended Policies

• Minimum length: 14+ characters
• Require mixed case, numbers, and symbols
• Enable dictionary checks
• Enable username checks

Remove Anonymous Accounts

Find Anonymous Users

Anonymous users have an empty User field.

SELECT user, host FROM mysql.user WHERE user='';

If you see rows returned, those are anonymous accounts.

Drop Anonymous Users

In modern MySQL versions:

DROP USER ''@'localhost';
DROP USER ''@'%';

Adjust the Host value based on what your query returned.

Why This Matters

Anonymous users:

• Allow login without credentials
• May have default privileges in some distributions
• Increase the attack surface unnecessarily

In hardened environments, there should be zero accounts with an empty username. Every identity should be explicit, accountable, and least-privileged.

3. Encryption Everywhere

Encryption protects data both in transit and at rest.

Enable Transparent Data Encryption (TDE)

See my January 13 post for a deep dive into Transparent Data Encryption:
Configuring the Component Keyring in Percona Server and PXC 8.4

Enable TLS for Connections

require_secure_transport=ON

Verify SSL Usage

SHOW STATUS LIKE 'Ssl_cipher';

Encryption Areas to Consider

• Client-server connections
• Replication channels
• Backups and snapshot storage
• Disk-level encryption

4. Patch Management & Version Hygiene

Running outdated MySQL versions is equivalent to leaving known
vulnerabilities exposed.

Maintenance Strategy

• Track vendor security advisories
• Apply minor updates regularly
• Test patches in staging before production rollout
• Avoid unsupported MySQL versions

Check Version

SELECT VERSION();

5. Logging, Auditing, and Monitoring

Security without visibility is blind defense, enable Audit Logging.

1. audit_log Plugin (Legacy Model)

Installation

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Verify

SHOW PLUGINS LIKE 'audit%';

2. audit_log_filter Component (Modern Model)

Introduced in MySQL 8 to provide a more flexible and granular alternative to the older plugin model.

Installation

INSTALL COMPONENT 'file://component_audit_log_filter';

Verify

SELECT * FROM mysql.component;

Architecture Difference

Instead of a single global policy, you create:

• Filters (define what to log)
• Users assigned to filters

It’s granular and rule-driven.

Auditing Key Events

• Failed logins
• Privilege changes
• Schema modifications
• Unusual query activity

References:

1. Audit Log Filter Component
   
2. Audit Log Filters Part II
   

Useful Metrics

SHOW GLOBAL STATUS LIKE 'Aborted_connects';
SHOW GLOBAL STATUS LIKE 'Connections';

6. Secure Configuration Hardening

A secure baseline configuration reduces risk from common attack
patterns.

Recommended Settings

local_infile=OFF
secure_file_priv=/var/lib/mysql-files
sql_mode="STRICT_ALL_TABLES"
secure-log-path=/var/log/mysql

Why These Matter

• Prevent arbitrary file imports
• Reduce filesystem abuse
• Restrict data export/import locations

7. Backup Security

Backups often contain everything an attacker wants.

Backup Best Practices

• Encrypt backups
• Restrict filesystem permissions
• Store offsite copies securely
• Rotate backup credentials
• Verify restore procedures regularly

Example Permission Check

ls -l /backup/mysql

8. Replication & Cluster Security

Replication is not just a data distribution feature. It is a persistent, privileged communication channel between servers. If misconfigured, it can become a lateral movement pathway inside your infrastructure. Treat every replication link as a trusted but tightly controlled corridor.

Principle: Replication Is a Privileged Service Account

Replication users require elevated capabilities. They must be isolated, tightly scoped, and monitored like any other service identity.

Secure Replication Users

CREATE USER 'repl'@'10.%'
 IDENTIFIED BY 'strongpassword'
 REQUIRE SSL;

GRANT REPLICATION REPLICA ON *.* TO 'repl'@'10.%';

Hardening considerations:

• Restrict host patterns as narrowly as possible. Avoid % whenever feasible.
• Require SSL or X.509 certificate authentication.
• Enforce strong password policies or use a secrets manager.
• Disable interactive login capability if applicable.

Encrypt Replication Traffic

Replication traffic may include sensitive row data, DDL statements, and metadata. Always encrypt it.

At minimum:

• Enable require_secure_transport=ON
• Configure TLS certificates on source and replica
• Set replication channel to use SSL:

CHANGE REPLICATION SOURCE TO
 SOURCE_SSL=1,
 SOURCE_SSL_CA='/path/ca.pem',
 SOURCE_SSL_CERT='/path/client-cert.pem',
 SOURCE_SSL_KEY='/path/client-key.pem';

For MySQL Group Replication or InnoDB Cluster:

• Enable group communication SSL
• Validate certificate identity
• Use dedicated replication networks

Binary Log and Relay Log Protection

Replication relies on binary logs. Protect them.

• Set binlog_encryption=ON
• Set relay_log_info_repository=TABLE
• Restrict filesystem access to log directories
• Monitor log retention policies

Compromised binary logs can reveal historical data changes.

9. Continuous Security Reviews

Security is not a one-time checklist. Regular audits help catch
configuration drift and evolving threats.

Suggested Review Cadence

• Weekly: failed login review
• Monthly: privilege audits
• Quarterly: configuration review
• Semiannually: full security assessment

Security Checklist Summary

Final Thoughts

Strong MySQL security doesn’t come from one feature or one tool. It comes from layers working together. Hardened configuration. Tight, intentional privilege design. Encryption everywhere it makes sense. And monitoring that actually gets reviewed instead of just written to disk.

In my experience, the strongest environments aren’t the ones trying to be unbreakable. They’re the ones built to detect, contain, and respond. Every layer should either reduce blast radius or increase visibility. If an attacker gets through one control, the next one slows them down. And while they’re slowing down, your logging and monitoring should already be telling you something isn’t right.

That’s what a mature security posture looks like in practice.

The post Hardening MySQL: Practical Security Strategies for DBAs appeared first on MariaDB.org.

Source

The post MariaDB Community Server 12.3 Will Include Galera Cluster appeared first on MariaDB.org.

MariaDB plc’s decision to pull Galera, its synchronous multi-master replication solution, out of the open-source ecosystem in MariaDB 12.3 LTS has been quietly shaking the extended MariaDB community lately.

Galera has been a critical component for high-availability MariaDB deployments, enabling robust scaling and fault tolerance. But with MariaDB’s acquisition of Codership and the sudden shift of Galera to a commercial license, the message to the community is clear: the rules have changed. For organizations that migrated from MySQL to MariaDB in search of stability, openness, and innovation, this move raises serious questions.

MariaDB plc frames this decision as a necessary step to accelerate innovation and deliver greater value to customers. By integrating Galera directly into its Enterprise Platform, the company aims to streamline development, reduce feature delays, and offer a more cohesive high-availability solution. This aligns with MariaDB’s broader push to strengthen its enterprise offerings, particularly following its transition to private ownership and the arrival of a new CEO focused on profitability.

However, the acquisition also serves a more strategic purpose: preventing future forks of Galera by third parties. By owning the codebase, MariaDB now controls the technology’s evolution and distribution, effectively eliminating competition from community-driven alternatives. While this may secure revenue streams, it risks alienating the very community that has been MariaDB’s strongest advocate.

The parallels with HashiCorp’s controversial shift to a Business Source License for Vault are striking. Both companies have taken widely used open-source tools and placed them behind commercial barriers. Yet while HashiCorp provided clear migration paths, MariaDB’s approach feels abrupt.

Also the timing particularly damaging. MariaDB Foundation is making pushes to present MariaDB as the natural continuation of MySQL amidst growing concerns about Oracle’s neglect of MySQL. And in parallel we have MariaDB plc behaving just like Oracle with a very popular piece of tech. This comes in direct conflict with the Foundation claims giving MariaDB’s detractors just the perfect ammunition to kill the narrative even before it can spread out.
Beside this already disastrous timing, it can only erode the trust in MariaDB’s future. To put it bluntly, stating that open source does not equate to “free for everyone in perpetuity” as the CEO of MariaDB plc did during a MariaDB Foudnation board meeting is mostly telling about an absence of commitment to openness. Furthermore this argument used to explain close-sourcing Galera and Maxscale could very well be also applied the server itself. C*O won’t like this potential for instability. I dont like it either. And i really think this will drive many potential and actual clients away from MariaDB. It’s litteraly a godsend for the likes of Percona, VillageSQL and mostly PostgreSQL. As if the latter needed any help in this very moment …

One a personal level, it seems i underestimated the greed of the corporation when i expressed myself about the Codership acquisition. Maybe i was too naïve indeed despite having a usually rather cynical nature when it comes down to business.

Now the question is : how will the Foundation handle this ? We have elements of answers in the minutes of their board meeting 1/2026. I totally concur with option 2 as well. Will it take the form of a formal community-driven fork of galera 4 ? Would it take a port of Galera to VillageSQL ? Will it be Percona driven ? I can’t tell yet, but actions will speak louder than words as always.

The post SQLSTATE [HY000] [2006] Galera has gone away appeared first on MariaDB.org.

In this post, I will outline the operational blueprint for migrating high-volume analytical workloads from legacy database systems (like MySQL) to ClickHouse. The guide covers the critical steps from pre-migration assessment and schema redesign to execution, validation, and establishing post-migration operational excellence to achieve a stable and superior analytical environment capable of sub-second query execution on massive datasets.

Common circumstances teams who migrate to ClickHouse experience

• When dashboards and queries become too slow (seconds to minutes on tens or hundreds of millions of rows), blocking product requirements or user experience.
• When data volume and concurrency explode (billions to trillions of rows, petabyte‑scale logs, high QPS) and existing systems like Postgres or Elasticsearch can’t scale efficiently. 
• When architectures become too complex, with multiple stores (Postgres, Redis, DynamoDB, Elasticsearch, etc.) and heavy ETL just to serve analytics. 

Operational reasons why teams migrate to ClickHouse

• To get much faster analytics at scale: sub‑second or millisecond queries on billions of rows, often 5 –10× + faster than previous systems.
• To reduce cost: better compression and hardware efficiency lead to significantly lower storage and infrastructure costs (e.g., 4 –10× less storage, ~30%+ hardware savings vs Elasticsearch).
• To separate OLTP and OLAP cleanly, keeping Postgres or other transactional DBs for writes while using ClickHouse for heavy analytics via CDC or pipelines.
• To simplify architecture and operations, consolidating multiple systems into ClickHouse and reducing ETL, maintenance, and operational risk.
• To enable richer, real‑time analytics experiences for customers — interactive dashboards, observability, security analytics, and AI/ML workloads on fresh data.

Migrating to a high-performance system like ClickHouse is a strategic move. It’s more than a technical swap; it’s a fundamental data infrastructure shift that offers significant analytical benefits alongside necessary operational risk management.

To really understand why this shift is so important, we have to look at the practical value it brings. While the migration might seem complex, the benefits you’ll unlock at the end make the initial effort and investment more than worth it.

Operational and business benefits ClickHouse delivers post-migration

Successfully navigating the migration process unlocks a host of powerful benefits that justify the initial investment and risk.

1. Accelerated Analytical Query Performance: ClickHouse’s architecture enables fast, low-latency analytical queries on large datasets for real-time reporting, quick dashboard updates, and interactive data exploration.
2. Scalability and Cost Efficiency: The new system provides superior horizontal scalability, handling petabytes and trillions of rows at lower cost per query than legacy data warehouses. This supports future business growth without prohibitive infrastructure costs and allows retention of finer-grained historical data for deeper analysis.
3. Enhanced Data Granularity and Depth: Better performance and lower storage costs encourage keeping detailed, raw event data instead of pre-aggregated tables. This gives data scientists and analysts access to the raw truth, enabling more precise modeling, root cause analysis, and the discovery of subtle trends.
4. Simplified Data Architecture: Consolidating high-volume analytical workloads onto a single, purpose-built platform streamlines the data stack, reducing maintenance overhead and specialized support needs, while accelerating time-to-insight.

Key operational risks to be mitigated

While the new system offers superior performance, the transition phase and the initial operational period present several key challenges that must be proactively mitigated:

1. Data Integrity and Consistency Risk: The primary concern during migration is ensuring that all data is transferred accurately and that the new system maintains consistency with the old source.
2. Downtime and Service Interruption: The cutover from the legacy system to ClickHouse must be handled with minimal disruption to ongoing business intelligence and production services.
3. Learning Curve and Skill Gap: Operating and optimizing a specialized system like ClickHouse requires a different set of skills from the existing team, particularly concerning its column-oriented architecture and SQL dialect.
4. Integration Complexity: Ensuring seamless integration with existing upstream data sources (e.g., Kafka, object storage) and downstream analytical tools (e.g., BI platforms) is critical.

This case study will outline a direct data migration procedure, specifically moving data from a legacy database system, MySQL, to ClickHouse.

Before we do migration from legacy database systems to Clickhouse, it is required to begin with the Pre-migration assessment. This foundational phase starts with defining the scope, objectives, success criteria, and high-level timeline. 

Following this, a comprehensive inventory is undertaken to gain a clear understanding of the existing system through analysis of the following:

• Workloads: Different kinds of analytical tasks, such as ad-hoc analysis, reporting, or real-time dashboards.
• Data Size: The current amount of data stored and how much is expected in the future.
• Ingest Rates: How fast and how often new data is written to the system.
• Query Patterns: The most common, demanding, and important queries run against the data, crucial for meeting performance guarantees.

The core purpose of the inventory is identifying candidate data sets. This process includes:

• Evaluating schemas for ClickHouse compatibility and optimization.
• Prioritizing high-volume, analytical data for maximum performance gain.
• Defining exclusion criteria for unsuitable data (e.g., legacy, low-value, write-heavy transactional data).

Finally, the assessment addresses on-prem environment implications for non-cloud migrations, including:

• Hardware Sizing and Procurement (compute, memory, fast storage).
• Network Topology optimization for high-speed traffic.
• Security and Compliance integration (auth, encryption).
• Operational Readiness planning (monitoring, backup, DR, IT automation).

MySQL to ClickHouse migration planning and execution

A successful ClickHouse migration, exemplified by moving from MySQL, requires a critical, five-stage plan to minimize risk, downtime, and ensure performance: initial assessment, detailed planning, execution, rigorous testing, and final post-migration validation and optimization.

First, we must plan the data migration from MySQL to ClickHouse. There are several methods for this migration, which we will outline below.

A robust data migration pipeline is the backbone of any successful ClickHouse migration. The design should accommodate both historical data migration and continuous data synchronization. 

Crucial tasks to remember for successful migration

• Identify data sources (MySQL primary, replicas, read-only nodes)
• Define data freshness requirements (near-real-time vs batch)
• Separate historical backfill from live ingestion paths
• To prevent disruption during data migration, no Data Definition Language (DDL) activities should be executed on the source database.

Common migration approaches

• Initial backfill
  • Bulk export from MySQL (mysqldump, SELECT INTO OUTFILE)
  • Batch ingestion using clickhouse-client, clickhouse-local, or object storage (CSV/Parquet)
• Ongoing ingestion / CDC
  • Binlog-based CDC tools (Debezium, Maxwell, custom CDC)
  • Stream-based pipelines (Kafka → ClickHouse)
• Cut-over strategy
  • Dual-write or CDC-based sync during transition
  • Gradual read migration from MySQL to ClickHouse
  • Final cut-over once data parity and query validation are confirmed
• Using third-party tools
  • Third party tools like mysql_ch_replicator, Blade, and etc.

Before data migration, harmonize MySQL and ClickHouse schema design and modeling. ClickHouse’s columnar, OLAP nature necessitates a schema optimized for analytical performance and data compression; a direct “lift-and-shift” from relational MySQL will lead to suboptimal results.

Key MySQL to ClickHouse concept conversions

• Columnar Orientation vs. Row-Based:
  • MySQL: Optimized for transactional, row-level operations (OLTP).
  • ClickHouse: Optimized for scanning large volumes of data across columns (OLAP). The goal is to minimize the number of columns read per query.
• Denormalization:
  • MySQL: Highly normalized structure, relying heavily on JOINs for query execution.
  • ClickHouse: Strongly favors denormalization (flattening data, embedding dimension fields directly into the fact table). While ClickHouse supports JOINs, they are often less performant than in row-based systems, especially on huge datasets. Denormalization optimizes read performance by reducing I/O operations.
• Engine Selection:
  • MySQL: Primarily uses InnoDB.
  • ClickHouse: Requires careful selection of the table engine (e.g., MergeTree family) based on the workload (e.g., ReplacingMergeTree for deduplication, CollapsingMergeTree for state management, SummingMergeTree for pre-aggregation). The engine dictates data storage, replication, and query behavior.
• Primary Keys and Partitioning:
  • MySQL: Primary key is for row identification and index lookup.
  • ClickHouse: The Primary Key (ORDER BY) determines how data is physically sorted on disk, crucial for range queries and skip-index performance. The Partition Key (PARTITION BY) should align with common data retention or query filtering (e.g., by month or day).
• Data Types:
  • MySQL: Generic types (e.g., DATETIME, VARCHAR).
  • ClickHouse: Utilize specialized, compact types (e.g., Date, DateTime64, LowCardinality for strings, Decimal for precise monetary values, Array for nested data) to maximize compression and query speed. For example, replacing a high-cardinality VARCHAR with String is common, but replacing low-cardinality strings with LowCardinality(String) is a massive performance win.

Actionable ClickHouse schema modeling steps

• Identify Analytical Queries: Use the inventory from the assessment phase to determine which MySQL queries are most critical and resource-intensive.
• Redesign for Columnar: For each key analytical workload, design a denormalized target schema in ClickHouse that includes all necessary dimension fields, avoiding heavy cross-table joins.
• Define ORDER BY and PARTITION BY: Select keys that will drastically reduce the amount of data ClickHouse needs to scan (e.g., order by timestamp, partition by year_month).
• Prototype and Test: Create the new ClickHouse table structure and load a representative data sample. Run the target queries and benchmark performance against the legacy MySQL system before full migration.

Testing, performance benchmarks, and query validation

Comprehensive testing and validation are essential for a successful ClickHouse migration, ensuring functional correctness, data integrity, and, most importantly, the expected performance gains.

Key validation strategy points

• Functional testing and data integrity verification:
  • Row count and schema comparison: First, compare ClickHouse and MySQL row counts and concurrently verify table schemas (data types, columns, indexing) to quickly check for major data loss or incorrect transformation.
  • Key metric verification: Validate core business metrics (e.g., revenue, DAU, inventory) by comparing complex aggregation query results between MySQL and ClickHouse. Discrepancies signal data migration or query translation issues.
  • Comprehensive data spot checks: For granular verification, randomly sample data, focusing on edge cases. Verify nulls, timestamp accuracy, primary key uniqueness (if relevant), and special characters to ensure data fidelity and encoding.
• Performance benchmarking:
  • Query translation and optimization: MySQL queries must be translated to the ClickHouse SQL dialect, utilizing its column-oriented architecture and specialized functions for optimization. Each translated query requires a performance review.
  • A/B performance testing: To validate, a suite of representative queries — simple lookups to complex joins and aggregations, will run on both MySQL and the new ClickHouse cluster. The objective is to rigorously measure the performance difference.
    • Metrics to measure: Focus on latency (query execution time), Query Latency (p50, p95, p99 percentiles), throughput (queries per second), and resource utilization (CPU, memory, disk I/O).
    • Expected results: ClickHouse migration is only successful if its analytical query execution times are significantly (often orders of magnitude) faster than the MySQL baseline.
  • Load and concurrency testing: ClickHouse testing must verify cluster stability, resource management, and sustained low latency under simulated production loads, concurrent users, and complex simultaneous queries; isolated query performance is insufficient.
• End-to-end application validation:

The final step is Application Integration, connecting ClickHouse with downstream applications, reporting tools, and dashboards formerly using MySQL. This requires end-to-end testing to verify application logic, followed by User Acceptance Testing (UAT) with stakeholders to confirm the new system meets requirements and provides consistent, accurate data for all reporting

Executing the ClickHouse migration

With the requirements for migrating data from MySQL to ClickHouse now established, this section focuses on the migration process itself. We will utilize the third-party tool mysql_ch_replicator for this task. All necessary prerequisites must be met before proceeding. These prerequisites are also required for alternative approaches, particularly when using this specific tool.

Next to install mysql_ch_replicator, use the following command:

pip install --upgrade mysql_ch_replicator

After that we can try to migrate the data from MySQL legacy database to ClickHouse.

1. Prepare yaml config file. For an example.

# MySQL and ClickHouse credentials can be overridden using environment variables:
# MySQL: MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, MYSQL_CHARSET
# ClickHouse: CLICKHOUSE_HOST, CLICKHOUSE_PORT, CLICKHOUSE_USER, CLICKHOUSE_PASSWORD

mysql:
  host: 'localhost'
  port: 3306
  user: 'admin'
  password: ''
  charset: 'utf8mb4'  # Optional: charset for MySQL connection (default: utf8mb4). Use utf8mb4 for full Unicode support including emoji and 4-byte characters

clickhouse:
  host: 'localhost'
  port: 8323
  user: 'default'
  password: ''

binlog_replicator:
  data_dir: '/root/mysql_ch_replicator/binlog/'
  records_per_file: 100000

databases: 'sakila'
tables: '*'

2. To begin replication, you can use nohup to run the process in the background and enable logging. Since the data is being migrated continuously, we will initiate the process using the run_all option.

nohup mysql_ch_replicator --config config.yaml run_all

3. Then you can just monitor the replication process.

N.B.

• The mysql_ch_replicator tool, a community-developed utility, is currently unable to replicate data directly from a MySQL source to a ClickHouse cluster.

• If your migration involves a ClickHouse cluster, you will need to manually create the destination schema using the ON CLUSTER option first, or you could explore alternative methods such as Ongoing ingestion / CDC (Change Data Capture).

Post-ClickHouse migration operational go-live readiness

Successful ClickHouse deployment needs robust operational readiness, beyond migration, to handle production traffic reliably. This foundation requires a solid backup strategy, effective resource governance, comprehensive observability, a prepared team, and defined rollback paths. A strong operational footing minimizes cut-over risks and builds confidence in ClickHouse as a critical production system.

Monitoring and observability

Comprehensive observability is essential for a properly functioning ClickHouse cluster, providing deep insights across three critical dimensions: query, storage, and cluster levels. This holistic view is necessary for diagnosing bottlenecks, capacity planning, and ensuring high availability.

1. Query level

Focuses on workload for performance tuning and capacity planning:

• Query performance: Track execution time, rows processed, and memory usage to identify heavy or inefficient queries.
• Query load/concurrency: Monitor active/pending queries and concurrency (QPS) to signal scaling needs.
• Failures/errors: Log and alert on failed queries for quick issue identification (e.g., malformed SQL, resource exhaustion).
• User/source tracking: Identify users/applications submitting demanding queries for context on resource allocation.

2. Storage level

Ensures data integrity and optimal read/write performance:

• Disk I/O: Monitor latency, throughput, and I/O wait times across all volumes.
• Disk space: Track utilization and growth rate for data and temporary directories; alert proactively on low space.
• Merge operations: Observe the rate and efficiency of background data consolidation to prevent query degradation.
• Replication/consistency: Monitor replica lag to ensure synchronized data across nodes for durability and load balancing.

3. Cluster level

Covers overall health and resource management of the distributed system:

• System resources: Track CPU utilization, memory (RAM/swap), and network traffic for all nodes to guide scaling or load redistribution.
• ClickHouse status: Monitor server health, uptime, thread count, and internal metrics (memory pools, cache ratios).
• Distributed table health: Ensure inter-node communication is healthy and all sharded components are available.
• Configuration/version: Track running config and software versions for consistency and simplified debugging.

Robust monitoring across these three dimensions transforms the approach from reactive checks to proactive performance and stability assurance for ClickHouse.

Key areas to monitor:

• Query latency (p95 / p99)
• Query concurrency and queue depth
• CPU usage per shard and replica
• Disk IO wait and merge activity
• Replication lag and ZooKeeper / Keeper health
• Disk free space per volume and partition

Key metrics sources:

• system.metrics, system.asynchronous_metrics
• system.query_log, system.part_log
• system.replication_queue
• Keeper metrics (Raft state, leader election)

Backup & recovery readiness

Setting up a backup solution is not enough; rigorous testing is crucial. Regularly and thoroughly testing backups builds confidence that data can be successfully restored after a disaster, ensuring business continuity and data integrity.

Backup considerations:

• Full backups of MergeTree data (filesystem or object storage)
• Metadata backups (DDL, users, quotas, settings)
• Keeper metadata consistency
• Backup verification and restore drills

Operational checklist:

• Can you restore a single table?
• Can you restore a shard independently?
• Can you recover from a lost replica?
• Is backup performance acceptable under load?

Regularly integrating and monitoring backup procedures is essential to promptly detect and address failures, ensuring the integrity and recoverability of your ClickHouse data.

Resource management & capacity planning

ClickHouse is excellent for analytical tasks because it is efficient and fast. Nevertheless, its performance ceiling is determined by the physical hardware such as the CPU, memory, and storage I/O speed. Consequently, optimizing and scaling the infrastructure is essential to fully exploit ClickHouse’s capabilities.

Key areas to validate before go-live:

• CPU headroom during peak query windows
• Disk write amplification from merges
• Memory usage under concurrent workloads
• Network throughput for distributed queries

Controls to validate:

• Query limits (max_memory_usage, max_threads)
• User and workload isolation
• Merge throttling and background pool sizing
• Shard balance and data distribution

When performing capacity planning, it is crucial to anticipate and account for future growth. Simply basing the plan on the existing or current load will likely lead to resource shortages down the line.

Staffing, training and operational ownership

Operational readiness requires more than stable systems; it critically involves the managing personnel. A comprehensive approach must incorporate team training, process documentation, and clear communication to ensure staff are skilled, informed, and prepared for any situation.

Ensure that:

• On-call engineers understand ClickHouse internals
• Runbooks exist for common incidents (replication lag, disk pressure, slow queries)
• Ownership is clear between DB, infra, and application teams
• Escalation paths are defined

Training should focus on:

• Reading system tables
• Understanding MergeTree behavior
• Debugging distributed queries
• Safe operational actions (detaches, restarts, resyncs)

Failback / rollback plan

A comprehensive, well-prepared rollback plan is mandatory before any system or feature launch. This vital pre-go-live step ensures preparedness, minimizes downtime and data loss, and serves as crucial due diligence for risk mitigation.

Typical rollback models:

• Dual-write period (MySQL + ClickHouse) if a dual-write strategy is chosen during data migration.
• Read-only fallback to legacy system
• Snapshot-based recovery
• Feature-flag based traffic routing 
• Batch ingestion using clickhouse-client, clickhouse-local, or object storage (CSV/Parquet)

Critical questions:

• How long can rollback remain viable?
• What data is lost if rollback occurs?
• How do you reconcile post-rollback data?

Rollback plans must be constrained by a set timeframe for their completion. It is also crucial that these plans are practiced regularly to ensure effectiveness.

Hybrid deployment readiness

Deployments that span hybrid or multiple sites necessitate more rigorous verification steps. These additional checks are crucial to ensure successful operation across the distributed environment.

Connectivity readiness:

• Latency and packet loss between sites
• Stable routing (VPN / VPC peering)
• MTU alignment to avoid fragmentation

Storage tiering considerations:

• Hot vs cold data placement
• Object storage latency impact on queries
• Local SSD usage for active partitions
• Clear TTL and data movement policies

Hybrid deployments necessitate more robust observability capabilities. Furthermore, they require enhanced failure isolation mechanisms to ensure system stability.

Post-Migration ClickHouse operations

Once ClickHouse is live in production, operational focus shifts from migration execution to long-term stability, performance, and cost efficiency. Post-migration operations ensure that data remains well-organized, queries continue to perform as expected under evolving workloads, and operational risks are detected early. These activities are continuous and should be treated as part of standard database operations rather than one-time tasks.

Maintenance: Compaction, pruning, data retention

ClickHouse relies on background processes to maintain data layout and performance.

Key maintenance areas:

• MergeTree compaction (merges):
  • Monitor merge queue length and merge throughput
  • Avoid sustained merge backlogs that increase query latency
• Partition pruning:
  • Ensure partitioning keys align with query patterns (e.g. time-based)
  • Regularly review unused or oversized partitions
• Data retention & TTLs:
  • Apply TTL policies for automatic data deletion or movement to colder storage
  • Validate TTL execution does not overload background threads

The goal is to keep data sets lean, query-friendly, and aligned with business retention requirements without manual intervention.

Performance tuning and query optimisation

Post-migration workloads often differ from initial benchmarks.

Ongoing tuning areas:

• Review slow queries using system.query_log
• Validate use of primary keys, skipping indexes, and projections
• Identify inefficient distributed queries (excessive shard fan-out)
• Adjust:
  • max_threads, max_memory_usage
  • Join algorithms and aggregation strategies
• Periodically re-benchmark after schema or workload changes

Performance tuning in ClickHouse is iterative and driven by real usage patterns, not static configuration.

Monitoring and cost management (especially cloud)

Increased dataset sizes and query frequency necessitate a corresponding increase in system resources, directly impacting operational costs. Therefore, assessing the current system capacity and performance is essential for planning necessary upgrades and managing the associated budgetary requirements.

Monitoring focus areas:

• CPU and memory utilization per node
• Disk usage growth rate and merge IO
• Query concurrency and peak load windows
• Replication lag and background task saturation

Cost management considerations (cloud or hybrid):

• Detect over-provisioned shards or replicas
• Use TTLs and tiered storage to reduce hot data footprint
• Monitor egress and cross-region query costs
• Align scaling decisions with actual query demand

Keeping an eye on your operations is really important if you want to stop costs from slowly getting out of hand over time.

Integration with existing DB ops stack after ClickHouse migration

After migration, ClickHouse should not operate in isolation. To maintain operational consistency and reduce cognitive overhead for support and operations teams, ClickHouse must be integrated into the existing database operations stack. This ensures that monitoring, alerting, incident response, and capacity planning continue to follow familiar workflows while extending visibility to the new analytics platform.

First we need to Identify Existing Operational Touchpoints. Start by mapping how databases are currently operated. The goal is to integrate ClickHouse into these existing paths rather than creating parallel processes. There are typical touchpoints that we need to aware :

• Monitoring and alerting systems
• Log aggregation and query auditing
• Backup and recovery tooling
• Configuration management
• Incident response and on-call workflows

After we know the touchpoints, you can start integrating the Clickhouse Metrics and Logs. Basically ClickHouse exposes rich internal telemetry via system tables so you can feed into the same observability platforms already used existing dashboard

• Export metrics from system.metrics and system.asynchronous_metrics
• Capture query behavior from system.query_log
• Track replication health via system.replication_queue
• Monitor disk and merge activity from system.parts and system.part_log

Apart from that, we also need to standardize the alerting system. ClickHouse alerts should align with existing alert semantics to avoid ClickHouse-only alert silos and Clickhouse’s critical metrics itself. Don’t forget to ensure that all alerts should be actionable, mapped to clear runbooks and routed through existing escalations paths.

Examples of critical ClickHouse metrics:

• Replication lag exceeds SLA
• Query latency p95 above baseline
• Merge backlog growing unexpectedly.

You also need to unify backup and recovery operations. Backup operations must follow consistent patterns across databases. You need to be understanding and aware about this because this is really important and this reduces operational risk during incidents..

Integration checklist:

• Schedule ClickHouse backups alongside other DB backups
• Centralize backup status reporting
• Perform restore tests using the same operational playbooks
• Document ClickHouse-specific restore nuances (replicas, shards, Keeper)

A monitoring dashboard for ClickHouse must be enabled and configured to leverage the exposed metrics. Ideally, you should integrate ClickHouse into your existing monitoring system to ensure it is viewed as part of the wider data platform, not an isolated analytics component. Implementing cross-database dashboards will facilitate the identification of systemic issues, enabling better resource planning and expediting root-cause analysis.

Useful dashboard include:

• Ingestion rate vs query rate across systems
• Disk growth trends per platform
• Peak usage windows and contention
• Cost and resource efficiency comparisons

The integration process should be concluded by updating all relevant operational documentation. This includes revising and disseminating the necessary runbooks to reflect the final configuration.

• Define ownership for ClickHouse incidents
• Add ClickHouse scenarios to on-call runbooks
• Train teams on common failure patterns
• Ensure consistent terminology across databases

Establishing a comprehensive checklist and a clear timeline is a crucial final step after the migration process to ensure smooth, anticipated operations. Post-data migration to ClickHouse, a brief stabilization period is necessary before officially declaring the migration complete. This stabilization phase is vital for validating data accuracy, optimizing performance, and ensuring the support team is fully prepared operationally. The following week-by-week table provides a recommended operational timeline.

Each phase is planned to take approximately one week. This checklist is crucial for ensuring the migration results in a stable and well-operated ClickHouse environment, effectively mitigating the risks associated with a ‘big bang’ go-live approach.

Conclusion

Migration to ClickHouse is a strategic move, driven by the inability of existing OLTP or general analytics systems (like MySQL) to handle massive data volumes (billions/trillions of rows) and high-concurrency analytical queries efficiently. The core goals are achieving significantly faster, sub-second analytics at scale, reducing infrastructure costs through better compression, and simplifying the architecture with a specialized OLAP engine. However, this transition requires a structured approach to manage risks concerning data integrity, downtime, and team training. Successful migration requires a pre-migration assessment (inventory, data size, query patterns) and careful planning and execution.

Essential steps include implementing a robust data pipeline for historical backfill and ongoing CDC, and critically redesigning the schema for ClickHouse’s columnar structure. Schema redesign involves denormalization, selecting MergeTree engines, and defining optimal ORDER BY and PARTITION BY keys for performance. The execution phase requires rigorous testing and validation, including data consistency checks, A/B performance benchmarking, and load testing before final cut-over. Lastly, don’t forget that operational go-live readiness and subsequent post-migration operations are critical for sustained success.

Interested in streamlined ClickHouse operations? We will be adding ClickHouse support to ClusterControl soon. In the meantime, visit our ClickHouse page to see what we’ll support in its initial release and help influence its roadmap by submitting info about your use case.

The post Operational guide to migrating to ClickHouse appeared first on Severalnines.

The post Operational guide to migrating to ClickHouse appeared first on MariaDB.org.

Source

The post Introducing MariaDB Enterprise Cluster on MariaDB Cloud appeared first on MariaDB.org.

(if you are not interested in Caching and Flushing, you can jump directly to the section about Release Candidate)

The post More than Flushing (also Caching) for innodb_flush_method, and Missing Release Candidate appeared first on MariaDB.org.

Continue reading “The Rising Tide of Community Contributions to MariaDB Server”

The post The Rising Tide of Community Contributions to MariaDB Server appeared first on MariaDB.org.

The post The Rising Tide of Community Contributions to MariaDB Server appeared first on MariaDB.org.

You can find us at Booth 790. This is a great chance to talk with engineers working on Percona Operators.

We will be there to discuss:

• Running MySQL, PostgreSQL, and MongoDB on Kubernetes
• Production-ready HA setups
• Backup and PITR strategies
• Multi-cluster and multi-region deployments
• Operators roadmap and upcoming features
• Real-world troubleshooting stories

If you’re running Percona Operators in production (or just getting started), we’d love to hear your feedback and learn about your challenges.

If you’re just curious (or even suspicious) about running databases on Kubernetes, we’d love to talk and answer your questions.

Admission Tickets – 20% Off for Our Community

We have a 20% discount code available for Percona community members.
If you’re planning to attend and don’t have a ticket yet, drop a comment or message us and we’ll share the details.

Schedule a Meeting

Want dedicated time with our engineers?
Drop a comment here or reach out directly. We’re happy to schedule a meeting during the event.

See you in Amsterdam!

The post Meet Percona at KubeCon + CloudNativeCon Europe 2026 appeared first on MariaDB.org.

I loved the presentation, especially since a similar procedure is what we recommend our users as well. Unfortunately it’s not for everyone: it’s applicable ONLY when you can stop DDL operations on your database cluster for some time. As you can imagine that’s not a case for every deployment.

This limitation got me into some very engaging discussions during my fav part of any conference, the so called “hallway track”. Networking and discussions after the talks and on the conference corridors are why I like going to such events. I’ve heard some stories of nasty surprises coming out of the unexpected re-indexing after an upgrade.

It made me wonder, how many professionals have moved to PostgreSQL from other areas of expertise and are lacking information about the index rebuilds after upgrades may be necessary. How often are DevOps engineers or SREs, neither fluent in PostgreSQL nor experienced with databases, tasked with maintaining database infrastructure?

In the meantime I figured out that a short coffee time read is what I want to aim at. No super deep dives, rather food for thought and inspiring more of those engaging discussions I like so much about the conferences.

So let’s get to it. This week I want to go through some basic facts before diving into more challenging topics in the coming weeks.

What are collations

In general, a collation defines how values are ordered and compared. In databases, it most commonly applies to text. It’s often not a trivial thing to determine how strings are sorted, whether two values are considered equal, and how things like upper vs lower case or special characters are treated. Just like alphabetical order helps us organize words in everyday life, collations define the rules the database uses when comparing and sorting character data. This allows us to sort structures like strings. Numbers are even simpler.

A good visualization of how significant collations are is when you try to imagine determining whether one item is before another if they come from different alphabets. Where do you position country specific characters in such a sorting order?

Lets look at a set of example data:

Bob
Anna
Zoë
Álvaro

When using an English-like collation the sorting would look like this as Á is treated like A . This is visible in Collation 1:

Anna
Álvaro
Bob
Zoë

Though with a collation changed so that Á is sorted separately before A . This is visible in Collation 2:

Álvaro
Anna
Bob
Zoë

It’s clearly visible that collation defines how text is sorted and compared.

What are indexes

If you’re not a database professional, you may not be familiar with what an index is. Think of it as of a structure that speeds up the search. In an old school library you had to check the index of authors to find the book you’ve been looking for. Similarly in databases when knowing what the data is going to be searched for, we introduce indexes.

A common use of indexes is to enforce uniqueness of values. Primary keys and unique constraints automatically create unique indexes to ensure that no duplicate values are inserted. In addition, users can create their own indexes to support specific query patterns. Indexes can be single or multi column, depending on the particular use cases and to help speed up specific queries.
Let’s look at an example of physically unsorted data stored in heap

Users (unsorted data)
--------------------
[1] Zoë
[2] Álvaro
[3] Anna
[4] Bob

with an Index on name

Index (B-tree on name)
----------------------
Anna -> [3]
Bob -> [4]
Zoë -> [1]
Álvaro -> [2]

The index stores values in sorted order and points to their location in the table. Scanning through the list in order is fine for small number of items, as indexes get larger, there are opportunities to optimize this process. The most commonly used optimisation, very simplified, is to split the list and create a pointer which stores the maximum value in the left half of the list and the minimum value in the right half. As the lists get too large again, they are split again, creating a tree of these pointers.

Why re-indexing is needed

If the rules for comparing the strings are different at query time from what they were when the index was created, the search may fail in various ways. If the scan encounters a value which, by the rules at query time, is higher in the sort order than the value being searched for, it will conclude that the value being searched for is not in the list. Similarly, the search may follow a pointer to the wrong list of values. To make this more concrete, let’s look at a real world example from a glibc change in 2019. Before the change, a list of our values was sorted as follows

aa
a a
a-a
a+a

after the change, the list was sorted as follows:

a a
a+a
a-a
aa

If an index was built under the first set of rules and searched under the second, a search for the value ‘a a’ will fail, because the first item in the index (‘aa’) is higher in the sort order than the value being searched for. Failing to find a value which is in the list can cause issues with application behavior – like missing records or records which appear in some queries (when the index is not used) but not in others (where the index is used). It may allow the insertion of values that should now be considered equal under the new collation rules, effectively violating uniqueness expectations.

Since PostgreSQL 10, the server tracks the collation version used when an index was built and can detect when it no longer matches the system’s current collation version, which is why reindexing may suddenly become mandatory after an upgrade.

However, PostgreSQL server does not analyze your actual data to determine whether the collation change affects your stored values. It only detects that the collation provider version has changed. In some cases, this means reindexing is required even though the effective sort order of your specific data has not changed. Because PostgreSQL cannot reliably determine whether your specific dataset is affected, it treats the situation as potentially unsafe.

When do collations change?

There’s a number of scenarios when collations change:

• OS / glibc upgrade (Linux) – PostgreSQL relies on the system glibc provided collations so if a version changes, the collation rules may change as well. The example above is taken from the upgrade to glibc 2.28 which is one PostgreSQL Community remembers due to the ripple effect it caused.
• ICU library upgrade (for ICU collations) – if PostgreSQL deployment uses ICU collations, upgrading ICU library will affect these. While they are not tied to glibc these changes also happen.
• Major PostgreSQL upgrade (in some cases) – if the new version uses a different collation provider behavior or updated ICU integration
• Database restored on a system with different collation versions – logical dump/restore onto a host with different glibc / ICU versions can invalidate indexes.

When re-indexing is necessary

Looking at the above provided list an observation is quite immediate, that not every collation type is affected:

• if the collation is not dependent on glibc / ICU then it won’t be affected. As such C/POSIX collations are immune to such issues.
• Same truth sticks to the data types. The collation change problem affects only those indexes which are the text or character based. All other datatypes like all types of integers and floating points, date, timestamp,  geometric data types or even vector data remain unaffected.

What’s also important is that even if a collation changed it’s not necessary it will affect a given index. Think of it this way, if your database does not use any language specific characters, chances are that collation change will not require re-index. Unfortunately you don’t know that until you look inside your data.
In controlled environments, teams may assess the risk before scheduling reindexing, but from PostgreSQL’s perspective the index must be considered potentially inconsistent until rebuilt.

What’s next?

Next week we’ll look at what is the reality of the DBA team regarding upgrades

The post PostgreSQL coffee break: version upgrade related reindexing – reasons appeared first on MariaDB.org.

tl;dr

• Vector search recall vs precision in MariaDB 12.3 is better than in MariaDB 11.8
• Vector search recall vs precision in Maria 11.8 is better than in Postgres 18.2 with pgvector 0.8.1
• The improvements in MariaDB 12.3 are more significant for larger datasets
• MariaDB 12.3 has the best results because it use less CPU per query

Benchmark

Results: dbpedia-openai-500k-angular

Summary

• MariaDB 12.3 has the best results
• the difference between MariaDB 12.3 and 11.8 is larger here than above for 100k

Results: dbpedia-openai-1000k-angular

Summary

• MariaDB 12.3 has the best results
• the difference between MariaDB 12.3 and 11.8 is larger here than it is above for 100k and 500k

The post MariaDB innovation: vector index performance appeared first on MariaDB.org.

This isn’t theoretical. I use this architecture daily, handling AI agent memory across multiple projects. Here’s the schema and query patterns that actually work.

The Architecture

AI agents need two types of memory:

• Structured memory – What happened, when, why (MySQL)
• Pattern memory – What connects to what (Neo4j)

Vector databases are for similarity search. They’re not for tracking workflow state or decision history. For that, you need ACID transactions and proper relationships.

The MySQL Schema

Here’s the actual schema for AI agent persistent memory:

-- Architecture decisions the AI made
CREATE TABLE architecture_decisions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    decision TEXT NOT NULL,
    rationale TEXT,
    alternatives_considered TEXT,
    status ENUM('accepted', 'rejected', 'pending') DEFAULT 'accepted',
    decided_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    tags JSON,
    INDEX idx_project_date (project_id, decided_at),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- Code patterns the AI learned
CREATE TABLE code_patterns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    category VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    code_example TEXT,
    language VARCHAR(50),
    confidence_score FLOAT DEFAULT 0.5,
    usage_count INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_category (project_id, category),
    INDEX idx_confidence (confidence_score)
) ENGINE=InnoDB;

-- Work session tracking
CREATE TABLE work_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(255) UNIQUE NOT NULL,
    project_id INT NOT NULL,
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    ended_at DATETIME,
    summary TEXT,
    context JSON,
    INDEX idx_project_session (project_id, started_at)
) ENGINE=InnoDB;

-- Pitfalls to avoid (learned from mistakes)
CREATE TABLE pitfalls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    category VARCHAR(50),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    how_to_avoid TEXT,
    severity ENUM('critical', 'high', 'medium', 'low'),
    encountered_count INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_severity (project_id, severity)
) ENGINE=InnoDB;

Foreign keys. Check constraints. Proper indexing. This is what relational databases are good at.

Query Patterns

Here’s how you actually query this for AI agent memory:

-- Get recent decisions for context
SELECT title, decision, rationale, decided_at
FROM architecture_decisions
WHERE project_id = ?
  AND decided_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY decided_at DESC
LIMIT 10;

-- Find high-confidence patterns
SELECT category, name, description, code_example
FROM code_patterns
WHERE project_id = ?
  AND confidence_score >= 0.80
ORDER BY usage_count DESC, confidence_score DESC
LIMIT 20;

-- Check for known pitfalls before implementing
SELECT title, description, how_to_avoid
FROM pitfalls
WHERE project_id = ?
  AND category = ?
  AND severity IN ('critical', 'high')
ORDER BY encountered_count DESC;

-- Track session context across interactions
SELECT context
FROM work_sessions
WHERE session_id = ?
ORDER BY started_at DESC
LIMIT 1;

These are straightforward SQL queries. EXPLAIN shows index usage exactly where expected. No surprises.

The Neo4j Layer

MySQL handles the structured data. Neo4j handles the relationships:

// Create nodes for decisions
CREATE (d:Decision {
  id: 'dec_123',
  title: 'Use FastAPI',
  project_id: 1,
  embedding: [0.23, -0.45, ...]  // Vector for similarity
})

// Create relationships
CREATE (d1:Decision {id: 'dec_123', title: 'Use FastAPI'})
CREATE (d2:Decision {id: 'dec_45', title: 'Used Flask before'})
CREATE (d1)-[:SIMILAR_TO {score: 0.85}]->(d2)
CREATE (d1)-[:CONTRADICTS]->(d3:Decision {title: 'Avoid frameworks'})

// Query: Find similar past decisions
MATCH (current:Decision {id: $decision_id})
MATCH (current)-[r:SIMILAR_TO]-(similar:Decision)
WHERE r.score > 0.80
RETURN similar.title, r.score
ORDER BY r.score DESC

// Query: What outcomes followed this pattern?
MATCH (d:Decision)-[:LEADS_TO]->(o:Outcome)
WHERE d.title CONTAINS 'Redis'
RETURN d.title, o.type, o.success_rate

How They Work Together

The flow looks like this:

1. AI agent generates content or makes a decision
2. Store structured data in MySQL (what, when, why, full context)
3. Generate embedding, store in Neo4j with relationships to similar items
4. Next session: Neo4j finds relevant similar decisions
5. MySQL provides the full details of those decisions

MySQL is the source of truth. Neo4j is the pattern finder.

Why Not Just Vector Databases?

I’ve seen teams try to build AI agent memory with just Pinecone or Weaviate. It doesn’t work well because:

Vector DBs are good for:

• Finding documents similar to a query
• Semantic search (RAG)
• “Things like this”

Vector DBs are bad for:

• “What did we decide on March 15th?”
• “Show me decisions that led to outages”
• “What’s the current status of this workflow?”
• “Which patterns have confidence > 0.8 AND usage_count > 10?”

Those queries need structured filtering, joins, and transactions. That’s relational database territory.

MCP and the Future

The Model Context Protocol (MCP) is standardizing how AI systems handle context. Early MCP implementations are discovering what we already knew: you need both structured storage and graph relationships.

MySQL handles the MCP “resources” and “tools” catalog. Neo4j handles the “relationships” between context items. Vector embeddings are just one piece of the puzzle.

Production Notes

Current system running this architecture:

• MySQL 8.0, 48 tables, ~2GB data
• Neo4j Community, ~50k nodes, ~200k relationships
• Query latency: MySQL <10ms, Neo4j <50ms
• Backup: Standard mysqldump + neo4j-admin dump
• Monitoring: Same Percona tools I’ve used for years

The operational complexity is low because these are mature databases with well-understood operational patterns.

Too Much Work? Let AI Build It For You

Look, I get it. This is a lot of schema to set up, a lot of queries to write, a lot of moving parts.

Here’s the thing: you don’t have to type it all yourself. Copy the schema above, paste it into Claude Code or Kimi CLI, and tell it what you want to build. The AI will generate the Python code, the connection handling, the query patterns – all of it.

If you want to understand what’s happening under the hood, start here:

Building a Simple MCP Server in Python

Then let your AI tool do the heavy lifting. That’s literally what I did. The schema is mine, the architecture decisions are mine, but the implementation?
Claude wrote most of it while I watched and corrected.

Use the tools. That’s what they’re for.

When to Use What

Start with MySQL for state. Add Neo4j when you need pattern recognition. Only add vector DBs if you’re actually doing semantic document retrieval.

Summary

AI agents need persistent memory. Not just embeddings in a vector database – structured, relational, temporal memory with pattern recognition.

MySQL handles the structured state. Neo4j handles the graph relationships. Together they provide what vector databases alone cannot.

Don’t abandon relational databases for AI workloads. Use the right tool for each job, which is using both together.

For more on the AI agent perspective on this architecture, see the companion post on 3k1o.

The post MySQL + Neo4j for AI Workloads: Why Relational Databases Still Matter appeared first on MariaDB.org.

The AI hype is based on the assumption that the frontier AI labs are producing better and better foundational models at an accelerating pace. Is that really true, or are people just in sort of a mass psychosis because AI models have become so good at mimicking human behavior that we unconsciously attribute increasing intelligence to them? I decided to conduct a mini-benchmark of my own to find out if the latest and greatest AI models are actually really good or not.

The problem with benchmarks

Every time any team releases a new LLM, they boast how well it performs on various industry benchmarks such as Humanity’s Last Exam, SWE-Bench and Ai2 ARC or ARC-AGI. An overall leaderboard can be viewed at LLM-stats. This incentivizes teams to optimize for specific benchmarks, which might make them excel on specific tasks while general abilities degrade. Also, the older a benchmark dataset is, the more online material there is discussing the questions and best answers, which in turn increases the chances of newer models trained on more recent web content scoring better.

Thus I prefer looking at real-time leaderboards such as the LM Arena leaderboard (or OpenCompass for Chinese models that might be missing from LM Arena). However, even though the LM Arena Elo score is rated by humans in real-time, the benchmark can still be played. For example, Meta reportedly used a special chat-optimized model instead of the actual Llama 4 model when getting scored on the LM Arena.

Therefore I trust my own first-hand experience more than the benchmarks for gaining intuition. Intuition however is not a compelling argument in discussions on whether or not new flagship AI models have plateaued. Thus, I decided to devise my own mini-benchmark so that no model could have possibly seen it in its training data or be specifically optimized for it in any way.

My mini-benchmark

I crafted 6 questions based on my own experience using various LLMs for several years and having developed some intuition about what kinds of questions LLMs typically struggle with.

I conducted the benchmark using the OpenRouter.ai chat playroom with the following state-of-the-art models:

• Claude Opus 4.6 (Anthropic)
• GPT-5.2 (OpenAI)
• Grok 4.1 (xAI)
• Gemini 3.1 Pro Preview (Google)
• GLM 5 (Z.ai)
• MinMax M2.5 (MinMax)
• Qwen3.5 Plus 2026-02-15 (Alibaba)
• Kimi K2.5 (Moonshot.ai)

OpenRouter.ai is great as it very easy to get responses from multiple models in parallel to a single question. Also it allows to turn off web search to force the models to answer purely based on their embedded knowledge.

Common for all the test questions is that they are fairly straightforward and have a clear answer, yet the answer isn’t common knowledge or statistically the most obvious one, and instead requires a bit of reasoning to get correct.

Some of these questions are also based on myself witnessing a flagship model failing miserably to answer it.

1. Which cities have hosted the Olympics more than just once?

This question requires accounting for both summer and winter Olympics, and for Olympics hosted across multiple cities.

The variance in responses comes from if the model understands that Beijing should be counted as it has hosted both summer and winter Olympics. Interestingly GPT was the only model to not mention Beijing at all. Some variance also comes from how models account for co-hosted Olympics. For example Cortina should be counted as having hosted the Olympics twice, in 1956 and 2026, but only Claude, Gemini and Kimi pointed this out. Stockholm’s 1956 hosting of the equestrian games during the Melbourne Olympics is a special case, which GPT, Gemini and Kimi pointed out in a side note. Some models seem to have old training material, and for example Grok assumes the current year is 2024. All models that accounted for awarded future Olympics (e.g. Los Angeles 2028) marked them clearly as upcoming.

Overall I would judge that only GPT and MinMax gave incomplete answers, while all other models replied as the best humans could reasonably have.

2. If EUR/USD continues to slide to 1.5 by mid-2026, what is the likely effect on BMW’s stock price by end of 2026?

This question requires mapping the currency exchange rate to historic value, dodging the misleading word “slide”, and reasoning on where the revenue of a company comes from and how a weaker US dollar affects it in multiple ways. I’ve frequently witnessed flagship models get it wrong how interest rates and exchange rates work. Apparently the binary choice between “up” or “down” is somehow challenging to the internal statistical model in the LLMs on a topic where there are a lot of training material that talk about both things being likely to happen, and choosing between them requires specifically reasoning about the scenario at hand and disregarding general knowledge of the situation.

However, this time all the models concluded correctly that a weak dollar would have a negative overall effect on the BMW stock price. Gemini, GLM, Qwen and Kimi also mention the potential hedging effect of BMW’s X-series production in South Carolina for worldwide export.

3. What is the Unicode code point for the traffic cone emoji?

This was the first question where the the flagship models clearly still struggle in 2026. The trap here is that there is no traffic cone emoji, so an advanced model should simply refuse to give any Unicode numbers at all. Most LLMs however have an urge to give some answer, leading to hallucinations. Also, as the answer has a graphical element to it, the LLM might not understand how the emoji “looks” in ways that would be obvious to a human, and thus many models claim the construction sign emoji is a traffic cone, which it is not.

By far the worst response was from GPT, that simply hallucinates and stops there:

While Gemini and Grok were among the three models not falling into this trap, the response from Claude was exemplary good:

4. Which languages are the 10 most commonly spoken in the world? For each language, count from 1 to 5.

This seemingly simple question can become quite complex when you account for the fact that other languages might also have their own character system, both for words and the numerals (e.g. Arabic واحد/one vs ١/1), and that numbers might have variations depending on what the thing being counted is (e.g in French object gender un/une), or whether plain numbers and occurrences have their own words (e.g. Russian один/odin vs раз/raz). I’ve also witnessed older models frequently mix up on what parts of the reply should be using what language.

For a human this question is trivial, assuming they know the language. For LLMs it is surprisingly challenging. Grok and MinMax only gave a list of the most popular languages and stopped there without showing any words in the languages. Qwen got a bit further by trying to list numbers in each language, but the lists were only the numbers 1 to 5:

I have seen Grok do something similar before, which in fact inspired me to include this question in my mini-benchmark.

GPT got a bit further, but for Hindi, Arabic and Bengali it listed the numerals in local script, not the number words. Gemini, GLM and Kimi gave a complete and correct answer as a list, while the absolute best answer and presentation was by Claude, that gave the table below:

Claude Opus 4.6

10 Most Commonly Spoken Languages (by Total Speakers)

#	Language	1	2	3	4	5
1	English	One	Two	Three	Four	Five
2	Mandarin Chinese	一 (yī)	二 (èr)	三 (sān)	四 (sì)	五 (wǔ)
3	Hindi	एक (ek)	दो (do)	तीन (tīn)	चार (chār)	पाँच (pā̃ch)
4	Spanish	Uno	Dos	Tres	Cuatro	Cinco
5	French	Un	Deux	Trois	Quatre	Cinq
6	Arabic	واحد (wāḥid)	اثنان (ithnān)	ثلاثة (thalātha)	أربعة (arbaʿa)	خمسة (khamsa)
7	Bengali	এক (ek)	দুই (dui)	তিন (tin)	চার (chār)	পাঁচ (pā̃ch)
8	Portuguese	Um	Dois	Três	Quatro	Cinco
9	Russian	Один (odin)	Два (dva)	Три (tri)	Четыре (chetyre)	Пять (pyat’)
10	Japanese	一 (ichi)	二 (ni)	三 (san)	四 (shi)	五 (go)

Note: Rankings are based on estimated total speakers (native + non-native). Rankings can vary depending on the source and methodology used.