Intermediate · 11 lessons

Databases

Databases store and organize website data — user accounts, posts, products, and settings. MySQL/MariaDB powers most WordPress sites; PostgreSQL suits complex apps; Redis provides in-memory caching for speed.

Key Concepts

MySQL / MariaDB PostgreSQL Redis Cache Database Indexing Backups & Replication Connection Pooling SQL vs NoSQL phpMyAdmin

Key Takeaways

✓ Relational databases (MySQL, PostgreSQL) use tables with SQL queries.
✓ Redis caches frequently accessed data in memory for microsecond reads.
✓ Regular automated backups are non-negotiable — test restores monthly.
✓ Index columns used in WHERE/JOIN clauses to speed up slow queries.

Lessons in This Topic

Database Fundamentals for Web

Why every dynamic site needs a database.

Static HTML needs no database. CMS, e-commerce, and SaaS apps store data in DB. Relational DBs use tables with rows/columns and SQL. ACID properties ensure data integrity. Choose DB based on data structure, query patterns, and scale requirements.

MySQL for WordPress & PHP

Setup, optimize, and backup MySQL.

Create database and user in hosting panel or CLI. wp-config.php connects WordPress to MySQL. Optimize with proper indexes, query cache, and InnoDB engine. Regular mysqldump backups. Monitor slow query log. Limit max connections to prevent resource exhaustion.

PostgreSQL for Modern Apps

When to choose Postgres over MySQL.

PostgreSQL offers advanced features: JSONB columns, full-text search, window functions, better concurrency (MVCC). Preferred for SaaS, analytics, and geospatial apps. Slightly steeper learning curve but more powerful for complex queries.

Redis Caching Layer

Speed up apps with in-memory cache.

Redis stores key-value pairs in RAM — microsecond reads vs millisecond DB queries. Use for: session storage, page cache, rate limiting, pub/sub messaging. WordPress plugins like Redis Object Cache drop DB load by 80%+. Set TTL on cache keys to auto-expire stale data.

Database Backups & Recovery

Protect data from loss and corruption.

Automate daily backups with retention (7 daily, 4 weekly, 12 monthly). Store off-site (S3, different region). Test restore monthly — untested backups are worthless. Point-in-time recovery with binary logs. Encrypt backup files at rest.