Adaface PostgreSQL ready-to-go tests are designed by experts to evaluate on-the-job skills

Customize Adaface tests to assess multiple skills in a single assessment

🧐 Test 🔧 Sample Topics 💪 Useful for hiring
SQL Online Test
Relational Keys
Select Queries
Joins
Data Modelling
Fact and Dimension Table Types
Star Schema
Snow Flake Schema
SQL Developer
Data Analyst
Data Scientist
DevOps Engineer
Database Administrator
SQL Online Test
SQL Data Types and Literals
SQL Operators
Views
Indexes
Nulls
SQL Functions
SQL Tables
Queries
Commands and clause
PL/SQL
Junior SQL Developer
SQL Programmer
SQL Coder
SQL Database Administrator
Data Analyst Excel Test
MS Excel Advance
Numerical Reasoning
Data Analyst
DevOps AWS Test
Configuring Amazon EC2 instances
Load Balancer
Forklifting an existing Application onto AWS
Event-driven scaling
Hosting a new web application on AWS
Core AWS knowledge
Troubleshoot
AWS CloudFormation stack definition
Amazon Elastic Compute Cloud (EC2)
AWS CloudFormation
DevOps AWS Engineer
PostgreSQL Online Test
PostgreSQL Database Administrator
PostgreSQL Database Developer
MySQL Online Test
Joins
Queries
Grouping
Aggregate Functions
HAVING Clause
Functions
Keys
Constraints
Indexes
MySQL Developer
Data Analyst
Database Administrator
Database Administrator
SQL Developer
Java Online Test
Core Java
Data Types
Threads
Servlets
OOPs
classes
Core Java Developer
Core Java Programmer
Backend Developer
Web Developer
JavaScript Online Test
Control Flow
Error handling
Data Models
Asynchronity
DOM Manipulation
Node
Functional Approach
Object Oriented Approach
Prototype
Class
Scope
JavaScript Developer
JavaScript Programmer
Backend Developer
Frontend Developer
NodeJS Developer
React Developer
Angular Developer
PHP Online Test
Basic PHP
Operators
Syntax
built-in functions
Loops
Variables and operators
Object-Oriented Programming (OOPS)
passing by reference
globals and scope
Security
Session
Functional Programming Arrays
Core PHP Developer
Core PHP Programmer
Backend Developer
Spring Online Test
Spring Applications
Transactions in Spring
Spring Bean and Methods
Spring Configuration
Spring Testing Support
Spring Bean Life Cycle
Spring Test Module
Pointcut
Spring AOP Framework
Spring MVC Controller
Java Developer with Spring (0-3 years experience)
Software Engineer - Spring Framework
Hibernate Online Test
ORM (Object Relational Mapping)
Stored Procedure Queries
Hibernate Strategies
Sessions in Hibernate
Hierarchy in Hibernate
Hibernate Developer
Java Developer - Hibernate
Amazon Web Services (AWS) Online Test
Directory Manipulation
Requests
Objects
Unit Tests
Authentication
Encryption
Backup Files
Data Sets
Events
Amazon Aurora
Amazon Developer
AWS Cloud Architect
AWS Cloud Engineer
Senior AWS Cloud Engineer
MS SQL Test (Advance)
Transact SQL Statement
Stored Procedure
SQL Server 2008
Truncate Statements
triggers
SQL Server Developer
MS SQL Server developer
Node.js Online Test
Javascript engine
packages
asynchronous programming
bug fixing
hands-on coding
Node.JS Developer
Node JS Engineer
Backend Engineer Test
REST APIs
Web Fundamentals
Coding
Databases
Data Structres
Algorithms
Backend Engineer
Software Engineer
Full Stack Engineer
Software Engineer Test
Data Structres
Algorithms
Coding
Databases
Optimization
Software Engineer
Software Developer
Full Stack Engineer
Senior Backend Engineer Test
REST APIs
Web Fundamentals
Coding
Databases
Data Structres
Algorithms
Senior Backend Engineer
Senior Software Engineer
Senior Full Stack Engineer

Check out sample Adaface MCQ questions on PostgreSQL topics

These are just a small sample from our premium questions library (10273 non-googleable questions).

Are you a candidate? Complete list of PostgreSQL interview questions 👇

Index

General

  1. What are the features of PostgreSQL?
  2. What is Multi Version Concurrency Control in PostgreSQL?
  3. What is a child in PostgreSQL?
  4. What are indexes in PostgreSQL?
  5. Provide a brief explanation of the functions in PostgreSQL?
  6. Can you explain pgadmin?
  7. What is Multi-version control?
  8. What is the option in PostgreSQL to check rows that are affected in a previous part of the transaction?
  9. What are the advanced features/functionalities of Postgres DB ?
  10. What is write ahead logging?
  11. How do we perform queries using Multiple Databases?
  12. Is it possible to create a shared storage PostgreSQL server cluster?
  13. Does Postgresql have SPs/ Stored Procedures?
  14. Explain Triggers.
  15. What are the various enhancements to the straight relational data model by PostgreSQL?
  16. Explain about string constants?
  17. How to take backup of database?
  18. Name data types which are used in PostgreSQL.
  19. What is TRUNCATE used for?
  20. What is DELETE statement in PostgreSQL?
  21. What is maximum size of table in postgresql?
  22. How are stats updated in Postgresql?
  23. What is a CTID?
  24. Why do I get the error -error: memory exhausted in allocsetalloc ()?
  25. Does PostgreSQL run on the cloud?
  26. What are the disadvantages of PostgreSQL?
  27. What are the advantages of PostGRESQL?
  28. When should we use PostgreSQL?
  29. When should we avoid using PostgreSQL?
  30. What is a sequence in PostgreSQL?
  31. What are Connector Libraries?

Advanced

  1. What is Full Text Search? Is it is supported by Postgresql?
  2. What are BRIN Indexes?
  3. What is Parallel Query in PostgreSQL. How does it work?
  4. What are GiST Indexes in PostgreSQL?


The Questions
General
1. What are the features of PostgreSQL?

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
2. What is Multi Version Concurrency Control in PostgreSQL?

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.

3. What is a child in PostgreSQL?

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.

4. What are indexes in PostgreSQL?

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.

5. Provide a brief explanation of the functions in PostgreSQL?

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.

6. Can you explain pgadmin?

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.

7. What is Multi-version control?

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.

8. What is the option in PostgreSQL to check rows that are affected in a previous part 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 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.
9. What are the advanced features/functionalities of Postgres DB ?

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.
10. What is write ahead logging?

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.

11. How do we perform queries using Multiple Databases?

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.

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

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.

13. Does Postgresql have SPs/ Stored Procedures?

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.

14. Explain Triggers.

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.

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

16. Explain about string constants?

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.

17. How to take backup of database?

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

18. Name data types which are used in PostgreSQL.

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)
19. What is TRUNCATE used for?

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

20. What is DELETE statement in PostgreSQL?

DELETE statement is used to delete rows from the table.

21. What is maximum size of table in postgresql?

Postgresql has maximum size of table is 32TB.

22. How are stats updated in Postgresql?

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.

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

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

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

26. What are the disadvantages of PostgreSQL?
  • 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.
27. What are the advantages of PostGRESQL?
  • 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
28. When should we use PostgreSQL?
  • 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.
29. When should we avoid using PostgreSQL?
  • 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.
30. What is a sequence in PostgreSQL?

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.

31. What are Connector Libraries?

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.

Advanced
1. What is Full Text Search? Is it is supported by Postgresql?

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.

2. What are BRIN Indexes?

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.

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

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.

4. What are GiST Indexes in PostgreSQL?

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.