SQL Queries & Database Optimization Flashcards
What’s the difference between a clustered index and a non-clustered index?
Clustered Index: Sorts the data physically in the table based on the index.
Non-Clustered Index: Creates a separate structure that points to the rows in the table, without altering the physical order.
How would you optimize a slow-running query in Postgres?
Use EXPLAIN ANALYZE to inspect the query plan, add appropriate indexes, optimize joins, and reduce the data being fetched. You might also consider denormalization for frequently accessed data.
What are common performance bottlenecks in databases, and how can you address them?
Bottlenecks include slow queries, excessive indexing, and large datasets. Solutions include proper indexing, query optimization, caching, and sharding large datasets, as well as monitoring resource usage and optimizing the database schema to improve performance and reduce latency.
How does PostgreSQL handle concurrency?
PostgreSQL uses Multi-Version Concurrency Control (MVCC), which allows multiple transactions to occur simultaneously without locking the data. MVCC ensures that transactions work on snapshots of the data and maintain isolation. Readers do not block writers, and writers do not block readers, making it more efficient in high-read environments. PostgreSQL also runs a VACUUM process periodically to clean up old row versions and reclaim space.
