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.