100+ PostgreSQL DBA Interview Questions and Answers [2025 Guide]

This comprehensive guide presents 100+ essential PostgreSQL Database Administrator (DBA) interview questions and answers, organized by topic. Whether you're preparing for a junior or senior PostgreSQL DBA interview, studying for PostgreSQL certification, or looking to assess your database administration expertise, these questions cover all key aspects of PostgreSQL administration and management.

Want to master PostgreSQL?
Check out our comprehensive PostgreSQL DBA Course! This course covers everything from basic administration to advanced topics, helping you become a proficient PostgreSQL Database Administrator. Perfect for both beginners and experienced DBAs looking to enhance their skills.

Basic Concepts (1-10)

1. What is PostgreSQL?

   • Open-source RDBMS emphasizing extensibility and SQL compliance
   • Object-relational database supporting both relational and non-relational features

2. What are the key features of PostgreSQL?

   • ACID compliance
   • Multi-Version Concurrency Control (MVCC)
   • Table inheritance
   • Custom functions
   • Table partitioning
   • Rich indexing options

3. What is MVCC in PostgreSQL?

   • Mechanism allowing multiple transactions to access the same data simultaneously
   • Each transaction sees a snapshot of data from a particular point in time

4. What are roles in PostgreSQL?

   • Database objects managing database access permissions
   • Can be users or groups
   • Support inheritance of permissions

5. What is WAL in PostgreSQL?

   • Write-Ahead Logging
   • Ensures data integrity by logging changes before they're written to data files

6. What is the postgresql.conf file?

   • Main configuration file containing database parameters
   • Controls memory, connections, replication, etc.

7. What is the pg_hba.conf file?

   • Host-Based Authentication configuration file
   • Controls client authentication methods and access

8. What are tablespaces in PostgreSQL?

   • Physical locations where databases store their data
   • Allow distributing database objects across different storage devices

9. What is the difference between TRUNCATE and DELETE?

   • TRUNCATE is faster, removes all rows at once
   • DELETE removes rows one by one, can use WHERE clause

10. What is autovacuum in PostgreSQL?

    • Automated process that removes dead tuples and updates statistics
    • Essential for maintaining database performance

Backup & Recovery (11-20)

11. What is pg_dump?

    • Logical backup tool for single database
    • Creates SQL script or archive file format

12. What is pg_basebackup?

    • Physical backup tool for entire cluster
    • Creates exact copy of database files

13. What is Point-in-Time Recovery (PITR)?

    • Ability to restore database to specific point in time
    • Uses WAL archives and base backup

14. What is continuous archiving?

    • Method of archiving WAL files continuously
    • Essential for PITR capability

15. How do you take a hot backup?

    • Use pg_basebackup while database is running
    • Ensure archive_mode is enabled

16. What is recovery.conf?

    • In PostgreSQL <12: Recovery configuration file
    • In PostgreSQL ≥12: Uses recovery.signal and standby.signal

17. What is archive_command?

    • Configuration parameter specifying WAL archiving method
    • Example: 'cp %p /archive/%f'

18. What is restore_command?

    • Specifies how to retrieve archived WAL files during recovery
    • Example: 'cp /archive/%f %p'

19. What is recovery_target_time?

    • Parameter specifying point-in-time for recovery
    • Format: 'YYYY-MM-DD HH:MM:SS'

20. What is pg_dumpall?

    • Backs up entire PostgreSQL cluster
    • Includes roles and tablespace definitions

Performance Tuning (21-30)

21. What is EXPLAIN ANALYZE?

    • Shows query execution plan with actual timings
    • Helps identify performance bottlenecks

22. What are statistics in PostgreSQL?

    • Data about tables and columns
    • Used by query planner to create efficient plans

23. What is shared_buffers?

    • Memory parameter for shared data cache
    • Typically 25% of total RAM

24. What is work_mem?

    • Memory for sort operations and hash tables
    • Set per operation, not total

25. What is effective_cache_size?

    • Estimate of disk cache available
    • Influences query planner decisions

26. What is vacuum?

    • Reclaims storage from dead tuples
    • Updates statistics for query planner

27. What is analyze?

    • Updates statistics about table contents
    • Improves query planner estimates

28. What are index types in PostgreSQL?

    • B-tree (default)
    • Hash
    • GiST
    • SP-GiST
    • GIN
    • BRIN

29. What is table partitioning?

    • Splitting large tables into smaller pieces
    • Improves query performance and manageability

30. What is parallel query?

    • Multiple workers executing parts of query
    • Controlled by max_parallel_workers parameter

High Availability (31-40)

31. What is streaming replication?

    • Real-time WAL shipping to standby servers
    • Provides high availability

32. What is synchronous replication?

    • Ensures transaction commit waits for standby
    • Provides stronger consistency guarantees

33. What is Patroni?

    • HA solution for PostgreSQL
    • Manages failover and replication

34. What is pg_rewind?

    • Tool to re-synchronize primary and standby
    • Used after failback scenarios

35. What is replication slot?

    • Prevents removal of needed WAL files
    • Ensures standby doesn't fall behind

36. What is hot_standby?

    • Allows queries on standby server
    • May impact replication performance

37. What is promote_trigger_file?

    • File triggering standby promotion
    • Used in manual failover

38. What is primary_conninfo?

    • Connection string to primary server
    • Used by standby for replication

39. What is application_name in replication?

    • Identifies standby servers
    • Used in monitoring and configuration

40. What is pg_stat_replication?

    • View showing replication status
    • Shows lag and connection details

Security (41-50)

41. What is SSL in PostgreSQL?

    • Encrypts client/server communications
    • Configured in postgresql.conf

42. What is Row-Level Security (RLS)?

    • Fine-grained access control at row level
    • Implemented using policies

43. What are GRANT/REVOKE?

    • Commands managing permissions
    • Control access to database objects

44. What is password encryption?

    • Passwords stored using SCRAM-SHA-256
    • Previous methods: MD5, password

45. What is client authentication?

    • Methods: trust, password, md5, cert
    • Configured in pg_hba.conf

46. What is pgcrypto?

    • Extension for cryptographic functions
    • Supports various encryption methods

47. What are connection limits?

    • max_connections parameter
    • Per-role connection limits

48. What is pg_audit?

    • Extension for detailed audit logging
    • Tracks database activities

49. What is SSL certificate authentication?

    • Client authentication using certificates
    • Requires SSL configuration

50. What are default privileges?

    • ALTER DEFAULT PRIVILEGES command
    • Sets permissions for new objects

Monitoring (51-60)

51. What is pg_stat_activity?

    • View showing server process activities
    • Shows current queries and states

52. What is pg_stat_statements?

    • Extension tracking query execution statistics
    • Shows query performance metrics

53. What are wait events?

    • Show what processes are waiting for
    • Help identify bottlenecks

54. What is log_min_duration_statement?

    • Logs queries exceeding specified duration
    • Helps identify slow queries

55. What is pg_stat_bgwriter?

    • Shows background writer statistics
    • Helps tune checkpoints

56. What is pg_stat_database?

    • Database-wide statistics
    • Shows transactions, tuples, cache hits

57. What is track_io_timing?

    • Enables I/O timing measurements
    • Shows detailed I/O statistics

58. What is log_checkpoints?

    • Logs checkpoint activity
    • Helps monitor I/O activity

59. What is pg_stat_user_tables?

    • Table access and update statistics
    • Shows vacuum and analyze activity

60. What is pg_locks?

    • Shows current locks
    • Helps diagnose blocking issues

Cloud & AWS RDS (61-70)

61. What is RDS for PostgreSQL?

    • AWS managed PostgreSQL service
    • Handles maintenance and backups

62. What are parameter groups in RDS?

    • Manage database configuration
    • Some parameters can't be modified

63. What is Multi-AZ in RDS?

    • Automatic failover capability
    • Synchronous replication to standby

64. What is RDS read replica?

    • Asynchronous replication copy
    • Offloads read operations

65. What is Aurora PostgreSQL?

    • AWS-optimized PostgreSQL variant
    • Claims better performance

66. What is RDS automated backup?

    • Daily backups and transaction logs
    • Enables point-in-time recovery

67. What is RDS encryption?

    • At-rest encryption using KMS
    • SSL for in-transit encryption

68. What is RDS monitoring?

    • Enhanced monitoring metrics
    • Performance Insights

69. What is RDS proxy?

    • Connection pooling service
    • Reduces connection management overhead

70. What is RDS event notification?

    • SNS notifications for RDS events
    • Monitors database changes

Azure PostgreSQL (71-80)

71. What is Azure Database for PostgreSQL?

    • Microsoft's managed PostgreSQL service
    • Offers single server and flexible server

72. What is Flexible Server?

    • Newer deployment option
    • More control over configuration

73. What is Azure high availability?

    • Zone-redundant HA
    • Automatic failover

74. What is Azure backup?

    • Automated backups
    • Geo-redundant storage option

75. What is Azure networking?

    • Private Link
    • VNET integration

76. What is Azure monitoring?

    • Azure Monitor integration
    • Query Performance Insight

77. What is Azure scaling?

    • Vertical and horizontal scaling
    • Storage autogrowth

78. What is Azure maintenance?

    • Planned maintenance windows
    • Automatic minor version updates

79. What is Azure firewall?

    • Server-level firewall rules
    • VNET service endpoints

80. What is Azure PostgreSQL pricing?

    • Compute + Storage pricing model
    • Reserved capacity options

Advanced Features (81-90)

81. What is pgvector?

    • Vector similarity search extension
    • Used for AI/ML applications

82. What is pg_cron?

    • Job scheduling extension
    • Runs periodic SQL commands

83. What is pg_stat_monitor?

    • Advanced monitoring extension
    • Provides detailed query statistics

84. What is logical replication?

    • Replication at object level
    • Supports selective replication

85. What is pg_partman?

    • Partition management extension
    • Automates partition creation

86. What is pg_bouncer?

    • Connection pooler
    • Reduces connection overhead

87. What is Foreign Data Wrapper?

    • Connects to external data sources
    • Supports various data formats

88. What is pg_upgrade?

    • Upgrades PostgreSQL version
    • Supports fast and link modes

89. What is event trigger?

    • Triggers on database events
    • DDL operation notifications

90. What is table sampling?

    • TABLESAMPLE clause
    • Quick data sampling methods

Troubleshooting (91-100)

91. How to handle connection issues?

    • Check pg_hba.conf
    • Verify network connectivity
    • Check max_connections

92. How to identify slow queries?

    • Use pg_stat_statements
    • Check log_min_duration_statement
    • EXPLAIN ANALYZE

93. How to handle disk full?

    • VACUUM FULL
    • Remove unused indexes
    • Archive old data

94. How to fix high CPU usage?

    • Check for missing indexes
    • Optimize queries
    • Adjust work_mem

95. How to handle replication lag?

    • Monitor pg_stat_replication
    • Check network bandwidth
    • Adjust wal_keep_segments

96. How to fix database bloat?

    • Regular VACUUM
    • Adjust autovacuum settings
    • Monitor table/index bloat

97. How to handle connection pooling?

    • Use pg_bouncer
    • Adjust pool_size
    • Monitor pool statistics

98. How to fix lock contention?

    • Monitor pg_locks
    • Optimize transaction duration
    • Adjust lock timeout

99. How to handle index corruption?

    • REINDEX command
    • Check system logs
    • Verify disk health

100. How to improve checkpoint performance?

     • Adjust checkpoint_timeout
     • Set checkpoint_completion_target
     • Monitor checkpoint timing