PostgreSQL is simply known as Postgres is an object-relational database management system (ORDBMS). It is developed by PostgreSQL Global Development Group initially in the year 1996 but its stabled release was in the year 2018. It emphasizes mainly two things, extensibility and standards compliance. It is used to handle a lump sum amount of workloads which ranges from small single-machine applications to gigantic Internet-facing applications with a huge number of concurrent users, working parallel on macOS, Linux, and windows. Have a look at PostgreSQL interview questions and answers:
1. What Is Postgresql?
This is regarded as one of the most successful open-source databases in the world. This is also used to create advanced applications. This relies on an Object-relational database management system. Familiarity with UNIX and Linux can be an added advantage while using PostgreSQL.
2. What are the features of PostgreSQL?
PostgreSQL has many advanced features that other enterprise-class database management systems offer, such as:
- User-defined types
- Table inheritance
- Sophisticated locking mechanism
- Foreign key referential integrity
- Views, rules, subquery
- Nested transactions (savepoints)
- Multi-version concurrency control (MVCC)
- Asynchronous replication
The recent versions of PostgreSQL support the following features:
- Native Microsoft Windows Server version
- Tablespaces
- Point-in-time recovery
3. Who uses PostgreSQL
Many companies have built products and solutions based on PostgreSQL. Some featured companies are Apple, Fujitsu, Red Hat, Cisco, Juniper Network, Instagram, etc.
4. What is Multi-Version Concurrency 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 on the content. All the transactions are kept as a record.
5. What is the process of splitting a large table into smaller pieces called in PostgreSQL?
It is called table partitioning.
6. What is a partitioned table in PostgreSQL?
The partitioned table is a logical structure. It is used to split a large table into smaller pieces, which are called partitions.
7. What purpose does pgAdmin have in the PostgreSQL server?
The pgAdmin in PostgreSQL is a data administration tool. It serves the purpose of retrieving, developing, testing, and maintaining databases.
8. How can you avoid unnecessary locking of a database?
We can use MVCC (Multi-version concurrency control) to avoid unnecessary locking of a database.
9. What is PL/Python?
PL/Python is a procedural language to which PostgreSQL provides support.
10. Which are the methods PostgreSQL provides to create a new database?
PostgreSQL provides the following methods to create a new database:
- Using CREATE DATABASE, an SQL command
- Using created a command-line executable
11. How do you delete the database in PostgreSQL?
We can delete the database by using any one of the below options:
- Using DROP DATABASE, an SQL command
- Using dropdb a command-line executable
12. What does a schema contain?
A schema contains tables along with data types, views, indexes, operators, sequences, and functions.
13. What are the different operators in PostgreSQL?
The PostgreSQL operators include – Arithmetic operators, Comparison operators, Logical operators, and Bitwise operators.
14. What are database callback functions called? What is its purpose?
The database callback functions are called PostgreSQL Triggers. When a specified database event occurs, the PostgreSQL Triggers are performed or invoked automatically.
15. What indexes are used?
Indexes are used by the search engine to speed up data retrieval.
16. What does a Cluster index do?
The clustered index sorts table data rows based on their key values.
17. What are the benefits of specifying data types in columns while creating a table?
Some of these benefits include consistency, compactness, validation, and performance.
18. What do you need to do to update statistics in PostgreSQL?
To update statistics in PostgreSQL, we need to use a special function called a vacuum.
19. What is the disadvantage of the DROP TABLE command in deleting complete data from an existing table?
Though the DROP TABLE command has the ability to delete complete data from an existing table, the disadvantage with it is – it removes the complete table structure from the database. Due to this, we need to re-create a table to store data.
20. How can you delete complete data from an existing table?
We can delete complete data from an existing table using the PostgreSQL TRUNCATE TABLE command.
21. What are the different properties of a transaction in PostgreSQL? Which acronym is used to refer to them?
The properties of a transaction in PostgreSQL include Atomicity, Consistency, Isolation, and Durability. These are referred to by the acronym, namely ACID.
22. What purpose does the CTIDs field serve?
The CTIDs field identifies the specific physical rows in a table according to their block and offsets positions in that table.
23. Which are the commands used to control transactions in PostgreSQL?
The commands used to control transactions in PostgreSQL are BEGIN TRANSACTION, COMMIT, and ROLLBACK.
24. What are the main differences between SQL and PostgreSQL?
PostgreSQL is an advanced version of SQL. Some of the differences between these two include the following:
- Unlike SQL, views in PostgreSQL are not updatable.
- Another difference is whereas SQL provides computed columns; the same cannot be expected from PostgreSQL.
- Unlike SQL, in PostgreSQL, you don’t need to create a DLL to see the code what it is doing.
- PostgreSQL supports dynamic actions whereas SQL doesn’t support them.
25. How is security ensured in PostgreSQL?
PostgreSQL uses SSL connections to encrypt client or server communications so that security will be ensured.
26. What is the function of the Atomicity property in PostgreSQL?
Atomicity property ensures the successful completion of all the operations in a work unit.
27. What are the advantages of PostgreSQL?
Some of the advantages of PostgreSQL are open-source DBMS, community support, ACID compliance, diverse indexing techniques, full-text search, a variety of replication methods, diversified extension functions, etc.
28. What does Write-Ahead Logging do?
The Write-Ahead Logging enhances database reliability by logging changes before any changes or updates are made to the database.
29. What is pgAdmin and how do you set up pgAdmin?
PgAdmin is a free, open-source PostgreSQL database administration GUI that is used in Microsoft Windows, Mac OS X, and Linux systems. PgAdmin is used for database server information retrieval, development process, Quality testing, and other ongoing maintenance.
Follow these steps to install PgAdmin:
- Launch pgAdmin 4.
- Go to the “Dashboard” tab, click on the “Quick Link” section and then click on “Add new Server”
- Now, select the “Connection” tab in the “Create-Server” window.
- Now, configure the connection as follows:
- Enter your server’s IP address in the “Hostname/Address” field.
- Specify the “Port” as “5432”.
30. What is the Maximum size for a database in PostgreSQL?
PostgreSQL has no maximum database size, so users can put unlimited data into it. But, the table, row, and field size are limited. The row and indexes are also universal for its users.
PostgreSQL usually stores its desk facts in chunks of 8KB. The quantity of these blocks is confined to a 32-bit signed integer, giving the most desk dimension of 16TB.
Practical Questions
31. How To Start The Database Server?
/usr/local/etc/rc.d/010.pgsql.sh start /usr/local/etc/rc.d/postgresql start
32. How To Stop The Database Server?
/usr/local/etc/rc.d/010.pgsql.sh stop /usr/local/etc/rc.d/postgresql stop
33. How To Check Whether Postgresql Server Is Up And Running?
/usr/local/etc/rc.d/010.pgsql.sh status /usr/local/etc/rc.d/postgresql status
34. How to select the first 10 records in PostgreSQL?
To get the first 10 records from a database in PostgreSQL, use the LIMIT command.
Here is an example of picking the first 10 records from a database called Example:
select * from users order by name desc LIMIT 0, 1035. How to change the datatype of a column in PostgreSQL?
To change the records type of a column, you use the ALTER TABLE assertion as follows:
ALTER TABLE users
ALTER COLUMN username[SET DATA] TYPE new_data_type;