# Data Performance

> Data Fetching & Performance Guidelines

- Skill: `rumasorengfadil/data-performance` (Agent Skill)
- Install (CLI): `npx skillmds@latest add rumasorengfadil/data-performance`
- Raw SKILL.md: https://api.skillmd.com/api/skills/rumasorengfadil/data-performance/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: RumasorengFadil (https://skillmd.com/u/rumasorengfadil)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/rumasorengfadil/data-performance

---

## Data Fetching & Performance Guidelines

When implementing features that handle large datasets, prioritize performance and scalability.

### General Rules

* Do not load entire large datasets into the frontend.
* Use **server-side pagination** for large lists and tables.
* Default to **20–50 records per request** unless the existing implementation requires otherwise.
* Avoid `SELECT *`; fetch only required columns.
* Avoid unnecessary or repeated database queries.
* Prevent N+1 query problems.
* Add appropriate database indexes for frequently filtered, joined, searched, or sorted columns.
* Preserve existing application behavior unless a performance change requires otherwise.

### Pagination

For normal datasets, server-side pagination using `LIMIT` and `OFFSET` is acceptable.

For very large datasets or deep pagination, prefer **Cursor/Keyset Pagination** when practical.

Prefer:

```sql
SELECT id, nip, nama
FROM pegawai
WHERE id > :last_id
ORDER BY id
LIMIT 20;
```

Instead of large offsets:

```sql
SELECT *
FROM pegawai
ORDER BY id
LIMIT 20 OFFSET 100000;
```

### Prefetch & Client Cache

For paginated UI:

1. Load only the current page.
2. After the current page is displayed, prefetch the next page in the background.
3. Cache previously loaded pages on the client.
4. When navigating to a cached page, display it immediately without another loading state.
5. Prefetch the next required page afterward.

Do not fetch the entire dataset just to make pagination appear instant.

### Search

For search on large datasets:

* Perform search on the backend.
* Use **debounce around 300–500 ms** before sending the request.
* Reset pagination when the search query changes.
* Return only the required page of results.
* Add database indexes where appropriate.
* Avoid sending a request for every keystroke.

### Lazy Loading / Infinite Scroll

For long lists:

* Load data in small batches.
* Fetch the next batch only when the user approaches the end of the current list.
* Do not send thousands of records to the frontend at once.
* Prevent duplicate requests while a request is still running.
* Stop requesting when no more records are available.

### Dashboard & Statistics

For expensive dashboard calculations:

* Prefer aggregate queries instead of loading raw records and calculating in PHP/JavaScript.
* Avoid recalculating identical statistics repeatedly.
* Consider caching expensive results.
* Consider PostgreSQL **Materialized Views** for expensive aggregations that do not require real-time updates.
* Reuse existing Materialized Views when suitable instead of duplicating aggregation logic.

### Database Query Optimization

Before optimizing frontend or application code, inspect the underlying database queries and identify the actual bottleneck.

Do not assume pagination, caching, or frontend rendering is the primary cause of slowness.

#### Diagnose Before Optimizing

For slow PostgreSQL queries, inspect the execution plan when practical:

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;
```

Look for issues such as:

* Sequential scans over large tables
* Repeated scans caused by joins or correlated subqueries
* Expensive nested loops
* Large numbers of rows processed before `LIMIT`
* Sorts over unnecessarily large result sets
* Missing or ineffective indexes
* Filters that prevent existing indexes from being used
* Excessive buffer reads
* N+1 query patterns

Measure query execution time before and after optimization whenever possible.

#### Indexing

Add appropriate database indexes for columns or expressions frequently used in:

* `WHERE`
* `JOIN`
* `ORDER BY`
* Search
* Foreign-key lookups
* Cursor/keyset pagination

Do not add indexes blindly.

Indexes improve read performance but also have costs:

* Additional storage
* Slower `INSERT`
* Slower `UPDATE`
* Slower `DELETE`
* Additional maintenance overhead

Prefer indexes that are justified by real query patterns.

For example, if queries frequently use:

```sql
WHERE nip = :nip
```

consider:

```sql
CREATE INDEX idx_pegawai_nip
ON pegawai (nip);
```

If the application consistently queries an expression such as:

```sql
WHERE TRIM(nip::text) = :nip
```

a functional index may be appropriate:

```sql
CREATE INDEX idx_pegawai_trimmed_nip
ON pegawai ((TRIM(nip::text)));
```

However, when practical, prefer fixing inconsistent data types or normalization issues so queries can use simple indexed columns instead of relying permanently on casts or transformation functions.

#### Join Performance

Pay special attention to columns used repeatedly in joins.

A missing index on a joined column can cause the database to repeatedly scan an entire table, especially when combined with nested loops, lateral joins, correlated subqueries, sorting, or pagination.

Avoid queries inside loops when the same data can be retrieved using joins, batch queries, or set-based operations.

When a query performs joins before `LIMIT`, verify that the database is not processing a large intermediate result set just to return a small page.

#### Verify the Optimization

After making a performance change:

1. Run the same query or endpoint again.
2. Compare execution time.
3. Compare rows scanned and buffer usage when available.
4. Confirm that the expected index is actually used.
5. Test the real application endpoint, not only an isolated SQL query.
6. Verify that results and application behavior remain unchanged.

Prefer evidence-based optimization over speculative optimization.

### Large Export

For large Excel/CSV exports:

* Do not depend on data already loaded in the frontend.
* Process data on the backend.
* Process large datasets in batches/chunks when necessary.
* Avoid repeating expensive calculations for every row.
* Reuse previously calculated/cached values when valid.
* Show real export progress when processing may take noticeable time.

### Performance Priority

When implementing data-heavy features, prefer this flow:

```text
PostgreSQL
    ↓
Optimized Query + Index
    ↓
Backend Pagination / Cursor
    ↓
Cache (when appropriate)
    ↓
Frontend
    ↓
Lazy Load / Prefetch
    ↓
Client Cache
```

The main principle is:

> **Do not fetch, calculate, or render data before it is actually needed.**

When modifying an existing feature, inspect the current implementation first and apply these optimizations only where appropriate. Do not introduce unnecessary architectural complexity for small datasets.