Partitioning Deep-Dive.

Strong Answer:

"Vertical partitioning splits a table by columns—you might put frequently accessed columns in one partition and rarely accessed or large columns (like BLOBs) in another. It's useful when you have wide tables with different access patterns per column group. For example, a user table might have profile_data accessed constantly but profile_photo accessed rarely—vertical partitioning keeps the hot data compact.

Horizontal partitioning (sharding) splits by rows—each partition holds a subset of rows based on some key. This is what you use when your data volume or write throughput exceeds what one machine can handle.

In practice, I'd use vertical partitioning first for optimization within a single database, and horizontal partitioning when I genuinely need to scale beyond one machine. Most systems I've worked on needed horizontal partitioning long before vertical became relevant—the row count was the problem, not the column width."

Strong Answer:

"Hash partitioning gives you even data distribution—a good hash function spreads keys uniformly across partitions. The downside is you lose ordering. Range queries become scatter-gather operations across all partitions.

Range partitioning preserves order—all keys from A-F on one partition means you can efficiently query that range. But you risk hot spots. If keys are auto-incrementing or time-based, all writes hit one partition.

The decision depends on access patterns. For a key-value store where you always lookup by exact key—hash partitioning. For time-series data where you query by time ranges—range partitioning, but you'll need to handle the hot partition problem, maybe with additional sub-partitioning or load balancing.

A real example: at my previous company, we used hash partitioning for user data (lookup by user_id) but range partitioning for analytics events (query by time windows). Different access patterns, different strategies."

Strong Answer:

"Consistent hashing solves the resharding problem. With naive modulo hashing, adding a partition changes where almost every key maps—you'd have to migrate most of your data. Consistent hashing minimizes this movement.

The idea is to map both keys and nodes onto a ring. A key belongs to the first node you encounter walking clockwise from its position. When you add a node, only keys between the new node and its predecessor need to move—roughly 1/N of the data, and only from one existing node.

Virtual nodes improve balance. Instead of one point per physical node, you place hundreds of virtual nodes per physical node around the ring. This smooths out the distribution.

I'd use consistent hashing when I expect frequent scaling—like a distributed cache where nodes come and go—or when the cost of data migration is high. For a more stable database cluster that scales quarterly, simpler modulo hashing might be fine because I can plan migrations during maintenance windows."

Strong Answer:

"Hot partitions happen when traffic or data concentrates on specific keys. Detection comes first—you need metrics on partition load: QPS, CPU, queue depth. Anomaly detection or simple threshold alerts can identify hot partitions.

For read-heavy hot spots, caching is the first defense. A hot key served from Redis or even in-memory cache doesn't hit the database. Second option is read replicas specifically for the hot partition.

For write-heavy hot spots, you can split the hot key artificially. If user_12345 is hot, internally treat it as user_12345:0 through user_12345:9, spreading writes across ten partitions. Reads aggregate across the splits.

If the partition itself is hot (not just one key), you can split the partition. With range partitioning, divide the range. With hash partitioning, you'd need to reshard.

A practical example: large feed systems (Twitter's hybrid timeline is the well-documented case) use fan-out-on-write for most accounts but fan-out-on-read for celebrity accounts. They essentially treat hot accounts differently in their partitioning strategy."

Strong Answer:

"First, let me size this. If we assume each partition can handle 10K writes/sec (conservative for SSDs with batching), we need 100 partitions minimum. I'd plan for 200 to handle bursts and growth.

I'd use hash partitioning by key since our access pattern is key-based. Consistent hashing makes sense here because at this scale, we'll likely add/remove nodes frequently.

For the write path: client computes partition from key hash, sends write directly to that partition's leader. Each partition uses a write-ahead log for durability—append-only, sequential I/O is fast. The leader replicates to followers asynchronously for availability.

We batch writes—clients buffer for 1-5ms before sending, and the partition batches writes to disk. This amortizes I/O overhead.

For failures: if a partition leader dies, followers elect a new leader. We might lose a few milliseconds of async-replicated writes. If that's unacceptable, we'd do synchronous replication to at least one follower before acknowledging, trading latency for durability.

Consistent hashing means adding capacity is smooth—spin up new partitions, they take ownership of their key range, data migrates in the background."

Strong Answer:

"The core tension is between two access patterns: 'show me my feed' (read by viewer_id) and 'fan out a new post' (write by author_id, read by all followers).

Option 1: Partition by author_id. All of a user's posts are on one partition. Creating a post is fast—single partition write. But reading a feed requires querying every partition where you follow someone.

Option 2: Partition by viewer_id. Each user's feed is on one partition. Reads are fast—single partition. But posting requires writing to potentially millions of partitions if you have millions of followers.

Option 3: Hybrid approach. Small users (< 10K followers) use fan-out-on-write—when they post, we write to all followers' partitions. Large users (celebrities) use fan-out-on-read—we only store the post once and merge it into feeds at read time.

I'd go with Option 3. For partitioning the feed storage itself, I'd partition by viewer_id since reads are more frequent than writes. Each partition stores feeds for a subset of users.

The feed table would be: (viewer_id, post_id, timestamp, author_id, ...) partitioned by hash of viewer_id. When someone opens their app, we hit one partition, get their precomputed feed, and merge in any celebrity posts in the application layer."

Strong Answer:

"First, the constraints. 100K increments/second globally, millions of counters. Most counters are cold (few increments), some are very hot (viral content).

Naive approach: partition counters by counter_id using hash partitioning. Each increment goes to the right partition, which atomically increments. Problem: a hot counter overwhelms one partition.

Better approach: approximate counting with partition-local counters. Each partition maintains a local count. Periodically (every second or every N increments), local counts are aggregated to a global count. Reads return a slightly stale but much more available count.

For exact counting under high load: use a write-ahead log per partition. Increments append to the log. A background process aggregates. Reads of hot counters could hit a cache that's updated by the aggregator.

Even better for hot counters specifically: detect hot counters (incrementing > 1000/sec), and for those, switch to probabilistic counting (HyperLogLog for uniques, or just accept eventual consistency with wider aggregation windows).

The partition key would be counter_id. I'd use consistent hashing so we can add capacity for hot counters. The data model per partition:

counter_id -> {current_value, pending_increments[], last_aggregated_at}

Pending increments get batched and applied, reducing write amplification."

Strong Answer:

"This is the secondary index problem. Our primary partition key is user_id, but we need to query by product_id.

Option 1: Scatter-gather. Query all partitions, filter by product_id, merge results. Works for infrequent queries. Doesn't scale for high-QPS queries.

Option 2: Local secondary index. Each partition maintains an index of product_id → orders for that partition. Query still hits all partitions, but each partition query is indexed. Better than full scans, still O(partitions).

Option 3: Global secondary index. Separate index partitioned by product_id. Each entry points to (user_id, order_id). Query hits one partition of the index, then fetches from relevant user partitions. Two hops but predictable performance.

Option 4: Denormalization. Store orders twice: once partitioned by user_id (for user queries), once partitioned by product_id (for product queries). Writes are doubled, but reads are single-partition.

I'd choose based on query frequency. If product_id queries are rare (admin reports), scatter-gather is fine. If they're frequent (real-time product analytics), I'd build a global secondary index or denormalize.

The trade-off is write amplification versus read performance. For an e-commerce system, I'd probably denormalize—write once to the user-partitioned table, queue an async job to update the product-partitioned view. Eventual consistency is acceptable for product analytics."

Strong Answer:

"Short-term triage first. Can I add disk to this specific partition? If it's a cloud volume, resize it. If it's physical, add a disk and extend the filesystem or mount point.

If I can't add disk, I need to reduce data:

1. Archive old data: Move cold data to cheaper storage (S3, glacier). Partition by time if possible so old partitions are entirely archivable.

2. Compression: Enable or increase compression. Some databases support transparent compression. Trade CPU for disk.

3. TTL enforcement: If data has expiration, make sure TTL cleanup is running. Sometimes cleanup jobs fall behind.

4. Delete soft-deleted data: If we have logical deletes, hard-delete old tombstoned records.

5. Split the partition: Emergency resharding. Add a new partition, move half the data. Painful but sometimes necessary.

For the root cause: why is one partition out of space? Data skew? Unexpected growth? I'd investigate whether we need to rebalance or if the partition key is causing uneven distribution.

Longer-term, I'd set up monitoring and alerting for partition size. Trigger alerts at 70% capacity, pages at 85%, so we have time to react before hitting limits."

Strong Answer:

"This is about maintaining correctness during the transition. The key insight is that during migration, a key might exist on the old partition, the new partition, or be in-flight.

Approach 1: Dual reads. For any key that might be migrating, read from both old and new partitions. Return the newer version (by timestamp or version number). Writes go to the new partition.

def read_during_migration(key):
    if is_migrating(key):
        old_result = read_from_old_partition(key)
        new_result = read_from_new_partition(key)
        return newer_of(old_result, new_result)
    else:
        return read_from_current_partition(key)

Approach 2: Forwarding. Old partition knows it doesn't own the key anymore, forwards to new partition. New partition handles the request (fetching from old if needed).

Approach 3: Freeze-and-copy. Stop writes to migrating keys, copy data, switch routing, resume writes. Simple but causes brief write unavailability.

I prefer Approach 1 for read-heavy workloads. The overhead of dual reads is acceptable for a bounded migration period.

For writes during migration, I'd route to the new partition immediately and have the new partition pull any existing data from the old partition on first write if needed.

The migration state machine would be:

1. Mark key range as migrating
2. Copy data from old to new (background)
3. Dual-read, write-to-new
4. When copy complete, mark as migrated
5. Cleanup old partition"

Strong Answer:

"DynamoDB partitions by the partition key (or hash key). Each partition can hold about 10GB of data and handle about 3,000 RCU or 1,000 WCU (read/write capacity units).

The clever part is automatic splitting. When a partition gets too big or too hot, DynamoDB splits it transparently. You don't manage partition counts.

But this creates the hot partition problem. If one partition key is hot, it's stuck on one partition. DynamoDB has adaptive capacity that raises a hot partition's throughput (as long as the table's total capacity allows) and can isolate a hot item on its own partition, but there's still a ceiling of about 3,000 RCU / 1,000 WCU per partition.

Limitations:

1. Partition key design is critical. A bad partition key (like a boolean or low-cardinality field) means all your data lands on few partitions.
2. Limited transactions: TransactWriteItems (since 2018) works across partitions, but caps the item count per transaction and costs about twice the capacity units.
3. Eventual consistency by default. Strong consistency costs double the RCU.
4. GSI propagation is async. If you query a global secondary index right after writing, you might not see the update.

I'd use DynamoDB for truly key-value access patterns with high cardinality partition keys. For relational queries or strong consistency needs, I'd look at PostgreSQL (Aurora if I need scaling) or CockroachDB."

Strong Answer:

"PostgreSQL (native partitioning): Declarative partitioning since version 10. Supports range, list, and (since version 11) hash partitioning. The query planner uses partition pruning—if your WHERE clause matches the partition key, it only scans relevant partitions. But it's single-node by default. For distributed PostgreSQL, you need Citus (which adds hash distribution across workers) or a Postgres-compatible distributed database like CockroachDB.

Summary:

• PostgreSQL: Traditional RDBMS, partitioning for manageability and query optimization
• MongoDB: Document store, sharding for horizontal scale with some relational features
• Cassandra: Distributed-first, extreme write throughput, but strict data modeling requirements

I'd pick PostgreSQL for complex queries and ACID needs, MongoDB for flexible documents with moderate scale, Cassandra for massive write throughput with well-defined access patterns."

Strong Answer:

"The terms are often used interchangeably, but technically there's a distinction:

Partitioning is the general concept of dividing data into subsets. It can be within a single database instance—like PostgreSQL table partitioning where all partitions are on the same server but stored in separate files for manageability.

Sharding specifically refers to horizontal partitioning across multiple database instances or servers. Each shard is a separate database that can run on different hardware.

So all sharding is partitioning, but not all partitioning is sharding.

In practice:

• 'Partitioned table' in PostgreSQL → single server, multiple physical files
• 'Sharded database' → multiple servers, each holding a subset of data

The distinction matters for:

• Queries: Partitioned tables on one server can still do efficient joins. Sharded data across servers makes joins expensive (need to shuffle data).
• Transactions: Partitions on one server can share a transaction. Shards need distributed transactions.
• Administration: Partitions are managed by one database. Shards need coordination across multiple databases.

In conversation, if someone says 'partitioned' and means across servers, I'd clarify whether they mean logical partitioning (query optimization) or physical sharding (horizontal scale)."

Strong Answer:

"Absolutely. More partitions means more overhead:

1. Metadata overhead: Each partition needs tracking—where it lives, its health, its size. Thousands of partitions mean megabytes of metadata that needs to be consistent across the cluster.

2. Connection overhead: If your application connects to all partitions, that's N connections per app server. With 1000 partitions and 100 app servers, you're managing 100,000 connections.

3. Query overhead: For queries that can't be pruned to specific partitions, you're doing N sub-queries instead of one. Coordinator overhead grows linearly.

4. Replication overhead: If each partition has replicas, total machines = partitions × replication factor. 100 partitions × 3 replicas = 300 partition-replicas to manage.

5. Rebalancing complexity: Moving data during rebalancing is proportional to partition count. More partitions, more moves.

The right number depends on your data size and throughput. Rules of thumb:

• Each partition should hold at least 1GB (small partitions waste resources)
• Each partition should handle at least 100 QPS (otherwise you're over-partitioned)
• Start with fewer partitions than you think you need; splitting is easier than merging

I've seen teams over-partition preemptively—'we might need 100 shards someday'—and suffer the overhead for years while their data fit on 5 shards."

Strong Answer:

"The problem is that you've lost the ability to query by user_id alone. If you hash (user_id + timestamp), each combination maps to a different partition. To get all data for user_123, you'd need to know every timestamp and query every partition.

You've essentially created a random partition key. You'll have excellent load distribution but terrible query performance for any practical access pattern.

What they probably wanted was to avoid hot spots for users with high activity. Better approaches:

1. Partition by user_id, sub-partition by time: User data is co-located, but within a user's partition, you can time-range prune.

2. Add salt for hot users only: Most users use hash(user_id). Detected hot users use hash(user_id + salt) where salt cycles 0-9, spreading their data across 10 partitions.

3. Composite key with user_id first: hash(user_id) determines partition. (user_id, timestamp) is the full primary key for ordering within the partition.

The key insight is that your partition key should match your most common query pattern. If you always query by user, partition by user. Timestamp adds value as a sort key within the partition, not as part of the partition key."

Strong Answer:

"Let me break this into the main data entities and their access patterns:

1. Ride Requests (hot, real-time)

• Access: By rider_id (my rides), by driver_id (driver's rides), by geography (match riders to nearby drivers)
• Strategy: Geospatial partitioning for matching. Use geo-hashing (like S2 cells or H3 hexagons). Each partition owns a geographic region. This co-locates riders and drivers in the same area.
• Challenge: City centers are hot. Sub-partition dense areas or replicate hot regions.

2. User Profiles (rider + driver)

• Access: By user_id
• Strategy: Hash partition by user_id. Standard key-value access pattern.

3. Ride History

• Access: By rider_id (my past rides), by driver_id (their past rides), by time range (analytics)
• Strategy: Partition by user_id (could be rider or driver, pick one as primary). For cross-user analytics, replicate to an analytics-optimized store partitioned by time.

4. Real-time Location (extremely hot)

• Access: Driver locations updated every few seconds. Queries for nearby drivers.
• Strategy: This isn't traditional partitioning—use a specialized spatial index like Redis with geospatial commands, or a purpose-built system. Partition by geo-region if you need to scale beyond one node.

5. Payments

• Access: By ride_id, by user_id
• Strategy: Partition by user_id for user-facing queries. Separate payment_id-partitioned store for the payment processor that needs to look up by transaction.

Architecture sketch:

%% title: Architecture sketch
flowchart TD
  GW@{ icon: "sd:gateway", label: "API Gateway" } --> MS["Matching Service<br/>(geo-part)"]:::core
  GW --> US["User Service<br/>(user-part)"]:::service
  GW --> RH["Ride History<br/>(user-part)"]:::service
  MS --> LI@{ icon: "sd:map-pin", label: "Location Index<br/>(geo-hash)" }
  US --> UD@{ icon: "sd:database", label: "User DB<br/>(hash by id)" }
  RH --> RD@{ icon: "sd:database", label: "Rides DB<br/>(hash by uid)" }
  class GW edge
  class LI,UD,RD store

Key insight: Different data has different partitioning needs. Don't force one strategy across everything."