PostgreSQL Interview Questions

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.

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

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.

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.

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.

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

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.

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.

We can start the Postgres database server by following the given steps:

• Open Run Window by Winkey + R.
• Type services. msc.
• Search Postgres service based on version installed.
• Click stop, start or restart the service option.

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.

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.

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.

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.

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.

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.

There are four different types of PostgreSQL joins:

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

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.

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.

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.

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.