Sanity-check my DB choice: Aurora Serverless v2 vs RDS vs Redshift Serverless for a small multi-tenant analytics warehouse that onboards ~4 new hospitals per quarter

Q&A
, , ,
1

Small team running procurement dashboards for independent hospitals. Each hospital is a separate inbound feed needing its own private reporting plus a consolidated roll-up.

We add ~4 new hospitals per quarter(additive) — so ~19 within a year, plausibly low hundreds over a few years.

Today it’s S3/CSV end to end: Excel → S3 → Lambda (Excel to CSV, one per hospital per subject area) → Glue DataBrew (per-hospital “bronze” recipe, then a consolidating “silver”) → Glue Spark (joins) → gold CSV in S3 → QuickSight SPICE.

  • ~3.4 GB raw Excel,
  • ~342 MB curated across 6 subject areas (orders, requisitions, inventory, adjustments, contracts, savings)
  • Daily batch, no real-time requirement
  • ~7 Lambdas, 17 DataBrew jobs, 6 Glue Spark jobs, 13 Step Functions state machines

The problem isn’t scale - it’s that schema knowledge lives in code.Onboarding one hospital means building ~9 artefacts across 4 services, because each hospital’s file layout is hard-coded in Lambda environment variables and DataBrew recipes.

Secondary issues: the nightly job truncates the curated layer before rebuilding it (a failed step leaves dashboards empty), and there’s no typing, dedupe or lineage back to a source file.

Target shape : Excel → S3 (raw landing, unchanged) → metadata-driven ingest → relational warehouse → QuickSight

Growth assumption: +4 hospitals per quarter, additive — roughly 19 by end of year one, plausibly low hundreds over a few years. So onboarding has to become configuration rather than deployment, and cost has to scale sublinearly.

Current plan: Aurora Serverless v2 PostgreSQL, min capacity 0 ACU. S3 stays as raw landing plus a Parquet archive for ad-hoc/ML via Athena. Column mappings become config rows so onboarding is an INSERT rather than a deployment. Facts HASH-partitioned 64 ways on`hospital_id` + RLS.

Ruled out: Redshift Serverless (8 RPU floor is ~10x our total spend), RDS provisioned (~$50/mo buying peak capacity 24/7 — the smallest sensible instance covers the entire growth curve), DynamoDB (query model — no joins or server-side aggregation, and access is ad-hoc analytical).

Sizing I’d like challenged: ~90 ACU-hours/month fixed + ~3 ACU-hours per hospital, giving ~$12/mo at 3 hospitals and ~$18/mo at 19.

Questions:

1. Is `min_capacity = 0` realistic in production? My costing assumes the cluster genuinely pauses ~20 hours/day. What silently prevents auto-pause in practice?

2. HASH vs LIST partitioning for tenant isolation? LIST-per-hospital means thousands of partitions and DDL on every onboarding at scale. Is HASH + RLS the right call, or should I look at schema-per-tenant / database-per-tenant?

3. What actually triggers a move to Redshift Serverless — a data volume, concurrency or query-pattern threshold, rather than “when Postgres starts hurting”?

4. Is metadata-driven ingest sound at this scale? Column mappings as table rows rather than code is the core bet here. Anyone regretted this pattern as source formats drift?

Cost-sensitive, daily batch is fine. Small team, comfortable with PostgreSQL, no Spark or Redshift experience. Single region, must stay AWS-native.

2

Hi @mahesh_dalvi

Thanks for the thorough write-up, this is a solid design, and most of your instincts are right. Your target shape works, and retiring the Spark/DataBrew layer is the biggest win. At 342 MB curated, doing the joins in SQL inside Aurora is the correct choice for a small PostgreSQL-comfortable team not a compromise.

Here are direct answers to four questions.

1. Is min_capacity = 0 realistic in production?

Yes, a daily batch with no real-time requirement is exactly what scale-to-zero is for. Resume takes ~15 seconds, which SPICE handles fine.

The main thing to know: one open connection keeps the whole cluster awake. So:

  • Don’t put RDS Proxy in front, it holds connections open. Connect directly from Lambda with retry.
  • Keep every QuickSight dataset on SPICE (direct-query datasets hold connections).
  • Don’t rely on pg_cron a paused instance won’t wake for it. Run VACUUM/ANALYZE as the last step of your nightly load.

Official Documentation - Scaling to Zero ACUs with automatic pause and resume for Aurora serverless - Amazon Aurora and Performance and scaling for Aurora serverless - Amazon Aurora

Your cost numbers look right. One thing to watch - Aurora I/O charges on a write-heavy rebuild, if I/O gets high, Aurora I/O-Optimized may be cheaper. Worth measuring. Amazon Aurora storage - Amazon Aurora

2. HASH vs LIST partitioning?

HASH + RLS is the right call. LIST-per-hospital means DDL on every onboarding, which defeats your onboarding = config, not code goal. 64-way HASH is DDL-free on onboarding and comfortable into the low hundreds of hospitals.

Your real tenant isolation is probably at the SPICE layer (per-hospital datasets or QuickSight row-level security), with database RLS as a backstop.

Official Documentation - Using row-level security with user-based rules to restrict access to a dataset - Amazon Quick and PostgreSQL: Documentation: 18: 5.12. Table Partitioning

3. What triggers a move to Redshift Serverless?

The 8-RPU minimum you referenced for Redshift Serverless is no longer current. As of November 2025, AWS lowered the minimum base capacity to 4 RPUs (~$1.50/hr) roughly half the floor you costed against. This doesn’t change your conclusion (Redshift is still overkill for 342 MB), but it’s worth ruling it out on data-size grounds rather than the old pricing floor. Amazon Redshift Serverless now offers 4-RPU Minimum Capacity across more aws regions - AWS

Revisit Redshift only when: data grows past ~50–100 GB, you need high-concurrency direct query beyond SPICE, or the nightly transform blows the batch window. None are near. Compute capacity for Amazon Redshift Serverless - Amazon Redshift

4. Is metadata-driven ingest sound?

Yes. Config as rows is right for mapping columns to canonical fields. The one trap: it’s good for mapping, bad for logic. Keep transforms as tested functions that the config calls, never free-text SQL in a config row. Version the mapping rows so you have lineage when formats drift.

Two fixes worth prioritizing:

  • Truncate then rebuild is your worst failure mode. Load into staging, validate, then swap and only trigger the SPICE refresh after the load succeeds, so a failed load keeps yesterday’s data instead of blanking the dashboards. Refreshing SPICE data - Amazon Quick
  • Add typing, dedupe, and lineage: a typed staging table, dedupe with INSERT ... ON CONFLICT, and stamp each row with source_file / load_id / loaded_at. PostgreSQL: Documentation: 18: INSERT
3

Hi @mahesh_dalvi,

It’s been a while since we last heard from you, did you have any additional or follow up questions regarding your initial topic? Were you able to try out the second approach that was shared?

If we do not hear back within the next 3 business days, I’ll go ahead and mark the solution to close out/archive the topic.

Thank you!