Postgresql Interview Questions

Postgresql Interview Questions | Freshers & Experienced

  • Arushi
  • 27th Jan, 2020
  • 372 Followers

PostgreSQL Interview Questions

Q1. What is PostgreSQL?

PostgreSQL is an open-source database system that uses SQL language and extends its functionality to safely scale the complicated data loads. Many features such as data integrity, reliability, architecture, etc. made it to constantly give innovative solutions to complicated problems. It runs on major operating systems and has strong add-ons such as PostGIS geospatial database extender.

PostgreSQL is very stable; therefore, it requires minimum efforts to maintain it. Whenever you develop an application with PostgreSQL, you will face less cost in comparison with other database management systems.

Q2. What is default port no of PostgreSQL?

The default port no. Of PostgreSQL is 5432 which can be easily changed.

Q3. Enlist major advantages of Postgresql?

The major advantages are:

  • Open Source DBMS: PostgreSQL provides enterprise-class functions in Open Source DBMS with many possibilities of development. Along with that, PostgreSQL has a bigger community which means any user can post and share.
  • Functionality: PostgreSQL supports SQL functions such as stored procedures that are used for a server environment.
  • Diverse Community: PostgreSQL has a wide variety of communities to supervise its users. The development is eminently high because developers propose the modules to the community for the use of other users.
  • ACID properties: One of the important benefits of using PostgreSQL is the support of ACID properties. ACID refers to Atomicity, Consistency, Isolation, and Durability.
  • Different indexing techniques: PostgreSQL provides various techniques such as B+ tree, GIN (Generalized Inverted Index), and GIST (Generalized Search Tree) and many more.
  • Replication methods: PostgreSQL offers many replication methods like Slony-I, Streaming replication and cascading.
  • Full-Text Search: Searching for strings is easy in PostgreSQL as it provides a full-text search operation.

Q4. What are the Indices in PostgreSQL?

Indices are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indices also add overhead to the database system as a whole, so they should be used sensibly.

Q5. List different datatypes available in Postgresql?

The different data types available in PostgreSQL are:

  • Boolean: True or False.
  • Character types: Such as char, varchar, and text.
  • Numeric types: For example, integer and floating-point numbers.
  • Temporal types: For example, date, time, timestamp, and interval.
  • UUID: It is utilized to store a Universally Unique Identifiers
  • Array: It is utilized to store array strings, numbers, etc.
  • JSON: It is used to stores JSON data.
  • hstore: It is used to stores the key-value pairs.
  • Special types: It comprises of geometric data and network address.

Q6. What is Difference between clustered index and non clustered index in Postgresql?

The Difference between the clustered index and non-clustered index in PostgreSQL .

Clustered Index Non clustered Index
ONLY 1 Clustered Index is occupied by a Table. A table may or may not have any Non-clustered Indexes.
It always has an Index Id of 0. In the Non-clustered Indexes, they have Index Id > 0.
A Primary Key constraint is used to build a Clustered Index. A Unique Key constraint is used to build a Non-clustered Index.
Clustered Index can enforce the Unique Key constraint. Non-clustered Index can enforce the Primary Key constraint.
It is faster to read in comparison with the non-clustered. It is quicker to add and update operations in comparison with the clustered index.
Clustered Index’s leaf nodes comprise of data pages of the table on which it is built. Non-clustered Index’s leaf nodes comprise of Index pages which covers Clustering Key or RID to locate Data Row.

Q7. What is multi-version control?

MVCC (Multi-version Concurrency Control) is an approach to control the uniformity of data accessed by several users at the same time. To ensure that each and every transaction always sees a uniform snapshot of data, MVCC implements the snapshot isolation guarantee.

Q8. What Is A Ctid?

A Ctid is used to locate the physical location of the row version within its table. Although the ctid helps to locate the row version fastly, it is not useful for a long-term row identifier. This is because the ctid of a row is changed every time it is updated or moved by the VACUUM FULL. There are more and better ways to locate the row version like OID and user-defined serial number.

Q9. How do we start Postgres database server?

Q10. What are Triggers in Postgres?

A set of actions that take place automatically on changing a specified operation (SQL INSERT, UPDATE, DELETE or TRUNCATE statement) on a specified table is called Triggers.

It is very useful for tasks like validating input data, enforcing business rules, keeping an audit trail and validating input data.

Q11. What is write ahead logging in Postgresql?

Write-Ahead Logging (WAL) is an approach for ensuring data integrity. In general, whenever any transaction executes, any single change that a transaction made is written to persistent storage including row deletion, addition or modification. If let’s suppose, transaction completed before writing all changes to the disk and suddenly the power went out, then the data would be lost. After that when PostgreSQL would do back up, it will show a status of the transaction completed but there will be no record of changes made by the transaction. Thus, PostgreSQL will suffer data loss.

So, WAL is used to prevent data loss and minimizing the disk I/O. The main concept of the WAL is that only those changes will be made to disk files that have been added to the WAL log. In short, the WAL log is a file that keeps records of all the changes that have been made and is stored on disk.

Since WAL has a record of changes, so whenever PostgreSQL does a backup, it first checks WAL records. If there are any changes that have been not propagated to disk, then it first performs those changes. This is how WAL reduces the need for I/O operations without suffering the loss of data.

Q12. How to you find the installed version of Postgresql?

We can find the installed version of PostgreSQL.

  • Run the SHOW server_version command on a query pane in PgAdmin or other admin tools.
  • After successful execution, the output shows the PostgreSQL’s version number.

Q13. What is a sequence in PostgreSQL?

The sequence is defined as a user-defined schema-bound object that helps in generating a sequence of integers. You can use the “CREATE SEQUENCE” statement to build a sequence in PostgreSQL.

Q14. What are Connector Libraries?

It is a collection of connectors that can be downloaded and customized within any ProcessMaker instance. The ProcessMaker manage and continuously update the connector public library.

Q15. What are Different types of string constants available in Postgresql?

The Different types of string constants available in PostgreSQL.

  • String Constants: These are the random sequence of characters in SQL and bordered by the single quotes (''), for example, 'This is an example'. In case of addition of a single-quote character within a string constant, bordered the two adjacent single quotes. For example, 'David''s a cat'.
  • String Constants with C-style Escapes: PostgreSQL also supports the "escape" string constants, which are however an extension to the SQL standard. The letter E is used to designate the escape string constant prior to the opening single quote.
  • string Constants with Unicode Escapes: PostgreSQL also supports escape syntax for strings that permits the specifying random Unicode characters by code point. The U& is used to designate the Unicode escape string constant and it is added prior to the opening quote, with the absence of any spaces in between them.

Q16. What are different types of table joins supported by Postgresql?

There are four different types of PostgreSQL joins:

  • PostgreSQL INNER JOIN
  • PostgreSQL LEFT OUTER JOIN
  • PostgreSQL RIGHT OUTER JOIN
  • PostgreSQL FULL OUTER JOIN

Q17. What is Full Text Search? Is it is supported by Postgresql?

It is the method or technique for the search of a single or collection of computer-stored documents in a full-text database. It can be easily differentiated from searches based on the metadata or on parts of the original texts represented in databases.

Although PostgreSQL is not as advanced as compared to the Elasticsearch and SOLR, both of these are specific for full-text search tools. Whereas in PostgreSQL, full-text search is only a feature and it is a pretty good one.

Q18. What are triggers in PostgreSQL?

Q19. What are GiST Indexes in PostgreSQL?

The GiST is an extendable data structure that helps the users to develop indices over any kind of data. It also supports any lookup over that data.

The GiST is able to do this by inserting an API to Postgres's index system.

Q20. What are BRIN Indexes?

After you install the BRIN index, PostgreSQL is able to select the maximum and minimum value by reading your selected column for each 8k page of stored data. After that, PostgreSQL stores the page number, the minimum value, and the maximum value for your chosen column in BRIN Indexes.

Q21. What is Parallel Query in PostgreSQL. How it Works?

A Parallel query is a feature in PostgreSQL in which it can devise query plans which can exploit multiple CPUs to answer the queries faster.

About Author :

  • Author of Postgresql Interview Questions

    Arushi Pandey

    Arushi Pandey is senior PHP developer and have hand on experience on PHP Frameworks and tools.He loves to share and write programming content on internet.

Leave A Comment :

Valid name is required.

Valid name is required.

Valid email id is required.