--- title: 53/60 Days System Design Questions | daily.dev description: A practical system design challenge about safely migrating a PostgreSQL users table with 40 million rows and 8 active writing services — splitting a full_name... canonical: https://daily.dev/posts/53-60-days-system-design-questions-wq6t5oh0r twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: 53/60 Days System Design Questions | daily.dev og:description: A practical system design challenge about safely migrating a PostgreSQL users table with 40 million rows and 8 active writing services — splitting a full_name... og:url: https://daily.dev/posts/53-60-days-system-design-questions-wq6t5oh0r og:image: https://api.daily.dev/og/posts/Wq6T5oh0R.png og:image:alt: 53/60 Days System Design Questions og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # 53/60 Days System Design Questions **[Joud Awad](https://daily.dev/sources/iac4jsbu0lv8wbsc85fsh)** · [@joudawad](https://daily.dev/joudawad) · 2 min read · 219 upvotes · 27 comments ## Summary A practical system design challenge about safely migrating a PostgreSQL users table with 40 million rows and 8 active writing services — splitting a full_name column into first_name and last_name without downtime. Four strategies are presented: a risky single ALTER TABLE, a phased expand-contract migration, a dual-write table swap, or a DB view abstraction. Readers are asked to pick and justify their approach. ## Content Your migration ran fine in staging. Then you ran it in production. The app went down. Not because the SQL was wrong. Because you ran it on a live table with 40 million rows while 8 services were actively writing to it. Your setup: → PostgreSQL. users table. 40M rows. Active writes from 8 services. → Product request: split full_name into first_name + last_name. → You have a 2-hour maintenance window tonight. The engineering question: how do you ship this without downtime? **A)** Run ALTER TABLE to drop full_name and add first_name + last_name in a single migration during the maintenance window. **B)** Add first_name + last_name as nullable columns first → backfill → update all services to write to both → drop full_name only after everything is migrated. **C)** Create a new users_v2 table with the target schema → dual-write to both tables → flip the read pointer → drain the old table. **D)** Add a DB view that aliases full_name as first_name || ' ' || last_name → let each service migrate off it at its own pace. Pick one — A, B, C, or D — and tell me why. Full breakdown in the comments. Drop your answer 👇 #30DaysOfSystemDesign #SystemDesign #BackendEngineering #DistributedSystems ## Community discussion Top comments from developers on daily.dev. **@joudawad** · 27 upvotes > **Why B is correct** > > > Two phases. > > > Phase 1 — Expand: add first_name and last_name as nullable columns. On PostgreSQL, adding a nullable column with no default is a metadata-only operation — near-instant, no lock. Backfill in small batches (rate-limited, no row lock escalation). Update services to write to both columns simultaneously. > > > Phase 2 — Contract: once every service is writing to the new columns and reads are fully migrated, drop full_name. You control the timing. > > > The key property: every step is independently reversible. If something breaks after Phase 1, you stop — the old column... **@joudawad** · 9 upvotes > **Why C is overkill ** > > > Shadow table + dual-write works. But it requires a dual-write layer in every service, an atomic read-pointer flip, and a drain/cleanup phase for the old table. > > > Worth it for genuine structural rewrites — new primary key, re-partitioning, changing storage engines. For a column split on a single table? That's 3 weeks of engineering for a 3-day problem. **@joudawad** · 9 upvotes > **Why A will get you paged ** > > > ALTER TABLE on a live table acquires an ACCESS EXCLUSIVE lock in PostgreSQL. It blocks every read and write for the duration. On a 40M row table with active traffic, that lock holds for minutes — not seconds. Your 8 services time out. The queue backs up. Alerts fire. > > > CONCURRENTLY helps for index creation. It doesn't help for column drops or renames. The maintenance window doesn't save you — the lock is the problem, not the timing. **@joudawad** · 5 upvotes > Also, it would mean a lot to me if you could support my content and stay in touch 🙏 > > - YouTube: [https://www.youtube.com/@system-design-lab](https://www.youtube.com/@system-design-lab) > - LinkedIn: [https://www.linkedin.com/in/joud-awad/](https://www.linkedin.com/in/joud-awad/) > - Medium Blog: [https://joudwawad.medium.com/](https://joudwawad.medium.com/) > - Substack: [https://joudawad.substack.com/](https://joudawad.substack.com/) **@joudawad** · 5 upvotes > **Why D defers the problem ** > > > A view aliasing full_name buys time. Useful as a transition layer inside Option B. But PostgreSQL write-through views only work on simple single-table views with no joins or aggregates. You still need to backfill the real columns eventually. > > > It defers the migration. It doesn't replace it. --- Tags: [#database](https://daily.dev/tags/database), [#postgresql](https://daily.dev/tags/postgresql), [#distributed-systems](https://daily.dev/tags/distributed-systems) [View this post on daily.dev](https://daily.dev/posts/53-60-days-system-design-questions-wq6t5oh0r) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"DiscussionForumPosting","mainEntityOfPage":"https://daily.dev/posts/53-60-days-system-design-questions-wq6t5oh0r","headline":"53/60 Days System Design Questions","text":"A practical system design challenge about safely migrating a PostgreSQL users table with 40 million rows and 8 active writing services — splitting a full_name column into first_name and last_name without downtime. Four strategies are presented: a risky single ALTER TABLE, a phased expand-contract migration, a dual-write table swap, or a DB view abstraction. Readers are asked to pick and justify their approach.","url":"https://daily.dev/posts/53-60-days-system-design-questions-wq6t5oh0r","datePublished":"2026-06-28T16:14:36.659Z","dateModified":"2026-07-01T09:52:44.767Z","author":{"@type":"Person","name":"Joud Awad","url":"https://daily.dev/joudawad","image":"https://media.daily.dev/image/upload/s--dOB9RaXY--/f_auto/v1773320801/avatars/avatar_iaC4JsBU0lV8wBsc85fSh?_a=BAMAMiiu0","description":"Principal Solution Architecture ","worksFor":{"@type":"Organization","name":"Metalab","logo":"https://www.google.com/s2/favicons?domain=metalab.com&sz=128"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"EndorseAction"},"userInteractionCount":81840}},"image":"https://media.daily.dev/image/upload/s--ZXPUGJDO--/f_auto/v1782663300/posts/Wq6T5oh0R?_a=BAMAMicg0","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":219},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":27}],"comment":[{"@type":"Comment","text":"Why B is correct\nTwo phases.\nPhase 1 — Expand: add first_name and last_name as nullable columns. On PostgreSQL, adding a nullable column with no default is a metadata-only operation — near-instant, no lock. Backfill in small batches (rate-limited, no row lock escalation). Update services to write to both columns simultaneously.\nPhase 2 — Contract: once every service is writing to the new columns and reads are fully migrated, drop full_name. You control the timing.\nThe key property: every step is independently reversible. If something breaks after Phase 1, you stop — the old column still exists, old services still work.\nSlower than A? Yes. 3am pages? Zero.","datePublished":"2026-06-28T16:16:27.058Z","url":"https://daily.dev/posts/Wq6T5oh0R#c-vDwfTokBL","author":{"@type":"Person","name":"Joud Awad","url":"https://daily.dev/joudawad","image":"https://media.daily.dev/image/upload/s--dOB9RaXY--/f_auto/v1773320801/avatars/avatar_iaC4JsBU0lV8wBsc85fSh?_a=BAMAMiiu0"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":27}},{"@type":"Comment","text":"**Why C is overkill **\nShadow table + dual-write works. But it requires a dual-write layer in every service, an atomic read-pointer flip, and a drain/cleanup phase for the old table.\nWorth it for genuine structural rewrites — new primary key, re-partitioning, changing storage engines. For a column split on a single table? That’s 3 weeks of engineering for a 3-day problem.","datePublished":"2026-06-28T16:16:38.238Z","url":"https://daily.dev/posts/Wq6T5oh0R#c-Q0aJAVPWh","author":{"@type":"Person","name":"Joud Awad","url":"https://daily.dev/joudawad","image":"https://media.daily.dev/image/upload/s--dOB9RaXY--/f_auto/v1773320801/avatars/avatar_iaC4JsBU0lV8wBsc85fSh?_a=BAMAMiiu0"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":9}},{"@type":"Comment","text":"**Why A will get you paged **\nALTER TABLE on a live table acquires an ACCESS EXCLUSIVE lock in PostgreSQL. It blocks every read and write for the duration. On a 40M row table with active traffic, that lock holds for minutes — not seconds. Your 8 services time out. The queue backs up. Alerts fire.\nCONCURRENTLY helps for index creation. It doesn’t help for column drops or renames. The maintenance window doesn’t save you — the lock is the problem, not the timing.","datePublished":"2026-06-28T16:16:32.051Z","url":"https://daily.dev/posts/Wq6T5oh0R#c-KEXuEgWT4","author":{"@type":"Person","name":"Joud Awad","url":"https://daily.dev/joudawad","image":"https://media.daily.dev/image/upload/s--dOB9RaXY--/f_auto/v1773320801/avatars/avatar_iaC4JsBU0lV8wBsc85fSh?_a=BAMAMiiu0"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":9}},{"@type":"Comment","text":"Also, it would mean a lot to me if you could support my content and stay in touch 🙏\n\nYouTube: https://www.youtube.com/@system-design-lab\nLinkedIn: https://www.linkedin.com/in/joud-awad/\nMedium Blog: https://joudwawad.medium.com/\nSubstack: https://joudawad.substack.com/","datePublished":"2026-06-28T16:16:57.411Z","url":"https://daily.dev/posts/Wq6T5oh0R#c-qj9huUQIY","author":{"@type":"Person","name":"Joud Awad","url":"https://daily.dev/joudawad","image":"https://media.daily.dev/image/upload/s--dOB9RaXY--/f_auto/v1773320801/avatars/avatar_iaC4JsBU0lV8wBsc85fSh?_a=BAMAMiiu0"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":5}},{"@type":"Comment","text":"**Why D defers the problem **\nA view aliasing full_name buys time. Useful as a transition layer inside Option B. But PostgreSQL write-through views only work on simple single-table views with no joins or aggregates. You still need to backfill the real columns eventually.\nIt defers the migration. It doesn’t replace it.","datePublished":"2026-06-28T16:16:45.263Z","url":"https://daily.dev/posts/Wq6T5oh0R#c-kivuYZOIN","author":{"@type":"Person","name":"Joud Awad","url":"https://daily.dev/joudawad","image":"https://media.daily.dev/image/upload/s--dOB9RaXY--/f_auto/v1773320801/avatars/avatar_iaC4JsBU0lV8wBsc85fSh?_a=BAMAMiiu0"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":5}}],"isPartOf":{"@type":"WebPage","url":"https://daily.dev/sources/iac4jsbu0lv8wbsc85fsh","name":"Joud Awad"}} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Joud Awad","item":"https://daily.dev/sources/iac4jsbu0lv8wbsc85fsh"},{"@type":"ListItem","position":3,"name":"53/60 Days System Design Questions"}]} ```