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

• Constants
• func CostKey(sql string) (string, error)
• 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
• type SortProfile
  • func SortProfiles(clusters []Cluster) []SortProfile
• type SortVariant

Constants

const CostKeyPrefix = "qsort1:"

CostKeyPrefix marks a sort-insensitive cost key. Deliberately not "sha1:" so joining a cost key against a fingerprint column returns empty rather than a false match. Own version digit, independent of GroupingVersion.

const GroupingVersion = 1

GroupingVersion identifies the clustering and fingerprinting rules. Bump it in any change that moves a Fingerprint value or moves a query between clusters.

Functions

func CostKey

func CostKey(sql string) (string, error)

CostKey groups sort permutations of one statement under a single key for cost attribution. Attribution only: never EXPLAIN it, never use it as a Fingerprint — grouped members differ in sort order, which decides index column order and direction.

Same reshaped tree and fingerprint as Fingerprint, with statement-level ORDER BY dropped, so the key is never finer than Fingerprint. Aggregate and window ORDER BY survive (forEachChild does not reach them); LIMIT/ OFFSET are kept (top-N heapsort vs full sort is a real plan difference). The deparsed text is a hashing input only — its $N positions no longer line up with the fine canonical, so it is never returned or planned.

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"`
}

type SortProfile

type SortProfile struct {
	CostKey         string        `json:"cost_key"`
	DistinctSorts   int           `json:"distinct_sorts"`
	Variants        []SortVariant `json:"variants"`
	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"`
}

func SortProfiles

func SortProfiles(clusters []Cluster) []SortProfile

SortProfiles groups clusters by CostKey and reports the distinct orderings in each

type SortVariant

type SortVariant struct {
	Fingerprint     string   `json:"fingerprint"`
	SortClauses     []string `json:"sort_clauses,omitempty"`
	Calls           int64    `json:"calls,omitempty"`
	TotalExecTimeMs float64  `json:"total_exec_time_ms,omitempty"`
	MeanExecTimeMs  float64  `json:"mean_exec_time_ms,omitempty"`
	Rows            int64    `json:"rows,omitempty"`
}