A backend service built using Node.js, Express.js, and PostgreSQL (Neon) that supports browsing a catalog of 200,000+ products with filtering and high-performance pagination.
- Node.js
- Express.js
- PostgreSQL (Neon)
- pg
- Zod
- Browse products sorted by newest first.
- Filter products by category.
- Cursor-based pagination.
- Handles changing datasets without duplicates or missing records.
- Efficiently seeds 200,000 products.
- Optimized with database indexes.
GET /api/products| Parameter | Type | Description |
|---|---|---|
limit |
Number | Number of products to return (default: 20, max: 100) |
category |
String | Filter products by category |
cursor |
String | Cursor for fetching the next page |
GET /api/products?limit=20GET /api/products?category=BooksGET /api/products?limit=20&cursor=<cursor>I chose cursor-based pagination instead of traditional offset pagination because the dataset is large and changes frequently.
-
Performance degrades significantly on large datasets.
-
Newly inserted or updated records can cause:
- Duplicate products across pages.
- Missing products while browsing.
Example:
A user loads page 1 and, before requesting page 2, new products are inserted at the top. Using OFFSET may shift records, causing inconsistent results.
- Consistent results while data changes.
- No duplicate or missing products.
- Better scalability for large datasets.
- Query performance remains stable even with hundreds of thousands of records.
Pagination is implemented using:
ORDER BY updated_at DESC, id DESCThe cursor stores:
{
"updated_at": "...",
"id": "..."
}PostgreSQL was chosen because it provides:
- Strong indexing capabilities.
- Excellent support for ordered queries.
- Efficient execution of keyset pagination.
- Reliability and production readiness.
Neon was selected because:
- It offers a generous free tier.
- It is fully managed.
- It provides serverless PostgreSQL.
- Easy deployment and integration with Node.js applications.
To ensure fast queries, the following indexes were added:
CREATE INDEX idx_updated_id
ON products(updated_at DESC, id DESC);CREATE INDEX idx_category_updated_id
ON products(category, updated_at DESC, id DESC);The application frequently executes queries such as:
SELECT *
FROM products
WHERE category = $1
AND (updated_at, id) < ($2, $3)
ORDER BY updated_at DESC, id DESC
LIMIT 20;These composite indexes allow PostgreSQL to efficiently:
- Filter by category.
- Traverse records in sorted order.
- Avoid full table scans.
ORDER BY updated_at DESC
LIMIT 20Approximate complexity:
O(log N + K)
Where:
N= total recordsK= number of returned rows
WHERE (updated_at, id) < (...)
ORDER BY updated_at DESC, id DESC
LIMIT KApproximate complexity:
O(log N + K)
Since the query uses indexed columns, performance remains efficient even as the dataset grows.
The database is seeded with 200,000 products using batch inserts.
Instead of inserting records one by one, products are generated and inserted in batches of 5,000 rows.
Benefits:
- Faster execution.
- Fewer database round trips.
- Reduced overhead.
Given more time, I would add:
- Automated tests for pagination correctness.
- API rate limiting.
- Redis caching for popular queries.
- Cursor signing/encryption to prevent tampering.
- Docker support.
AI tools were used primarily for:
- Discussing architecture options.
- Comparing pagination strategies.
- Reviewing indexing approaches.
- Accelerating boilerplate implementation.
All architectural decisions, database design choices, and pagination logic were verified and understood before implementation.
AI suggestions that did not fit the requirements (such as offset pagination) were intentionally rejected after evaluating their trade-offs.