8S8SPACE TECH BLOGEngineering The Future
2 min read

Tuning PostgreSQL feed lookup index

Query optimization guide to reduce feed API latency with complex indexes and execution plan interpretation

Tuning PostgreSQL feed lookup index thumbnail
PostgreSQLDatabaseBackendPerformance

Introduction

Feed API performance issues usually appear first due to changes in query patterns rather than traffic increases. As features are added, sort conditions and filter conditions gradually increase, and at some point, the index cannot keep up with the query. At this time, simply creating more indexes only increases writing costs.

This article summarizes the practical procedures for verifying “which index was created and why” with an execution plan.

PostgreSQL 피드 조회 인덱스 튜닝 커버
Wikimedia Commons 기반 무료 이미지

Problem definition

There are four bottlenecks that frequently occur when viewing feeds:

  • OFFSET-based pagination increases the back page response time rapidly.
  • Index selection is inefficient in combination of multiple filters (status, published_at, author_id).
  • Excessive heap fetches occur because there is no covering index.
  • Even after adding the index, it is distributed without EXPLAIN ANALYZE verification.

The key is to view keyset pagination + composite index + actual plan verification as one set.

Key concepts

ItemWrong approachRecommended Approach
PaginationOFFSET n LIMIT 20keyset (WHERE (published_at,id) < (...))
Index DesignAdd single index per conditionComposite index based on query pattern
verificationCheck only development environment estimatesEXPLAIN ANALYZE with production-like data
MonitoringCheck only average response timep95 + rows scanned + shared hit ratio

Code example 1: Composite index + keyset query for feed lookup

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_posts_feed
ON posts (status, published_at DESC, id DESC)
INCLUDE (title, author_id, summary);

-- cursor: (published_at, id)
SELECT id, title, author_id, summary, published_at
FROM posts
WHERE status = 'published'
  AND (published_at, id) < ($1::timestamptz, $2::bigint)
ORDER BY published_at DESC, id DESC
LIMIT 20;

Code example 2: Execution plan regression check

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, title, author_id, summary, published_at
FROM posts
WHERE status = 'published'
  AND (published_at, id) < ('2026-03-01T00:00:00Z', 982341)
ORDER BY published_at DESC, id DESC
LIMIT 20;

-- 목표 예시
-- Index Scan using idx_posts_feed on posts
-- Buffers: shared hit 비율 높음
-- actual time: < 10ms (warm cache)

Architecture flow

Mermaid diagram rendering...

Tradeoffs

  • Keyset pagination is fast, but random page jump UX is weak.
  • INCLUDE Indexes are advantageous for search performance, but use more storage space.
  • As the index increases, the write cost increases, so “search pattern priority” is important.

Cleanup

Optimizing feed performance is not a task of creating many indexes. It is an iterative loop that designs an index based on a search pattern and verifies the effect with an execution plan. By including this loop in your CI or deployment checklist, you can prevent performance regression in advance.

Image source

  • Cover: source link
  • License: LGPL / Author: The Oxygen Team, KDE;
  • Note: After downloading the free license image from Wikimedia Commons, it was optimized to JPG at 1600px.

Comments