Top 30 PostgreSQL Intermediate Interview Questions – Part 2 (11-20)

Welcome to Part 2 of our PostgreSQL Intermediate Interview Questions series! 🎯

In this post, we’ll cover the first 10 must-know intermediate PostgreSQL questions, including: Sequences, Tablespaces, storage, and more!
Note: All questions and answers are based on my experience, research, and self-study.

1. How does PostgreSQL handle case sensitivity in identifiers?

In PostgreSQL,case sensitivity varries on unquoted identifiers are folded to lowercase and are case-insensitive. Quoted identifiers are case-sensitive and must be used with exact casing.

2. What is the difference between NOW(), CURRENT_TIMESTAMP, and LOCALTIMESTAMP?

1. NOW() and CURRENT_TIMESTAMP: They are essentially the same. Both return the current date and time based on the server’s time zone.
2. NOW(): It’s actually just a shorthand for CURRENT_TIMESTAMP.
3. LOCALTIMESTAMP: It also returns the current date and time without the time zone information attached.

3. What is a Common Table Expression (CTE)?

A Common Table Expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It improves query readability, helps organize complex queries, and can even be recursive for hierarchical data. CTE is written using the WITH keyword. Example:

WITH recent_orders AS (
SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '7 days'
)
SELECT customer_id, COUNT(*)
FROM recent_orders
GROUP BY customer_id;

4. What is the difference between materialized and regular views?

A regular view is simply a stored SQL query that does not store any data. Every time you query a view, PostgreSQL re-runs the underlying query dynamically.

A materialized view, on the other hand, stores the actual query result on disk. When you query a materialized view, it returns precomputed data, making it much faster, but the data can become stale until you refresh it manually.

5. How does PostgreSQL handle NULL values in indexing and sorting?

NULL Values are treated as distinct and not equal to any other value, including another value NULL. During indexing, especially with B-tree indexes (the default type), NULL Values are included and indexed, but since comparisons like col = NULL these are not valid (they must be written as col IS NULLSuch queries won’t benefit from the index unless written appropriately. Partial indexes can be used to include or exclude NULLs explicitly, such as creating an index only on rows where a column is not NULL. In sorting, PostgreSQL by default places NULL values last when ordering in ascending (ASC) order and first in descending (DESC) order. However, this behavior can be overridden using NULLS FIRST or NULLS LAST in the ORDER BY A clause to control the placement of NULLs explicitly.

6. What is the purpose of pg_stat_statements?

The pg_stat_statements A module in PostgreSQL is like a built-in tool that helps us understand how our database is being used. It keeps track of all the SQL queries that run on your system, showing things like how often each query runs, how long it takes on average, and how many rows it returns. One of the best parts is that it groups similar queries together by replacing specific values with placeholders, so even if the values change, it still counts them as the same query. This makes it much easier to spot which queries are slowing things down or running too often, so you can focus on optimizing the ones that matter most. It’s really helpful for performance tuning and keeping your database healthy.

7. How can you retrieve the size of a database, table, or index in PostgreSQL?

PostgreSQL provides handy built-in functions to check the size of different objects. For a whole database, you can use pg_database_size('your_db_name') the friendlier pg_size_pretty() to get a readable format. For example:

sqlCopyEditSELECT pg_size_pretty(pg_database_size('mydb'));  

To get the size of a table, use: pg_table_size('table_name'), and for indexes, use: pg_indexes_size('table_name'). You can also check the total size of a table, including its indexes and toast data, with pg_total_relation_size('table_name').

8. How does PostgreSQL handle user privileges and roles?

The database uses a flexible role-based system for managing user access. A role can be a user or a group, and it can have login privileges (LOGIN) or just act as a permission group. You grant permissions (like SELECT, INSERT, UPDATE) to roles on specific objects such as tables or schemas. Roles can also inherit privileges from other roles, making it easy to manage groups of users. This structure allows us to define fine-grained access control for your database in a clean and scalable way.

9. Explain the ON CONFLICT clause in PostgreSQL.

The ON CONFLICT Clause is PostgreSQL’s way of handling upserts—basically, inserting data while gracefully dealing with duplicates. When you try to insert a row that would violate a unique constraint or primary key, ON CONFLICT it let you decide what to do. You can either DO NOTHING (ignore the insert) or DO UPDATE (Update the existing row). For example:

sqlCopyEditINSERT INTO users(id, name) VALUES (1, 'John')  
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;  

10. How does PostgreSQL implement Full-Text Search (FTS)?

PostgreSQL has powerful built-in support for Full-Text Search (FTS). It allows you to search natural language text efficiently by converting the content into a searchable format called a tsvector, which breaks text into searchable tokens. Queries use tsquery to define search terms. For example, you can index text columns using a GIN index on a to_tsvector() expression, and then query it with @@ (text match operator). It supports ranking, stemming, stop words, and different languages, making it ideal for building search features inside applications.

• Continue Learning:
• Top 30 PostgreSQL Intermediate Interview Questions – Part 1 (1-10)