Why Databases Exist

1Concept / Definition

A database is an organised collection of data managed by a database management system (DBMS) that provides persistent storage, a declared structure, shared access for many users and applications, safe concurrent updates and durability of committed changes.

2Why It Exists

Before DBMSs, each program stored data in its own files with its own format. Sharing data meant copying files; two programs updating the same file corrupted it; a crash mid-write left files half-written; answering a new question meant writing a new program. Hierarchical (IMS, 1966) and network (CODASYL) databases centralised data but tied programs to physical access paths. Codd's relational model (1970) and SQL separated what data is needed from how it is retrieved, and transaction theory (Gray, 1970s) formalised the ACID guarantees that business systems rely on.

3Core Concepts

3.1 Persistent data

Persistence means data outlives the process that created it — across restarts, deployments and hardware changes. In Java, objects vanish when the JVM exits; the database is where state lives between requests and releases.

3.2 Structured data

A schema declares tables, column types and constraints. The database rejects data that violates it — no matter which application writes.

CREATE TABLE customer (
    customer_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email        VARCHAR(254) NOT NULL UNIQUE,
    full_name    VARCHAR(200) NOT NULL,
    pan          CHAR(10) CHECK (pan ~ '^[A-Z]{5}[0-9]{4}[A-Z]$'),
    credit_limit NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK (credit_limit >= 0),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

Schema-less stores move structure into application code ("schema-on-read"); the structure still exists — it is just not enforced centrally.

3.3 Data sharing

One database commonly serves many consumers: web and mobile back ends, batch jobs, reporting tools, integrations and admin consoles.

Architecture note: sharing one database across services creates tight coupling (Book 5, Volume VII, 8.3). Sharing within one application or through read-only reporting replicas is normal; sharing writable tables across independently deployed services is an anti-pattern.

3.4 Concurrent access

Many clients read and write simultaneously. The DBMS ensures each transaction sees a consistent view and that concurrent updates don't corrupt data.

-- Two sessions buy the last unit simultaneously;
-- the DBMS serialises the conflicting updates
UPDATE inventory SET available = available - 1
 WHERE sku = 'A1' AND available > 0;
-- second session waits for the first, then updates 0 rows

// Spring: one transaction per use case; the database does the concurrency control
@Transactional
public boolean reserve(Sku sku) {
    int updated = jdbc.sql("""
            UPDATE inventory SET available = available - 1
             WHERE sku = :sku AND available > 0""")
        .param("sku", sku.value())
        .update();
    return updated == 1;
}

3.5 Durability

Durability (the D in ACID) guarantees that once a transaction commits, its changes survive crashes and power loss.

4How It Works Internally — From COMMIT to Disk

1. The application sends statements within a transaction; the server modifies pages in the shared buffer pool (memory).

2. Each change generates a WAL record appended to the log buffer.

3. On COMMIT, the WAL is flushed (fsync) to disk; only then does the server acknowledge success.

4. Modified data pages are written to data files later by the background writer and checkpoints.

5. After a crash, recovery replays WAL from the last checkpoint, redoing committed changes; uncommitted changes are never visible.

4ADeep Dive — What Applications Should Not Re-implement

// Correct: let the UNIQUE constraint decide; translate the violation
public CustomerId register(NewCustomer c) {
    try {
        return new CustomerId(jdbc.sql("""
                INSERT INTO customer (email, full_name) VALUES (:email, :name)
                RETURNING customer_id""")
            .param("email", c.email()).param("name", c.name())
            .query(Long.class).single());
    } catch (DuplicateKeyException e) {
        throw new EmailAlreadyRegistered(c.email());
    }
}

5APIs / Features / Syntax

6Practical Examples

6.1 Replacing a JSON file store

A configuration service stored tenant settings in JSON files on a shared volume; concurrent edits overwrote each other. Moving to a tenant_setting table with optimistic locking removed lost updates and enabled auditing.

6.2 Durability misunderstanding

A team disabled synchronous_commit to speed up an order service; a crash lost 40 committed orders. The setting was restored for orders and kept off only for a click-stream table where small loss was acceptable.

6.3 Worked example — mapping requirements to database guarantees

Scenario. A clinic chain in Pune is replacing a spreadsheet-and-phone booking process with an appointment system used by receptionists, doctors and an SMS reminder service. The product owner's requirements are written in business language; the architect's job is to turn each one into a guarantee the database must provide, rather than leaving every rule to application code.

Requirements for a clinic appointment system:

CREATE TABLE appointment (
    appointment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    doctor_id      BIGINT NOT NULL REFERENCES doctor,
    patient_id     BIGINT NOT NULL REFERENCES patient,
    slot_start     TIMESTAMPTZ NOT NULL,
    status         VARCHAR(12) NOT NULL CHECK (status IN ('BOOKED','CANCELLED','DONE')),
    CONSTRAINT one_booking_per_slot UNIQUE (doctor_id, slot_start)
);

What it shows. Each requirement maps to one of the reasons databases exist. "Appointments must survive restarts" is persistence; "phone numbers must be valid" is structure, enforced with a CHECK constraint so no client can insert a bad number; "three systems use the same data" is sharing, handled with one database and a role per consumer; "two receptionists must not book the same slot" is concurrency, made atomic with a UNIQUE constraint on doctor and slot; and "confirmed bookings must never disappear" is durability, provided by the write-ahead log, synchronous commit, a synchronous replica and backups.

Takeaway. Translate requirements into database mechanisms explicitly, one row per rule, and put each rule where it holds for every writer. Rules enforced only in one application's code are broken the first time another system or a manual fix writes to the same tables.

6.4 Worked example — proving concurrency safety with a test

Scenario. The booking service passed every unit test, yet in production two patients occasionally received the same 10:30 slot with the same doctor. The team believed their "check availability, then insert" logic was correct, because it never failed when tested one request at a time.

@Testcontainers
@SpringBootTest
class SlotBookingConcurrencyTest {
    @Container @ServiceConnection
    static PostgreSQLContainer<?> db = new PostgreSQLContainer<>("postgres:17");
    @Autowired BookingService booking;
 
    @Test
    void onlyOneOfTwentyConcurrentBookingsSucceeds() throws Exception {
        var results = new ConcurrentLinkedQueue<Boolean>();
        try (var exec = Executors.newVirtualThreadPerTaskExecutor()) {
            for (int i = 0; i < 20; i++) {
                exec.submit(() ->
                        results.add(booking.tryBook(DOCTOR, SLOT, newPatient())));
            }
        }
        assertThat(results).containsOnlyOnce(true);
    }
}

What it shows. The test starts a real PostgreSQL in a container and fires twenty booking attempts for the same doctor and slot at the same moment, using virtual threads. It then asserts that exactly one succeeded. Against the old check-then-insert code, several attempts pass the availability check before any of them inserts, so the test fails. Once the UNIQUE (doctor_id, slot_start) constraint is added and the service treats a unique-violation as "slot taken", exactly one booking succeeds every time, because the database serialises the conflicting inserts.

Takeaway. Concurrency bugs do not appear in sequential tests. Prove concurrency guarantees with a concurrent test against the real database engine, not an in-memory substitute, and let constraints, not application checks, enforce the rule.

6.5 Worked example — choosing durability settings per data class

Scenario. A retail platform runs one PostgreSQL cluster for very different data: payments, user profiles, session logs and click streams. Applying the strictest durability to everything made write latency high for logging traffic, while relaxing it globally would have risked losing payments after a crash.

What it shows. The team classified data by how much loss the business can tolerate and chose settings per class. Payments, orders and profiles keep synchronous_commit on with a synchronous standby and point-in-time recovery, so a committed transaction is never lost. Session and activity logs, where losing a few seconds is acceptable, use SET LOCAL synchronous_commit = off inside those transactions only, which removes the wait for the disk flush. Metrics and click streams, where minutes of loss are tolerable, move to a separate database or to unlogged or partitioned tables designed for high write volume.

Takeaway. Durability is a business decision per data class, not one global switch. Make the loss tolerance explicit with data owners, apply weaker settings only to the transactions that can afford them, and keep money and orders on the strongest guarantees.

7Common Problems & Mistakes

• Using files or caches as a system of record for shared business data.
• Application-level uniqueness checks without database constraints.
• Disabling durability globally for performance.
• Sharing writable tables across services.
• Assuming the ORM provides concurrency safety without transactions and constraints.
• No tested backups — durability ends at the last restorable backup.

8Performance & Production Considerations

9Architecture & Design Considerations

• Treat the database as the system of record and use its guarantees deliberately.
• Enforce invariants in the schema (constraints) and translate violations in the application.
• Choose durability levels per data class explicitly.
• Keep one owner per database in service architectures; share via APIs, events or read replicas.

10Interview Questions & Answers