Skip to content
Skills Library
Data & analytics · Analysis

querying-local-postgres

/querying-local-postgres

Run read-only SQL against the local Postgres app database (SELECT, EXPLAIN, EXPLAIN ANALYZE on SELECT). Default local URL postgres://posthog:posthog@localhost:5432/posthog; else DATABASE_URL. Use when querying the local DB, inspecting tables, debugging data, or analyzing query plans. Mutations are strictly forbidden.

Repository
posthog ↗
Licence
MIT
Last repository change
26.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 26.10.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Run read-only SQL against the local Postgres app database (SELECT, EXPLAIN, EXPLAIN ANALYZE on SELECT). Default local URL postgres://posthog:posthog@localhost:5432/posthog; else DATABASE_URL. Use when querying the local DB, inspecting tables, debugging data, or analyzing query plans. Mutations are strictly forbidden.

Original author description

Run read-only SQL against the local Postgres app database (SELECT, EXPLAIN, EXPLAIN ANALYZE on SELECT). Default local URL postgres://posthog:posthog@localhost:5432/posthog; else DATABASE_URL. Use when querying the local DB, inspecting tables, debugging data, or analyzing query plans. Mutations are strictly forbidden.

How it works

  1. When to use
  2. Instructions
  3. Mutations strictly forbidden
  4. EXPLAIN and EXPLAIN ANALYZE (performance)
  5. PostHog: connection and DATABASEURL
  6. Usage (command pattern)
  7. Schema reference (PostHog)
  8. Debugging with the query runner (PostHog-flavored)
  9. Cross-reference
Original SKILL.md headings · 162 lines, 1,249 words
SKILL.md preview
---
name: querying-local-postgres
description: Run read-only SQL against the local Postgres app database (SELECT, EXPLAIN, EXPLAIN ANALYZE on SELECT). Default local URL postgres://posthog:posthog@localhost:5432/posthog; else DATABASE_URL. Use when querying the local DB, inspecting tables, debugging data, or analyzing query plans. Mutations are strictly forbidden.
allowed-tools: Bash
---

# Querying local Postgres (READ-ONLY) — PostHog repo

User's query: $ARGUMENTS

**Scope:** This repo uses **PostgreSQL** for app metadata (teams, projects, flags, Django models, etc.). **Analytics event data** lives in **ClickHouse**, not Postgres — use HogQL / ClickHouse tools for `events`-style questions unless the user explicitly wants Postgres.

## When to use

- User asks to query the database, inspect tables, or run SQL against **Postgres**
- **Debugging**: Row-level checks (e.g. why a team/project/flag row looks wrong), migrations, constraints, duplicate keys
- **Performance**: `EXPLAIN` / `EXPLAIN (ANALYZE, …)` on **read-only `SELECT`** against Django or app tables

## Instructions

1. **Strictly forbid mutations** — See "Mutations strictly forbidden" below. If the user asks for any write or mutation, refuse and explain the skill is read-only.
2. **Translate** the user's question into one or more read-only SQL statements.
3. **Show the SQL** in a code block before running.
4. **Run** using the command pattern below (always with `PGOPTIONS='-c default_transaction_read_only=on'` to force a read-only connection).
5. **Show results** and give a brief interpretation (especially when used for debugging or plan review).

## Mutations strictly forbidden

**Do not run, suggest, or generate any of the following.** Refuse and state that this skill is read-only.

- **DML**: `INSERT`, `UPDATE`, `DELETE`, `MERGE`, `TRUNCATE`
- **DDL**: `CREATE`, `DROP`, `ALTER`, `RENAME`
- **Other writes**: `COPY ... TO program`, `CALL` (if it mutates), `GRANT`/`REVOKE`
- **`EXPLAIN ANALYZE` on anything other than a read-only `SELECT`** (including `WITH … SELECT`). Do not wrap DML in `EXPLAIN ANALYZE` — it would execute the write. The read-only connection below rejects writes, but the agent must not attempt this pattern.
- Any statement that modifies data, schema, or roles

**Allowed:**

- `SELECT` (including `WITH … SELECT`)
- `EXPLAIN` … `SELECT` (estimate-only plan; no execution)
- `EXPLAIN (ANALYZE, …) SELECT` — **executes** the `SELECT` once; use only for performance analysis. Must run on the read-only connection below.
- `SHOW`, `SELECT` from catalog views (`pg_stat_*`, `information_schema`, etc.) when read-only

If the user requests a write operation, say: "This skill is read-only. I can't run INSERT/UPDATE/DELETE or other mutations. Use a DB client or migration tool for writes."

## EXPLAIN and EXPLAIN ANALYZE (performance)

| Goal                                      | What to use                                                                     |
| ----------------------------------------- | ------------------------------------------------------------------------------- |
| Plan shape, estimated costs, no execution | `EXPLAIN (FORMAT TEXT, COSTS)` or add `VERBOSE`                                 |
| Actual timings, row counts, buffer hits   | `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` on the **`SELECT`**                   |
| Buffer + WAL stats                        | `BUFFERS` requires **`ANALYZE`**; `WAL` requires **`ANALYZE`** (PostgreSQL 13+) |

**Safe pattern:** the analyzed statement must be **only** a `SELECT` (or `WITH … SELECT`), run on the read-only connection (see Usage below). Example:

```bash
PGOPTIONS='-c default_transaction_read_only=on' psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT … LIMIT 100;"
```

Optional flags (when useful): `SETTINGS` (show non-default GUCs), `WAL` (with `ANALYZE`), `TIMING` (default on in recent versions for `ANALYZE`).

**Caveats:**

- **`EXPLAIN ANALYZE` runs the query** — can be slow or heavy on large scans; prefer a bounded `SELECT` (e.g. realistic `WHERE`, `LIMIT` matching production shape) when exploring.
- **Production / shared DBs** — analyzing hot or wide queries can add load; prefer staging, a replica, or off-peak when the user cares about impact.
- **`EXPLAIN` without `ANALYZE`** — does not execute the inner statement (except some special cases); still only wrap **read-only** SQL.

## PostHog: connection and `DATABASE_URL`

### Local Postgres (host machine) — default for this skill

Use this **hardcoded** URL for day-to-day local queries (matches typical Docker Compose + port `5432` on localhost, SSL off):

| Setting  | Value       |
| -------- | ----------- |
| Host     | `localhost` |
| Port     | `5432`      |
| User     | `posthog`   |
| Password | `posthog`   |
| Database | `posthog`   |
| SSL      | off         |

```bash
# Prefer this unless the user says their local password/db differs
LOCAL_POSTGRES_URL='postgres://posthog:posthog@localhost:5432/posthog'
```

Equivalent: `postgresql://posthog:posthog@localhost:5432/posthog`

**Other local DBs** on the same server: swap the path only, e.g. `...5432/posthog_persons`.

---

**Configuration source of truth (app):** `posthog/settings/data_stores.py` (Django `DATABASES`, optional replica `POSTHOG_POSTGRES_READ_HOST`, direct `POSTHOG_POSTGRES_DIRECT_HOST`, `PERSONS_DB_WRITER_URL`, product DB routing from `products/db_routing.yaml`).

**When not using the hardcoded URL:** Connecting **from the host** with the same credentials is documented in [Developing locally](../../../docs/published/handbook/engineering/developing-locally.md) (`fe_sendauth` troubleshooting). Ensure containers are running.

**Default env when `DEBUG` is on:** Django builds a default `DATABASE_URL` from `PGHOST` (default **`db`**), `PGUSER` / `PGPASSWORD`, `PGPORT`, `PGDATABASE` — matching **in-container** hostnames. From the **host**, use `localhost` and the same user/password/database name unless your shell already exports `DATABASE_URL`.

**Multiple PostgreSQL databases** (same server in local compose; separate logical DBs):

- Main app DB: usually `posthog`
- Persons DB: `posthog_persons` (`PERSONS_DB_WRITER_URL` / `PERSONS_DB_READER_URL`)
- Product-isolated DBs: `posthog_<name>` per `products/db_routing.yaml` (created by `docker/postgres-init-scripts/create-product-dbs.sh`)
- Other init scripts may create additional DBs (e.g. cyclotron) — inspect `docker/postgres-init-scripts/` if needed

Point `psql` at the right database by changing the path in `DATABASE_URL` (e.g. `.../posthog_persons`).

**Rust / sqlx:** Some services use `rust/.env` for `DATABASE_URL` when working from `posthog/rust` — see `rust/README.md`.

## Usage (command pattern)

**Always** force the connection read-only via `PGOPTIONS='-c default_transaction_read_only=on'` so Postgres rejects writes even if the generated SQL is wrong.

> **Why `PGOPTIONS`, not `SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY`?**
> A `psql -c "..."` string with multiple statements runs as a **single implicit transaction**.
> `SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY` only sets the default for _subsequent_
> transactions — the in-progress one keeps the read-write mode it was given at `BEGIN`, so a write
> in the same `-c` would **not** be rejected. `PGOPTIONS='-c default_transaction_read_only=on'` sets
> the GUC at connection startup, so every transaction (including the implicit `-c` one) starts
> read-only. The inline equivalent is `SET TRANSACTION READ ONLY;` **as the first statement** of the
> `-c` string (it affects the current transaction, unlike `SET SESSION CHARACTERISTICS`).

**Run from the PostHog repo root** so relative env paths resolve.

**Default — local hardcoded URL** (`posthog` / `posthog` @ `localhost:5432` / db `posthog`):

```bash
PGOPTIONS='-c default_transaction_read_only=on' psql "postgres://posthog:posthog@localhost:5432/posthog" -v ON_ERROR_STOP=1 -c "SELECT 1;"
```

**Option A — `DATABASE_URL` already in the shell** (e.g. after `flox activate` or manual `export`):

```bash
PGOPTIONS='-c default_transaction_read_only=on' psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "SELECT 1;"
```

**Option B — load from a gitignored env file at repo root** (if `DATABASE_URL` is set there):

```bash
npx dotenv -e .env -- bash -c "PGOPTIONS='-c default_transaction_read_only=on' psql \"\$DATABASE_URL\" -c 'SELECT ...'"
```

- Use single quotes for string literals in SQL inside the shell as usual; escape carefully when nesting quotes in `-c`.
- Default `LIMIT 100` unless the user specifies otherwise.
- For wide rows use `-x`: `psql ... -x -c "..."`.

## Schema reference (PostHog)

- **Django models → tables:** see `posthog/models/` (and product packages under `products/`). Table names are usually prefixed with `posthog_` and snake-cased (e.g. `posthog_team`, `posthog_user`). Confirm with `\dt posthog_*` in psql, or check the model's `Meta.db_table` if nonstandard.
- **Migrations:** `posthog/migrations/` (and product migration paths) define the authoritative DDL over time.
- **Person table name:** configurable via `PERSON_TABLE_NAME` (see `data_stores.py`); default `posthog_person`.

## Debugging with the query runner (PostHog-flavored)

- Confirm a row exists for a team, project, user, or feature-flag linkage; check soft-delete / `deleted` fields where applicable.
- Compare counts and joins to what the app assumes (e.g. membership, project access).
- Validate replica vs primary read differences only if the user is connected to the right host (replica: `POSTHOG_POSTGRES_READ_HOST`).
- Use `EXPLAIN ANALYZE` on `SELECT` for slow Django queries **replicated as SQL** — mind loading production-sized data.

## Cross-reference

- Local setup and DB gotchas: `docs/published/handbook/engineering/developing-locally.md`
- Repo CLI: `hogli` (see `.agents/skills/hogli/SKILL.md`)

Versions

The database has no other skills named querying-local-postgres.

Identical content appears in 1 other GitHub folders. We show one copy with the highest score.

Database snapshot: 13.11.2026 · package version 75bc44e002cf (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100postgres
    Use when user references PostgreSQL/Postgres, postgres:// or postgresql:// connection strings, asks for ad-hoc SELECT, EXPLAIN/ANALYZE plans, schema/index/lock inspection, or slow-query investigation. Read-only by default. Multi-database via PG_PROFILE.
  • 100querying-production-databases
    Default path for read-only queries against PostHog's production ClickHouse and Postgres in the US and EU regions. Uses the direct-connect SQL sources in the internal projects, through the PostHog MCP on US team 2. US sources run with `execute-sql` and a `connectionId`. EU…
  • 100postgres-query
    Run PostgreSQL queries for testing, debugging, and performance analysis. Use when you need to query the database directly, run EXPLAIN ANALYZE, compare query results, or test SQL optimizations. Always uses read-only connections unless explicitly directed otherwise.
  • 100postgres
    Execute read-only SQL queries against multiple PostgreSQL databases. Use when: (1) querying PostgreSQL databases, (2) exploring database schemas/tables, (3) running SELECT queries for data analysis, (4) checking database contents. Supports multiple database connections with…
  • 100querying-production-databases-via-metabase
    Runs read-only production database analysis through PostHog's internal Metabase instances. Use for ClickHouse query logs, slow query cost, Postgres query plans, index selection, or tenant-size analysis. Covers US and EU database discovery, SSO login through `hogli`, safe query…
  • 100postgres-query
    Run PostgreSQL queries for testing, debugging, and performance analysis. Use when you need to query the database directly, run EXPLAIN ANALYZE, compare query results, or test SQL optimizations. Always pass a target — `--prod` or `--dev` — because prod and dev are different…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.