Your company must meet compliance rules (such as PCI DSS or GDPR) for a database holding personal and card data. What controls do you put in place?

What the interviewer wants to hear

• Data classification and minimisation; tokenise or avoid storing card data
• Least-privilege roles, named accounts, MFA and regular access reviews
• Encryption in transit and at rest, including backups, with managed keys and rotation
• Audit logging of access and privileged actions to tamper-resistant storage
• Masked or synthetic data outside production, retention and deletion processes

Model answer

I would start by classifying data and storing as little as possible: card numbers should be tokenised by the payment provider rather than stored, and personal data limited to what is needed. Access follows least privilege, with separate roles per application, named accounts for people, MFA through SSO or IAM authentication, and quarterly access reviews. Data is encrypted in transit with TLS and at rest, including backups and replicas, using a key management service with rotation, and the most sensitive columns can use application-level encryption. I enable audit logging, for example pgAudit, for logins, DDL and access to sensitive tables, shipped to tamper-resistant storage the DBAs cannot edit. Non-production environments get masked or synthetic data. Finally, retention and deletion jobs support rules like the right to be forgotten, and everything is documented for auditors.

More hard Database Administrator questions

• Design a high-availability setup for a critical PostgreSQL database with RPO near zero and RTO under one minute.
• Your single database is reaching its limits. How do you decide whether to shard, and how would you do it?
• How would you upgrade a large production PostgreSQL database to a new major version with minimal downtime?
• The application is timing out and you suspect lock contention in production. Walk through how you diagnose and fix it.
• Someone ran DELETE without a WHERE clause on an important production table 20 minutes ago. What do you do?
• Design a disaster recovery plan for a critical database that must survive the loss of a whole cloud region.
• How would you migrate a 5 TB on-premises PostgreSQL database to a managed cloud service with less than 15 minutes of downtime?
• A critical 1 TB PostgreSQL table is 60 percent bloat and queries are slowing. How do you fix it without downtime and stop it coming back?
• The business expects traffic to grow ten times over the next 18 months. How do you plan database capacity?