PostgreSQL DBA Interview Questions and Answers

 

PostgreSQL DBA interview questions with detailed answers.

🔹 Basic PostgreSQL Concepts

1. What is PostgreSQL? How is it different from MySQL or Oracle?

Answer:
PostgreSQL is an advanced open-source object-relational database system with strong support for SQL standards, JSON, full ACID compliance, and extensibility (e.g., custom data types, functions).
Differences:

• vs MySQL: PostgreSQL is more standards-compliant and better at handling complex queries, indexing, and concurrency.

• vs Oracle: Oracle is proprietary and offers advanced features, but PostgreSQL provides similar capabilities in open-source form.

---

2. How do you check the PostgreSQL version?

Answer:

bash

psql --version

Or inside psql:

sql

SELECT version();

---

3. What are schemas in PostgreSQL?

Answer:
Schemas are namespaces within a database. They allow you to organize tables, views, functions, etc., and prevent name conflicts.

---

4. What is the role of pg_hba.conf and postgresql.conf?

Answer:

• pg_hba.conf: Defines client authentication rules.

• postgresql.conf: Controls database server configuration (memory, logging, replication, etc.).

---

5. What data types are supported in PostgreSQL?

Answer:
Standard types: INTEGER, TEXT, BOOLEAN, DATE, NUMERIC
Advanced: JSON/JSONB, ARRAY, HSTORE, UUID, INET, TSVECTOR

---

6. What is a sequence in PostgreSQL?

Answer:
A sequence is a special kind of object used to generate unique numeric identifiers, often used for auto-incrementing primary keys.

---

🔹 SQL & Administration

7. How do you create and manage users and roles in PostgreSQL?

Answer:

sql

CREATE USER myuser WITH PASSWORD 'pass';
GRANT CONNECT ON DATABASE mydb TO myuser;
ALTER USER myuser WITH SUPERUSER;  -- if needed

---

8. How do you check current connections?

Answer:

sql

SELECT * FROM pg_stat_activity;

---

9. How do you terminate an active session?

Answer:

sql

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';

---

10. How do you monitor query performance?

Answer:

• Enable pg_stat_statements extension.

• Use:

sql

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

---

11. How do you list all tables in PostgreSQL?

Answer:

• Inside psql: \dt

• Or SQL:

sql

SELECT table_name FROM information_schema.tables WHERE table_schema='public';

---

12. Difference between a table and a materialized view?

Answer:

• Table: Stores actual data and is updated real-time.

• Materialized View: Stores the result of a query physically and needs to be refreshed manually.

---

🔹 Backup and Recovery

13. What are the different backup types in PostgreSQL?

Answer:

• Logical: pg_dump, pg_dumpall

• Physical: pg_basebackup

• PITR: Continuous WAL archiving

---

14. How to perform full logical backup using pg_dump?

Answer:

bash

pg_dump -U postgres -F c -b -v -f mydb.backup mydb

---

15. What is pg_basebackup?

Answer:
It’s a tool for physical base backups used with streaming replication setups. Captures the entire data directory.

---

16. How to restore a .sql or .dump file?

Answer:

bash

psql -U postgres -d mydb -f mydb.sql   # for .sql files
pg_restore -U postgres -d mydb mydb.backup   # for .dump files

---

17. What is PITR and how is it done?

Answer:
Point-In-Time Recovery lets you restore a DB to a specific timestamp using a base backup + WAL files.
Steps:

• Take base backup.

• Archive WAL logs.

• Restore backup.

• Set recovery_target_time in recovery.conf.

---

🔹 Performance Tuning

18. How to identify slow-running queries?

Answer:
Enable logging:

conf

log_min_duration_statement = 1000  # Logs queries longer than 1 second

Or use pg_stat_statements.

---

19. What is EXPLAIN and EXPLAIN ANALYZE?

Answer:

• EXPLAIN: Shows the query plan.

• EXPLAIN ANALYZE: Executes the query and shows actual time taken at each step.

---

20. Key performance parameters:

Answer:

• work_mem: Memory per query operation (e.g., sort)

• shared_buffers: Memory for caching

• effective_cache_size: Estimation of available OS cache

• maintenance_work_mem: For vacuum and create index operations

---

21. What is the difference between VACUUM, VACUUM FULL, and ANALYZE?

Answer:

• VACUUM: Cleans up dead tuples.

• VACUUM FULL: Rewrites and compacts table (locks table).

• ANALYZE: Updates planner stats.

---

22. What causes table bloat and how to fix it?

Answer:
Bloat is caused by dead tuples left behind by UPDATE/DELETE.
Fix: Run VACUUM, VACUUM FULL, or pg_repack.

---

🔹 Replication & High Availability

23. What is streaming replication and how to set it up?

Answer:
Primary continuously streams WAL changes to standby.
Setup involves:

• Enable WAL on primary (wal_level = replica)

• Use pg_basebackup to clone

• Configure primary_conninfo on standby

---

24. Difference between synchronous and asynchronous replication?

Answer:

• Synchronous: Primary waits for standby to confirm.

• Asynchronous: Primary does not wait; better performance, less safe.

---

25. What is a replication slot?

Answer:
A mechanism to ensure the primary retains WAL files until a subscriber or replica has received them.

---

26. What happens when the primary goes down?

Answer:
If synchronous: standby may block writes. If configured properly, standby can be promoted to primary using:

bash

pg_ctl promote

---

27. How to monitor replication lag?

Answer:

sql

SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

---

🔹 Security

28. How is authentication handled in PostgreSQL?

Answer:
Defined in pg_hba.conf using methods like md5, trust, peer, or cert.

---

29. How to restrict user access to certain tables?

Answer:

sql

REVOKE ALL ON table_name FROM public;
GRANT SELECT ON table_name TO myuser;

---

30. What is row-level security and how is it implemented?

Answer:
Allows filtering rows per user/condition:

sql

ALTER TABLE tablename ENABLE ROW LEVEL SECURITY;
CREATE POLICY policy_name ON tablename USING (user_id = current_user);

---

🔹 Troubleshooting & Real-World Scenarios

31. PostgreSQL is running but not accepting connections — what do you check?

Answer:

• pg_hba.conf rules

• Port in postgresql.conf

• listen_addresses

• OS firewall or SELinux

• Max connections

---

32. What if autovacuum is not working?

Answer:
Check:

• If autovacuum is enabled in postgresql.conf

• Table thresholds exceeded

• Long-running transactions blocking cleanup

---

33. How do you find long-running queries?

Answer:

sql

SELECT pid, now() - query_start AS runtime, query FROM pg_stat_activity WHERE state = 'active';

---

34. User reports missing table – what do you do?

Answer:

• Check schema and ownership.

• Check logs for DROP statements.

• Restore from backup if dropped.

---

35. How to find blocking sessions and resolve deadlocks?

Answer:

sql

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid
FROM pg_locks blocked
JOIN pg_locks blocking ON blocking.locktype = blocked.locktype
AND blocked.pid != blocking.pid
JOIN pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid
JOIN pg_stat_activity blocking_activity ON blocking.pid = blocking_activity.pid
WHERE NOT blocked.granted;

To terminate:

sql

SELECT pg_terminate_backend(pid);