Question 1

What is PostgreSQL?

Accepted Answer

PostgreSQL is a powerful, open-source object-relational database system (ORDBMS) with over 35 years of active development. It is known for its reliability, feature robustness, and performance. It supports both SQL and JSON querying and is highly extensible.

Question 2

What are the key features of PostgreSQL?

Accepted Answer

Key features include:
1. **ACID Compliance**: Ensures reliable transactions.
2. **Conjoined Scalability**: Support for complex queries and large data volumes.
3. **Extensibility**: Custom types, functions, and operators.
4. **Advanced Data Types**: JSONB, HSTORE, Arrays, Ranges.
5. **MVCC**: Multi-Version Concurrency Control for high performance.

Question 3

What is the difference between PostgreSQL and MySQL?

Accepted Answer

- **MySQL**: Traditional RDBMS, focused on speed and simplicity, traditionally better for read-heavy web apps.
- **PostgreSQL**: ORDBMS, focused on compliance and advanced features, better for complex queries, high concurrency, and technical data types like JSONB.

Question 4

What is a Schema in PostgreSQL?

Accepted Answer

In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, and functions. A database can contain one or more schemas.

Question 5

How do you create a table in PostgreSQL?

Accepted Answer

You use the `CREATE TABLE` command.
Code Example:
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  salary NUMERIC(10, 2)
);

Question 6

What is the SERIAL data type?

Accepted Answer

The `SERIAL` type is a shorthand for creating an integer column that automatically increments. It creates a sequence behind the scenes. `SERIAL` is 4 bytes, while `BIGSERIAL` is 8 bytes.

Question 7

How do you insert a row into a table?

Accepted Answer

You use the `INSERT INTO` command.
Code Example:
INSERT INTO employees (name, email, salary) VALUES ('John Doe', 'john@example.com', 50000);

Question 8

What is the purpose of the 'psql' utility?

Accepted Answer

`psql` is the interactive terminal (command-line interface) for working with PostgreSQL. It allows you to run queries and manage the database directly.

Question 9

How do you list all tables in 'psql'?

Accepted Answer

You use the meta-command `\dt` (display tables).
Code Example:
\dt

Question 10

How do you see the structure of a table in 'psql'?

Accepted Answer

You use the meta-command `\d tableName` (describe).
Code Example:
\d employees

Question 11

What is a Primary Key?

Accepted Answer

A primary key is a field or combination of fields that uniquely identifies each row in a table. It cannot be NULL and must be unique.

Question 12

What is a Foreign Key?

Accepted Answer

A foreign key is a column or group of columns used to establish a link between two tables. It ensures referential integrity by pointing to the primary key of another table.

Question 13

Explain the 'LIMIT' and 'OFFSET' clauses.

Accepted Answer

- **LIMIT**: Restricts the maximum number of rows returned.
- **OFFSET**: Skips the first N rows before starting to return rows.
Code Example:
SELECT * FROM employees LIMIT 10 OFFSET 20;

Question 14

How do you update data in a table?

Accepted Answer

You use the `UPDATE` command.
Code Example:
UPDATE employees SET salary = salary * 1.1 WHERE id = 1;

Question 15

How do you delete data from a table?

Accepted Answer

You use the `DELETE` command.
Code Example:
DELETE FROM employees WHERE id = 5;

Question 16

What is the 'TRUNCATE' command?

Accepted Answer

`TRUNCATE` quickly removes all rows from a table. It is generally faster than `DELETE` because it doesn't log individual row deletions and ignores most triggers.

Question 17

What is a View in PostgreSQL?

Accepted Answer

A view is a virtual table based on the result-set of an SQL query. It does not store data itself but provides a way to simplify complex queries.

Question 18

What is an Index?

Accepted Answer

An index is a pointer that helps speed up data retrieval. PostgreSQL supports several index types like B-tree (default), Hash, GIN, and GIST.

Question 19

What is the 'WHERE' clause used for?

Accepted Answer

The `WHERE` clause is used to filter records that fulfill a specified condition.

Question 20

What is the 'GROUP BY' clause?

Accepted Answer

The `GROUP BY` clause groups rows that have the same values into summary rows, typically used with aggregate functions like `COUNT()`, `MAX()`, `MIN()`, `SUM()`, `AVG()`.

Question 21

Explain 'INNER JOIN' vs 'LEFT JOIN'.

Accepted Answer

- **INNER JOIN**: Returns records that have matching values in both tables.
- **LEFT JOIN**: Returns all records from the left table, and the matched records from the right table. If no match, NULLs are returned for the right side.

Question 22

What is the 'COALESCE' function?

Accepted Answer

`COALESCE(val1, val2, ...)` returns the first non-null value from a list of expressions. It is commonly used to provide default values for NULL columns.

Question 23

What is the purpose of 'VACUUM'?

Accepted Answer

`VACUUM` reclaims storage occupied by dead tuples (deleted or updated rows) to keep the database efficient. PostgreSQL has an 'Autovacuum' daemon that does this automatically.

Question 24

What is 'COPY' command for?

Accepted Answer

The `COPY` command is used to move data between PostgreSQL tables and standard file-system files. It is much faster than `INSERT` for large data sets.
Code Example:
COPY employees FROM '/tmp/data.csv' WITH (FORMAT csv);

Question 25

What is the default port for PostgreSQL?

Accepted Answer

The default port for PostgreSQL is **5432**.

Question 26

What is a Window Function in PostgreSQL?

Accepted Answer

A window function performs a calculation across a set of table rows that are somehow related to the current row. Common functions include `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, and `SUM() OVER ()`.
Code Example:
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;

Question 27

What is a CTE (Common Table Expression)?

Accepted Answer

A CTE is a temporary result set that you can reference within another SQL statement. It is defined using the `WITH` clause and makes complex queries more readable.
Code Example:
WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_earners WHERE department = 'Sales';

Question 28

Explain the 'EXPLAIN' and 'EXPLAIN ANALYZE' commands.

Accepted Answer

- **EXPLAIN**: Shows the execution plan that the PostgreSQL planner generates for a query.
- **EXPLAIN ANALYZE**: Actually executes the query and shows the real timing and row counts, providing much more detail.

Question 29

What is MVCC (Multi-Version Concurrency Control)?

Accepted Answer

MVCC allows multiple users to access the same data at the same time without locking each other out. When data is modified, a new version is created rather than overwriting the old one, ensuring that readers see a consistent snapshot.

Question 30

What are the differences between JSON and JSONB?

Accepted Answer

- **JSON**: Stores an exact copy of the input text. Parsing is done on every query.
- **JSONB**: Stores data in a decomposed binary format. It is slower to insert but much faster to query and supports indexing (GIN/GIST).

PostgreSQL Database Interview Master

Difficulty Filters

Tools & Options

What is PostgreSQL?

What are the key features of PostgreSQL?

What is the difference between PostgreSQL and MySQL?

What is a Schema in PostgreSQL?

How do you create a table in PostgreSQL?

What is the SERIAL data type?

How do you insert a row into a table?

What is the purpose of the 'psql' utility?

How do you list all tables in 'psql'?

How do you see the structure of a table in 'psql'?

What is a Primary Key?

What is a Foreign Key?

Explain the 'LIMIT' and 'OFFSET' clauses.

How do you update data in a table?

How do you delete data from a table?

What is the 'TRUNCATE' command?

What is a View in PostgreSQL?

What is an Index?

What is the 'WHERE' clause used for?

What is the 'GROUP BY' clause?

Explain 'INNER JOIN' vs 'LEFT JOIN'.

What is the 'COALESCE' function?

What is the purpose of 'VACUUM'?

What is 'COPY' command for?

What is the default port for PostgreSQL?

What is a Window Function in PostgreSQL?

What is a CTE (Common Table Expression)?

Explain the 'EXPLAIN' and 'EXPLAIN ANALYZE' commands.

What is MVCC (Multi-Version Concurrency Control)?

What are the differences between JSON and JSONB?

What is a GIN Index?

What is a Partial Index?

How do you perform a recursive query in PostgreSQL?

What is the 'UPSERT' (INSERT ON CONFLICT) operation?

What are the different isolation levels in PostgreSQL?

What is an extension in PostgreSQL?

Explain the 'RETURNING' clause in PostgreSQL.

What is a Materialized View?

How do you generate a UUID in PostgreSQL?

What is 'ILIKE'?

Explain the 'OVER' clause.

What are Savepoints in a transaction?

What is a Sequence in PostgreSQL?

What is the difference between 'VARCHAR' and 'TEXT'?

What is 'hstore'?

How do you handle character encoding in PostgreSQL?

What is the 'DISTINCT ON' clause?

What is a TRIGGER in PostgreSQL?

Explain 'CAST' and the '::' shorthand.

What is the 'pg_stat_activity' view?

Explain PostgreSQL Partitioning.

What is BRIN (Block Range Index)?

Explain GIST (Generalized Search Tree) vs GIN.

What is Table Inheritance?

What are Logical and Physical Replication?

Explain 'Vacuum Full' vs 'Vacuum'.

What are Foreign Data Wrappers (FDW)?

Explain the 'WAL' (Write-Ahead Logging).

What is an Advisory Lock?

What is JIT (Just-In-Time) compilation in Postgres?

How do you handle Deadlocks in PostgreSQL?

What is 'Streaming Replication'?

Explain 'Work Mem' and 'Maintenance Work Mem'.

What is a Bloom Index?

Explain 'HOT' (Heap Only Tuple) updates.

What is 'pg_dump' vs 'pg_dumpall'?

How to find slow queries in PostgreSQL?

What is 'Postgis'?

Explain 'Declarative Partitioning'.

What is 'Index Scan' vs 'Bitmap Index Scan' vs 'Sequential Scan'?

What is 'Checkpointer' process?

What is 'Schema Search Path'?

How to optimize queries with JSONB data?

Explain 'LISTEN' and 'NOTIFY'.

What is 'Parallel Query' execution?

Scenario: How to find the second highest salary without using LIMIT?

Scenario: How to delete duplicate rows from a table while keeping the one with the lowest ID?