PostgreSQL interview questions
Postgres is a widely used system for Object-Relational Database Management that is mainly used for large web applications. Every year there are some vacancies, in big companies like Oracle, SQLite, Amazon web services and such others, to fill up in this field. People with technical expertise in the computer can easily crack the interview.
Here, we have listed few important and expected questions that may come up in your interview. With a little bit of practice and hard work and thoroughness, the questions can be understood. Going through the questions, you may find some questions that come up almost in every interview.
These predictable questions and answers will help you get through the interview without breaking a sweat. So, here are the questions:
1. List some of the features of Postgresql ?
- Object-relational database
- Support and Extensibility for SQL
- flexible API and Database validation
- MVCC and Procedural languages
- WAL and Client server
2.What is the option that can be used in PostgreSQL to make transactions see rows affected in previous parts of the transaction?
- 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.
3. Put some light on Multi-Version concurrency control?
4. 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 Vacuum where the option of Analyze is used to update statistics in Postgresql
is the syntax.
5. Provide a brief explanation of the functions in Postgresql.
6. List different datatypes of Postgresql?
- Geometric primitives
- Arbitrary precision numeric
- Arrays etc.
Users can also create their indexes and get them indexed.
7. 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.
8. Do provide an explanation for pgadmin? (100% asked Postgresql Interview Questions)
9. What is PostgreSQL? Explain the history of PostgreSQL.
Postgres or simply known as Postgresql in 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 system which also powerful. It provides additional and substantial power by incorporating four basic concepts in such 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.
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 default database. Michel Stonebraker is Father of Postgresql who has started 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.
10. List different advantages of Postgresql? (A must ask the question)
- Easy to learn
- Open source
- Designed for High Volume Environments
- Cross Platform
- Better Support
11. What is multi-version control?
12. What are string constants in PostgreSQL?
13. What Is A Ctid?
14. Provide an explanation About Write Ahead Logging?
15. What is the command enable-debug?
16. What are the Indices of PostgreSQL?
17. What are tokens?
18. 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
19. What will be the new characteristics of Postgre 9.1?
20. 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.
We have added some very expected questions and appropriate answers to them. These are not mandatory ones but they have high chances of being asked in the interview. You can go through these questions to understand the pattern and way of answering the questions properly. With a better approach and right way of answering questions can help you excel in the interview. All the best!