Top 40+ (Aurora) PostgreSQL Interview Questions - part 1

40 Best PostgreSQL/Aurora Postgres DBA Interview Questions - Part1

Q1. What is write-ahead logging (WAL)? or What are WAL files / segments?

Answer:

Q2. What is vacuuming?

Answer:

Q3. What is a vacuum full?

Answer:

Q4. What are the vacuum related init database parameters?

Answer:

Q5. What is auto vacuuming (or) what is the difference between normal vacuum and auto-vacuum?

Answer:

Q6. What are the auto vacuum related init database parameters?

Answer:

Q7. How you will confirm whether the auto-vacuum is completed or not?

Answer:

Q8. What is vacuum freeze?

Answer:

Q9. What are Transaction ID wraparound failures and how to avoid them?

Answer:

Q10. What is Frozen Transaction ID (or) what is the difference between normal Transaction ID and Frozen Transaction ID?

Answer:

Q11. How to reclaim space in Postgres?

Answer:

Q12. What is Multi-Version Concurrency Control (MVCC) in PostgreSQL?

Answer:

Q13. What are shared buffers (what is the use of shared_buffer parameter)?

Answer:

Q14. Why would Postgres not free up storage space even after the deletion of data?

Answer:

Q15. What is the use of pg_repack extension? How to remove bloating tables/indexes from (Aurora) Postgres databases?

Answer:

Q16. What is a checkpoint? how often checkpoint will happen?

Answer:

Q17. What is bgwriter? how often background writer should invoke?

Answer:

Q18. What are the toast tables?

Answer:

Q19. What is the meaning of table bloating? how to reduce table/index bloating?

Answer:

Q20. What is Host Based Access (HBA) control? How to allow/restrict a few IP ranges to connect to your Postgres database?

Answer:

Related PostgreSQL Articles:  vacuumdb utility commands in PostgreSQL   Aurora PostgreSQL Database Admin Interview Questions Part2

40 Best (Aurora) PostgreSQL Interview Questions - part 2

40 Top PostgreSQL/Aurora Postgres DBA Interview Questions - Part2

Q21. Which are the methods PostgreSQL provides to create a new database?

Answer: PostgreSQL provides the following methods to create a new database:

• Using CREATE DATABASE, an SQL command
• Using created a command-line executable

Q22. How do you delete the database in PostgreSQL?

Answer: We can delete the database by using any one of the below options:

• Using DROP DATABASE, an SQL command
• Using dropdb a command-line executable
  

Q23. What is table partitioning in Postgres?

Answer:

Q24. Explain PostgreSQL Inheritance. How does PostgreSQL implement table inheritance? What is the use of it?

Answer:

Q25. What types of indexes are supported in PostgreSQL?

Answer:

Q26. How to take the backup of PostgreSQL?

Answer:

Q27. What is a CTID of PostgreSQL?

Answer:

Q28. What are the different datatypes in PostgreSQL?

Answer:

Q29. How are stats/statistics updated in PostgreSQL?

Answer:

Q30. How to start a PostgreSQL database server?

Answer:

Q31. How To stop the PostgreSQL database server?

Answer:

Q32. How to check the status of the PostgreSQL server running or not?

Answer:

Q33. How to get a list of databases in a PostgreSQL cluster?

Answer:

Q34. How to take differential or incremental backup of PostgreSQL database?

Answer: There is NO utility for differential or incremental backups.

Q35. How to upgrade the Postgres database server?

Answer:

Q36. How crash recovery works in Postgres?

Answer:

Q37. What are the default tablespaces created in Postgres?

Answer:

Q38. What are the levels of isolation?

Answer:

Q39. How to setup logical replication between two AWS Aurora Postgres clusters?

Answer:

Q40. 

Answer:

Related Postgres Articles: PostgreSQL/Aurora Postgres Database Interview Questions Part1