PostgreSQL Interview Questions & Answers
What is PostgreSQL?
PostgreSQL is a lightweight, free, and open-source relational database management system. PostgreSQL is used widely across regions and companies and can be used in most popular operating systems.
What are the benefits of PostgreSQL?
PostgreSQL excels among other SQL databases for several reasons, including:
- Robustness that makes it suitable for all kinds of applications,
- Free and open-source,
- Security and reliability
- Wide variety of data types,
- A big community of users worldwide.
What are the main applications of PostgreSQL?
PostgreSQL allows you to:
- Create, delete, and update tables in a database,
- Access, manipulate, and modify data in a table,
- Retrieve and summarize the necessary information from a table or several tables,
- Add or remove certain rows or columns from a table
What are CRUD operations in PostgreSQL?
CRUD (Create, Read, Update, Delete) operations are the basic operations in any SQL database system, including PostgreSQL. CRUD operations are frequently used in database design and management.
What is a database in PostgreSQL?
As in other SQL dialects, the database is a structured storage space where the data is kept in many tables and organized so that the necessary information can be easily fetched, manipulated, and summarized.
What is pgAdmin in PostgreSQL?
pgAdmin is a handy utility that comes with the PostgreSQL installation, and it lets you do regular database-related tasks through a nice graphical interface.
How can you create a new database in PostgreSQL?
PostgreSQL uses the standard CREATE DATABASE command to create new databases.
How can you add new values to a certain table?
PostgreSQL uses the standard INSERT INTO statement to add data to your SQL table.
How can you delete a database in PostgreSQL?
To delete a database in PostgreSQL, use the DROP DATABASE command.
What is a schema in PostgreSQL?
A database schema contains the logical and visual configuration of the entire relational database. In PostgreSQL, it includes the tables, along with the data types, views, indexes, sequences, constraints, and functions.
How can you select the five first rows in a table called ‘customers’ in PostgreSQL?
You can retrieve that data using the following query:
SELECT * FROM customers LIMIT 5;
What is a constraint in PostgreSQL?
A set of conditions defining the type of data that can be input into each column of a table. Constraints are used to ensure data integrity in a table and prevent undesired actions.
What is a join in PostgreSQL?
Joins are used to combine and retrieve records from two or multiple tables. PostgreSQL uses standard SQL joins to perform these kinds of operations.
Can you explain what is a primary key in PostgreSQL?
A primary key is used to identify a row uniquely in a table. Primary keys may be made of one column or multiple columns. A primary key can be classified as a type of constraint.
Is PostgreSQL compatible with Python?
\
Yes, there are many packages available that allow you to use PostgreSQL through Python. Psycopg is one of the most popular Python libraries for PostgreSQL.
What is the difference between a foreign key and a primary key in PostgreSQL?
A foreign key provides shared keys between two or more tables, whereas a primary key allows only unique and strictly non-null values. Both are considered types of constraints.
What are the main constraints in PostgreSQL?
PostgreSQL providers for the following constraints:
- Check Constraints
- Not-Null Constraints
- Unique Constraints
- Primary Keys
- Foreign Keys
- Exclusion Constraints
What is the latest version of PostgreSQL?
As of January 2024, the latest version is PostgreSQL 16.
Can you run PostgreSQL on the cloud?
Yes, PostgreSQL is compatible and can be run on most popular cloud providers, including AWS, Azure, and Google Cloud.
What is PL/Python in PostgreSQL?
PostgreSQL provides support to a procedural language known as PL/Python. This allows you to write SQL functions and procedures using Python.
What is Multi-version Concurrency Control in PostgreSQL?
Multi-version Concurrency Control or MVCC is an advanced technique in PostgreSQL. It enhances database performance in multi-user scenarios. This means that while querying a database each transaction sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This protects the transaction from viewing inconsistent data that could be caused by (other) concurrent transaction updates on the same data rows.
Put simply, MVCC creates multiple versions of a single database record, enabling various transactions to access different versions of one database record without conflicting with one another, thereby allowing simultaneous transitions.
What is the maximum size for a table in PostgreSQL?
While PostgreSQL provides unlimited database size, there is a maximum size for tables, which is set to 32 TB.
What are the main operators in PostgreSQL?
The main types of operators available in PostgreSQL are:
- Arithmetic operators
- Logical operators
- Comparison operators
- Bitwise operators
What is an index in PostgreSQL?
An index is a special data structure related to a table and used for storing its important parts and enabling faster data search and retrieval. This is achieved by creating a sorted data structure that allows PostgreSQL to locate rows more quickly compared to a full table scan. Indexes are especially efficient for large databases, where they significantly enhance query performance.
What is partitioning in PostgreSQL?
It’s the process of splitting a large table into smaller pieces. It can be done through several methods, including range partitioning, list partitioning, and hash partitioning.
What are the 4 main properties of a transaction in PostgreSQL? Which acronym is used to refer to them?
Transactions in PostgreSQL are expected to be atomic, consistent, isolated, and durable. These properties are commonly referred to by the acronym, ACID.
What is Write-Ahead Logging in PostgreSQL?
Write-ahead logging is a technique used to ensure the data integrity of PostgreSQL databases. Write-ahead logging ensures that any changes and actions in the database are logged in a transaction log before the updating or modification of the database. In case of a database crash, this helps in providing the log of the database changes.
What types of joins are available in PostgreSQL?
In PostgreSQL, the main joins are:
- INNER JOIN: Returns records that have matching values in both tables
- LEFT JOIN: Returns all records from the left table, and the matched records from the right table
- RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
- FULL JOIN: Returns all records when there is a match in either left or right table
What is a function in PostgreSQL?
Functions, also known as Stored Procedures, allow you to wrap in a single procedure several operations that would normally take several queries. That can be reused anytime, thereby helping users save time.
What is a view in PostgreSQL?
Views represent the result of a query to one or more underlying tables. Views are used to simplify complex queries since these queries are defined once in the view, and can then be directly queried via the same.
What is normalization in PostgreSQL?
Database normalization is a process by which databases and tables are created or modified to address inefficiencies associated with data storage, data modification, or querying processes. In simple terms, normalization involves multiple steps to reduce data redundancy and complexity.
What are triggers in PostgreSQL?
A trigger, also known as a callback function, is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. Triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the event. Triggers help ensure data integrity during the modification of a database.
How can you make a backup of a database in PostgreSQL?
Making a backup of your database in PostgreSQL is fairly simple. There are various methods to perform a backup in PostgreSQL, including:
- SQL dump
- File system-level backup
- On-line backup
What is the pg_dump method used for?
The pg_dump method allows you to create a text file with a set of SQL commands that, when run in a PostgreSQL server, will recreate the database in the same state as it was at the moment of the dump.
How can you delete a table, as well as any other object associated with it, such as views, triggers, functions, and stored procedures?
To delete a table in PostgreSQL, use the DROP TABLE command, followed by the name of the table. To ensure that any other object is associated with it, you will need to add the CASCADE command.
What are the benefits of partitioning?
Partitioning allows to divide a table into smaller, more manageable partitions, which translate into increased query performance. It’s particularly suitable when dealing with big tables.
What is the fastest way to remove all the rows in a large table?
There are two main ways to remove the rows in a table: the DELETE command and the TRUNCATE command. The former is designed to remove rows more selectively and requires a full scan of the tables. The latter quickly removes all the rows and empty disk space without requiring a table scan. Therefore, the TRUNCATE command is the most suitable.
What commands are used to control transactions in PostgreSQL?
There are 3 main commands to control transactions in PostgreSQL:
- BEGIN TRANSACTION or simply BEGIN: To start a transaction.
- COMMIT or END TRANSACTION: To save the changes. The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.
- ROLLBACK: It’s used to undo transactions that have not already been saved to the database.
How is security ensured in PostgreSQL?
PostgreSQL is one of the most secure SQL databases. Security is addressed on several levels:
- Database file protection. All files stored within the database are protected from reading by any account other than the Postgres superuser account.
- Connections from a client to the database server are, by default, allowed only via a local Unix socket
- Client connections can be restricted by IP address and/or username.
- Client connections may be authenticated via other external packages.
- Each user in Postgres is assigned a username and (optionally) a password.
- Users may be assigned to groups, and table access may be restricted based on group privileges.
Imagine you have a large table with the historical daily temperature in New York. What would be the most effective strategy to partition the table?
When dealing with time-series data, the most effective strategy is range partitioning, which allows the breaking of tables based on a fixed range of values. In this case, the most likely partition would be based on days.
What is parallel querying in PostgreSQL?
Parallel querying is a technique in PostgreSQL that allows you to create query plans that can leverage multiple CPUs to answer queries more efficiently. This technique is particularly well-suited when the query involves scanning a lot of data but returning a few rows, for example, aggregate calculations.
How can you improve query performance in PostgreSQL?
There are multiple strategies to increase query performance, including:
- Using indexing, especially in queries that involve WHERE clauses;
- Writing efficient SQL statements to reduce processing overhead, for example, by avoiding unnecessary columns in the SELECT statement.
- Implementing partitioning for large tables.
- Optimizing memory usage by tuning server parameters to match hardware specifications.
In which scenarios the EXPLAIN ANALYZE command can be handy?
The EXPLAIN command shows you the execution plan of a SQL statement. This includes the manner the table(s) referenced in your statement will be processed, the underlying algorithms that will be used for complex operations, such as joins, as well as the estimated execution time.
If, in addition to the estimated time, you want to know the actual time required, you can add the ANALYZE command, and the statement will be actually executed, not only planned.
Overall, The EXPLAIN ANALYZE command is particularly handy to spot bottleneck in complex queries so you can rewrite them to improve query performance
How can you handle errors in PostgreSQL?
There are two main ways to address errors in PostgreSQL:
- Callback functions can be developed to handle warning and error conditions. In this case, you can specify a certain behavior in case of errors and warnings in your queries using the WHENEVER command.
- Detailed information about the error or warning can be obtained from the sqlca variable. This variable provides detailed information when errors and warnings arise during execution.
What is the relevance of logs for troubleshooting in PostgreSQL?
PostgreSQL logs are a valuable resource for troubleshooting problems, tracking performance, and auditing database activity. PostgreSQL comes with a wide variety of logs, including error logs. These logs can help you spot queries and statements that lead to errors during execution.