MariaDB Trends 2025-2026: Vector Search, AI Integration, and New Features

-- Create vector table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    description TEXT,
    embedding VECTOR(384)  -- 384-dimensional vectors
);

-- Create vector index (HNSW)
CREATE INDEX idx_embedding ON products(embedding)
    USING VECTOR_HNSW WITH (nlist = 4);

-- Insert vector data
INSERT INTO products (name, description, embedding) VALUES
('Python Book', 'Learn Python programming', '[0.1, 0.2, 0.3, ...]'),
('JavaScript Guide', 'Master JavaScript', '[0.2, 0.1, 0.4, ...]');

-- Vector similarity search
SELECT id, name, 
    vector_distance_cosine(embedding, '[0.1, 0.2, 0.3, ...]') as distance
FROM products
ORDER BY distance
LIMIT 5;

-- Using vector index (faster)
SELECT id, name
FROM products
WHERE vector_search(embedding, '[0.1, 0.2, 0.3, ...]', 5);

-- JSON improvements in 11.8

-- JSON table functions
SELECT jt.*
FROM JSON_TABLE(
    '[{"a":1,"b":2},{"a":3,"b":4}]',
    '$[*]' COLUMNS (
        a INT PATH '$.a',
        b INT PATH '$.b'
    )
) AS jt;

-- JSON aggregation
SELECT JSON_ARRAYAGG(name) FROM products;
SELECT JSON_OBJECTAGG(category, COUNT(*)) FROM products GROUP BY category;

-- JSON validation
SELECT JSON_VALID('{"key": "value"}');

-- System-versioned tables
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    PERIOD FOR employment (start_date, end_date)
);

-- MariaDB 11.8: AS OF system time
SELECT * FROM employees FOR SYSTEM_TIME AS OF '2025-01-01';

-- Between two points
SELECT * FROM employees 
FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2026-01-01';

-- All historical versions
SELECT * FROM employees FOR SYSTEM_TIME ALL;

-- Benchmark results (vs 10.6):
-- - Query execution: 2.5x faster
-- - Bulk inserts: 3x faster
-- - Analytical queries: 2x faster

-- These improvements come from:
-- - Optimized query planner
-- - Better index algorithms
-- - Improved caching
-- - Parallel query execution enhancements

# MariaDB 11.8 performance settings
[mysqld]
# InnoDB improvements
innodb-buffer-pool-instances=8
innodb-flush-optimizer-elimination=ON

# Parallel query execution
thread_pool_size=32
thread_pool_max_threads=2000

# Vector search optimization
vector_buffer_size=1G

# MariaDB Cloud continues to evolve
# Managed services include:
# - MariaDB Enterprise Server
# - ColumnStore clusters
# - Smart MySQL compatibility

# Terraform provider for MariaDB Cloud
terraform {
  required_providers {
    mariadb = {
      source = "mariadb/mariadb"
    }
  }
}

resource "mariadb_server" "example" {
  provider = mariadb.cloud
  name     = "my-db"
  plan     = "Enterprise"
  region   = "us-east-1"
}

# MariaDB Operator for Kubernetes
apiVersion: mariadb.mmont.io/v1alpha1
kind: MariaDB
metadata:
  name: mariadb-cluster
spec:
  replicas: 3
  storage:
    size: 10Gi
  resources:
    limits:
      memory: 2Gi
    requests:
      memory: 1Gi

-- More window functions in 11.8

-- LEAD and LAG improvements
SELECT 
    name,
    salary,
    LAG(salary) OVER (ORDER BY salary) as prev_salary,
    salary - LAG(salary) OVER (ORDER BY salary) as diff
FROM employees;

-- Moving average
SELECT 
    date,
    sales,
    AVG(sales) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7
FROM daily_sales;

-- NTH_VALUE
SELECT 
    name,
    salary,
    NTH_VALUE(salary, 2) OVER (ORDER BY salary DESC) as second_highest
FROM employees;

-- Recursive CTE for hierarchy
WITH RECURSIVE org_chart AS (
    -- Base case
    SELECT id, name, manager_id, 1 as level
    FROM employees WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;

-- Multiple CTEs
WITH 
    active_employees AS (
        SELECT * FROM employees WHERE status = 'active'
    ),
    high_salary AS (
        SELECT * FROM active_employees WHERE salary > 70000
    )
SELECT * FROM high_salary;

-- Strong password enforcement
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

SET GLOBAL validate_password_policy='STRONG';
SET GLOBAL validate_password_length=12;
SET GLOBAL validate_password_mixed_case_count=1;
SET GLOBAL validate_password_special_char_count=1;

-- Use ed25519 authentication (faster, more secure)
INSTALL PLUGIN ed25519 SONAME 'auth_ed25519.so';

CREATE USER 'app'@'%' 
IDENTIFIED VIA ed25519 
USING 'password';

-- PAM authentication
INSTALL PLUGIN pam SONAME 'auth_pam.so';

-- Table space encryption
ALTER TABLE sensitive_data ENCRYPT='Y';

-- Binary log encryption
SET GLOBAL binlog_encryption=ON;

-- InnoDB redo log encryption
SET GLOBAL innodb_redo_log_encryption=ON;

-- Key rotation
ALTER INSTANCE ROTATE INNODB MASTER KEY;

-- Detailed query profiling
SELECT * FROM performance_schema.events_statements_history
ORDER BY END_TIME DESC LIMIT 10;

-- Wait analysis
SELECT * FROM performance_schema.events_waits_history
LIMIT 100;

-- Resource monitoring
SELECT * FROM performance_schema.memory_summary_by_thread
ORDER BY CURRENT_ALLOCATED DESC LIMIT 10;

-- MariaDB analyzer
ANALYZE TABLE employees;

-- Check table statistics
SHOW TABLE STATUS FROM mydb;

-- Index statistics
SHOW INDEX FROM employees;

-- InnoDB metrics
SHOW ENGINE INNODB STATUS;

import mariadb
from mariadb import cursors

# New cursor types in MariaDB 11.8
# Dictionary cursor
conn = mariadb.connect(cursorclass=cursors.DictCursor)
cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT * FROM users")
for row in cursor:
    print(row['username'])

# SSCursor for large result sets
conn = mariadb.connect()
cursor = conn.cursor(sscursor=True)
cursor.execute("SELECT * FROM huge_table")
for row in cursor:
    process(row)

const mariadb = require('mariadb');

// Async connection pooling
const pool = mariadb.createPool({
    host: 'localhost',
    user: 'root',
    password: 'secret',
    database: 'mydb',
    // New in 11.8
    connectionLimit: 20,
    acquireTimeout: 10000,
    // Prepared statement caching
    prepareCacheLength: 50
});

// Execute with promise
async function main() {
    let conn = await pool.getConnection();
    try {
        // Prepared statements
        let result = await conn.execute(
            "SELECT * FROM users WHERE id = ?",
            [userId]
        );
        
        // Transaction
        await conn.beginTransaction();
        await conn.execute("INSERT INTO logs (...) VALUES (...)");
        await conn.commit();
    } catch (err) {
        await conn.rollback();
        throw err;
    } finally {
        conn.release();
    }
}

# Production MariaDB 11.8 configuration
[mysqld]
# Basic
server-id=1
datadir=/var/lib/mysql

# InnoDB
innodb-buffer-pool-size=4G
innodb-log-file-size=1G
innodb-flush-log-at-trx-commit=1
innodb-file-per-table=1

# Performance
thread-pool-size=32
table-open-cache=4000
table-definition-cache=2000

# Replication
log-bin=mysql-bin
binlog-format=ROW
sync-binlog=1

# Vector search (if using AI)
vector-buffer-size=1G

# Logging
log-error=/var/log/mysql/error.log
slow-query-log=1
slow-query-log-file=/var/log/mysql/slow.log
long-query-time=1

# Security
require-secure-transport=ON

# Upgrade to MariaDB 11.8

# 1. Backup
mysqldump -u root -p --all-databases > backup.sql

# 2. Stop MariaDB
sudo systemctl stop mariadb

# 3. Upgrade packages
sudo apt update
sudo apt install mariadb-server mariadb-client

# 4. Upgrade system tables
sudo mysql_upgrade -u root -p

# 5. Restart
sudo systemctl start mariadb

# 6. Check version
mysql -u root -p -e "SELECT VERSION();"