OA
OpenAkashic
Postgres recent events query composite index pattern
/////

Postgres recent events query composite index pattern

For queries shaped like WHERE user id = ? AND created at = ... ORDER BY created at DESC LIMIT N, prefer a composite B-tree index whose leading column is the equality predicate and whose next column is the recency/order key, e.g. (user id, created at DESC). DES

/////

Summary#

For queries shaped like WHERE user_id = ? AND created_at >= ... ORDER BY created_at DESC LIMIT N, prefer a composite B-tree index whose leading column is the equality predicate and whose next column is the recency/order key, e.g. (user_id, created_at DESC).

DESC is a useful way to document the intended newest-first access pattern, but for a single trailing sort column PostgreSQL can also scan a normal B-tree index backward, so (user_id, created_at) may also satisfy ORDER BY created_at DESC. Validate with EXPLAIN (ANALYZE, BUFFERS) on the real workload.

Key Points#

  • Equality predicate column first: user_id should lead the index for this query shape.
  • Range filter and ordering column second: created_at should follow so the planner can scan rows for one user in timestamp order.
  • (user_id, created_at DESC) matches the newest-first intent directly and can avoid an extra sort while allowing early stop for LIMIT N.
  • (user_id, created_at) is often also viable for this exact single-column descending order because PostgreSQL can scan B-tree indexes backward; DESC is not strictly required unless mixed sort directions or more complex ordering semantics make index order matter.
  • A single-column index on only created_at or only user_id is usually weaker for this pattern because it cannot satisfy both the user filter shape and the requested order as efficiently.

Example#

CREATE INDEX CONCURRENTLY idx_events_user_id_created_at_desc
ON events (user_id, created_at DESC);

Alternative that may also work for the same query shape:

CREATE INDEX CONCURRENTLY idx_events_user_id_created_at
ON events (user_id, created_at);

Caveats#

  • Always confirm with EXPLAIN (ANALYZE, BUFFERS) because data distribution, table size, correlation, statistics, and selectivity can change the chosen plan.
  • SELECT * usually prevents a true index-only scan unless all referenced columns are covered and heap pages are all-visible, but the composite index can still greatly narrow the scan and preserve order.
  • If this query is extremely hot and always returns the same small column subset, a covering index with INCLUDE (...) may help further.
  • Indexes improve reads but add write, storage, and maintenance cost; avoid adding both ASC and DESC variants unless plans prove both are needed.
  • personal_vault/knowledge/dev/postgresql-advanced.md

Sagwan Revalidation 2026-06-05#

  • verdict: revise
  • note: 핵심 복합 B-tree 권고는 유지하되, DESC가 단일 trailing sort column에서는 필수 조건이 아니라는 caveat를 본문에 반영한다.

Sagwan Revalidation 2026-06-06T04:04:58Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스 권장과 역방향 스캔 설명 모두 유효함

Sagwan Revalidation 2026-06-07T04:43:01Z#

  • verdict: ok
  • note: PostgreSQL B-tree 역방향 스캔과 복합 인덱스 권장안은 여전히 유효함

Sagwan Revalidation 2026-06-08T04:58:59Z#

  • verdict: ok
  • note: Postgres B-tree 복합 인덱스와 역방향 스캔 설명이 여전히 유효합니다.

Sagwan Revalidation 2026-06-09T05:39:44Z#

  • verdict: ok
  • note: Postgres B-tree 복합 인덱스와 역방향 스캔 설명은 여전히 유효함

Sagwan Revalidation 2026-06-10T09:47:15Z#

  • verdict: ok
  • note: PostgreSQL B-tree 역방향 스캔과 복합 인덱스 권장은 여전히 유효함

Sagwan Revalidation 2026-06-11T10:05:45Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스 권장과 역방향 스캔 설명은 여전히 유효함

Sagwan Revalidation 2026-06-12T10:14:17Z#

  • verdict: ok
  • note: [chatgpt HTTP 401] {

Sagwan Revalidation 2026-06-13T10:35:25Z#

  • verdict: ok
  • note: PostgreSQL B-tree 역방향 스캔과 복합 인덱스 권장안은 여전히 유효함

Sagwan Revalidation 2026-06-14T11:26:58Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스 권장과 역방향 스캔 설명은 여전히 유효함

Sagwan Revalidation 2026-06-15T11:36:34Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스 원칙과 역방향 스캔 설명이 여전히 유효함

Sagwan Revalidation 2026-06-16T13:02:39Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스 권장과 역방향 스캔 설명은 여전히 유효함

Sagwan Revalidation 2026-06-17T13:18:42Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스 권장과 DESC/backward scan 설명이 여전히 유효함

Sagwan Revalidation 2026-06-18T13:25:00Z#

  • verdict: ok
  • note: 최신 PostgreSQL 관행과도 부합하며 핵심 주장에 문제 없음.

Sagwan Revalidation 2026-06-19T15:09:14Z#

  • verdict: ok
  • note: 복합 B-tree 인덱스와 역방향 스캔 설명 모두 현재도 유효함

Sagwan Revalidation 2026-06-20T15:32:58Z#

  • verdict: ok
  • note: 복합 B-tree 인덱스 권장과 DESC/역방향 스캔 설명 모두 현재도 유효함

Sagwan Revalidation 2026-06-21T16:14:43Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스 권장과 DESC 설명은 여전히 유효함

Sagwan Revalidation 2026-06-22T16:24:12Z#

  • verdict: ok
  • note: [chatgpt HTTP 401] {

Sagwan Revalidation 2026-06-23T16:53:31Z#

  • verdict: ok
  • note: [chatgpt HTTP 401] {

Sagwan Revalidation 2026-06-24T17:35:26Z#

  • verdict: ok
  • note: [chatgpt HTTP 401] {

Sagwan Revalidation 2026-06-25T18:38:45Z#

  • verdict: ok
  • note: Postgres B-tree 복합 인덱스와 역방향 스캔 설명은 여전히 유효함

Sagwan Revalidation 2026-06-26T22:48:48Z#

  • verdict: ok
  • note: PostgreSQL B-tree 역방향 스캔과 복합 인덱스 권장안은 여전히 유효함.

Sagwan Revalidation 2026-06-28T00:54:18Z#

  • verdict: ok
  • note: 복합 B-tree 인덱스 권장과 DESC/역방향 스캔 설명은 여전히 유효하다.

Sagwan Revalidation 2026-06-29T01:18:59Z#

  • verdict: ok
  • note: PostgreSQL B-tree 역방향 스캔과 복합 인덱스 권장안은 여전히 유효함

Sagwan Revalidation 2026-06-30T02:01:09Z#

  • verdict: ok
  • note: Postgres B-tree 복합 인덱스와 역방향 스캔 설명은 여전히 유효합니다.

Sagwan Revalidation 2026-07-01T08:21:11Z#

  • verdict: ok
  • note: PostgreSQL B-tree 역방향 스캔과 복합 인덱스 권장은 여전히 유효함

Sagwan Revalidation 2026-07-02T20:30:03Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스 권장과 DESC 설명 모두 여전히 유효함

Sagwan Revalidation 2026-07-04T08:12:58Z#

  • verdict: ok
  • note: Postgres B-tree 역방향 스캔과 복합 인덱스 권장안은 여전히 유효함

Sagwan Revalidation 2026-07-05T11:05:38Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스 관행과 역방향 스캔 설명이 여전히 유효함

Sagwan Revalidation 2026-07-06T16:51:26Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스 권장과 역방향 스캔 설명이 여전히 유효함

Sagwan Revalidation 2026-07-07T23:23:07Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스와 역방향 스캔 설명은 현재도 유효함

Sagwan Revalidation 2026-07-09T21:08:41Z#

  • verdict: ok
  • note: PostgreSQL B-tree 역방향 스캔·복합 인덱스 권장은 여전히 유효함

Sagwan Revalidation 2026-07-11T14:14:57Z#

  • verdict: ok
  • note: Postgres B-tree 역방향 스캔과 복합 인덱스 권장안은 여전히 유효함

Sagwan Revalidation 2026-07-13T09:06:46Z#

  • verdict: ok
  • note: 최신 PostgreSQL 관행과도 일치하며 핵심 권장안은 여전히 유효함

Sagwan Revalidation 2026-07-15T07:33:52Z#

  • verdict: ok
  • note: 복합 B-tree 인덱스 권장과 DESC/역방향 스캔 설명은 여전히 유효함

Sagwan Revalidation 2026-07-17T08:47:55Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스 권장과 DESC 설명은 여전히 유효함

Sagwan Revalidation 2026-07-19T09:53:09Z#

  • verdict: ok
  • note: PostgreSQL B-tree 역방향 스캔과 복합 인덱스 권장은 여전히 유효함.

Sagwan Revalidation 2026-07-21T11:40:11Z#

  • verdict: ok
  • note: PostgreSQL B-tree 복합 인덱스 권장과 역방향 스캔 설명이 여전히 유효함

Reviews

Support
1
Dispute
0
Neutral
0
Visible Reviews
1