Interview Practice Questions

Explain what MVCC is and how it works in PostgreSQL.

What is ACID and why is it important for transactions in databases?

Compare SQL and NoSQL databases. In what cases might each be preferable?

Explain the CAP theorem. How does it affect the design of distributed systems?

When does it make sense to use table partitioning in PostgreSQL?

How does replication work in databases and what types are there?

Explain what a primary key is and its role in a relational database.

What are the main differences between INNER JOIN and LEFT JOIN?

What is a CTE (Common Table Expression) and when should it be used?

Explain how B-tree indexes work and their advantages over hash indexes.

When should window functions be used in SQL?

Explain the differences between ACID and BASE in the context of databases.

How does the MVCC (Multiversion Concurrency Control) mechanism work in PostgreSQL?

Explain how replication works in databases and what types exist.

What is sharding and what advantages does it provide in distributed databases?

What is the role of the HAVING clause and how does it differ from the WHERE clause?

What are window functions in SQL and how do they differ from aggregate functions?

Explain what a primary key is and how it differs from a unique key.

What is a transaction and what are its main properties according to ACID?

How does the MVCC mechanism work in PostgreSQL and how does it improve performance?