Reference manual

This document describes about all features. Tutorial focuses on easy to understand only about important features. This document focuses on completeness. If you don't read tutorial yet, read tutorial before read this document.

pgroonga schema

pgroonga schema is deprecated since 2.0.0. Use pgroonga_* functions, operators and operator classes in the current schema for newly written code.

PGroonga defines functions, operators, operator classes and so on into pgroonga schema. Only superuser can use features in pgroonga schema by default. Superuser needs to grant USAGE privilege on pgroonga schema to normal users who want to use PGroonga.

• GRANT USAGE ON SCHEMA pgroonga

pgroonga index

• CREATE INDEX USING pgroonga explained in detail

• PGroonga versus GiST and GIN

• PGroonga versus textsearch and pg_trgm

• PGroonga versus pg_bigm

• Replication

• Crash safe

• jsonb support

Operators

Row level security support

Since 2.3.3.

All v2 operators supports PostgreSQL's row level security.

For text

pgroonga_text_full_text_search_ops_v2 operator class (default)

• LIKE operator

• ILIKE operator

• &@ operator: Full text search by a keyword

• %% operator: Full text search by a keyword

  • Don't use this operator for newly written code. It's just for backward compatibility.

• &@~ operator: Full text search by easy to use query language

• &? operator: Full text search by easy to use query language

  • Deprecated since 1.2.2. Use &@~ operator instead.

• @@ operator: Full text search by easy to use query language

  • Don't use this operator for newly written code. It's just for backward compatibility.

• &@* operator: Similar search

• &~? operator: Similar search

  • Deprecated since 1.2.2. Use &@* operator instead.

• &` operator: Advanced search by ECMAScript like query language

• &@| operator: Full text search by an array of keywords

• &@> operator: Full text search by an array of keywords

  • Deprecated since 1.2.1. Use &@| operator instead.

• &@~| operator: Full text search by an array of queries in easy to use query language

• &?| operator: Full text search by an array of queries in easy to use query language

  • Deprecated since 1.2.2. Use &@~| operator instead.

• &?> operator: Full text search by an array of queries in easy to use query language

  • Deprecated since 1.2.1. Use &@~| operator instead.

pgroonga_text_term_search_ops_v2 operator class

• <

  • Since 1.2.2.

• <=

  • Since 1.2.2.

• =

  • Since 1.2.2.

• >=

  • Since 1.2.2.

• >

  • Since 1.2.2.

• &= operator: Exact match search

  • Since 2.4.6.

• &^ operator: Prefix search

• &^~ operator: Prefix RK search

• &^| operator: Prefix search by an array of prefixes

• &^> operator: Prefix search by an array of prefixes

  • Deprecated since 1.2.1. Use &^| operator instead.

• !&^| operator: NOT prefix search by an array of prefixes

  • Since 2.2.1.

• &^~| operator: Prefix RK search by an array of prefixes

• &^~> operator: Prefix RK search by an array of prefixes

  • Deprecated since 1.2.1. Use &^~| operator instead.

pgroonga_text_regexp_ops_v2 operator class

• LIKE operator

• ILIKE operator

• &~ operator: Search by a regular expression

• @~ operator: Search by a regular expression

  • Don't use this operator for newly written code. It's just for backward compatibility.

• &~| operator: Search by an array of regular expressions

  • Since 2.2.1.

pgroonga_text_semantic_search_ops_v2 operator class

Since 4.0.5.

• &@* operator: Semantic search

  • Since 4.0.5.

• <&@*> operator: Calculates the semantic distance

  • Since 4.0.5.

For text[]

pgroonga_text_array_full_text_search_ops_v2 operator class (default)

• &@ operator: Full text search by a keyword

• %% operator: Full text search by a keyword

  • Don't use this operator for newly written code. It's just for backward compatibility.

• &@~ operator: Full text search by easy to use query language

• &? operator: Full text search by easy to use query language

  • Deprecated since 1.2.2. Use &@~ operator instead.

• @@ operator: Full text search by easy to use query language

  • Don't use this operator for newly written code. It's just for backward compatibility.

• &@* operator: Similar search

• &~? operator: Similar search

  • Deprecated since 1.2.2. Use &@* operator instead.

• &` operator: Advanced search by ECMAScript like query language

• &@| operator: Full text search by an array of keywords

• &@> operator: Full text search by an array of keywords

  • Deprecated since 1.2.1. Use &@| operator instead.

• &@~| operator: Full text search by an array of queries in easy to use query language

• &?| operator: Full text search by an array of queries in easy to use query language

  • Deprecated since 1.2.2. Use &@~| operator instead.

• &?> operator: Full text search by an array of queries in easy to use query language

  • Deprecated since 1.2.1. Use &@~| operator instead.

pgroonga_text_array_term_search_ops_v2 operator class

• &^ operator: Prefix search

• &^> operator: Prefix search

  • Deprecated since 1.2.1. Use &^ operator instead.

• &^~ operator: Prefix RK search

• &^~> operator: Prefix RK search

  • Deprecated since 1.2.1. Use &^~ operator instead.

• &^| operator: Prefix search by an array of prefixes

• &^~| operator: Prefix RK search by an array of prefixes

• @> operator: Contained search by an array

  • Since 2.2.1.

• &=~ operator: Equal search by easy to use query language

  • Since 3.0.0.

pgroonga_text_array_regexp_ops_v2 operator class

Since 3.2.5.

• &~ operator: Search by a regular expression

For varchar

pgroonga_varchar_term_search_ops_v2 operator class (default)

• <

• <=

• =

• >=

• >

• &= operator: Exact match search

  • Since 2.4.6.

• &^ operator: Prefix search

• &^~ operator: Prefix RK search

• &^| operator: Prefix search by an array of prefixes

• &^~| operator: Prefix RK search by an array of prefixes

pgroonga_varchar_full_text_search_ops_v2 operator class

• &@ operator: Full text search by a keyword

• %% operator: Full text search by a keyword

  • Don't use this operator for newly written code. It's just for backward compatibility.

• &@~ operator: Full text search by easy to use query language

• &? operator: Full text search by easy to use query language

  • Deprecated since 1.2.2. Use &@~ operator instead.

• @@ operator: Full text search by easy to use query language

  • Don't use this operator for newly written code. It's just for backward compatibility.

• &@* operator: Similar search

• &~? operator: Similar search

  • Deprecated since 1.2.2. Use &@* operator instead.

• &` operator: Advanced search by ECMAScript like query language

• &@| operator: Full text search by an array of keywords

• &@> operator: Full text search by an array of keywords

  • Deprecated since 1.2.1. Use &@| operator instead.

• &@~| operator: Full text search by an array of queries in easy to use query language

• &?| operator: Full text search by an array of queries in easy to use query language

  • Deprecated since 1.2.2. Use &@~| operator instead.

• &?> operator: Full text search by an array of queries in easy to use query language

  • Deprecated since 1.2.1. Use &@~| operator instead.

pgroonga_varchar_regexp_ops_v2 operator class

• &~ operator: Search by a regular expression

• @~ operator: Search by a regular expression

  • Don't use this operator for newly written code. It's just for backward compatibility.

• &~| operator: Search by an array of regular expressions

  • Since 2.2.1.

For varchar[]

pgroonga_varchar_array_term_search_ops_v2 operator class (default)

• &> operator: Check whether a term is included in an array of terms

• %% operator: Check whether a term is included in an array of terms

  • Don't use this operator for newly written code. It's just for backward compatibility.

• @> operator: Contained search by an array

  • Since 2.2.1.

• &=~ operator: Equal search by easy to use query language

  • Since 3.0.0.

For boolean, numbers and timestamps

Supported types: boolean, smallint, integer, bigint, real, double precision, timestamp and timestamp with time zone

• <

• <=

• =

• >=

• >

For jsonb

pgroonga_jsonb_ops_v2 operator class (default)

• &@ operator: Full text search against all text data in jsonb by a keyword

• &@~ operator: Full text search against all text data in jsonb by easy to use query language

• &? operator: Full text search against all text data in jsonb by easy to use query language

  • Deprecated since 1.2.2. Use &@~ operator instead.

• &` operator: Advanced search by ECMAScript like query language

• @@ operator: Advanced search by ECMAScript like query language

  • Don't use this operator for newly written code. It's just for backward compatibility.

• @> operator: Search by a jsonb data

pgroonga_jsonb_full_text_search_ops_v2 operator class

• &@ operator: Full text search against all text data in jsonb by a keyword

• &@~ operator: Full text search against all text data in jsonb by easy to use query language

Old operators

For text

pgroonga_text_full_text_search_ops operator class (default)

Deprecated since 2.0.0.

Use pgroonga_text_full_text_search_ops_v2 operator class instead.

• LIKE operator

• ILIKE operator

• &@ operator: Full text search by a keyword

• %% operator: Full text search by a keyword

  • Deprecated since 1.2.0. Use &@ operator instead.

• &@~ operator: Full text search by easy to use query language

• &? operator: Full text search by easy to use query language

  • Deprecated since 1.2.2. Use &@~ operator instead.

• @@ operator: Full text search by easy to use query language

  • Deprecated since 1.2.0. Use &@~ operator instead.

pgroonga_text_regexp_ops operator class

Deprecated since 2.0.0.

Use pgroonga_text_regexp_ops_v2 operator class instead.

• LIKE operator

• ILIKE operator

• &~ operator: Search by a regular expression

• @~ operator: Search by a regular expression

  • Deprecated since 1.2.1. Use &~ operator instead.

For text[]

pgroonga_text_array_full_text_search_ops operator class

Deprecated since 2.0.0.

Use pgroonga_text_array_full_text_search_ops_v2 operator class instead.

• &@ operator: Full text search by a keyword

• %% operator: Full text search by a keyword

  • Deprecated since 1.2.0. Use &@ operator instead.

• &@~ operator: Full text search by easy to use query language

• &? operator: Full text search by easy to use query language

  • Deprecated since 1.2.2. Use &@~ operator instead.

• @@ operator: Full text search by easy to use query language

  • Deprecated since 1.2.0. Use &@~ operator instead.

For varchar

pgroonga_varchar_ops operator class

Deprecated since 2.0.0.

Use pgroonga_varchar_term_search_ops_v2 operator class instead.

• <

• <=

• =

• >=

• >

pgroonga_varchar_full_text_search_ops operator class

Deprecated since 2.0.0.

Use pgroonga_varchar_full_text_search_ops_v2 operator class instead.

• &@ operator: Full text search by a keyword

• %% operator: Full text search by a keyword

  • Deprecated since 1.2.0. Use &@ operator instead.

• &@~ operator: Full text search by easy to use query language

• &? operator: Full text search by easy to use query language

  • Deprecated since 1.2.2. Use &@~ operator instead.

• @@ operator: Full text search by easy to use query language

  • Deprecated since 1.2.0. Use &@~ operator instead.

pgroonga_varchar_regexp_ops operator class

Deprecated since 2.0.0.

Use pgroonga_varchar_regexp_ops_v2 operator class instead.

• &~ operator: Search by a regular expression

• @~ operator: Search by a regular expression

  • Deprecated since 1.2.1. Use &~ operator instead.

For varchar[]

pgroonga_varchar_array_ops operator class

Deprecated since 2.0.0.

Use pgroonga_varchar_array_term_search_ops_v2 operator class instead.

• &> operator: Check whether a term is included in an array of terms

• %% operator: Check whether a term is included in an array of terms

  • Deprecated since 1.2.1. Use &> operator instead.

For jsonb

pgroonga_jsonb_ops operator class

Deprecated since 2.0.0.

Use pgroonga_jsonb_ops_v2 operator class instead.

• &@ operator: Full text search against all text data in jsonb by a keyword

• &@~ operator: Full text search against all text data in jsonb by easy to use query language

• &? operator: Full text search against all text data in jsonb by easy to use query language

  • Deprecated since 1.2.2. Use &@~ operator instead.

• &` operator: Advanced search by ECMAScript like query language

• @@ operator: Advanced search by ECMAScript like query language

  • Deprecated since 1.2.1. Use &` operator instead.

• @> operator: Search by a jsonb data

Functions

• pgroonga_command function

• pgroonga_command_escape_value function

• pgroonga_condition function

• pgroonga_escape function

• pgroonga_flush function

• pgroonga_database_remove function

• pgroonga_highlight_html function

• pgroonga_index_column_name function

• pgroonga_is_writable function

• pgroonga_language_model_vectorize function

  • Since 4.0.5.

• pgroonga_list_broken_indexes function

• pgroonga_list_lagged_indexes function

• pgroonga_match_positions_byte function

• pgroonga_match_positions_character function

• pgroonga_normalize function

• pgroonga_query_escape function

• pgroonga_query_expand function

• pgroonga_query_extract_keywords function

• pgroonga_result_to_jsonb_objects function

• pgroonga_result_to_recordset function

• pgroonga_set_writable function

• pgroonga_score function

• pgroonga_snippet_html function

• pgroonga_table_name function

• pgroonga_tokenize function

• pgroonga_vacuum function

• pgroonga_wal_apply function

• pgroonga_wal_set_applied_position function

• pgroonga_wal_status function

• pgroonga_wal_truncate function

Parameters

• pgroonga.enable_row_level_security parameter

  • Since 3.1.6.

• pgroonga.enable_trace_log parameter

  • Since 3.0.8.

• pgroonga.enable_wal_resource_manager parameter

  • Since 3.2.1.

• pgroonga.enable_wal parameter

• pgroonga.force_match_escalation parameter

• pgroonga.libgroonga_version parameter

• pgroonga.lock_timeout parameter

• pgroonga.log_level parameter

• pgroonga.log_path parameter

• pgroonga.log_rotate_threshold_size parameter

  • Since 3.2.3.

• pgroonga.log_type parameter

• pgroonga.match_escalation_threshold parameter

• pgroonga.max_bulk_insert_wal_record_size parameter

  • Since 3.2.5.

• pgroonga.max_wal_size parameter

  • Since 2.3.3.

• pgroonga.query_log_path parameter

• pgroonga.query_log_rotate_threshold_size parameter

  • Since 3.2.3.

• pgroonga_crash_safer.flush_naptime parameter

  • Since 2.3.3.

• pgroonga_crash_safer.log_level parameter

  • Since 2.3.3.

• pgroonga_crash_safer.log_path parameter

  • Since 2.3.3.

• pgroonga_crash_safer.max_recovery_threads parameter

  • Since 3.1.9.

• pgroonga_standby_maintainer.max_parallel_wal_appliers_per_db parameter

  • Since 3.1.2.

• pgroonga_standby_maintainer.naptime parameter

  • Since 2.4.2.

• pgroonga_wal_resource_manager.log_level parameter

  • Since 3.2.1.

• pgroonga_wal_resource_manager.log_path parameter

  • Since 3.2.1.

Modules

• pgroonga_check module

  • Deprecated since 2.3.3. Use pgroonga_crash_safer module instead.

• pgroonga_crash_safer module

  • Since 2.3.3.

• pgroonga_database module

  • Deprecated since 2.3.3. Use pgroonga_crash_safer module instead.

• pgroonga_wal_applier module

  • Since 2.3.3.

  • pgroonga_wal_applier module is deprecated since 2.4.2. We use the pgroonga_standby_maintainer module instead.

• pgroonga_standby_maintainer module

  • Since 2.4.2.

• pgroonga_wal_resource_manager module

  • Since 3.2.1.

Executables

• pgroonga-primary-maintainer.sh command

  • Since 3.2.1.

• pgroonga-generate-primary-maintainer-service.sh command

  • Since 3.2.1.

• pgroonga-generate-primary-maintainer-timer.sh command

  • Since 3.2.1.

Groonga functions

You can use them with pgroonga_command function. You can't use them in WHERE clause.

• pgroonga_tuple_is_alive Groonga function

Tuning

Normally, you don't need to tune PGroonga because PGroonga works well by default.

But you need to tune PGroonga in some cases such as a case that you need to handle a very large database. PGroonga uses Groonga as backend. It means that you can apply tuning knowledge for Groonga to PGroonga. See the following Groonga document to tune PGroonga:

• Tuning