# Dexter: automatic Postgres index recommendations and the HypoPG dependency

> Dexter reads a Postgres query workload and recommends indexes by running candidates through HypoPG's hypothetical planner. It proposes without committing by default, and it cannot run at all on managed databases where HypoPG is not available.

**ankane/dexter** — The automatic indexer for Postgres

- Repository: https://github.com/ankane/dexter
- Stars: 2,096 · Forks: 57
- Language: Ruby
- License: MIT
- Published: 2026-10-09 · Updated: 2026-10-09 · Language: en
- Canonical page: https://hysenlabs.com/projects/ankane-dexter

## HypoPG tests indexes before they exist

Dexter solves a specific Postgres problem: deciding which indexes to add to a database without relying on intuition or manual EXPLAIN analysis. It works by connecting to the database, reading a query workload from one of four sources, and running each candidate index through HypoPG before recommending it. HypoPG is a Postgres extension that creates hypothetical indexes, structures visible to the query planner but never written to disk. Dexter asks the planner whether each proposed index would change the execution plan, and only indexes that pass that test appear in the output.

Behind the recommendation engine, two libraries do the underlying work. HypoPG, maintained by Dalibo, provides the hypothetical planner. pg_query, a Ruby binding to Postgres's own parser by Lukas Fittl, parses and fingerprints query text so that identical queries across many sessions are treated as one. This problem has a name in database research: the Index Selection Problem. Dexter brings that research problem to the command line as a practical tool.

Every recommendation Dexter makes has been evaluated by the same planner that will eventually use it. No guessing happens between the query sample and the output. Dexter is aimed at engineers managing Postgres databases with an established query workload: applications running long enough to have accumulated query statistics, and schemas that have moved past the initial table definitions. For a new schema with no query history, the manual approach of writing a targeted EXPLAIN ANALYZE is the more direct path.

## Compiling HypoPG before pgdexter, and two build failures to know about

HypoPG is compiled on the database server. pgdexter is a Ruby gem that runs separately on whatever machine will issue commands. Both components are required, and the order matters: HypoPG must be present before pgdexter can do anything useful.

Building HypoPG 1.4.3 from source:

```sh
cd /tmp
curl -L https://github.com/HypoPG/hypopg/archive/1.4.3.tar.gz | tar xz
cd hypopg-1.4.3
make
make install # may need sudo
```

Enable the extension in each database where Dexter will operate:

```sql
CREATE EXTENSION hypopg;
```

Then install the CLI:

```sh
gem install pgdexter
```

Two documented build failures exist. A machine running multiple Postgres versions can compile against the wrong one. Setting PG_CONFIG beforehand fixes this:

```sh
export PG_CONFIG=/Applications/Postgres.app/Contents/Versions/latest/bin/pg_config
```

Re-run the installation instructions, and if the PG_CONFIG was wrong before, run `make clean` before `make`. If that `fatal error: postgres.h: No such file or directory` error appears, Postgres development headers are not installed on the server. For Ubuntu and Debian:

```sh
sudo apt-get install postgresql-server-dev-18
```

Note: Replace `18` with your Postgres server version.

The command line tool is also available with Docker or Homebrew. Get the Docker image with:

```sh
docker pull ankane/dexter
```

Run it with:

```sh
docker run -ti ankane/dexter <connection-options>
```

For databases on the host machine, use `host.docker.internal` as the hostname. Homebrew users can install with:

```sh
brew install dexter
```

## Four query sources feed Dexter: from live stats to SQL files

Dexter's recommendations are only as good as the query workload it receives, so the input source is the first decision to make. pg_stat_statements is the mode the README uses in its opening example:

```sh
dexter -d dbname --pg-stat-statements
```

With the extension enabled and query history accumulated, Dexter processes fingerprints and prints proposed indexes. A sample run in the README shows 189 new query fingerprints processed and 6 indexes identified: two on genres_movies, one on movies, and three on ratings. pg_stat_statements normalizes queries, so a query run thousands of times appears once with aggregate cost data. Enable it first with `CREATE EXTENSION pg_stat_statements;`.

For live inspection of currently running queries, pg_stat_activity is the alternative:

```sh
dexter <connection-options> --pg-stat-activity
```

Log files address the historical case, when queries have already run. Postgres log output in stderr, csvlog, or jsonlog format is supported. To enable slow-query logging in the Postgres config:

```ini
log_min_duration_statement = 10 # ms
```

For real-time indexing from a running log file:

```sh
tail -F -n +1 postgresql.log | dexter <connection-options> --stdin
```

Pass `--input-format csv` or `--input-format json` when the format is not detected correctly. For testing a known workload, pass a SQL file or a single query:

```sh
dexter <connection-options> queries.sql
```

```sh
dexter <connection-options> -s "SELECT * FROM ..."
```

pg_stat_statements covers the broadest sample on a production database. SQL files are useful when testing what indexes a planned schema change would need before those queries reach production.

## --create commits proposals; running without it changes nothing

By default, Dexter does not modify the database schema. A run without `--create` prints proposed indexes and stops; nothing in the schema changes. Adding the flag turns those proposals into real index builds, and for each one Dexter prints the CREATE INDEX statement it used and records the build duration. The README's sample output shows one build on a table named ratings completing in 15243 ms.

CREATE INDEX CONCURRENTLY builds without locking the table for writes, which matters on a database carrying live traffic. That 15243 ms figure is from the README's sample run on a specific table; other tables and workloads will produce different build times.

Dry-run output lists the table, schema, and column for each proposed index. Review that list before adding `--create`, paying attention to any proposed tables that handle high write volumes. The `--exclude` flag exists to remove such tables from consideration entirely.

For visibility into how Dexter is processing queries, the README documents a debug flag:

```sh
dexter --log-sql --log-level debug2
```

This prints the SQL Dexter uses internally and is useful when the output is unexpected or when queries appear to be filtered by the threshold settings.

## Thresholds and table exclusions keep indexing decisions stable

One-off queries and rarely-run reports should not drive index decisions that affect the common write path. Two threshold flags address this directly.

`--min-calls` sets a floor on how many times a query must have executed before Dexter considers it:

```sh
dexter --min-calls 100
```

`--min-time` sets a floor on total accumulated query duration in minutes:

```sh
dexter --min-time 10 # minutes
```

These two filters combined prevent a rarely-run but expensive query from adding an index that hurts every other operation on that table.

For streaming from a log file, `--interval` controls how often Dexter processes the incoming queue:

```sh
dexter --interval 60 # seconds
```

Table scope is controlled separately from the threshold settings. To exclude write-heavy or oversized tables:

```sh
dexter --exclude table1,table2
```

To narrow Dexter's scope to specific tables only:

```sh
dexter --include table3,table4
```

The `--analyze` flag tells Dexter to run ANALYZE on tables it encounters that have not been analyzed in the past hour. Outdated statistics cause the planner's cost estimates to diverge from reality, so stale statistics can produce misleading index proposals.

## Managed Postgres without HypoPG and the limits of automation

HypoPG is not universally available on managed Postgres services, and without it Dexter cannot function at all. There is no partial mode, no degraded analysis, and no fallback for databases where the extension cannot be loaded.

Issue 44 in the repository tracks which hosted providers support HypoPG. Google Cloud SQL and DigitalOcean Managed Databases are named as providers without support, with links to vote for it on each platform's feature request tracker. Opening issue 44 directly gives the current state, since provider support can change and the README's references to it predate ongoing provider announcements.

For teams where HypoPG is unavailable, the manual path is to query pg_stat_statements directly, identify slow queries by total execution time, and run EXPLAIN ANALYZE on each candidate with and without the proposed index. That process gives the DBA full visibility into each planner decision, at the cost of doing by hand what Dexter automates. Dexter's value is removing that manual loop; without HypoPG, there is no shortcut.

Dexter is a Postgres-only tool. Nothing in the repository targets any other database system.

## No GitHub releases, upgrading from the same install command, and an MIT license

No GitHub releases exist in this repository. CHANGELOG.md at the root is where the version history lives. Upgrading uses the same command as the initial install: `gem install pgdexter`.

To use the master branch before a version is published to RubyGems:

```sh
gem install specific_install
gem specific_install https://github.com/ankane/dexter.git
```

Without tagged releases, there is no version to pin in a dependency lock file. If the pgdexter gem advances and behavior changes, the CHANGELOG is the only record before a change lands in your environment. Anyone depending on reproducible indexing behavior should note the gem version in use and review CHANGELOG.md before any upgrade.

The last push was on 2026-08-15, and the repository is not archived. Dexter ships under the MIT license; LICENSE.txt at the repository root holds the complete terms. ruby:4-alpine is the base image in the Dockerfile, which matches the Docker Hub image tagged ankane/dexter.

## Conclusion

Dexter is worth evaluating for any team running self-managed Postgres with a real query workload. Verify the HypoPG dependency first: if the database provider does not support it, the tool cannot run, and manual query analysis via pg_stat_statements is the only path. For self-hosted databases, the two-step build sequence is the main friction point. Run without --create first, read the proposed index list, and check each proposed table for write load before committing. Without tagged releases, note the installed gem version before any upgrade.

## FAQ

### What is Dexter and what problem does it solve for Postgres?

Dexter is a command-line tool that reads query statistics from a Postgres database and uses HypoPG's hypothetical planner to identify which indexes would speed up slow queries. The problem it addresses is called the Index Selection Problem in database research.

### Does Dexter automatically create indexes in my Postgres database?

Not by default. Running Dexter without the --create flag prints proposed indexes without changing the schema. Pass --create to have it issue CREATE INDEX CONCURRENTLY statements for each proposed index.

### Does Dexter work with managed Postgres services like Google Cloud SQL?

Only if the provider supports the HypoPG extension. Issue 44 in the Dexter repository tracks known provider support. Google Cloud SQL and DigitalOcean Managed Databases are listed as not supporting HypoPG.

### What query sources does Dexter support?

Dexter supports four sources: pg_stat_statements for accumulated query statistics, pg_stat_activity for live queries, Postgres log files in stderr, csvlog, or jsonlog format, and SQL files containing queries you supply directly.

### Does Dexter work with Docker?

The README documents a Docker image at ankane/dexter. Pull it with docker pull ankane/dexter and run it with docker run -ti ankane/dexter followed by connection options. For databases on the host machine, use host.docker.internal as the hostname.

## Sources

- [ankane/dexter on GitHub](https://github.com/ankane/dexter)
- [Issues](https://github.com/ankane/dexter/issues)
- [License: MIT](https://github.com/ankane/dexter/blob/master/LICENSE)
- [README](https://github.com/ankane/dexter/blob/master/README.md)

---

Hysen Labs editorial analysis, written from the project's own repository and release notes. Cite the canonical page: https://hysenlabs.com/projects/ankane-dexter