ClickHouse / ClickHouse/clickhouse-go

High memory consumption INSERTing

Open
#1,384 12 comments 1 reaction 0 assignees View on GitHub
performance Q1-FY-2026
Dominant language
Go
Stars
3.3k
Forks
680
Avg merge
2d 3h
Merged PRs (30d)
14

Description

## Observed

We insert 1 million records at a time using the function `batch.AppendStruct(item)`
After some number of iterations we had high memory consumption by the clickhouse client.
```
Type: inuse_space
Time: Aug 22, 2024 at 11:40am (+04)
Entering interactive mode (type "help" for commands, "o" for options)
(pprof) top
Showing nodes accounting for 20049.33MB, 97.87% of 20485.24MB total
Dropped 144 nodes (cum <= 102.43MB)
Showing top 10 nodes out of 31
flat flat% sum% cum cum%
7554.70MB 36.88% 36.88% 7554.70MB 36.88% github.com/ClickHouse/ch-go/compress.(*Writer).Compress
6764.72MB 33.02% 69.90% 6764.72MB 33.02% github.com/ClickHouse/ch-go/proto.ColStr.EncodeColumn
2455.30MB 11.99% 81.89% 2458.01MB 12.00% github.com/bytedance/sonic.frozenConfig.Unmarshal
1498.78MB 7.32% 89.20% 1498.78MB 7.32% github.com/G-Core/cdn-analytics-platform/src/log-sender/internal/domain.init.func6
1362.98MB 6.65% 95.86% 1362.98MB 6.65% github.com/ClickHouse/ch-go/proto.(*ColStr).Append (inline)
184.73MB 0.9% 96.76% 184.73MB 0.9% github.com/G-Core/cdn-analytics-platform/src/log-sender/internal/accumulator.NewAccumulator
182.13MB 0.89% 97.65% 223.74MB 1.09% github.com/ClickHouse/clickhouse-go/v2/lib/column.(*LowCardinality).AppendRow
```
```
(pprof) list Compress
Total: 20.01GB
ROUTINE ======================== github.com/ClickHouse/ch-go/compress.(*Writer).Compress in cdn-analytics-platform/vendor/github.com/ClickHouse/ch-go/compress/writer.go
7.38GB 7.38GB (flat, cum) 36.88% of Total
. . 21:func (w *Writer) Compress(m Method, buf []byte) error {
. . 22: maxSize := lz4.CompressBlockBound(len(buf))
7.38GB 7.38GB 23: w.Data = append(w.Data[:0], make([]byte, maxSize+headerSize)...)
. . 24: _ = w.Data[:headerSize]
. . 25: w.Data[hMethod] = byte(m)
. . 26:
. . 27: var n int
. . 28:
```
```
(pprof) list proto.ColStr.EncodeColumn
Total: 20.01GB
ROUTINE ======================== github.com/ClickHouse/ch-go/proto.ColStr.EncodeColumn in cdn-analytics-platform/vendor/github.com/ClickHouse/ch-go/proto/col_str.go
6.61GB 6.61GB (flat, cum) 33.02% of Total
. . 70:func (c ColStr) EncodeColumn(b *Buffer) {
. . 71: buf := make([]byte, binary.MaxVarintLen64)
. . 72: for _, p := range c.Pos {
. . 73: n := binary.PutUvarint(buf, uint64(p.End-p.Start))
. . 74: b.Buf = append(b.Buf, buf[:n]...)
6.61GB 6.61GB 75: b.Buf = append(b.Buf, c.Buf[p.Start:p.End]...)
. . 76: }
. . 77:}
. . 78:
. . 79:// ForEach calls f on each string from column.
. . 80:func (c ColStr) ForEach(f func(i int, s string) error) error {
```

pprof memory report: [pprof.alloc_objects.alloc_space.inuse_objects.inuse_space.028.pb.gz](https://github.com/user-attachments/files/16724591/pprof.alloc_objects.alloc_space.inuse_objects.inuse_space.028.pb.gz)

Our golang structure that we put into the database:
```go
type Item struct {
FieldA time.Time `ch:"field_a"`
FieldB time.Time `ch:"field_b"`
FieldC net.IP `ch:"field_c"`
FieldD string `ch:"field_d"`
FieldE string `ch:"field_e"`
FieldF string `ch:"field_f"`
FieldG string `ch:"field_g"`
FieldH string `ch:"field_h"`
FieldI uint16 `ch:"field_i"`
FieldJ int64 `ch:"field_j"`
FieldK string `ch:"field_k"`
FieldL string `ch:"field_l"`
FieldM int64 `ch:"field_m"`
FieldN string `ch:"field_n"`
FieldO uint32 `ch:"field_o"`
FieldP string `ch:"field_p"`
FieldQ []uint32 `ch:"field_q"`
FieldR []int64 `ch:"field_r"`
FieldS string `ch:"field_s"`
FieldT []uint16 `ch:"field_t"`
FieldU []uint32 `ch:"field_u"`
FieldV []uint32 `ch:"field_v"`
FieldW int32 `ch:"field_w"`
FieldX int32 `ch:"field_x"`
FieldY string `ch:"field_y"`
FieldZ net.IP `ch:"field_z"`
FieldAA string `ch:"field_aa"`
FieldAB string `ch:"field_ab"`
FieldAC string `ch:"field_ac"`
FieldAD uint32 `ch:"field_ad"`
FieldAE string `ch:"field_ae"`
FieldAF string `ch:"field_af"`
FieldAG string `ch:"field_ag"`
FieldAH string `ch:"field_ah"`
FieldAI string `ch:"field_ai"`
FieldAJ string `ch:"field_aj"`
FieldAK string `ch:"field_ak"`
FieldAL string `ch:"field_al"`
FieldAM string `ch:"field_am"`
FieldAN string `ch:"field_an"`
FieldAO uint8 `ch:"field_ao"`
FieldAP string `ch:"field_ap"`
FieldAQ []net.IP `ch:"field_aq"`
FieldAR uint64 `ch:"field_ar"`
FieldAS string `ch:"field_as"`
FieldAT uint32 `ch:"field_at"`
FieldAU uint32 `ch:"field_au"`
FieldAV string `ch:"field_av"`
FieldAW uint16 `ch:"field_aw"`
FieldAX uint16 `ch:"field_ax"`
FieldAY int8 `ch:"field_ay"`
FieldAZ string `ch:"field_az"`
}
```

## Expected behaviour
The client should reuse memory whenever possible, rather than allocating new memory at each iteration of batch insertion

## Code example

```go

query := "INSERT INTO target_table"

batch, err := conn.PrepareBatch(ctx, query)
if err != nil {
return fmt.Errorf("prepare batch: %v", err)
}

for _, item := range items {
if err := batch.AppendStruct(item); err != nil {
return fmt.Errorf("append to batch: %v", err)
}
}

if err := batch.Send(); err != nil {
return fmt.Errorf("send batch: %v", err)
}

```

## Details

### Environment
* [x] `clickhouse-go` version: `v2.25.0`
* [x] Interface: ClickHouse API / `database/sql` compatible driver: `ClickHouse API`
* [x] Go version: `1.22.1`
* [x] Operating system: `Linux`
* [x] ClickHouse version: `23.8.8.20`
* [x] Is it a ClickHouse Cloud? `No`
* [x] `CREATE TABLE` statements for tables involved:
```
CREATE TABLE target_table (
`field_a` DateTime('UTC'),
`field_b` Date,
`field_c` IPv6,
`field_d` LowCardinality(String),
`field_e` String,
`field_f` String,
`field_g` LowCardinality(String),
`field_h` LowCardinality(String),
`field_i` UInt16,
`field_j` Int64,
`field_k` String,
`field_l` String,
`field_m` Int64,
`field_n` String,
`field_o` UInt32,
`field_p` LowCardinality(String),
`field_q` Array(UInt32),
`field_r` Array(Int64),
`field_s` String,
`field_t` Array(UInt16),
`field_u` Array(UInt32),
`field_v` Array(UInt32),
`field_w` Int32,
`field_x` Int32,
`field_y` LowCardinality(String),
`field_z` IPv6,
`field_aa` String,
`field_ab` LowCardinality(String),
`field_ac` LowCardinality(String),
`field_ad` Nullable(UInt32),
`field_ae` LowCardinality(String),
`field_af` LowCardinality(String),
`field_ag` String,
`field_ah` LowCardinality(String),
`field_ai` LowCardinality(String),
`field_aj` LowCardinality(String),
`field_ak` LowCardinality(String),
`field_al` LowCardinality(String),
`field_am` LowCardinality(String),
`field_an` LowCardinality(String),
`field_ao` UInt8,
`field_ap` LowCardinality(String),
`field_aq` Array(IPv6),
`field_ar` UInt64,
`field_as` LowCardinality(String),
`field_at` UInt32,
`field_au` UInt32,
`field_av` LowCardinality(String),
`field_aw` Nullable(UInt16),
`field_ax` Nullable(UInt32),
`field_ay` Int8,
`field_az` LowCardinality(String)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/table', '{replica}')
PARTITION BY toYYYYMMDD(day)
ORDER BY (field_w, field_x, field_ab, field_av, field_ar)
TTL field_b + toIntervalDay(4)
```

Contributor guide

Open the contributing guide

Research direction

Start with the provided batch.AppendStruct reproduction and profile repeated million-row inserts. Inspect vendor/github.com/ClickHouse/ch-go/compress/writer.go, especially Writer.Compress, and vendor/github.com/ClickHouse/ch-go/proto/col_str.go, especially ColStr.EncodeColumn. Done means repeated batch insertion no longer causes unbounded memory growth and the observed allocations are reduced or reused where possible.

Written by the indexing model from the issue text.

Assessment

Tech stack
go
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.