• Position: Senior Software Engineer
• Interview Time: Nov 2024
• Company Type: Insurance & User Management
• Company Name: Private

---

1. What is Database Indexing and Why is it Important?

Question: What is database indexing?

Answer:
Indexing is a technique used to speed up the retrieval of data from a database. Indexes are created on columns that are frequently searched or sorted. They allow for faster searches, reducing the need for full table scans.

Question: What are the types of indexes in databases?

Answer:

• B-Tree Indexes: Balanced tree structure, ideal for range queries and exact matches.
• Hash Indexes: Best for equality comparisons (e.g., = operator).
• Full-text Indexes: Used for searching text in large datasets, especially for string-based searches.

---

2. Query Optimization for Better Performance

Question: How can you optimize SQL queries for better performance?

Answer:

• Avoid SELECT : Retrieve only the columns you need.
• Use JOINs efficiently: Ensure that JOIN conditions are indexed and avoid unnecessary ones.
• Use WHERE clauses wisely: Filtering data early in the query reduces the result set size and optimizes performance.
• Use indexes properly: Make sure to index columns used in WHERE clauses and JOINs.

Question: How do you analyze query performance?

Answer:
Use the EXPLAIN command (or equivalent) to analyze query execution plans. It shows the database engine’s process of executing the query, helping you identify bottlenecks.

---

3. Database Caching Strategies

Question: What is caching, and how can it improve performance?

Answer:
Caching stores frequently accessed data in memory, reducing the need to query the database repeatedly. This improves read performance significantly, especially for data that doesn’t change often.

Question: When should you use database caching?

Answer:

• For read-heavy applications where the same data is queried frequently.
• When data is relatively static and doesn’t change often.

---

4. Partitioning for Performance Scaling

Question: What is database partitioning?

Answer:
Partitioning is the process of splitting a large database into smaller, more manageable pieces (partitions). It can be done based on key columns like date or region. This improves query performance and scalability by reducing the amount of data the database has to scan.

Question: When should you implement partitioning?

Answer:
Partitioning is useful when dealing with large tables that would otherwise be slow to query or maintain. It’s especially beneficial for time-series data or large transactional tables.

---

5. Backup and Restore for Database Recovery

Question: What are the best practices for database backups?

Answer:

• Full backups: Perform regular full backups of the entire database to ensure you have a copy of all data.
• Incremental backups: Backup only the changes since the last backup, to save time and storage.
• Backup off-site: Store backups in a different location to ensure they are protected from physical disasters.

Question: How do you ensure backups are reliable?

Answer:

• Regularly test backups by performing restore operations in a staging environment.
• Monitor backup processes and alert when backups fail or are incomplete.

---

6. Point-in-Time Recovery (PITR)

Question: What is Point-in-Time Recovery (PITR)?

Answer:
PITR is a method of restoring a database to a specific point in time, typically using a combination of backups and transaction logs. It’s useful when data corruption or accidental deletion occurs, and you need to restore the system to a known good state.

Question: How do you configure PITR?

Answer:
Ensure that you regularly capture transaction logs and have a reliable full backup. Transaction logs are applied sequentially during recovery to bring the database up to the desired point in time.

---

7. Redo Logs for Data Recovery

Question: What are redo logs, and how do they aid in recovery?

Answer:
Redo logs capture all changes made to the database, ensuring that you can recover any changes that were committed, even in the event of a system crash. These logs are essential for restoring data to its last consistent state.

Question: How do you manage redo logs effectively?

Answer:

• Ensure redo logs are stored on fast storage devices for quick recovery.
• Regularly archive old logs to prevent disk space from running out.

---

8. Database Failover and High Availability

Question: What is database failover?

Answer:
Database failover is the process of switching to a backup database in the event of a failure of the primary database. It’s crucial for ensuring high availability and minimizing downtime in critical applications.

Question: What are common strategies for database failover?

Answer:

• Active-Active replication: Both databases are active, and traffic is balanced between them.
• Active-Passive replication: The backup database is passive and only activated when the primary fails.

---

9. Deadlocks and Lock Management

Question: What is a deadlock in a database, and how do you resolve it?

Answer:
A deadlock occurs when two or more transactions are waiting for each other to release locks on resources. Resolving deadlocks usually involves either aborting one of the transactions or using timeout strategies to prevent them.

Question: How do you prevent deadlocks?

Answer:

• Ensure that transactions acquire locks in a consistent order.
• Use lower isolation levels where appropriate to reduce locking.

---

10. Indexing Strategies for Performance Improvement

Question: How can you improve database performance using indexes?

Answer:

• Use composite indexes for queries involving multiple columns.
• Monitor index usage: Remove unused indexes as they consume resources during write operations.
• Consider covering indexes: These indexes include all the columns needed for a query, eliminating the need to access the table.

---

For more insights into database optimization, performance, and recovery strategies, visit my blog.