• Stars
    star
    1,491
  • Rank 30,062 (Top 0.7 %)
  • Language
    Ruby
  • License
    MIT License
  • Created almost 7 years ago
  • Updated 10 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

The automatic indexer for Postgres

Dexter

The automatic indexer for Postgres

Read about how it works or watch the talk

Build Status

Installation

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.0.tar.gz | tar xz
cd hypopg-1.4.0
make
make install # may need sudo

And enable it in databases where you want to use Dexter:

CREATE EXTENSION hypopg;

See the installation notes if you run into issues.

Then install the command line tool with:

gem install pgdexter

The command line tool is also available with Docker, Homebrew, or as a Linux package.

How to Use

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

Connection Options

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.

Collecting Queries

Dexter can collect queries from a number of sources.

Query Stats

Enable pg_stat_statements in your database.

CREATE EXTENSION pg_stat_statements;

And use:

dexter <connection-options> --pg-stat-statements

Live Queries

Get live queries from the pg_stat_activity view with:

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

Log Files

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 csvlog or --input-format jsonlog if needed.

SQL Files

Pass a SQL file with:

dexter <connection-options> queries.sql

Pass a single query with:

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

Collection Options

To prevent one-off queries from being indexed, specify a minimum number of calls before a query is considered for indexing

dexter --min-calls 100

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

Analyze

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:

dexter --analyze

Tables

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

Debugging

See how Dexter is processing queries with:

dexter --log-sql --log-level debug2

Hosted Postgres

The hypopg extension, which Dexter needs to run, is available on these providers.

For other providers, see this guide. To request a new extension:

  • Google Cloud SQL - vote or comment on this page
  • DigitalOcean Managed Databases - vote or comment on this page

HypoPG Installation Notes

Postgres Location

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)

Missing Header

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-15

Note: Replace 15 with your Postgres server version

Additional Installation Methods

Docker

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 Docker 20.04+ and --add-host=host.docker.internal:host-gateway).

Homebrew

With Homebrew, you can use:

brew install dexter

Future Work

Here are some ideas

Upgrading

Run:

gem install pgdexter

To use master, run:

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

Upgrade Notes

0.5.0

The --stdin option is now required to read queries from stdin.

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

Thanks

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.

Research

This is known as the Index Selection Problem (ISP).

Contributing

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

More Repositories

1

pghero

A performance dashboard for Postgres
Ruby
7,123
star
2

searchkick

Intelligent search made easy
Ruby
6,257
star
3

chartkick

Create beautiful JavaScript charts with one line of Ruby
Ruby
6,157
star
4

ahoy

Simple, powerful, first-party analytics for Rails
Ruby
3,872
star
5

blazer

Business intelligence made simple
Ruby
3,766
star
6

strong_migrations

Catch unsafe migrations in development
Ruby
3,662
star
7

groupdate

The simplest way to group temporal data
Ruby
3,617
star
8

pgsync

Sync data from one Postgres database to another
Ruby
2,787
star
9

the-ultimate-guide-to-ruby-timeouts

Timeouts for popular Ruby gems
Ruby
2,212
star
10

production_rails

Best practices for running Rails in production
1,975
star
11

lockbox

Modern encryption for Ruby and Rails
Ruby
1,290
star
12

chartkick.js

Create beautiful charts with one line of JavaScript
JavaScript
1,211
star
13

react-chartkick

Create beautiful JavaScript charts with one line of React
JavaScript
1,183
star
14

pretender

Log in as another user in Rails
Ruby
1,124
star
15

ahoy_email

First-party email analytics for Rails
Ruby
1,051
star
16

secure_rails

Rails security best practices
954
star
17

pgslice

Postgres partitioning as easy as pie
Ruby
953
star
18

mailkick

Email subscriptions for Rails
Ruby
847
star
19

vue-chartkick

Create beautiful JavaScript charts with one line of Vue
JavaScript
747
star
20

eps

Machine learning for Ruby
Ruby
609
star
21

awesome-legal

Awesome free legal documents for companies
589
star
22

searchjoy

Search analytics made easy
Ruby
579
star
23

polars-ruby

Blazingly fast DataFrames for Ruby
Ruby
563
star
24

torch.rb

Deep learning for Ruby, powered by LibTorch
Ruby
552
star
25

blind_index

Securely search encrypted database fields
Ruby
470
star
26

safely

Rescue and report exceptions in non-critical code
Ruby
470
star
27

authtrail

Track Devise login activity
Ruby
466
star
28

ahoy.js

Simple, powerful JavaScript analytics
JavaScript
463
star
29

multiverse

Multiple databases for Rails 🎉
Ruby
463
star
30

hightop

A nice shortcut for group count queries
Ruby
462
star
31

field_test

A/B testing for Rails
Ruby
460
star
32

s3tk

A security toolkit for Amazon S3
Python
439
star
33

disco

Recommendations for Ruby and Rails using collaborative filtering
Ruby
431
star
34

active_median

Median and percentile for Active Record, Mongoid, arrays, and hashes
Ruby
427
star
35

informers

State-of-the-art natural language processing for Ruby
Ruby
417
star
36

notable

Track notable requests and background jobs
Ruby
402
star
37

shorts

Short, random tutorials and posts
379
star
38

tensorflow-ruby

Deep learning for Ruby
Ruby
350
star
39

distribute_reads

Scale database reads to replicas in Rails
Ruby
328
star
40

slowpoke

Rack::Timeout enhancements for Rails
Ruby
327
star
41

prophet-ruby

Time series forecasting for Ruby
Ruby
321
star
42

rover

Simple, powerful data frames for Ruby
Ruby
311
star
43

groupdate.sql

The simplest way to group temporal data
PLpgSQL
280
star
44

kms_encrypted

Simple, secure key management for Lockbox and attr_encrypted
Ruby
235
star
45

jetpack

A friendly package manager for R
R
234
star
46

neighbor

Nearest neighbor search for Rails and Postgres
Ruby
230
star
47

rollup

Rollup time-series data in Rails
Ruby
230
star
48

hypershield

Shield sensitive data in Postgres and MySQL
Ruby
227
star
49

logstop

Keep personal data out of your logs
Ruby
218
star
50

pdscan

Scan your data stores for unencrypted personal data (PII)
Go
213
star
51

delete_in_batches

Fast batch deletes for Active Record and Postgres
Ruby
202
star
52

vega-ruby

Interactive charts for Ruby, powered by Vega and Vega-Lite
Ruby
192
star
53

mapkick

Create beautiful JavaScript maps with one line of Ruby
Ruby
173
star
54

dbx

A fast, easy-to-use database library for R
R
171
star
55

fastText-ruby

Efficient text classification and representation learning for Ruby
Ruby
162
star
56

autosuggest

Autocomplete suggestions based on what your users search
Ruby
162
star
57

swipeout

Swipe-to-delete goodness for the mobile web
JavaScript
159
star
58

pghero.sql

Postgres insights made easy
PLpgSQL
154
star
59

mainstreet

Address verification for Ruby and Rails
Ruby
149
star
60

or-tools-ruby

Operations research tools for Ruby
Ruby
139
star
61

mapkick.js

Create beautiful, interactive maps with one line of JavaScript
JavaScript
138
star
62

trend-ruby

Anomaly detection and forecasting for Ruby
Ruby
128
star
63

mitie-ruby

Named-entity recognition for Ruby
Ruby
122
star
64

barkick

Barcodes made easy
Ruby
120
star
65

ownership

Code ownership for Rails
Ruby
111
star
66

anomaly

Easy-to-use anomaly detection for Ruby
Ruby
98
star
67

errbase

Common exception reporting for a variety of services
Ruby
87
star
68

tokenizers-ruby

Fast state-of-the-art tokenizers for Ruby
Rust
81
star
69

ip_anonymizer

IP address anonymizer for Ruby and Rails
Ruby
79
star
70

str_enum

String enums for Rails
Ruby
75
star
71

faiss-ruby

Efficient similarity search and clustering for Ruby
C++
73
star
72

trend-api

Anomaly detection and forecasting API
R
71
star
73

archer

Rails console history for Heroku, Docker, and more
Ruby
70
star
74

onnxruntime-ruby

Run ONNX models in Ruby
Ruby
70
star
75

xgboost-ruby

High performance gradient boosting for Ruby
Ruby
69
star
76

secure-spreadsheet

Encrypt and password protect sensitive CSV and XLSX files
JavaScript
66
star
77

active_hll

HyperLogLog for Rails and Postgres
Ruby
66
star
78

guess

Statistical gender detection for Ruby
Ruby
60
star
79

morph

An encrypted, in-memory, key-value store
C++
59
star
80

lightgbm

High performance gradient boosting for Ruby
Ruby
56
star
81

midas-ruby

Edge stream anomaly detection for Ruby
Ruby
54
star
82

moves

Ruby client for Moves
Ruby
54
star
83

blingfire-ruby

High speed text tokenization for Ruby
Ruby
54
star
84

vowpalwabbit-ruby

Fast online machine learning for Ruby
Ruby
52
star
85

xlearn-ruby

High performance factorization machines for Ruby
Ruby
51
star
86

tomoto-ruby

High performance topic modeling for Ruby
C++
51
star
87

trove

Deploy machine learning models in Ruby (and Rails)
Ruby
50
star
88

ahoy_events

Simple, powerful event tracking for Rails
Ruby
42
star
89

mapkick-static

Create beautiful static maps with one line of Ruby
Ruby
42
star
90

practical-search

Let’s make search a better experience for our users
40
star
91

breakout-ruby

Breakout detection for Ruby
Ruby
40
star
92

plu

Price look-up codes made easy
Ruby
40
star
93

ngt-ruby

High-speed approximate nearest neighbors for Ruby
Ruby
39
star
94

gindex

Concurrent index migrations for Rails
Ruby
39
star
95

clockwork_web

A web interface for Clockwork
Ruby
38
star
96

ahoy_guide

A foundation of knowledge and libraries for solid analytics
38
star
97

notable_web

A web interface for Notable
HTML
36
star
98

AnomalyDetection.rb

Time series anomaly detection for Ruby
Ruby
34
star
99

khiva-ruby

High-performance time series algorithms for Ruby
Ruby
34
star
100

immudb-ruby

Ruby client for immudb, the immutable database
Ruby
34
star