PostgreSQL Interview Questions

PostgreSQL stores, organises, and retrieves data effectively. It stores data in tables made up of rows and columns. It's used for web applications, business software, analytics, and location-based applications.

PostgreSQL uses Structured Query Language (SQL) to create, read, update, and delete data. It supports ACID compliance, ensuring transactions are reliable and consistent. You can add custom data types, functions, and extensions with PostgreSQL.

Asked by: TCS, Cognizant, Tech Mahindra

Below are the key features of PostgreSQL Beta 2:

• Parallel Autovacuum
• Automatic Scaling of Asynchronous I/O Workers
• pg_plan_advice Extension
• pg_stash_advice Extension
• Improved Query Optimizer
• Parallel Maintenance Improvements
• DDL Extraction in Core
• Better Replication and Monitoring.

AIO allows the database to send multiple disk read requests simultaneously. So it doesn't have to wait for each I/O operation to finish before starting the next one.

Asked by: Infosys, Wipro, HCL Tech

The following workloads benefit the most:

• Data warehousing (OLAP)
• Sequential scans
• Large bulk operations
• Vacuum on large tables
• Business intelligence and reporting
• Analytics queries
• Data lake and ETL processing
• Batch jobs.

UUIDv7 creates time-ordered UUIDs, enabling values to increase steadily over time. It solves the random-insert fragmentation issue in B-tree indexes using UUIDv4.

Let’s see the advantages of UUIDv7 in the following:

• Sequential inserts reduce index fragmentation
• Improved write performance
• Enhanced index performance
• Reduced storage costs
• Efficient chronological ordering.

A stored generated column calculates its value whenever a row is inserted or updated. It saves the result in the table. On the other hand, a virtual generated column is not stored in the table. PostgreSQL computes it each time it is referenced in a query.

Stored columns write results to disk, leading to faster reads but requiring more storage. On the other hand, virtual columns are calculated during reading, using no storage and reducing CPU costs per query.

Asked by: HCL Tech, Infosys, TCS

PostgreSQL enables you to:

• Create, update, and delete tables in the database
• Manipulate, modify, and access data in tables
• Summarise and retrieve the required information from a single table or multiple tables
• Support OLTP, analytics, GIS, JSON applications, SaaS, and data warehousing.

Asked by: TCS

PostgreSQL uses the following methods:

• CREATE DATABASE command
• A command-line executable

Asked by: IBM, TCS, EPAM

A schema in PostgreSQL is a logical namespace that organizes database objects such as tables, views, functions, sequences, and types.

Asked by: Wipro, HCL Tech

Joins can be used to combine and retrieve records from two or more tables. PostgreSQL uses SQL joins to perform these operations.

Asked by: Cognizant, Accenture

We use constraints to ensure data integrity in tables and avoid unnecessary actions.

A vector embedding is a numerical representation of data, such as text, images, and audio, produced by an AI model.

pgvector allows you to store high-dimensional vectors generated by AI models. We can perform semantic searches, recommendations, and retrieval-augmented generation (RAG) without needing a separate vector database.

PostgreSQL offers the following constraints:

• Not-Null constraints
• Unique constraints
• Check constraints
• Foreign keys
• Primary keys
• Exclusion Constraints (EXCLUDE)

Asked by: TCS

• Inner Join: It returns the records with matching values in both tables.
• Right Join: It returns all records from the right table and the matched records from the left table.
• Left Join: It returns all records from the left table and the matched records from the right table.
• Full Join: It returns all records when there is a match in either the left or the right table.

Asked by: Wipro, HCL Tech

A view displays the results of a query against one or more inherent tables. Views simplify complex queries. We can define views once and query directly.

Asked by: TCS, Wipro

In PostgreSQL, a sequence is a database object that generates a sequence of unique integers that we can use as default values for a column or as part of a primary key.

A tablespace is a disk location where we can store the data files for specific tables or indexes. It helps to have good control over disk usage and file placement.

In a database, normalization is a mechanism for creating or modifying databases to address inefficiencies in data storage, query processing, or data modification. In other words, normalization helps reduce data complexity and redundancy.

Asked by: Infosys, Cognizant, EPAM

Triggers are specifications that require a database to automatically execute a specific function whenever a particular kind of operation is performed. We can define triggers that execute before or after INSERT, UPDATE, or DELETE operations. 

If a trigger event occurs, the trigger’s function is invoked at the appropriate time to handle the event.

Asked by: HCL Tech, TCS, Wipro, Capgemini

A primary key ensures the integrity and uniqueness of the data in the table. Moreover, the values of primary key columns should be unique, and a primary key column must not include null values.

A primary key consists of unique values and non-null values, whereas a foreign key is shared between two or more tables.

Citus Data developed Citus and is now maintained by Microsoft. Citus is an extension for PostgreSQL that turns a single PostgreSQL database into a distributed database cluster. It is available as open source and serves as the distributed engine for Azure Cosmos DB for PostgreSQL.

Citus enables horizontal sharding of PostgreSQL across multiple nodes. It helps PostgreSQL scale horizontally by spreading data and queries across multiple servers, making it suitable for applications that exceed the limits of a single machine.

Below are the different isolation levels:

• READ COMMITTED (Default)
• REPEATABLE READ
• SERIALIZABLE

REPEATABLE READ cannot prevent all serialisation issues caused by concurrent transactions. SERIALIZABLE detects these issues and cancels one of the conflicting transactions, ensuring the database stays in a valid state.

Yes, we can run PostgreSQL on many cloud providers, including AWS, Google Cloud, and Azure.

MVCC improves database performance in multi-user scenarios. When we query a database, every transaction sees a snapshot of the database from some point in the past, regardless of the database's current state.

There are three primary commands for controlling transactions in PostgreSQL.

• BEGIN TRANSACTION or BEGIN: It can be used to start a transaction.
• ROLLBACK: It is used to undo transactions that have not been committed to the database.
• COMMIT or END TRANSACTION: Used to save changes. The “COMMIT” command saves all transactions to the database, whereas the “ROLLBACK” command rolls back all transactions.

pgAdmin is the official tool for managing and developing PostgreSQL. It is used for database management, running queries, backing up and restoring data, monitoring, and handling server tasks.

Asked by: TCS, EPAM, HCL Tech

We can use the following strategies to enhance query performance:

• Indexing for queries that include WHERE clauses.
• Performing partitioning for large tables.
• Developing SQL statements to reduce overhead, for instance, by avoiding selecting unnecessary columns in the SELECT statement.
• Maximising memory usage by tuning server parameters to match hardware specifications.

Asked by: Capgemini, Cognizant, Infosys

The database callback functions are called PostgreSQL Triggers. When a specified database event occurs, PostgreSQL Triggers are automatically executed.

We hope that these basic questions have helped you become familiar with PostgreSQL.

B-tree skip scan allows PostgreSQL to use a multi-column B-tree index even if the query doesn't filter on the index's leading column(s). It enables efficient searches even when the query doesn't limit the leading indexed column. 

This scan is helpful for large tables where the leading column has relatively few distinct values, and the later columns are selective. It enables skip scanning on multi-column indexes when the leading column is not in the WHERE clause. 

Asked by: TCS, MindMajix, EPAM

With PostgreSQL 18, the database server can authenticate users using OAuth 2.0 Bearer Tokens from trusted Identity Providers (IdPs). It makes authentication more efficient by enabling PostgreSQL to integrate directly with enterprise identity systems rather than relying on passwords or external authentication methods.

Asked by: Wipro, HCL Tech

If any part of the transaction fails, all the modifications within the transaction can be rolled back, maintaining data integrity.

In PostgreSQL, we can handle transactions internally via auto-commit mode, where each statement is treated as a separate transaction. It can be done externally via the BEGIN, ROLLBACK, and COMMIT statements to define transaction boundaries.

PostgreSQL uses the CLUSTER command to physically reorganise a table by using an index.

Asked by: Wipro, Infosys

We can use the following methods for backing up PostgreSQL:

• Online backups
• SQL dumps
• File-system-level backups

In PostgreSQL, security is addressed on various levels:

• All files stored in a database are protected from read access by accounts other than the PostgreSQL superuser.
• We can limit client connections to a username or IP address.
• Connections from clients can be restricted to the local Unix socket.
• We can authenticate client connections through external packages.
• Each PostgreSQL user is assigned a username and a password.
• We can assign users to groups and limit table access per group privileges.

Asked by: EPAM, infosys, TCS

We can use the “ANALYZE” command to get the actual time needed. We can use the “EXPLAIN ANALYZE” command to identify problems in complex queries and rewrite them to improve performance.

The “pg_dump” method enables us to create a text file with a group of SQL commands. When we run the commands on the PostgreSQL server, it will recreate the database in the same state as it was at the time of the dump.

In PostgreSQL, we use the “CREATE INDEX” statement to create an index. Here is an example:

The above statement will create an index on the “Employee” table.

A composite type allows you to create user-defined types made up of the available data types. They are useful for grouping related data elements into a single entity. To define a Composite type, we can use the “CREATE TYPE” statement.

To create the materialized view, we can use the “CREATE MATERIALIZED VIEW” statement, defining the contents of the view. The materialized view is created or refreshed using a query.

By storing and precomputing results, you can achieve significant performance gains when querying materialized views. Unlike regular views, materialized views are precomputed and updated manually or periodically.

• INSERT INTO … SELECT Statement

By using the INSERT INTO … SELECT statement, we can insert multiple rows in a single SQL statement. We can define the columns and their values in the “SELECT” clause. We need to repeat the “SELECT” statement for each row we want to insert.

This method is used when we need to insert data generated dynamically or retrieved from another table.

• COPY Command

The “COPY” command reads data from the file specified by “data_file” and inserts it into the specified table. The file must include data in the format specified. This method is rapid and useful for huge datasets.

Asked by: TCS, Cognizant, HCL Tech

The advantages of using stored procedures are:

• Code reusability
• Improved security
• Enhanced performance

For creating a stored procedure, you must execute CREATE PROCEDURE or CREATE FUNCTION statements.

A stored procedure will include input and output parameters, control flow logic, and local variables used in loops and conditionals. It will also return the result sets through the “OUT” or “RETURN TABLE” parameters.

A CTE is a temporary result set defined in the query. We can create this result set using the "WITH" clause and refer to it multiple times in the same query. It enhances query readability and enables recursive queries. It is helpful when a complex query requires multiple subqueries that share a common table.

On the other hand, a subquery is a nested query in another query. We can use the subquery in the ‘WHERE', ‘HAVING’, or ‘FROM’ clauses. Subqueries are assessed first, and their results can be used in the outer query. We cannot reuse subqueries. It may impact query performance when subqueries are used excessively.

The ‘UNION ALL’ operator combines the results of multiple ‘SELECT’ statements without removing duplicate rows. It contains all the rows from every ‘SELECT’ statement, including duplicates. This operator is faster than the ‘UNION’ statement because it does not require duplicate elimination.

On the other hand, the ‘UNION’ operator combines the results of multiple ‘SELECT’ statements and removes duplicate rows from the final result set. It performs a distinct operation, ensuring that unique rows are returned. This operation will incur overhead due to duplicate elimination.

Write-ahead logging enhances database reliability by recording changes before they are applied to the database.

Autovacuum protects storage from dead tuples, updates table statistics, and helps prevent transaction ID wraparound. Without autovacuum, database performance gradually declines as tables gather dead rows, indexes become inefficient, and the query planner uses outdated statistics.

Common tuning scenarios:

1. Large OLTP Databases - Large transactional systems produce many updates and deletes.

Typical adjustments include the following:

These changes reduce table bloat and keep statistics up to date.

2. Write-heavy Tables - Change settings for each table individually when tables are heavily updated.

VACUUM in PostgreSQL is a critical process that reclaims disk space occupied by outdated data. The VACUUM process will identify reusable pages and free up space for future use.

It mainly performs the following two primary tasks:

• Updating the Statistics: The VACUUM ANALYZE or ANALYZE command updates the statistics used by the query planner. It enables PostgreSQL to select optimal query plans, thereby improving performance.
• Releasing Disk Space: When data is deleted or updated in PostgreSQL, it is not immediately freed on disk. Rather, it is marked as reusable for future inserts.

• Physical Replication

This method involves creating an exact copy of the main database by continuously streaming write-ahead logs to multiple standby servers. We can use the standby servers as read-only query failover targets if the primary server becomes unavailable.

Streaming Replication is a type of physical replication that is easy to set up and supports real-time, low-latency replication.

• Logical Replication

This method replicates changes to specific databases or tables, rather than to the entire database cluster. It uses publications and subscriptions to define which data must be replicated. It offers more flexibility but needs continuous monitoring.

PostgreSQL uses MVCC (Multi-Version Concurrency Control), which doesn't allow dead tuples to be removed immediately. While VACUUM marks that space as reusable, it doesn’t actually shrink the table file on disk. Over time, this can make tables and indexes much larger than they need to be.

The following are the different methods that you can use to diagnose table bloat:

• Checking dead tuples
• Verifying autovacuum activity
• Comparing table sizes
• Comparing table and index sizes
• Using the pgstattuple extension.

pg_stat_user_tables is one of the most helpful views for tracking table activity, autovacuum, and the build-up of dead tuples. It is therefore crucial for identifying table bloat and checking if autovacuum is working effectively.

We can use pg_stat_user_tables to track dead tuples in the following ways.

• Showing estimated counts of live and dead rows for each user table
• Providing information about when autovacuum and ANALYZE last ran
• Checking if autovacuum is managing table maintenance effectively.

The key columns are n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze, autovacuum_count, and vacuum_count.

In applications with many users, opening a new PostgreSQL connection for each request can consume significant CPU and memory. It can lower performance and hit the server's connection limit.

A connection pooler allows many client requests to share a smaller pool of persistent database connections.

Monitoring replication lag in PostgreSQL is crucial to keep standby servers in sync with the primary. Too much lag can lead to outdated reads, slow failover, and extended recovery times.

There are a few methods to monitor replication lag, depending on whether you are checking from the primary or the standby server.

• Checking the replication status on the primary
• Comparing WAL locations
• Examining the replay delay on the standby.

MVCC enables multiple transactions to use the same data simultaneously without causing conflicts or blocking. PostgreSQL provides multiple isolation levels and locking mechanisms to handle concurrent updates and resolve conflicts. 

We can select proper transaction isolation levels like REPEATABLE READ, READ COMMITTED, and SERIALIZABLE based on the application’s needs.

We can use the JSON and JSONB data types to store JSON data. JSONB offers binary storage and provides querying and indexing capabilities.

For querying JSON data, PostgreSQL provides functions such as:

• jsonb_array_elements
• jsonb_extract_path
• jsonb_agg

These functions enable the extraction of specific values, navigation through JSON arrays and objects, and aggregation of JSON data.

We can also use operators such as -> and ->> to access JSON values and fields directly in SQL queries. Moreover, PostgreSQL supports indexing on JSONB columns, enabling effective querying of JSON data.

To perform logical replication, we must follow the steps below:

• Enable the logical replication feature by setting the "wal_level" configuration parameter to logical in the postgresql.conf file.
• Create the publication on the source database through the "CREATE PUBLICATION" statement. It will define the tables or schemas that can be replicated.
• Create the subscription on the target database through the "CREATE SUBSCRIPTION" statement.
• Specify connection information for the source database and publication to replicate.
• Begin the replication process by implementing the "ALTER SUBSCRIPTION" statement with the "ENABLE" option.

After that, PostgreSQL replicates the table or schema from the source database to the target database, keeping them synchronised.

Let’s explore more about the PostgreSQL Architecture

• Postmaster Supervisor Process

Postmaster serves as the supervisor in PostgreSQL, and it is the first process to start after PostgreSQL starts. It serves as the listener and is responsible for authorising and authenticating incoming client requests and for allocating a new Postgres connection for each request.

• Shared Memory Segments

Shared Memory Segments are memory-backed caches reserved for transactions and maintenance activities. We can allocate various shared memory segments to perform different operations.

• Background Processes of PostgreSQL

Background processes maintain consistency between disk and memory, enabling PostgreSQL to operate properly. Checkpointer, Background Writer, WAL Writer, and Physical Files are the background processes.

To implement parallel query execution, we can follow the steps below:

• Ensure that the max_parallel_workers configuration parameter is set to a value greater than zero in the postgresql.conf file. It determines the maximum number of parallel workers for query execution.
• Adjust the max_parallel_workers_per_gather configuration to control the number of parallel tasks per query gather. It allows restricting parallelism on an individual query.
• Set the min_parallel_index_scan_size and min_parallel_table_scan_size configuration parameters to control the minimum index or table size required for parallel scans to be considered.
• If required, we can manually disable or enable parallel execution for particular queries through the SET max_parallel_workers_per_gather statement or by modifying the index or table settings with "ALTER INDEX" or "ALTER TABLE."

By configuring these settings and using the parallel-safe operators, PostgreSQL will parallelise query execution across worker processes, improving query performance.

PostgreSQL supports the following kinds of Indexes:

• Hash Index: Effective for equality-based lookups but not ideal for range queries.
• B-tree Index: This is the default index type, ideal for equality conditions and range queries.
• Generalized Inverted Index (GIN): It is suitable for full-text search and arrays.
• Generalized Search Tree (GIST) Index: It supports several data types and operators, including text and spatial search.
• SP-GiST (Space-Partitioned Generalized Search Tree) Index: It is suitable for custom data types and supports several search strategies.
• Block Range Index (BRIN): It is designed for large tables that store sorted data, enabling efficient scans by partitioning data into blocks.
• Bloom Filter Index: It provides approximate matching, which is helpful for massive datasets.

Every index type has its benefits and is ideal for different scenarios. The index type selection is based on your data's particular characteristics and requirements.

In PostgreSQL, the pg_stat_activity view provides information about the server's currently active sessions. It includes a row for every session linked to the database, containing details such as username, process ID, application name, and more.

It is generally used to monitor the database server, check for idle or blocked connections, identify long-running queries, and gather performance-related information. By querying this view, we can gain insights into current activity and the database's state, troubleshoot issues, optimise performance, and manage connections efficiently.

In PostgreSQL, a recursive query is a query that references its own output. It enables you to perform repetitive operations or traverse hierarchical or graph-like structures.

This query is built using the "WITH RECURSIVE" clause, also known as a Common Table Expression (CTE).

CTE includes two parts:

• The anchor member
• The recursive member

The anchor member serves as the base case, whereas the recursive member depends on the result of the previous iteration.

The recursive member enables you to query iteratively until a particular condition is satisfied. This recursion will enable you to perform hierarchical queries, traverse tree structures, and handle recursive data relationships.

A multi-version model enables multiple versions of the same data to exist in parallel. In contrast, a lock model allows only one version to be available at a time and locks the data while it is being edited.

The pg_stat_replication view provides information on the activity and status of standby servers in a streaming replication setup. By using this view, we can get the following details:

• Standby server name and connection information
• Replication lag between the standby and primary servers.
• Received and applied WAL positions
• Replication state

This view is used to monitor the synchronisation and health status of the standby servers and to ensure the overall stability of the replication setup.

Sharding refers to horizontal partitioning of data across multiple shards or servers to distribute load and scale the database system.

We must follow these steps to implement sharding.

In PostgreSQL, row-level security enables us to limit access to rows in a table based on specific policies or conditions.

To implement row-level security, we have to follow these steps:

• Define the security policy on the table through the “ALTER TABLE” statement with the “ENABLE ROW LEVEL SECURITY” clause. It relates to the table that lists the security policy names.
• Create the security policy using the “CREATE POLICY” statement, specifying the conditions that determine which rows we can modify or access. We can use column values, custom functions, or user roles to define policy rules.
• Grant the appropriate privileges to database roles using the “GRANT” statement, enabling them to access the table with the specified security policies.

After implementing row-level security, PostgreSQL automatically applies security policies whenever queries are executed against the related table.

We can use the following techniques to implement data encryption:

• Column-level Encryption: This technique encrypts sensitive data in database columns. We can use the “pgcrypto” extension to encrypt specific columns using cryptographic algorithms and functions.
• Transparent Data Encryption (TDE): It encrypts data at the storage level, making it transparent to users and applications. By enabling TDE on the file system or using hardware encryption, all data stored in PostgreSQL will be encrypted on disk in the latest PostgreSQL versions.
• TLS/SSL Encryption: PostgreSQL supports encrypting client-server communication using TLS/SSL. By enabling SSL/TLS and configuring the required certificates, we can secure data in transit.
• Application-Level Encryption: Encrypting data at the application level involves encrypting it before it is stored in the database and decrypting it when it is retrieved. This approach gives complete control over key management, access control, and encryption algorithms.

Changes to execution plans, data volume, statistics, indexes, configuration, or system resources can cause slowdowns.

We can resolve this issue by using the methods below:

• Comparing the execution plan
• Checking if the statistics are outdated
• Verifying the index usage
• Checking for table growth
• Looking for table or index bloat
• Investigating blocking.

Choosing between B-tree and BRIN (Block Range Index) mainly depends on table size, data distribution, and query patterns. They are optimized for various workloads.

We can use a B-tree index for:

• primary keys
• Equality searches
• Highly selective queries
• Range queries.

We can use BRIN for:

• Time-series tables
• Append-only tables
• Very large tables
• Data with natural correlation.