Database & SQL Configuration Templates
Optimized PostgreSQL slow-query detectors, SQLite WAL production setups, MySQL InnoDB tuning flags, and Redis sentinel configurations.
High-Performance SQLite Production PRAGMAs
Maximum performance SQLite pragmas for WAL mode, concurrency, and memory buffers.
-- High-Performance Concurrent SQLite Configuration
-- Run immediately after opening database connection
-- 1. Enable Write-Ahead Logging (concurrent readers + writer)
PRAGMA journal_mode = WAL;
-- 2. Synchronous NORMAL is safe in WAL mode and...SQLite FTS5 Full-Text Search Schema & Sync Triggers
External content FTS5 virtual table with unicode61 tokenizer and insert, update, and delete synchronization triggers.
-- High-Performance SQLite FTS5 External Content Virtual Table
-- Synchronizes search index with the primary 'articles' table
-- 1. Create FTS5 Virtual Table
CREATE VIRTUAL TABLE IF NOT EXISTS articles_fts USING fts5(
title,
body,
...Production PgBouncer Transaction Connection Pooling Config
Battle-tested pgbouncer.ini configuration for high-traffic PostgreSQL workloads with transaction pooling and keepalive tuning.
;; Production PgBouncer Configuration
[databases]
app_db = host=127.0.0.1 port=5432 dbname=app_production auth_user=postgres
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr =...Redis In-Memory Production Cache & Eviction Config
Redis 7 configuration optimized for high-throughput cache tier with memory caps, volatile-lru eviction, and disabled persistence.
# Redis In-Memory Production Cache Tier
# Optimized for zero disk bottleneck and LRU eviction
port 6379
bind 127.0.0.1 ::1
protected-mode yes
timeout 300
tcp-keepalive 60
# Max Memory Limit & Eviction Policy
maxmemory 2gb
maxmemory-policy...PostgreSQL Slow Query Logging & auto_explain Configuration
Production postgresql.conf snippet to log queries exceeding 250ms with execution plans, buffer usage, and lock wait times.
-- PostgreSQL 16 Slow Query Logging & Auto Explain
-- Append these directives to postgresql.conf or run as ALTER SYSTEM
-- 1. Log Queries Exceeding 250ms
log_min_duration_statement = 250
-- 2. Detail Logging for Slow Statements
log_checkpoints =...MySQL 8.0 InnoDB Production Performance Tuning Configuration
Hardened my.cnf parameters for InnoDB buffer pool size, redo log capacity, thread cache, and connection limits.
# MySQL 8.0 / Percona Server Production InnoDB Tuning
# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Basic Network & Connection Settings
bind-address = 127.0.0.1
max_connections = 500
max_connect_errors = 10000
wait_timeout ...ClickHouse Single-Node Analytics Container Stack
High-performance columnar database setup with ClickHouse, Tabix web client, and persistent data volumes.
version: '3.8'
services:
clickhouse:
image: clickhouse/clickhouse-server:24.3
container_name: clickhouse-analytics
restart: unless-stopped
ports:
- "8123:8123"
- "9000:9000"
environment:
CLICKHOUSE_DB:...PostgreSQL Logical Replication & CDC WAL Settings
Optimized postgresql.conf and publication/subscription SQL statements for change data capture and replication.
-- PostgreSQL Logical Replication & Change Data Capture (CDC) Setup
-- Step 1: Ensure postgresql.conf settings are applied:
-- wal_level = logical
-- max_wal_senders = 10
-- max_replication_slots = 10
-- Step 2: On PRIMARY Publisher...