PostgreSQL Deep Dive

Question 1

What are JSON and JSONB data types in PostgreSQL?

Answer 1

JSON stores data as text, while JSONB stores it in a binary format that allows indexing and faster querying.

Question 2

What is the difference between SERIAL, BIGSERIAL, and IDENTITY columns?

Answer 2

SERIAL and BIGSERIAL auto-increment via sequences (legacy), while IDENTITY (introduced in SQL standard) offers better compliance and control.

Question 3

What does the RETURNING clause do in INSERT/UPDATE statements?

Answer 3

It returns specified columns (like inserted IDs) immediately after the operation, reducing the need for a follow-up SELECT.

Question 4

What are arrays in PostgreSQL?

Answer 4

Arrays allow you to store multiple values of the same type in a single column.

Question 5

What are enums in PostgreSQL?

Answer 5

Enums restrict a column to a predefined set of values, improving data consistency and readability.

Question 6

What is table inheritance in PostgreSQL?

Answer 6

It allows a table to inherit columns and constraints from a parent table, useful for modeling polymorphic data.

Question 7

What is partitioning in PostgreSQL?

Answer 7

Partitioning splits large tables into smaller pieces for better performance and maintainability.

Question 8

What is PostGIS extension?

Answer 8

It adds support for geographic objects, enabling spatial queries like distance, overlap, and geometry operations.

Question 9

What does the pg_trgm extension do?

Answer 9

It enables similarity-based text searching and indexing, useful for fuzzy matching and full-text search.

Question 10

What is the uuid-ossp extension?

Answer 10

It provides functions to generate UUIDs, commonly used for unique identifiers in distributed systems.

Question 11

What is role-based access control in PostgreSQL?

Answer 11

It allows granting permissions to users and groups (roles) to manage data access securely and flexibly.

Question 12

What is vacuuming in PostgreSQL?

Answer 12

VACUUM reclaims storage by cleaning up dead tuples created by updates/deletes.

Question 13

What does ANALYZE do in PostgreSQL?

Answer 13

It updates statistics used by the query planner to optimize query execution.

Question 14

What is autovacuum in PostgreSQL?

Answer 14

It’s a background process that automatically performs VACUUM and ANALYZE to maintain performance.

Question 15

What are advantages of JSONB over JSON?

Answer 15

JSONB supports indexing and is faster to query but requires more storage space and CPU for insertions.

Question 16

What are best practices for using enums in PostgreSQL?

Answer 16

Use for static value sets; avoid if the list changes frequently since modifying enums requires DDL operations.

Question 17

What is a good use case for RETURNING clause?

Answer 17

Retrieving the auto-generated ID or timestamp after inserting a row, in a single atomic operation.

Question 18

What is a trade-off of table inheritance?

Answer 18

Inheritance doesn’t always play well with constraints or indexes, and parent queries may not hit child tables unless specified.

Question 19

What is the benefit of partitioning?

Answer 19

Improves performance for large datasets by reducing scan size and enabling parallel query execution.

Question 20

What are monitoring tools for PostgreSQL performance?

Answer 20

Use pg_stat_activity, pg_stat_user_tables, auto_explain, and third-party tools like pgBadger or pgHero.

Question 21

What is a real-world tradeoff of using UUIDs over SERIAL?

Answer 21

UUIDs offer global uniqueness and better security but are larger and slower to index than integer-based SERIALs.

Question 22

What is a potential gotcha with JSONB?

Answer 22

It’s not human-readable like JSON, and frequent updates can lead to bloat if not vacuumed properly.

Question 23

What’s a gotcha when using autovacuum?

Answer 23

It might not run frequently enough for high-update tables, causing bloat and degraded performance.

Question 24

What’s a best practice when using extensions like pg_trgm?

Answer 24

Only enable when needed due to extra storage and potential performance cost; monitor usage carefully.

Question 25

What’s a common interview question around VACUUM?

Answer 25

Explain the difference between VACUUM, ANALYZE, and autovacuum, and how they impact performance.

Question 26

What’s an architectural implication of using JSONB?

Answer 26

It enables schema-less designs in a relational DB but can blur the line between relational and NoSQL approaches.

Question 27

What’s a use case for PostGIS?

Answer 27

Applications that require spatial data operations, like geolocation, route optimization, or proximity-based services.

Question 28

What’s an advantage of RETURNING clause in system design?

Answer 28

Reduces network round trips and ensures atomicity between insert/update and read.

Question 29

What’s the impact of using arrays in PostgreSQL?

Answer 29

They allow compact data modeling but can complicate queries and indexing.

Question 30

What’s the downside of overusing extensions?

Answer 30

Increased maintenance complexity and risk of incompatibilities during upgrades or migrations.

Question 31

What’s a best practice for roles in PostgreSQL?

Answer 31

Use roles to abstract permission sets and avoid directly assigning privileges to individual users.