Oracle & PostgreSQL · Java / Spring · JPA / Hibernate

Primitive types
vs strings

Performance in computer systems and databases.

A primitive is a value on the stack; a string is an object on the heap behind a pointer. Every cost difference — memory, comparison, cache, index, join — follows from that.

AudienceLao Development Bank (Laos)
AuthorJacques BOUNLIPHONE
Date22 July 2026 · version 1

Memory representation: stack vs heap

  • int (32-bit) → 4 bytes, the value is on the stack
  • long (64-bit) → 8 bytes, the value is on the stack
  • String → the stack only holds a 64-bit address (8 B) pointing to a heap object: header + length + hash + byte array

"12345" → ~40–56 bytes  vs  4 for the int

Reading a primitive = 1 read. Reading/comparing a string = dereference the pointer then walk the heap.

Stack vs heap diagram

Access cost: direct (stack) vs indirection (heap)

Stack vs heap access
Stack — direct ≈ 1 CPU cycle
Heap — indirection ≈ 5 cycles in L1 cache, up to ~100–200 on a cache miss

Multiplied across millions of rows, this access gap becomes the dominant cost of a query.

Latency numbers every programmer should know

Latency ladder

Same hierarchy, one level up: L1 is ~200× faster than RAM, and RAM is millions of times faster than disk or a network hop.

Human scale = real latency × 1,000,000,000.

An L1 read becomes half a second; a disk seek becomes four months.

Source: Latency numbers every programmer should know (jboner) · Humanized: @srigi

The 4 query patterns

1 · TO_CHAR / TRUNC on date

-- anti: full scan + function
WHERE TO_CHAR(created_at,'..')='2026-07-22'
-- fixed: index range scan
WHERE created_at >= DATE '2026-07-22'
  AND created_at < DATE '2026-07-22' + 1

2 · WHERE on string

-- anti: string, no index
WHERE phone = '02012345';
-- fixed: index on the column
CREATE INDEX ix_phone ON customer(phone);

3 · WHERE on integer

-- anti: integer, no index
WHERE account_no = 100237;
-- fixed: index on the column
CREATE INDEX ix_acc ON account(account_no);

4 · JOIN / PK on string

-- anti: PK/FK as VARCHAR
JOIN customer c ON c.customer_code = o.customer_code
-- fixed: BIGINT key
JOIN customer c ON c.id = o.customer_id

Column type and index decide the cost

SQL pattern cost
costly / anti-patternfixed / lighter

TO_CHAR / TRUNC on date

full scan vs index range → ×225

WHERE on string

non-indexed vs indexed → ×267

WHERE on integer

non-indexed vs indexed → ×180

WHERE non-indexed

string vs bigint → ×1.8 · both full scans, the index is the real win

JOIN / PK on string

string vs bigint → ×6

Correlated nested subqueries vs join

Nesting depth

The danger isn't the subquery, it's the correlation.

Each correlated level re-executes for every row of the level above → non-linear growth.

Fix: flatten into joins + upstream aggregation (CTE), a single pass.

-- 4 correlated levels (each level re-executes per row) — avoid
SELECT c.* FROM customer c WHERE c.id IN (
  SELECT o.customer_id FROM orders o WHERE o.total > (
    SELECT AVG(o2.total) FROM orders o2 WHERE o2.region_id = (
      SELECT r.id FROM region r WHERE r.code = (
        SELECT b.region_code FROM branch b WHERE b.id = o.branch_id))));

The N+1 problem in JPA / Hibernate

N+1 vs batch

1 + N queries

500 parents → 501 queries

Fix: 2 queries

findAll() then findAllById(ids) (WHERE id IN …), join in memory.

JPA: JOIN FETCH, @EntityGraph, @BatchSize.

// ANTI: 1 query parents + N children
orderRepo.findAll();                 // (1)
for (Order o : orders)
    o.getCustomer().getName();       // SELECT ... WHERE id=?  (xN)

Reflexes for the team

Cost follows the number of operations the engine cannot avoid — rows scanned, string comparisons, correlated re-executions, network round-trips. Every fix brings the work back to the strict minimum.