Top 150+ PostgreSQL DBA, Developer, and Designer Interview Questions

 

Table of Contents

1. Beginner-Level Questions

   • PostgreSQL DBA

   • PostgreSQL Developer

   • PostgreSQL Designer

2. Intermediate-Level Questions

   • PostgreSQL DBA

   • PostgreSQL Developer

   • PostgreSQL Designer

3. Expert-Level Questions

   • PostgreSQL DBA

   • PostgreSQL Developer

   • PostgreSQL Designer

4. Conclusion

---

Beginner-Level Questions

These questions cover foundational concepts, basic SQL, and core PostgreSQL principles for entry-level candidates.

PostgreSQL DBA (Beginner)

1. What is PostgreSQL, and what are its key components?

   • Type: Basic Understanding

   • Expected Answer: PostgreSQL is an open-source RDBMS with components like the server, data files, and pgAdmin.

   • Example: Data directory stores tablespaces and WAL logs.

2. What is the difference between a PostgreSQL instance and a database?

   • Type: Conceptual

   • Expected Answer: An instance is the running server process, while a database is a collection of schemas within it.

3. What is the role of the postgresql.conf file?

   • Type: Conceptual

   • Expected Answer: The postgresql.conf file configures server settings like memory and connections.

4. What is a tablespace in PostgreSQL?

   • Type: Basic Understanding

   • Expected Answer: A tablespace is a logical storage location for database objects.

   • Example: CREATE TABLESPACE fast_storage LOCATION '/ssd/pgdata';

5. What is the purpose of the Write-Ahead Log (WAL)?

   • Type: Conceptual

   • Expected Answer: WAL records changes for crash recovery and replication.

6. What are the main PostgreSQL authentication methods?

   • Type: Conceptual

   • Expected Answer: Methods include trust, password, and GSSAPI, defined in pg_hba.conf.

7. What is a backup, and why is it important in PostgreSQL?

   • Type: Basic Understanding

   • Expected Answer: A backup is a copy of a database for recovery from data loss or corruption.

8. What is the difference between a user and a role in PostgreSQL?

   • Type: Conceptual

   • Expected Answer: Roles are entities with privileges; users are roles with login capability.

9. What is the purpose of the template0 and template1 databases?

   • Type: Conceptual

   • Expected Answer: template0 is a pristine template, while template1 is customizable for new databases.

10. What is a checkpoint in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: A checkpoint writes dirty buffers to disk, ensuring data consistency.

PostgreSQL Developer (Beginner)

11. What is SQL, and how does PostgreSQL use it?

    • Type: Basic Understanding

    • Expected Answer: SQL is a query language; PostgreSQL extends it with advanced features like JSONB.

    • Example:

      SELECT CURRENT_DATE;

12. What is a stored procedure in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: A stored procedure is a callable SQL block for reusable logic (PostgreSQL 11+).

13. What is the difference between a function and a stored procedure?

    • Type: Conceptual

    • Expected Answer: Functions return values and are query-friendly, while procedures focus on side effects.

    • Example:

      CREATE FUNCTION get_salary(emp_id INT) RETURNS INT AS $$
      DECLARE
        sal INT;
      BEGIN
        SELECT salary INTO sal FROM employees WHERE employee_id = emp_id;
        RETURN sal;
      END;
      $$ LANGUAGE plpgsql;

14. What is a trigger in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: A trigger executes a function on DML/DDL events.

    • Example:

      CREATE TRIGGER log_update
      AFTER UPDATE ON employees
      FOR EACH ROW
      EXECUTE FUNCTION log_update_func();

15. What is a cursor in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: A cursor processes query results row by row in PL/pgSQL.

    • Example:

      DECLARE emp_cursor CURSOR FOR SELECT employee_id FROM employees;
      OPEN emp_cursor;
      FETCH emp_cursor INTO v_emp_id;

16. What is the purpose of the SELECT ... FOR UPDATE statement?

    • Type: Practical

    • Expected Answer: It locks rows to prevent concurrent updates.

    • Example:

      SELECT * FROM employees WHERE employee_id = 100 FOR UPDATE;

17. What is a view in PostgreSQL?

    • Type: Basic Understanding

    • Expected Answer: A view is a virtual table based on a query.

    • Example:

      CREATE VIEW emp_view AS
      SELECT employee_id, first_name FROM employees;

18. What is the difference between DELETE and TRUNCATE?

    • Type: Conceptual

    • Expected Answer: DELETE removes specific rows with logging, while TRUNCATE removes all rows without logging.

19. What is an index, and why is it used?

    • Type: Basic Understanding

    • Expected Answer: An index speeds up query performance by organizing data.

    • Example:

      CREATE INDEX idx_emp_id ON employees(employee_id);

20. What is the purpose of the EXPLAIN command?

    • Type: Practical

    • Expected Answer: EXPLAIN shows the query execution plan for optimization.

    • Example:

      EXPLAIN SELECT * FROM employees WHERE employee_id = 100;

PostgreSQL Designer (Beginner)

21. What is database normalization, and why is it important?

    • Type: Basic Understanding

    • Expected Answer: Normalization reduces redundancy and ensures integrity using normal forms (1NF, 2NF, 3NF).

    • Example: Splitting orders and customers to avoid duplicate data.

22. What is a primary key in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: A primary key uniquely identifies rows and enforces integrity.

23. What is a foreign key, and how does it work?

    • Type: Conceptual

    • Expected Answer: A foreign key links tables, ensuring referential integrity.

    • Example:

      CREATE TABLE orders (
        order_id INT PRIMARY KEY,
        customer_id INT REFERENCES customers(customer_id)
      );

24. What is the difference between a logical and physical data model?

    • Type: Conceptual

    • Expected Answer: Logical models define data structure conceptually, while physical models specify storage.

25. What is an ERD (Entity-Relationship Diagram)?

    • Type: Basic Understanding

    • Expected Answer: An ERD visualizes entities, attributes, and relationships.

26. What is a materialized view in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: A materialized view stores query results physically for performance.

    • Example:

      CREATE MATERIALIZED VIEW mv_sales AS
      SELECT SUM(amount) FROM sales;

27. What is the purpose of a schema in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: A schema organizes database objects for modularity and access control.

28. What is the role of constraints in PostgreSQL?

    • Type: Basic Understanding

    • Expected Answer: Constraints enforce rules like uniqueness or nullability.

    • Example:

      ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id);

29. What is data modeling, and why is it important?

    • Type: Basic Understanding

    • Expected Answer: Data modeling designs the database structure to meet business needs and ensure efficiency.

30. What is the difference between 1NF and 2NF?

    • Type: Conceptual

    • Expected Answer: 1NF eliminates repeating groups, while 2NF ensures non-key attributes depend on the entire primary key.

---

Intermediate-Level Questions

These questions target candidates with 2–5 years of experience, focusing on practical scenarios, optimization, and advanced PostgreSQL features.

PostgreSQL DBA (Intermediate)

31. How do you monitor PostgreSQL performance?

    • Type: Practical

    • Expected Answer: Use pg_stat_activity, pg_stat_statements, or tools like pgAdmin.

    • Example:

      SELECT * FROM pg_stat_activity WHERE state = 'active';

32. What is the purpose of the pg_stat_statements extension?

    • Type: Conceptual

    • Expected Answer: Tracks query execution statistics for performance analysis.

33. How do you handle a PostgreSQL server crash?

    • Type: Scenario-Based

    • Expected Answer: Check logs, restore from backups, and replay WAL files.

    • Example:

      pg_restore --dbname=mydatabase backup.dump;

34. What is PostgreSQL streaming replication?

    • Type: Conceptual

    • Expected Answer: Streaming replication copies WAL logs to replicas for high availability.

    • Example:

      SELECT * FROM pg_stat_replication;

35. How do you configure a PostgreSQL backup?

    • Type: Practical

    • Expected Answer: Use pg_dump for logical backups or pg_basebackup for physical backups.

    • Example:

      pg_dump -U postgres mydatabase > backup.sql;

36. What is the difference between synchronous and asynchronous replication?

    • Type: Conceptual

    • Expected Answer: Synchronous replication waits for replica confirmation, while asynchronous does not.

37. How do you troubleshoot a slow PostgreSQL query?

    • Type: Scenario-Based

    • Expected Answer: Use EXPLAIN ANALYZE, check indexes, and optimize query structure.

    • Example:

      EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 50000;

38. What is the role of the WAL in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: WAL ensures durability and supports replication and recovery.

39. How do you manage user privileges in PostgreSQL?

    • Type: Practical

    • Expected Answer: Use GRANT and REVOKE to manage role-based privileges.

    • Example:

      GRANT SELECT ON employees TO hr_role;

40. What is the purpose of the pg_catalog schema?

    • Type: Conceptual

    • Expected Answer: pg_catalog stores system metadata for database objects.

    • Example:

      SELECT * FROM pg_catalog.pg_tables WHERE schemaname = 'public';

PostgreSQL Developer (Intermediate)

41. How do you optimize a PostgreSQL query for performance?

    • Type: Practical

    • Expected Answer: Use indexes, avoid correlated subqueries, and leverage CTEs.

    • Example:

      SELECT e.employee_id, e.first_name
      FROM employees e
      WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.employee_id);

42. What is a Common Table Expression (CTE) in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: A CTE is a temporary result set for query readability.

    • Example:

      WITH emp_cte AS (
        SELECT employee_id, first_name FROM employees WHERE salary > 50000
      )
      SELECT * FROM emp_cte;

43. How do you handle errors in PL/pgSQL?

    • Type: Practical

    • Expected Answer: Use EXCEPTION blocks for error handling.

    • Example:

      DO $$
      BEGIN
        PERFORM 1/0;
      EXCEPTION WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero detected';
      END;
      $$;

44. What is the difference between UNION and UNION ALL?

    • Type: Conceptual

    • Expected Answer: UNION removes duplicates, while UNION ALL is faster and keeps all rows.

45. How do you implement pagination in a PostgreSQL query?

    • Type: Coding Challenge

    • Expected Answer:

      SELECT * FROM employees
      ORDER BY employee_id
      LIMIT 10 OFFSET 20;

46. What is a table function in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: A table function returns a result set, used in queries.

    • Example:

      CREATE FUNCTION get_employees(dept_id INT) RETURNS TABLE (id INT, name TEXT) AS $$
      SELECT employee_id, first_name FROM employees WHERE department_id = dept_id;
      $$ LANGUAGE SQL;

47. How do you use dynamic SQL in PostgreSQL?

    • Type: Practical

    • Expected Answer: Use EXECUTE in PL/pgSQL for dynamic queries.

    • Example:

      DO $$
      BEGIN
        EXECUTE 'SELECT * FROM employees WHERE salary > $1' USING 50000;
      END;
      $$;

48. What is the purpose of the ON CONFLICT clause?

    • Type: Conceptual

    • Expected Answer: ON CONFLICT handles unique key conflicts with updates or skips.

    • Example:

      INSERT INTO employees (employee_id, salary)
      VALUES (100, 60000)
      ON CONFLICT (employee_id) DO UPDATE SET salary = 60000;

49. How do you create a sequence in PostgreSQL?

    • Type: Practical

    • Expected Answer:

      CREATE SEQUENCE emp_seq START 1 INCREMENT 1;

50. What is the difference between INNER JOIN and LEFT JOIN?

    • Type: Conceptual

    • Expected Answer: INNER JOIN returns matching rows, while LEFT JOIN includes all rows from the left table.

PostgreSQL Designer (Intermediate)

51. How do you design a database schema for a multi-tenant application?

    • Type: Scenario-Based

    • Expected Answer: Use separate schemas, databases, or tenant IDs in tables.

    • Example:

      CREATE SCHEMA tenant1;
      CREATE TABLE tenant1.employees (employee_id INT, name TEXT);

52. What is the role of indexing in database design?

    • Type: Conceptual

    • Expected Answer: Indexes improve query performance but increase storage and maintenance.

53. How do you handle denormalization in database design?

    • Type: Scenario-Based

    • Expected Answer: Denormalize for read-heavy systems, balancing redundancy and performance.

54. What is a composite key, and when is it used?

    • Type: Conceptual

    • Expected Answer: A composite key uses multiple columns for uniqueness when no single column suffices.

55. How do you model a many-to-many relationship?

    • Type: Practical

    • Expected Answer: Use a junction table with foreign keys.

    • Example:

      CREATE TABLE student_courses (
        student_id INT,
        course_id INT,
        PRIMARY KEY (student_id, course_id)
      );

56. What is the role of table partitioning in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: Partitioning splits large tables for performance and manageability.

    • Example:

      CREATE TABLE sales (
        sale_id INT,
        sale_date DATE
      ) PARTITION BY RANGE (sale_date);

57. How do you ensure data integrity in a database design?

    • Type: Practical

    • Expected Answer: Use constraints, triggers, and foreign keys.

58. What is the difference between BCNF and 3NF?

    • Type: Conceptual

    • Expected Answer: BCNF is stricter, ensuring no non-trivial functional dependencies.

59. How do you design a database for scalability?

    • Type: Scenario-Based

    • Expected Answer: Use partitioning, sharding, and replication.

60. What is the purpose of the information_schema database?

    • Type: Conceptual

    • Expected Answer: information_schema provides metadata about database objects.

    • Example:

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

---

Expert-Level Questions

These questions challenge senior professionals with complex scenarios, advanced PostgreSQL features, and critical system design tasks.

PostgreSQL DBA (Expert)

61. How do you implement PostgreSQL logical replication?

    • Type: Scenario-Based

    • Expected Answer: Configure publications and subscriptions for logical replication.

    • Example:

      CREATE PUBLICATION my_pub FOR TABLE employees;
      CREATE SUBSCRIPTION my_sub CONNECTION 'host=remote dbname=mydb' PUBLICATION my_pub;

62. What is the role of the shared buffer in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: Shared buffers cache data pages in memory for faster access.

63. How do you recover a PostgreSQL database from a corrupted table?

    • Type: Scenario-Based

    • Expected Answer: Restore from a backup or use pg_restore with selective recovery.

    • Example:

      pg_restore --table=employees backup.dump;

64. What is the role of pg_stat_activity?

    • Type: Conceptual

    • Expected Answer: Monitors active sessions and queries.

    • Example:

      SELECT * FROM pg_stat_activity WHERE state = 'active';

65. How do you optimize PostgreSQL for high-concurrency workloads?

    • Type: Scenario-Based

    • Expected Answer: Tune work_mem, max_connections, and use connection pooling.

    • Example:

      ALTER SYSTEM SET work_mem = '16MB';

66. What is the difference between physical and logical replication?

    • Type: Conceptual

    • Expected Answer: Physical replication copies data files, while logical replication copies SQL changes.

67. How do you implement PostgreSQL hot standby?

    • Type: Practical

    • Expected Answer: Configure streaming replication with read-only replicas.

    • Example:

      ALTER SYSTEM SET hot_standby = ON;

68. What is the role of pg_stat_statements in performance tuning?

    • Type: Practical

    • Expected Answer: Tracks query performance metrics for optimization.

    • Example:

      SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC;

69. How do you handle a replication lag in PostgreSQL?

    • Type: Scenario-Based

    • Expected Answer: Optimize queries, increase WAL sender resources, or use parallel apply.

    • Example:

      ALTER SYSTEM SET max_parallel_replay = 4;

70. What is the role of the pg_cron extension?

    • Type: Conceptual

    • Expected Answer: pg_cron schedules recurring tasks.

    • Example:

      SELECT cron.schedule('daily_cleanup', '0 0 * * *', $$DELETE FROM logs WHERE log_date < NOW() - INTERVAL '30 days'$$);

PostgreSQL Developer (Expert)

71. How do you implement bulk insert in PostgreSQL?

    • Type: Coding Challenge

    • Expected Answer:

      COPY employees FROM '/data/employees.csv' DELIMITER ',';

72. What is the difference between a scalar and table function?

    • Type: Conceptual

    • Expected Answer: Scalar functions return a single value, while table functions return result sets.

73. How do you implement parallel query execution in PostgreSQL?

    • Type: Practical

    • Expected Answer: Enable parallel query with max_parallel_workers.

    • Example:

      SET max_parallel_workers_per_gather = 4;

74. What is the role of the LATERAL join?

    • Type: Conceptual

    • Expected Answer: LATERAL allows subqueries to reference outer query columns.

    • Example:

      SELECT e.employee_id, d.dept_name
      FROM employees e
      CROSS JOIN LATERAL (SELECT dept_name FROM departments WHERE dept_id = e.dept_id) d;

75. How do you handle JSONB data in PostgreSQL?

    • Type: Practical

    • Expected Answer: Use JSONB operators like -> and ->>.

    • Example:

      SELECT data->'name' FROM json_table;

76. What is the difference between ROW_NUMBER and RANK?

    • Type: Conceptual

    • Expected Answer: ROW_NUMBER assigns unique numbers, while RANK assigns same rank for ties.

77. How do you implement a recursive CTE in PostgreSQL?

    • Type: Coding Challenge

    • Expected Answer:

      WITH RECURSIVE org_chart AS (
        SELECT employee_id, manager_id, first_name
        FROM employees WHERE manager_id IS NULL
        UNION ALL
        SELECT e.employee_id, e.manager_id, e.first_name
        FROM employees e
        INNER JOIN org_chart o ON e.manager_id = o.employee_id
      )
      SELECT * FROM org_chart;

78. What is the role of DO blocks in PostgreSQL?

    • Type: Practical

    • Expected Answer: DO executes anonymous PL/pgSQL code.

    • Example:

      DO $$
      BEGIN
        RAISE NOTICE 'Hello, PostgreSQL!';
      END;
      $$;

79. How do you secure PL/pgSQL code?

    • Type: Practical

    • Expected Answer: Use SECURITY DEFINER or role-based permissions.

    • Example:

      CREATE FUNCTION my_func() RETURNS VOID SECURITY DEFINER AS $$
      BEGIN
        SELECT * FROM employees;
      END;
      $$ LANGUAGE plpgsql;

80. What is the difference between a correlated and non-correlated subquery?

    • Type: Conceptual

    • Expected Answer: Correlated subqueries reference outer queries, executed per row, while non-correlated are independent.

PostgreSQL Designer (Expert)

81. How do you design a database for high availability?

    • Type: Scenario-Based

    • Expected Answer: Use streaming replication and failover mechanisms.

82. What is the role of table inheritance in PostgreSQL?

    • Type: Conceptual

    • Expected Answer: Table inheritance allows child tables to inherit parent table structure.

    • Example:

      CREATE TABLE employees () INHERITS (people);

83. How do you implement data archiving in PostgreSQL?

    • Type: Practical

    • Expected Answer: Use partitioning and automated jobs.

    • Example:

      ALTER TABLE logs DETACH PARTITION logs_old;

84. What is the difference between a B-tree and GiST index?

    • Type: Conceptual

    • Expected Answer: B-tree indexes support equality and range queries, while GiST supports complex data types like geospatial.

85. How do you design a database for real-time analytics?

    • Type: Scenario-Based

    • Expected Answer: Use materialized views and optimized indexes.

    • Example:

      REFRESH MATERIALIZED VIEW mv_sales;

86. What is the role of PostgreSQL’s logical decoding?

    • Type: Conceptual

    • Expected Answer: Logical decoding extracts changes from WAL for replication or CDC.

87. How do you model a temporal database?

    • Type: Practical

    • Expected Answer: Use range types or timestamp columns.

    • Example:

      CREATE TABLE employee_history (
        emp_id INT,
        valid_period TSRANGE
      );

88. What is the difference between OLTP and OLAP in database design?

    • Type: Conceptual

    • Expected Answer: OLTP handles transactional data, while OLAP supports analytical queries.

89. How do you handle schema evolution in a production database?

    • Type: Scenario-Based

    • Expected Answer: Use online DDL and backward-compatible changes.

    • Example:

      ALTER TABLE employees ADD COLUMN new_col TEXT NULL;

90. What is the role of PostgreSQL’s FDW (Foreign Data Wrapper)?

    • Type: Conceptual

    • Expected Answer: FDW integrates external data sources into PostgreSQL.

    • Example:

      CREATE FOREIGN TABLE remote_employees () SERVER remote_server;

91. How do you design a database for GDPR compliance?

    • Type: Scenario-Based

    • Expected Answer: Implement data masking and encryption.

    • Example:

      CREATE FUNCTION mask_data() RETURNS TRIGGER AS $$
      BEGIN
        NEW.email = '***';
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;

92. What is the role of PostgreSQL’s pg_stat_replication?

    • Type: Conceptual

    • Expected Answer: Monitors replication status and lag.

93. How do you design a database for audit logging?

    • Type: Practical

    • Expected Answer: Use triggers or pgAudit extension.

    • Example:

      CREATE EXTENSION pgaudit;

94. What is the difference between a clustered and non-clustered index?

    • Type: Conceptual

    • Expected Answer: PostgreSQL does not use clustered indexes; B-tree indexes are non-clustered.

95. How do you optimize a star schema for data warehousing?

    • Type: Scenario-Based

    • Expected Answer: Use denormalized tables and BRIN indexes.

96. What is the role of PostgreSQL’s pg_dump?

    • Type: Conceptual

    • Expected Answer: pg_dump creates logical backups of databases.

97. How do you design a database for global applications?

    • Type: Scenario-Based

    • Expected Answer: Use replication and locale-aware collations.

98. What is the difference between a unique and non-unique index?

    • Type: Conceptual

    • Expected Answer: Unique indexes enforce uniqueness, non-unique do not.

99. How do you implement row-level security in PostgreSQL?

    • Type: Practical

    • Expected Answer: Use RLS policies.

    • Example:

      ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
      CREATE POLICY emp_policy ON employees USING (department_id = current_user::INT);

100. What is the role of PostgreSQL’s pg_trgm extension?

     • Type: Conceptual

     • Expected Answer: pg_trgm supports trigram-based text search.

     • Example:

       CREATE INDEX trgm_idx ON employees USING GIN (first_name gin_trgm_ops);

101. How do you design a database for high-concurrency applications?

     • Type: Scenario-Based

     • Expected Answer: Use MVCC and connection pooling.

102. What is the role of PostgreSQL’s pg_stat_bgwriter?

     • Type: Conceptual

     • Expected Answer: Monitors background writer activity for performance tuning.

103. How do you handle data migration across PostgreSQL versions?

     • Type: Scenario-Based

     • Expected Answer: Use pg_dump and pg_restore.

     • Example:

       pg_dump -U postgres mydatabase > backup.sql;

104. What is the role of PostgreSQL’s pg_stat_replication_slots?

     • Type: Conceptual

     • Expected Answer: Monitors replication slot activity.

105. How do you design a database for IoT data?

     • Type: Scenario-Based

     • Expected Answer: Use time-based partitioning and JSONB.

     • Example:

       CREATE TABLE sensor_data (
         sensor_id INT,
         timestamp TIMESTAMPTZ,
         data JSONB
       ) PARTITION BY RANGE (timestamp);

PostgreSQL Developer (Expert, Continued)

106. How do you implement a PostgreSQL cron job?

     • Type: Coding Challenge

     • Expected Answer:

       SELECT cron.schedule('daily_cleanup', '0 0 * * *', $$DELETE FROM logs WHERE log_date < NOW() - INTERVAL '30 days'$$);

107. What is the role of JSONB in PostgreSQL?

     • Type: Conceptual

     • Expected Answer: JSONB stores binary JSON for efficient querying.

     • Example:

       SELECT data->>'name' FROM json_table WHERE data @> '{"city": "New York"}';

108. How do you handle large datasets in PostgreSQL?

     • Type: Scenario-Based

     • Expected Answer: Use COPY, partitioning, and indexing.

109. What is the difference between SERIAL and UUID?

     • Type: Conceptual

     • Expected Answer: SERIAL generates sequential integers, while UUID creates unique strings.

110. How do you implement a PostgreSQL event trigger?

     • Type: Coding Challenge

     • Expected Answer:

       CREATE EVENT TRIGGER log_ddl ON ddl_command_start
       EXECUTE FUNCTION log_ddl_func();

111. What is the role of ANALYZE in PostgreSQL?

     • Type: Practical

     • Expected Answer: Updates planner statistics for query optimization.

     • Example:

       ANALYZE employees;

112. How do you implement error logging in PostgreSQL?

     • Type: Practical

     • Expected Answer: Use PL/pgSQL and log tables.

     • Example:

       CREATE FUNCTION log_error(err_msg TEXT) RETURNS VOID AS $$
       BEGIN
         INSERT INTO error_log (message, log_time) VALUES (err_msg, NOW());
       END;
       $$ LANGUAGE plpgsql;

113. What is the difference between BIGSERIAL and SERIAL?

     • Type: Conceptual

     • Expected Answer: BIGSERIAL uses BIGINT, while SERIAL uses INT.

114. How do you implement a PostgreSQL job chain?

     • Type: Practical

     • Expected Answer: Use pg_cron with dependent tasks.

     • Example:

       SELECT cron.schedule('step2', '0 1 * * *', $$CALL proc2()$$);

115. What is the role of COPY in PostgreSQL?

     • Type: Conceptual

     • Expected Answer: COPY imports/exports data efficiently.

     • Example:

       COPY employees TO '/data/employees.csv' DELIMITER ',';

PostgreSQL Designer (Expert, Continued)

116. How do you design a database for microservices?

     • Type: Scenario-Based

     • Expected Answer: Use separate schemas or databases per microservice.

117. What is the role of PostgreSQL’s BRIN index?

     • Type: Conceptual

     • Expected Answer: BRIN indexes are efficient for large, sequentially ordered data.

118. How do you implement a data warehouse in PostgreSQL?

     • Type: Scenario-Based

     • Expected Answer: Use star schemas and materialized views.

119. What is the difference between a snowflake and star schema?

     • Type: Conceptual

     • Expected Answer: Star schemas are denormalized, while snowflake schemas normalize dimension tables.

120. How do you design a database for time-series data?

     • Type: Practical

     • Expected Answer:

       CREATE TABLE time_series (
         sensor_id INT,
         timestamp TIMESTAMPTZ,
         value FLOAT
       ) PARTITION BY RANGE (timestamp);

121. What is the role of PostgreSQL’s pglogical extension?

     • Type: Conceptual

     • Expected Answer: pglogical enables advanced logical replication.

122. How do you handle cross-database queries in PostgreSQL?

     • Type: Practical

     • Expected Answer: Use dblink or FDW.

     • Example:

       SELECT * FROM dblink('dbname=otherdb', 'SELECT * FROM employees') AS t(id INT, name TEXT);

123. What is the role of PostgreSQL’s pg_bouncer?

     • Type: Conceptual

     • Expected Answer: pg_bouncer manages connection pooling.

124. How do you design a database for audit compliance?

     • Type: Scenario-Based

     • Expected Answer: Use pgAudit and temporal tables.

     • Example:

       CREATE EXTENSION pgaudit;

125. What is the difference between a GiST and GIN index?

     • Type: Conceptual

     • Expected Answer: GiST supports complex data types, while GIN is optimized for full-text search and arrays.

126. How do you implement data encryption in PostgreSQL?

     • Type: Practical

     • Expected Answer: Use pgcrypto or tablespace encryption.

     • Example:

       CREATE EXTENSION pgcrypto;
       SELECT pgp_sym_encrypt('data', 'key');

127. What is the role of PostgreSQL’s TimescaleDB extension?

     • Type: Conceptual

     • Expected Answer: TimescaleDB optimizes time-series data storage and querying.

128. How do you design a database for high-frequency trading?

     • Type: Scenario-Based

     • Expected Answer: Use TimescaleDB and optimized indexing.

129. What is the difference between ACID and BASE properties?

     • Type: Conceptual

     • Expected Answer: ACID ensures reliability, while BASE prioritizes availability.

130. How do you implement a data retention policy?

     • Type: Practical

     • Expected Answer: Use partitioning and pg_cron.

     • Example:

       SELECT cron.schedule('drop_old', '0 0 1 * *', $$ALTER TABLE logs DETACH PARTITION logs_old$$);

131. What is the role of PostgreSQL’s pg_stat_statements?

     • Type: Conceptual

     • Expected Answer: Tracks query performance metrics.

132. How do you design a database for global applications?

     • Type: Scenario-Based

     • Expected Answer: Use replication and locale-aware collations.

133. What is the role of PostgreSQL’s PostGIS extension?

     • Type: Conceptual

     • Expected Answer: PostGIS supports geospatial data analysis.

     • Example:

       SELECT ST_Distance(geom1, geom2) FROM locations;

134. How do you handle schema versioning in PostgreSQL?

     • Type: Practical

     • Expected Answer: Use migrations and versioning tables.

     • Example:

       CREATE TABLE schema_version (
         version INT,
         applied_at TIMESTAMPTZ
       );

135. What is the difference between a heap and index-organized table?

     • Type: Conceptual

     • Expected Answer: PostgreSQL uses heap tables; index-organized tables are Oracle-specific.

136. How do you implement a change data capture (CDC) system?

     • Type: Practical

     • Expected Answer: Use logical decoding and tools like Debezium.

     • Example:

       ALTER TABLE employees REPLICA IDENTITY FULL;

137. What is the role of PostgreSQL’s pg_stat_archiver?

     • Type: Conceptual

     • Expected Answer: Monitors WAL archiving status.

138. How do you design a database for machine learning workloads?

     • Type: Scenario-Based

     • Expected Answer: Use JSONB and optimized indexing.

139. What is the difference between a unique and non-unique index?

     • Type: Conceptual

     • Expected Answer: Unique indexes enforce uniqueness, non-unique do not.

140. How do you implement a failover mechanism in PostgreSQL?

     • Type: Practical

     • Expected Answer: Use streaming replication and tools like Patroni.

     • Example:

       SELECT pg_promote();

141. What is the role of PostgreSQL’s EXPLAIN ANALYZE?

     • Type: Conceptual

     • Expected Answer: Provides detailed query execution statistics.

142. How do you design a database for event-driven architectures?

     • Type: Scenario-Based

     • Expected Answer: Use NOTIFY and triggers.

     • Example:

       NOTIFY my_channel, 'event_data';

143. What is the difference between a full and incremental backup?

     • Type: Conceptual

     • Expected Answer: Full backups copy all data, while incremental copy changes.

144. How do you implement a data masking solution?

     • Type: Practical

     • Expected Answer: Use pgcrypto or views for masking.

     • Example:

       CREATE VIEW masked_employees AS
       SELECT employee_id, '***' AS email FROM employees;

145. What is the role of PostgreSQL’s Window Functions?

     • Type: Conceptual

     • Expected Answer: Window Functions perform calculations across rows.

     • Example:

       SELECT employee_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary) AS rank
       FROM employees;

146. How do you design a database for reporting?

     • Type: Scenario-Based

     • Expected Answer: Use materialized views and star schemas.

147. What is the role of PostgreSQL’s pg_stat_user_tables?

     • Type: Conceptual

     • Expected Answer: Monitors table-level statistics.

148. How do you handle large-scale data imports?

     • Type: Practical

     • Expected Answer: Use COPY or pg_bulkload.

     • Example:

       COPY employees FROM '/data/employees.csv' DELIMITER ',';

149. What is the difference between a view and a materialized view?

     • Type: Conceptual

     • Expected Answer: Views are virtual, while materialized views store data physically.

150. How do you implement a distributed transaction in PostgreSQL?

     • Type: Practical

     • Expected Answer: Use two-phase commit (2PC).

     • Example:

       PREPARE TRANSACTION 'tx1';
       COMMIT PREPARED 'tx1';

151. What is the role of PostgreSQL’s pg_stat_progress_vacuum?

     • Type: Conceptual

     • Expected Answer: Monitors VACUUM operation progress.

152. How do you design a database for high-performance analytics?

     • Type: Scenario-Based

     • Expected Answer: Use TimescaleDB and parallel queries.

153. What is the difference between a physical and logical data model?

     • Type: Conceptual

     • Expected Answer: Physical models define storage, logical models focus on business rules.

154. How do you implement a backup strategy for a 24/7 database?

     • Type: Scenario-Based

     • Expected Answer: Use pg_basebackup and PITR.

     • Example:

       pg_basebackup -D /backup --wal-method=stream;

155. What is the role of PostgreSQL’s pg_stat_statements extension?

     • Type: Conceptual

     • Expected Answer: Tracks query performance for optimization.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone