Django Database Routing for PostgreSQL and ClickHouse Separation in PostHog

PostHog routes Django ORM queries across multiple PostgreSQL databases using three specialized database routers while executing ClickHouse analytics queries through a custom client that completely bypasses Django's routing layer.

The PostHog platform separates transactional application data (users, teams, feature flags) stored in PostgreSQL from high-volume analytics data (events, sessions, funnels) stored in ClickHouse. To manage scale and workload isolation across PostgreSQL instances, the codebase implements a sophisticated database routing layer in posthog/settings/data_stores.py that dynamically selects database aliases based on model type and operation.

Router Registration and Priority

Django evaluates database routers in the order specified in DATABASE_ROUTERS. PostHog builds this list dynamically in posthog/settings/data_stores.py, inserting routers with specific priority:

# posthog/settings/data_stores.py

DATABASE_ROUTERS: list[str] = []

# Replica routing (lowest priority)

DATABASE_ROUTERS.append("posthog.dbrouter.ReplicaRouter")

# Person table routing (higher priority)

DATABASE_ROUTERS.insert(0, "posthog.person_db_router.PersonDBRouter")

# Product-specific routing (highest priority)

DATABASE_ROUTERS.insert(0, "posthog.product_db_router.ProductDBRouter")

The first router that returns a non-None value wins. This ordering ensures product-specific routes take precedence over generic replica routing.

ReplicaRouter: Optional Read Replica Optimization

Located in posthog/dbrouter.py, the ReplicaRouter enables offloading read queries to an Aurora read replica for specific models:

# posthog/dbrouter.py

class ReplicaRouter:
    def __init__(self, opt_in=None):
        self.opt_in = opt_in if opt_in else READ_REPLICA_OPT_IN

    def db_for_read(self, model, **hints):
        if "ALL_MODELS_USE_READ_REPLICA" in self.opt_in:
            return "replica"
        return "replica" if model.__name__ in self.opt_in else "default"

    def db_for_write(self, model, **hints):
        return "default"

To opt-in a model, set the READ_REPLICA_OPT_IN environment variable to a comma-separated list of model class names (e.g., Event,PersonDistinctId), or use "ALL_MODELS_USE_READ_REPLICA" to route all reads to the replica. Writes always return to the default database.

PersonDBRouter: Isolating the Persons Table

The PersonDBRouter in posthog/person_db_router.py handles write-heavy person data by routing all person-related models to dedicated persons_db_writer and persons_db_reader connections:

# posthog/person_db_router.py

PERSONS_DB_MODELS = {
    "person",
    "persondistinctid",
    # ... other person-related tables

}

def _get_persons_db_for_read():
    if settings.TEST or settings.DEBUG:
        return "persons_db_writer" if "persons_db_writer" in settings.DATABASES else "default"
    return "persons_db_reader" if "persons_db_reader" in settings.DATABASES else "default"

def _get_persons_db_for_write():
    return "persons_db_writer" if "persons_db_writer" in settings.DATABASES else "default"

class PersonDBRouter:
    def db_for_read(self, model, **hints):
        if model.__name__.lower() in PERSONS_DB_MODELS:
            return _get_persons_db_for_read()
        return None

    def db_for_write(self, model, **hints):
        if model.__name__.lower() in PERSONS_DB_MODELS:
            return _get_persons_db_for_write()
        return None

In test or debug environments, reads default to the writer to ensure uncommitted data visibility. In production, reads hit the persons_db_reader while writes go to persons_db_writer.

ProductDBRouter: Per-Product Database Isolation

The ProductDBRouter supports horizontal scaling for specific product apps (e.g., surveys, visual review, tracing) by routing their models to isolated databases configured via YAML.

Configuration loading happens in posthog/product_db_config.py:

# posthog/product_db_config.py

@dataclass
class ProductDBRoute:
    app_label: str
    database: str

def load_product_db_routes(base_dir: Path) -> tuple[ProductDBRoute, ...]:
    # reads db_routing.yaml and returns routes

The router implementation filters routes against available databases in settings:

# posthog/product_db_router.py

class ProductDBRouter:
    def __init__(self, routes=None):
        configured_routes = routes if routes is not None else get_product_db_routes()
        self.routes = tuple(
            route for route in configured_routes
            if f"{route.database}_db_writer" in settings.DATABASES
        )

    def db_for_read(self, model, **hints):
        for route in self.routes:
            if route.app_label == model._meta.app_label:
                suffix = "_db_writer" if settings.TEST else "_db_reader"
                return f"{route.database}{suffix}"
        return None

Models in configured product apps automatically read from <product>_db_reader (or writer in tests) and write to <product>_db_writer.

ClickHouse Bypass: Custom Client Execution

ClickHouse operates completely outside Django's database routing because it is not a Django-managed database. Instead, analytics queries use the custom ClickHouse client located in posthog/clickhouse/client/:

# posthog/clickhouse/client/execute.py

def sync_execute(sql, args=None, settings=None):
    # Executes against ClickHouse using CLICKHOUSE_HOST/CLICKHOUSE_DATABASE

    # from settings.DATA_STORES, bypassing Django ORM entirely

    pass

All funnel, trend, and session queries import sync_execute or async_execute directly. The client connects using CLICKHOUSE_HOST, CLICKHOUSE_DATABASE, and related settings defined at the bottom of posthog/settings/data_stores.py.

Practical Routing Examples

Routing Reads to the Aurora Replica

# With READ_REPLICA_OPT_IN=Event in environment

from posthog.models.event import Event

def get_recent_events(team):
    # SELECT ... FROM "replica"."posthog_event"

    return Event.objects.filter(team=team).order_by("-timestamp")[:100]

Writing to the Dedicated Persons Database

from posthog.models.person.person import Person

def create_person(team, distinct_ids):
    # INSERT INTO "persons_db_writer"."posthog_person"

    return Person.objects.create(team=team, distinct_ids=distinct_ids)

Querying Product-Specific Models

# Model in products/surveys/backend/models.py

from products.surveys.backend.models import Survey

def list_surveys(team):
    # SELECT ... FROM "surveys_db_reader"."surveys_survey"

    return Survey.objects.filter(team=team)

Executing ClickHouse Analytics

from posthog.clickhouse.client import sync_execute

def get_event_count(team_id):
    sql = "SELECT count(*) FROM events WHERE team_id = %(team_id)s"
    result = sync_execute(sql, {"team_id": team_id})
    return result[0][0]

Summary

• PostgreSQL routing uses three Django database routers (ReplicaRouter, PersonDBRouter, ProductDBRouter) registered in settings.DATA_STORES.DATABASE_ROUTERS to distribute ORM queries across default, replica, persons_db_*, and <product>_db_* aliases.
• Router precedence follows list order: product-specific routes override person routes, which override replica routing.
• Person table isolation automatically routes models named in PERSONS_DB_MODELS to dedicated writer/reader PostgreSQL instances.
• Product database scaling loads routing configuration from YAML and dynamically creates database aliases for per-app isolation.
• ClickHouse separation bypasses Django entirely; analytics queries execute through posthog.clickhouse.client.sync_execute using independent connection settings.

Frequently Asked Questions

How does PostHog decide which PostgreSQL database to use for a query?

Django evaluates the db_for_read or db_for_write methods of each router in DATABASE_ROUTERS sequentially. The first router returning a database alias string (e.g., "replica", "persons_db_reader") wins. If all routers return None, Django falls back to the default database. PostHog registers ProductDBRouter first, PersonDBRouter second, and ReplicaRouter last, ensuring person data isolation takes priority over replica optimization.

Why doesn't ClickHouse use Django database routing?

ClickHouse is not a Django-supported database backend. PostHog accesses ClickHouse through a custom Python client (posthog.clickhouse.client.execute) that manages its own connections using CLICKHOUSE_HOST and CLICKHOUSE_DATABASE settings. This design intentionally separates the high-volume analytics store from Django's ORM transaction management and migration system.

How can I configure a model to use the read replica?

Set the READ_REPLICA_OPT_IN environment variable to a comma-separated list of model class names (e.g., Event,Person). Alternatively, set it to ALL_MODELS_USE_READ_REPLICA to route all read queries to the replica. The ReplicaRouter checks this list in its db_for_read method; writes always route to default regardless of this setting.

What is the purpose of the persons_db_writer and persons_db_reader aliases?

These aliases point to a dedicated PostgreSQL instance (or cluster) specifically for the persons table and related models (distinct IDs, person overrides). Because person lookups and updates are extremely write-heavy, isolating them from the main default database prevents performance degradation of other application features. The PersonDBRouter automatically handles the read/write splitting, using the writer in test/debug environments to ensure data consistency.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}