7.5. Tables

7.5.1. Summary

Table in Groonga manages relation between ID and key. Groonga provides four table types. They are TABLE_NO_KEY, TABLE_HASH_KEY, TABLE_PAT_KEY and TABLE_DAT_KEY.

All tables except TABLE_NO_KEY provides both fast ID search by key and fast key search by ID. TABLE_NO_KEY doesn’t support key. TABLE_NO_KEY only manages ID. So TABLE_NO_KEY doesn’t provides ID search and key search.

7.5.2. Characteristics

Here is a chracteristic table of all tables in Groonga. (TABLE_ prefix is omitted in the table.)

Characteristics of all tables

Item

NO_KEY

HASH_KEY

PAT_KEY

DAT_KEY

Data structure

Array

Hash table

Patricia trie

Double array trie

ID support

o

o

o

o

Key support

x

o

o

o

Value support

o

o

o

x

Key -> ID speed

  • o: fast

  • x: slow

-

oo

x

o

Update speed

  • o: fast

  • x: slow

ooo

o

o

x

Size

  • o: small

  • x: large

ooo

o

oo

x

Key update

-

x

x

o

Common prefix search

-

x

o

o

Predictive search

-

x

o

o

Range search

-

x

o

o

The maximum one key size

-

4KiB

4KiB

4KiB

The maximum total size of keys

-

4GiB or 1TiB (by specifying KEY_LARGE flag to flags)

4GiB

4GiB

The maximum number of records

1,073,741,815 (2 30 - 9)

536,870,912 (2 29)

1,073,741,823 (2 30 - 1)

268,435,455 (2 28 - 1)

7.5.2.1. TABLE_NO_KEY

TABLE_NO_KEY is very fast and very small but it doesn’t support key. TABLE_NO_KEY is a only table that doesn’t support key.

You cannot use TABLE_NO_KEY for lexicon for fulltext search because lexicon stores tokens as key. TABLE_NO_KEY is useful for no key records such as log.

7.5.2.2. TABLE_HASH_KEY

TABLE_HASH_KEY is fast but it doesn’t support advanced search functions such as common prefix search and predictive search.

TABLE_HASH_KEY is useful for index for exact search such as tag search.

7.5.2.3. TABLE_PAT_KEY

TABLE_PAT_KEY is small and supports advanced search functions.

TABLE_PAT_KEY is useful for lexicon for fulltext search and index for range search.

7.5.2.4. TABLE_DAT_KEY

TABLE_DAT_KEY is fast and supports key update but it is large. It is not suitable for storing many records. TABLE_DAT_KEY is a only table that supports key update.

TABLE_DAT_KEY is used in Groonga database. Groonga database needs to convert object name such as ShortText, TokenBigram and table names to object ID. And Groonga database needs to rename object name. Those features are implemented by TABLE_DAT_KEY. The number of objects is small. So large data size demerit of TABLE_DAT_KEY can be ignored.

7.5.3. Record ID

Record ID is assigned automatically. You cannot assign record ID.

Record ID of deleted record may be reused.

Valid record ID range is between 1 and 1073741823. (1 and 1073741823 are valid IDs.)

7.5.4. Persistent table and temporary table

Table is persistent table or temporary table.

7.5.4.1. Persistent table

Persistent table is named and registered to database. Records in persistent table aren’t deleted after closing table or database.

Persistent table can be created by table_create command.

7.5.4.2. Temporary table

Temporary table is anonymous. Records in temporary table are deleted after closing table. Temporary table is used to store search result, sort result, group (drilldown) result and so on. TABLE_HASH_KEY is used for search result and group result. TABLE_NO_KEY is used for sort result.

7.5.5. Limitations

The max number of records is 268435455. You cannot add 268435456 or more records in a table.

The max number of a key size is 4096byte. You cannot use 4097byte or larger key. You can use column instead of key for 4097byte or larger size data. Text and LargeText types supports 4097byte or larger size data.

The max number of total key size is 4GiB. You need to split a table, split a database (sharding) or reduce each key size to handle 4GiB or more larger total key size.

7.5.6. See also