PostgreSQL Interview Questions

1) What is PostgreSQL?

Ans: PostgreSQL is a popular Object-relational database management system. It is an open-source platform that is used to create advanced applications. Familiarity with Linux and UNIX can be an additional benefit when running PostgreSQL.

       Get ahead in your career by learning PostgreSQL course through HKRTrainings PostgreSQL Online Training!

2) List some PostgreSQL features.

Ans: PostgreSQL offers a number of helpful features. Some of them are as follows:

• Data integrity protection helps users create a fault-tolerant environment.
• Easily compatible with important platforms, middleware and languages.
• It supports Multi-version concurrency control.
• It supports customer-server network architecture.
• Log-based and Trigger-based replication SSL.
• Highly available and standby server

3) List various data types in PostgreSQL.

Ans: PostgreSQL supports various data types. Some of them are:

• UUID
• Numeric types 
• Temporal types 
• Geometric primitives 
• Arbitrary precision numeric
• XML 
• Boolean
• Character types
• Array
• JSON

4) List some advantages and disadvantages of PostgreSQL.

Ans: Advantages of PostgreSQL:

• Reliable
• Flexible
• Stable
• Easy to learn
• Extensible
• Cross-Platform
• Open-source
• Better Support
• Designed for High Volume Environments

Disadvantages of PostgreSQL:

• It is slower compared to MySQL in terms of performance.
• It does not have support for many open source applications in comparison with MySQL.
• With a greater focus on compatibility, modifications to enhance speed require additional work.

5) What are string Constants?

Ans: The string constant consists of a sequence of characters tied by single quotes('). It is used when inserting a character or passing a character to database objects. PostgreSQL enables the use of single quotes but is integrated by a C-style backslash. It is used in data parsing. 

Example: 'It is an example for a string constant'.

6) What is Multi-Version Control?

Ans: Multi-Version Concurrency Control in PostgreSQL is used to prevent unnecessary database locking. It eliminates the delay time for the user logging into their database. It occurs when some other people are accessing the content. All these transactions are captured.

7) What are the indices?

Ans: PostgreSQL offers a number of index types: Hash, B-tree, GiST, SP-GiST, BRIN and GIN. Each type of index will use a different algorithm which is most suitable for different types of queries. CREATE INDEX will create B-tree indexes by default that match the most popular situations. Users can set their PostgreSQL indexes as well.

8) What are tokens?

Ans: PostgreSQL tokens are the building blocks for any source code. They include a lot of special character symbols. A token represents an identifier, a quoted identifier, a keyword, a special character symbol or a literal symbol.

9) What is table partitioning?

Ans: In PostgreSQL, table partitioning is a process in which a large table is divided into smaller pieces called partitions. PostgreSQL supports ranges and lists partitioning through table inheritance. Users must create every partition like a child table in the main table.

10) How can we start and stop a database server on PostgreSQL? And how can we check whether PostgreSQL is up and running?

Ans: Prior to accessing the database, you should be able to start the database server. The database server program is referred to as Postgres. The Postgres program needs to know where to look for the data it should use. This is accomplished using the -D option. So the easiest way of starting the server is:

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

We use the following way to stop the server:

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

We can verify if the postgresql server is up and running by:

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

11) How is pgadmin used in PostgreSQL?

Ans: Pgadmin is a well-known feature for forming a front-end graphic administration tool. This functionality is available as free software with an artistic license. The latest database administration tool available with an artistic license is Pgadmin iii. It is used to retrieve information, develop, test and continuous maintenance of databases.

PostgreSQL Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

12) Compare PostgreSQL with MySQL.

Ans: MySQL is the "user-friendly, web developer" database, while PostgreSQL is the "feature-rich and standards-compliant" database. PostgreSQL is freely licensed and part of its community; MySQL is GPL licensed and belongs to Oracle. Beyond this, every user of the database should make their own assessment; free software facilitates comparisons.

13) How can we change the column data type in PostgreSQL?

Ans: Using the ALTER TABLE command, we can change a column type.

For example: 

ALTER TABLE name_of_the_table

ALTER COLUMN name_of_the_column [SET DATA] TYPE new_data_type;

14) How are statistics updated within Postgresql?

Ans: In PostgreSQL, To update your statistics, a special function known as an explicit "vacuum" call is done. The way to do this is to create a Vacuum where the Analyze option is used to update the stats in Postgresql as shown:

VACUUM ANALYZE; 

15) What is the enable-debug command in PostgreSQL?

Ans: The enable-debug command allows you to compile all apps and libraries. Executing this process usually prevents the system, but it also increases the size of the binary file. The debug symbols that are present usually help developers identify bugs and other issues that may occur related to their script.

16) Which option can be used in PostgreSQL to view the affected rows in the earlier parts of the transaction?

Ans: The SQL standard is defined by four levels of isolating transactions concerning essentially three phenomena. The three phenomena should be avoided between concurrent transactions. Unwanted phenomena include:

Non-repeatable read: A transaction that will read the data which is already read and then discovers that the data has already been altered by another transaction.

Phantom read: A transaction that will execute a query again by returning a set of rows that meets the search condition and finds the set of rows which satisfied the requirements that have changed as a result of another recently initiated transaction.

Dirty read: A transaction that reads the data which is written by a simultaneous uncommitted transaction is called the dirty read.

17) Briefly describe the functions of Postgresql.

Ans: Functions are an important part of running the code on the server everywhere. Some languages used for programming functions are PL/pgSQL, a native PostgreSQL language, as well as other scripting languages such as Perl, PHP, Python, etc. A statistical language called PL/R can also be used to make functions more efficient.

18) What are the different improvements in the relational data model through PostgreSQL?

Ans: A number of improvements have been made to the simple relationship data model through PostgreSQL. They support arrays that include various values, functions, inheritance, and extensibility. Jargon is different in its object-oriented nature in which tables are known as classes.

19) What kind of database administration tools do you use in Postgresql?

Ans: We have a variety of data administration tools. They are Pgadmin, Psql, Phppgadmin. Phppgadmin is the most popular among them. Most of these tools are web-based interfaces and front-end administration tools.

20) What is Ctid?

21) Explain about write-ahead logging.

Ans: Write-ahead logging is a standard way of ensuring data integrity. It is a correct rule or protocol for writing actions and modifications in a transaction log. This feature enhances the reliability of the database by saving modifications before any modification or update of the database. It provides the database log if the database crashes. It allows work to begin from the moment it was interrupted.

22) When should PostgreSQL be avoided?

Ans: At the cost of speed, PostgreSQL was built to be expandable and compatible. PostgreSQL may not suit you if your project needs the fastest read operations possible.

Due to its wide range of features and its high adherence to the SQL standard, Postgres may be excessive for simple database configurations. MySQL is the practical choice for operations like read-heavy where we need speed. 

While PostgreSQL provides solid support for replication, it is always a relatively new functionality, and certain configurations like primary architecture are only possible with expansions. Replication is an advanced feature of MySQL. Most users feel that replicating MySQL is easier to implement, especially for those without the required experience in administering databases and systems.

23) What do you mean by Full-Text search in PostgreSQL?

Ans: This refers to the technique or method of searching for a single or a collection of documents stored on a computer in a full-text database. It may be simply distinguished from searches according to metadata or portions of the original texts depicted in the databases. While PostgreSQL is not as advanced as SOLR and Elasticsearch, these two tools are specific to full-text search. While in PostgreSQL, the full-text search is just a feature, and it's quite good.

24) What are BRIN indices?

Ans: Once the BRIN index has been installed, PostgreSQL will be able to select the minimum and maximum values by reading the selected column for each 8k page of data saved. After that, PostgreSQL will store the page number, the maximum and minimum values of the selected column in the BRIN indexes.

25) What is a Parallel Query?

Ans: A parallel query is a PostgreSQL feature in which it can design query plans that can leverage a number of CPUs to respond more quickly to queries.

26) What are GiST indexes in PostgreSQL?

27) What is the Atomicity property's function in PostgreSQL?

Ans: The Atomicity property guarantees the success of all operations within one work unit.

28) Does Postgresql be executed on Cloud?

Ans: Yes. Like the other open source databases, PostgreSQL can be easily executed in virtual containers and is very portable. Many companies like GoGrid, Heroku and Joyent have PostgreSQL support within Cloud hosting environments.

29) How can we perform queries through multiple databases?

Ans: There is no better way to query a database other than the present one. As PostgreSQL loads database-specific system catalogs, we don't know how a cross-database query should behave. contrib/dblink enables cross-database queries with function calls. Of course, a customer can also establish concurrent connections to various databases and merge client-side results.

Subscribe to our youtube channel to get new updates..!

Subscribe

30) Can we create a shared storage PostgreSQL server cluster?

Ans: PostgreSQL will not support clustering by using shared storage on SCSI, SAN, iSCSI volume, or other shared media. Such "RAC-style" clustering is not supported. Only the replicated clustering is supported at present.

31. Describe the various advantages of PostgreSQL.

Ans. The major benefits of the PostgreSQL database include an open-source database management system, diverse indexing techniques, full-text search flexibility, community support, different types of duplication methods, highly expandable functions, and many more.

32. What is meant by the Partitioned table in PostgreSQL?

Ans. In PostgreSQL, the partitioned table is a logical structure and a special table divided into parts. It allows the splitting of large size tables into smaller ones, known as partitions.

33. Define the purpose of pgAdmin in the PostgreSQL server.

Ans. In PostgreSQL, pgAdmin is a data administration tool that helps to manage and observe many PostgreSQL and EDB advanced server database servers. It is useful for recovering, testing, managing, and developing databases.

 If you have any doubts on PostgreSQL, then get them clarified from PostgreSQL Industry experts on our PostgreSQL Community

34. Name the process of dividing a large table into smaller sizes in PostgreSQL.

Ans. The process is known as “table partitioning”.

35. How to avoid needless database locking in PostgreSQL?

Ans. To avoid the needless or unnecessary locking of a database, we can use MVCC (Multi-version concurrency control).

36. What is meant by PL/Python?

Ans. It’s a procedural language to which PostgreSQL offers support and it allows us to write PostgreSQL functions in the Python language.

37. Define the process of deleting a database in PostgreSQL.

Ans. We can remove the database by using the below commands:-

• We can use an SQL command- DROP DATABASE
• Also, we can use”dropdb” which is a feasible command-line 

38. What we can find in the Schema?

Ans. A schema in PostgreSQL includes the following- views, indexes, operators, sequences, data types, tables, and functions.

39. Define the methods that PostgreSQL provides to build a new database.

Ans. To build a new database, PostgreSQL offers the below approaches:-

• Using an SQL command - CREATE DATABASE to create a database 
• Also, we can use ‘created’ which is an executable command-line 

40. Name the different types of operators in PostgreSQL.

Ans. Arithmetic operators, Logical operators, Comparison operators, Bitwise operators

41. What is another name for database callback functions? What is their purpose in PostgreSQL?

Ans. The PostgreSQL Triggers are also known as database callback functions. When a defined database event takes place, the PostgreSQL Triggers are automatically executed or cited.

42. For what do we use indexes in PostgreSQL?

Ans. To speed up data recovery in PostgreSQL, the search engine uses indexes.

43. Define the use of the Cluster Index.

Ans. The Cluster index classifies the table data rows on the basis of their key values.  Here, the order of the data rows within data pages communicates with the data rows' order in the index.

44. Define the benefits of specifying data types in columns when you create a table.

Ans. The advantages of specifying data types in columns include consistency, conciseness, validation/confirmation, and performance.

45. Explain how to update statistics in PostgreSQL.

Ans. We have to use a special function “vacuum” in order to update statistics in the PostgreSQL database.

46. Define the method to remove complete data from an existing table.

Ans. We can completely remove data from the current table through the PostgreSQL TRUNCATE TABLE command.

47. Name the various properties of a transaction in PostgreSQL and their Acronym.

Ans. The transaction properties in PostgreSQL consist of Atomicity, Consistency, Isolation, and Durability and they are collectively called ACID properties. 

48. What is the use of the CTIDs field?

Ans. The CTIDs field helps to locate certain physical rows within a table as per their block and balances the positions in that table.

49. Name the commands to control transactions in PostgreSQL.

Ans. The commands for Transaction Control in PostgreSQL include the following- BEGIN, COMMIT, TRANSACTION, and ROLLBACK.

50. Distinguish between SQL and PostgreSQL?

Ans. PostgreSQL is an extension or advanced version of SQL (Structured Query Language).  The following differences you can see between these two:-

• We cannot update the views in the PostgreSQL database like in SQL.
• In PostgreSQL, there is no need to create a DLL  (dynamic link library) to see the code’s activity like in the SQL database.
• Moreover, SQL offers computed columns but PostgreSQL doesn’t offer any.
• SQL doesn’t support dynamic actions but PostgreSQL supports them. 

51. Define the security measures in Postgre

Ans. PostgreSQL database uses SSL connections that help to encrypt client or server reports to ensure security services.

52. Explain the function of the Atomicity property in the PostgreSQL database.

Ans. This property in PostgreSQL makes sure that all the operations in a work unit are successfully completed.

53. Define Base Directory in PostgreSQL.

Ans. In PostgreSQL, the base directory (data_dir/base) is a folder in which all the user-inserted data into the database is stored. It also includes sub-directories that a database uses within the cluster.

54. Define non-clustered Index in PostgreSQL.

Ans. A non-clustered index is an index where the row order doesn’t match the order within the actual data.

55. What is meant by Reserved Words in PostgreSQL?

Ans. The reserved words in PostgreSQL are the SQL keywords which include some symbols with special meanings when the Relational Engine starts processing them. These reserved keywords are considered actual and cannot be used as identifiers. Also, we cannot use them as databases, columns, variables, tables, etc.

56. What do you mean by Tablespace in PostgreSQL?

Ans. The tablespace in PostgreSQL is the directory or location on the helpful disk to store various data files that include indices and tables. These are made up of data files built to store various databases.

57. Define Sequence in PostgreSQL?

Ans. In PostgreSQL, a Sequence is a schema object to produce a unique number of identifiers within the database. It helps to build unique identifiers between different rows within a table. Further, producing an artificial primary key within PostgreSQL is generally helpful.

58. What is an Inverted File in PostgreSQL?

Ans. An inverted file in PostgreSQL is generally an index data structure helpful for mapping content to its destination to a database in the sets or within a document.

59. What are the drawbacks of using the DROP TABLE command in PostgreSQL?

Ans. The command “DROP TABLE” helps remove all the data from the existing table. But the major drawback of using this command is that it completely deletes the table from the database. So, it would be best if you rebuilt the table to save all the data.

60. Define Aggregate Function in PostgreSQL.

Ans. An Aggregate function in PostgreSQL calculates over different input rows and returns a single value. In SQL, there are different types of aggregate functions available such as avg, count, min(), sum(), max(), etc.

61. What tool is helpful for PostgreSQL?

Ans. Ora2Pg is a free-to-use tool helpful in migrating Oracle data to the PostgreSQL-compatible schema. It connects with the Oracle database and automatically scans it to extract data. Then it produces the SQL scripts, which you can quickly load into the database of PostgreSQL.

62. What is the way to store Binary Data in PostgreSQL?

Ans. There are two ways to store binary data in PostgreSQL: bytes or extensive object features.

63. Why is PostgreSQL most useful for Data engineering?

Ans. SQL is the crucial language that helps to succeed in any data-oriented task, including data engineering. There are some reasons why PostgreSQL is the most helpful platform for data engineering. These reasons include- complete SQL syntax support, extended data structure support like JSON, running parallel queries, etc.

64. Define Blob Storage in PostgreSQL.

Ans. SQL is the crucial language that helps to succeed in any data-oriented task, including data engineering. There are some reasons why PostgreSQL is the most helpful platform for data engineering. These reasons include- complete SQL syntax support, extended data structure support like JSON, running parallel queries, etc. 

65. Which tools are used for PostgreSQL?

Ans: pgAdmin is the most useful GUI tool for PostgreSQL, which helps to communicate with PostgreSQL. Also, it is an open-source administration and development platform. Further, there are multiple tools for PostgreSQL, such as DBeaver, Navicat, OmniDB, TablePlus, etc.

66. Which index is best in PostgreSQL?

Ans: PostgreSQL uses the B-tree as a default index that helps scan ranges, values searches, etc., and it is compatible with multiple data types.

67. How many columns can PostgreSQL have?

Ans: In PostgreSQL, the maximum number of columns per table is 1600 based on the column type.

69. Who should learn PostgreSQL?

Ans: Anyone with basic knowledge of database management and building databases can start learning PostgreSQL. Also, anyone working with extensive data and information in the online space can learn about this database.

70. How many threads in PostgreSQL?

Ans: Postgresql is not thread-based, and it doesn’t use threads.