Indexes beyond the B-tree

🧪

Hands-on lab

Run this module yourself — the `before/` source, the three-engine sandbox, and the worked solution to diff against.

Demos/Learn/course12-module-10 →

The B-tree indexes are in the package. The other ones live in a script called special_indexes.sql that someone runs after a restore, if they remember. The spatial index on the store locations. The XML index on the order documents. The columnstore on the sales fact table. They’re the indexes that took a specialist an afternoon to get right, and they’re the ones nobody can rebuild from memory when they go missing.

They aren’t a list of columns in key order, so they look like they need a script. They don’t. Each one has a shape you can declare, and once it’s declared, a deploy compares it, rebuilds only what changed, and puts it back when something takes it away.

SQL Server: one array per kind

A spatial index isn’t a column list, so it doesn’t go in Indexes. It gets its own array with its own vocabulary:

"SpatialIndexes": [
  { "Name": "SX_Place_Shape", "Column": "Shape", "Tessellation": "GEOMETRY_GRID",
    "BoundingBox": { "XMin": 0, "YMin": 0, "XMax": 1000, "YMax": 1000 } }
]

Declare what you care about and leave the rest out. An attribute you don’t write is the engine’s choice at create, and later deploys don’t compare it, so the grid densities SQL Server picked for you never read as drift.

A selective XML index promotes only the paths your queries use, instead of shredding every node of every document. It’s far smaller than a primary XML index, and a secondary can index one of its paths:

"XmlIndexes": [
  { "Name": "SXI_OrderDoc_Doc", "Column": "Doc",
    "Selective": { "Paths": [
      { "Name": "Customer", "Path": "/order/customer", "Type": "xs:string", "MaxLength": 40, "SingletonCardinality": true },
      { "Name": "Total", "Path": "/order/total", "Type": "DECIMAL(10,2)", "SingletonCardinality": true } ] } },
  { "Name": "SXI_OrderDoc_Doc_Total", "Column": "Doc", "PrimaryIndex": "SXI_OrderDoc_Doc", "ForPath": "Total" }
]

The same table carries "XmlCompression": true, SQL Server 2022’s compression for XML data.

An ordered clustered columnstore is still in Indexes, with two flags and a sort order. Ordered by SaleDate, a query for one month reads the segments for that month instead of all of them:

{ "Name": "CCI_SalesFact", "ColumnStore": true, "Clustered": true, "Order": "[SaleDate]" }

Deploy, and the catalog shows exactly what you declared: CLUSTERED COLUMNSTORE ordered on SaleDate, XML compression ON, two selective paths. Deploy again and nothing is created.

A change rebuilds what it has to

Queries start filtering on order status, so add a third path to the selective index. A selective index can’t take a new path list in place, so the deploy drops it and creates it again:

Dropping index [dbo].[OrderDoc].[SXI_OrderDoc_Doc]
Creating index [dbo].[OrderDoc].[SXI_OrderDoc_Doc]
Creating index [dbo].[OrderDoc].[SXI_OrderDoc_Doc_Total]

SQL Server drops a selective index’s secondaries when it drops the index. You didn’t ask for the secondary to be touched, but it’s in the package, so the same run puts it back. That’s the difference from the script in the drawer: the script would have rebuilt the primary and left you to notice the secondary was gone.

PostgreSQL: one array, many access methods

PostgreSQL has no separate arrays. Every index goes in Indexes, and AccessMethod picks the structure:

Index	AccessMethod	Key	For
ix_booking_during	gist	during, a tsrange	overlapping time windows (&&)
ix_booking_details	gin	details jsonb_path_ops	JSON containment (@>)
ix_booking_notes	gin	notes_tsv, a stored generated tsvector	full-text search (@@)

An operator class goes in the key after the column, the way PostgreSQL writes it. details jsonb_path_ops builds a smaller GIN index that answers only containment, which is usually all a JSON column’s queries need.

Two things to know today. --Validate reports SS-IDX-001 for that operator-class key, as though details jsonb_path_ops were a column name (#609); the deploy reads it correctly. And an index key that calls a function with more than one argument, like to_tsvector('english', notes), is rebuilt on every deploy (#610). Indexing a stored generated tsvector column avoids it, and it’s the form most teams use anyway.

Check yourself: You add a path to a selective XML index that has a secondary index on one of its existing paths. What does the next deploy do to each index, and why does it touch the one you didn't change?

It drops the selective index and creates it again with the new path list, because a selective index can’t change its paths in place. SQL Server drops the index’s secondaries with it, so the secondary is gone too, even though its own declaration didn’t change. Because the secondary is still declared in the package, the same deploy creates it again. You get both indexes back in one run, rather than discovering later that the secondary disappeared.

---

Every shop has its specialty tools, the ones only one smith knows how to set up. Hang them on the wall with the rest, labelled, and anyone can see when one’s missing. Declare the spatial grid, the selective paths, the columnstore order. The deploy keeps them true, and puts back what a rebuild knocked off the wall.

Got an index you’re still creating from a script because you weren’t sure it could be declared? Email me at forgebarrett@schemasmith.com — I read every one.

Next up: Module 11 · The security layer, declared, where the policy, the keys and the signed procedures come out of the database and into the package.

Until then, may every index you rely on be one you can point to in the package.

— Forge

Mark this lesson complete