What is B-Tree (Balanced Tree)?

B-Tree (Balanced Tree) is a data structure used in databases to quickly search, insert and delete records. It is organized as a tree, where each node contains keys and references to child nodes. All keys in one node are ordered, and other nodes contain the data itself or links to table rows. The main advantage of B-Tree is balance: the height of the tree remains minimal, which ensures a search in logarithmic time.

The indices in the databases work specifically on the basis of B-Tree (in PostgreSQL and MySQL by default). When you create an index per column, the database builds a tree by the values of that column. This allows you to quickly find the desired records without a full table scan. For example, the WHERE email = '[email protected]' query uses an index to immediately go to the node with the desired email instead of checking all lines.

In Rails, indices are created through migrations. For example, in PostgreSQL:

class AddIndexToUsersEmail < ActiveRecord::Migration[7.0]
  def change
    add_index:users,:email, unique: true
  end
end

This index provides a quick search for the user by email and guarantees the uniqueness of the values. In MySQL, the migration syntax is the same because Rails abstracts a specific DBMS.

Internally, the database builds B-Tree by the values of the email column, and any search through this index goes through the nodes of the tree, and not through the entire table. This greatly increases the speed of requests, especially on large tables.

In short - yes. But there is a nuance. The indices in the database are visible, although they are not stored as separate tables in the usual sense. Each DBMS provides ways to see which indices exist on the table and their properties.

IN PostgreSQL you can view indexes through the command:

\d users

or through system directories:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

This will show the names of the indices, the columns on which they are created, and the type of index (B-Tree by default).

IN MySQL indices can be viewed as follows:

SHOW INDEX FROM users;

Here will be a list of all indices of the users table, columns, index type (BTREE or other), uniqueness and sorting order.

IN Rails you can view indexes through the console:

ActiveRecord::Base.connection.indexes(:users)

The output will show an array of objects with information about each index: name, columns, uniqueness.

That is, indices actually exist in the database, and they can be accessed and analyzed for their structure, even if they do not appear as a regular table.