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)
• func Prettify(sql string) (string, error)
• func WalkNodes(n *pg_query.Node, visit func(*pg_query.Node))
• 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
• type TableRef
  • func Tables(sql string) ([]TableRef, error)

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)

func Prettify

func Prettify(sql string) (string, error)

Prettify re-indents a statement for display: line breaks at clause boundaries, indentation by nesting depth. It changes WHITESPACE ONLY. Every token is copied verbatim from the input, so the result parses to the same tree and fingerprints to the same value as what went in; TestPrettifyPreservesFingerprint pins that over the captured corpus.

It is a display transform, never a normalization. The canonical single-line form stays the fingerprint input and the storage format; this is what a human reads.

Built on the PostgreSQL scanner rather than on pattern matching over the text, because only the real lexer knows that the FROM in `WHERE note = 'sent FROM x'` is four letters inside a string constant and not a clause. The same goes for dollar quoting, comments and quoted identifiers containing keywords; a regex formatter corrupts all four, silently.

func WalkNodes

func WalkNodes(n *pg_query.Node, visit func(*pg_query.Node))

WalkNodes visits n and every Node reachable from it, covering all pg_query node types via protobuf reflection. forEachChild stays for walks that must remain selective (stripSortClause).

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"`
	// Summed over the members, and nil unless EVERY member carried them: a partial
	// sum understates the spill, which is the direction that makes "raise work_mem"
	// advice wrong.
	TempBlksRead    *int64                  `json:"temp_blks_read,omitempty"`
	TempBlksWritten *int64                  `json:"temp_blks_written,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.

Fingerprinting already uses all cores; callers should not fan Group out across their own worker pool.

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"`
	Kind     string `json:"kind,omitempty"`  // "" (column) | "limit" | "offset"
	Shape    string `json:"shape,omitempty"` // "" (scalar) | "array"
	Schema   string `json:"schema,omitempty"`
	Table    string `json:"table,omitempty"`
	Column   string `json:"column,omitempty"`
	// exact | expression | heuristic | none
	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"`
	// Ignored by Group; a cluster's mean is derived from total/calls.
	MeanExecTimeMs   float64 `json:"mean_exec_time_ms,omitempty"`
	StddevExecTimeMs float64 `json:"stddev_exec_time_ms,omitempty"`
	Rows             int64   `json:"rows,omitempty"`
	// Temp blocks read and written by this statement: the sorts and hashes that
	// spilled out of work_mem. Pointers because a caller that did not collect them
	// (an older pg_stat_statements read, a fixture) means unknown, and 0 would say
	// the statement spilled nothing.
	TempBlksRead         *int64   `json:"temp_blks_read,omitempty"`
	TempBlksWritten      *int64   `json:"temp_blks_written,omitempty"`
	SharedBlksHit        *int64   `json:"shared_blks_hit,omitempty"`
	SharedBlksRead       *int64   `json:"shared_blks_read,omitempty"`
	SharedBlksDirtied    *int64   `json:"shared_blks_dirtied,omitempty"`
	SharedBlksWritten    *int64   `json:"shared_blks_written,omitempty"`
	SharedBlkReadTimeMs  *float64 `json:"shared_blk_read_time_ms,omitempty"`
	SharedBlkWriteTimeMs *float64 `json:"shared_blk_write_time_ms,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"`
}

type TableRef

type TableRef struct {
	Schema string `json:"schema,omitempty"`
	Name   string `json:"name"`
}

TableRef is one relation a statement references.

Schema is empty when the SQL did not qualify the name, which is the common case and is NOT resolvable here: pg_stat_statements carries no search_path, so `FROM orders` in a multi-tenant database may mean a different relation per session. Callers must treat an unqualified name as a name to match, not as an identity.

func Tables

func Tables(sql string) ([]TableRef, error)

Tables reports the relations a query references, deduplicated and ordered (schema, name). It is a read-only walk over the parse tree: it does not reshape, normalize or fingerprint, so it cannot move a Fingerprint value and needs no GroupingVersion bump.

Every RangeVar in the tree counts, which is what makes the DML targets come out right: `INSERT INTO orders`, `UPDATE orders`, `DELETE FROM orders` and `MERGE INTO orders` hang the target off the statement's own Relation field, not off a FROM list, and a walker written around FROM clauses reports zero tables for all four.

Scoped to DML (SELECT/INSERT/UPDATE/DELETE/MERGE/VALUES), which is what pg_stat_statements corpora carry. Utility statements are best-effort and draw no line between a relation used and one defined or dropped: `CREATE TABLE t` reports t, while `DROP TABLE t` reports nothing at all (DropStmt names its target as a string list, not a RangeVar).

CTE names are excluded when unqualified: `WITH recent AS (...) SELECT * FROM recent` references one real table, not two. The exclusion is by name across the whole input rather than per lexical scope, so a real table sharing a name with a CTE declared in any other scope (or, for multi-statement input, in any other statement) is dropped too. That is the deliberate direction to be wrong in: a missing cross-link is a gap, an invented one is a false claim about what a query touches.

An empty result is legitimate and does NOT mean the query was unreadable: `SELECT $1`, `VALUES ($1)` and a blank string all reference no relation. Callers that need "could we read this at all" must use the error, not the length.