Efficient Pagination Query

sql Copy Code

-- Efficient Pagination Patterns for PostgreSQL
-- Avoid OFFSET for large tables; use keyset pagination instead

-- Pattern 1: Keyset Pagination (recommended for large datasets)
-- First page
SELECT id, title, created_at, status
FROM articles
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Subsequent pages (use last row's values as cursor)
SELECT id, title, created_at, status
FROM articles
WHERE status = 'published'
  AND (created_at, id) < ('2025-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Pattern 2: Optimized COUNT with estimate for total
-- Exact count (expensive for large tables)
SELECT COUNT(*) FROM articles WHERE status = 'published';

-- Fast estimate using statistics (PostgreSQL)
SELECT reltuples::bigint AS estimated_count
FROM pg_class
WHERE relname = 'articles';

-- Pattern 3: Window function pagination (when you need total)
WITH paginated AS (
    SELECT
        id, title, created_at,
        COUNT(*) OVER() AS total_count
    FROM articles
    WHERE status = 'published'
    ORDER BY created_at DESC
    LIMIT 20 OFFSET 0
)
SELECT *, total_count FROM paginated;

-- Pattern 4: Indexed pagination with covering index
-- Create covering index for common query
CREATE INDEX idx_articles_published_covering
ON articles (created_at DESC, id DESC)
INCLUDE (title, status)
WHERE status = 'published';

pagination performance sql optimization

javascript

MongoDB Connection with Retry

bash

MongoDB Backup Script

sql

User Analytics Query

sql

Full-Text Search Setup