Filtering / Sorting

Backend Fundamentals · Core Backend Fundamentals · 7 min read

1. What problem does this solve?

• Problem: Backend services often expose collections of data (users, orders, logs). Clients need to retrieve only the relevant subset and in a meaningful order. Without filtering/sorting, a client would receive the entire dataset, waste bandwidth, and have to do expensive client‑side processing.
• Why it matters:
  • Performance: Large tables (millions of rows) can’t be sent wholesale.
  • Cost: Cloud storage and network egress charges grow with data volume.
  • User experience: Pagination and sorted lists are essential for responsive UIs.
  • Security: Filtering can enforce row‑level access control.
• What happens if it’s not used:
  • Clients download huge payloads → slow UI, high memory usage.
  • Backend servers become bottlenecks, potentially exhausting CPU or I/O.
  • Harder to enforce fine‑grained permissions.
• Real‑world appearance:
  • REST APIs (GET /users?role=admin&sort=created_at,-name)
  • GraphQL queries with where and orderBy arguments
  • Search engines (Elasticsearch) with query and sort clauses
  • Redis sorted sets (ZRANGE key 0 -1 WITHSCORES)
  • Kafka consumer offsets with time‑based filtering

2. Mental model

Analogy: Think of a library.

• Filtering is like asking the librarian for books that are fiction and published after 2010.
• Sorting is like ordering those books alphabetically by title or by publication date.
  The librarian uses the catalog (index) to quickly locate the requested books, then hands them to you in the requested order.

Engineering model:

• Filter → Predicate applied to rows/records.
• Sort → Ordering based on one or more columns/fields.
• Both operations are usually performed at the data store level (SQL WHERE + ORDER BY, MongoDB $match + $sort, Redis ZRANGEBYSCORE, etc.) to avoid transferring unnecessary data.

3. Deep explanation

4. How it works

1. Client request: GET /orders?status=shipped&sort=created_at,-total&limit=20&offset=40.
2. API layer parses query params, validates them.
3. Service layer builds a query object: { status: 'shipped' }.
4. Repository layer translates to SQL:

   SELECT id, total, created_at
   FROM orders
   WHERE status = 'shipped'
   ORDER BY created_at DESC, total ASC
   LIMIT 20 OFFSET 40;
   

5. Database engine uses indexes to locate matching rows, applies ordering, returns 20 rows.
6. API serializes to JSON and sends back to client.

sequenceDiagram
    participant Client
    participant API
    participant Service
    participant Repo
    participant DB

    Client->>API: GET /orders?status=shipped&sort=created_at,-total&limit=20&offset=40
    API->>Service: validate & build query
    Service->>Repo: executeQuery({status:'shipped'}, {order:['created_at DESC','total ASC'], limit:20, offset:40})
    Repo->>DB: SELECT ... WHERE status='shipped' ORDER BY created_at DESC, total ASC LIMIT 20 OFFSET 40
    DB-->>Repo: 20 rows
    Repo-->>Service: rows
    Service-->>API: rows
    API-->>Client: JSON payload

5. Practical implementation

TypeScript/Node.js with PostgreSQL (pg)

import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

interface OrderFilter {
  status?: string;
  minTotal?: number;
  maxTotal?: number;
}

interface OrderSort {
  field: keyof Order; // e.g., 'created_at'
  direction: 'ASC' | 'DESC';
}

interface OrderQuery {
  filter?: OrderFilter;
  sort?: OrderSort[];
  limit?: number;
  offset?: number;
}

async function getOrders(q: OrderQuery) {
  const values: any[] = [];
  const whereClauses: string[] = [];
  let idx = 1;

  if (q.filter?.status) {
    whereClauses.push(`status = ${idx++}`);
    values.push(q.filter.status);
  }
  if (q.filter?.minTotal !== undefined) {
    whereClauses.push(`total >= ${idx++}`);
    values.push(q.filter.minTotal);
  }
  if (q.filter?.maxTotal !== undefined) {
    whereClauses.push(`total <= ${idx++}`);
    values.push(q.filter.maxTotal);
  }

  const where = whereClauses.length ? `WHERE ${whereClauses.join(' AND ')}` : '';

  const orderBy = q.sort?.map((s, i) => `${s.field} ${s.direction}`).join(', ') || 'id ASC';

  const limit = q.limit ?? 50;
  const offset = q.offset ?? 0;

  const sql = `
    SELECT id, total, status, created_at
    FROM orders
    ${where}
    ORDER BY ${orderBy}
    LIMIT ${idx++} OFFSET ${idx++}
  `;
  values.push(limit, offset);

  const { rows } = await pool.query(sql, values);
  return rows;
}

Key points

• Parameterized queries prevent SQL injection.
• WHERE clauses are built only when filters are present.
• ORDER BY uses a safe list of allowed fields to avoid injection.
• LIMIT/OFFSET are also parameterized.

Redis sorted set example

import { createClient } from 'redis';
const client = createClient();

async function getTopUsers(limit: number, minScore = 0, maxScore = '+inf') {
  // ZRANGEBYSCORE key min max WITHSCORES LIMIT 0 limit
  const result = await client.zRangeByScoreWithScores(
    'user:activity',
    minScore,
    maxScore,
    { LIMIT: { offset: 0, count: limit } }
  );
  return result; // [{ value: 'userId', score: 123 }]
}

6. Production considerations

7. Common mistakes

1. Using OFFSET for deep pagination – leads to full scans of preceding rows.
2. Not indexing filter/sort columns – causes full table scans.
3. Allowing arbitrary sort fields – opens injection and performance holes.
4. Ignoring LIMIT – can return millions of rows.
5. Mixing client‑side and server‑side filtering – wastes bandwidth.
6. Using ORDER BY on computed columns without indexes – expensive.
7. Not handling null values in sort – inconsistent ordering.
8. Over‑fetching data (selecting all columns) – increases payload size.

8. Senior engineer perspective

At scale, filtering/sorting becomes a bottleneck and a cost driver.

• Architecture decisions

  • Use cursor‑based pagination for deep reads.
  • Implement search indexes (Elasticsearch, Meilisearch) for complex queries.
  • Leverage materialized views for frequent aggregate filters.
  • Partition tables by time or sharding key aligned with common filters.

• Trade‑offs

  • Index maintenance cost vs. query speed.
  • Denormalization for fast reads vs. write amplification.

• Bottlenecks

  • Disk I/O when scanning large indexes.
  • CPU during in‑memory sorting of large result sets.

• Failure scenarios

  • Index corruption → fallback to full scan.
  • Hot spot on a single shard due to skewed filter values.

• Monitoring

  • Query latency dashboards.
  • Index usage metrics.
  • Pagination depth metrics.

• Cost/performance

  • Larger indexes increase storage costs.
  • Cursor pagination reduces read amplification.

• When NOT to use

  • For one‑off ad‑hoc reports: use a data warehouse.
  • When write latency is critical and indexes would slow writes.

9. Interview questions

10. Hands‑on task

Task: Extend the existing /orders endpoint in the repository to support cursor‑based pagination.

• Add a cursor query param that encodes the last seen id.
• Modify the repository to use WHERE id > $cursor instead of OFFSET.
• Ensure the API returns the next page and a new cursor.
• Write a unit test that verifies pagination works correctly for 3 pages of 10 rows each.

11. Quick revision

• Filtering narrows data; sorting orders it.
• Use indexes on filter and sort columns.
• Prefer cursor pagination over OFFSET for deep reads.
• Validate and whitelist query parameters.
• Avoid SELECT *; project only needed columns.
• Monitor query plans and latency.
• Use materialized views for expensive aggregates.
• In Redis, use sorted sets (ZRANGEBYSCORE) for sorted data.
• In MongoDB, $match + $sort with compound indexes.
• For complex search, consider Elasticsearch or Meilisearch.

12. References

• PostgreSQL Documentation – SELECT
• MongoDB Docs – Aggregation Pipeline
• Redis Docs – Sorted Sets
• Elasticsearch Docs – Query DSL
• OWASP – Input Validation
• SQL Performance Tuning – Indexing