NoSQL and Polyglot Persistence Questions

Global Secondary Indexes (GSIs) and Local Secondary Indexes (LSIs) let you query DynamoDB by alternative key(s).

• What they are

  • LSI: Same partition key as the base table, different sort key. Created at table creation time. Shares provisioned throughput and storage with the base table.
  • GSI: Independent partition and sort key. Can be created/modified after table creation. Has separate provisioned throughput and storage (or uses on-demand).

• Consistency and correctness

  • LSIs support strongly consistent reads (because same partition) while GSIs only support eventually consistent reads—updates to the base table are propagated asynchronously to GSIs, so queries might briefly return stale data.

• Performance/cost impacts and write amplification

  • Every write to the base table that affects indexed attributes also writes to each index: you pay additional write capacity units (WCUs) and storage for each index — this is write amplification.
  • GSIs have their own provisioned throughput; heavy writes can consume both base table WCUs and GSI WCUs and create throttling if either is exhausted.
  • Poor GSI partition (key) choice can create hot partitions (hot keys) where one partition receives disproportionate traffic, causing throttling and high cost.

• Example of hot-key risk

  • Using a boolean or low-cardinality attribute (e.g., "status=active") as GSI partition key funnels many items into very few partitions; billing and throttling spike.

• Mitigation strategies

  • Design high-cardinality GSI partition keys (hashing, composite keys, time-bucketing).
  • Use write sharding: append a shard suffix (0..N) to GSI partition key and route reads across shards; aggregate at read time.
  • Use sparse indexes: project only items that need the index to reduce index size and writes.
  • Project only needed attributes to GSIs to lower storage and read cost.
  • Monitor metrics (ConsumedRead/WriteCapacity, ThrottledRequests, Latency, ReadThrottleEvents) and enable autoscaling or use on-demand for unpredictable traffic.
  • For strong consistency need, prefer LSIs or design application-level coordination; remember LSIs must be defined at table creation and are limited to 5 per table.
  • Limit number of GSIs (each has limits) and prefer denormalization or materialized views updated via controlled background jobs for extremely write-heavy workloads.

Choosing index keys and projections carefully, combined with monitoring, autoscaling, and sharding techniques, reduces write amplification, avoids hot keys, and controls unexpected costs.

Trade-offs

• Benefits: fit-for-purpose storage (ACID safety in RDBMS, flexible schemas in document stores, fast analytic queries in columnar stores); better performance and scalability for specialized workloads.
• Costs: operational complexity (more services, backups, security), data consistency/coupling challenges, duplicated storage and ETL costs, need for cross-system schema/versioning and monitoring.
• When to use: domain boundaries with different access patterns (transactions vs product catalog vs analytics). Avoid if team cannot operate multiple datastores.

Example architecture

• OLTP: PostgreSQL (orders, payments, users) for ACID transactionality and FK integrity.
• Catalog: MongoDB for flexible product models (variants, localized attributes) exposed to product-service via document APIs.
• Analytics: ClickHouse for event/aggregate analytics and fast ad-hoc queries.
• Integration backbone: Kafka as the event bus.

Data synchronization strategies

1. Source-of-truth + CDC: Use PostgreSQL as source for transactional data; Debezium captures WAL changes and produces events to Kafka. Consumers:
   • Product service listens and updates MongoDB documents when product-related rows change.
   • ETL consumer streams cleansed events into ClickHouse (or writes to S3 partitioned files for bulk load).
     Advantages: low-latency, reliable, ordering preserved. Requires idempotent consumers and schema evolution strategy.
2. Event-first (event sourcing): Application writes domain events to Kafka; services project those events into their local stores (Postgres projections, MongoDB views, ClickHouse aggregates). Advantages: single write path, easier replay; trade-off: more design work and eventual consistency.
3. Batch ETL for heavy analytics: Periodic Airflow jobs export transactional snapshots to Parquet on S3 and bulk-load into ClickHouse/Redshift for cost-efficient large-scale aggregates.

Consistency & conflict handling

• Accept eventual consistency; design APIs to surface freshness SLAs (e.g., “last updated” timestamps).
• Ensure idempotency (upserts with natural keys), deduplication (event IDs), and schema migration plans.
• For read-after-write strictness, route reads to the authoritative store or implement read-through caching.

Operational considerations

• Observability: trace events (OpenTelemetry), monitor lag, consumer offsets, schema registry for Avro/JSON schema compatibility.
• Backups & DR per datastore, unified access control.
• Test: contract tests between producers/consumers, replay tests, and disaster recovery drills.

This design balances correctness for transactions, flexibility for products, and performance for analytics while managing complexity via CDC/event streaming, idempotent consumers, and clear ownership of source-of-truth.