qshape

README

qShape

AST-level canonicalization and fingerprinting of PostgreSQL queries, plus a pg_stat_statements capture command that ranks workload by execution time, not just call count.

pg_stat_statements.queryid hashes the parse tree after literal-to-$N substitution but before any AST normalization. ORMs (Rails, ActiveRecord, SQLAlchemy, Prisma, Sequelize) produce many queryids for one logical query shape. qshape collapses those variants to a single canonical fingerprint and aggregates their timing.

qShape is part of the boringSQL stack alongside RegreSQL, Fixturize and dryrun.

Measured corpus reduction

Typical reduction on an ORM-heavy pg_stat_statements snapshot is 30–50%. Enough that per-shape timing aggregates stop fragmenting across near-duplicate queryids. The upper bound depends on how much of the workload is alias / predicate-order variation vs. genuinely distinct shapes.

On one real production snapshot we measured 4,716 distinct queryids to 177 canonical fingerprints (96.2% reduction) — an outlier, driven by heavy ORM alias churn on a small set of underlying query shapes. See reshape.go for the AST transformations that drive the collapse.

Install

Homebrew:

brew install boringsql/boringsql/qshape

From source:

go install github.com/boringsql/qshape/cmd/qshape@latest

Pre-built binaries for macOS and Linux (amd64 + arm64) are published on each release via GoReleaser.

CLI

qshape normalize "SELECT u.id FROM users u WHERE u.id = 1"
SELECT.id FROM users u WHERE id = 1

qshape fingerprint "SELECT id FROM users WHERE id = 1"
sha1:63fe28385e8b4d95

qshape capture "postgres://user:pass@host/db" > queries.json

qshape attribute --in clusters.json --conn "$DATABASE_URL" > queries-attributed.json

capture connects directly to a PostgreSQL node and reads pg_stat_statements with original whitespace and timing intact, then writes {"clusters":[...]} to stdout sorted by descending total_exec_time_ms. Passing - (or omitting the SQL arg) to normalize / fingerprint reads stdin.

qshape also harvests structured comments (sqlcommenter, marginalia, sqlc / boringSQL headers, bare comments) into per-cluster owners and regresql_meta fields.

Learn more

• boringSQL, the blog and project home
• RegreSQL, regression testing for PostgreSQL queries
• Fixturize and dryrun, companion tools in the suite

License

BSD 2-Clause.

Documentation

Index

• func Fingerprint(sql string) (string, error)
• func Normalize(sql string) (string, error)
• type Cluster
  • func Group(queries []Query) ([]Cluster, error)
  • func GroupWithPolicy(queries []Query, policy *tags.Policy) ([]Cluster, error)
• type DynamicKeyObservation
• type ParamAttribution
• type Query

Functions

func Fingerprint

func Fingerprint(sql string) (string, error)

func Normalize

func Normalize(sql string) (string, error)

Types

type Cluster

type Cluster struct {
	Fingerprint     string                  `json:"fingerprint"`
	Canonical       string                  `json:"canonical"`
	Members         []Query                 `json:"members"`
	TotalCalls      int64                   `json:"total_calls"`
	TotalExecTimeMs float64                 `json:"total_exec_time_ms,omitempty"`
	MeanExecTimeMs  float64                 `json:"mean_exec_time_ms,omitempty"`
	Rows            int64                   `json:"rows,omitempty"`
	Params          []ParamAttribution      `json:"params,omitempty"`
	Owners          map[string]string       `json:"owners,omitempty"`
	RegresqlMeta    map[string]string       `json:"regresql_meta,omitempty"`
	DynamicTagKeys  []DynamicKeyObservation `json:"dynamic_tag_keys,omitempty"`
}

func Group

func Group(queries []Query) ([]Cluster, error)

Group clusters queries by canonical fingerprint. Queries that fail to parse become singleton clusters with empty Fingerprint. Output is sorted by descending TotalExecTimeMs (when any timing is present), otherwise by descending TotalCalls, with Fingerprint as the tiebreaker. Each cluster's Members are sorted by QueryID, then Raw, and Canonical is the normalized form of the first member after that sort.

func GroupWithPolicy

func GroupWithPolicy(queries []Query, policy *tags.Policy) ([]Cluster, error)

type DynamicKeyObservation

type DynamicKeyObservation struct {
	Key                  string `json:"key"`
	ValueCardinalitySeen int    `json:"value_cardinality_seen"`
}

type ParamAttribution

type ParamAttribution struct {
	Position   int    `json:"position"`
	Schema     string `json:"schema,omitempty"`
	Table      string `json:"table,omitempty"`
	Column     string `json:"column,omitempty"`
	Confidence string `json:"confidence"`
	Note       string `json:"note,omitempty"`
}

type Query

type Query struct {
	Raw              string  `json:"raw"`
	QueryID          int64   `json:"queryid,omitempty"`
	Calls            int64   `json:"calls,omitempty"`
	TotalExecTimeMs  float64 `json:"total_exec_time_ms,omitempty"`
	MeanExecTimeMs   float64 `json:"mean_exec_time_ms,omitempty"`
	StddevExecTimeMs float64 `json:"stddev_exec_time_ms,omitempty"`
	Rows             int64   `json:"rows,omitempty"`
}