# The Engineering Meaning of Indexes in Relational Databases: Identity, Access Paths, and Concurrency

> A technical analysis of index design beyond query speed, covering primary keys, uniqueness, ROWID, ORM identity, concurrency, RAC, CDC, partitioning, and measured access cost.

- Author: Muhammet Ali Köker
- Language: en
- Canonical: https://alikoker.com.tr/en/engineering-meaning-of-indexes-in-relational-databases
- Translation: https://alikoker.com.tr/iliskisel-veri-tabanlarinda-indeksin-muhendislik-karsiligi
- Published: 2025-12-12T12:00:00+03:00
- Modified: 2026-09-04T17:58:00+03:00
- Verified: 2026-09-04T17:58:00+03:00
- Type: article

Indexes are often treated simply as data structures for accelerating queries. In production systems, however, index design is inseparable from **stable row identity, data integrity, predictable access paths, concurrency, and write cost**.

The more useful question is therefore not “which column should be indexed?” but:

> Which integrity rule must be enforced by the data model, which access path is justified by the measured workload, and what are the write, locking, maintenance, and replication costs of that choice?

## Identity in the relational model: a primary key is not a performance setting

A relation in Codd's relational model is a set and therefore does not contain duplicate tuples. SQL tables, by contrast, permit bag/multiset semantics by default. An important qualification follows: a table does not need a constraint literally declared with the words `PRIMARY KEY` in order to model a relation, but it does need at least one real candidate key if rows are to have stable logical identity. A design with no candidate key and unrestricted duplicates loses that identity at the application boundary.

A primary key's first responsibility is therefore not query acceleration; it is to **define stable row identity**. When a domain-level business key must also be unique, that invariant should be enforced with a `UNIQUE` constraint or an equivalent database mechanism.

A surrogate key and a business key serve different purposes:

```text
surrogate key -> technical identity of a row
business key  -> uniqueness defined by the domain
```

For example:

```sql
CREATE TABLE transaction_record (
    record_id NUMBER GENERATED ALWAYS AS IDENTITY,
    source_system VARCHAR2(32) NOT NULL,
    reference_no VARCHAR2(64) NOT NULL,
    event_time TIMESTAMP NOT NULL,
    amount NUMBER,
    CONSTRAINT pk_transaction_record PRIMARY KEY (record_id),
    CONSTRAINT uq_transaction_record_business UNIQUE (source_system, reference_no)
);
```

`record_id` provides a stable technical identity to ORM and infrastructure layers. `UNIQUE (source_system, reference_no)` prevents two technical rows from representing the same business event. Neither replaces the other.

## A physical row locator is not identity

Without a logical key, addressing exactly one row **with a database-level guarantee** becomes difficult. A predicate over all columns may happen to return one row in the current dataset, but if uniqueness is not enforced there is no guarantee that it will continue to do so.

Oracle `ROWID` and PostgreSQL `ctid` are frequently used as physical locators in this situation.

Oracle `ROWID` encodes physical location information related to the data object, file, block, and slot. A normal `UPDATE` commonly preserves the head-row ROWID even when row migration occurs. Operations that rebuild or move physical storage, however, may change ROWIDs. Examples include `ALTER TABLE ... MOVE` and some shrink, redefinition, partition-maintenance, and row-movement operations.

PostgreSQL `ctid` is even shorter-lived. Because an `UPDATE` creates a new tuple version under MVCC, the `ctid` can change; physical rewrite operations such as `VACUUM FULL` can change it as well. PostgreSQL documentation explicitly warns against treating `ctid` as a long-term row identifier.

The engineering rule is therefore straightforward:

> A physical row locator is a short-lived access token, not an application identity.

Persisting ROWID in caches, API contracts, messages, or queues creates a contract that storage maintenance can invalidate.

## When is ROWID defensible?

ROWID is not useless in inherited Oracle systems where the schema cannot be changed. In legacy tables without a primary key, it can be a controlled engineering technique when selection and modification remain inside the same transaction and lock scope:

```sql
SELECT t.ROWID AS row_address,
       t.source_system,
       t.event_time,
       t.amount
FROM transaction_record t
WHERE t.source_system = :source
  AND t.event_time >= :start_time
FOR UPDATE;
```

Then, in the same transaction:

```sql
UPDATE transaction_record
SET amount = :amount
WHERE ROWID = :row_address;
```

The safety of this pattern does not come from ROWID being a durable unique key. It comes from keeping the selected physical row locked for the lifetime of the operation. Once the lock is released, the physical locator should not be promoted to durable identity.

## Why Hibernate and Jakarta Persistence require real identity

In the Jakarta Persistence entity model, each entity has an identifier. Hibernate's persistence context consequently behaves much like an identity map: managed instances are tracked by entity type and identifier.

Identity is not needed only for `find()`. It underpins:

- the first-level cache,
- dirty checking,
- entity lifecycle transitions,
- lazy association resolution,
- merge/detach semantics,
- cascading,
- targeting the correct row during flush.

Marking a non-unique column as `@Id` does not make Hibernate support keyless data. It gives the persistence context a false identity model.

### Composite identifiers

If a real business key exists, `@EmbeddedId` or `@IdClass` can represent it. The combination must actually be unique. Wide composite identifiers can also create larger indexes, wider foreign keys, and more expensive comparisons.

### Using ROWID as entity identity

Treating ROWID as `@Id` is tempting because it appears to solve mapping immediately, but it makes entity lifetime depend on physical storage lifetime. The weakness propagates from segment maintenance to detached entities, second-level caches, and asynchronous messaging.

Hibernate's `@RowId` support does not manufacture identity for a keyless entity. Its purpose is to take advantage of row-id based DML on supported databases for an entity that already has an identifier.

### Not mapping a keyless table as a writable entity

When the schema cannot be modified, the cleanest design is often not to model the table as a writable entity. Reads can use projections/DTOs, while controlled writes can be isolated in a repository/DAO path with explicit transaction semantics.

The advantage is conceptual correctness: the ORM is not told that the data model guarantees something it does not.

## Duplicate data: an invariant problem, not merely a cleanup task

One-time duplicate removal does not solve the underlying issue. The real invariant is:

```text
two committed rows with the same business key cannot exist
```

The classic application-side sequence does not guarantee this:

```text
SELECT -> not found
INSERT
```

Two transactions can both observe “not found” and both insert. Isolation and explicit locking can implement special-purpose serialization, but the natural enforcement point for a core uniqueness rule is a database `UNIQUE` constraint.

In Oracle, small datasets are sometimes deduplicated with ROWID-based statements such as:

```sql
DELETE FROM transaction_record t
WHERE t.ROWID NOT IN (
    SELECT MIN(k.ROWID)
    FROM transaction_record k
    GROUP BY k.source_system, k.event_time, k.reference_no
);
```

This can be acceptable for a small table. On a large table, however, a single transaction can generate substantial undo/redo, long rollback exposure, and heavy row/block-lock pressure. Saying that Oracle “locks the entire table” would be inaccurate; normal DML uses row locks, although a very large delete can still disrupt concurrent workload severely.

At hundreds of millions of rows, the solution must be designed around data volume, maintenance windows, referential integrity, partitioning, redo capacity, and rollback strategy. CTAS with a controlled swap, partition exchange, online redefinition, and batched delete all have different operational trade-offs.

## Algorithmic cost: O(log n) is not a complete optimizer model

At the abstract data-structure level, point lookup in the B-tree family is approximately `O(log n)`, while a full scan grows linearly with the number of rows or blocks. Real optimizer decisions, however, are not made from Big-O notation alone.

For cost-based optimizers such as Oracle and PostgreSQL, important variables include:

- cardinality estimates,
- selectivity,
- table and index statistics,
- clustering factor or physical locality,
- expected result size,
- random versus sequential I/O cost,
- cache state,
- predicate shape,
- join order and join algorithm,
- parallelism,
- partition pruning.

An approximate B-tree height is:

```text
h ≈ log_f(N)
```

where `N` is the number of index entries and `f` is the average branch fan-out. Even an index over hundreds of millions of rows may therefore be only a few levels high, explaining why point lookup can complete with very few logical reads.

If a query returns a large portion of the table, however, index range scan plus table access can be more expensive than a full scan because of scattered block visits. Therefore:

> The existence of an index and the optimizer's decision to use it are different questions.

A correct optimizer choosing a full table scan is not a defect.

## Does a small table need an index?

The statement “the table is small, so it does not need an index” mixes two separate concerns.

**Access path:** scanning a table that occupies only a few blocks may genuinely be cheapest.

**Integrity:** primary-key and uniqueness requirements are independent of table size.

Even a five-row lookup table needs `UNIQUE` if duplicate codes would violate a business rule. A secondary index added only for performance, on the other hand, may be unnecessary.

Execution frequency is another dimension. A five-block table scanned ten thousand times per second can consume meaningful CPU and logical I/O even though each individual execution is cheap. Measurements must consider both cost per execution and executions per unit time.

## Covering indexes and index-only access

When every column required by a query is available from the index, the engine may avoid a table lookup altogether.

Oracle may use access paths such as `INDEX FAST FULL SCAN`, `INDEX RANGE SCAN`, or other index-only plans when applicable. PostgreSQL `Index Only Scan` additionally depends on MVCC visibility information; even if all columns are present in the index, heap fetches may still be required when the visibility map does not permit an index-only answer.

This is one reason an identical SQL statement can have different costs under different maintenance and vacuum conditions.

## Composite indexes: column order is part of the access contract

These indexes are not equivalent:

```sql
CREATE INDEX ix_a ON transaction_record (source_system, event_time);
CREATE INDEX ix_b ON transaction_record (event_time, source_system);
```

Column order should follow real predicate distribution. Equality predicates, range predicates, sort requirements, and product-specific capabilities such as skip scan all matter.

For:

```sql
WHERE source_system = :source
  AND event_time BETWEEN :from_time AND :to_time
ORDER BY event_time
```

`(source_system, event_time)` is often a natural candidate, but it should not be turned into a universal rule without workload and cardinality data.

## Function-based and expression indexes

A predicate that applies a function to a column may not use an ordinary index efficiently:

```sql
WHERE UPPER(username) = :value
```

Oracle function-based indexes and PostgreSQL expression indexes can support such access paths. However, if normalization, a case-insensitive data type, or an appropriate collation would represent the domain more directly, the data-model solution should be considered first.

An index is not a substitute for a coherent data model.

## Partitioning and local/global indexes

Partitioning does not replace indexing. They solve different problems:

```text
partitioning -> divides the data set into physical/logical partitions
index        -> provides an access path within or across those partitions
```

In Oracle, local indexes align with table partitions and can simplify partition maintenance. Global indexes can support access patterns independent of the partition key, but may carry additional maintenance cost during partition operations.

For large event or time-series tables, partition pruning can restrict a query from billions of rows to only the relevant time partitions. If narrow range or point access is still required inside those partitions, indexes remain relevant.

## Write amplification: every index has a DML cost

Each secondary index adds work to related DML:

- leaf-block changes,
- possible block splits,
- redo/WAL generation,
- undo/MVCC version cost,
- cache invalidation,
- buffer consumption,
- maintenance and statistics overhead.

An insert into a table with eight indexes does not write only the base table; it must maintain the index structures as well.

That trade-off can be reasonable for read-heavy systems. In write-heavy ingest tables, the same index set may dominate throughput and tail latency.

Good indexing means the **minimum correct index set that satisfies the workload**, not the largest possible set.

## Monotonic keys and right-edge hot spots

With sequence, identity, or monotonic timestamp keys, B-tree inserts can concentrate on leaf blocks at the right edge of the tree. On a single instance this may appear as latch/buffer contention; on Oracle RAC it can become more visible because of cache-fusion block movement.

Possible techniques include:

- larger sequence caches,
- reverse-key indexes,
- hash-partitioned indexes,
- different key distributions,
- partitioning.

Each has a cost. Reverse-key indexes restrict range scans; hash partitioning adds operational complexity; random keys may reduce locality and enlarge indexes. The decision must follow workload measurements.

## Foreign-key indexes and concurrency

In Oracle, an unindexed foreign-key column can become particularly expensive when a parent key is deleted or changed, because child-table access and TM enqueue behavior may interfere with concurrency. In a busy system, that can grow into wait chains and eventually connection-pool saturation.

PostgreSQL does not automatically create an index on referencing columns when a foreign key is declared. An appropriate child-side index is therefore important in many real OLTP workloads to avoid expensive reference checks during parent deletes or updates.

Even so, “index every foreign key unconditionally” should not become a mechanical rule. If parent rows are immutable, the child table is tiny, or write amplification is the dominant constraint, the actual workload should be examined.

## Does a full table scan wear out an SSD?

This claim needs careful wording.

NAND flash endurance is primarily associated with program/erase cycles and bytes written. Normal reads do not directly consume P/E cycles. Therefore, the generic statement “full table scans shorten SSD life” is not technically defensible.

The measurable systems effect is different:

- shared I/O bandwidth is consumed,
- useful cache blocks may be displaced,
- CPU and memory bandwidth are spent,
- concurrent workload latency rises,
- sort/hash operations may spill to temporary storage and create indirect writes,
- storage queue depth and tail latency can increase.

In critical systems, the main concern is not drive lifetime but **capacity isolation and predictable latency**.

## Explicit cursors: the problem is row-by-row processing, not the cursor itself

A PL/SQL explicit cursor is not inherently an anti-pattern. The problem is converting a set operation over millions of rows into procedural row-by-row processing when a set-based formulation is possible.

A better hierarchy is:

```text
prefer set-based SQL
use bulk processing when necessary
use procedural row logic only when the state truly requires it
```

Oracle mechanisms such as `BULK COLLECT` and `FORALL` can reduce SQL/PLSQL context-switch overhead. Yet the largest improvement often comes from changing the algorithm into one SQL statement or a small number of set-oriented statements.

## OLTP and OLAP do not share the same indexing strategy

A typical OLTP workload targets:

- short transactions,
- high concurrency,
- narrow predicates,
- low p95/p99 latency,
- small and stable working sets,
- fast commits.

Analytical workloads emphasize:

- large scans,
- aggregation,
- column pruning,
- parallel execution,
- throughput,
- long-running queries.

A full scan in an analytical workload is therefore not automatically poor design. Columnar engines are built around wide scans. The problem arises when heavy analytical scans compete with latency-sensitive OLTP for the same CPU, cache, I/O, and lock budget.

Oracle RAC, PostgreSQL, or another database product does not eliminate that workload distinction.

## Oracle RAC can amplify poor locality

RAC provides availability and scaling options, but shared blocks still move between nodes. A hot index block, hot table block, or poor locality pattern that is local contention on one instance can become global cache traffic in RAC.

A useful RAC investigation therefore looks beyond SQL elapsed time and considers:

- buffer contention,
- `gc` wait classes,
- hot-block distribution,
- interconnect latency,
- service affinity,
- sequence/index hot spots,
- partition access locality.

Adding nodes is not a deterministic cure for a weak data model.

## Why identity matters to CDC

Change Data Capture systems must know which target row receives an `UPDATE` or `DELETE`.

PostgreSQL logical replication models this explicitly through `REPLICA IDENTITY`. Without an appropriate key, `REPLICA IDENTITY FULL` can require a much wider old-row image for identification.

Log-based CDC systems such as Oracle GoldenGate likewise benefit from a reliable unique key. Without one, broader column sets and additional logging may be required to identify target rows.

The cost of missing primary keys is therefore paid not only in Hibernate but also in replication and integration pipelines.

## Restore, reconciliation, and forensic traceability

Identity problems become especially visible during disaster recovery.

To compare two datasets and answer:

```text
which row is new?
which row changed?
which row was deleted?
which duplicate represents the original event?
```

the rows need an identity that can be matched across copies.

If two rows have identical values and no distinguishing identifier, it may be impossible to prove which physical copy originated from which source event. That weakens reconciliation, audit trails, and partial-restore procedures.

## Index design should be workload-driven and measured

An index recommendation is incomplete until production workload has been observed. Typical Oracle evidence includes execution plans, `DBMS_XPLAN`, AWR/ASH access patterns, and segment/index statistics. PostgreSQL commonly uses `EXPLAIN (ANALYZE, BUFFERS)`, `pg_stat_statements`, and catalog/statistics views.

Useful measurements include:

| Dimension | Example metric |
|---|---|
| Access frequency | executions/s |
| Latency | p50, p95, p99 |
| CPU | CPU time / execution |
| Logical I/O | buffer gets / shared hits |
| Physical I/O | reads, latency |
| Result cardinality | rows returned |
| DML cost | inserts/updates/deletes per second |
| Contention | lock/latch/buffer waits |
| Index efficiency | scans, rows fetched, maintenance cost |

Average duration alone is insufficient. In real-time systems, p99 and saturation behavior can be more important than average latency.

## A defensible transition plan when the schema cannot be changed

A legacy schema may not permit immediate primary-key or unique-constraint changes. Risk can still be reduced systematically:

1. Measure candidate business keys against real data.
2. Quantify duplicates and NULL distribution.
3. Identify every code path that performs writes.
4. Find every layer that already assumes uniqueness.
5. If the schema is immutable, keep physical locators within one transaction boundary.
6. Prefer projections/read models over false ORM entities.
7. Do not persist physical addresses into CDC, caches, or queues.
8. Validate index proposals with real execution plans and workload statistics.
9. When schema changes become possible, move invariants into database constraints first.
10. Add secondary read-path indexes only after measuring the critical queries.

The order matters. Adding dozens of performance indexes before solving identity does not remove integrity debt.

## Conclusion

Index design should not be reduced to “add an index when the query is slow.”

A production database must answer three questions together:

```text
Identity  -> What permanently and uniquely identifies this row?
Integrity -> Which business invariants are enforced by the database?
Access    -> What access path minimizes total cost for the measured workload?
```

Primary keys and unique constraints belong first to correctness; secondary indexes belong primarily to access economics. Foreign-key indexing, hot blocks, partitioning, covering indexes, CDC, and RAC effects are concurrency and operational consequences of the same design decision.

The strongest indexing strategy is not the one with the most indexes. It is the one that **makes correctness invariants explicit in the data model, keeps critical access paths at low and predictable latency, and constrains write amplification within measured limits**.

## References

1. Codd, E. F. (1970). *A Relational Model of Data for Large Shared Data Banks*. Communications of the ACM, 13(6), 377-387. DOI: 10.1145/362384.362685
2. Bayer, R.; McCreight, E. (1972). *Organization and Maintenance of Large Ordered Indices*. Acta Informatica, 1(3), 173-189. DOI: 10.1007/BF00288683
3. Comer, D. (1979). *The Ubiquitous B-Tree*. ACM Computing Surveys, 11(2), 121-137. DOI: 10.1145/356770.356776
4. Silberschatz, A.; Korth, H. F.; Sudarshan, S. *Database System Concepts*. 7th ed., McGraw-Hill, 2019.
5. Garcia-Molina, H.; Ullman, J. D.; Widom, J. *Database Systems: The Complete Book*. 2nd ed., Pearson.
6. Date, C. J. *An Introduction to Database Systems*. 8th ed., Addison-Wesley.
7. Elmasri, R.; Navathe, S. B. *Fundamentals of Database Systems*. 7th ed., Pearson.
8. Oracle. *Oracle Database Concepts*. https://docs.oracle.com/en/database/oracle/oracle-database/
9. Oracle. *Oracle Database SQL Tuning Guide*. https://docs.oracle.com/en/database/oracle/oracle-database/
10. PostgreSQL Global Development Group. *PostgreSQL Documentation: System Columns*. https://www.postgresql.org/docs/current/ddl-system-columns.html
11. PostgreSQL Global Development Group. *PostgreSQL Documentation: Indexes and Logical Replication*. https://www.postgresql.org/docs/current/
12. Hibernate. *Hibernate ORM User Guide: Identifiers and RowId*. https://docs.hibernate.org/
13. Eclipse Foundation. *Jakarta Persistence Specification*. https://jakarta.ee/specifications/persistence/
14. JEDEC. *JESD218: Solid-State Drive Requirements and Endurance Test Method; JESD219: SSD Endurance Workloads*. https://www.jedec.org/

## Cite This Work

Köker, M. A. (2025). The Engineering Meaning of Indexes in Relational Databases: Identity, Access Paths, and Concurrency. alikoker.com.tr. https://alikoker.com.tr/en/engineering-meaning-of-indexes-in-relational-databases

- BibTeX: https://alikoker.com.tr/en/engineering-meaning-of-indexes-in-relational-databases.bib
- RIS: https://alikoker.com.tr/en/engineering-meaning-of-indexes-in-relational-databases.ris
- CSL-JSON: https://alikoker.com.tr/en/engineering-meaning-of-indexes-in-relational-databases.csl.json