2

From https://www.postgresql.org/docs/9.6/static/indexes-index-only-scans.html:

All indexes in PostgreSQL are secondary indexes, meaning that each index is stored separately from the table's main data area (which is called the table's heap in PostgreSQL terminology).

In postgresql, is a secondary index defined as an index which is stored separately from the table's main data area?

If not, what is its definition, and why does the quote mention the one which is not a definition?

Similarly, what is the definition of a primary index?

Are the concepts in postgresql the same as in Oracle database?

Thanks.

  • @klin I think that comment should be an Answer. There's not much more than that to say. – IMSoP Jun 28 '18 at 13:51
  • This is supposed to help people who come from MySQL understand the difference. In MySQL, all indexes do not reference the table heap, but the primary key index. PostgreSQL does not have such a concept. – Laurenz Albe Jun 28 '18 at 14:41
  • @LaurenzAlbe Thanks. "In MySQL, all indexes do not reference the table heap, but the primary key index." Do "all indexes" include "primary key index"? In postgresql, do all indexes reference reference the table heap directly, correct? –  Jun 28 '18 at 15:07
  • Of course the primary key index in MySQL does not reference itself. In PostgreSQL all indexes are equal, which makes reads faster, but can slow down modifications. – Laurenz Albe Jun 28 '18 at 15:10
  • @LaurenzAlbe Do you have a link to where in MySQL a secondary index and a primary index are defined? –  Jun 28 '18 at 15:16
  • Is [this](https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html) good enough? – Laurenz Albe Jun 28 '18 at 15:29
  • @LaurenzAlbe Thanks. –  Jun 28 '18 at 15:35

2 Answers2

11

There is some lack of precision in the definitions of primary & secondary indices.

Using two popular university texts as reference:

Fundamentals of Database Systems, Elmasri & Navathe defines them as:

A primary index as an index on an ordered file where the search key is the same as the sort key

A secondary index provides a secondary means of accessing a data file for which some primary access already exists. The data file records could be ordered, unordered, or hashed.

Database Systems: The Complete Book, Garcia-Molina et. al defines them as:

A primary index determines the location of the records of the data file

The secondary index is distinguished from the primary index in that a secondary index does not determine the placement of records in the data file. Rather, the secondary index tells us the current locations of records; that location may have been decided by a primary index on some other field

Some properties that hold true for either definition above:

  • primary keys can be primary indices
  • there can be at most 1 primary index per table
  • primary indices uniquely determine where a record is kept in physical storage.
  • All other indices are classified as secondary.

However, if the placement of records in the data file is not determined by any field, then a primary index cannot be constructed.

Thus for sorted files, it makes sense to talk about the primary index (which would be the list of fields upon which the sorting is based). I can't find other examples of physical file structures where a primary index can be constructed.

Postgresql utilizes a heap structure for the physical storage for records. Heaps are not sorted (pun alert: they're sorta sorted). Therefore, even the primary keys are implemented using secondary indices, and as such all indices in Postgresql are secondary.

Other RDBMS Systems do implement storage formats that support primary indices:

The language in the Postgres Documentation is imprecise.

All indexes in PostgreSQL are secondary indexes

This is true.

meaning that each index is stored separately from the table's main data area

This is not why all indices are secondary in Postgresql. Primary indices may also be stored separately from the table's main data area.

Community
  • 1
  • 1
Haleemur Ali
  • 26,718
  • 5
  • 61
  • 85
  • Thanks. "primary keys can be primary indices". Must the search key of a primary index be or related to a primary key ? Will the answer be different in PostgreSQL and other DBMS' –  Jul 01 '18 at 15:55
  • 1
    https://stackoverflow.com/questions/51128629/must-the-search-key-of-a-primary-index-be-or-related-to-the-primary-key –  Jul 02 '18 at 02:59
1

It seems that the term secondary index does not have a formal definition. In the quoted passage, the author wanted to emphasize that the index is stored separately from the data table (physically, these are two files). Usually the secondary index is understood as an index other than the primary one. However, in Postgres a table's primary key is based on an index stored separately from heap as well. These meanings are different, I do not think one should pay too much attention to this.

klin
  • 112,967
  • 15
  • 204
  • 232
  • Thanks. My question is to ask for clarification of the meanings of the terminology. and if the same term has different meanings in the different places. So it is very important and helpful for me. –  Jun 28 '18 at 14:07
  • I understand you perfectly, it seems that the authors of the documentation should avoid ambiguous concepts. – klin Jun 28 '18 at 14:21