PostgreSQL Interview Questions
- 1) What will be the new characteristics of Postgre 9.1?
- 2) List different datatypes of Postgresql?
- 3) What are tokens in PostgreSQL?
- 4) List different advantages of Postgresql?
- 5) How are the stats updated in Postgresql?
- 6) What is multi-version control in PostgreSQL?
- 7) What are string constants in PostgreSQL?
- 8) Provide a brief explanation of the functions in Postgresql?
- 9) What is table partitioning in PostgreSQL?
- 10) What is Cube Root Operator (||/) in PostgreSQL?
- 11) How can we change the columns datatype in PostgreSQL?
- 12) What is PostgreSQL?
- 13) Compare ‘PostgreSQL’ with ‘NoSQL’?
- 14) What is difference between clustered index and non clustered index?
- 15) Explain the history of PostgreSQL?
- 16) Which are different types of database administration tools used in Postgresql?
- 17) What is the command enable-debug in PostgreSQL?
- 18) Provide an explanation About Write Ahead Logging?
- 19) What are the Indices of PostgreSQL?
- 20) List some of the features of Postgresql?
- 21) Put some light on Multi-Version concurrency control?
- 22) Do provide an explanation for pgadmin? (100% asked Postgresql Interview Questions)
- 23) What is the option that can be used in PostgreSQL to make transactions see rows affected in previous parts of the transaction?
- 24) What is use of pgadmin in PostgreSQL?
- 25) What Is A Ctid?
Postgresql is a popular Object-Relational Database Management system that is primarily used for large web applications. Every year, there are various openings in this industry at large organisations such as Oracle, SQLite, Amazon Web Services, and others. People that are computer savvy will have no trouble passing the interview.
We’ve compiled a list of some of the most significant and anticipated interview questions. The questions can be understood with a little effort, hard work, and thoroughness. While going over the questions, you may notice several that appear in practically every interview. These well-known questions and responses will ensure that you breeze through the interview without breaking a sweat.
1) What will be the new characteristics of Postgre 9.1?
2) List different datatypes of Postgresql?
There are new, different data-types supported by Postgresql. Following are those data-types:
- Numeric types
- Character types
- Temporal types
- Geometric primitives
- Arbitrary precision numeric
- Arrays etc.
Users can also create their indexes and get them indexed.
3) What are tokens in PostgreSQL?
Tokens in PostgreSQL are the building blocks of any source code. They are known to comprise many of the special character symbols. These can be regarded as constants, quoted identifiers, other identifiers, and keywords. Tokens which are keywords consist of pre-defined SQL commands and meanings. Identifiers are used to represent variable names like columns, tables, etc.
4) List different advantages of Postgresql?
Following are some of the advantages of PostgreSQL :
- Easy to learn
- Open source
- Designed for High Volume Environments
- Cross Platform
- Better Support
5) How are the stats updated in Postgresql?
It is not that hard as it seems. To get your statistics updated in PostgreSQL a special function called explicit ‘vacuum’ call is made. The method to do is to create a Vacuum where the option of Analyze is used to update statistics in Postgresql
is the syntax.
6) What is multi-version control in PostgreSQL?
Multi-version concurrency control or MVCC in PostgreSQL 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 the transactions are kept as a record.
7) What are string constants in PostgreSQL?
A string constant in PostgreSQL is a sequence of some character that is bounded by single quotes (‘).
‘This is a string Constant’
8) Provide a brief explanation of the functions in Postgresql?
9) What is table partitioning in PostgreSQL?
Table partitioning in PostgreSQL is the process of splitting a large table into smaller pieces. A partitioned table is a logical structure used to divide a large table into smaller pieces called partitions.
10) What is Cube Root Operator (||/) in PostgreSQL?
PostgreSQL Cube Root Operator (||/) is used to get the cube root of a number.
SELECT ||/40 AS "Cube Root of 40";
11) How can we change the columns datatype in PostgreSQL?
Use change column type statement with ALTER TABLE command to change a column type in PostgreSQL.
ALTER TABLE table_name ALTER COLUMN column_name [SET DATA] TYPE new_data_type;
12) What is PostgreSQL?
Postgres or simply known as Postgresql in the SQL world is one of the widely and popularly used for Object-Relational Database Management System that is used mainly in large web applications. It is one of the open-source object-relational database systems which also powerful. It provides additional and substantial power by incorporating four basic concepts in such a way that the user can extend the system without any problem. It extends and uses the SQL language that is combined with various features to safely scale and store the intricate data workloads.
13) Compare ‘PostgreSQL’ with ‘NoSQL’?
The expression ‘NoSQL’ encompasses a wide collection of implementations which are part of the non-relational database. This includes tiny embedded databases such as TokyoCabinet, massive bunched data processing platforms such as Hadoop and everything in between. In short, it’s practically impossible to comment on the range comprised by NoSQL as a typical class.
Choosing between the non-relational and relational databases is also quite commonly debated as both have existed alongside each other for over forty years. In fact, users should opt for the features, community support and implementation of the database according to their current application needs. Additionally, use of multiple various databases for sizeable projects is becoming more of a norm than a trend. Moreover, the users of PostgreSQL are no exception.
14) What is difference between clustered index and non clustered index?
Difference between clustered index and non clustered index
- Cluster index is an index type that is used to sort table data rows on the basis of their key values. In RDBMS primary key allows us to create a clustered index based on that specific column.
- A non-clustered index (or regular b-tree index) is an index where the order of the rows does not match the physical order of the actual data. It is instead ordered by the columns that make up the index.
15) Explain the history of PostgreSQL.
The origin of PostgreSQL dates back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform. It runs on all the major operating systems and has been ACID-compliant since 2001. It also has add-on like PostGIS database extender. In MAC OS Postgresql is the default database. Michel Stonebraker is Father of Postgresql who has started the Post Ingres project for supporting Contemporary Database systems.PostgreSQL’s developers pronounce PostgreSQL as It is abbreviated as Postgres because of ubiquitous support for the SQL Standard among most relational databases.PostgreSQL, originally called Postgres, was created at UCB by a computer science professor named Michael Stonebraker, who went on to become the CTO of Informix Corporation.
Stonebraker started Postgres in 1986 as a followup project to its predecessor, Ingres, now owned by Computer Associates. The name Postgres thus plays off of its predecessor (as in “after Ingres”). Ingres, developed from 1977 to 1985, had been an exercise in creating a database system according to classic RDBMS theory. Postgres, developed in 1986-1994, was a project meant to break new ground in database concepts such as exploration of “object-relational” technologies. An enterprise-class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write-ahead logging for fault tolerance.
16) Which are different types of database administration tools used in Postgresql?
There are the number of data administration tools, and they are
Out of these, phppgadmin is the most popular one. Most of these tools are front-end administration tools and web-based interfaces.
17) What is the command enable-debug in PostgreSQL?
18) Provide an explanation About Write Ahead Logging?
19) What are the Indices of PostgreSQL?
Indices of PostgreSQL are inbuilt functions or methods like GIST Indices, hash table and B-tree (Binary tree) which can be used by the user to scan the index in a backward manner. Users can also define their indices of PostgreSQL.
20) List some of the features of Postgresql?
Following are some of the major features of Postgresql :
- Object-relational database
- Supports major Operating systems
- Support Extensibility for SQL and Complex SQL queries
- Nested transactions
- Flexible API and Database validation
- Multi-version concurrency control (MVCC) and Procedural languages
- WAL and Client server
- Table inheritance & Asynchronous replication
21) Put some light on Multi-Version concurrency control?
22) Do provide an explanation for pgadmin? (100% asked Postgresql Interview Questions)
23) What is the option that can be used in PostgreSQL to make transactions see rows affected in previous parts of the transaction?
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 phenomena are:
- 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 another recently-committed transaction.
- Non-repeatable read: A transaction that re-reads the data that it has previously read and then finds that data has already been modified by another transaction (that committed since the initial read).
- Dirty read : A transaction when reads data that is written by a concurrent uncommitted transaction is the dirty read.
24) What is use of pgadmin in PostgreSQL?
It is a free open source GUI tool PostgreSQL database administration tool for Windows, Mac OS X, and Linux system. It is used for information retrieval, development, testing, and ongoing maintenance of Databases.
25) What Is A Ctid?
26) How to start database server in PostgreSQL?
Before you can have access to the database, you must be able to start the database server. The server program of the database is called Postgres. The Postgres program must know where to find the data it is supposed to use. This is done with the -D option. Thus, the simplest way to start the server is:
- /usr/local/etc/rc.d/010.pgsql.sh start
- /usr/local/etc/rc.d/PostgreSQL start
Conclusion: PostgreSQL, sometimes known as Postgres, is a relational database management system (RDBMS) that focuses on flexibility and SQL conformance. It was initially known as POSTGRES, after the Ingres database built at the University of California, Berkeley. The project was renamed PostgreSQL in 1996 to reflect its SQL support. The development team opted to preserve the name PostgreSQL and the alias Postgres after a review in 2007.Transactions with Atomicity, Consistency, Isolation, and Durability (ACID) qualities, automatically updatable views, materialised views, triggers, foreign keys, and stored procedures are all available in PostgreSQL. It can manage a wide range of workloads, from single computers to data warehouses or Web services with a large number of concurrent users. It is also available for Windows, Linux, FreeBSD, and OpenBSD, and is the default database for macOS Server.
Multiversion concurrency control (MVCC) in PostgreSQL regulates concurrency by giving each transaction a “snapshot” of the database, allowing modifications to be made without affecting other transactions. This obviates the requirement for read locks and assures that the database follows ACID guidelines. Read Committed, Repeatable Read, and Serializable are the three levels of transaction isolation provided by PostgreSQL. Requesting a Read Uncommitted transaction isolation level instead offers read committed because PostgreSQL is immune to dirty reads. The serializable snapshot isolation (SSI) mechanism in PostgreSQL allows for full serializability.