Module 4: DB & Performance

Database Optimization

WordPress default queries can be slow. Learn to optimize them and use caching layers.

Key Concepts

• PostgreSQL: Using `pg4wp` for enterprise data compliance.
• Redis Object Cache: Caching query results in memory.
• Transients: Storing temporary data.

1. Why PostgreSQL?

PostgreSQL offers better concurrency, JSON support, and reliability than MySQL. We use `pg4wp` to make WordPress talk to Postgres.

2. Installing pg4wp

Drop the `db.php` drop-in into `web/app/` and configure your connection string in `.env`.

3. Redis Object Caching

WordPress makes many database queries per page. Redis caches the results of these queries in memory.

wp redis enable

4. Query Monitor

Use the Query Monitor plugin to identify slow queries, duplicate queries, and high memory usage.

5. N+1 Query Problem

Avoid fetching related data in a loop. Use `Timber::get_posts()` which handles eager loading better, or custom SQL.

6. Transients API

Cache API responses (like NocoDB data) for a set time:

if ( false === ( $data = get_transient( 'noco_data' ) ) ) {
    $data = fetch_from_nocodb();
    set_transient( 'noco_data', $data, 12 * HOUR_IN_SECONDS );
}

7. Custom Tables vs Post Meta

Post Meta is flexible but slow for searching. For high-performance data, create custom tables in Postgres.

8. Indexing

Ensure your custom tables and frequently searched columns are properly indexed in the database.

9. Asset Optimization

Minify and concatenate CSS/JS. Use WebP images. Bedrock's structure makes it easy to integrate build tools like Webpack or Vite.

10. CDN Integration

Offload static assets (images, CSS, JS) to a CDN like Cloudflare or AWS CloudFront to reduce server load.