PostgreSQL Interview Questions and Answers -Interview Questions for Basic Level / Developer level interview Questions with Answers.
PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance. As a database server, its primary functions are to store data securely and return that data in response to requests from other software applications. It can handle workloads ranging from small single-machine applications to large Internet-facing applications (or for data warehousing) with many concurrent users; on macOS Server, PostgreSQL is the default database] and it is also available for Microsoft Windows and Linux (supplied in most distributions).
PostgreSQL is ACID-compliant and transactional. PostgreSQL has updatable views and materialized views, triggers, foreign keys; supports functions and stored procedures, and other expandability.
PostgreSQL was designed to run on UNIX-like platforms. However, PostgreSQL was then also designed to be portable so that it could run on various platforms such as Mac OS X, Solaris, and Windows. PostgreSQL is free and open source software. Its source code is available under PostgreSQL license, a liberal open source license. You are free to use, modify and distribute PostgreSQL in any form. PostgreSQL requires very minimum maintained efforts because of its stability.  Therefore, if you develop applications based on PostgreSQL, the total cost of ownership is low in comparison with other database management systems.
PostgreSQL is highly programmable, and therefore extendible, with custom procedures that are called "stored procedures". These functions can be created to simplify the execution of repeated, complex and often required database operations. Although this DBMS does not have the popularity of MySQL, there are many amazing third-party tools and libraries that are designed to make working with PostgreSQL simple, despite this database's powerful nature. Nowadays it is possible to get PostgreSQL as an application package through many operating-system's default package manager with ease.
PostgreSQL, originally called Postgres, was created at UCB by a computer science professor named Michael Stonebreaker, who went on to become the CTO of Informix Corporation.
What is PostgreSQL?
PostgreSQL is an open source, object-relational database management system (ORDBMS). It is used to store data securely; supporting best practices and allow retrieving them when request is processed.  It supports both SQL (relational) and JSON (non-relational) querying. PostgreSQL (Post-gress-Q-L) is developed by the PostgreSQL Global Development Group (a worldwide team of volunteers). It is not controlled by any corporation or other private entity. It is open source and its source code is available free of charge. It runs on numerous platforms including Linux, most flavors of UNIX, Mac OS X, Solaris, Tru64, and Windows. It supports text, images, sounds, and video, and includes programming interfaces for C / C++, Java, Perl, Python, Ruby, Tcl and Open Database Connectivity (ODBC).

What are the features of PostgreSQL?
  • PostgreSQL runs on all major operating systems i.e. Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows etc.
  • PostgreSQL supports text, images, sounds, and video, and includes programming interfaces for C / C++, Java, Perl, Python, Ruby, Tcl and Open Database Connectivity (ODBC).
  • PostgreSQL supports a lot of features of SQL like Complex SQL queries, SQL Sub-selects, Foreign keys, Trigger, Views, Transactions, Multisession concurrency control (MVCC), Streaming Replication (as of 9.0), Hot Standby (as of 9.0).
  • In PostgreSQL, table can be set to inherit their characteristics from a "parent" table.
  • Nested transactions (save points)
  • Asynchronous replication
  • Native Microsoft Windows Server version
  • Tablespaces
  • Point-in-time recovery
  • You can install several extensions to add additional functionality to PostgreSQL.
Explain when is using PostgreSQL?
  • Data integrity: When reliability and data integrity are an absolute necessity without excuses, PostgreSQL is the best choice.
  • Complex, custom procedures: If you require your database to perform custom procedures, PostgreSQL, being extensible, is the best choice.
  • Integration: In the future, if there is a chance of necessity arising for migrating the entire database system to a propriety (e.g. Oracle) solution, PostgreSQL will be the most compliant and easy to handle base for the switch.
  • Complex designs: Compared to other open-source and free RDBMS implementations, for complex database designs, PostgreSQL offers the most in terms of functionality and possibilities without giving up on other valuable assets.
What is MVCC?
Multi-Version Concurrency Control (MVCC) is an advanced technique for improving database performance in a multi-user environment. This feature or time lag occurs when someone else is on the content. All the transactions are kept as a record.

What is WAL?
In computer science, write-ahead logging (WAL) is a family of techniques for providing atomicity and durability (two of the ACID properties) in database systems. AL (Write-Ahead Logging) is a standard method for ensuring data integrity. A detailed description can be found in most (if not all) books about transaction processing. Briefly, WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage.

What is a CTID?
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

What are indices in PostgreSQL?
There are built in functions such as B-tree, hash table, and GIST indices can be used or users can define their own indices. PostgreSQL can scan the index backwards. An expression index can be created with the addition of a WHERE clause. Partial index created with addition of WHERE clause

What are the database administration tools?
There are various data administration tools they are
  • PSQL
  • SQLyog
  • Navicat for MySQL
  • phpMyAdmin
Most of these tools are front end administration tools and web based interfaces. Out of these phppgadmin is the most popular one.

What is command enable debug in PostgreSQL?
This command is used for enabling compilation of all libraries and applications. This process generally slows down the system and it also increases the binary file size. Debugging symbols are present which can assist developers in noticing bugs and problems associated with their script.

What are the advantages of PostgreSQL?
PostgreSQL offers many advantages for your company or business over other database systems.
  • More profitable business models with wide-scale deployment.
  • No possibility of being audited for license compliance at any stage.
  • Flexibility to do concept research and trial deployments without needing to include additional licensing costs.
  • Better support than the proprietary vendors
  • Significant saving on staffing costs
  • Legendary reliability and stability
  • Extensible
  • Cross platform
  • Designed for high volume environments
  • Immunity to over-deployment
  • GUI database design and administration tools
How to start the database server?
/usr/local/etc/rc.d/ start
/usr/local/etc/rc.d/postgresql start

How to stop the database server?
/usr/local/etc/rc.d/ stop
/usr/local/etc/rc.d/postgresql stop

How to create a database?
/usr/local/bin/createdb mydatabase

How to take backup of database?
/usr/local/bin/pg_dump mydatabase > mydatabase.pgdump

How to create a postgresql user?
Create user user with password ‘password’;

Why do I get the error "error: memory exhausted in allocsetalloc ()"?
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

What are the languages which PostgreSQL supports?
Some of the languages which PostgreSQL supports are as follows: - It supports a language of its own known as PL/pgSQL and it supports internal procedural languages. Pl/pgSQL can be compared to oracle, PL/SQL, etc. Languages such as Perl, Python, TCL can be used as embedded languages.

What is CBRT Function?
PostgreSQL - CBRT
Syntax: CBRT ()
Description: CBRT function returns cube root

What are the key differences between MySQL and PostgreSQL? Which Open Source Database to Choose? Which one is best?
MySQL and PostgreSQL are both free and open source powerful and full-featured databases. You should be able to compare these two databases. Here is the complete article on this.

What are the various enhancements to the straight relational data model by PostgreSQL?
There are various enhancements provided to the straight relational data model by PostgreSQL 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.

Does PostgreSQL run on the cloud?
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?
Performance: For simple read-heavy operations, PostgreSQL can be an over-kill and might appear less performant than the counterparts, such as MySQL.
Popularity: Given the nature of this tool, it lacks behind in terms of popularity, despite the very large amount of deployments - which might affect how easy it might be possible to get support.
Hosting: Due to above mentioned factors, it is harder to come by hosts or service providers that offer managed PostgreSQL instances.
Request to Download PDF


Vestibulum bibendum felis sit amet dolor auctor molestie. In dignissim eget nibh id dapibus. Fusce et suscipit orci. Aliquam sit amet urna lorem. Duis eu imperdiet nunc, non imperdiet libero.

Post A Comment: