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.

14.2 KB
Raw Blame
// Postgres
import { DateTime } from 'luxon'
import { Client, Pool, PoolClient, QueryConfig, QueryResult, QueryResultRow, types as pgTypes } from 'pg'

import { MISSING_DATABASE_URL_MESSAGE } from '~/common/config/database-url'
import { withSpan } from '~/common/tracing/tracing-utils'

import { logger } from '../logger'
import { createPostgresPool } from '../utils'
import { DependencyUnavailableError } from './error'
import {
    postgresClientErrorCounter,
    postgresErrorCounter,
    postgresOpenTransactionsGauge,
    postgresPoolAcquireDurationHistogram,
} from './metrics'
import { timeoutGuard } from './utils'

/** Config that PostgresRouter needs to create its connection pools. */
export type PostgresRouterConfig = {
    DATABASE_URL: string
    POSTGRES_CONNECTION_POOL_SIZE: number
    DATABASE_READONLY_URL?: string
    PLUGIN_STORAGE_DATABASE_URL?: string
    PERSONS_DATABASE_URL?: string
    PERSONS_READONLY_DATABASE_URL?: string
    BEHAVIORAL_COHORTS_DATABASE_URL?: string
}

// By default node-postgres returns dates as JS Date objects using the local timezone.
// We need UTC ISO strings instead. This must be called before creating any Pool.
// Idempotent — safe to call multiple times (e.g. from both hub.ts and PostgresRouter).
let typeParsersInstalled = false
export function installPostgresTypeParsers(): void {
    if (typeParsersInstalled) {
        return
    }
    typeParsersInstalled = true

    pgTypes.setTypeParser(1083 /* types.TypeId.TIME */, (timeStr) =>
        timeStr ? DateTime.fromSQL(timeStr, { zone: 'utc' }).toISO() : null
    )
    pgTypes.setTypeParser(1114 /* types.TypeId.TIMESTAMP */, (timeStr) =>
        timeStr ? DateTime.fromSQL(timeStr, { zone: 'utc' }).toISO() : null
    )
    pgTypes.setTypeParser(1184 /* types.TypeId.TIMESTAMPTZ */, (timeStr) =>
        timeStr ? DateTime.fromSQL(timeStr, { zone: 'utc' }).toISO() : null
    )
}

const POSTGRES_UNAVAILABLE_ERROR_MESSAGES = [
    'connection to server at',
    'could not translate host',
    'server conn crashed',
    'no more connections allowed',
    'server closed the connection unexpectedly',
    'getaddrinfo EAI_AGAIN',
    'getaddrinfo ENOTFOUND', // Cluster DNS briefly answering NXDOMAIN for the PgBouncer service name
    'Connection terminated unexpectedly',
    'ECONNREFUSED',
    'ECONNRESET', // Connection reset by peer, e.g. PgBouncer/PG closed an idle or in-flight connection
    'EHOSTUNREACH', // No route to host, e.g. the PgBouncer pod's IP vanished during a restart
    'ETIMEDOUT',
    'query_wait_timeout', // Waiting on PG bouncer to give us a slot
    'server login has been failing', // PgBouncer cannot authenticate with upstream PG
    'pooler is shutting down', // PgBouncer terminating client connections during a restart
    'Cannot use a pool after calling end on the pool', // Shutdown ended the pool while work was still in flight
    // A hot standby cancels a read when WAL replay conflicts with it.
    // Some servers capitalize the first word of the message, so the entry holds only the words that every variant shares.
    // Servers report the conflict as SQLSTATE 40001 or 57014.
    // Both codes also cover other failures, so the list matches this phrase instead of a code.
    'conflict with recovery',
]

export function isTransientPgError(err: unknown): boolean {
    const message = (err as Error | undefined)?.message
    return !!message && POSTGRES_UNAVAILABLE_ERROR_MESSAGES.some((m) => message.includes(m))
}

// Below the realtime cohort member page deadline, so a stalled connection fails before the page does.
const BEHAVIORAL_COHORTS_CONNECTION_TIMEOUT_MS = 30_000

export enum PostgresUse {
    COMMON_READ, // Read replica on the common tables, uses need to account for possible replication delay
    COMMON_WRITE, // Main PG master with common tables, we need to move as many queries away from it as possible
    PLUGIN_STORAGE_RW, // Plugin Storage table, no read replica for it
    PERSONS_READ, // Person database, read replica
    PERSONS_WRITE, // Person database, write
    BEHAVIORAL_COHORTS_RW, // Behavioral cohorts database for behavioral cohorts
}

export class TransactionClient {
    readonly target: PostgresUse
    readonly client: PoolClient

    constructor(target: PostgresUse, client: PoolClient) {
        this.target = target
        this.client = client
    }
}

export class PostgresRouter {
    private pools: Map<PostgresUse, Pool>

    constructor(serverConfig: PostgresRouterConfig, appName?: string) {
        installPostgresTypeParsers()

        if (!serverConfig.DATABASE_URL) {
            throw Error(MISSING_DATABASE_URL_MESSAGE)
        }

        const app_name = appName ?? 'unknown'
        logger.info('🤔', `Connecting to common Postgresql...`)
        const commonClient = createPostgresPool(
            serverConfig.DATABASE_URL,
            serverConfig.POSTGRES_CONNECTION_POOL_SIZE,
            app_name
        )
        logger.info('👍', `Common Postgresql ready`)
        // We fill the pools maps with the default client by default as a safe fallback for hobby,
        // the rest of the constructor overrides entries if more database URLs are passed.
        this.pools = new Map([
            [PostgresUse.COMMON_WRITE, commonClient],
            [PostgresUse.COMMON_READ, commonClient],
            [PostgresUse.PLUGIN_STORAGE_RW, commonClient],
            [PostgresUse.PERSONS_WRITE, commonClient],
            [PostgresUse.BEHAVIORAL_COHORTS_RW, commonClient],
        ])

        if (serverConfig.DATABASE_READONLY_URL) {
            logger.info('🤔', `Connecting to read-only common Postgresql...`)
            this.pools.set(
                PostgresUse.COMMON_READ,
                createPostgresPool(
                    serverConfig.DATABASE_READONLY_URL,
                    serverConfig.POSTGRES_CONNECTION_POOL_SIZE,
                    app_name
                )
            )
            logger.info('👍', `Read-only common Postgresql ready`)
        }
        if (serverConfig.PLUGIN_STORAGE_DATABASE_URL) {
            logger.info('🤔', `Connecting to plugin-storage Postgresql...`)
            this.pools.set(
                PostgresUse.PLUGIN_STORAGE_RW,
                createPostgresPool(
                    serverConfig.PLUGIN_STORAGE_DATABASE_URL,
                    serverConfig.POSTGRES_CONNECTION_POOL_SIZE,
                    app_name
                )
            )
            logger.info('👍', `Plugin-storage Postgresql ready`)
        }
        if (serverConfig.PERSONS_DATABASE_URL) {
            logger.info('🤔', `Connecting to persons Postgresql...`)
            this.pools.set(
                PostgresUse.PERSONS_WRITE,
                createPostgresPool(
                    serverConfig.PERSONS_DATABASE_URL,
                    serverConfig.POSTGRES_CONNECTION_POOL_SIZE,
                    app_name
                )
            )
            logger.info('👍', `Persons Postgresql ready`)
        }

        if (serverConfig.BEHAVIORAL_COHORTS_DATABASE_URL) {
            logger.info('🤔', `Connecting to behavioral cohorts Postgresql...`)
            this.pools.set(
                PostgresUse.BEHAVIORAL_COHORTS_RW,
                createPostgresPool(
                    serverConfig.BEHAVIORAL_COHORTS_DATABASE_URL,
                    serverConfig.POSTGRES_CONNECTION_POOL_SIZE,
                    app_name,
                    undefined,
                    // A connection whose startup stalls would otherwise hold a pool slot forever.
                    BEHAVIORAL_COHORTS_CONNECTION_TIMEOUT_MS
                )
            )
            logger.info('👍', `Behavioral cohorts Postgresql ready`)
        }

        if (serverConfig.PERSONS_READONLY_DATABASE_URL) {
            logger.info('🤔', `Connecting to persons read-only Postgresql...`)
            this.pools.set(
                PostgresUse.PERSONS_READ,
                createPostgresPool(
                    serverConfig.PERSONS_READONLY_DATABASE_URL,
                    serverConfig.POSTGRES_CONNECTION_POOL_SIZE,
                    app_name
                )
            )
            logger.info('👍', `Persons read-only Postgresql ready`)
        } else {
            this.pools.set(PostgresUse.PERSONS_READ, this.pools.get(PostgresUse.PERSONS_WRITE)!)
            logger.info('👍', `Using persons write pool for read-only`)
        }
    }

    public async query<R extends QueryResultRow = any, I extends any[] = any[]>(
        target: PostgresUse | TransactionClient,
        queryString: string | QueryConfig<I>,
        values: I | undefined,
        tag: string,
        queryFailureLogLevel: 'error' | 'warn' = 'error'
    ): Promise<QueryResult<R>> {
        if (target instanceof TransactionClient) {
            const wrappedTag = `${PostgresUse[target.target]}:Tx<${tag}>`
            return postgresQuery(target.client, queryString, values, wrappedTag, queryFailureLogLevel, target.target)
        } else {
            const wrappedTag = `${PostgresUse[target]}<${tag}>`
            return postgresQuery(this.pools.get(target)!, queryString, values, wrappedTag, queryFailureLogLevel, target)
        }
    }

    public async transaction<ReturnType>(
        usage: PostgresUse,
        tag: string,
        transaction: (client: TransactionClient) => Promise<ReturnType>
    ): Promise<ReturnType> {
        const poolLabel = PostgresUse[usage]
        const wrappedTag = `${poolLabel}:Tx<${tag}>`

        return withSpan('postgres', 'query.postgres_transaction', { tag: wrappedTag }, async () => {
            const timeout = timeoutGuard(`Postgres slow transaction warning after 30 sec!`)
            const acquireStart = performance.now()
            const client = await this.pools.get(usage)!.connect()
            postgresPoolAcquireDurationHistogram.observe({ pool: poolLabel }, (performance.now() - acquireStart) / 1000)

            // pg removes the pool's error listener from a checked-out client, so a lost
            // connection would otherwise crash the process as an unhandled 'error' event.
            let releaseError: Error | undefined
            const onClientError = (error: Error): void => {
                releaseError = error
                postgresClientErrorCounter.inc({ pool: poolLabel })
                logger.warn('🔌', 'Postgres client error during transaction', { tag: wrappedTag, error: String(error) })
            }
            client.on('error', onClientError)
            postgresOpenTransactionsGauge.inc({ pool: poolLabel, tag })
            try {
                await client.query('BEGIN')
                const response = await transaction(new TransactionClient(usage, client))
                await client.query('COMMIT')
                return response
            } catch (e) {
                try {
                    await client.query('ROLLBACK')
                } catch (rollbackError) {
                    // The connection's transaction state is unknown, so the pool must not reuse it.
                    releaseError ??= rollbackError as Error
                    logger.warn('🔌', 'Postgres ROLLBACK failed', { tag: wrappedTag, error: String(rollbackError) })
                }

                // if Postgres is down the ROLLBACK above won't work, but the transaction shouldn't be committed either
                handlePostgresError(e, usage)

                throw e
            } finally {
                postgresOpenTransactionsGauge.dec({ pool: poolLabel, tag })
                client.removeListener('error', onClientError)
                client.release(releaseError)
                clearTimeout(timeout)
            }
        })
    }

    public async connect(usage: PostgresUse): Promise<PoolClient> {
        return await this.pools.get(usage)!.connect()
    }

    async end(): Promise<void> {
        // Close all the connection pools
        const uniquePools: Set<Pool> = new Set(this.pools.values())
        for (const pool of uniquePools) {
            await pool.end()
        }
    }
}

/**
 * Scope entry for a `PostgresRouter`. `start` constructs the router
 * (which opens connection pools eagerly), `stop` ends every pool. Register
 * this in a `Scope` so the router's lifetime is tied to the scope that
 * owns it.
 */
export class PostgresRouterComponent {
    constructor(
        private readonly config: PostgresRouterConfig,
        private readonly appName?: string
    ) {}

    start(): Promise<{ value: PostgresRouter; stop: () => Promise<void> }> {
        const router = new PostgresRouter(this.config, this.appName)
        return Promise.resolve({
            value: router,
            stop: () => router.end(),
        })
    }
}

function postgresQuery<R extends QueryResultRow = any, I extends any[] = any[]>(
    // Un-exported, use PostgresRouter to run PG queries
    client: Client | Pool | PoolClient,
    queryString: string | QueryConfig<I>,
    values: I | undefined,
    tag: string,
    queryFailureLogLevel: 'error' | 'warn' = 'error',
    databaseUse: PostgresUse
): Promise<QueryResult<R>> {
    return withSpan('postgres', 'query.postgres', { tag: tag ?? 'unknown' }, async () => {
        const queryConfig =
            typeof queryString === 'string'
                ? {
                      // Annotate query string to give context when looking at DB logs
                      // TODO: Use the plugin-server-mode tag to be extra specific
                      text: `/* nodejs:${tag} */ ${queryString}`,
                      values,
                  }
                : queryString

        try {
            return await client.query(queryConfig, values)
        } catch (error) {
            handlePostgresError(error, databaseUse)

            logger[queryFailureLogLevel]('🔴', 'Postgres query error', {
                query: queryConfig.text,
                error,
                stack: error.stack,
            })
            throw error
        }
    })
}

/** Throws retriable DependencyUnavailableError for transient PG/PgBouncer errors, does nothing otherwise. */
export function handlePostgresError(error: Error, databaseUse: PostgresUse): void {
    const matchedMessage = POSTGRES_UNAVAILABLE_ERROR_MESSAGES.find((msg) => error.message?.includes(msg))
    if (!matchedMessage) {
        return
    }

    postgresErrorCounter.inc({ error_type: matchedMessage, database_use: PostgresUse[databaseUse] })
    throw new DependencyUnavailableError(error.message, 'Postgres', error)
}