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:

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

Instead of large offsets:

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:

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:

WHERE nip = :nip

consider:

CREATE INDEX idx_pegawai_nip
ON pegawai (nip);

If the application consistently queries an expression such as:

WHERE TRIM(nip::text) = :nip

a functional index may be appropriate:

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:

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.