Prepare for Your PostgreSQL Interview with These Essential Questions
Whether you’re a fresher, have 1-3 years of experience, or are a seasoned professional with 3-6 years in database management, mastering PostgreSQL interview questions is crucial for success at companies like Zoho, Atlassian, or Paytm. This comprehensive guide covers 30 PostgreSQL interview questions arranged by difficulty: basic, intermediate, and advanced. Each question includes clear, practical answers with code examples where applicable.
Basic PostgreSQL Interview Questions (1-10)
1. What are the key data types available in PostgreSQL?
PostgreSQL supports various data types including Boolean, CHAR, VARCHAR, INTEGER, FLOAT(n), UUID, DATE, TIMESTAMP, JSON, and ARRAY. These types handle everything from simple numbers to complex structured data.[1][4]
2. How do you create a new database in PostgreSQL?
Use the CREATE DATABASE command followed by the database name. Example:
CREATE DATABASE mydb;
CREATE DATABASE mydb WITH OWNER = myuser ENCODING = 'UTF8';[3][4]
3. What is the difference between a role and a user in PostgreSQL?
A role is a database object that can own database objects and have privileges. A user is a role with the LOGIN attribute enabled, allowing database connections.[1][3]
4. How do you check if the PostgreSQL server is running?
Use pg_isready command or connect using psql -U username -d database. You can also check the process with pg_ctl status.[3]
5. Write a query to select the first 10 records from a table.
Use the LIMIT clause:
SELECT * FROM employees LIMIT 10;[3]
6. What are the main constraints supported by PostgreSQL?
PostgreSQL supports NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and EXCLUSION constraints to enforce data integrity.[4]
7. How do you add new data to a PostgreSQL table?
Use the INSERT INTO statement:
INSERT INTO employees (name, salary) VALUES ('John Doe', 50000);[4]
8. What command deletes a database in PostgreSQL?
Use DROP DATABASE database_name;. Ensure no active connections exist before dropping.[4]
9. How do you find the size of a database?
Query the pg_database system catalog:
SELECT pg_size_pretty(pg_database_size('mydb'));[3]
10. What languages does PostgreSQL support for writing functions?
PostgreSQL supports PL/pgSQL (default), SQL, C, Python (via extensions), Perl, and Tcl for procedural language functions.[3]
Intermediate PostgreSQL Interview Questions (11-20)
11. What is an index in PostgreSQL and why use it?
An index is a data structure that improves query performance by allowing faster data retrieval. Common types include B-tree (default), Hash, GIN, GiST, BRIN.[1][4][5]
12. Explain the difference between PRIMARY KEY and FOREIGN KEY.
PRIMARY KEY ensures unique, non-null values in a column. FOREIGN KEY maintains referential integrity between tables by linking to a primary key in another table.[4]
13. What is a schema in PostgreSQL?
A schema is a logical container for database objects like tables, views, and functions. The default schema is ‘public’. Use CREATE SCHEMA myschema;.[3][4]
14. How do you change the data type of a column?
Use ALTER TABLE with ALTER COLUMN:
ALTER TABLE employees ALTER COLUMN salary TYPE DECIMAL(10,2);[3]
15. What are the different types of operators in PostgreSQL?
PostgreSQL supports arithmetic (+, -, *, /), comparison (=, <, >), logical (AND, OR, NOT), and bitwise operators (&, |, ~).[4]
16. How do you delete all data from a table without dropping it?
Use TRUNCATE TABLE table_name; for faster deletion than DELETE, which also resets sequences if CASCADE is used.[3]
17. What is MVCC in PostgreSQL?
Multi-Version Concurrency Control (MVCC) allows readers to access consistent snapshots of data without blocking writers, enabling high concurrency.[3]
18. How do you create a user and grant privileges in PostgreSQL?
Create role and grant login:
CREATE ROLE myuser WITH LOGIN PASSWORD 'securepass';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;[1]
19. What are triggers in PostgreSQL?
Triggers are functions automatically executed on table events like INSERT, UPDATE, DELETE. They enforce business rules or maintain data integrity.[4]
20. How do you monitor long-running queries?
Query pg_stat_activity view:
SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active';[2]
Advanced PostgreSQL Interview Questions (21-30)
21. What are the types of replication in PostgreSQL?
Asynchronous (performance-focused), Synchronous (zero data loss), and Logical (selective table replication).[1]
22. How do you kill a session in PostgreSQL?
Use SELECT pg_terminate_backend(pid); to terminate gracefully or pg_cancel_backend(pid); to cancel query only.[2]
23. What is table partitioning and its advantages?
Table partitioning divides large tables into smaller partitions based on range, list, or hash. Advantages: better query performance, easier maintenance.[2]
24. Explain different index types available in PostgreSQL.
B-tree (default), Hash (= queries), GIN (arrays/JSON), GiST (geospatial), BRIN (large sorted data), SP-GiST (hierarchical data).[1][5]
25. How do you handle index corruption?
Rebuild using REINDEX INDEX index_name; or REINDEX DATABASE dbname; to restore integrity.[1]
26. What is GEQO in PostgreSQL?
Genetic Query Optimization uses genetic algorithms for non-exhaustive planning of large join queries, improving performance.[1]
27. How do you implement high availability in PostgreSQL?
Use streaming replication with tools like Patroni or repmgr for automatic failover and cluster management.[1][6]
28. What extensions have you used in PostgreSQL?
Common extensions: pg_stat_statements (query performance), pg_track_settings (parameter changes), PostGIS (geospatial).[2]
29. What parameters control synchronous replication?
synchronous_standby_names, synchronous_commit, and wal_sender_timeout manage replication behavior and commit waiting.[1]
30. In a scenario at Salesforce where query performance degrades during peak hours, how would you optimize using PostgreSQL features?
Analyze with EXPLAIN ANALYZE, add indexes on filtered columns, partition large tables, tune work_mem and maintenance_work_mem, vacuum regularly.[2][4]
