Top 48 Most Asked PostgreSQL Interview Question and Answers

Following is a list of most frequently asked technical support interview questions and their best possible answers.

1) What is PostgreSQL?

PostgreSQL is an open-source object-relational database management system known as ORDBMS. It is known as Postgres or Postgresql. In the SQL world, it is one of the most widely and popularly used for Object-Relational Database Management System mainly used in large web applications. It is a powerful database management system that provides additional and substantial power by incorporating four basic concepts so that the users can extend the system without any problem.

It uses the SQL language and extends its features to store the data securely. It supports the best practices and allows users to retrieve the data when the request is processed.

2) What are the most important features of PostgreSQL?

The most important features of PostgreSQL are as follows:

Free to download

PostgreSQL is open-source software and free to download. We can easily download it from the official website of PostgreSQL.

Area of Compatibility

• PostgreSQL is compatible with several operating systems such as Microsoft Windows, Linux, MacOS X, UNIX (AIX, BSD, HP-UX, SGI IRIX, Solaris, and Tru64), etc.
• It is compatible with various programming languages such as C/C++, Java, Python, Perl, Ruby, Tcl, and ODBC (Open Database Connectivity).

PostgreSQL is compatible with multiple data types such as

• Structured data types, i.e., Array, Date and Time, UUID (Universally Unique Identifier), Array, Range, etc.
• Primitive data types, i.e., String, Integer, Boolean, Numeric, etc.
• Customizations data types, i.e., Custom Types, Composite, etc.
• Geometry data types, i.e., Polygon, Circle, Line, Point, etc.
• Document data types i.e. XML, JSON/JSONB, Key-value, etc.

Extensibility

• PostgreSQL is highly extensible as it supports procedural languages such as Perl, PL/PGSQL, Python, etc.
• It is compatible with foreign data wrappers, which connect to further databases with a standard SQL interface.
• It supports JSON/SQL path expressions, stored procedures, and functions.
• It supports a customizable storage interface for a table.

Secure and Highly Reliable

PostgreSQL is safe and secure because of the following security aspects:

• PostgreSQL provides a robust access control system and several authentications such as Lightweight Directory Access Protocol (LDAP), Generic Security Service Application Program Interface (GSSAPI), SCRAM-SHA-256, Security Support Provider Interface (SSPI), Certificate, etc.
• It also supports column and row-level security.
• It is highly reliable as it provides disaster recovery such as active standbys and PITR (Point in time recovery).
• It supports WAL (Write-ahead Logging)
• It supports different types of Replication like Synchronous, Asynchronous, and Logical.
• It supports Internationalization, which includes ICU collations, accent-insensitive and case-sensitive collations, and full-text searches.
• It is compatible with ANSI-SQL2008.
• It can improve the functionality of Server-Side programming.

3) What are the key advantages and disadvantages of PostgreSQL?

Advantages of PostgreSQL

Following is a list of the biggest advantages of PostgreSQL:

• PostgreSQL is available as an open-source license, so we can easily get the source code of PostgreSQL, immediately implement it, and change it according to our requirements.
• It is one of the easiest relational database management systems to learn, so users do not require much training before using it.
• It can execute dynamic web-application and websites as the LAMP stack option.
• PostgreSQL is a highly risk-tolerant database, widely used in large web applications.
• It requires easy and low maintenance management for enterprise and embedded usage.
• PostgreSQL is robust and powerful. That's why it is preferred for large-scale web applications.

Disadvantages of PostgreSQL

Following is a list of the key disadvantages of PostgreSQL:

• PostgreSQL does not support many open-source applications compared to the MySQL database.
• In PostgreSQL, creating replication is a bit complex which reduces its popularity.
• The speed and performance of PostgreSQL are not as good as compared to some other databases and tools.
• PostgreSQL is not maintained by one company. This may be one of its drawbacks.
• It is slower as compared to the MySQL database.
• It is not popular as MySQL, so the installation process is sometimes not easy for beginners.

4) What are the different data types used in PostgreSQL?

Following is a list of the different data types supported by and used in PostgreSQL?

• Numeric types
• Character types
• Temporal types
• Boolean
• UUID
• Geometric primitives
• Arbitrary precision numeric
• XML
• Arrays etc.

Note: PostgreSQL also facilitates their users to create indexes and get them indexed.

5) What is the name of the process of splitting a large table into smaller pieces in PostgreSQL?

The name of the process of splitting a large table into smaller pieces in PostgreSQL is known as table partitioning.

6) What do you understand by a base directory in PostgreSQL?

The base directory in PostgreSQL is data_dir/base. It is a folder in PostgreSQL which contains all the sub-directories used by a database in clusters and stores all the data you have inserted in your databases.

7) What do you understand by string constants in PostgreSQL?

In the PostgreSQL database, a string constant is a sequence of some character bounded by single quotes ('). For example: 'This is an example of string Constant'.

8) What is the maximum size for a table in PostgreSQL?

PostgreSQL provides unlimited user database size, but it doesn't provide an unlimited size for tables. In PostgreSQL, the maximum size for a table is set to 32 TB.

9) What do you understand by a partitioned table in PostgreSQL?

In PostgreSQL, a partitioned table is a logical structure used to split a large table into smaller pieces. These small pieces of the tables are called partitions.

10) What is Multi-Version Concurrency Control in PostgreSQL? Why is it used?

Multi-Version Concurrency Control or MVCC is an advanced technique in PostgreSQL that improves database performance in a multi-user environment. It is mainly used to avoid unnecessary locking of the database by removing the time lag for the user to log into his database. This time lag occurs when someone else is accessing the content. In Multi-Version Concurrency Control or MVCC, all the transactions are kept as records. That's why PostgreSQL maintains data consistency, unlike most other database systems which use locks for concurrency control.

11) What is the key difference between multi-version and lock models?

The key difference between Multi-Version Concurrency Control and lock models is that in MVCC, the locks acquired for querying or reading the data doesn't conflict with locks acquired for writing data. In this case, reading never blocks writing, and writing never blocks reading. So, Multi-Version Concurrency Control has the upper hand compared to other lock models.

12) What is pgAdmin in the PostgreSQL server? Why is it used?

In the PostgreSQL server, PgAdmin is a free, open-source PostgreSQL database administration GUI or tool used in Microsoft Windows, Mac OS X, and Linux systems. PgAdmin is used to retrieve, develop, conduct quality testing, and maintain databases or other ongoing maintenances.

13) How can you set up pgAdmin in PostgreSQL?

To set up pgAdmin in PostgreSQL, we should follow the steps given below:

• First, start and launch pgAdmin 4.
• Then, go to the "Dashboard" tab, click on the "Quick Link" section and then click on "Add new Server."
• After clicking on the "Add new Server", you have to select the "Connection" tab in the "Create-Server" window.
• Now, configure the connection by entering your server's IP address in the "Hostname/Address" field.
• At last, you have to specify the "Port" as "5432," which is the by default port for the PostgreSQL server.

14) What do you understand by PL/Python?

PostgreSQL provides support to a procedural language known as PL/Python.

15) What are the Indices of PostgreSQL?

Indices of PostgreSQL are inbuilt functions or methods such as GIST Indices, hash table, and B-tree (Binary tree). The user uses these to scan the index in a backward manner. PostgreSQL also facilitates their users to define their indices of PostgreSQL.

16) What is the way to avoid unnecessary locking of a database?

The best way to avoid unnecessary database locking is to use MVCC (Multi-version concurrency control). It is an advanced technique used in PostgreSQL for improving database performances.

17) What are the different types of operators used in PostgreSQL?

There are mainly four types of operators used in PostgreSQL:

• Arithmetic operators
• Comparison operators
• Logical operators
• Bitwise operators

18) What are the tokens in PostgreSQL?

In PostgreSQL, tokens are the building blocks of any source code. Tokens contain several types of special character symbols like constants, quoted identifiers, other identifiers, and keywords. The keywords tokens contain pre-defined SQL commands and meanings. On the other hand, identifiers specify variable names like columns, tables, etc.

19) What does a schema contain in PostgreSQL?

In PostgreSQL, a schema contains tables and data types, views, indexes, operators, sequences, and functions.

20) What are some new characteristics introduced in Postgre 9.1?

The new PostgreSQL 9.1 version is working on important features such as JSON support, synchronous replication, nearest-neighbor geographic searches, SQL/MED external data connections, security labels, and index-only access.

Following is a list of some newly added characteristics in PostgreSQL 9.1:

• Added support for foreign tables.
• Added support for per-column collation.
• Added some extensions to simplify packaging of additions to PostgreSQL.
• Added a true serializable isolation level.
• Added nearest-neighbor (order-by-operator) searching to GiST indexes.
• Added a SECURITY LABEL command and support for SELinux permissions control.
• Along with the above features, PostgreSQL 9.1 has allowed other features such as allowing synchronous replication, allowing data-modification commands (INSERT/UPDATE/DELETE) in WITH clauses, providing support for unlogged tables using the UNLOGGED option in CREATE TABLE, and updating the PL/Python server-side language.

21) What is the use of indexes in PostgreSQL?

In PostgreSQL, indexes are used by the search engine to enhance the speed of data retrieval.

22) What do you know about the history of PostgreSQL?

The POSTGRES project was started and led by Professor Michael Stonebraker in 1986 and was sponsored by the Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc. It has completed more than 30 years of active development on the core platform. It has been ACID-compliant since 2001 and runs on all the major operating systems. It also has an add-on like PostGIS database extender.

Michel Stonebraker is known as the father of PostgreSQL. He started and led the development of this database in 1986 as a follow-up project to its predecessor, Ingres, now owned by Computer Associates. PostgreSQL was originally called Postgres. It is pronounced PostgreSQL because of its ubiquitous support for the SQL standards among most relational databases. PostgreSQL is used as a by default database in MAC OS.

Postgres was started by Michael Stonebraker in 1986 as a follow-up project to its predecessor, Ingres, which Computer Associates now own. The name Postgres was derived from its predecessor Ingres. Here, Postgres means "after Ingres". The first project of Postgres was developed between 1986 - 1994. It has provided many sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write-ahead logging for fault tolerance.

23) What do we call database callback functions? What is its purpose?

The database callback functions are known as PostgreSQL Triggers. The PostgreSQL Triggers are performed or invoked automatically whenever a specified database event occurs.

24) How can you start, stop, and restart the PostgreSQL server on Windows?

To start, stop, and restart the PostgreSQL server on Windows, first, we have to find the PostgreSQL database directory. It would look something like this: C:\Program Files\PostgreSQL\10.4\data. Now, open the Command Prompt and execute the following commands:

To start the PostgreSQL server of Windows:

To stop the PostgreSQL server of Windows:

To restart the PostgreSQL server of Windows:

Another way to start, stop and restart the PostgreSQL server on Windows.

There is also another way to start, stop, and restart the PostgreSQL server on Windows. Follow the steps given below:

• First, open the Run Window by pressing the Windows key + R simultaneously.
• Then, type services.msc to find out the PostgreSQL services.
• Search Postgres service based on the version installed.
• Click the stop, start or restart option to do the same.

25) What does a Cluster index do?

A Cluster index is used to sort table data rows according to their key values.

26) Which command is used to create a database in PostgreSQL?

In PostgreSQL, the CREATE DATABASE command is used to create a new database.

27) How can you delete a database in PostgreSQL?

In PostgreSQL, you can delete a database using the DROP DATABASE command in the psql command-line tool.

28) How do you update the stats or statistics in PostgreSQL?

To update stats or statistics in PostgreSQL, we have to call a special function called explicit 'vacuum'. This function creates a Vacuum where the option of Analyze is used to update statistics in PostgreSQL.

Syntax:

29) What is the difference between clustered index and non clustered index in PostgreSQL?

The main difference between clustered index and non clustered index in PostgreSQL is that the clustered index is an index type used to sort table data rows according to their key values. In RDBMS, a user can create a clustered index based on that column using the primary key. On the other hand, a non-clustered index is an index where the order of the rows does not match the physical order of the actual data. The non-clustered index is ordered by the columns that make up the index.

30) What are the advantages of specifying data types in columns while creating a table?

The key advantages of specifying data types in columns while creating a table are consistency, compactness, validation, and optimum performance.

31) What are the different types of database administration tools used in PostgreSQL?

Following are the different types of database administration tools used in PostgreSQL:

• Phppgadmin
• Psql
• Pgadmin

Note: From the above PostgreSQL database administration tools, phppgadmin is the most popular. Most of the above tools are front-end administration tools and web-based interfaces.

32) What is the way to delete complete data from an existing table in PostgreSQL?

The best way to delete complete data from an existing table in PostgreSQL is to use the TRUNCATE TABLE command.

33) What are the disadvantages of the DROP TABLE command in deleting complete data from an existing table?

In PostgreSQL, the DROP TABLE command can be used to delete complete data from an existing table, but the biggest disadvantage of using the DROP TABLE command is that it removes complete table structure from the database. If you use the DROP TABLE command to delete the table, you must re-create a table to store data.

34) What are the different properties of a transaction in PostgreSQL?

Like other RDBMS, PostgreSQL also supports ACID properties. This is commonly referred to by the acronym named ACID. It means the properties of a transaction in PostgreSQL include Atomicity, Consistency, Isolation, and Durability.

35) What is the difference between PostgreSQL and MongoDB databases?

Following are the key differences between PostgreSQL and MongoDB databases:

36) Which commands are used to control transactions in PostgreSQL?

The following commands are used to control transactions in PostgreSQL:

• BEGIN TRANSACTION
• COMMIT
• ROLLBACK

37) What do you understand by parallel query in PostgreSQL? How does it work?

Parallel query is an advanced feature of PostgreSQL in which query plans are arranged so that they are assigned to multiple CPUs, and the user gets the answer to the queries faster.

38) What is the use of the CTIDs field in PostgreSQL?

The CTIDs field identifies the specific physical rows in a table according to their block and offsets positions in that table.

39) What is the use of command enable-debug in PostgreSQL?

In PostgreSQL, the command enable-debug is used to compile all applications and libraries. It provides some debugging symbols that facilitate developers to spot the bugs and other problems which may occur while the execution of the script. When we execute this procedure, it can delay or obstruct the system, amplifying the binary file size.

40) What are the reserved words in PostgreSQL?

The reserved words in PostgreSQL are SQL keywords and other symbols having some special meaning when the Relational Engine processes them. According to the SQL standard, the reserved keywords are the only real keywords, and they cannot be allowed as identifiers. On the other hand, the non-reserved keywords have a special meaning in particular contexts and can be used as identifiers in other contexts.

41) What do you understand by WAL or Write-Ahead Logging in PostgreSQL?

In PostgreSQL, Write-Ahead Logging or WAL is a standard method to ensure data integrity. It is a protocol or syntax to write actions and changes into a transaction log.

The Write-Ahead Logging feature is used to enhance the reliability of the database by logging changes before any changes or updating to the database. It provides the database log in case of a database failure and ensures to start the work again from the point it was crashed or discontinued.

42) What is tablespace in PostgreSQL? What is its usage?

In PostgreSQL, the tablespace is a location in the disk used to store the data files containing indices and tables.

43) What are the three phenomena that must be prevented between concurrent transactions in PostgreSQL?

There are four levels of transaction isolation used in SQL standard regarding three phenomena that must be prevented between concurrent transactions in PostgreSQL. These three unwanted phenomena are as follows:

• Dirty read: A transaction is called a dirty read when it reads data written by a concurrent uncommitted transaction.
• Non-repeatable read: It specifies a transaction that re-reads the data it has previously read and then finds another transaction that has already modified it.
• Phantom read: It specifies a transaction that re-executes a query, returning a set of rows that satisfy a search condition and then finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

44) What are the key differences between Oracle and PostgreSQL?

45) What do you understand by a sequence in PostgreSQL?

In PostgreSQL, a sequence is a special form of data created to generate multiple numeric identifiers in the database. It creates unique identifiers between multiple rows inside a table. It is mainly used to create sequences and artificial primary keys similar to Auto_Increment in MySQL.

46) What does a token represent in an SQL Statement?

A token represents an identifier, keyword, quoted identifier, special character symbol, or a constant in a SQL Statement.

47) What do you understand by inverted file in PostgreSQL?

In PostgreSQL, an inverted file is an index data structure used to map content to its location to a database file, within a document, or in sets of documents. It generally includes the distinct words found in a text and a list containing the occurrences of a word in the text. It is used in a data structure for document retrieval systems to provide a full-text search.

48) What is the way to store the binary data in PostgreSQL?

There are two ways to store the binary data in PostgreSQL, either by using bytes or the large object feature.