Add Example for Using WithReleaseConnection in Go ClickHouse Client

[barkhayot]

Discussed in #1337

^{Originally posted by barkhayot June 27, 2024}
There is limited documentation on using the WithReleaseConnection option in the Go ClickHouse client (github.com/ClickHouse/clickhouse-go). This feature is crucial for optimizing connection management in high-concurrency scenarios, but many users are not aware of its benefits or how to implement it.

I propose adding an example to the documentation to illustrate how to use WithReleaseConnection when preparing batches. Here’s a detailed example and explanation:

package main

import (
    "context"
    "fmt"
    "github.com/ClickHouse/clickhouse-go/v2"
    "time"
)

func main() {
    connect, err := clickhouse.Open(&clickhouse.Options{
        Addr: []string{"host:port"},
        Auth: clickhouse.Auth{
            Database: "db",
            Username: "user",
            Password: "pass",
        },
        DialTimeout: 5 * time.Second,
        ConnMaxLifetime: time.Hour,
        ConnOpenStrategy: clickhouse.ConnOpenRoundRobin,
    })
    if err != nil {
        fmt.Println("Error connecting:", err)
        return
    }

    ctx := context.Background()
    batch, err := connect.PrepareBatch(ctx, "INSERT INTO table (column1, column2)")
    if err != nil {
        fmt.Println("Prepare batch error:", err)
        return
    }

    for i := 0; i < 10; i++ {
        if err := batch.Append(i, fmt.Sprintf("value %d", i)); err != nil {
            fmt.Println("Append error:", err)
            return
        }
    }

    if err := batch.Send(); err != nil {
        fmt.Println("Send error:", err)
        return
    }

    defer connect.Close()
}

With WithReleaseConnection

Using WithReleaseConnection ensures that the connection is released back to the pool after the batch operation is completed, making it available for other operations.

package main

import (
    "context"
    "fmt"
    "github.com/ClickHouse/clickhouse-go/v2"
    "time"
)

func main() {
    connect, err := clickhouse.Open(&clickhouse.Options{
        Addr: []string{"host:port"},
        Auth: clickhouse.Auth{
            Database: "db",
            Username: "user",
            Password: "pass",
        },
        DialTimeout: 5 * time.Second,
        ConnMaxLifetime: time.Hour,
        ConnOpenStrategy: clickhouse.ConnOpenRoundRobin,
    })
    if err != nil {
        fmt.Println("Error connecting:", err)
        return
    }

    ctx := context.Background()
    batch, err := connect.PrepareBatch(ctx, "INSERT INTO table (column1, column2)", clickhouse.WithReleaseConnection())
    if err != nil {
        fmt.Println("Prepare batch error:", err)
        return
    }

    for i := 0; i < 10; i++ {
        if err := batch.Append(i, fmt.Sprintf("value %d", i)); err != nil {
            fmt.Println("Append error:", err)
            return
        }
    }

    if err := batch.Send(); err != nil {
        fmt.Println("Send error:", err)
        return
    }

    // Connection is released back to the pool after batch.Send()
}

Explanation

Without WithReleaseConnection: This method directly uses the connection object to execute queries and prepare batches. Each connection remains open until explicitly closed, which can lead to resource exhaustion and scalability issues in high-concurrency scenarios.

With WithReleaseConnection: This method ensures that the connection is promptly released back to the pool after the batch operation is completed. It provides efficient resource usage and better scalability for high-concurrency environments.

[jkaflik]

Hi @barkhayot

Thanks for providing these examples? Would you like to become a contributor and push a PR?