sql 53 lines · 3 tabs

Partial index for “active” rows in Postgres

Shared by codesnips Jan 2026

3 tabs

CREATE TABLE subscriptions (
    id                  BIGGENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id         BIGINT       NOT NULL,
    plan_code           TEXT         NOT NULL,
    status              TEXT         NOT NULL DEFAULT 'active',
    current_period_end  TIMESTAMPTZ  NOT NULL,
    deleted_at          TIMESTAMPTZ,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT now(),
    CONSTRAINT status_allowed
        CHECK (status IN ('active', 'past_due', 'cancelled', 'expired'))
);

-- General-purpose lookup for a customer's subscriptions.
CREATE INDEX idx_subs_customer_status
    ON subscriptions (customer_id, status);

-- Partial index: only live rows, ordered for renewal sweeps.
CREATE INDEX idx_subs_active_renewals
    ON subscriptions (current_period_end)
    WHERE status = 'active' AND deleted_at IS NULL;

-- Hot path: find active subscriptions due to renew soon.
-- Predicates match the partial index predicate exactly so it is eligible.
SELECT id, customer_id, plan_code, current_period_end
FROM subscriptions
WHERE status = 'active'
  AND deleted_at IS NULL
  AND current_period_end < now() + INTERVAL '24 hours'
ORDER BY current_period_end
LIMIT 500;

-- Verify the planner actually chooses the partial index.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, current_period_end
FROM subscriptions
WHERE status = 'active'
  AND deleted_at IS NULL
  AND current_period_end < now() + INTERVAL '24 hours'
ORDER BY current_period_end
LIMIT 500;

-- Build the index without locking writes on a large, live table.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_subs_active_renewals
    ON subscriptions (current_period_end)
    WHERE status = 'active' AND deleted_at IS NULL;

-- Refresh planner statistics so cost estimates reflect the new index.
ANALYZE subscriptions;

-- Sanity check: partial index size versus a hypothetical full index.
SELECT
    relname                                    AS index_name,
    pg_size_pretty(pg_relation_size(oid))      AS index_size
FROM pg_class
WHERE relname = 'idx_subs_active_renewals';

3 files · sql Explain with highlit

This snippet shows how a partial index dramatically improves queries that only ever touch a small "active" subset of a large table. The core idea is that most rows in a subscriptions table are historically inactive (cancelled, expired, or soft-deleted), yet the application almost always filters on the tiny slice where status = 'active'. A full B-tree index on status would waste space and cache on rows that are never queried, so a partial index with a WHERE predicate builds the index over only the qualifying rows.

In schema.sql, the subscriptions table stores a status, a deleted_at soft-delete column, and a current_period_end. The plain composite index idx_subs_customer_status covers general lookups, but the interesting one is idx_subs_active_renewals, a partial index whose predicate WHERE status = 'active' AND deleted_at IS NULL restricts it to live subscriptions and orders them by current_period_end. Because the predicate matches the query's WHERE clause, the planner can use the index and skip the deleted rows entirely. A partial index is also far smaller, so more of it stays hot in memory.

In renewal_queries.sql, the first query mirrors the index predicate exactly and adds a range filter on current_period_end, which lets Postgres do an index range scan over just the active rows. The EXPLAIN ANALYZE at the bottom is included to confirm the planner chooses idx_subs_active_renewals rather than a sequential scan; the crucial detail is that the query's predicates must be provably implied by the index predicate for it to be eligible.

A common pitfall is a subtle mismatch: querying status <> 'cancelled' or omitting the deleted_at IS NULL check means the planner cannot prove the partial index applies, and it silently falls back to a full scan. Another is that partial indexes do not help queries that need inactive rows.

The trade-off is narrow applicability in exchange for a smaller, cheaper-to-maintain index and faster hot-path reads. Developers reach for this pattern whenever a boolean or enum flag makes one subset overwhelmingly dominant, such as unprocessed jobs, undeleted records, or open tickets, where indexing the whole column would be wasteful.

Related snips

package dbutil

import (
  "context"

  "github.com/jackc/pgconn"

Retry Postgres serialization failures with bounded attempts

go postgres transactions

by Leah Thompson 1 tab

ruby

require "csv"

class PeopleCsvStream
  include Enumerable

  HEADERS = %w[id full_name email signed_up_at plan].freeze

Resilient CSV Export as a Streamed Response

rails performance streaming

by codesnips 3 tabs

ruby

Rails.application.configure do
  config.after_initialize do
    Bullet.enable = true
    Bullet.alert = false
    Bullet.bullet_logger = true
    Bullet.console = true

N+1 query detection with Bullet gem

rails performance activerecord

by Alex Kumar 2 tabs

ruby

# BAD: N+1 query problem
@users = User.all
@users.each do |user|
  puts user.posts.count  # Fires query for each user!
end

ActiveRecord query optimization and N+1 prevention

ruby rails activerecord

by Sarah Mitchell 3 tabs

ruby

json.array! @posts do |post|
  json.cache! ['v1', post], expires_in: 1.hour do
    json.id post.id
    json.title post.title
    json.excerpt post.excerpt
    json.published_at post.published_at

Fragment caching for expensive JSON serialization

rails caching performance

by Alex Kumar 1 tab

ruby

class AddSettingsToAccounts < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_column :accounts, :settings, :jsonb, null: false, default: {}

Postgres JSONB Partial Index for Feature Flags

rails postgres jsonb

by codesnips 3 tabs

Share this code

Here's the card — post it anywhere.

Partial index for “active” rows in Postgres — share card

Link copied