0

I understand basic primary key table storage in PostgreSQL, where the data is stored in pages, and a B-tree index structure is used for efficient retrieval.

However, I would like to understand the behaviour when a table doesn't have a primary key but has an index on a non-primary key column, such as the name column.

CREATE TABLE products (
  id SERIAL,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

Assuming the "products" table has 100 rows, my understanding is that the data will be stored in pages, and the B-tree index will have a root node [1, 100].

Additionally, there will be intermediate nodes with ranges like [1, 49] and [50, 100], which will have child nodes, and so on. However, I'm not clear about what will happen with the leaf nodes of the index. Will they contain actual row data?

  1. In the case of an index on the name column without a primary key, will the leaf nodes of the index contain the actual row data or pointers to the rows?

  2. How will the retrieval process work if the leaf nodes contain the actual row data? Will it perform a linear scan of the leaf nodes to find the desired "name" value?

  3. Also, can having a primary key, if the table is already indexed on name, optimize the query?

I would appreciate a detailed explanation of this scenario's storage and retrieval mechanism, along with any relevant code examples or additional considerations.

2 Answers 2

1

I think that you have fallen prey to a fundamental misunderstanding. A PostgreSQL table is just an unordered assortment of rows, it is not organized by a primary key. A primary key index is no different from other B-tree indexes. A B-tree index does not reference the table rows by their primary key, but by their ctid (current tuple ID), which is a combination of 8kB block number and entry number withing the block.

A B-tree index on name will store the actual strings, organized in a tree-shaped ordered list. The leaf nodes contain the ctid of the referenced table rows.

1
  • You are right! I was reading use-the-index-luke.com/sql/anatomy/the-leaf-nodes. Then I got confused about how Postgres will handle it. Later I got to know, in Postgresql, every table is stored in a heap. Whatever you explained about PK is correct. By default, the index will contain the index column value and pointer cid, a tuple which is a pointer to the actual page and the row.
    – sujeet
    Jul 5, 2023 at 9:57
1

I think this part of the documentation answers most of your question. With my understanding, no I don't think having a primary key on name will optimize the query. The primary key constraint doesn't affect the existing index on the name column. However, having a primary key may have other performance benefits, such as improved data integrity and faster join operations if you need to reference the products table in foreign keys.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.