Database Internals

Data can be retrieved from tables in databases in a variety of ways.

Sequential scanning and B-tree index scanning are two popular techniques.

In this essay, we'll talk about both of them and examine their differences.

Sequential scan:

The simplest method for obtaining data from a table is a sequential scan.

As the name implies, this function reads each tuple on each page in order.

This is accomplished by scanning each page's line pointers.

The tuples on the pages can be found using the line pointers.

Sequential scanning is a brute-force technique that might be sluggish for big tables.

Yet, it can be helpful when the data is little and the query must scan the entire table.

B-tree index scan:

Scan the B-tree index:

It is more effective to get data from a table using the B-tree index scan.

It makes use of an index file with index tuples.

A TID (tuple identifier) pointing to the target heap tuple plus an index key make up each index tuple.

When a query needs to retrieve data, it first looks for the index tuple with the desired key in the index file.

Once the index tuple has been located, PostgreSQL uses the acquired TID value to fetch the required heap tuple.

When a query needs to retrieve data, it first looks for the index tuple with the desired key in the index file.

Once the index tuple has been located, PostgreSQL uses the acquired TID value to fetch the required heap tuple.

Because it doesn't have to read every tuple on every page, the B-tree index scan is quicker than the sequential scan.

Instead, it merely reads the desired heap tuple and the index file.

The index's creation and upkeep, nevertheless, can take some time and have an impact on the database's performance.

Conclusion:

Table data can be retrieved using both the sequential scan and B-tree index scan.

The B-tree index scan is more effective but harder to set up and maintain than the sequential scan, which is straightforward but slow.

Depending on the size of the table, the difficulty of the query, and the available resources, the best approach must be chosen.

#data #databases #postgresql #mysql #relationaldatabases #btree