PostgreSQL Interview Master

PostgreSQL is a powerful, open-source object-relational database system (ORDBMS) with over 35 years of active development. It is known for its reliability, feat...

Key features include: 1. **ACID Compliance**: Ensures reliable transactions. 2. **Conjoined Scalability**: Support for complex queries and large data volumes. 3...

- **MySQL**: Traditional RDBMS, focused on speed and simplicity, traditionally better for read-heavy web apps. - **PostgreSQL**: ORDBMS, focused on compliance a...

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 sc...

You use the `CREATE TABLE` command.

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  salary NUMERIC(10, 2)
);

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, whi...

You use the `INSERT INTO` command.

INSERT INTO employees (name, email, salary) VALUES ('John Doe', 'john@example.com', 50000);

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

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

\dt

You use the meta-command `\d tableName` (describe).

\d employees

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.

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 an...

- **LIMIT**: Restricts the maximum number of rows returned. - **OFFSET**: Skips the first N rows before starting to return rows.

SELECT * FROM employees LIMIT 10 OFFSET 20;

You use the `UPDATE` command.

UPDATE employees SET salary = salary * 1.1 WHERE id = 1;

You use the `DELETE` command.

DELETE FROM employees WHERE id = 5;

`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...

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.

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

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

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

- **INNER JOIN**: Returns records that have matching values in both tables. - **LEFT JOIN**: Returns all records from the left table, and the matched records fr...

`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.

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

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.

COPY employees FROM '/tmp/data.csv' WITH (FORMAT csv);

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

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(...

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;

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 readab...

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_earners WHERE department = 'Sales';

- **EXPLAIN**: Shows the execution plan that the PostgreSQL planner generates for a query. - **EXPLAIN ANALYZE**: Actually executes the query and shows the real...

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 ...

- **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 inse...

GIN stands for **Generalized Inverted Index**. It is designed for data that contains multiple components, such as arrays or JSONB documents. It is excellent for...

A partial index is built over a subset of a table, defined by a conditional expression (the `WHERE` clause of the index). This saves space and improves performa...

CREATE INDEX active_orders_idx ON orders (id) WHERE status = 'active';

You use the `WITH RECURSIVE` clause. This is commonly used for hierarchical data like organizational charts or bill of materials.

WITH RECURSIVE sub_org AS (
  SELECT * FROM org WHERE name = 'Leadership'
  UNION
  SELECT org.* FROM org JOIN sub_org ON org.parent_id = sub_org.id
)
SELECT * FROM sub_org;

Upsert allows you to either insert a new row or update an existing one if a conflict (like a unique constraint violation) occurs.

INSERT INTO users (email, last_login) VALUES ('test@test.com', NOW())
ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login;

PostgreSQL supports four isolation levels: 1. **Read Uncommitted** (behaves like Read Committed) 2. **Read Committed** (default) 3. **Repeatable Read** 4. **Ser...

Extensions allow you to add new features, data types, and functions to your database. Common extensions include `postgis` (spatial data), `pg_stat_statements` (...

The `RETURNING` clause allows you to return values from rows that were impacted by an `INSERT`, `UPDATE`, or `DELETE` operation.

INSERT INTO users (name) VALUES ('Bob') RETURNING id;

A materialized view stores the result-set of a query physically on disk. It provides much faster read performance for complex queries but must be manually or sc...

REFRESH MATERIALIZED VIEW my_view;

You can use the `gen_random_uuid()` function (built-in for Postgres 13+) or install the `uuid-ossp` extension and use `uuid_generate_v4()`.

SELECT gen_random_uuid();

`ILIKE` is a PostgreSQL-specific operator for case-insensitive pattern matching. `LIKE` is case-sensitive.

The `OVER` clause defines the 'window' over which a window function operates. It can include `PARTITION BY` and `ORDER BY` sub-clauses.

Savepoints allow you to roll back parts of a transaction without canceling the entire transaction. They are useful for error handling within large transactions.

BEGIN;
INSERT INTO t1 VALUES (1);
SAVEPOINT my_save;
INSERT INTO t1 VALUES (2); -- Oops!
ROLLBACK TO my_save;
COMMIT;

A sequence is a special kind of table that generates a sequence of numbers. They are commonly used to generate primary key values.

SELECT nextval('my_sequence');

In PostgreSQL, there is no performance difference between `VARCHAR(N)` and `TEXT`. Both are stored in the same way. The only difference is that `VARCHAR(N)` enf...

`hstore` is an extension that provides a data type for storing sets of (key, value) pairs within a single value. It is useful for semi-structured data, though `...

PostgreSQL uses **UTF-8** by default. You can set the encoding at the database level when creating it.

CREATE DATABASE mydb WITH ENCODING 'UTF8';

`DISTINCT ON (field)` is a PostgreSQL extension that allows you to keep only the first row for each distinct set of values in the target fields. It is usually c...

SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, order_date DESC;

A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed (e.g., `BEFO...

Both are used for type conversion. `CAST(val AS type)` is standard SQL, while `val::type` is a PostgreSQL shorthand.

SELECT '100'::INTEGER;

`pg_stat_activity` is a system view that shows information about currently active database processes, including the user, query, and start time.

Partitioning is the split of one large logical table into several smaller physical pieces. PostgreSQL support Declarative Partitioning by Range, List, or Hash. ...

BRIN indexes store the minimum and maximum values for a block of pages. They are much smaller than B-tree indexes and are ideal for very large tables where data...

- **GIST**: Good for spatial data, full text search, and nearest neighbor searches. Slower to query than GIN but faster to update. - **GIN**: Best for multi-val...

Table inheritance allows a table to inherit the columns and constraints of a parent table. While powerful, it has been largely superseded by Declarative Partiti...

- **Physical Replication**: Copies the entire data directory byte-for-byte. Used for high availability and read-only standbys. - **Logical Replication**: Copies...

- **VACUUM**: Marks old rows as reusable but doesn't return space to the OS (except at the end of the file). - **VACUUM FULL**: Compacts the table and returns a...

FDW allows PostgreSQL to connect to and query external data sources (like other Postgres servers, MySQL, MongoDB, or even Redis) as if they were local tables.

CREATE EXTENSION postgres_fdw;

WAL ensures that no data is lost if the system crashes. Changes are written to a log file on disk before they are applied to the data files, ensuring that the d...

Advisory locks are application-defined locks that have no inherent meaning to the database. They provide a way for applications to coordinate actions using the ...

Since version 11, PostgreSQL can use LLVM to compile parts of complex queries into machine code at runtime. This can significantly speed up CPU-bound queries th...

PostgreSQL automatically detects deadlocks and aborts one of the transactions involved to resolve the conflict. Developers should handle these retries in their ...

Streaming replication allows a standby server to stay up-to-date with a primary server by continuously receiving WAL logs over a network connection, providing a...

- **work_mem**: The amount of memory used for internal sort operations and hash tables. High values speed up complex queries but use more RAM. - **maintenance_w...

A Bloom index is based on Bloom filters. It is useful for tables where you have many columns and any subset of them might be used in a query. It is much smaller...

HOT updates allow PostgreSQL to update a row without updating its indexes, provided the change fits on the same disk page and no indexed columns were modified. ...

- **pg_dump**: Backs up a single database. - **pg_dumpall**: Backs up all databases in a cluster, including global objects like roles and groups.

Enable the `pg_stat_statements` extension. It tracks execution statistics for all SQL statements executed on the server, allowing you to find the queries that t...

PostGIS is an extension that adds support for geographic objects to PostgreSQL, allowing it to be used as a spatial database for Geographic Information Systems ...

Introduced in Postgres 10, it allows you to define partitions directly using `PARTITION BY` in the table definition, eliminating the need for complex triggers a...

- **Sequential Scan**: Reads every row in the table (Slow for large datasets). - **Index Scan**: Follows the index directly to the row (Fast for small row count...

The checkpointer periodically flushes 'dirty' data pages (changes made in RAM) to the disk. This ensures that the WAL logs can be reused and keeps the recovery ...

The search path is a list of schemas that PostgreSQL looks through when a table is referenced without a schema name. It usually defaults to `"$user", public`.

1. Use GIN indexes for general containment queries. 2. Use expression indexes for specific nested fields. 3. Use the containment operator `@>` for best index pe...

This is a simple messaging system within PostgreSQL. A client can `LISTEN` to a channel, and another client can `NOTIFY` that channel with a message, which is d...

Postgres can use multiple CPU cores to execute a single query. It splits the work (e.g., table scans, joins) across multiple background workers to speed up exec...

Use a subquery with the `MAX()` function.

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

Use a subquery with `MIN(id)` and a `GROUP BY` clause.

DELETE FROM employees WHERE id NOT IN (
  SELECT MIN(id) FROM employees GROUP BY email
);

Use `GROUP BY` and `HAVING`.

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;

Use the `ILIKE` operator or the `LOWER()` function.

SELECT * FROM users WHERE username ILIKE 'john%';
-- OR
SELECT * FROM users WHERE LOWER(username) = 'john';

Use the `?` operator.

SELECT * FROM logs WHERE data ? 'error_code';

Use the `->>` operator or the containment operator `@>`.

SELECT * FROM orders WHERE info ->> 'status' = 'shipped';
-- OR
SELECT * FROM orders WHERE info @> '{"status": "shipped"}';

Use the `||` operator or the `CONCAT()` function.

SELECT first_name || ' ' || last_name AS full_name FROM users;

Use the `LEFT()` or `SUBSTRING()` function.

SELECT LEFT(description, 5) FROM products;

Use the `NULLIF()` function.

SELECT amount / NULLIF(quantity, 0) FROM sales;

Use the `ROW_NUMBER()` window function within a CTE.

WITH RankedLogins AS (
  SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date DESC) as rn
  FROM logins
)
SELECT * FROM RankedLogins WHERE rn = 2; -- 2nd most recent

Use the `UPDATE ... FROM` syntax with a values list or a temporary table.

UPDATE employees AS e SET salary = v.new_salary
FROM (VALUES (1, 60000), (2, 70000)) AS v(id, new_salary)
WHERE e.id = v.id;

Use `CREATE TABLE ... AS` with a `WHERE false` condition.

CREATE TABLE employees_backup AS SELECT * FROM employees WHERE false;

Use the `BETWEEN` operator.

SELECT * FROM products WHERE price BETWEEN 10 AND 20;

After inserting, you must manually sync the sequence associated with the SERIAL column.

SELECT setval(pg_get_serial_sequence('employees', 'id'), MAX(id)) FROM employees;

Use the `current_database()` function.

SELECT current_database();

Use the `pg_database_size()` function combined with `pg_size_pretty()` for readability.

SELECT pg_size_pretty(pg_database_size('mydb'));

Query the `pg_locks` and `pg_stat_activity` views.

SELECT pid, usename, query FROM pg_stat_activity 
WHERE pid IN (SELECT pid FROM pg_locks WHERE relation = 'employees'::regclass);

Use the `pg_cancel_backend(pid)` or `pg_terminate_backend(pid)` functions.

SELECT pg_terminate_backend(1234); -- pid 1234

Use the `generate_series()` function.

SELECT generate_series(1, 10);

Use `string_to_array()` and `unnest()`.

SELECT unnest(string_to_array('apple,banana,cherry', ','));

Subtract the timestamps to get an interval, then use `EXTRACT(EPOCH ...)` / 3600.

SELECT EXTRACT(EPOCH FROM (end_time - start_time)) / 3600 FROM tasks;

Query the `information_schema.columns` view.

SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'employees';

Use a window function with `SUM()`.

SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date) as running_total FROM sales;

Use the `INET` or `CIDR` data types and create a GIST index. These types provide built-in validation and allow for efficient network overlap queries.

Use the `COPY` command (requires server-side access) or the `\copy` meta-command in `psql` (client-side).

\copy employees TO 'employees.csv' CSV HEADER;