Design innovative, scalable, and highly available cloud database solutions →hardMultiple ChoiceObjective-mapped
PCDE Practice Question: Design innovative, scalable, and highly available cloud database solutions
A company runs a MySQL database on Cloud SQL for an e-commerce platform. They need to add a new column to a table with millions of rows without causing downtime. What is the recommended approach?
⚠ Common exam trap
A common trap is assuming that Cloud SQL's managed service automatically makes all DDL operations online for large tables, but MySQL's native online DDL has limitations and does not eliminate downtime without using external tools like pt-online-schema-change or gh-ost.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use a tool like pt-online-schema-change to perform the change with minimal impact.
Pt-online-schema-change (or gh-ost) creates a shadow table with the new schema, incrementally copies rows using triggers or binary log replay, and then atomically swaps the tables. This avoids holding any locks on the original table, preventing downtime for an e-commerce platform with millions of rows. Cloud SQL's InnoDB does not support true online DDL for all ALTER TABLE operations, especially on large tables, making a dedicated online schema change tool the safest approach.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use 'ALTER TABLE ... ADD COLUMN ... ALGORITHM=INPLACE, LOCK=NONE'.
Why it's wrong here
In MySQL, ALGORITHM=INPLACE may still require a rebuild and metadata lock for some column additions.
- ✓
Use a tool like pt-online-schema-change to perform the change with minimal impact.
Why this is correct
pt-online-schema-change uses triggers and a shadow table to avoid locks.
- ✗
Create a new table with the column, copy data manually, then swap tables.
Why it's wrong here
Manual data copying is error-prone and risky; pt-online-schema-change automates it safely.
- ✗
Use 'ALTER TABLE ... ADD COLUMN' directly; Cloud SQL handles it online.
Why it's wrong here
Direct ALTER TABLE on large tables causes locks and potential downtime.
Go deeper
Related to this question
About these practice questions
This PCDE question is part of Courseiva's 1,446-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PCDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PCDE exam.