Logging Pipeline — Naive (Direct-to-Database)

Designing a centralized logging pipeline is one of the most important system design interview questions because it forces candidates to reason about extreme write-read asymmetry, tiered storage economics, and the tension between search latency and retention cost. The naive direct-to-database approach is where every candidate should start because it establishes the baseline that makes the improvements in buffered and tiered architectures measurable and concrete.

The core challenge is capturing log data from a fleet of hosts, persisting it durably, and making it searchable for debugging and incident investigation. In the naive approach, this is done with the simplest possible architecture: a single LogService that receives log lines via HTTP POST and writes each one synchronously to a PostgreSQL database. Search is implemented as SQL LIKE queries on the raw message column, which translates to a sequential full-table scan with no index assistance.

The naive approach has two fatal flaws that emerge at even modest scale. First, write throughput is capped by PostgreSQL's ability to process synchronous INSERTs. Each INSERT requires WAL write, B-tree index update, and fsync. With a 50-connection pool and approximately 2ms per INSERT at low load, the theoretical ceiling is 25,000 inserts per second, but WAL contention, checkpoint I/O, and lock contention reduce the real ceiling to approximately 500 sustained log lines per second. At this rate, the connection pool saturates and ingest latency climbs from 2ms to 50ms or more, with spillover affecting search queries that share the same connection pool.

Second, search performance degrades linearly with data volume. SQL LIKE '%keyword%' cannot use a B-tree index because the wildcard prefix defeats index seek. Every search is a sequential scan of the entire logs table. At 500 lines per second, the table accumulates approximately 43 million rows per day. After just two days, a search query must scan 86 million rows, taking 30 seconds or more. There is no full-text index, no inverted index, no bloom filter — just raw sequential I/O.

The naive approach also lacks any mechanism for tiered storage. All logs live in PostgreSQL on a single disk. When the disk fills (typically 500 GB for a standard RDS instance), old logs must be manually deleted. There is no warm tier, no cold archive, no lifecycle policy. Compliance requirements for multi-year retention are impossible to meet. The Pipeline variant (v1) solves this with S3 archival at $0.01 per GB per month.

This template makes the write ceiling and search degradation visible and quantifiable. Run the simulation at increasing log rates and watch PostgreSQL connection pool utilization climb while search latency degrades from 2 seconds to 30 seconds. The comparison with the Pipeline and Multi-Tier variants provides concrete numbers for the discussion of buffering, indexing, and tiered storage that interviewers expect.

Logging pipeline design appears in interviews at Datadog, Splunk, Elastic, AWS, Google, and any company operating at scale. Interviewers expect candidates to start with the direct-write approach, identify the write throughput ceiling and search degradation, and then propose Kafka buffering and Elasticsearch indexing as the first optimization.

The naive logging system is a three-component linear architecture: LogAgent (Client), LogService, and LogDB (PostgreSQL). There is no load balancer, no event stream, no search index, no cache, and no tiered storage. Every request flows through the same single path.

LogAgent represents the fleet of application hosts generating log data. Each agent sends individual log lines via HTTP POST to LogService. There is no batching at the agent level — each log line is a separate HTTP request, adding per-request overhead (TCP connection, HTTP headers, JSON serialization). At peak, the fleet generates approximately 1,000 log lines per second, but the system's actual ceiling is around 500 lines per second due to the database bottleneck.

LogService is a stateless REST API running on 3 ECS Fargate pods with 50 threads each, providing 150 total threads. It handles two operations: (1) log ingest via POST /api/v1/logs, which validates the payload (non-empty message, valid log level) and performs a synchronous INSERT into the logs table; and (2) log search via POST /api/v1/search, which translates the query into a SQL SELECT with LIKE '%keyword%' on the message column. The service processing time is approximately 5ms, but the total response time is dominated by the database operation — 2ms for INSERTs at low load, 500ms to 30 seconds for searches depending on table size.

LogDB is a single Amazon RDS PostgreSQL instance (db.r7g.large, 2 vCPU, 16 GB RAM) with strong consistency and no replicas. The logs table stores all log data with columns for log_id (UUID), service name, log level, raw message text, timestamp, and created_at. B-tree indexes on (service, timestamp) and (level, timestamp) support filtered queries like 'show ERROR logs from checkout service in the last hour,' but they cannot accelerate LIKE '%keyword%' on the unindexed message column. The connection pool is limited to 50 connections, shared between ingest writes and search reads.

The system has zero redundancy. A single PostgreSQL primary handles all reads and writes. If the database fails, both ingest and search stop entirely. There is no failover, no read replica, no connection pooler (like PgBouncer). The write and read workloads compete for the same resources: connection pool, buffer cache, WAL bandwidth, and disk I/O. Under load, search queries that hold connections for seconds starve the ingest path, causing log lines to be dropped or delayed.

The concrete scaling ceiling is approximately 500 log lines per second sustained. At this rate, the 50-connection pool is 80 percent utilized (assuming 2ms per INSERT), leaving minimal headroom for search queries. A single search query holding a connection for 5 seconds at 1 million rows consumes 1 of 50 connections for the duration. Ten concurrent searches consume 10 connections, reducing ingest capacity by 20 percent. This mutual degradation between reads and writes is the fundamental problem that the Pipeline variant solves with separate ingest (Kafka) and search (Elasticsearch) paths.