Optimizing Tenant Schema Access in Go Applications

Introduction

In multi-tenant applications, efficient management of database schemas is crucial for performance. This post explores how caching and schema tracking can significantly reduce database load in Go applications.

The Challenge

Accessing tenant-specific data often involves repetitive database queries to check for schema existence and set the appropriate search path. These redundant operations can lead to performance bottlenecks, especially when dealing with a large number of tenants. Specifically, the devlog-ist/landing project experienced a high volume of pg_class queries and redundant SET search_path statements, impacting overall application responsiveness.

The Solution

To address these performance issues, the following optimizations were implemented:

1. Caching Tenant Table Existence: The result of the tenantsTableExists() check is now cached using a persistent cache with a Time-To-Live (TTL) of one hour. This eliminates a significant number of pg_class queries.
2. Tracking Applied Schema: The application now tracks the currently applied schema. Before executing a SET search_path statement, it checks if the desired tenant schema is already set. If so, the redundant statement is skipped.
3. Skipping Redundant Schema Reset: Similarly, the resetTenantSchema() function is skipped when the schema is already null, avoiding unnecessary database interactions.

These optimizations are implemented in Go, leveraging caching mechanisms and state management to minimize database load. Here's an example illustrating the concept of caching the schema existence:

package main

import (
    "fmt"
    "time"
)

var schemaCache = make(map[string]bool)
var cacheTTL = time.Hour

func checkSchemaExists(schemaName string) bool {
    // Check if the schema exists in the cache
    if exists, ok := schemaCache[schemaName]; ok {
        fmt.Println("Schema existence retrieved from cache for:", schemaName)
        return exists
    }

    // Simulate a database call to check schema existence
    fmt.Println("Checking schema existence in database for:", schemaName)
    // In real implementation, this would be a database query
    exists := simulateDatabaseCheck(schemaName)

    // Store the result in the cache with a TTL
     schemaCache[schemaName] = exists

    // Invalidate cache after TTL
    go func() {
         time.Sleep(cacheTTL)
         delete(schemaCache, schemaName)
         fmt.Println("Cache invalidated for:", schemaName)
    }()

    return exists
}

func simulateDatabaseCheck(schemaName string) bool {
    // Replace with actual database query
    // For example:
    // err := db.QueryRow("SELECT 1 FROM pg_namespace WHERE nspname = $1", schemaName).Scan(nil)
    // return err == nil
    return true // Simulating schema exists
}

func main() {
     schemaName := "tenant_123"
     exists := checkSchemaExists(schemaName)
     fmt.Println("Schema exists:", exists)

    // Subsequent check will use the cache
     exists = checkSchemaExists(schemaName)
     fmt.Println("Schema exists:", exists)

     time.Sleep(2 * time.Second) // Give some time for goroutine to execute


}

Results

These optimizations resulted in a significant reduction in database queries:

• Approximately 13,000 pg_class queries per day were eliminated.
• Around 10,000 redundant SET search_path statements per day were skipped.

This leads to improved application performance and reduced load on the database server.

Lessons Learned

Caching and state management are powerful tools for optimizing database-intensive applications. By identifying and eliminating redundant database operations, developers can achieve significant performance gains. Regular monitoring and profiling of database queries can help identify areas for optimization.