- Published on
PostgreSQL
- Authors
- Name
- Lucian Oprea
- @LucianDSA_
00:30:00
Basics and Fundamentals
⏷ 1. What is PostgreSQL, and how is it different from other RDBMS?
⏷ 2. Explain the concept of ACID properties in PostgreSQL.
⏷ 3. How do schemas work in PostgreSQL?
⏷ 4. How would you handle schema versioning in a PostgreSQL database shared across multiple microservices?
⏷ 5. What are the advantages and limitations of PostgreSQL's MVCC (Multi-Version Concurrency Control)?
⏷ 6. How does PostgreSQL ensure data durability during a system crash?
Installation and Configuration
⏷ 7. How do you install PostgreSQL on a Linux system?
⏷ 8. What are the key parameters in the postgresql.conf file?
⏷ 9. Explain the purpose of pg_hba.conf.
⏷ 10. How would you optimize the shared_buffers and work_mem parameters for a high-performance OLTP workload?
⏷ 11. What is the difference between host-based and client authentication in PostgreSQL, and when would you use each?
⏷ 12. How would you troubleshoot a database connection issue caused by misconfiguration in pg_hba.conf?
Data Modeling and Schema Design
⏷ 13. How do you design a many-to-many relationship in PostgreSQL?
⏷ 14. What are the different types of indexes supported by PostgreSQL?
⏷ 15. Explain the use of FOREIGN KEY and CHECK constraints.
⏷ 16. How would you design a schema to support soft deletes in PostgreSQL?
⏷ 17. What are the advantages and disadvantages of denormalization in PostgreSQL schema design?
⏷ 18. How do you design a schema for a multi-tenant application with tenant-specific data isolation?
Querying and Optimization
⏷ 19. How does the EXPLAIN command help in query optimization?
⏷ 20. What are common techniques for optimizing slow queries in PostgreSQL?
⏷ 21. How do CTEs (Common Table Expressions) work in PostgreSQL?
⏷ 22. How would you optimize a query involving multiple joins and aggregate functions in a large dataset?
⏷ 23. What is the difference between EXPLAIN and EXPLAIN ANALYZE, and when would you use each?
⏷ 24. How do you identify and address sequential scans in queries when indexes are available?
⏷ 25. What are materialized views, and how are they used?
⏷ 26. How does PostgreSQL handle JSON and JSONB data types?
⏷ 27. What is the purpose of table inheritance in PostgreSQL?
⏷ 28. How would you use PostgreSQL’s JSONB features to store and query semi-structured data efficiently?
⏷ 29. What are the trade-offs of using materialized views versus regular views in a high-update workload?
⏷ 30. How would you implement table partitioning for a time-series dataset and ensure efficient query performance?
Backup and Recovery
⏷ 31. How do you create a backup using pg_dump?
⏷ 32. What is the difference between logical and physical backups?
⏷ 33. How does point-in-time recovery (PITR) work in PostgreSQL?
⏷ 34. How would you restore a specific schema from a database dump while excluding others?
⏷ 35. What are the implications of using WAL archiving for continuous backups, and how would you troubleshoot issues with it?
⏷ 36. How do you verify the integrity of a restored backup in PostgreSQL?
Security
⏷ 37. How do roles and privileges work in PostgreSQL?
⏷ 38. How can you encrypt data in PostgreSQL?
⏷ 39. How would you design a role hierarchy to enforce least-privilege access in a multi-tenant system?
⏷ 40. How would you implement SSL for secure client-server communication in PostgreSQL?
⏷ 41. How do you prevent SQL injection in applications interacting with a PostgreSQL database?
Replication and High Availability
⏷ 42. What is streaming replication in PostgreSQL?
⏷ 43. How does logical replication differ from physical replication?
⏷ 44. What is the role of pg_rewind in failover scenarios?
⏷ 45. How would you configure synchronous replication to ensure zero data loss in a PostgreSQL cluster?
⏷ 46. What challenges might you face when setting up logical replication for a database with high write throughput?
⏷ 47. How would you handle a split-brain scenario in a PostgreSQL high-availability setup?
Extensions and Customization
⏷ 48. What are extensions in PostgreSQL, and how do you install them?
⏷ 49. Explain the purpose of pg_stat_statements.
⏷ 50. How do you create a custom function in PostgreSQL?
⏷ 51. How would you address autovacuum issues in a table experiencing frequent updates and deletions?
⏷ 52. How do you identify and resolve long-running transactions in a busy PostgreSQL system?
⏷ 53. What are the performance implications of increasing the work_mem parameter for query optimization?