--- title: "19 — Search, Real-Time, and Scaling Databases — From Keyword to Live to Distributed" uid: search-realtime-scaling tags: ["cap-theorem", "roadmap:backend", "indexes", "sharding", "elasticsearch", "replication", "long-polling", "sse", "solr", "websockets"] excerpt: "Search inverts text into indices for sub-linear lookup; real-time is one chosen pattern on a latency-and-direction trade-off; scaling databases forces the CAP theorem's impossible three-way." date: 2026-08-13T03:28:23+0000 source: https://www.aveshina.my.id/en/blog/search-realtime-scaling --- Search engines, real-time data, and database scaling sat in three separate folders in my head until they revealed the same shape: data, at scale, in time. The thread: **search inverts text into indices for sub-linear lookup, real-time communication is one chosen pattern along a latency-and-direction trade-off, and scaling databases forces the CAP theorem's impossible-three-way.** [1][4][7] All three are responses to "data, at scale, in time" — getting to the right row, the right event, or the right copy. The frame that helped is what each cluster optimizes. Search optimizes *retrieval by content* (find documents matching these words, fast). Real-time optimizes *delivery latency* (get the new data to the client the instant it exists). Database scaling optimizes *capacity and survival* (hold more data, survive node loss). Each has a canonical hard problem — inverted indices for search, the push/pull patterns for real-time, and CAP for distributed databases — and once those are clear, the products and patterns snap into place around them. ## Search engines: the inverted index A **search engine** is a specialized store optimized for fast full-text retrieval, built on the **inverted index** data structure [1]. A regular database index maps row → columns; an inverted index maps word → documents containing it. To find documents containing "backend," the engine looks up "backend" in the index and gets the document list directly — no scan. The two dominant engines both build on Apache Lucene: - **Elasticsearch** is the distributed, document-oriented search engine and analytics platform [2]. It's the default choice for full-text search at scale — log analysis (the ELK stack), product search, application search. Features include full-text query DSL, faceted search, aggregations, near-real-time indexing, and horizontal scaling. - **Solr** is the other Lucene-based search platform, with similar capabilities — full-text search, faceting, highlighting, distributed search [3]. Solr and Elasticsearch overlap heavily; Elasticsearch has more momentum and a richer ecosystem, Solr has a long enterprise track record. The reason to use a dedicated search engine rather than a database's LIKE '%term%' is the inverted index. A database doing LIKE scans every row; a search engine doing a term lookup reads one index entry. For full-text search across many documents, the performance difference is orders of magnitude. Many applications therefore run a relational database for the source of truth and a search engine (Elasticsearch) as a derived index for search queries — the search engine is populated from the database, and queries go to whichever store answers them faster. ## Real-time data: three patterns for live delivery **Real-time** delivery — getting new data to the client as it happens — has three patterns, each a different point on the latency/direction trade-off [4][5][6]: - **Long polling.** The client makes a request; the server holds it open, waiting for new data; when data arrives (or a timeout), the server responds and the client immediately re-requests. It's HTTP-only and simple, but inefficient (constant reconnects) and high-latency. - **Server-Sent Events (SSE).** A persistent HTTP connection over which the server pushes updates one-way (server → client) as they happen [5]. The browser's EventSource API handles reconnection automatically. SSE is the right choice when the server needs to push and the client doesn't need to push back — live notifications, dashboards, log tails. - **WebSockets.** A single persistent connection upgraded from HTTP that allows full-duplex (bidirectional) communication — both sides push at any time [6]. WebSockets are the choice when the client and server both need to push continuously — chat, multiplayer games, collaborative editing. The pattern selection is directional and lateness-driven: ```figure bidirectional server-push only high latency low latency Long polling simple, HTTP, reconnects SSE persistent, server → client WebSockets persistent, bidirectional ``` The rule: if only the server pushes, SSE; if both sides push, WebSockets; if I can't add a persistent connection, long polling. For most live-update features (a notification badge, a live comment feed), SSE is enough and dramatically simpler than WebSockets. WebSockets get pulled in when the client is also a continuous producer (chat input, cursor position). ## Scaling databases: indexes, replication, sharding **Scaling databases** is the cluster that handles more data and more users than one machine can serve [7]. Three techniques, in increasing order of complexity: ## Indexes: faster reads without scaling out A **database index** is a data structure (usually a B-tree) that speeds up reads by avoiding full table scans — it creates a lookup from column values to the rows that contain them [8]. An index on users.email lets the database find a user by email in O(log n) instead of scanning every row. The trade-off: indexes speed up reads but slow down writes (the index must be updated on every insert/update) and consume storage. The discipline is to index the columns used in WHERE, JOIN, and ORDER BY clauses, and to drop indexes that aren't used. The single best resource on this is "Use the Index, Luke!" — a guide to database performance for developers that explains what indexes do and how the query planner chooses them. Indexes are the first scaling lever — they make one machine handle far more load before any horizontal scaling is needed. They're also the most common root cause of slow queries: a missing index on a frequently-filtered column means every query scans the whole table. ## Replication: copies for availability and read scale **Data replication** creates multiple copies of the data across nodes — for availability, read throughput, and geographic latency [9]. The common pattern is **primary-replica**: one node accepts writes (the primary), and one or more nodes replicate from it (the replicas). Reads can go to any copy, multiplying read capacity. The trade-off is **replication lag** — replicas update asynchronously, so they can fall behind the primary. A user writes a comment, immediately refreshes, and doesn't see their comment because the read went to a lagging replica. This is the "I posted it but it's not there" bug, and it's a direct consequence of asynchronous replication. The mitigations are read-your-writes consistency (route the user's next read to the primary) or accepting the lag with UX (the comment appears after a moment). ## Sharding: splitting by key **Sharding** (horizontal partitioning) splits a large dataset across multiple machines by some key — say, user ID mod N [10]. Each shard holds a subset of the data; the cluster as a whole holds everything. Sharding scales write capacity (each shard handles its own writes) and total data size (no one machine holds everything). The costs are enormous. Once data is sharded: - Cross-shard queries are hard or impossible (a query that doesn't include the shard key must hit every shard). - Joins across shards are typically not supported. - Rebalancing shards (when one shard gets too big) is operationally painful. - The shard key choice is consequential and hard to change later. Sharding is the scaling technique of last resort. Most applications should scale up (vertical) and replicate (read scale) long before sharding, because sharding's complexity is permanent. The databases that support it natively (Cassandra, MongoDB) make it tractable but bake its constraints into the data model. ## CAP theorem: the impossible three-way The **CAP theorem** states that a distributed system can guarantee at most two of three properties [11]: - **Consistency** — every read sees the latest write (all nodes agree). - **Availability** — every request gets a response (even if not the latest). - **Partition tolerance** — the system keeps operating despite network partitions (dropped/delayed messages between nodes). Because networks *will* partition (this is non-negotiable in reality), the practical choice is between **CP** (consistency + partition tolerance — refuse requests during a partition to avoid inconsistency) and **AP** (availability + partition tolerance — always respond, possibly with stale data). Relational databases lean CP (consistency-first); many NoSQL stores lean AP (availability-first, eventual consistency). CAP is the reason there's no free lunch in distributed databases. Choosing to scale out means choosing which guarantee to relax, and that choice should be driven by the application's actual needs (a bank cannot serve stale balances; a social feed can). ## How I use this Three habits capture the practice across all three clusters: - **Search engines as derived indexes.** The relational database is the source of truth; Elasticsearch is a derived search index populated from it. Search queries go to the engine; writes go to the database and then propagate to the index. - **Match the real-time pattern to the direction.** SSE for server-push, WebSockets for bidirectional, long polling only when persistent connections aren't possible. Most live features are SSE-shaped, not WebSockets-shaped. - **Scale in order: indexes, replication, then sharding.** Most performance problems are missing indexes. Most availability problems are solved with replication. Sharding is the last resort, chosen when the data genuinely doesn't fit one machine, and its cost is permanent. The framing — *inverted index for search, push/pull patterns for real-time, CAP for distributed scale* — is the vocabulary of "data, at scale, in time." Each cluster has a canonical hard problem, and the products and patterns are tools around it. Knowing which problem I'm solving keeps me from reaching for Elasticsearch when I need an index, or for sharding when I need a replica. ## References [1] Elastic, "What is Elasticsearch?." [Online]. Available: [https://www.elastic.co/guide/en/elasticsearch/reference/current/elasticsearch-intro.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/elasticsearch-intro.html) [2] Elastic, "Elasticsearch." [Online]. Available: [https://www.elastic.co/elasticsearch/](https://www.elastic.co/elasticsearch/) [3] "Apache Solr." [Online]. Available: [https://solr.apache.org/](https://solr.apache.org/) [4] Mozilla, "Server-Sent Events," MDN Web Docs. [Online]. Available: [https://developer.mozilla.org/en-US/docs/Web/API/Server-sent_events](https://developer.mozilla.org/en-US/docs/Web/API/Server-sent_events) [5] Socket.io, "Bidirectional and Low-latency Communication." [Online]. Available: [https://socket.io/](https://socket.io/) [6] "Long Polling," javascript.info. [Online]. Available: [https://javascript.info/long-polling](https://javascript.info/long-polling) [7] "Horizontal vs. Vertical Scaling - How to Scale a Database," freeCodeCamp. [Online]. Available: [https://www.freecodecamp.org/news/horizontal-vs-vertical-scaling-in-database/](https://www.freecodecamp.org/news/horizontal-vs-vertical-scaling-in-database/) [8] "Use the Index, Luke!." [Online]. Available: [https://use-the-index-luke.com/](https://use-the-index-luke.com/) [9] IBM, "Data Replication." [Online]. Available: [https://www.ibm.com/topics/data-replication](https://www.ibm.com/topics/data-replication) [10] "How sharding a database can make it faster," Stack Overflow Blog. [Online]. Available: [https://stackoverflow.blog/2022/03/14/how-sharding-a-database-can-make-it-faster/](https://stackoverflow.blog/2022/03/14/how-sharding-a-database-can-make-it-faster/) [11] "What is CAP Theorem?," BMC. [Online]. Available: [https://www.bmc.com/blogs/cap-theorem/](https://www.bmc.com/blogs/cap-theorem/) ```quiz Q: What data structure does a search engine use to find documents fast, and why is it faster than a database LIKE? - The inverted index — maps word → documents containing it, so a term lookup reads one entry instead of scanning every row - A B-tree on the document ID correct: 0 explain: An inverted index maps each word to the list of documents containing it. Finding documents with 'backend' is one index lookup, not a scan. A database LIKE '%term%' scans every row. For full-text search the difference is orders of magnitude. Q: How do SSE and WebSockets differ, and when do you choose each? - SSE is persistent server→client push over HTTP (one-way); WebSockets are persistent bidirectional (both sides push). Choose SSE when only the server pushes; WebSockets when the client also pushes continuously - They are the same thing with different names correct: 0 explain: SSE handles server-to-client push simply over HTTP (notifications, dashboards). WebSockets upgrade to a bidirectional channel for chat, games, collaborative editing. Most live-update features are SSE-shaped; WebSockets are for when the client is also a continuous producer. Q: What is the trade-off a database index makes, and what is the most common cause of slow queries? - Indexes speed up reads but slow writes (index maintenance) and use storage; the most common cause of slow queries is a missing index on a frequently-filtered column - Indexes speed up both reads and writes equally correct: 0 explain: An index trades write cost and storage for read speed. The most common slow-query cause is a missing index on a WHERE/JOIN/ORDER BY column — every query then scans the whole table. Index the columns used for filtering and ordering; drop unused ones. Q: Why is sharding described as a technique of last resort? - It permanently complicates queries (cross-shard, joins), rebalancing is painful, and the shard key choice is hard to change. Most apps should index and replicate first - It is impossible to reverse once applied correct: 0 explain: Sharding bakes its constraints into the data model — cross-shard queries and joins become hard, rebalancing is operationally painful, and the shard key is consequential. Its complexity is permanent. Index (for read speed) and replicate (for read scale and availability) long before sharding. Q: What does the CAP theorem state, and what is the practical choice? - A distributed system guarantees at most two of Consistency, Availability, Partition tolerance. Since networks partition, the practical choice is CP (consistency) or AP (availability) — driven by application needs - You can have all three with enough engineering effort correct: 0 explain: CAP says at most two of the three. Partitions are unavoidable, so the real choice is CP (refuse requests during partition to stay consistent — relational DBs) or AP (always respond, possibly stale — many NoSQL stores). The choice depends on whether the app tolerates stale data. ```