# Django Database Routing for PostgreSQL and ClickHouse Separation in PostHog > Explore Django database routing for PostgreSQL and ClickHouse separation in PostHog. Learn how PostHog uses specialized routers and a custom client to manage diverse data needs efficiently. - Repository: [PostHog/posthog](https://github.com/PostHog/posthog) - Tags: internals - Published: 2026-04-25 --- **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`](https://github.com/PostHog/posthog/blob/main/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`](https://github.com/PostHog/posthog/blob/main/posthog/settings/data_stores.py), inserting routers with specific priority: ```python # 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`](https://github.com/PostHog/posthog/blob/main/posthog/dbrouter.py), the **ReplicaRouter** enables offloading read queries to an Aurora read replica for specific models: ```python # 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`](https://github.com/PostHog/posthog/blob/main/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: ```python # 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`](https://github.com/PostHog/posthog/blob/main/posthog/product_db_config.py): ```python # 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: ```python # 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 `_db_reader` (or writer in tests) and write to `_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/`: ```python # 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`](https://github.com/PostHog/posthog/blob/main/posthog/settings/data_stores.py). ## Practical Routing Examples ### Routing Reads to the Aurora Replica ```python # 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 ```python 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 ```python # 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 ```python 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 `_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.