Searching for frequently asked PostgreSQL interview questions? This post will strengthen your PostgreSQL concepts and understanding.
1. What are the main features of PostgreSQL?
Some prominent features of PostgreSQL are:
- Object-relational database.
- Support and extensibility for SQL.
- Flexible API and database validation.
- MVCC and procedural languages.
- WAL and Client-Server.
2. What is the Cube Root Operator (||/) in PostgreSQL?
The cube root operator returns the cube root of a number.
Select ||/ 8 will return 2, i.e., the cube root of 8.
Read More: PostgreSQL vs MySQL
3. Provide a brief explanation of the functions in PostgreSQL.
Functions are important in PostgreSQL because they help in executing the code on the server. You can write functions using PL/pgSQL, a native language of PostgreSQL, and other scripting languages like Perl, Python, PHP, etc. The statistical language “PL/R” can also be used to increase the efficiency of the functions.
4. What is Pgadmin in PostgreSQL?
Pgadmin is a feature used for accessing a graphical front-end administration tool. This feature is available with the free software released under an Artistic License. Pgadmin iii is the latest version release.
5. What is multi-version control?
Multi-version concurrency control or MVCC is used to avoid unnecessary locking of the database. MVCC removes the time lag for the users to log into their database. This time lag occurs when someone else is accessing the content. All of the transactions are kept as a record.
Note: MVCC is one of the most commonly asked PostgreSQL interview questions.
6. What are the new characteristics of PostgreSQL 9.1?
During the process of updating the project, one can never be certain about features that will go in and which ones won’t make the cut. The project has precise and stringent standards for quality and some patches may or may not match them before the set deadline.
Currently, the 9.1 version is working on some important features which include JSON support, synchronous replication, nearest-neighbor geographic searches, collation at the column level, SQL/MED external data connections, security labels as well as index-only access. However, this list has a high chance of being changed by the time Postgre 9.1 is released.
7. What does the enable-debug command do?
The command enable-debug is used to enable the compilation of applications and libraries. The execution of this procedure usually impedes the system, but it also amplifies the binary file size. Debugging symbols that are present generally assist the developers in spotting the bugs and other problems associated with their script that may arise.
Q8. What are indices in PostgreSQL?
These are inbuilt functions or methods which can be used to scan the index in a backward manner. Some examples of indices are GIST Indices, hash table, and B-tree. Users can also define their indices of PostgreSQL.
Read More: Official Website for Training and Downloads.
9. What are the three types of reads that must be prevented while dealing with concurrent transactions?
The SQL standard is defined by four levels of transaction isolation based on three phenomena. These three phenomena must be prevented between concurrent transactions. They are:
- Phantom read: A transaction that re-executes a query, returning a set of rows that satisfy a search condition; only to find that the rows satisfying the condition have changed due to other recently committed transactions.
- Non-repeatable read: A transaction that re-reads the data it has previously read and then finds that data has already been modified by another transaction.
- Dirty reads: A transaction that reads data written by a concurrent uncommitted transaction causes a dirty read.
10. What do you mean by CTID?
CTID is a field that exists in every PostgreSQL table and is known to identify specific physical rows according to their block and offset positions within a particular table. CTID is 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.
What are the best resources for PostgreSQL tutorials? Are you aware of other important PostgreSQL interview questions? Want to share your experience of clearing some PostgreSQL certification? Do comment.
