PostgreSQL interview questions and answers 👇

  1. General
  2. PostgreSQL Advanced Interview Questions


What are the features of PostgreSQL?

View answer

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

What is Multi Version Concurrency Control in PostgreSQL?

View answer

Multi-Version Concurrency Control (MVCC) is an advanced method used in PostgreSQL for improving the performance of a database in a multi-user environment. 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.

What is a child in PostgreSQL?

View answer

The ctid field exists in every PostgreSQL table. It is unique for every record in a table and denotes the turple 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 are indexes in PostgreSQL?

View answer

In PostgreSQL, the index is a common way for database performance enhancement. It allows the database server to find the retrieve specific rows faster compared to without index. It also adds overhead to the database system as a whole, so users have to implement them sensibly.

Indexes are special lookup tables that are used by the database search engine to speed up data retrieval. Simply defining, an index is a pointer to a specific data in a table.

Provide a brief explanation of the functions in PostgreSQL?

View answer

Functions are an important part because they help in executing the code on the server. Some of the languages to program functions are PL/pgSQL, a native language of PostgreSQL, and other scripting languages like Perl, Python, PHP etc. statistical language named PL/R can also be used to increase the efficiency of the functions.

Can you explain pgadmin?

View answer

Pgadmin is a feature that is known to form a graphical front-end administration tool. This feature is available under free software released under Artistic License. Pgadmin iii is the new database administration tool released under artistic license.

What is Multi-version control?

View answer

Multi-version concurrency control or MVCC is used to avoid unnecessary locking of the database. This removes the time lag for the user to log into his database. This feature or time lag occurs when someone else is accessing the content. All transactions are kept as a record.

What is the option in PostgreSQL to check rows that are affected in a previous part of the transaction?

View answer

The SQL standard is defined by four levels of transaction isolation basically regarding three phenomena. The three phenomenon must be prevented between concurrent transactions. The unwanted phenomenon is

  • Phantom read: 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 that have been satisfying the condition has changed due to other recently committed transaction.
  • Non-repeatable read: a transaction that re-reads the data that it has previously read then finds that data has already been modified by another transaction.
  • Dirty reads: a transaction when reads data that is written by a concurrent uncommitted transaction is the dirty read.

What are the advanced features/functionalities of Postgres DB ?

View answer

The major functionalities/ features of PostgreSQL are

  • Object relational database
  • Extensibility and support for SQL
  • DB validation and flexible Application Program Interface.
  • Procedural languages and Multi Version Concurrency Control.
  • Client server and WAL.

What is write ahead logging?

View answer

Postgres’ write ahead logging increases the reliability (in a way resiliency) of the db by logging changes before any changes or updates to the database. This provides log of database in case of a db crash and this also helps to start the work from the point it was discontinued. This it is useful for crash recovery.

How do we perform queries using Multiple Databases?

View answer

There is no way to query a db other than the existing database. Because PostgreSQL loads database specific system catalogs, it is uncertain how a cross-database query should even behave for Postgres.

However, contrib or dblink allows cross-database queries using function calls. Nevertheless, a client can also make simultaneous connections to different databases and merge the results on the client side.

Is it possible to create a shared storage PostgreSQL server cluster?

View answer

Since no one type of clustering satisfies all needs, they have several different clustering tools which take various clustering approaches. The open source projects PostgresXC and Postgres-XL are available, as well as open source forks and proprietary tools such as Greenplum Database, Citus Data and several others.

Also, PostgreSQL is supported by filesystem-based clustering systems for failover, including ones from Red Hat, Microsoft, Veritas and Oracle.

Does Postgresql have SPs/ Stored Procedures?

View answer

PostgreSQL doesn't have Stored Procedures directly.

However, by working with Object Relational Database Management Systems i.e., PostgreSQL have not necessarily the function Stored Procedure (SP) defined, as we can find in most of other types of DBMS.

They are in fact, small sets of code stored at server side of a database.

On the contrary, to what happens in other databases, the Stored Procedures in PostgreSQL are defined as FUNCTIONS, as well as triggers, making this feature a bit more complicated, depending on its return type. These functions are important and variant in features, but created equal. Working with the creation of these small snippets of code is a good practice because it can leave quite complicated code acting on the server-side that can be used by multiple apps, thus avoiding the need to replicate them in each of these apps.

Explain Triggers.

View answer

With an SQL query you can trigger an event. Triggers can be activated with the help of INSERT and UPDATE queries. These can be attached to tables. Triggers more than one can be triggered alphabetically. These triggers have the capability to invoke functions from other languages.

What are the various enhancements to the straight relational data model by PostgreSQL?

View answer

There are various enhancements provided to the straight relational data model by postgre SQl they are support for arrays which includes multiple values, inheritance, functions and extensibility. Jargon differs because of its object oriented nature where tables are called as classes.

Explain about string constants?

View answer

String constant contains a sequence of characters bound by single quotes. This feature is used during insertion of a character or passing character to database objects. PostgreSQL allows the usage of single quotes but embedded by a C style backslash. This feature is important in parsing data.

How to take backup of database?

View answer

/usr/local/bin/pg_dump mydatabase > mydatabase.pgdump

Name data types which are used in PostgreSQL.

View answer

PostgreSQL supports the following data types:

  • Booloean
  • Character (char, varchar, text)
  • Numeric (Integer, Floating-point)
  • Temporal (date, time, timestamp, interval)
  • Array (array string, number)
  • JSON
  • hstore (key-value pair)

What is TRUNCATE used for?

View answer

TRUNCATE TABLE statement is used to remove all data quickly and efficiently from the table.

What is DELETE statement in PostgreSQL?

View answer

DELETE statement is used to delete rows from the table.

What is maximum size of table in postgresql?

View answer

Postgresql has maximum size of table is 32TB.

How are stats updated in Postgresql?

View answer

To update statistics in PostgreSQL explicit ‘vacuum’ call is made. Hope you know the method of doing this. If not let us know and we would help you.Vacuum with the option Analyze is used to update statistics in Postgresql VACUUM ANALYZE ; is the syntax.

What is a CTID?

View answer

CTIDs identify specific physical rows by their block and offset positions within a table. They are used by index entries to point to physical rows. A logical row’s CTID changes when it is updated, so the CTID cannot be used as a long-term row identifier. But it is sometimes useful to identify a row within a transaction when no competing update is expected.

Why do I get the error -error: memory exhausted in allocsetalloc ()?

View answer

You probably have run out of virtual memory on your system, or your kernel has a low limit for certain resources. Try this before starting the server:

Ulimit -D 262144 Limit Datasize 256m

Does PostgreSQL run on the cloud?

View answer

Yes. Like other open source databases, PostgreSQL is easy to run in virtual containers and is highly portable. Several companies have support for PostgreSQL in cloud hosting environments, including Heroku, GoGrid and Joyent.

What are the disadvantages of PostgreSQL?

View answer

  • Postgres is not owned by one organization. So, it has had trouble getting its name out there despite being fully featured and comparable to other DBMS systems
  • Changes made for speed improvement requires more work than MySQL as PostgreSQL focuses on compatibility
  • Many open source apps support MySQL, but may not support PostgreSQL
  • On performance metrics, it is slower than MySQL.

What are the advantages of PostGRESQL?

View answer

  • 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 you can use it for location-based services and geographic information systems
  • PostgreSQL supports geographic objects so it can be used as a geospatial data store for location-based services and geographic information systems
  • To learn Postgres, you don't need much training as its easy to use
  • Low maintenance administration for both embedded and enterprise use

When should we use PostgreSQL?

View answer

  • 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.

When should we avoid using PostgreSQL?

View answer

  • Speed is imperative: At the expense of speed, PostgreSQL was designed with extensibility and compatibility in mind. If your project requires the fastest read operations possible, PostgreSQL may not be the best choice of DBMS.
  • Simple setups: Because of its large feature set and strong adherence to standard SQL, Postgres can be overkill for simple database setups. For read-heavy operations where speed is required, MySQL is typically a more practical choice.
  • Complex replication: Although PostgreSQL does provide strong support for replication, it’s still a relatively new feature and some configurations — like a primary-primary architecture — are only possible with extensions. Replication is a more mature feature on MySQL and many users see MySQL’s replication to be easier to implement, particularly for those who lack the requisite database and system administration experience.

What is a sequence in PostgreSQL?

View answer

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.

What are Connector Libraries?

View answer

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.

PostgreSQL Advanced Interview Questions

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

View answer

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.

What are BRIN Indexes?

View answer

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.

What is Parallel Query in PostgreSQL. How does it work?

View answer

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.

What are GiST Indexes in PostgreSQL?

View answer

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.