Datebase

Question 1

DB Migrations:

How would you migrate an application from a database to another, for example from MySQL to PostgreSQL? If you had to manage that project, which issues would you expect to face?

Question 2

NULL is special:

Why do databases treat null as a so special case? For example, why does SELECT * FROM table WHERE field = null not match records with null field in SQL?

Question 3

ACID:

ACID is an acronym that refers to Atomicity, Consistency, Isolation, and Durability, 4 properties guaranteed by a database transaction in most database engines. What do you know about this topic? Would you like to elaborate?

Question 4

Schema Migrations:

How would you manage database schema migrations? That is, how would you automate changes to the database schema, as the application evolves, version after version?

Question 5

Lazy Loading:

How is lazy loading achieved? When is it useful? What are its pitfalls?

Question 6

N+1 Problem:

The so-called “N + 1 problem” is an issue that occurs when code needs to load the children of a parent-child relationship with an ORMs that have lazy-loading enabled, and that therefore issue a query for the parent record, and then one query for each child record. How to fix it?

Question 7

Slowest Queries:

How would you find the most expensive queries in an application?

Question 8

Normalization:

In your opinion, is it always needed to use database normalization? When is it advisable to use denormalized databases?

Question 9

Blue/Green Deployment:

Of of the Continuous Integration’s techniques is called Blue-Green Deployment: it consists in having two production environments, as identical as possible, and performing the deployment in one of them while the other one is still operating, and then safely switching the traffic to the second one after some convenient testing. This technique becomes more complicated when the deployment includes changes to the database structure or content. I’d like to discuss this topic with you.