Databases
PostgreSQL, MySQL, and Redis install, ops, and query tuning
Browse by topic
Databases guides19
PostgreSQL Privileges: Setting Up Read-Only and Least-Privilege Roles with GRANT, REVOKE, and DEFAULT PRIVILEGES
A role is both a user and a group; privileges stack in three layers (database → schema → table); DEFAULT PRIVILEGES covers tables that do not exist yet. Design read-only accounts and least privilege with production-ready SQL.
PostgreSQL VACUUM and autovacuum — Diagnosing and Tuning Table Bloat
Diagnose dead tuples and table bloat caused by PostgreSQL's MVCC, and tune VACUUM/autovacuum parameters. Covers pg_stat_user_tables, wraparound prevention, and alternatives to VACUUM FULL.
Redis Sentinel — High Availability and Automatic Failover Setup
Configure Redis Sentinel for master-replica monitoring, automatic failover, and service discovery. A hands-on walkthrough of quorum behavior, client integration, and operational pitfalls.
MySQL Replication: Setting Up Source-Replica (Master-Slave) HA, Diagnosing Lag, and Failing Over
A practitioner’s walkthrough of building Master-Slave (Source-Replica) high availability with MySQL/MariaDB binlog replication, covering GTID, semi-sync, lag diagnosis, and failover.
MSSQL Log Shipping — Disaster Recovery Redundancy Setup
Set up a Primary–Secondary DR configuration with SQL Server Log Shipping, and practice scheduling backup, copy, and restore jobs plus monitoring.
MSSQL Always On Availability Groups — SQL Server HA Redundancy Configuration
This guide walks through configuring Primary–Secondary redundancy with SQL Server Always On Availability Groups, including automatic failover and read-only routing.
Building a PostgreSQL Automatic Failover HA Cluster with Patroni and etcd
Configure a 3-node PostgreSQL HA cluster with Patroni and etcd, including automatic failover when the Primary fails and HAProxy-based load balancing.
PostgreSQL Streaming Replication — Primary/Replica High Availability Setup
Configure Primary–Replica high availability with PostgreSQL Streaming Replication, and learn WAL-based real-time synchronization and failover principles through hands-on practice.
Slow Query Analysis — pg_stat_statements · MySQL Slow Log
How to find slow queries with PostgreSQL pg_stat_statements, diagnose index issues using the MySQL slow query log and EXPLAIN ANALYZE, and optimize your queries.
PgBouncer Connection Pooling — PostgreSQL Performance Optimization
Covers installing and configuring PgBouncer, choosing a pooling mode (session/transaction/statement), setting up user authentication, and using monitoring queries to cut PostgreSQL connection counts and improve performance.
MySQL Basics — Installation, Users, Privileges, and Essential Queries
Covers MySQL operational basics: installation, root security hardening, creating users and databases, granting privileges, essential CRUD queries, and configuration file tuning.
SQLite Practical Guide — CLI · Schema · Backup · Python Integration
A practical rundown of using SQLite as a lightweight database: CLI usage, table design, indexes and triggers, .dump backups, and CRUD with Python’s sqlite3 module.
Getting Started with Elasticsearch — Installation, Indexes, Queries, and Connecting Kibana
A hands-on introduction to search engine fundamentals: installing Elasticsearch, creating indexes and performing document CRUD, match/term/range queries, aggregations, and connecting Kibana.
MongoDB Fundamentals in Practice — CRUD, Indexes, and Aggregation Pipelines
A practical rundown of MongoDB for practitioners: install, collection CRUD, index creation, aggregation pipelines ($match/$group/$lookup), and mongodump backups.
PostgreSQL High Availability: Setting Up Streaming Replication, Failover, and Point-in-Time Recovery with WAL
An in-depth PostgreSQL operations guide for building high availability with Streaming Replication and implementing point-in-time recovery (PITR) with WAL archiving.
Complete MySQL/MariaDB Backup and Restore Guide
Covers logical backup and restore with mysqldump, automation scripts, and binary-log point-in-time recovery.
Redis Fundamentals in Practice — Caching, Sessions, and Message Queues
A practitioner-focused guide covering Redis from installation through String, Hash, List, and Sorted Set data structures, plus caching strategies and TTL.
PostgreSQL Index Optimization — Diagnosing Slow Queries and Index Strategy
A practical guide to finding slow queries with EXPLAIN ANALYZE and choosing the right index types to improve performance.
PostgreSQL Installation and Basic Configuration
How to install PostgreSQL on Ubuntu, create users and databases, and configure remote access and backups.