PostgreSQL Interview Questions

Prepare better with the best interview questions and answers, and walk away with top interview tips. These interview questions and answers will boost your core interview skills and help you perform better. Be smarter with every interview.

  • 4.8 Rating
  • 40 Question(s)
  • 30 Mins of Read
  • 9654 Reader(s)

Beginner

PostgreSQL is the one of the best successful open source database and will be  used to create advanced applications. Postgres relies on the Object relational database management system. Familiarity with Unix and Linux can be an added advantage while using PostgreSQL.

The major functionalities/ features of PostgreSQL are

1) Object relational database
2) Extensibility and support for SQL
3) DB validation and flexible Application Program Interface.
4) Procedural languages and Multi Version Concurrency Control.
5) Client server and WAL.

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.

To start Postgres Database server, we need to move to the location where pgsql script (ideally it’s a shell script) and the location of postgresql and start the services respectively as below.

We can add them to the auto startup scripts using chkconfig such that whenever server gets restarted postgres will be started.

  • /usr/local/etc/rc.d/010.pgsql.sh start
  • /usr/local/etc/rc.d/postgresql start

To stop the Postgres Database server, we need to move to the location where pgsql script (ideally it’s a shell script) and the location of postgresql and stop the services respectively as below.

Alternatively, we can add them to the auto startup scripts using chkconfig such that whenever server gets restarted postgres server will be restarted and thus we can avoid manual intervention.

  • /usr/local/etc/rc.d/010.pgsql.sh stop
  • /usr/local/etc/rc.d/postgresql stop

To check the status of the Postgres Database server, we need to switch to the location where pgsql script (ideally it’s a shell script) and the location of postgresql and run the following commands to check  the services respectively.

  • /usr/local/etc/rc.d/010.pgsql.sh status
  • /usr/local/etc/rc.d/postgresql status?

Also, we can check the services running on the server as below

[root hostname$]#  ps -ef | egrep “pg | postgres”

To enable debug in Postgres, we’ll have to enable the below shared library as shown below.

# Edit the entry to add the library ‘plugin_debugger’:

shared_preload_libraries = ‘plugin_debugger’

Restart PostgreSQL
sudo service postgresql stop
sudo service postgresql start

To enable this extension in PostgreSQL

The following is done at each DB level, not instance level. So log into the DB and run following SQL:

CREATE EXTENSION pldbgapi;

The above mentioned command is used for enabling the 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.

Functions in PostgreSQL are important because they help the code to be executed on the server.

PostgreSQL provides a large number of functions and operators for the built-in data types. Users can also define their own functions and operators. The psql commands \df and \do can be used to list all available functions and operators, respectively.

If you are concerned about portability then note that most of the functions and operators with the exception of the most trivial arithmetic and comparison operators and some explicitly marked functions, are not specified by the SQL standard. Some of this extended functionality is present in other SQL database management systems, and in many cases this functionality is compatible and consistent between the various implementations.

Some of the languages which can program functions for efficient use are PL/pgSQL which is the native language of PostgreSQL. Scripting languages are

supported by many languages such as PHP, Perl, Python, etc. PL/R a statistical language can also be used.

Postgres has built in functions such as hash-table, B-tree, & GIST indices can be used or users can define their own indices. PostgreSQL can scan the index both forward and backwards as well.

Expression index can be created with the result of an expression. Partial index can be created with addition of WHERE clause.

PostgreSQL Triggers are database callback functions, which are automatically performed or invoked when a specified database event occurs.

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

Blow are few important points about PostgreSQL triggers −

Postgres trigger can be specified to fire.

Before the operation is attempted on a row (before constraints are checked and the INSERT,DELETE or  UPDATE is attempted)

After the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed)

Instead of the operation (in the case of inserts, updates or deletes on a view)

A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies.

Both, the WHEN clause and the trigger actions, may access elements of the row being inserted, deleted or updated using references of the form NEW.column_name and OLD.column_name, where column-name is the name of a column from the table that the trigger is associated with.

If a WHEN clause is supplied, the Postgres statements specified are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the Postgrea statements are executed for all rows in a table.

The basic syntax of creating a trigger is as below −

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
-- Trigger logic goes here....

There are different data types which are supported by PostgreSQL and they are:

Special types such as network address and geometric data.
Temporal types such as date, time, timestamp, and interval
UUID for storing Universally Unique Identifiers
Array for storing array strings, numbers, etc.
JSON stores JSON data
Boolean (True/False)
Character types such as char, varchar, and text.
Numeric types such as integer and floating-point number.
hstore stores key-value pair

There are variety of data administration tools that are supported by Postgres and the popular are

1) Psql
2) Pgadmin
3) Phppgadmin

Most of these tools are front end administration tools and web based interfaces. Out of these phppgadmin is the most popular one.

Similar to the ever-popular phpMyAdmin, it’s enhanced for PostgreSQL, supports browsing and modification of most types of PostgreSQL database objects, also execution of ad-hoc queries.

Creating a database is the primary step in creating a database

 $createdb newdatabasedb

CREATE DATABASE

To create a database, you must be a superuser/admin or have the special CREATEDB privilege.

By default, the new database will be created by cloning the standard system database template1. A different template can be specified by writing TEMPLATE name. In particular, by writing TEMPLATE template0, you can create a virgin database containing only the standard objects predefined by your version of PostgreSQL.

This is useful if you wish to avoid copying any installation-local objects that might have been added to template1.

CREATE DATABASE name
   [ [ WITH ] [ OWNER [=] user_name ]
          [ TEMPLATE [=] template ]
          [ ENCODING [=] encoding ]
          [ LC_COLLATE [=] lc_collate ]
          [ LC_CTYPE [=] lc_ctype ]
          [ TABLESPACE [=] tablespace ]
          [ CONNECTION LIMIT [=] connlimit ] ]

This creates a new database and a message displays CREATE DATABASE which indicates that the creation of the database was successful.

To create a database in Postgres, we should use the following command.

/usr/local/bin/createdb <database_name>
/usr/local/bin/createdb zeolearndb

To list out all the databases in Postgres, below are the commands we should execute.

su -l pgsql
psql -l

Below is the user creation syntax

CREATE USER <user_name> WITH password ‘<password_here>’ ;

E.g., create user zeolearnusr with password ‘Z@0learn’;

Use CURRENT_TIMESTAMP:

CREATE TABLE tbl_name (a int, modtime TIMESTAMP DEFAULT

CURRENT_TIMESTAMP );

PostgreSQL supports outer joins using the SQL standard syntax.

Below are two examples:

SELECT * FROM tbl1 LEFT OUTER JOIN tbl2 ON (tbl1.col = tbl2.col);

/
SELECT * FROM tbl1 LEFT OUTER JOIN tbl2 USING (col);

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.

Postgres already have it. 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.

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.

Yes. Postgres is Cloud compatible like all other open source databases.

PostgreSQL is very easy to run in virtual containers and is highly portable too.
May companies have support for PostgreSQL in cloud hosting environments like

Heroku,
GoGrid and
Joyent.

And moreover Postgres (compatible with AWS Aurora) is generally avaible at AWS as per

https://aws.amazon.com/blogs/aws/now-available-amazon-aurora-with-postgresql-compatibility/

“NoSQL” Databases (Not Only SQL) which are varied range of non-relational databases from small embedded databases to massive clustered (HA/Sarding) data processing platforms like Hadood &  MongoDB and thus it’s absurd to compare and remark on them as a general class RDBMS Databases.

Non relational databases preceded RDBMS (relational databases_ and have existed alongside them for 40 years, so choosing between relational and non relational databases is nothing strange.

Users should choose the database based on the following aspects

Features,
Functionalities,
Scalability
Reliability,
HA capabilities implementation (Replication)  &
Community support their current application needs.

Further, using multiple different databases for huge projects is becoming the standard, and PostgreSQL has no exception as well.

Multi-Version Concurrency Control (MVCC) is an advanced technique for improving database performance in a multi-user environment.

Multi version concurrency control is used to manage concurrency. This feature is very useful because changes made in the database will not be visible to other users until the transaction is completed.

This removes the need for read locks. ACID principles are given a further boost by this feature and can be implemented in general.

Unlike most other database systems which use locks for concurrency control, Postgres maintains data consistency by using a multiversion model. This means that while querying a database each transaction sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.

This protects the transaction from viewing inconsistent data that could be caused by (other) concurrent transaction updates on the same data rows, providing transaction isolation for each database session.

The main difference between multiversion and lock models is that in MVCC locks acquired for querying (reading) data don't conflict with locks acquired for writing data and so reading never blocks writing and writing never blocks reading.

Postgres 11, which was released October 18, 2018. This is thier 29th major release in 30 years of development.
They’ will release a new version of PostgreSQL every year, which is unique among SQL databases.

Advanced

MVCC is one of the good feature in Postgres. Multi version concurrency control or MVCC is used to avoid unnecessary locking of the database. This avoids the time lag for the user to log into the database. This functionality or time lag occurs when someone else is on the content.

All the transactions are kept as a record.

PostgreSQL supports a language of its own known as Procedurel Language/pgSQL and it supports internal procedural languages.  Three language extensions are included with PostgreSQL to support Perl, Python  and Tcl.

There are external projects to add support for many other languages,  including Java,JavaScript (PL/V8), R, Ruby, and others.

pgAdmin is (Admin tool) is the Open Source management tool for your PostgreSQL databases. Features full Unicode support, fast, multithreaded query and data editing tools and support for all PostgreSQL object types.

pgAdmin III is bundled with the Windows installer, and you can use that such a client to administer a remote server on another OS. Note that binary packages for platforms like RPM don't show up in every point release, you currently have to go back to v1.8.0 to get the last full set of packages.

Free Administration Centre for the PostgreSQL database. Includes a graphical administration interface, an SQL query tool, a procedural code editor and much more. pgAdmin is designed to answer the needs of most users, from writing simple SQL queries to developing complex databases. The graphical interface supports all PostgreSQL features and makes administration easy. Available in more than 30 languages and for several operating systems.

Supported OS ‘s : MS Windows, GNU/Linux, FreeBSD, Mac OS X, OpenBSD, Solaris

Pgadmin forms 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.

There are various variety of enhancements provided to the straight relational data model by postgreSQL and there are support for arrays which includes multiple values, inheritance, functions and extensibility.

Terminology differs because of its object oriented nature where tables are called as classes.

Also there are Major new features, such as

Synchronous replication and other replication features
Per-column collations
Unlogged Tables
Extensions
K-Nearest-Neighbor Indexing
Serializable Snapshot Isolation
Writeable Common Table Expressions
SE-Postgres
PGXN
SQL/MED

Tokens are also known to consists several special character symbols.

It can be considered as keyword, constant, identifier and quoted identifier.

Keywords include predefined SQL meanings and SQL commands. Variable names such as columns,  tables etc are represented by identifiers.

Text-search templates provide the functions underlying dictionaries. (A dictionary simply specifies a template and a set of parameters for the template.)

Text-search configurations select a parser and a set of dictionaries to use to normalise the tokens produced by the parser.

Text-search parsers break documents into tokens and classify each token (for example, as words or numbers).

Text-search dictionaries convert tokens to normalised form and reject stop words.

String constant contains a sequence of characters bounded by single quotes. This feature/functionality 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.

A string constant in SQL is an arbitrary sequence of characters bounded by single quotes ('), for e.g., 'This is a string'.

To include a single-quote character within a string constant, write two adjacent single quotes, e.g., 'Zia''s horse'. Note that this is not the same as a double-quote character (").

Two string constants that are only separated by whitespace with at least one newline are concatenated and effectively treated as if the string had been written as one constant. For example:

SELECT 'foo'

'bar';

is equivalent to:

SELECT 'foobar';

but:

SELECT 'foo'      'bar';

is not valid syntax.

write-ahead log (WAL),  means it always writes the transactions to the log following with writing the modified pages to the disk to maintain the transaction ACID properties.

Write-Ahead Logging (WAL) is a standard method for ensuring data integrity.. 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. If we follow this procedure, we don’t need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the log records. (This is roll-forward recovery or REDO.)

To take a backup of Postgres database, we’ll have use pg_dump binary utility as below.

/usr/local/bin/pg_dump <database_name>  > database_name.pgdump

E.g., /usr/local/bin/pg_dump zeolearn_db >  zeolearn_db.pgdump

A sequence is a special kind of database object designed for generating unique numeric identifiers.

It is typically used to generate artificial primary keys.

Sequences are similar, but not identical, to the AUTO_INCREMENT concept in MySQL.

CTIDs identify specific physical rows by their block and offset positions within a table.

The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change each time it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

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.

We may probably have to run out of virtual memory on your system, or our kernel has a low limit for certain resources.

We need to try this before starting the server:

ulimit -d 262144
limit datasize 256m

PostgreSQL's developers pronounce PostgreSQL as /ˈpoʊstɡrɛs ˌkjuː ˈɛl/. And post-GRES-que-ell, as per this audio file.

It is abbreviated as Postgres because of ubiquitous support for the SQL standard among most relational databases.

Most of the people, however, just say “post-GRES”.

The name (Post Ingres) refers to the projects’ origins in the database which was developed at University of California.

The community considered changing the name back to Postgres;

However, the PostgreSQL Core Team announced in the 2007 that the product would continue to use the name PostgreSQL (Post + In’gre’s + SQL).

Postgres 9.1 expecting to have the following new features.

Synchronous replication,
JSON support,
Security labels,
Nearest-neighbor geographic searches,
SQL/MED external data connections,
Column-level collations and
Index-only access.

When 9.1 is released, though, this feature list might vary considerably.

To make it as a crisp answer, MySQL is the “easy-to-use, web developer” database, and PostgreSQL is the “feature-rich, standards-compliant” database.

PostgreSQL is liberally licensed and owned by its community; MySQL is GPL-licensed and owned by Oracle. Beyond that, each database user should make his own evaluation; open source software makes doing comparisons very easy.


MYSQL

POSTGRESQL
Basic
MySQL is a relational database management system.
PostgreSQL is an object-relational database management system.

Product

MySQL is the product of Oracle Corporation.
PostgreSQL is the product of Global Development Group.

Operating System

MySQL is supported by Windows, Mac OS X, Linux, BSD, UNIX, z/OS, Symbian, AmigaOS.
Postgre is supported by Windows, Mac OS X, Linux and BSD but not by UNIX, z/OS, Symbian, AmigaOS.

Data Domain Object

MySQL does not provide the Data Domain Object.
PostgreSQL provides the Data Domain Object.

Extensible
MySQL is not extensible.
PostgreSQL is highly extensible.
Interface
In MySQL, the phpMyAdmin tool provides GUI.
In PostgreSQL, the pgAdmin tool provides GUI.
Materialized View
MySQL provides temporary table, but not provide materialized View.
PostgreSQL provides temporary table as well as materialized View.
Backup
Mysqldump, and XtraBackup provides backup in MySQL.
PostgresSQL provides online backup.

The major differences are

  1. My SQL programming language is not extensible whereas, the programming language PostgreSQL is highly extensible.
  2. In MySQL, the phpMyAdmin tool provides GUI and SQL interface. However, in PostgreSQL, the pgAdmin tool provides GUI and SQL interface.
  3. In MySQL, Mysqldump, and XtraBackup tools provides backup. On the other hands, PostgresSQL provides complete backup online.
  4. MySQL provides temporary tables but does not provide materialized view. However, PostgreSQL provides temporary table and also the materialized view.
  5. MySQL does not offers data domain object whereas, PostgreSQL provide data domain object.
  6. The architectural difference between MySQL and PostgreSQL is that MySQL is a relational database management system whereas, PostgresSQL is object-relational database management system
  7. MySQL is supported by the following operating system, Windows, Mac OS X, Linux, BSD, UNIX, z/OS, Symbian, AmigaOS. However, the PostgreSQL  is supported by Windows, Mac OS X, Linux and BSD but not by UNIX, z/OS, Symbian, AmigaOS
  8. MySQL is the product of Oracle Corporation while PostgreSQL is a product of Global Development Group.

Postgres already have it. 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.

Description

Prepare better with the best interview questions and answers, and walk away with top interview tips. These interview questions and answers will boost your core interview skills and help you perform better. Be smarter with every interview.
Levels