PostgreSQL Interview Questions

1) What is the PostgreSQL database?

PostgreSQL sometimes called as POSTGRES, is an open-source relational database management system. It's a free and most advanced relational database emphasizing SQL compliance. It provides a huge number of functionalities to it's users.

PostgreSQL is a very stable database management system.

It provides transactions with ACID properties. We can perform all common operations like query, filter, join, grouping, subquery in PostgreSQL similar to other databases like SQL Server, MySQL and Oracle.

2) What are the features of PostgreSQL?

PostgreSQL has many useful features. Here are a few of them:

By protecting data integrity, users can build a fault-tolerant environment.
Easy compatibility with significant platforms, languages, and middleware.
Multi-version concurrency control is supported.
The client-server network architecture is supported.
Trigger-based and log-based replication SSL
High availability and standby server

3) List data type in PostgreSQL?

PostgreSQL supports various data types, which includes:

Boolean
Numeric types (Integer, Floating-point)
Character types (char, varchar, text)
Temporal types (date, time, timestamp, interval)
Array (array string, number)
UUID
JSON
Store
Special types such as geometric data and particular types

4) What is Multi Version Concurrency Control in PostgreSQL?

Multi-Version Concurrency Control (MVCC) is an advanced method used in PostgreSQL for improving the performance of a database in a multi-user environment. Multi-version concurrency control or MVCC in PostgreSQL is used to avoid unnecessary locking of the database.

Unlike lock models in other databases, PostgreSQL uses a multi-version environment in which locks that are acquired for reading data don’t conflict with locks acquired for writing the data. Hence, making the process more compartmentalized and a lot faster.

5) List different advantages of Postgresql?

Following are some of the advantages of PostgreSQL :

Stable
Reliable
Extensible
Easy to learn
Open source
Designed for High Volume Environments
Cross Platform
Better Support
Flexible

6) What is the Maximum size for a table in PostgreSQL?

Even though PostgreSQL has unlimited database size for users, but it has a limit for maximum table size. The maximum table size is set to 32 TB.

7) What are string constants in PostgreSQL?

In PostgreSQL, a string constant is an arbitrary sequence of multiple characters that are bounded by single quotes ('). PostgreSQL enables the use of single quotes but is integrated by a C-style backslash. It is used in data parsing.

Example

SELECT 'This' 'is' 'an' 'example' Is equivalent to SELECT 'This is an example'Note that this is not the same as using double quotes (").

8) Which are different types of database administration tools used in Postgresql?
There are the number of data administration tools, and they are

Phppgadmin
Psql
Pgadmin

Out of these, phppgadmin is the most popular one. Most of these tools are front-end administration tools and web-based interfaces.

9) What is difference between clustered index and non clustered index?

Difference between clustered index and non clustered index

Cluster index is an index type that is used to sort table data rows on the basis of their key values. In RDBMS primary key allows us to create a clustered index based on that specific column.
A non-clustered index (or regular b-tree index) is an index where the order of the rows does not match the physical order of the actual data. It is instead ordered by the columns that make up the index.

10) What is a child in PostgreSQL?
The ctid field exists in every PostgreSQL table. It is unique for every record in a table and denotes the tuple location. It can be used to delete records. The thing to remember, we should only use ctid if we have absolutely no other unique identifier to use.
What Is A Ctid?
CTIDs is a field, which exists in every PostgreSQL table and is known to identify specific physical rows according to their block and offset positions within a particular table. They are used by index entries to point to physical rows. It is unique for each record in the table and easily denotes the location of a tuple. A logical row’s CTID changes when it is updated, so the CTID cannot be used as a long-term row identifier. However, it is sometimes useful to identify a row within a transaction when no competing update is expected.

11) What is table partitioning in PostgreSQL?
Table partitioning in PostgreSQL is the process of splitting a large table into smaller pieces. A partitioned table is a logical structure used to divide a large table into smaller pieces called partitions. PostgreSQL supports ranges and lists partitioning through table inheritance. Users must create every partition like a child table in the main table.

12) How to select first 10 records in PostgreSQL?

To get the first 10 records from a database in PostgreSQL, use the LIMIT command. Here is an example of picking the first 10 records from a database.

Example
select * from users order by name desc
LIMIT 0, 10

13) How to change the datatype of a column in PostgreSQL?

To change the records type of a column, you use the ALTER TABLE query as follows:

Example

ALTER TABLE tname
ALTER COLUMN col_name [SET DATA] TYPE new_data_type;

14) What are the indices?
PostgreSQL offers a number of index types: Hash, B-tree, GiST, SP-GiST, BRIN and GIN. Each type of index will use a different algorithm which is most suitable for different types of queries. 
CREATE INDEX will create B-tree indexes by default that match the most popular situations. Users can set their PostgreSQL indexes as well.
15) What are tokens?
PostgreSQL tokens are the building blocks for any source code. They include a lot of special character symbols. A token represents an identifier, a quoted identifier, a keyword, a special character symbol or a literal symbol.
16) What is use of pgadmin in PostgreSQL?
It is a free open source  PostgreSQL database administration tool for Windows, Mac OS X, and Linux system. 
It is used for information retrieval, development, testing, and ongoing maintenance of Databases.
17) What is Cube Root Operator (||/) in PostgreSQL?
PostgreSQL Cube Root Operator (||/) is used to get the cube root of a number.
Example
SELECT ||/40 AS "Cube Root of 40";

18) What does a schema contain? 
A schema contains tables along with data types, views, indexes, operators, sequences, and functions.

19) What are the advantages of PostgreSQL?
Some of the advantages of PostgreSQL are open-source DBMS, community support, ACID compliance, diverse indexing techniques, full-text search, a variety of replication methods, and diversified extension functions, etc.
PostgreSQL can run dynamic websites and web apps as a LAMP stack option.
PostgreSQL's write-ahead logging makes it a highly fault-tolerant database.
PostgreSQL source code is freely available under an open source license. This allows you the freedom to use, modify, and implement it as per your business needs.
PostgreSQL supports geographic objects so it can be used as a geospatial data store for location-based services and geographic information systems.
Low maintenance administration for both embedded and enterprise use.

20) Explain about write-ahead logging.
WAL or write-ahead logging is a standard method to ensure data integrity. It is a protocol or the correct rule to write both actions and changes into a transaction log. 
This feature is known to increase the reliability of the database by logging changes before any changes or updating to the database.
 This provides the log of the database in case of a database crash. This helps to start the work from the point it was discontinued.

21) What do you mean by Full-Text search in PostgreSQL?

This refers to the technique or method of searching for a single or a collection of documents stored on a computer in a full-text database. 
It may be simply distinguished from searches according to metadata or portions of the original texts depicted in the databases. While PostgreSQL is not as advanced as SOLR and Elasticsearch, these two tools are specific to full-text search. While in PostgreSQL, the full-text search is just a feature, and it's quite good.
22) How is security ensured in PostgreSQL?
PostgreSQL uses SSL connections to encrypt client or server communications so that security will be ensured.

 23) What is the disadvantage of the DROP TABLE command in deleting complete data from an existing table?
Though the DROP TABLE command has the ability to delete complete data from an existing table, the disadvantage with it is - it removes complete table structure from the database. Due to this, we need to re-create a table to store data.

24) How can you delete complete data from an existing table?
We can delete complete data from an existing table using the PostgreSQL TRUNCATE TABLE command.

25) What is the operator that is used for case-insensitive regular expression searches in PostgreSQL? 
To match a regular expression that is case insensitive, we can use the POSIX regular expression (~*) from the pattern matching operators.    
Example    'umesh' ~* '.*Umesh.*' 

26) Authentication Methods in PostgreSQL? 
1. Trust Authentication
trust authentication is appropriate and very convenient for local connections on a single-user workstation. It is usually not appropriate by itself on a multiuser machine. 
2. Password Authentication
The password-based authentication methods are md5 and password
if you are at all concerned about password "sniffing" attacks then md5 is preferred. Plain password should always be avoided if possible. However, md5cannot be used with the db_user_namespace feature. If the connection is protected by SSL encryption then password can be used safely (though SSL certificate authentication might be a better choice if one is depending on using SSL).
3. GSSAPI Authentication
GSSAPI is an industry-standard protocol for secure authentication defined in RFC 2743. PostgreSQL supports GSSAPI with Kerberos authentication according to RFC 1964.
4. SSPI Authentication
SSPI is a Windows technology for secure authentication with single sign-on.
5. Kerberos AuthenticationKerberos is an industry-standard secure authentication system suitable for distributed computing over a public network.
6. Ident AuthenticationThe ident authentication method works by obtaining the client's operating system user name from an ident server and using it as the allowed database user name (with an optional user name mapping). This is only supported on TCP/IP connections.
7. Peer Authentication
The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.
8. LDAP Authentication
This authentication method operates similarly to password except that it uses LDAP as the password verification method. LDAP is used only to validate the user name/password pairs. Therefore the user must already exist in the database before LDAP can be used for authentication.
9. RADIUS Authentication
This authentication method operates similarly to password except that it uses RADIUS as the password verification method. RADIUS is used only to validate the user name/password pairs. Therefore the user must already exist in the database before RADIUS can be used for authentication.
10. Certificate Authentication
This authentication method uses SSL client certificates to perform authentication. It is therefore only available for SSL connections. When using this authentication method, the server will require that the client provide a valid certificate. No password prompt will be sent to the client. 
11. PAM Authentication
This authentication method operates similarly to password except that it uses PAM (Pluggable Authentication Modules) as the authentication mechanism. The default PAM service name is postgresql. PAM is used only to validate user name/password pairs. Therefore the user must already exist in the database before PAM can be used for authentication. 

27) Explain Triggers?
PostgreSQL Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs.
The following are important points about PostgreSQL triggers −
PostgreSQL trigger can be specified to fire
Before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE or DELETE is attempted)
After the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed)
Instead of the operation (in the case of inserts, updates or deletes on a view)
A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies.
Syntax:
The basic syntax of creating a trigger is as follows −
CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic goes here....
];
Here, event_name could be INSERT, DELETE, UPDATE, and TRUNCATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name.
The following is the syntax of creating a trigger on an UPDATE operation on one or more specified columns of a table as follows −
CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name[-- Trigger logic goes here....];

28) PostgreSQL Monitoring Tools ?
There are several tool options for monitoring our database. It can be a built-in PostgreSQL tool, like extensions, or some external tool. Let’s see some examples of these tools.
Extensions
Pg_stat_statements: This extension will help we know the query profile of our database. It tracks all the queries that are executed and stores a lot of useful information in a table called pg_stat_statements. By querying this table we can get what queries are run in the system, how many times they have run, and how much time they have consumed, among other information.
Pgbadger: It’s a software that performs an analysis of PostgreSQL logs and displays them in an HTML file. It helps us to understand the behavior of our database and identify which queries need to be optimized.
Pgstattuple: It can generate statistics for tables and indexes, showing how much space used by each table and index, is consumed by live tuples, deleted tuples or how much-unused space is available in each relation.
Pg_buffercache: With this, we can check what's happening in the shared buffer cache in real-time, showing how many pages are currently held in the cache.
29) When should we use PostgreSQL? 
Data integrity is important: PostgreSQL has been fully ACID-compliant since 2001 and implements multiversion currency control to ensure that data remains consistent, making it a strong choice of RDBMS when data integrity is critical.
Integration with other tools: PostgreSQL is compatible with a wide array of programming languages and platforms. This means that if you ever need to migrate your database to another operating system or integrate it with a specific tool, it will likely be easier with a PostgreSQL database than with another DBMS.
Complex operations: Postgres supports query plans that can leverage multiple CPUs in order to answer queries with greater speed. This, coupled with its strong support for multiple concurrent writers, makes it a great choice for complex operations like data warehousing and online transaction processing.

30) What are pg_dump and pg_dumpall?

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).

$ pg_dump mydb > db.sql

“pg_dumpall is a utility for writing out (“dumping”) all PostgreSQL databases of a cluster into one script file  
pg_dump command can take the backup of any one database at a time. What if we want to take the backup of all the databases we have in the PostgreSQL server? There are two options for that.
Run the pg_dump command against each database one by one.
Use another tool provided by the PostgreSQL called pg_dumpall to take the backup of all the databases.

31) Disadvantages of pg_dumpall command?
pg_dumpall command creates the backups of each database one by one in a sequential manner which can be a time-consuming process if your databases are big. That’s why you can’t perform parallel restores of your databases. In the case of pg_dump command, you can start the restore process as soon as one database backup file is generated, allowing you to run the backup and restore process simultaneously.
Taking the backup of all databases together requires more time than taking the backup of a single database so, it can lead to more downtime for your server.