Caching Slow Database Queries

I have a friend who owns a small startup. The backend stack is python based, but he was questioning whether he should gradually migrate to Go to exploit its runtime speed. I had been involved in similar projects migrating PHP applications, so I had expertise in the topic.

After studying the specifics of his case, I recommended that he should not. Based on what he was trying to accomplish, I advised him that he would definitely get a substantial latency improvement, but it would still be limited by the latency caused by the database operations.

In many cases, database operations are the primary bottleneck — irrespective of the language or framework that you use.

Despite that, there are some ways to mitigate it. One way is by caching the results of slow database queries. Caching involves storing the results of your database queries temporarily into memory. Subsequent attempts to query the database will use the stored data instead.

Why should I cache?

There are numerous reasons why you should cache. Here are some:

• Speed up slow queries
• Reduce the memory and CPU pressure on your database server
• Add some functionality to your application so if your database goes down (or during planned maintenance), you can provide some limited functionality to your users

How do I cache?

You use my remember-go package. It provides the most convenient and elegant way to do so.

A variant of my package has now been battle-tested in production for over 1
½ years.

Preliminary Decisions

You need to decide which storage driver to use. The package comes with two drivers. There are advantages to each. The community can create storage drivers for alternatives such as Memcached.

In-Memory

• Significantly faster
• No shared state for horizontal scaling. Each cache is unique to the one running application.

Redis

• If your application is composed of multiple instances running, they can all share the same cache
• Provides powerful features such as filtering keys

Create a Key

A cache is essentially a key-value pair. The key is used to extract the correct data from the cache. The key can be an arbitrary string, but it must encode all variables that impact the results of the database query. That way, the same arguments used for the query can be used to obtain from the cache the exact results that the query would have returned.

There are two convenient ways to generate a key. Let’s assume the query’s argument is an arbitrary search term and a page number for pagination.

CreateKeyStruct

CreateKeyStruct can generate a JSON based key by providing a struct.

type Key struct {
    Search string
    Page   int `json:"page"`
}
var key string = remember.CreateKeyStruct(Key{"golang", 2})

CreateKey

CreateKey provides more flexibility to generate keys:

key :=  remember.CreateKey(false, "-", "search-x-y", "search", "golang", 2)
// Key will be "search-golang-2"

Initialize the Storage Driver

In-Memory

The In-Memory storage driver stores data in a map. Expired data can be cleaned up at a fixed time interval. In the example below, removal is configured for every 10 minutes. It should be noted that data can still be set with an expiry of less than the cleanup interval.

var ms = memory.NewMemoryStore(10 * time.Minute)

Redis

The Redis storage driver relies on Gary Burd’s excellent Redis client library.

Create a SlowRetrieve Function

type SlowRetrieve func(ctx context.Context) (interface{}, error)

The coolest feature of my package is the reliance on the SlowRetrieve function. The package initially checks if data exists in the cache. If it doesn’t, then it elegantly fetches the data directly from the database by calling the SlowRetrieve function. It then saves the data into the cache so that next time it doesn’t have to refetch it from the database.

type Result struct {
    Title string
}
slowQuery := func(ctx context.Context) (interface{}, error) {
    results := []Result{}
    stmt := `
        SELECT title
        FROM books
        WHERE title LIKE ?
        ORDER BY title
        LIMIT ?, 20
    `
    rows, err := db.QueryContext(ctx, stmt, search, (page-1)*20)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    for rows.Next() {
        var title string
        if err := rows.Scan(&title); err != nil {
            return nil, err
        }
        results = append(results, Result{title})
    }
    return results, nil
}

The Super Bowl

The main function is the Cache( ) function. You provide a key and an expiry which signifies how long the data should remain in the cache before it is invalidated (and eventually removed).

key := remember.CreateKeyStruct(Key{"golang", 2})
exp := 10*time.Minute
results, found, err := remember.Cache(ctx, ms, key, exp, slowQuery, remember.Options{GobRegister: false})
return results.([]Result) // Type assert in order to use

Gob Register Errors

The Redis storage driver stores the data in a gob encoded form. You have to register with the gob package the data type returned by the SlowRetrieve function. It can be done inside a func init(). Alternatively, you can set the GobRegister option to true. This will slightly impact concurrency performance however.

License

The package is provided with a modified MIT license (mMIT). It has all the freedoms that the standard MIT license has, but with the additional constraint that the package not be used intentionally for military purposes.

Project Location

remember-go [Documentation]

Other Articles

• Entrepreneurial Teachings Of The Great Donald J. Trump (for open-minded only)
• Slice Tricks (beginners)
• Type Conversions, Casting & Type Assertions (beginners)
• How to Benchmark (intermediate)
• Canceling MySQL Queries in Go (properly) (intermediate/advanced)
• Better Go Syntax (igo project) (advanced)