What is a B-Tree (Balanced Tree)?

B-Tree (Balanced Tree) is a data structure used in databases for fast searching, inserting, and deleting records. It is organized in the form of a tree, where each node contains keys and references to child nodes. All keys in a single node are ordered, and other nodes contain the actual data or references to table rows. The main advantage of B-Tree is balance: the height of the tree remains minimal, ensuring logarithmic time search.

Indexes in databases operate based on B-Tree (by default in PostgreSQL and MySQL). When you create an index on a column, the database builds a tree based on the values of that column. This allows for quick retrieval of the required records without a full table scan. For example, the query WHERE email = '[email protected]' uses the index to jump directly to the node with the required email instead of checking all rows.

In Rails, indexes 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 fast user lookup by email and ensures the uniqueness of values. In MySQL, the migration syntax is the same, as Rails abstracts the specific DBMS.

Internally, the database builds a B-Tree based on the values of the email column, and any search through this index traverses the nodes of the tree rather than the entire table. This significantly increases query speed, especially on large tables.

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

In PostgreSQL, you can view indexes using the command:

\d users

or through system catalogs:

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

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

In MySQL, indexes can be viewed like this:

SHOW INDEX FROM users;

This will provide a list of all indexes on the users table, the columns, the type of index (BTREE or other), uniqueness, and sort 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.

So indexes do exist in the database, and they can be accessed and analyzed for their structure, even if they are not displayed as a regular table.