Explore topic-wise InterviewSolutions in .

The commit action ensures that the DATA CONSISTENCY of the transaction is maintained and it ends the current transaction in the section. Commit adds a new record in the log that describes the COMMIT to the memory. Whereas, a checkpoint is used for writing all changes that were committed to disk up to SCN which would be kept in datafile headers and control files.

SQL is a language for the database. It has a vast scope and robust capability of creating and manipulating a variety of database objects using commands like CREATE, ALTER, DROP, ETC, and also in loading the database objects using commands like INSERT. It also provides options for Data Manipulation using commands like DELETE, TRUNCATE and also does effective retrieval of data using cursor commands like FETCH, SELECT, etc. There are many such commands which provide a large amount of control to the PROGRAMMER to interact with the database in an efficient way without wasting many resources. The popularity of SQL has grown so much that almost every programmer relies on this to implement their application's storage functionalities thereby making it an exciting language to learn. Learning this provides the developer a benefit of understanding the data structures used for storing the organization's data and giving an additional level of control and in-depth understanding of the application.

PostgreSQL being an open-source database system having extremely robust and sophisticated ACID, Indexing, and Transaction supports has found widespread popularity among the developer community. 

References and Resources:

PostgreSQL Download

PostgreSQL Tutorial

SQL Guide

SQL Server Interview Questions

MySQL Interview Questions

DBMS Interview Questions

PL SQL Interview Questions

MongoDB Interview Questions

SQL Vs MySQL

PostgreSQL vs MySQL

Difference Between SQL and PLSQL

SQL Vs NoSQL

SQL IDE

SQL Projects

PARALLEL Queries support is a FEATURE provided in PostgreSQL for devising query plans CAPABLE of exploiting MULTIPLE CPU processors to execute the queries faster.

Full-Text Search is the METHOD of searching single or collection of documents stored on a computer in a full-text based database. This is mostly supported in advanced database systems like SOLR or ELASTICSEARCH. However, the FEATURE is PRESENT but is PRETTY basic in PostgreSQL.

We can ACHIEVE this by using the pg_dump tool for DUMPING all object CONTENTS in the database into a single file. The steps are as follows:

Step 1: Navigate to the bin folder of the PostgreSQL installation path.

Step 2: EXECUTE pg_dump program to TAKE the dump of data to a .tar folder as shown below:

The database dump will be stored in the sample_data.tar file on the location specified.

To PERFORM CASE INSENSITIVE matches using a regular expression, we can use POSIX (~*) expression from pattern matching OPERATORS. For EXAMPLE:

DROP TABLE command deletes complete data from the table along with removing the complete table structure too. In CASE our requirement entails just remove the data, then we would NEED to RECREATE the table to store data in it. In such CASES, it is advised to use the TRUNCATE command.

Write Ahead Logging is a feature that increases the database reliability by logging changes before any changes are DONE to the database. This ensures that we have ENOUGH INFORMATION when a database crash OCCURS by helping to pinpoint to what point the work has been complete and gives a starting point from the point where it was discontinued.

For more information, you can REFER here.

SQL standards state that the following three phenomena should be prevented whilst concurrent transactions. SQL standards define 4 levels of TRANSACTION isolations to deal with these phenomena.

• Dirty reads: If a transaction reads data that is written DUE to concurrent uncommitted transaction, these reads are called dirty reads.
• Phantom reads: This occurs when two same queries when EXECUTED separately return different rows. For example, if transaction A retrieves some set of rows matching search criteria. Assume another transaction B retrieves new rows in addition to the rows obtained earlier for the same search criteria. The results are different.
• Non-repeatable reads: This occurs when a transaction tries to read the same row multiple times and gets different values each time due to CONCURRENCY. This happens when another transaction updates that data and our current transaction fetches that updated data, resulting in different values.

To tackle these, there are 4 standard isolation levels defined by SQL standards. They are as follows:

• Read Uncommitted – The lowest level of the isolations. Here, the transactions are not isolated and can read data that are not committed by other transactions resulting in dirty reads.
• Read Committed – This level ensures that the data read is committed at any instant of read time. Hence, dirty reads are avoided here. This level makes use of read/write lock on the current rows which prevents read/write/update/delete of that row when the current transaction is being operated on.
• Repeatable Read – The most restrictive level of isolation. This holds read and write locks for all rows it operates on. Due to this, non-repeatable reads are avoided as other transactions cannot read, write, update or delete the rows.
• Serializable – The HIGHEST of all isolation levels. This guarantees that the execution is serializable where execution of any concurrent operations are guaranteed to be appeared as executing serially.

The following table clearly explains which type of unwanted reads the levels avoid:

The COMMAND enable-debug is used for enabling the COMPILATION of all LIBRARIES and applications. When this is enabled, the system processes get hindered and generally also increases the size of the binary file. Hence, it is not recommended to switch this on in the production environment. This is most commonly used by DEVELOPERS to debug the bugs in their scripts and help them spot the issues. For more information regarding how to debug, you can REFER here.

MVCC or Multi-version concurrency control is USED for avoiding unnecessary database LOCKS when 2 or more requests tries to access or modify the data at the same TIME. This ENSURES that the time lag for a user to log in to the database is avoided. The transactions are RECORDED when anyone tries to access the content.

For more information regarding this, you can refer here.

• The architecture of PostgreSQL follows the CLIENT-SERVER model.
• The server side comprises of background process manager, query processer, utilities and shared memory space which work together to build PostgreSQL’s instance that has access to the data. The client application does the task of connecting to this instance and REQUESTS data processing to the services. The client can either be GUI (Graphical User Interface) or a web application. The most COMMONLY used client for PostgreSQL is PGADMIN.

ACID stands for Atomicity, Consistency, Isolation, DURABILITY. They are database transaction properties which are used for GUARANTEEING data validity in case of errors and failures.

• Atomicity: This property ensures that the transaction is completed in all-or-nothing way.
• Consistency: This ensures that updates made to the database is valid and follows RULES and restrictions.
• Isolation: This property ensures integrity of transaction that are visible to all other transactions.
• Durability: This property ensures that the committed transactions are stored PERMANENTLY in the database.

PostgreSQL is compliant with ACID properties.

This can be done by using the DROP DATABASE command as shown in the SYNTAX below:

If the database has been deleted successfully, then the following message would be shown:

This can be done by USING the COMMAND \l -> backslash FOLLOWED by the lower-case LETTER L.

They are character SEQUENCES bound within single quotes. These are using during data insertion or updation to characters in the database.
There are special string constants that are quoted in dollars. Syntax: $tag$<string_constant>$tag$ The tag in the CONSTANT is optional and when we are not specifying the tag, the constant is CALLED a double-dollar string LITERAL.

A sequence is a schema-bound, user-defined object which aids to generate a sequence of integers. This is most commonly used to generate values to IDENTITY columns in a table. We can create a sequence by USING the CREATE SEQUENCE statement as shown below:

To get the NEXT number 101 from the sequence, we use the nextval() method as shown below:

We can also use this sequence while inserting new records using the INSERT command:

The MAXIMUM SIZE of POSTGRESQL is 32TB.

TRUNCATE TABLE name_of_table statement REMOVES the data efficiently and quickly from the table.
The truncate statement can also be used to reset values of the identity columns along with data CLEANUP as shown below:

We can also USE the statement for removing data from multiple tables all at once by mentioning the table names separated by comma as shown below:

A token in POSTGRESQL is either a keyword, identifier, literal, CONSTANT, quotes identifier, or any symbol that has a distinctive personality. They may or may not be SEPARATED USING a space, NEWLINE or a tab. If the tokens are keywords, they are usually commands with useful meanings. Tokens are known as building blocks of any PostgreSQL code.

Partitioned tables are LOGICAL structures that are used for dividing large tables into smaller structures that are called partitions. This approach is used for effectively increasing the query performance while dealing with large database tables. To create a partition, a key called partition key which is usually a table column or an expression, and a partitioning method needs to be defined. There are three types of inbuilt partitioning methods provided by Postgres:

• Range Partitioning: This method is DONE by partitioning based on a range of values. This method is most commonly used upon date fields to get MONTHLY, weekly or yearly data. In the case of corner cases like value belonging to the end of the range, for example: if the range of partition 1 is 10-20 and the range of partition 2 is 20-30, and the given value is 10, then 10 BELONGS to the second partition and not the first.
• List Partitioning: This method is used to partition based on a list of known values. Most commonly used when we have a key with a categorical value. For example, getting sales data based on regions divided as countries, cities, or states.
• Hash Partitioning: This method utilizes a hash FUNCTION upon the partition key. This is done when there are no specific requirements for data division and is used to access data individually. For example, you want to access data based on a specific product, then using hash partition would result in the dataset that we require.

The type of partition key and the type of method used for partitioning determines how positive the performance and the level of manageability of the partitioned table are.

• To start the PostgreSQL SERVER, we RUN:

• Once the server is successfully started, we get the below message:

• To restart the PostgreSQL server, we run:

Once the server is successfully restarted, we get the message:

• To stop the server, we run the command:

Once stopped successfully, we get the message:

The first STEP of using POSTGRESQL is to create a database. This is done by using the createdb command as SHOWN below: createdb db_name
After RUNNING the above command, if the database creation was SUCCESSFUL, then the below message is shown:

This can be DONE by using the ALTER TABLE statement as shown below:

SYNTAX:

Indexes are the INBUILT FUNCTIONS in PostgreSQL which are USED by the queries to perform search more efficiently on a table in the database. Consider that you have a table with thousands of records and you have the below QUERY that only a few records can satisfy the condition, then it will take a lot of time to search and return those rows that abide by this condition as the engine has to perform the search operation on every SINGLE to check this condition. This is undoubtedly inefficient for a system dealing with huge data. Now if this system had an index on the column where we are applying search, it can use an efficient method for identifying matching rows by walking through only a few levels. This is called indexing.

PostgreSQL was first called Postgres and was developed by a team led by Computer Science Professor Michael Stonebraker in 1986. It was developed to help developers build enterprise-level applications by upholding data integrity by making systems fault-tolerant. PostgreSQL is therefore an enterprise-level, flexible, ROBUST, open-source, and object-relational DBMS that supports flexible workloads along with handling concurrent USERS. It has been CONSISTENTLY supported by the GLOBAL developer community. Due to its fault-tolerant nature, PostgreSQL has gained widespread popularity among developers.