Skip to content

:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.

11.9 KB
Raw Blame
import { ClickHouseClient, ExecResult, createClient as createClickhouseClient } from '@clickhouse/client'
import { performance } from 'perf_hooks'
import { Readable } from 'stream'

import { withSpan } from '~/common/tracing/tracing-utils'
import { timeoutGuard } from '~/common/utils/db/utils'
import { isTestEnv } from '~/common/utils/env-utils'
import { parseRawClickHouseEvent } from '~/common/utils/event'
import { parseJSON } from '~/common/utils/json-parse'
import { logger } from '~/common/utils/logger'
import { fetch } from '~/common/utils/request'
import { delay, escapeClickHouseString } from '~/common/utils/utils'
import {
    ClickHouseEvent,
    ClickHousePerson,
    ClickHousePersonDistinctId2,
    ClickhouseGroup,
    DeadLetterQueueEvent,
    InternalPerson,
    RawClickHouseEvent,
    RawSessionRecordingEvent,
} from '~/types'

import { assertTestDatabaseName } from './database-guard'

// Extracts the topic(s) a ClickHouse Kafka engine table consumes from its `engine_full` string.
// Handles both the keyword form (`kafka_topic_list = 'a,b'`) and the positional form
// (`Kafka('broker', 'topic', 'group', 'format')`, where the topic is the second quoted argument).
function extractKafkaTopicList(engineFull: string): string[] {
    const keyword = engineFull.match(/kafka_topic_list\s*=\s*'([^']+)'/)
    const raw = keyword ? keyword[1] : ([...engineFull.matchAll(/'([^']*)'/g)].map((m) => m[1])[1] ?? '')
    return raw
        .split(',')
        .map((topic) => topic.trim())
        .filter(Boolean)
}

export class Clickhouse {
    private client: ClickHouseClient

    constructor(client: ClickHouseClient) {
        this.client = client
    }

    static createClient(): ClickHouseClient {
        // NOTE: We never query CH in production so we just load these from the env directly
        const CLICKHOUSE_HOST = process.env.CLICKHOUSE_HOST ?? 'localhost'
        const CLICKHOUSE_DATABASE = process.env.CLICKHOUSE_DATABASE ?? (isTestEnv() ? 'posthog_test' : 'default')
        const CLICKHOUSE_USER = process.env.CLICKHOUSE_USER ?? 'default'
        const CLICKHOUSE_PASSWORD = process.env.CLICKHOUSE_PASSWORD ?? null

        const clickhouse = createClickhouseClient({
            // We prefer to run queries on the offline cluster.
            url: `http://${CLICKHOUSE_HOST}:8123`,
            username: CLICKHOUSE_USER,
            password: CLICKHOUSE_PASSWORD || undefined,
            database: CLICKHOUSE_DATABASE,
            max_open_connections: 50, // Increased from 30 for better concurrency
            // Connection reliability improvements
            request_timeout: 30000, // 30s minutes request timeout
            keep_alive: {
                enabled: true,
                idle_socket_ttl: 30000, // 30 seconds idle timeout
            },
        })

        return clickhouse
    }

    static create(): Clickhouse {
        const client = Clickhouse.createClient()
        return new Clickhouse(client)
    }

    close(): void {
        this.client.close()
    }

    // Memoized so repeated truncates don't re-query the database name. The
    // onRejected handler resets the cache when the `query` itself fails (a
    // transient connection error), so it isn't cached as a permanently rejected
    // promise. An assertTestDatabaseName failure throws from onFulfilled and
    // stays cached on purpose: it's a deterministic verdict on a fixed database
    // name, so re-querying would only reach the same conclusion.
    private databaseGuard?: Promise<void>
    private ensureTestDatabase(): Promise<void> {
        this.databaseGuard ??= this.query<{ name: string }>('SELECT currentDatabase() AS name').then(
            (rows) => assertTestDatabaseName(rows[0].name, 'ClickHouse'),
            (error) => {
                this.databaseGuard = undefined
                throw error
            }
        )
        return this.databaseGuard
    }

    async truncate(table: string) {
        await this.ensureTestDatabase()
        await this.exec(`TRUNCATE ${table}`)
    }

    async resetTestDatabase(): Promise<void> {
        await this.waitForHealthy()
        // Guard before the truncates: Promise.allSettled below would swallow
        // the guard error if it only surfaced inside truncate().
        await this.ensureTestDatabase()
        // NOTE: Don't do more than 5 at once otherwise we get socket timeout errors
        await Promise.allSettled([
            this.truncate('sharded_events'),
            this.truncate('person'),
            this.truncate('person_distinct_id'),
            this.truncate('person_distinct_id2'),
            this.truncate('person_distinct_id_overrides'),
        ])

        await Promise.allSettled([
            this.truncate('person_static_cohort'),
            this.truncate('sharded_session_replay_events'),
            this.truncate('events_dead_letter_queue'),
            this.truncate('groups'),
            this.truncate('ingestion_warnings'),
        ])

        await Promise.allSettled([
            this.truncate('sharded_ingestion_warnings'),
            this.truncate('sharded_app_metrics'),
            this.truncate('ingestion_warnings_v2'),
        ])
    }

    // Topics that this database's Kafka engine tables consume from. Tests pre-create every
    // subscribed topic so no consumer is left retrying "Can't get assignment" against a missing
    // topic — those retries saturate ClickHouse's background scheduler and intermittently starve
    // the consumers the tests depend on (e.g. the ingestion_warnings warmup probe).
    async getKafkaEngineTopics(): Promise<string[]> {
        const rows = await this.query<{ engine_full: string }>(
            `SELECT engine_full FROM system.tables WHERE database = currentDatabase() AND engine = 'Kafka'`
        )
        return [...new Set(rows.flatMap((row) => extractKafkaTopicList(row.engine_full)))]
    }

    async waitForHealthy(delayMs = 100, maxDelayCount = 300): Promise<void> {
        const timer = performance.now()

        for (let i = 0; i < maxDelayCount; i++) {
            try {
                await this.query('SELECT 1')
                console.log(`ClickHouse healthy after ${Math.round((performance.now() - timer) / 100) / 10}s`)
                return
            } catch (error) {
                console.log(
                    `ClickHouse not healthy yet. ${
                        Math.round((performance.now() - timer) / 100) / 10
                    }s since start. Error: ${error}`
                )
                const res = await fetch('http://localhost:8123/ping').catch((e) => {
                    console.log('ClickHouse ping failed', e)
                    return null
                })
                if (res) {
                    console.log('ClickHouse ping', res.status, await res.text())
                }

                await delay(delayMs)
            }
        }

        throw Error(`ClickHouse failed to become healthy after ${maxDelayCount * delayMs}ms`)
    }

    async delayUntilEventIngested<T extends any[] | number>(
        fetchData: () => T | Promise<T>,
        minLength = 1,
        delayMs = 100,
        maxDelayCount = 300
    ): Promise<T> {
        const timer = performance.now()
        let data: T | null = null
        let dataLength = 0

        for (let i = 0; i < maxDelayCount; i++) {
            data = await fetchData()
            dataLength = typeof data === 'number' ? data : data.length
            logger.debug(
                `Waiting. ${Math.round((performance.now() - timer) / 100) / 10}s since the start. ${dataLength} event${
                    dataLength !== 1 ? 's' : ''
                }.`
            )
            if (dataLength >= minLength) {
                return data
            }
            await delay(delayMs)
        }

        throw Error(`Failed to get data in time, got ${JSON.stringify(data)}`)
    }

    async exec(query: string): Promise<ExecResult<Readable>> {
        try {
            return await this.client.exec({
                query,
            })
        } catch (e) {
            console.error('Clickhouse exec failed', {
                query,
                error: e,
            })
            throw e
        }
    }

    query<T>(query: string): Promise<T[]> {
        return withSpan('clickhouse', 'query.clickhouse', { tag: 'unknown' }, async () => {
            const timeout = timeoutGuard('ClickHouse slow query warning after 30 sec', { query })
            try {
                const queryResult = await this.client.query({
                    query,
                    format: 'JSON',
                    clickhouse_settings: {
                        output_format_json_quote_64bit_integers: 0,
                        output_format_json_quote_denormals: 0,
                    },
                })

                const jsonData = (await queryResult.json()).data as T[]
                return jsonData
            } catch (e) {
                console.error('Clickhouse query failed', {
                    query,
                    error: e,
                })
                throw e
            } finally {
                clearTimeout(timeout)
            }
        })
    }

    async fetchPersons(teamId?: number): Promise<ClickHousePerson[]> {
        const query = `
            SELECT id, team_id, is_identified, ts as _timestamp, properties, created_at, is_del as is_deleted, _offset
            FROM (
                SELECT id,
                    team_id,
                    max(is_identified) as is_identified,
                    max(_timestamp) as ts,
                    argMax(properties, _timestamp) as properties,
                    argMin(created_at, _timestamp) as created_at,
                    max(is_deleted) as is_del,
                    argMax(_offset, _timestamp) as _offset
                FROM person
                FINAL
                ${teamId ? `WHERE team_id = ${teamId}` : ''}
                GROUP BY team_id, id
                HAVING max(is_deleted)=0
            )
            `
        const data = await this.query(query)
        return data.map((row) => {
            const { 'person_max._timestamp': _discard1, 'person_max.id': _discard2, ...rest }: any = row
            return rest
        })
    }

    async fetchDistinctIds(person: InternalPerson): Promise<ClickHousePersonDistinctId2[]> {
        const query = `
            SELECT *
            FROM person_distinct_id2
            FINAL
            WHERE person_id='${escapeClickHouseString(person.uuid)}'
              AND team_id='${person.team_id}'
              AND is_deleted=0
            ORDER BY _offset`
        return await this.query<ClickHousePersonDistinctId2>(query)
    }

    public async fetchDistinctIdValues(person: InternalPerson): Promise<string[]> {
        const personDistinctIds = await this.fetchDistinctIds(person)
        return personDistinctIds.map((pdi) => pdi.distinct_id)
    }

    public async fetchEvents(): Promise<ClickHouseEvent[]> {
        const queryResult = await this.query<RawClickHouseEvent>(`SELECT * FROM events ORDER BY timestamp ASC`)
        return queryResult.map(parseRawClickHouseEvent)
    }

    public async fetchDeadLetterQueueEvents(): Promise<DeadLetterQueueEvent[]> {
        const result = await this.query<DeadLetterQueueEvent>(
            `SELECT * FROM events_dead_letter_queue ORDER BY _timestamp ASC`
        )
        return result
    }

    // SessionRecordingEvent

    public async fetchSessionRecordingEvents(): Promise<RawSessionRecordingEvent[]> {
        const events = await this.query<RawSessionRecordingEvent>(`SELECT * FROM session_recording_events`)
        return events.map((event) => {
            return {
                ...event,
                snapshot_data: event.snapshot_data ? parseJSON(event.snapshot_data) : null,
            }
        })
    }

    public async fetchClickhouseGroups(): Promise<ClickhouseGroup[]> {
        const query = `
        SELECT group_type_index, group_key, created_at, team_id, group_properties FROM groups FINAL
        `
        return await this.query<ClickhouseGroup>(query)
    }
}