Access patterns choose the storage layout
List operations, predicates, ordering, update rate and acceptable staleness. A database name is not a data model. Orders need transactional state; search needs relevance and inverted indexes; videos need blob storage plus metadata. Duplicating derived data can improve reads but adds repair and freshness obligations.
B-trees, LSM trees and write-ahead logs
A B-tree-family index keeps ordered keys in pages, making point/range lookup efficient with page-level updates. LSM-style storage buffers writes and flushes sorted files, then compacts them. LSM designs trade foreground-write behavior for read and compaction costs; amplification depends on implementation and workload.
A WAL records changes for crash recovery before the relevant durability acknowledgment. Replication logs and backup streams may reuse log machinery but have different retention and replay requirements. Compaction consumes I/O and needs headroom; do not size only for steady-state bytes.
Indexes need selectivity and ordering
A composite index on (tenant_id, status, created_at) can serve tenant/status filters ordered by time. A query only filtering a trailing column may not get the intended benefit. Covering indexes can reduce table access but increase storage and write cost. Use actual query plans and row counts.
Hot partitions arise from time-only keys, popular tenants or sequential write concentration. Shard by a suitable ownership key and plan resharding, skew detection and cross-shard queries. More partitions are not a substitute for choosing a safe key.
Bloom filters
A Bloom filter tests probable membership using a bit array and several hashes. A negative means absent under the classic correctly maintained model; a positive means possibly present and needs an authoritative check. False positives are expected. It is not an exact deduplication database.
For expected count n and target false-positive rate p, a common sizing approximation is m = -n ln(p) / (ln(2)^2) bits and k = (m/n) ln(2) hashes. One million entries at p=1% needs about 9.6 million bits, approximately 1.2 MB before implementation overhead. A classic Bloom filter does not support arbitrary deletion by clearing bits; counting variants have different trade-offs.
Use one to avoid unnecessary storage probes for missing keys, while monitoring false positives and filter freshness. Never reject a valid user solely because an approximate filter claims an identifier was already used.
Experiment with approximate membership
Insert keys and inspect which bits become shared. A positive result still requires an authoritative lookup. Clearing a single shared bit to delete one key could create a false negative for another, which is why this classic model has no individual delete operation.
Search and ranking
An inverted index maps terms to matching documents. Analyze/tokenize consistently and choose stemming, language handling and stop-word behavior deliberately. Rank using an appropriate relevance model, then apply permission filters. Autocomplete can use prefixes, tries or indexed suggestions; popularity signals must not leak private searches.
Update search through durable events or CDC. The DB remains authoritative, while an index can lag. Rebuild from source data into a new version, catch up changes and atomically switch an alias. Deletes and access changes must reach every serving index promptly.
Object storage and media
Store large immutable payloads in object storage and searchable metadata in a database. Use signed upload/download URLs with bounded expiry and scoped keys, validate size/content and quarantine untrusted uploads before publishing. Multipart upload supports large objects; retries need cleanup of incomplete uploads.
A CDN distributes public/versioned objects. Private delivery needs an access policy that survives caching. Track checksums, lifecycle expiry, replication and restore procedures. Erasure coding trades redundancy efficiency against reconstruction costs; replication and erasure coding are choices tied to durability and repair time.
Geohashing and nearby search
Geohash encodes coordinates into hierarchical cells; shared prefixes often indicate nearby locations, but points across a cell boundary can be very close with different prefixes. Fetch the cell plus neighboring candidates, then apply exact distance filtering. Precision changes cell size and may behave differently by latitude.
Other approaches include spatial trees, S2/H3 cells and database spatial indexes. A cell ID is a candidate/routing tool, not proof of distance. PostGIS offers index-aware ST_DWithin; choose geography or geometry deliberately because units and earth assumptions differ.
Build a geohash, then search its neighborhood
Start with longitude bounds from −180 to 180 and latitude bounds from −90 to 90. Bisect longitude, record which half contains the point, then bisect latitude. Continue alternating dimensions. Group the resulting bits into five-bit values and encode them using the geohash alphabet. Each extra character identifies a smaller rectangular cell.
A shared prefix identifies a shared containing cell, but nearby points across a boundary can have different prefixes. Search the containing cell and relevant neighbors, then apply an exact distance filter. More characters reduce candidate count but increase the number of cells needed for a wider radius. Cell width in physical distance also changes with latitude. Do not use string-prefix similarity as a distance measurement.
Worked nearby-driver design
Drivers send bounded-frequency position updates with timestamp and sequence. Store recent positions with expiry, partition geographically and query neighboring cells. Reject stale updates and filter exact radius. Rank candidates, then atomically assign a driver; a location lookup alone cannot prevent two riders booking the same person.
Consider urban hotspots, movement between cells, privacy retention and region boundaries. Approximate availability is acceptable for displaying pins but a booking requires authoritative validation.
Exercise
Design searchable course PDFs and nearby study groups. Explain source-of-truth storage, indexing lag, delete propagation, approximate membership, geo candidate expansion, privacy filtering and rebuild after index loss.
Next: database scaling and capstone designs.