Arvind Toorpu

Who this is for: DBAs and engineers running Aurora PostgreSQL who are deciding whether the jump to major version 18 is worth scheduling, and want real EXPLAIN output instead of a release notes bullet list.

Our monthly billing reconciliation query started tripping our 300-second statement timeout about four months ago. Nobody had touched the query or the index. What had grown was the orders table itself, past 8 million rows, and finance’s query filters by order status and a rolling date window, but never by tenant, since it’s a cross-tenant report by design. The only index covering those columns was idx_orders_tenant_status_created on (tenant_id, status, created_at), built years ago when almost every query in this app filtered by tenant first. The planner saw no tenant_id predicate and gave up on that index every time, falling back to a sequential scan across the whole table.

I’d been putting off Aurora PostgreSQL 18 for a while, partly out of the usual major-version caution, partly because our last major upgrade left us nursing degraded plans for most of a day while statistics rebuilt. Two things in the 18.3 release notes changed my mind: B-tree skip scans, and a change that keeps optimizer statistics intact across the upgrade instead of resetting them. Here’s what changed once we made the jump, with the actual plans.

Why This Matters

Multicolumn B-tree indexes have always followed the “left-most rule”: an index on (a, b, c) is genuinely useful to the planner only when your query constrains a, or a and b, and so on from the left. Skip a leading column and the index becomes close to useless, because the planner can’t know where in the tree to start looking. Multi-tenant applications run into this constantly. Every isolation-conscious schema puts tenant_id first in nearly every composite index, since it keeps each tenant’s rows clustered together and makes per-tenant queries fast. But the moment you need a report that spans tenants, filtering only on the trailing columns, that same index stops helping. Before PostgreSQL 18, the fixes were building a second index without tenant_id leading it (more storage, more write overhead) or accepting the sequential scan. Skip scan gives the planner a third option: treat the omitted leading column as if it were a small IN-list of its distinct values, and probe the index once per value.

Recreating the Problem on Aurora PostgreSQL 16

This is easy to reproduce on any pre-18 Aurora PostgreSQL cluster. Build a composite index with a low-cardinality leading column, then filter on the trailing columns only:

CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at);
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), sum(amount)
FROM orders
WHERE status = 'pending'
  AND created_at > now() - interval '30 days';

On our Aurora PostgreSQL 16.6 cluster, with 46 distinct tenants and 8.4 million rows in orders, that came back like this:

Aggregate  (cost=289143.30..289143.31 rows=1 width=40)
          (actual time=41822.615..41822.617 rows=1 loops=1)
  ->  Seq Scan on orders
        (cost=0.00..287310.00 rows=366660 width=12)
        (actual time=0.038..41680.204 rows=371452 loops=1)
        Filter: (status = 'pending'::text
                 AND created_at > (now() - '30 days'::interval))
        Rows Removed by Filter: 8028548
Planning Time: 0.312 ms
Execution Time: 41822.701 ms

Forty-two seconds, and it read every row in the table just to throw away 8 million of them. The index existed. The planner simply had no way to use it without a value for tenant_id.

Upgrading to Aurora PostgreSQL 18.3

Aurora PostgreSQL 18.3 went GA on June 11, 2026, available in all commercial AWS Regions plus AWS GovCloud (US). AWS documents three upgrade paths: an RDS Blue/Green Deployment, an in-place major version upgrade, or restoring a snapshot into a new 18.3 cluster. We used a Blue/Green Deployment, since it let us test the new query plans against production-shaped data before cutting over.

aws rds create-blue-green-deployment \
  --blue-green-deployment-name orders-pg18-upgrade \
  --source arn:aws:rds:us-east-1:111122223333:cluster:orders-prod \
  --target-engine-version 18.3 \
  --target-db-cluster-parameter-group-name default.aurora-postgresql18
# once the green environment reports AVAILABLE and lag is caught up:
aws rds switchover-blue-green-deployment \
  --blue-green-deployment-identifier bgd-abc123xyz \
  --switchover-timeout 300

After the switchover, confirming the engine version and instance status is a single check:

aws rds describe-db-clusters \
  --db-cluster-identifier orders-prod \
  --query "DBClusters[0].[EngineVersion,Status]"
# ["18.3", "available"]

The part I was bracing for, a stretch of degraded plans while statistics rebuilt, never happened. Aurora PostgreSQL 18 retains optimizer statistics across a major version upgrade instead of discarding them, so the planner had accurate row estimates the moment the cutover finished. That alone would have justified the upgrade; the query plan changes on top of it were the bigger win.

What the Query Plan Looked Like After

Same query, same index, same data, on Aurora PostgreSQL 18.3:

Aggregate  (cost=19402.11..19402.12 rows=1 width=40)
          (actual time=378.204..378.206 rows=1 loops=1)
  ->  Index Scan using idx_orders_tenant_status_created on orders
        (cost=0.56..17984.40 rows=366660 width=12)
        (actual time=0.091..312.558 rows=371452 loops=1)
        Index Cond: (status = 'pending'::text
                     AND created_at > (now() - '30 days'::interval))
        Index Searches: 46
Planning Time: 0.298 ms
Execution Time: 378.341 ms

Index Searches: 46 is the tell: one targeted probe per distinct tenant, matching our tenant count exactly, instead of a scan of the whole table. Forty-two seconds became 378 milliseconds. Note the plan didn’t switch to some new node type called “Index Skip Scan”; the behavior shows up as extra index searches on an ordinary Index Scan or Bitmap Index Scan. That detail matters if you’re grepping plan output for a string that doesn’t exist.

It’s worth being clear that this isn’t free, and it isn’t guaranteed. The planner is comparing costs, not applying a rule. To see where it draws the line, we ran a second query against an index that leads with a high-cardinality column instead:

CREATE INDEX idx_orders_id_created ON orders (order_id, created_at);
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE created_at > now() - interval '7 days'
ORDER BY created_at
LIMIT 100;

order_id is effectively unique: 8.4 million distinct values on 8.4 million rows. The planner didn’t touch idx_orders_id_created; it used a bitmap scan against a different, more selective index we already had on created_at alone. That’s the right call: probing once per distinct order_id would mean millions of searches. Skip scan only pays off when the omitted leading column has few distinct values relative to the table, which is why it solved our tenant-scoped index and wouldn’t have helped a primary-key-led one.

Where This Requires Care

A few things bit us, or nearly did, while we worked through this:

• Skip scan only applies to B-tree indexes. GiST, GIN, and BRIN indexes don’t get this behavior, so a query that only has a GIN index to lean on won’t benefit no matter how low-cardinality the omitted column is.
• The planner needs at least one real constraint on a column after the omitted one. A query with no predicate at all on status or created_at, just an unfiltered scan of the table, gets no help from skip scan; there’s nothing for it to target.
• It’s cost-based, not something you can force with a hint. We saw the planner choose a parallel sequential scan over skip scan on a version of our query with a wider date range, since the result set was large enough that reading the table directly won that time. Check the actual plan; don’t assume skip scan is active just because the index shape looks right.
• Stale statistics still cause bad plans; retained statistics across the upgrade only solves the upgrade moment itself. Keep ANALYZE running normally afterward.
• If you’re on a Blue/Green Deployment, budget time to run your slow queries against the green environment before switchover. We caught the skip scan win in staging, which is also how we caught that one reporting query still fell back to a sequential scan before it surprised anyone in production.

Quick Reference

• Aurora PostgreSQL 18.3 is GA (June 11, 2026) in all commercial Regions plus AWS GovCloud (US); upgrade via Blue/Green Deployment, in-place upgrade, or snapshot restore.
• Major version upgrades to 18 now retain optimizer statistics, avoiding the post-upgrade plan regression window older upgrades caused.
• Look for Index Searches: N in EXPLAIN (ANALYZE, BUFFERS) output to confirm skip scan is active; there’s no separate “Index Skip Scan” node type.
• Skip scan helps most on composite indexes with a low-cardinality leading column and a filtered trailing column; it won’t help (and won’t trigger) on high-cardinality leading columns like surrogate keys.
• It’s B-tree only, and it’s the planner’s call every time based on cost, not something you can force per query.

My Take

This is the rare major version bump where the headline feature fixed a specific, named problem we already had, rather than adding something we’d get around to using eventually. We didn’t touch the query, didn’t add an index: we upgraded and the planner started making a smarter choice on its own. I’d still tell anyone to test their own slow cross-tenant or cross-partition queries against a green environment first, since it’s cost-based and won’t kick in on every index shape. But for any multi-tenant schema where the tenant column leads your indexes, this is worth moving up your upgrade calendar for.

Further Reading

• AWS: Amazon Aurora now supports PostgreSQL major version 18
• Amazon Aurora PostgreSQL updates and release notes
• PostgreSQL Documentation: Multicolumn Indexes
• PostgreSQL 18 Released