The automatic indexer for Postgres
Read about how it works or watch the talk
First, install HypoPG on your database server. This doesn’t require a restart.
cd /tmp curl -L https://github.com/HypoPG/hypopg/archive/1.4.2.tar.gz | tar xz cd hypopg-1.4.2 make make install # may need sudo
And enable it in databases where you want to use Dexter:
See the installation notes if you run into issues.
Then install the command line tool with:
The command line tool is also available with Docker, Homebrew, or as a Linux package.
Dexter needs a connection to your database and a source of queries (like pg_stat_statements) to process.
dexter -d dbname --pg-stat-statements
This finds slow queries and generates output like:
Started
Processing 189 new query fingerprints
Index found: public.genres_movies (genre_id)
Index found: public.genres_movies (movie_id)
Index found: public.movies (title)
Index found: public.ratings (movie_id)
Index found: public.ratings (rating)
Index found: public.ratings (user_id)
To be safe, Dexter will not create indexes unless you pass the --create flag. In this case, you’ll see:
Index found: public.ratings (user_id)
Creating index: CREATE INDEX CONCURRENTLY ON "public"."ratings" ("user_id")
Index created: 15243 ms
Dexter supports the same connection options as psql.
-h host -U user -p 5432 -d dbname
This includes URIs:
postgresql://user:pass@host:5432/dbname
and connection strings:
host=localhost port=5432 dbname=mydb
Always make sure your connection is secure when connecting to a database over a network you don’t fully trust.
Dexter can collect queries from a number of sources.
Enable pg_stat_statements in your database.
CREATE EXTENSION pg_stat_statements;
And use:
dexter <connection-options> --pg-stat-statements
Get live queries from the pg_stat_activity view with:
dexter <connection-options> --pg-stat-activity
Enable logging for slow queries in your Postgres config file.
log_min_duration_statement = 10 # ms
And use:
dexter <connection-options> postgresql.log
Supports stderr, csvlog, and jsonlog formats.
For real-time indexing, pipe your logfile:
tail -F -n +1 postgresql.log | dexter <connection-options> --stdin
And pass --input-format csv or --input-format json if needed.
Pass a SQL file with:
dexter <connection-options> queries.sql
Pass a single query with:
dexter <connection-options> -s "SELECT * FROM ..."
To prevent one-off queries from being indexed, specify a minimum number of calls before a query is considered for indexing
You can do the same for total time a query has run
dexter --min-time 10 # minutes
When streaming logs, specify the time to wait between processing queries
dexter --interval 60 # seconds
For best results, make sure your tables have been recently analyzed so statistics are up-to-date. You can ask Dexter to analyze tables it comes across that haven’t been analyzed in the past hour with:
You can exclude large or write-heavy tables from indexing with:
dexter --exclude table1,table2
Alternatively, you can specify which tables to index with:
dexter --include table3,table4
See how Dexter is processing queries with:
dexter --log-sql --log-level debug2
The hypopg extension, which Dexter needs to run, is available on these providers.
For other providers, see this guide. To request a new extension:
If your machine has multiple Postgres installations, specify the path to pg_config with:
export PG_CONFIG=/Applications/Postgres.app/Contents/Versions/latest/bin/pg_config
Then re-run the installation instructions (run make clean before make if needed)
If compilation fails with fatal error: postgres.h: No such file or directory, make sure Postgres development files are installed on the server.
For Ubuntu and Debian, use:
sudo apt-get install postgresql-server-dev-17
Note: Replace 17 with your Postgres server version
Get the Docker image with:
docker pull ankane/dexter
And run it with:
docker run -ti ankane/dexter <connection-options>
For databases on the host machine, use host.docker.internal as the hostname (on Linux, this requires --add-host=host.docker.internal:host-gateway).
With Homebrew, you can use:
Run:
To use master, run:
gem install specific_install gem specific_install https://github.com/ankane/dexter.git
This software wouldn’t be possible without HypoPG, which allows you to create hypothetical indexes, and pg_query, which allows you to parse and fingerprint queries. A big thanks to Dalibo and Lukas Fittl respectively. Also, thanks to YugabyteDB for this article on how to explain normalized queries.
This is known as the Index Selection Problem (ISP).
View the changelog
Everyone is encouraged to help improve this project. Here are a few ways you can help:
To get started with development, run:
git clone https://github.com/ankane/dexter.git cd dexter bundle install bundle exec rake install
To run tests, use:
createdb dexter_test bundle exec rake test
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4