.. highlight:: psql
.. _system-information:

==================
System information
==================

CrateDB provides the ``sys`` schema which contains virtual tables. These tables
are read-only and can be queried to get statistical real-time information about
the cluster, its nodes and their shards:

.. rubric:: Table of contents

.. contents::
   :local:

.. _sys-cluster:

Cluster
=======

Basic information about the CrateDB cluster can be retrieved from the
``sys.cluster`` table:

+------------------+---------------------------------+-------------+
| Name             | Description                     | Return Type |
+==================+=================================+=============+
| ``id``           | A unique ID generated by the    | ``TEXT``    |
|                  | system.                         |             |
+------------------+---------------------------------+-------------+
| ``license``      | The current CrateDB license     | ``OBJECT``  |
|                  | information. Always `NULL`.     |             |
|                  | This exists for backward        |             |
|                  | compatibility                   |             |
+------------------+---------------------------------+-------------+
| ``name``         | The cluster name.               | ``TEXT``    |
+------------------+---------------------------------+-------------+
| ``master_node``  | Node ID of the node which       | ``TEXT``    |
|                  | currently operates as master    |             |
+------------------+---------------------------------+-------------+
| ``settings``     | The cluster settings.           | ``OBJECT``  |
+------------------+---------------------------------+-------------+

.. Hidden: reset settings

    cr> reset GLOBAL stats.enabled, stats.jobs_log_size, stats.operations_log_size;
    RESET OK, 1 row affected (... sec)

The result has at most 1 row::

  cr> select name from sys.cluster;
  +-----------------+
  | name            |
  +-----------------+
  | Testing-CrateDB |
  +-----------------+
  SELECT 1 row in set (... sec)

.. _sys-cluster-license:

Cluster license
---------------

The ``sys.cluster.license`` :ref:`expression <gloss-expression>` returns
information about the currently registered license.

.. NOTE::

      Licenses were removed in CrateDB 4.5. Accordingly, these values are
      deprecated and return `NULL` in CrateDB 4.5 and higher.

``license``
-----------

+----------------------------+-----------------------------------------+------------------------------+
| Column Name                | Description                             | Return Type                  |
+============================+=========================================+==============================+
| ``license``                | The current CrateDB license information | ``OBJECT``                   |
|                            |  or NULL on CrateDB CE.                 |                              |
+----------------------------+-----------------------------------------+------------------------------+
| ``license['expiry_date']`` | The :ref:`data-types-dates-times`       | ``TIMESTAMP WITH TIME ZONE`` |
|                            | on which the license expires.           |                              |
+----------------------------+-----------------------------------------+------------------------------+
| ``license['issued_to']``   | The organisation for which the license  | ``TEXT``                     |
|                            | is issued.                              |                              |
+----------------------------+-----------------------------------------+------------------------------+
| ``license['max_nodes']``   | The maximum number of nodes the license | ``INTEGER``                  |
|                            | is valid for.                           |                              |
+----------------------------+-----------------------------------------+------------------------------+

.. _sys-cluster-settings:

Cluster settings
----------------

The ``sys.cluster.settings`` :ref:`expression <gloss-expression>` returns
information about the currently applied cluster settings.

::

    cr> select settings from sys.cluster;
    +-----------------------------------------------------...-+
    | settings                                                |
    +-----------------------------------------------------...-+
    | {"bulk": {...}, "cluster": {...}, "gateway": {...}, ... |
    +-----------------------------------------------------...-+
    SELECT 1 row in set (... sec)

::

    cr> select column_name, data_type from information_schema.columns
    ... where column_name like 'settings%'
    ... and table_name = 'cluster';
    +-----------------------------------------------------------------------------------+--------------+
    | column_name                                                                       | data_type    |
    +-----------------------------------------------------------------------------------+--------------+
    | settings                                                                          | object       |
    | settings['bulk']                                                                  | object       |
    | settings['bulk']['request_timeout']                                               | text         |
    | settings['cluster']                                                               | object       |
    | settings['cluster']['graceful_stop']                                              | object       |
    | settings['cluster']['graceful_stop']['force']                                     | boolean      |
    | settings['cluster']['graceful_stop']['min_availability']                          | text         |
    | settings['cluster']['graceful_stop']['timeout']                                   | text         |
    | settings['cluster']['info']                                                       | object       |
    | settings['cluster']['info']['update']                                             | object       |
    | settings['cluster']['info']['update']['interval']                                 | text         |
    | settings['cluster']['max_shards_per_node']                                        | integer      |
    | settings['cluster']['routing']                                                    | object       |
    | settings['cluster']['routing']['allocation']                                      | object       |
    | settings['cluster']['routing']['allocation']['allow_rebalance']                   | text         |
    | settings['cluster']['routing']['allocation']['balance']                           | object       |
    | settings['cluster']['routing']['allocation']['balance']['index']                  | real         |
    | settings['cluster']['routing']['allocation']['balance']['shard']                  | real         |
    | settings['cluster']['routing']['allocation']['balance']['threshold']              | real         |
    | settings['cluster']['routing']['allocation']['cluster_concurrent_rebalance']      | integer      |
    | settings['cluster']['routing']['allocation']['disk']                              | object       |
    | settings['cluster']['routing']['allocation']['disk']['threshold_enabled']         | boolean      |
    | settings['cluster']['routing']['allocation']['disk']['watermark']                 | object       |
    | settings['cluster']['routing']['allocation']['disk']['watermark']['flood_stage']  | text         |
    | settings['cluster']['routing']['allocation']['disk']['watermark']['high']         | text         |
    | settings['cluster']['routing']['allocation']['disk']['watermark']['low']          | text         |
    | settings['cluster']['routing']['allocation']['enable']                            | text         |
    | settings['cluster']['routing']['allocation']['exclude']                           | object       |
    | settings['cluster']['routing']['allocation']['exclude']['_host']                  | text         |
    | settings['cluster']['routing']['allocation']['exclude']['_id']                    | text         |
    | settings['cluster']['routing']['allocation']['exclude']['_ip']                    | text         |
    | settings['cluster']['routing']['allocation']['exclude']['_name']                  | text         |
    | settings['cluster']['routing']['allocation']['include']                           | object       |
    | settings['cluster']['routing']['allocation']['include']['_host']                  | text         |
    | settings['cluster']['routing']['allocation']['include']['_id']                    | text         |
    | settings['cluster']['routing']['allocation']['include']['_ip']                    | text         |
    | settings['cluster']['routing']['allocation']['include']['_name']                  | text         |
    | settings['cluster']['routing']['allocation']['node_concurrent_recoveries']        | integer      |
    | settings['cluster']['routing']['allocation']['node_initial_primaries_recoveries'] | integer      |
    | settings['cluster']['routing']['allocation']['require']                           | object       |
    | settings['cluster']['routing']['allocation']['require']['_host']                  | text         |
    | settings['cluster']['routing']['allocation']['require']['_id']                    | text         |
    | settings['cluster']['routing']['allocation']['require']['_ip']                    | text         |
    | settings['cluster']['routing']['allocation']['require']['_name']                  | text         |
    | settings['cluster']['routing']['allocation']['total_shards_per_node']             | integer      |
    | settings['cluster']['routing']['rebalance']                                       | object       |
    | settings['cluster']['routing']['rebalance']['enable']                             | text         |
    | settings['fdw']                                                                   | object       |
    | settings['fdw']['allow_local']                                                    | boolean      |
    | settings['gateway']                                                               | object       |
    | settings['gateway']['expected_data_nodes']                                        | integer      |
    | settings['gateway']['expected_nodes']                                             | integer      |
    | settings['gateway']['recover_after_data_nodes']                                   | integer      |
    | settings['gateway']['recover_after_nodes']                                        | integer      |
    | settings['gateway']['recover_after_time']                                         | text         |
    | settings['indices']                                                               | object       |
    | settings['indices']['breaker']                                                    | object       |
    | settings['indices']['breaker']['query']                                           | object       |
    | settings['indices']['breaker']['query']['limit']                                  | text         |
    | settings['indices']['breaker']['request']                                         | object       |
    | settings['indices']['breaker']['request']['limit']                                | text         |
    | settings['indices']['breaker']['total']                                           | object       |
    | settings['indices']['breaker']['total']['limit']                                  | text         |
    | settings['indices']['recovery']                                                   | object       |
    | settings['indices']['recovery']['internal_action_long_timeout']                   | text         |
    | settings['indices']['recovery']['internal_action_timeout']                        | text         |
    | settings['indices']['recovery']['max_bytes_per_sec']                              | text         |
    | settings['indices']['recovery']['recovery_activity_timeout']                      | text         |
    | settings['indices']['recovery']['retry_delay_network']                            | text         |
    | settings['indices']['recovery']['retry_delay_state_sync']                         | text         |
    | settings['indices']['replication']                                                | object       |
    | settings['indices']['replication']['retry_timeout']                               | text         |
    | settings['logger']                                                                | object_array |
    | settings['logger']['level']                                                       | text_array   |
    | settings['logger']['name']                                                        | text_array   |
    | settings['memory']                                                                | object       |
    | settings['memory']['allocation']                                                  | object       |
    | settings['memory']['allocation']['type']                                          | text         |
    | settings['memory']['operation_limit']                                             | integer      |
    | settings['overload_protection']                                                   | object       |
    | settings['overload_protection']['dml']                                            | object       |
    | settings['overload_protection']['dml']['initial_concurrency']                     | integer      |
    | settings['overload_protection']['dml']['max_concurrency']                         | integer      |
    | settings['overload_protection']['dml']['min_concurrency']                         | integer      |
    | settings['overload_protection']['dml']['queue_size']                              | integer      |
    | settings['replication']                                                           | object       |
    | settings['replication']['logical']                                                | object       |
    | settings['replication']['logical']['ops_batch_size']                              | integer      |
    | settings['replication']['logical']['reads_poll_duration']                         | text         |
    | settings['replication']['logical']['recovery']                                    | object       |
    | settings['replication']['logical']['recovery']['chunk_size']                      | text         |
    | settings['replication']['logical']['recovery']['max_concurrent_file_chunks']      | integer      |
    | settings['statement_timeout']                                                     | text         |
    | settings['stats']                                                                 | object       |
    | settings['stats']['breaker']                                                      | object       |
    | settings['stats']['breaker']['log']                                               | object       |
    | settings['stats']['breaker']['log']['jobs']                                       | object       |
    | settings['stats']['breaker']['log']['jobs']['limit']                              | text         |
    | settings['stats']['breaker']['log']['operations']                                 | object       |
    | settings['stats']['breaker']['log']['operations']['limit']                        | text         |
    | settings['stats']['enabled']                                                      | boolean      |
    | settings['stats']['jobs_log_expiration']                                          | text         |
    | settings['stats']['jobs_log_filter']                                              | text         |
    | settings['stats']['jobs_log_persistent_filter']                                   | text         |
    | settings['stats']['jobs_log_size']                                                | integer      |
    | settings['stats']['operations_log_expiration']                                    | text         |
    | settings['stats']['operations_log_size']                                          | integer      |
    | settings['stats']['service']                                                      | object       |
    | settings['stats']['service']['interval']                                          | text         |
    | settings['stats']['service']['max_bytes_per_sec']                                 | text         |
    | settings['udc']                                                                   | object       |
    | settings['udc']['enabled']                                                        | boolean      |
    | settings['udc']['initial_delay']                                                  | text         |
    | settings['udc']['interval']                                                       | text         |
    | settings['udc']['url']                                                            | text         |
    +-----------------------------------------------------------------------------------+--------------+
    SELECT ... rows in set (... sec)

For further details, see the :ref:`Cluster Settings <conf-cluster-settings>`
configuration section.

.. _sys-nodes:

Nodes
=====

To get information about the nodes query for ``sys.nodes``.

This table can be queried for one, multiple or all nodes within a cluster.

The table schema is as follows:

``id``
------

+-------------+---------------------------------------------+-------------+
| Column Name | Description                                 | Return Type |
+=============+=============================================+=============+
| ``id``      | A unique ID within the cluster generated by | ``TEXT``    |
|             | the system.                                 |             |
+-------------+---------------------------------------------+-------------+

``name``
--------

+-------------+-------------------------------------------------+-------------+
| Column Name | Description                                     | Return Type |
+=============+=================================================+=============+
| ``name``    | The node name within a cluster. The system will | ``TEXT``    |
|             | choose a random name. You can also customize    |             |
|             | the node name, see :ref:`conf-node-settings`.   |             |
+-------------+-------------------------------------------------+-------------+

``hostname``
------------

+--------------+-------------------------------------------------+-------------+
| Column Name  | Description                                     | Return Type |
+==============+=================================================+=============+
| ``hostname`` | The specified host name of the machine the node | ``TEXT``    |
|              | is running on.                                  |             |
+--------------+-------------------------------------------------+-------------+

``rest_url``
------------

+--------------+-----------------------------------------------------+-------------+
| Column Name  | Description                                         | Return Type |
+==============+=====================================================+=============+
| ``rest_url`` | Full HTTP(s) address where the REST API of the node | ``TEXT``    |
|              | is exposed, including schema, hostname (or IP)      |             |
|              | and port.                                           |             |
+--------------+-----------------------------------------------------+-------------+

``attributes``
--------------

+----------------+-----------------------------------------------------+-------------+
| Column Name    | Description                                         | Return Type |
+================+=====================================================+=============+
| ``attributes`` | The :ref:`custom attributes <conf-node-attributes>` | ``OBJECT``  |
|                | set for the node, e.g. if ``node.attr.color`` is    |             |
|                | ``blue``, and ``node.attr.location`` is ``east`,    |             |
|                | the value of this column would be:                  |             |
|                | ``{color=blue, location=east}``                     |             |
+----------------+-----------------------------------------------------+-------------+

``port``
--------

+-----------------------+-------------------------------------------------+-------------+
| Column Name           | Description                                     | Return Type |
+=======================+=================================================+=============+
| ``port``              | The specified ports for both HTTP and binary    | ``OBJECT``  |
|                       | transport interfaces. You can also customize    |             |
|                       | the ports setting, see :ref:`conf_ports`.       |             |
+-----------------------+-------------------------------------------------+-------------+
| ``port['http']``      | CrateDB's HTTP port.                            | ``INTEGER`` |
+-----------------------+-------------------------------------------------+-------------+
| ``port['transport']`` | CrateDB's binary transport port.                | ``INTEGER`` |
+-----------------------+-------------------------------------------------+-------------+
| ``port['psql']``      | The PostgreSQL wire protocol port.              | ``INTEGER`` |
+-----------------------+-------------------------------------------------+-------------+

``load``
--------

+-----------------------------+------------------------------------------+-----------------------+
| Column Name                 | Description                              | Return Type           |
+=============================+==========================================+=======================+
| ``load``                    | System load statistics                   | ``OBJECT``            |
+-----------------------------+------------------------------------------+-----------------------+
| ``load['1']``               | Average load over the last 1 minute.     | ``DOUBLE PRECISION``  |
+-----------------------------+------------------------------------------+-----------------------+
| ``load['5']``               | Average load over the last 5 minutes.    | ``DOUBLE PRECISION``  |
+-----------------------------+------------------------------------------+-----------------------+
| ``load['15']``              | Average load over the last 15 minutes.   | ``DOUBLE PRECISION``  |
+-----------------------------+------------------------------------------+-----------------------+
| ``load['probe_timestamp']`` | Unix timestamp at the time of collection | ``BIGINT``            |
|                             | of the load probe.                       |                       |
+-----------------------------+------------------------------------------+-----------------------+


``mem``
-------

+----------------------------+-------------------------------------------------+--------------+
| Column Name                | Description                                     | Return Type  |
+============================+=================================================+==============+
| ``mem``                    | Memory utilization statistics of the host.      | ``OBJECT``   |
+----------------------------+-------------------------------------------------+--------------+
| ``mem['used']``            | Currently used memory in bytes.                 | ``BIGINT``   |
+----------------------------+-------------------------------------------------+--------------+
| ``mem['used_percent']``    | Currently used memory in percent of total.      | ``SMALLINT`` |
+----------------------------+-------------------------------------------------+--------------+
| ``mem['free']``            | Currently available memory in bytes.            | ``BIGINT``   |
+----------------------------+-------------------------------------------------+--------------+
| ``mem['free_percent']``    | Currently available memory in percent of total. | ``SMALLINT`` |
+----------------------------+-------------------------------------------------+--------------+
| ``mem['probe_timestamp']`` | Unix timestamp at the time of collection        | ``BIGINT``   |
|                            | of the memory probe.                            |              |
+----------------------------+-------------------------------------------------+--------------+

``heap``
--------

+-----------------------------+------------------------------------------------+-------------+
| Column Name                 | Description                                    | Return Type |
+=============================+================================================+=============+
| ``heap``                    | Heap memory utilization statistics.            | ``OBJECT``  |
+-----------------------------+------------------------------------------------+-------------+
| ``heap['used']``            | Currently used heap memory in bytes.           | ``BIGINT``  |
+-----------------------------+------------------------------------------------+-------------+
| ``heap['max']``             | Maximum available heap memory. You can specify | ``BIGINT``  |
|                             | the max heap memory CrateDB should use in the  |             |
|                             | :ref:`config`.                                 |             |
+-----------------------------+------------------------------------------------+-------------+
| ``heap['free']``            | Currently available heap memory in bytes.      | ``BIGINT``  |
+-----------------------------+------------------------------------------------+-------------+
| ``heap['probe_timestamp']`` | Unix timestamp at the time of collection       | ``BIGINT``  |
|                             | of the heap probe.                             |             |
+-----------------------------+------------------------------------------------+-------------+

.. _sys-versions:

``version``
-----------

+----------------------------------------------------+---------------------------------------------------+-------------+
| Column Name                                        | Description                                       | Return Type |
+====================================================+===================================================+=============+
| ``version``                                        | CrateDB version information.                      | ``OBJECT``  |
+----------------------------------------------------+---------------------------------------------------+-------------+
| ``version['number']``                              | Version string in format ``"major.minor.hotfix"`` | ``TEXT``    |
+----------------------------------------------------+---------------------------------------------------+-------------+
| ``version['build_hash']``                          | SHA hash of the GitHub commit which               | ``TEXT``    |
|                                                    | this build was built from.                        |             |
+----------------------------------------------------+---------------------------------------------------+-------------+
| ``version['build_snapshot']``                      | Indicates whether this build is a snapshot build. | ``BOOLEAN`` |
+----------------------------------------------------+---------------------------------------------------+-------------+
| ``version['minimum_index_compatibility_version']`` | Indicates the minimum compatible index version    | ``TEXT``    |
|                                                    | which is supported.                               |             |
+----------------------------------------------------+---------------------------------------------------+-------------+
| ``version['minimum_wire_compatibility_version']``  | Indicates the minimum compatible wire protocol    | ``TEXT``    |
|                                                    | version which is supported.                       |             |
+----------------------------------------------------+---------------------------------------------------+-------------+

``cluster_state_version``
-------------------------

+--------------------------------+-----------------------------------------------+-------------+
| Column Name                    | Description                                   | Return Type |
+================================+===============================================+=============+
| ``cluster_state_version``      | The current version of the cluster state. The | ``BIGINT``  |
|                                | cluster state is an immutable structure and   |             |
|                                | that is recreated when a change is published. |             |
+--------------------------------+-----------------------------------------------+-------------+

``fs``
------

+----------------------------------+------------------------------------------------+-------------+
| Column Name                      | Description                                    | Return Type |
+==================================+================================================+=============+
| ``fs``                           | Utilization statistics about the file system.  | ``OBJECT``  |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['total']``                  | Aggregated usage statistic of all disks on the | ``OBJECT``  |
|                                  | host.                                          |             |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['total']['size']``          | Total size of all disks in bytes.              | ``BIGINT``  |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['total']['used']``          | Total used space of all disks in bytes.        | ``BIGINT``  |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['total']['available']``     | Total available space of all disks in bytes.   | ``BIGINT``  |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['total']['reads']``         | Total number of reads on all disks.            | ``BIGINT``  |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['total']['bytes_read']``    | Total size of reads on all disks in bytes.     | ``BIGINT``  |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['total']['writes']``        | Total number of writes on all disks.           | ``BIGINT``  |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['total']['bytes_written']`` | Total size of writes on all disks in bytes.    | ``BIGINT``  |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['disks']``                  | Usage statistics of individual disks on the    | ``ARRAY``   |
|                                  | host.                                          |             |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['disks']['dev']``           | Device name                                    | ``TEXT``    |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['disks']['size']``          | Total size of the disk in bytes.               | ``BIGINT``  |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['disks']['used']``          | Used space of the disk in bytes.               | ``BIGINT``  |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['disks']['available']``     | Available space of the disk in bytes.          | ``BIGINT``  |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['data']``                   | Information about data paths used by the node. | ``ARRAY``   |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['data']['dev']``            | Device name                                    | ``TEXT``    |
+----------------------------------+------------------------------------------------+-------------+
| ``fs['data']['path']``           | File path where the data of the node resides.  | ``TEXT``    |
+----------------------------------+------------------------------------------------+-------------+

``thread_pools``
----------------

+-------------------------------+------------------------------------------------+-------------+
| Column Name                   | Description                                    | Return Type |
+===============================+================================================+=============+
| ``thread_pools``              | Usage statistics of Java thread pools.         | ``ARRAY``   |
+-------------------------------+------------------------------------------------+-------------+
| ``thread_pools['name']``      | Name of the pool.                              | ``TEXT``    |
+-------------------------------+------------------------------------------------+-------------+
| ``thread_pools['active']``    | Number of currently running thread in the      | ``INTEGER`` |
|                               | thread pool.                                   |             |
+-------------------------------+------------------------------------------------+-------------+
| ``thread_pools['rejected']``  | Total number of rejected threads in the thread | ``BIGINT``  |
|                               | pool.                                          |             |
+-------------------------------+------------------------------------------------+-------------+
| ``thread_pools['largest']``   | Largest number of threads that have ever       | ``INTEGER`` |
|                               | simultaneously been in the pool.               |             |
+-------------------------------+------------------------------------------------+-------------+
| ``thread_pools['completed']`` | Total number of completed thread in the thread | ``BIGINT``  |
|                               | pool.                                          |             |
+-------------------------------+------------------------------------------------+-------------+
| ``thread_pools['threads']``   | Size of the thread pool.                       | ``INTEGER`` |
+-------------------------------+------------------------------------------------+-------------+
| ``thread_pools['queue']``     | Number of thread currently in the queue.       | ``INTEGER`` |
+-------------------------------+------------------------------------------------+-------------+

``os``
------

+-------------------------------------------------+------------------------------------------------------+--------------+
| Column Name                                     | Description                                          | Return Type  |
+=================================================+======================================================+==============+
| ``os``                                          | Operating system stats                               | ``OBJECT``   |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['uptime']``                                | System uptime in milliseconds                        | ``BIGINT``   |
|                                                 |                                                      |              |
|                                                 | Requires allowing system calls on Windows and macOS. |              |
|                                                 | See notes in :ref:`os_uptime_limitations`.           |              |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['timestamp']``                             | UNIX timestamp in millisecond resolution             | ``BIGINT``   |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cpu']``                                   | Information about CPU utilization                    | ``OBJECT``   |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cpu']['used']``                           | System CPU usage as percentage                       | ``SMALLINT`` |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['probe_timestamp']``                       | Unix timestamp at the time of collection             | ``BIGINT``   |
|                                                 | of the OS probe.                                     |              |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']``                                | Information about cgroups **(Linux only)**           | ``OBJECT``   |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['cpuacct']``                     | Information about CPU accounting                     | ``OBJECT``   |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['cpuacct']['control_group']``    | The path to the CPU accounting cgroup                | ``TEXT``     |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['cpuacct']['usage_nanos']``      | The total CPU time (in nanoseconds) consumed by      | ``BIGINT``   |
|                                                 | all tasks in this cgroup.                            |              |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['cpu']``                         | Information about the CPU subsystem                  | ``OBJECT``   |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['cpu']['control_group']``        | The path to the CPU cgroup                           | ``TEXT``     |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['cpu']['cfs_period_micros']``    | The period of time (in microseconds) the cgroup      | ``BIGINT``   |
|                                                 | access to the CPU gets reallocated.                  |              |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['cpu']['cfs_quota_micros']``     | The total amount of time (in microseconds) for which | ``BIGINT``   |
|                                                 | all tasks in the cgroup can run during one period    |              |
|                                                 | (cfs_period_micros).                                 |              |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['cpu']['num_elapsed_periods']``  | The nr. of period intervals (cfs_period_micros) that | ``BIGINT``   |
|                                                 | have elapsed.                                        |              |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['cpu']['num_times_throttled']``  | The nr. of times tasks in the cgroup have been       | ``BIGINT``   |
|                                                 | throttled.                                           |              |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['cpu']['time_throttled_nanos']`` | The total time (in nanoseconds) for which tasks in   | ``BIGINT``   |
|                                                 | the cgroup have been throttled.                      |              |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['mem']``                         | Information about memory resources used by tasks in  | ``OBJECT``   |
|                                                 | a cgroup.                                            |              |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['mem']['control_group']``        | The path to the memory cgroup                        | ``TEXT``     |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['mem']['usage_bytes']``          | The total current memory usage by processes in       | ``TEXT``     |
|                                                 | the cgroup.                                          |              |
+-------------------------------------------------+------------------------------------------------------+--------------+
| ``os['cgroup']['mem']['limit_bytes']``          | The max. amount of user memory in the cgroup.        | ``TEXT``     |
+-------------------------------------------------+------------------------------------------------------+--------------+

The CPU information values are cached for 1s. They might differ from the actual
values at query time. Use the probe timestamp to get the time of collection.
When analyzing the CPU usage over time, always use ``os['probe_timestamp']`` to
calculate the time difference between 2 probes.

.. _os_cgroup_limitations:

cgroup limitations
..................

.. NOTE::

    cgroup metrics only work if the stats are available from
    ``/sys/fs/cgroup/cpu`` and ``/sys/fs/cgroup/cpuacct``.

.. _os_uptime_limitations:

Uptime limitations
..................

.. NOTE::

    os['uptime'] required a system call when running CrateDB on Windows or
    macOS, however, system calls are not permitted by default. If you require
    this metric you need to allow system calls by setting ``bootstrap.seccomp``
    to ``false``. This setting must be set in the crate.yml or via command line
    argument and cannot be changed at runtime.

``os_info``
-----------

+-------------------------------------+----------------------------------------------+-------------+
| Column Name                         | Description                                  | Return Type |
+=====================================+==============================================+=============+
| ``os_info``                         | Operating system information                 | ``OBJECT``  |
+-------------------------------------+----------------------------------------------+-------------+
| ``os_info['available_processors']`` | Number of processors that are available in   | ``INTEGER`` |
|                                     | the JVM. This is usually equal to the number |             |
|                                     | of cores of the CPU.                         |             |
+-------------------------------------+----------------------------------------------+-------------+
| ``os_info['name']``                 | Name of the operating system (ex: Linux,     | ``TEXT``    |
|                                     | Windows, macOS)                              |             |
+-------------------------------------+----------------------------------------------+-------------+
| ``os_info['arch']``                 | Name of the JVM architecture (ex: amd64,     | ``TEXT``    |
|                                     | x86)                                         |             |
+-------------------------------------+----------------------------------------------+-------------+
| ``os_info['version']``              | Version of the operating system              | ``TEXT``    |
+-------------------------------------+----------------------------------------------+-------------+
| ``os_info['jvm']``                  | Information about the JVM (Java Virtual      | ``OBJECT``  |
|                                     | Machine)                                     |             |
+-------------------------------------+----------------------------------------------+-------------+
| ``os_info['jvm']['version']``       | The JVM version                              | ``TEXT``    |
+-------------------------------------+----------------------------------------------+-------------+
| ``os_info['jvm']['vm_name']``       | The name of the JVM (e.g. OpenJDK,           | ``TEXT``    |
|                                     | Java HotSpot(TM) )                           |             |
+-------------------------------------+----------------------------------------------+-------------+
| ``os_info['jvm']['vm_vendor']``     | The vendor name of the JVM                   | ``TEXT``    |
+-------------------------------------+----------------------------------------------+-------------+
| ``os_info['jvm']['vm_version']``    | The version of the JVM                       | ``TEXT``    |
+-------------------------------------+----------------------------------------------+-------------+

.. _sys-nodes-connections:

``connections``
---------------

+----------------------------------+-----------------+-----------------+
| Column Name                      | Description     | Return Type     |
+==================================+=================+=================+
| ``http``                         | Number of       | ``OBJECT``      |
|                                  | connections     |                 |
|                                  | established via |                 |
|                                  | HTTP            |                 |
+----------------------------------+-----------------+-----------------+
| ``http['open']``                 | The currently   | ``BIGINT``      |
|                                  | open            |                 |
|                                  | connections     |                 |
|                                  | established via |                 |
|                                  | HTTP            |                 |
+----------------------------------+-----------------+-----------------+
| ``http['total']``                | The total       | ``BIGINT``      |
|                                  | number of       |                 |
|                                  | connections     |                 |
|                                  | that have been  |                 |
|                                  | established via |                 |
|                                  | HTTP over the   |                 |
|                                  | life time of a  |                 |
|                                  | CrateDB node    |                 |
+----------------------------------+-----------------+-----------------+
| ``psql``                         | Number of       | ``OBJECT``      |
|                                  | connections     |                 |
|                                  | established via |                 |
|                                  | PostgreSQL      |                 |
|                                  | protocol        |                 |
+----------------------------------+-----------------+-----------------+
| ``psql['open']``                 | The currently   | ``BIGINT``      |
|                                  | open            |                 |
|                                  | connections     |                 |
|                                  | established via |                 |
|                                  | PostgreSQL      |                 |
|                                  | protocol        |                 |
+----------------------------------+-----------------+-----------------+
| ``psql['total']``                | The total       | ``BIGINT``      |
|                                  | number of       |                 |
|                                  | connections     |                 |
|                                  | that have been  |                 |
|                                  | established via |                 |
|                                  | PostgreSQL      |                 |
|                                  | protocol over   |                 |
|                                  | the life time   |                 |
|                                  | of a CrateDB    |                 |
|                                  | node            |                 |
+----------------------------------+-----------------+-----------------+
| ``transport``                    | Number of       | ``OBJECT``      |
|                                  | connections     |                 |
|                                  | established via |                 |
|                                  | Transport       |                 |
|                                  | protocol        |                 |
+----------------------------------+-----------------+-----------------+
| ``transport['open']``            | The currently   | ``BIGINT``      |
|                                  | open            |                 |
|                                  | connections     |                 |
|                                  | established via |                 |
|                                  | Transport       |                 |
|                                  | protocol        |                 |
+----------------------------------+-----------------+-----------------+
| ``transport['total']``           | The total       | ``BIGINT``      |
|                                  | number of       |                 |
|                                  | connections     |                 |
|                                  | that have been  |                 |
|                                  | established via |                 |
|                                  | Transport       |                 |
|                                  | protocol over   |                 |
|                                  | the life time   |                 |
|                                  | of a CrateDB    |                 |
|                                  | node            |                 |
+----------------------------------+-----------------+-----------------+

``process``
-----------

+------------------------------------------+------------------------------------------------+--------------+
| Column Name                              | Description                                    | Return Type  |
+==========================================+================================================+==============+
| ``process``                              | Statistics about the CrateDB process.          | ``OBJECT``   |
+------------------------------------------+------------------------------------------------+--------------+
| ``process['open_file_descriptors']``     | Number of currently open file descriptors used | ``BIGINT``   |
|                                          | by the CrateDB process.                        |              |
+------------------------------------------+------------------------------------------------+--------------+
| ``process['max_open_file_descriptors']`` | The maximum number of open file descriptors    | ``BIGINT``   |
|                                          | CrateDB can use.                               |              |
+------------------------------------------+------------------------------------------------+--------------+
| ``process['probe_timestamp']``           | The system UNIX timestamp at the moment of     | ``BIGINT``   |
|                                          | the probe collection.                          |              |
+------------------------------------------+------------------------------------------------+--------------+
| ``process['cpu']``                       | Information about the CPU usage of the CrateDB | ``OBJECT``   |
|                                          | process.                                       |              |
+------------------------------------------+------------------------------------------------+--------------+
| ``process['cpu']['percent']``            | The CPU usage of the CrateDB JVM process given | ``SMALLINT`` |
|                                          | in percent.                                    |              |
+------------------------------------------+------------------------------------------------+--------------+

The CPU information values are cached for 1s. They might differ from the actual
values at query time. Use the probe timestamp to get the time of the collect.
When analyzing the CPU usage over time, always use
``process['probe_timestamp']`` to calculate the time difference between 2
probes.

.. NOTE::

    If one of the queried nodes is not responding within three seconds it
    returns ``null`` every column except ``id`` and ``name``. This behaviour
    could be used to detect hanging nodes.

.. _sys-node-checks:

Node checks
===========

The table ``sys.node_checks`` exposes a list of internal node checks and
results of their validation.

The table schema is the following:

+------------------+----------------------------------+--------------+
| Column Name      | Description                      | Return Type  |
+==================+==================================+==============+
| ``id``           | The unique check ID.             | ``INTEGER``  |
+------------------+----------------------------------+--------------+
| ``node_id``      | The unique node ID.              | ``TEXT``     |
+------------------+----------------------------------+--------------+
| ``severity``     | The level of severity.           | ``INTEGER``  |
|                  | The higher the value of the      |              |
|                  | field the higher severity.       |              |
+------------------+----------------------------------+--------------+
| ``description``  | The description message for the  | ``TEXT``     |
|                  | setting check.                   |              |
+------------------+----------------------------------+--------------+
| ``passed``       | The flag determines whether the  | ``BOOLEAN``  |
|                  | check for the setting has passed.|              |
+------------------+----------------------------------+--------------+
| ``acknowledged`` | The flag determines whether the  | ``BOOLEAN``  |
|                  | check for this setting has been  |              |
|                  | acknowledged by the user in      |              |
|                  | order to ignored the value of    |              |
|                  | ``passed`` column. This column   |              |
|                  | can be *updated*.                |              |
+------------------+----------------------------------+--------------+

Example query::

  cr> select id, node_id, description from sys.node_checks order by id, node_id;
  +----+---------...-+--------------------------------------------------------------...-+
  | id | node_id     | description                                                      |
  +----+---------...-+--------------------------------------------------------------...-+
  |  1 | ...         | It has been detected that the 'gateway.expected_data_nodes' s... |
  |  2 | ...         | The cluster setting 'gateway.recover_after_data_nodes' (or th... |
  |  3 | ...         | If any of the "expected data nodes" recovery settings are set... |
  |  5 | ...         | The high disk watermark is exceeded on the node. The cluster ... |
  |  6 | ...         | The low disk watermark is exceeded on the node. The cluster w... |
  |  7 | ...         | The flood stage disk watermark is exceeded on the node. Table... |
  |  8 | ...         | The amount of shards on the node reached 90 % of the limit of... |
  +----+---------...-+--------------------------------------------------------------...-+
  SELECT 7 rows in set (... sec)

.. _sys-node-checks-ack:

Acknowledge failed checks
-------------------------

It is possible to acknowledge every check by updating the ``acknowledged``
column. By doing this, specially CrateDB's built-in Admin UI won't complain
anymore about failing checks.

Imagine we've added a new node to our cluster, but as the
:ref:`gateway.expected_data_nodes <gateway.expected_data_nodes>` column can
only be set via config-file or command-line argument, the check for this
setting will not pass on the already running nodes until the config-file or
command-line argument on these nodes is updated and the nodes are restarted
(which is not what we want on a healthy well running cluster).

In order to make the Admin UI accept a failing check (so the checks label goes
green again), we must acknowledge this check by updating it's ``acknowledged``
flag::

  cr> update sys.node_checks set acknowledged = true where id = 1;
  UPDATE OK, 1 row affected (... sec)

.. CAUTION::

   Updates on this column are transient, so changed values are lost after the
   affected node is restarted.

.. _sys-node-checks-settings:

Description of checked node settings
------------------------------------

.. raw:: html

  <span id="recovery-expected-nodes"></span>

Recovery expected data nodes
............................

This check looks at the
:ref:`gateway.expected_data_nodes <gateway.expected_data_nodes>` setting and
checks if its value matches the actual number of data nodes present in the
cluster.
If the actual number of nodes is below the expected number, the warning is
raised to indicate some nodes are down.
If the actual number is greater, this is flagged to indicate the setting
should be updated.

.. NOTE::

   For backward compatibility, setting the deprecated
   :ref:`gateway.expected_nodes <gateway.expected_nodes>` instead is still
   supported. It counts all nodes, not only
   :ref:`data-carrying nodes <node.data>`.

.. raw:: html

  <span id="recovery-after-nodes"></span>

Recovery after data nodes
.........................

This check looks at the
:ref:`gateway.recover_after_data_nodes <gateway.recover_after_data_nodes>`
setting and checks if its value is greater than half the configured expected
number, but not greater than the configured expected number.

.. NOTE::

   For backward compatibility, setting the deprecated
   :ref:`gateway.recover_after_nodes <gateway.recover_after_nodes>` instead
   is still supported.

::

  (E / 2) < R <= E

Here, ``R`` is the number of :ref:`recovery <gloss-shard-recovery>` nodes and
``E`` is the number of expected (data) nodes.

If recovery is started when some nodes are down, CrateDB proceeds on the
basis the nodes that are down may not be coming back, and it will create new
replicas and rebalance shards as necessary. This is throttled, and it can be
controlled with :ref:`routing allocation settings <conf_routing>`, but
depending on the context, you may prefer to delay recovery if the nodes are
only down for a short period of time, so it is advisable to review the
documentation around :ref:`the settings involved <metadata_gateway>` and
configure them carefully.

Recovery after time
...................

If :ref:`gateway.recover_after_data_nodes <gateway.recover_after_data_nodes>`
is set, then :ref:`gateway.recover_after_time <gateway.recover_after_time>`
must not be set to ``0s``, otherwise the ``gateway.recover_after_data_nodes``
setting wouldn't have any effect.

.. NOTE::

   For backward compatibility, setting the deprecated
   :ref:`gateway.recover_after_nodes <gateway.recover_after_nodes>` instead
   is still supported.

.. _sys-node_checks_watermark_high:

Routing allocation disk watermark high
......................................

The check for the :ref:`cluster.routing.allocation.disk.watermark.high
<cluster.routing.allocation.disk.watermark.high>` setting verifies that the
high watermark is not exceeded on the current node. The usage of each disk for
configured CrateDB data paths is verified against the threshold setting. If one
or more verification fails the check is marked as not passed.


.. _sys-node_checks_watermark_low:

Routing allocation disk watermark low
.....................................

The check for the :ref:`cluster.routing.allocation.disk.watermark.low
<cluster.routing.allocation.disk.watermark.low>` which controls the low
watermark for the node disk usage. The check verifies that the low watermark is
not exceeded on the current node. The verification is done against each disk
for configured CrateDB data paths. The check is not passed if the verification
for one or more disk fails.

.. _sys-node_checks_max_shards_per_node:

Maximum shards per node
.......................

The check verifies that the amount of shards on the current node is less
than 90 percent of :ref:`cluster.max_shards_per_node
<cluster.max_shards_per_node>`. Creating new tables or partitions which would
push the number of shards beyond 100 % of the limit will be rejected.


.. _sys-shards:

Shards
======

The table ``sys.shards`` contains real-time statistics for all shards of all
(non-system) tables.

Table schema
------------

.. list-table::
    :header-rows: 1

    * - Column Name
      - Description
      - Return Type
    * - ``node``
      - Information about the node the shard is located at.
      - ``OBJECT``
    * - ``node['name']``
      - The name of the node the shard is located at.
      - ``TEXT``
    * - ``node['id']``
      - The id of the node the shard is located at.
      - ``TEXT``
    * - ``blob_path``
      - Path to the directory which contains the blob files of the shard, or
        null if the shard is not a blob shard.
      - ``TEXT``
    * - ``id``
      - The shard id.
        This shard id is managed by the system, ranging from 0 up to the number
        of configured shards of the table.
      - ``INTEGER``
    * - ``min_lucene_version``
      - Shows the oldest Lucene segment version used in this shard.
      - ``TEXT``
    * - ``num_docs``
      - The total amount of documents within a shard.
      - ``BIGINT``
    * - ``oprhan_partition``
      - True if this shard belongs to an orphaned partition which doesn't belong to any table anymore.
      - ``BOOLEAN``
    * - ``partition_ident``
      - The partition ident of a partitioned table. Empty for non-partitioned tables.
      - ``TEXT``
    * - ``partition_uuid``
      - Internal id for the storage partition the shard is part of. Even tables
        without ``PARTITIONED BY`` clause have an internal storage partition.
      - ``TEXT``
    * - ``path``
      - Path to the shard directory on the filesystem. This directory contains state and index files.
      - ``TEXT``
    * - ``primary``
      - Indicates if this shard is the primary shard.
      - ``BOOLEAN``
    * - ``recovery``
      - :ref:`Recovery <gloss-shard-recovery>` statistics for a shard.
      - ``OBJECT``
    * - ``recovery['files']``
      - File recovery statistics
      - ``OBJECT``
    * - ``recovery['files']['percent']``
      - Percentage of files already recovered.
      - ``REAL``
    * - ``recovery['files']['recovered']``
      - Number of files recovered in the shard. Includes both existing and reused files.
      - ``INTEGER``
    * - ``recovery['files']['reused']``
      - Total number of files reused from a local copy while recovering the shard.
      - ``INTEGER``
    * - ``recovery['files']['used']``
      - Total number of files in the shard.
      - ``INTEGER``
    * - ``recovery['size']``
      - Recovery statistics for the shard in bytes
      - ``OBJECT``
    * - ``recovery['size']['percent']``
      - Percentage of bytes already recovered
      - ``REAL``
    * - ``recovery['size']['recovered']``
      - Number of bytes recovered. Includes both existing and re-used bytes.
      - ``BIGINT``
    * - ``recovery['size']['reused']``
      - Number of bytes re-used from a local copy while recovering the shard.
      - ``BIGINT``
    * - ``recovery['size']['used']``
      - Total number of bytes in the shard.
      - ``BIGINT``
    * - ``recovery['stage']``
      - Recovery stage:

        * init: Recovery has not started
        * index: Reading the Lucene index meta-data and copying bytes from source to destination
        * start: Starting the engine, opening the index for use
        * translog: Replaying transaction log
        * finalize: Cleanup
        * done: Complete
      - ``TEXT``
    * - ``recovery['total_time']``
      - Returns elapsed time from the start of the shard recovery.
      - ``BIGINT``
    * - ``recovery['type']``
      - Recovery type:

        * gateway
        * snapshot
        * replica
        * relocating
      - ``TEXT``
    * - ``relocating_node``
      - The id of the node to which the shard is getting relocated to.
      - ``TEXT``
    * - ``routing_state``
      - The current state of the shard in the routing table.
        Possible states are:

        * UNASSIGNED
        * INITIALIZING
        * STARTED
        * RELOCATING
      - ``TEXT``
    * - ``schema_name``
      - The schema name of the table the shard belongs to
      - ``TEXT``
    * - ``size``
      - The current size in bytes. This value is cached for a short period and
        may return slightly outdated values.
      - ``BIGINT``
    * - ``state``
      - The current state of the shard.
        Possible states are:

        * CREATED
        * RECOVERING
        * POST_RECOVERY
        * STARTED
        * RELOCATED
        * CLOSED
        * INITIALIZING
        * UNASSIGNED
      - ``TEXT``
    * - ``closed``
      - The state of the table associated with the shard.
      - ``BOOLEAN``
    * - ``table_name``
      - The name of the table this shard belongs to
      - ``TEXT``
    * - ``seq_no_stats``
      - Contains information about internal sequence numbering and checkpoints
        for these sequence numbers.
      - ``OBJECT``
    * - ``seq_no_stats['max_seq_no']``
      - The highest sequence number that has been issued so far on the shard.
      - ``BIGINT``
    * - ``seq_no_stats['local_checkpoint']``
      - The highest sequence number for which all lower sequence number of been
        processed on this shard. Due to concurrent indexing this can be lower
        than max_seq_no.
      - ``BIGINT``
    * - ``seq_no_stats['global_checkpoint']``
      - The highest sequence number for which the local shard can guarantee
        that all lower sequence numbers have been processed on all active shard
        copies.
      - ``BIGINT``
    * - ``translog_stats``
      - Contains information for the translog of the shard.
      - ``OBJECT``
    * - ``translog_stats['size']``
      - The current size of the translog file in bytes.
      - ``BIGINT``
    * - ``translog_stats['uncommitted_size']``
      - The size in bytes of the translog that has not been committed to Lucene yet.
      - ``BIGINT``
    * - ``translog_stats['number_of_operations']``
      - The number of operations recorded in the translog.
      - ``INTEGER``
    * - ``translog_stats['uncommitted_operations']``
      - The number of operations in the translog which have not been committed to Lucene yet.
      - ``INTEGER``
    * - ``retention_leases``
      - Versioned collection of retention leases.
      - ``OBJECT``
    * - ``flush_stats``
      - Flush information. Shard relocation resets this information.
      - ``OBJECT``
    * - ``flush_stats['count']``
      - The total amount of flush operations that happened on the shard.
      - ``BIGINT``
    * - ``flush_stats['periodic_count']``
      - The number of periodic flushes. Each periodic flush also counts as a
        regular flush. A periodic flush can happen after writes depending on
        settings like the translog flush threshold.
      - ``BIGINT``
    * - ``flush_stats['total_time_ns']``
      - The total time spent on flush operations on the shard.
      - ``BIGINT``


.. NOTE::

   The ``sys.shards`` table is subject to :ref:`shard_table_permissions`.


Example
-------

For example, you can query shards like this::

  cr> select schema_name as schema,
  ...   table_name as t,
  ...   id,
  ...   partition_ident as p_i,
  ...   num_docs as docs,
  ...   primary,
  ...   relocating_node as r_n,
  ...   routing_state as r_state,
  ...   state,
  ...   orphan_partition as o_p
  ... from sys.shards where table_name = 'locations' and id = 1;
  +--------+-----------+----+-----+------+---------+------+---------+---------+-------+
  | schema | t         | id | p_i | docs | primary | r_n  | r_state |  state  | o_p   |
  +--------+-----------+----+-----+------+---------+------+---------+---------+-------+
  | doc    | locations |  1 |     |    4 | TRUE    | NULL | STARTED | STARTED | FALSE |
  +--------+-----------+----+-----+------+---------+------+---------+---------+-------+
  SELECT 1 row in set (... sec)

.. _sys-segments:

Segments
========

The ``sys.segments`` table contains information about the Lucene segments
of the shards.

The segment information is useful to understand the behaviour of the underlying
Lucene file structures for troubleshooting and performance optimization
of shards.

.. list-table::
    :header-rows: 1

    * - Column Name
      - Description
      - Return Type
    * - ``segment_name``
      - Name of the segment, derived from the segment generation and used
        internally to create file names in the directory of the shard.
      - ``TEXT``
    * - ``shard_id``
      - ID of the effected shard.
      - ``INTEGER``
    * - ``table_schema``
      - Schema name of the table of the shard.
      - ``TEXT``
    * - ``table_name``
      - Table name of the shard.
      - ``TEXT``
    * - ``partition_ident``
      - The partition ident of a partitioned table. Empty for non-partitioned tables.
      - ``TEXT``
    * - ``node``
      - Information about the node the shard is located at.
      - ``OBJECT``
    * - ``node['name']``
      - The name of the node the shard is located at.
      - ``TEXT``
    * - ``node['id']``
      - The id of the node the shard is located at.
      - ``TEXT``
    * - ``generation``
      - Generation number of the segment, increments for each segment written.
      - ``LONG``
    * - ``num_docs``
      - Number of non-deleted Lucene documents in this segment.
      - ``INTEGER``
    * - ``deleted_docs``
      - Number of deleted Lucene documents in this segment.
      - ``INTEGER``
    * - ``size``
      - Disk space used by the segment in bytes.
      - ``LONG``
    * - ``memory``
      - Unavailable starting from CrateDB 5.0.  Always returns -1.
      - ``LONG``
    * - ``committed``
      - Indicates if the segments are synced to disk. Segments that are synced
        can survive a hard reboot.
      - ``BOOLEAN``
    * - ``primary``
      - Describes if this segment is part of a primary shard.
      - ``BOOLEAN``
    * - ``search``
      - Indicates if the segment is searchable. If ``false``, the segment has
        most likely been written to disk but needs a refresh to be searchable.
      - ``BOOLEAN``
    * - ``version``
      - Version of Lucene used to write the segment.
      - ``TEXT``
    * - ``compound``
      - If ``true``, Lucene merges all files from the segment into a single
        file to save file descriptors.
      - ``BOOLEAN``
    * - ``attributes``
      - Contains information about whether high compression was enabled.
      - ``OBJECT``

.. NOTE::

    The information in the ``sys.segments`` table is expensive to calculate and
    therefore this information should be retrieved with awareness that it can
    have performance implications on the cluster.

.. NOTE::

    The ``sys.shards`` table is subject to :ref:`shard_table_permissions`.

.. _jobs_operations_logs:

Jobs, operations, and logs
==========================

To let you inspect the activities currently taking place in a cluster, CrateDB
provides system tables that let you track current cluster jobs and operations.
See :ref:`Jobs Table <sys-jobs>` and :ref:`Operations Table<sys-operations>`.

Jobs and operations that finished executing are additionally recorded in
memory. There are two retention policies available to control how many records
should be kept.

One option is to configure the maximum number of records which should be kept.
Once the configured table size is reached, the older log records are deleted as
newer records are added. This is configurable using :ref:`stats.jobs_log_size
<stats.jobs_log_size>` and :ref:`stats.operations_log_size
<stats.operations_log_size>`.

Another option is to configure an expiration time for the records. In this
case, the records in the logs tables are periodically cleared if they are older
than the expiry time. This behaviour is configurable using
:ref:`stats.jobs_log_expiration <stats.jobs_log_expiration>` and
:ref:`stats.operations_log_expiration <stats.operations_log_expiration>`.

In addition to these retention policies, there is a memory limit in place
preventing these tables from taking up too much memory. The amount of memory
that can be used to store the jobs can be configured using
:ref:`stats.breaker.log.jobs.limit <stats.breaker.log.jobs.limit>` and
:ref:`stats.breaker.log.operations.limit <stats.breaker.log.operations.limit>`.
If the memory limit is reached, an error message will be logged and the log
table will be cleared completely.

It is also possible to define a filter which must match for jobs to be recorded
after they finished executing. This can be useful to only record slow queries
or queries that failed due to an error. This filter can be configured using the
:ref:`stats.jobs_log_filer <stats.jobs_log_filter>` setting.

Furthermore, there is a second filter setting which also results in a log entry
in the regular CrateDB log file for all finished jobs that match this filter.
This can be configured using :ref:`stats.jobs_log_persistent_filter
<stats.jobs_log_persistent_filter>`. This could be used to create a persistent
slow query log.


.. _sys-jobs:

Jobs
----

The ``sys.jobs`` table is a constantly updated view of all jobs that are
currently being executed in the cluster.

Table schema
............

+------------------+--------------------------------------------------+------------------------------+
| Column Name      | Description                                      |  Return Type                 |
+==================+==================================================+==============================+
| ``id``           | The job UUID.                                    | ``TEXT``                     |
|                  |                                                  |                              |
|                  | This job ID is generated by the system.          |                              |
+------------------+--------------------------------------------------+------------------------------+
| ``node``         | Information about the node that created the job. | ``OBJECT``                   |
+------------------+--------------------------------------------------+------------------------------+
| ``node['id']``   | The id of the node.                              | ``TEXT``                     |
+------------------+--------------------------------------------------+------------------------------+
| ``node['name']`` | The name of the node.                            | ``TEXT``                     |
+------------------+--------------------------------------------------+------------------------------+
| ``started``      | The point in time when the job started.          | ``TIMESTAMP WITH TIME ZONE`` |
+------------------+--------------------------------------------------+------------------------------+
| ``stmt``         | Shows the data query or manipulation statement   | ``TEXT``                     |
|                  | represented by this job.                         |                              |
+------------------+--------------------------------------------------+------------------------------+
| ``username``     | The user who is executing the statement.         | ``TEXT``                     |
+------------------+--------------------------------------------------+------------------------------+

The field ``username`` corresponds to the :ref:`SESSION_USER <scalar-session_user>`
that is performing the query::

    cr> select stmt, username, started from sys.jobs where stmt like 'sel% from %jobs%';
    +---------------------------------------------------------------------------------+----------+-...-----+
    | stmt                                                                            | username | started |
    +---------------------------------------------------------------------------------+----------+-...-----+
    | select stmt, username, started from sys.jobs where stmt like 'sel% from %jobs%' | crate    | ...     |
    +---------------------------------------------------------------------------------+----------+-...-----+
    SELECT 1 row in set (... sec)

.. NOTE::

    If the user management module is not available, the ``username`` is
    given as ``crate``.

Every request that queries data or manipulates data is considered a "job" if it
is a valid query. Requests that are not valid queries (for example, a request
that tries to query a non-existent table) will not show up as jobs.

.. NOTE::

   The ``sys.jobs`` table is subject to :ref:`jobs_table_permissions`.

.. _sys-jobs-metrics:

Jobs metrics
------------

The ``sys.jobs_metrics`` table provides an overview of the query latency in the
cluster. Jobs metrics are not persisted across node restarts.

The metrics are aggregated for each node and each unique classification of the
statements.

.. note::

  In order to reduce the memory requirements for these metrics, the times are
  statistically sampled and therefore may have slight inaccuracies.
  In addition, durations are only tracked up to 10 minutes. Statements taking
  longer than that are capped to 10 minutes.


``sys.jobs_metrics`` Table schema
.................................

+------------------------------+----------------------------------------------------+----------------------+
| Column Name                  | Description                                        |  Return Type         |
+==============================+====================================================+======================+
| ``node``                     | An object containing the id and name of the node   | ``OBJECT``           |
|                              | on which the metrics have been sampled.            |                      |
+------------------------------+----------------------------------------------------+----------------------+
| ``classification``           | An object containing the statement classification. | ``OBJECT``           |
+------------------------------+----------------------------------------------------+----------------------+
| ``classification['type']``   | The general type of the statement. Types are:      | ``TEXT``             |
|                              | ``INSERT``, ``SELECT``, ``UPDATE``, ``DELETE``,    |                      |
|                              | ``COPY``, ``DDL``, and ``MANAGEMENT``.             |                      |
+------------------------------+----------------------------------------------------+----------------------+
| ``classification['labels']`` | Labels are only available for certain statement    | ``TEXT_ARRAY``       |
|                              | types that can be classified more accurately than  |                      |
|                              | just by their type.                                |                      |
+------------------------------+----------------------------------------------------+----------------------+
| ``total_count``              | Total number of queries executed                   | ``BIGINT``           |
+------------------------------+----------------------------------------------------+----------------------+
| ``failed_count``             | Total number of queries that failed to complete    | ``BIGINT``           |
|                              | successfully.                                      |                      |
+------------------------------+----------------------------------------------------+----------------------+
| ``sum_of_durations``         | Sum of durations in ms of all executed queries per | ``BIGINT``           |
|                              | statement type.                                    |                      |
+------------------------------+----------------------------------------------------+----------------------+
| ``stdev``                    | The standard deviation of the query latencies      | ``DOUBLE PRECISION`` |
+------------------------------+----------------------------------------------------+----------------------+
| ``mean``                     | The mean query latency in ms                       | ``DOUBLE PRECISION`` |
+------------------------------+----------------------------------------------------+----------------------+
| ``max``                      | The maximum query latency in ms                    | ``BIGINT``           |
+------------------------------+----------------------------------------------------+----------------------+
| ``min``                      | The minimum query latency in ms                    | ``BIGINT``           |
+------------------------------+----------------------------------------------------+----------------------+
| ``percentiles``              | An object containing different percentiles         | ``OBJECT``           |
+------------------------------+----------------------------------------------------+----------------------+

Classification
..............

Certain statement types (such as ``SELECT`` statements) have additional labels
in their classification. These labels are the names of the logical plan
:ref:`operators <gloss-operator>` that are involved in the query.

For example, the following ``UNION`` statement::

    SELECT name FROM t1 where id = 1
    UNION ALL
    SELECT name FROM t2 where id < 2

would result in the following labels:

* ``Union``` for the UNION ALL
* ``Get`` for the left SELECT
* ``Collect`` for the right SELECT

.. note::

    Labels may be subject to change as they only represent internal properties
    of the statement!

.. _sys-operations:

Operations
----------

The ``sys.operations`` table is a constantly updated view of all operations
that are currently being executed in the cluster::

    cr> select node['name'], job_id, name, used_bytes from sys.operations
    ... order by name limit 1;
    +--------------+--------...-+-----...-+------------+
    | node['name'] | job_id     | name    | used_bytes |
    +--------------+--------...-+-----...-+------------+
    | crate        | ...        | ...     | ...        |
    +--------------+--------...-+-----...-+------------+
    SELECT 1 row in set (... sec)

An operation is a node-specific sub-component of a job (for when a job involves
multi-node processing). Jobs that do not require multi-node processing will not
produce any operations.

Table schema
............

+------------------+---------------------------------------------------+------------------------------+
| Column Name      | Description                                       |  Return Type                 |
+==================+===================================================+==============================+
| ``id``           | The operation UUID.                               | ``TEXT``                     |
|                  |                                                   |                              |
|                  | This operation ID is generated by the system.     |                              |
+------------------+---------------------------------------------------+------------------------------+
| ``job_id``       | The job id this operation belongs to.             | ``TEXT``                     |
+------------------+---------------------------------------------------+------------------------------+
| ``name``         | The name of the operation.                        | ``TEXT``                     |
+------------------+---------------------------------------------------+------------------------------+
| ``node``         | Information about the node that created the       | ``OBJECT``                   |
|                  | operation.                                        |                              |
+------------------+---------------------------------------------------+------------------------------+
| ``node['id']``   | The id of the node.                               | ``TEXT``                     |
+------------------+---------------------------------------------------+------------------------------+
| ``node['name']`` | The name of the node.                             | ``TEXT``                     |
+------------------+---------------------------------------------------+------------------------------+
| ``started``      | The point in time when the operation started.     | ``TIMESTAMP WITH TIME ZONE`` |
+------------------+---------------------------------------------------+------------------------------+
| ``used_bytes``   | Currently loaded amount of data by the operation. | ``BIGINT``                   |
+------------------+---------------------------------------------------+------------------------------+

.. NOTE::

    In some cases, operations are generated for internal CrateDB work that does
    not directly correspond to a user request. These entries do not have
    corresponding entries in ``sys.jobs``.

.. _sys-logs:

Logs
----

The :ref:`sys.jobs <sys-jobs>` and :ref:`sys.operations <sys-operations>` tables
have corresponding log tables: ``sys.jobs_log`` and ``sys.operations_log``.

``sys.jobs_log`` Table schema
.............................

+------------------------------+---------------------------------------+------------------------------+
| Column Name                  | Description                           | Return Type                  |
+==============================+=======================================+==============================+
| ``id``                       | The job ID.                           | ``TEXT``                     |
+------------------------------+---------------------------------------+------------------------------+
| ``ended``                    | The point in time when the job        | ``TIMESTAMP WITH TIME ZONE`` |
|                              | finished.                             |                              |
+------------------------------+---------------------------------------+------------------------------+
| ``error``                    | If the job encountered an error,      | ``TEXT``                     |
|                              | this will hold the error message.     |                              |
+------------------------------+---------------------------------------+------------------------------+
| ``started``                  | The point in time when the job        | ``TIMESTAMP WITH TIME ZONE`` |
|                              | started.                              |                              |
+------------------------------+---------------------------------------+------------------------------+
| ``stmt``                     | Shows the data query or manipulation  | ``TEXT``                     |
|                              | statement executed by the job.        |                              |
+------------------------------+---------------------------------------+------------------------------+
| ``username``                 | The user who executed the statement.  | ``TEXT``                     |
+------------------------------+---------------------------------------+------------------------------+
| ``classification``           | An object containing the statement    | ``OBJECT``                   |
|                              | classification.                       |                              |
+------------------------------+---------------------------------------+------------------------------+
| ``classification['type']``   | The general type of the statement.    | ``TEXT``                     |
|                              | Types are: ``INSERT``, ``SELECT``,    |                              |
|                              | ``UPDATE``, ``DELETE``,``COPY``,      |                              |
|                              | ``DDL``, and ``MANAGEMENT``.          |                              |
+------------------------------+---------------------------------------+------------------------------+
| ``classification['labels']`` | Labels are only available for certain | ``TEXT_ARRAY``               |
|                              | statement types that can be           |                              |
|                              | classified  more accurately than just |                              |
|                              | by their type.                        |                              |
+------------------------------+---------------------------------------+------------------------------+
| ``node``                     | Information about the node that       | ``OBJECT``                   |
|                              | created the job.                      |                              |
+------------------------------+---------------------------------------+------------------------------+
| ``node['id']``               | The id of the node.                   | ``TEXT``                     |
+------------------------------+---------------------------------------+------------------------------+
| ``node['name']``             | The name of the node.                 | ``TEXT``                     |
+------------------------------+---------------------------------------+------------------------------+


.. note::

  You can control which jobs are recorded using the :ref:`stats.jobs_log_filter
  <stats.jobs_log_filter>`

.. NOTE::

   The ``sys.jobs_log`` table is subject to :ref:`jobs_table_permissions`.


``sys.operations_log`` Table schema
...................................

+----------------+--------------------------------------------------+------------------------------+
| Column Name    | Description                                      |  Return Type                 |
+================+==================================================+==============================+
| ``id``         | The operation ID.                                | ``TEXT``                     |
+----------------+--------------------------------------------------+------------------------------+
| ``job_id``     | The job id.                                      | ``TEXT``                     |
+----------------+--------------------------------------------------+------------------------------+
| ``ended``      | The point in time when the operation finished.   | ``TIMESTAMP WITH TIME ZONE`` |
+----------------+--------------------------------------------------+------------------------------+
| ``error``      | If the operation encountered an error, this will | ``TEXT``                     |
|                | hold the error message.                          |                              |
+----------------+--------------------------------------------------+------------------------------+
| ``name``       | The name of the operation.                       | ``TEXT``                     |
+----------------+--------------------------------------------------+------------------------------+
| ``started``    | The point in time when the operation started.    | ``TIMESTAMP WITH TIME ZONE`` |
+----------------+--------------------------------------------------+------------------------------+
| ``used_bytes`` | The amount of data loaded by the operation.      | ``BIGINT``                   |
+----------------+--------------------------------------------------+------------------------------+

After a job or operation finishes, the corresponding entry will be moved into
the corresponding log table::

    cr> select id, stmt, username, started, ended, error
    ... from sys.jobs_log order by ended desc limit 2;
    +-...+----------------------------------------------...-+----------+-...-----+-...---+-------+
    | id | stmt                                             | username | started | ended | error |
    +-...+----------------------------------------------...-+----------+-...-----+-...---+-------+
    | ...| select node['name'], ...                         | crate    | ...     | ...   |  NULL |
    | ...| select stmt, username, started from sys.jobs ... | crate    | ...     | ...   |  NULL |
    +-...+----------------------------------------------...-+----------+-...-----+-...---+-------+
    SELECT 2 rows in set (... sec)

Invalid queries are also logged in the ``sys.jobs_log`` table, i.e. queries
that never make it to the ``sys.jobs`` table because they could not be
executed.

The log tables are bound by a fixed size
(:ref:`stats.jobs_log_size <stats.jobs_log_size>`) or by an expiration time
(:ref:`stats.jobs_log_expiration <stats.jobs_log_expiration>`)

See :ref:`conf_collecting_stats` for information on how to configure logs.

.. CAUTION::

   If you deactivate statistics tracking, the logs tables will be truncated.

.. _sys-checks:

Cluster checks
==============

The table ``sys.checks`` exposes a list of internal cluster checks and results
of their validation.

The ``sys.checks`` table looks like this:

+------------------+-----------------------------------+-------------+
| Column Name      | Description                       | Return Type |
+==================+===================================+=============+
| ``id``           | The unique check id.              | ``INTEGER`` |
+------------------+-----------------------------------+-------------+
| ``severity``     | The level of severity.            | ``INTEGER`` |
|                  | The higher the value of the field |             |
|                  | the higher severity.              |             |
+------------------+-----------------------------------+-------------+
| ``description``  | The description message for the   | ``TEXT``    |
|                  | setting check.                    |             |
+------------------+-----------------------------------+-------------+
| ``passed``       | The flag determines whether the   | ``BOOLEAN`` |
|                  | check for the setting has passed. |             |
+------------------+-----------------------------------+-------------+

Here's an example query::

  cr> select id, description from sys.checks order by id;
  +----+--------------------------------------------------------------...-+
  | id | description                                                      |
  +----+--------------------------------------------------------------...-+
  |  2 | The total number of partitions of one or more partitioned tab... |
  |  3 | The following tables need to be recreated for compatibility w... |
  +----+--------------------------------------------------------------...-+
  SELECT 2 rows in set (... sec)

Cluster checks are also indicated in the CrateDB `admin console`_. When all
cluster checks (and all :ref:`sys-node-checks`) pass, the *Checks* icon will be
green. Here's what it looks like when some checks are failing at the *CRITICAL*
severity level:

.. figure:: ../_static/cluster-checks-critical.png
   :align: center

.. _admin console: https://crate.io/docs/connect/admin_ui/

Current Checks
--------------

Number of partitions
....................

This check warns if any :ref:`partitioned table <partitioned-tables>` has more
than 1000 partitions to detect the usage of a high cardinality field for
partitioning.

.. raw:: html

  <span id="tables-need-to-be-upgraded"></span>

Tables need to be recreated
...........................

.. WARNING::

   Do not attempt to upgrade your cluster to a newer major version if this
   cluster check is failing. Follow the instructions below to get this cluster
   check passing.

This check warns you if your cluster contains tables that you need to reindex
before you can upgrade to a future major version of CrateDB.

If you try to upgrade to a later major CrateDB version without reindexing the
tables, CrateDB will refuse to start.

CrateDB table version compatibility scheme
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CrateDB maintains backward compatibility for tables created in ``majorVersion - 1``:

.. list-table::

    * - Table Origin
      - Current Version
      - Current Version
      - Current Version
    * -
      - 3.x
      - 4.x
      - 5.x
    * - 3.x
      - ✔️
      - ✔️
      - ❌
    * - 4.x
      - ❌
      - ✔️
      - ✔️
    * - 5.x
      - ❌
      - ❌
      - ✔️


Avoiding reindex using partitioned tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Reindexing tables is an expensive operation which can take a long time. If you
are storing time series data for a certain retention period and intend to
delete old data, it is possible to use the :ref:`partitioned tables
<partitioned-tables>` to avoid reindex operations.

You will have to use a :ref:`partition column <gloss-partition-column>` that
denotes time. For example, if you have a retention period of nine months, you
could partition a table by a ``month`` column. Then, every month, the system
will create a new partition. This new partition is created using the active
CrateDB version and is compatible with the next major CrateDB version. Now to
achieve your goal of avoiding a reindex, you must manually delete any partition
older than nine months. If you do that, then after nine months you rolled
through all partitions and the remaining nine are compatible with the next
major CrateDB version.


How to reindex
~~~~~~~~~~~~~~

.. hide:

    cr> CREATE TABLE rx.metrics (id TEXT PRIMARY KEY, temperature REAL);
    CREATE OK, 1 row affected (... sec)

    cr> INSERT INTO rx.metrics (id, temperature) VALUES ('1', 38.4), ('2', 42.7);
    INSERT OK, 2 rows affected  (... sec)

    cr> REFRESH TABLE rx.metrics;
    REFRESH OK, 1 row affected  (... sec)

1. Use :ref:`ref-show-create-table` to get the schema required to create an
   empty copy of the table to recreate::

    +---------------------------------------------+
    | SHOW CREATE TABLE rx.metrics                |
    +---------------------------------------------+
    | CREATE TABLE IF NOT EXISTS "rx"."metrics" ( |
    |    "id" TEXT NOT NULL,                      |
    |    "temperature" REAL,                      |
    |    PRIMARY KEY ("id")                       |
    | )                                           |
    | CLUSTERED BY ("id") INTO 4 SHARDS           |
    | WITH (                                      |
    |    column_policy = 'strict',                |
    |    number_of_replicas = '0-1'               |
    | )                                           |
    +---------------------------------------------+
    SHOW 1 row in set (... sec)

2. Create a new temporary table, using the schema returned from
   :ref:`ref-show-create-table`::

    cr> CREATE TABLE rx.tmp_metrics (id TEXT PRIMARY KEY, temperature REAL);
    CREATE OK, 1 row affected (... sec)

3. Copy the data::

    cr> INSERT INTO rx.tmp_metrics (id, temperature) (SELECT id, temperature FROM rx.metrics);
    INSERT OK, 2 rows affected (... sec)

4. Swap the tables::

    cr> ALTER CLUSTER SWAP TABLE rx.tmp_metrics TO rx.metrics;
    ALTER OK, 1 row affected  (... sec)

5. Confirm the new ``your_table`` contains all data and has the new version::

    cr> SELECT count(*) FROM rx.metrics;
    +----------+
    | count(*) |
    +----------+
    |        2 |
    +----------+
    SELECT 1 row in set (... sec)

    cr> SELECT version['created'] FROM information_schema.tables
    ... WHERE table_schema = 'rx' AND table_name = 'metrics';
    +--------------------+
    | version['created'] |
    +--------------------+
    | 5.10.0             |
    +--------------------+
    SELECT 1 row in set (... sec)

6. Drop the old table, as it is now obsolete::

    cr> DROP TABLE rx.tmp_metrics;
    DROP OK, 1 row affected  (... sec)


.. hide:

    cr> DROP TABLE rx.metrics;
    DROP OK, 1 row affected  (... sec)


After you reindexed all tables, this cluster check will pass.

.. NOTE::

   Snapshots of your tables created prior to them being upgraded will not work
   with future versions of CrateDB. For this reason, you should create a new
   snapshot for each of your tables. (See :ref:`snapshot-restore`.)

License check
.............


.. NOTE::

   This check was removed in version 4.5 because CrateDB no longer requires an
   enterprise license, see also `Farewell to the CrateDB Enterprise License`_.


This check warns you when your license is close to expiration, is already
expired, or if the cluster contains more nodes than allowed by your license. It
will yield a ``MEDIUM`` alert when your license is valid for less than 15 days
and a ``HIGH`` alert when your license is valid for less than a day.
All other cases, like `already expired` or `max-nodes-violation`, it will
result in a ``HIGH`` alert. We recommend that you request a new license when
this check triggers, in order to avoid the situation where operations are
rejected due to an invalid license.

.. _sys-health:

Health
======

The ``sys.health`` table lists the `health` of each table and table
partition. The `health` is computed by checking the states of the shard of each
table/partition.

+----------------------------+------------------------------------+--------------+
| Column Name                | Description                        | Return Type  |
+============================+====================================+==============+
| ``table_name``             | The table name.                    | ``TEXT``     |
+----------------------------+------------------------------------+--------------+
| ``table_schema``           | The schema of the table.           | ``TEXT``     |
+----------------------------+------------------------------------+--------------+
| ``partition_ident``        | The `ident` of the partition.      | ``TEXT``     |
|                            | NULL for non-partitioned tables.   |              |
+----------------------------+------------------------------------+--------------+
| ``health``                 | The health label.                  | ``TEXT``     |
|                            | Can be RED, YELLOW or GREEN.       |              |
+----------------------------+------------------------------------+--------------+
| ``severity``               | The health as a ``smallint`` value.| ``SMALLINT`` |
|                            | Useful when ordering on health.    |              |
+----------------------------+------------------------------------+--------------+
| ``missing_shards``         | The number of not assigned or      | ``INTEGER``  |
|                            | started shards.                    |              |
+----------------------------+------------------------------------+--------------+
| ``underreplicated_shards`` | The number of shards which are     | ``INTEGER``  |
|                            | not fully replicated.              |              |
+----------------------------+------------------------------------+--------------+

Both ``missing_shards`` and ``underreplicated_shards`` might return ``-1`` if
the cluster is in an unhealthy state that prevents the exact number from being
calculated. This could be the case when the cluster can't elect a master,
because there are not enough eligible nodes available.

::

    cr> select * from sys.health order by severity desc, table_name;
    +--------+----------------+-----------------+----------+------------+--------------+------------------------+
    | health | missing_shards | partition_ident | severity | table_name | table_schema | underreplicated_shards |
    +--------+----------------+-----------------+----------+------------+--------------+------------------------+
    | GREEN  |              0 |            NULL |        1 | locations  | doc          |                      0 |
    | GREEN  |              0 |            NULL |        1 | quotes     | doc          |                      0 |
    +--------+----------------+-----------------+----------+------------+--------------+------------------------+
    SELECT 2 rows in set (... sec)

The `health` with the highest `severity` will always define the `health` of the
query scope.

Example of getting a `cluster health` (`health` of all tables):

::

    cr> select health from sys.health order by severity desc limit 1;
    +--------+
    | health |
    +--------+
    | GREEN  |
    +--------+
    SELECT 1 row in set (... sec)

.. _sys-health-def:

Health definition
-----------------

+------------+---------------------------------------------------+
| Health     | Description                                       |
+============+===================================================+
| ``RED``    | At least one primary shard is missing (primary    |
|            | shard not started or unassigned).                 |
+------------+---------------------------------------------------+
| ``YELLOW`` | At least one shard is underreplicated (replica    |
|            | shard not started or unassigned).                 |
+------------+---------------------------------------------------+
| ``GREEN``  | All primary and replica shards have been started. |
+------------+---------------------------------------------------+

.. NOTE::

   The ``sys.health`` table is subject to :ref:`shard_table_permissions` as it
   will expose a summary of table shard states.

.. _sys-repositories:

Repositories
============

The table ``sys.repositories`` lists all configured repositories that can be
used to create, manage and restore snapshots (see :ref:`snapshot-restore`).

+---------------+-----------------------------------+-------------+
| Column Name   | Description                       | Return Type |
+===============+===================================+=============+
| ``name``      | The repository name               | ``TEXT``    |
+---------------+-----------------------------------+-------------+
| ``type``      | The type of the repository        | ``TEXT``    |
|               | determining how and where the     |             |
|               | repository stores its snapshots.  |             |
+---------------+-----------------------------------+-------------+
| ``settings``  | The configuration settings the    | ``OBJECT``  |
|               | repository has been created       |             |
|               | with. The specific settings       |             |
|               | depend on the repository type,    |             |
|               | see :ref:`sql-create-repository`. |             |
+---------------+-----------------------------------+-------------+

.. Hidden: create repository

   cr> CREATE REPOSITORY "my_repo" TYPE "fs"
   ... WITH (max_restore_bytes_per_sec='1000b', location='repo_location', compress=true);
   CREATE OK, 1 row affected (... sec)

::

    cr> SELECT name, type, settings FROM sys.repositories
    ... ORDER BY name;
    +---------+------+---------------------------------------------------...--+
    | name    | type | settings                                               |
    +---------+------+---------------------------------------------------...--+
    | my_repo | fs   | {"compress": "true", "location": "repo_location", ...} |
    +---------+------+---------------------------------------------------...--+
    SELECT 1 row in set (... sec)

.. NOTE::

    Sensitive user account information will be masked and thus not visible to the user.

.. _sys-snapshots:

Snapshots
=========

The table ``sys.snapshots`` lists all existing snapshots in all configured
repositories (see :ref:`snapshot-restore`).

+--------------------------+----------------------------------+------------------------------+
| Column Name              | Description                      | Return Type                  |
+==========================+==================================+==============================+
| ``id``                   | UUID of the snapshot             | ``TEXT``                     |
+--------------------------+----------------------------------+------------------------------+
| ``name``                 | The name of the snapshot         | ``TEXT``                     |
+--------------------------+----------------------------------+------------------------------+
| ``repository``           | The name of the repository that  | ``TEXT``                     |
|                          | contains this snapshot.          |                              |
+--------------------------+----------------------------------+------------------------------+
| ``concrete_indices``     | Contains the names of all        | ``ARRAY(TEXT)``              |
|                          | tables and partitions that are   |                              |
|                          | contained in this snapshot       |                              |
|                          | how they are represented         |                              |
|                          | as ES index names.               |                              |
+--------------------------+----------------------------------+------------------------------+
| ``tables``               | Contains the fully qualified     | ``ARRAY(TEXT)``              |
|                          | names of all tables within the   |                              |
|                          | snapshot.                        |                              |
+--------------------------+----------------------------------+------------------------------+
| ``relations``            | Contains the ``table_schema``    | ``ARRAY(OBJECT)``            |
|                          | and ``table_name`` of all tables |                              |
|                          | within the snapshot.             |                              |
+--------------------------+----------------------------------+------------------------------+
| ``table_partitions``     | Contains the table schema, table | ``ARRAY(OBJECT)``            |
|                          | name and partition values of     |                              |
|                          | partitioned tables within the    |                              |
|                          | snapshot.                        |                              |
+--------------------------+----------------------------------+------------------------------+
| ``started``              | The point in time when the       | ``TIMESTAMP WITH TIME ZONE`` |
|                          | creation of the snapshot         |                              |
|                          | started. Changes made after      |                              |
|                          | that are not stored in this      |                              |
|                          | snapshot.                        |                              |
+--------------------------+----------------------------------+------------------------------+
| ``finished``             | The point in time when the       | ``TIMESTAMP WITH TIME ZONE`` |
|                          | snapshot creation finished.      |                              |
+--------------------------+----------------------------------+------------------------------+
| ``state``                | The current state of the         | ``TEXT``                     |
|                          | snapshot. One of:                |                              |
|                          | ``IN_PROGRESS``, ``SUCCESS``,    |                              |
|                          | ``PARTIAL``, or ``FAILED``.      |                              |
+--------------------------+----------------------------------+------------------------------+
| ``version``              | An internal version this         | ``TEXT``                     |
|                          | snapshot was created with.       |                              |
+--------------------------+----------------------------------+------------------------------+
| ``failures``             | A list of failures that occurred | ``ARRAY(TEXT)``              |
|                          | while taking the snapshot.       |                              |
|                          | If taking the snapshot was       |                              |
|                          | successful this is empty.        |                              |
+--------------------------+----------------------------------+------------------------------+
| ``reason``               | Snapshot failure reason.         | ``TEXT``                     |
|                          | If the snapshot was              |                              |
|                          | successful this is ``NULL``.     |                              |
+--------------------------+----------------------------------+------------------------------+
| ``total_shards``         | Total number of primary shards   | ``INTEGER``                  |
|                          | in the snapshot.                 |                              |
+--------------------------+----------------------------------+------------------------------+
| ``include_global_state`` | Flag indicating that the         | ``BOOLEAN``                  |
|                          | snapshot was taken with the      |                              |
|                          | ``ALL`` option.                  |                              |
+--------------------------+----------------------------------+------------------------------+

Snapshot/Restore operates on a per-shard basis. Hence, the ``state`` column
indicates whether all (``SUCCESS``), some (``PARTIAL``), or no
shards(``FAILED``) have been backed up. ``PARTIAL`` snapshots are the result of
some primaries becoming unavailable while taking the snapshot when there are no
replicas at hand (cluster state is *RED*). If there are replicas of the (now
unavailable) primaries (cluster state is *YELLOW*) the snapshot succeeds and
all shards are included (state ``SUCCESS``). Building on a ``PARTIAL`` snapshot
will include all primaries again.

.. WARNING::

    In case of a ``PARTIAL`` state another snapshot should be created in order
    to guarantee a full backup! Only ``SUCCESS`` includes all shards.

The ``concrete_indices`` column contains the names of all Elasticsearch indices
that were stored in the snapshot. A *normal* CrateDB table maps to one
Elasticsearch index, a partitioned table maps to one Elasticsearch index per
partition. The mapping follows the following pattern:

+-----------------------------------------+------------------------------------------+
| CrateDB table / partition name          | ``concrete_indices`` entry               |
+=========================================+==========================================+
| ``doc.my_table``                        | ``my_table``                             |
+-----------------------------------------+------------------------------------------+
| ``my_schema.my_table``                  | ``my_schema.my_table``                   |
+-----------------------------------------+------------------------------------------+
| ``doc.parted_table`` (value=null)       | ``.partitioned.my_table.0400``           |
+-----------------------------------------+------------------------------------------+
| ``my_schema.parted_table`` (value=null) | ``my_schema..partitioned.my_table.0400`` |
+-----------------------------------------+------------------------------------------+

.. Hidden: create snapshots

   cr> CREATE SNAPSHOT "my_repo"."my_snapshot" ALL
   ... WITH (ignore_unavailable=true, wait_for_completion=true);
   CREATE OK, 1 row affected (... sec)

::

    cr> SELECT "repository", name, state, concrete_indices
    ... FROM sys.snapshots order by "repository", name;
    +------------+-------------+---------+-----------------...-+
    | repository | name        | state   | concrete_indices    |
    +------------+-------------+---------+-----------------...-+
    | my_repo    | my_snapshot | SUCCESS | [...]               |
    +------------+-------------+---------+-----------------...-+
    SELECT 1 row in set (... sec)

.. Hidden: drop snapshot

    cr> DROP SNAPSHOT "my_repo"."my_snapshot";
    DROP OK, 1 row affected (... sec)

.. Hidden: drop repository

    cr> DROP REPOSITORY "my_repo";
    DROP OK, 1 row affected (... sec)


.. _sys-snapshot-restore:

Snapshot Restore
================

The ``sys.snapshot_restore`` table contains information about the current
state of snapshot restore operations.

.. list-table:: pg_stats schema
    :header-rows: 1

    * - Name
      - Description
      - Type
    * - ``id``
      - The ``UUID`` of the restore snapshot operation.
      - ``TEXT``
    * - ``repository``
      - The name of the repository that contains the snapshot.
      - ``TEXT``
    * - ``snapshot``
      - The name of the snapshot.
      - ``TEXT``
    * - ``state``
      - The current state of the snapshot restore operations. Possible states
        are: ``INIT``, ``STARTED``, ``SUCCESS``, and ``FAILURE``.
      - ``TEXT``
    * - ``shards['table_schema']``
      - The schema name of the table of the shard.
      - ``TEXT``
    * - ``shards['table_name']``
      - The table name of the shard.
      - ``TEXT``
    * - ``shards['partition_ident']``
      - The identifier of the partition of the shard. ``NULL`` if the is not
        partitioned.
      - ``TEXT``
    * - ``shards['shard_id']``
      - The ID of the shard.
      - ``INTEGER``
    * - ``shards['state']``
      - The restore state of the shard. Possible states are: ``INIT``,
        ``STARTED``, ``SUCCESS``, and ``FAILURE``.
      - ``TEXT``

To get more information about the restoring snapshots and shards one can join
the ``sys.snapshot_restore`` with ``sys.shards`` or ``sys.snapshots`` table.

.. _sys-summits:

Summits
=======

The ``sys.summits`` table contains the information about the mountains in the
Alps higher than 2000m. The mountain names from the table are also used to
generate random nodes names.

.. _sys-users:

Users
=====

The ``sys.users`` table contains all existing database users in the cluster.

+----------------------------+----------------------------------+-------------+
| Column Name                | Description                      | Return Type |
+============================+==================================+=============+
| ``name``                   | The name of the database user.   | ``TEXT``    |
+----------------------------+----------------------------------+-------------+
| ``superuser``              | Flag to indicate whether the     | ``BOOLEAN`` |
|                            | user is a superuser.             |             |
+----------------------------+----------------------------------+-------------+
| ``password``               | ``********`` if there is a       | ``TEXT``    |
|                            | password set or ``NULL`` if      |             |
|                            | there is not.                    |             |
+----------------------------+----------------------------------+-------------+
| ``granted_roles``          | A list of parent roles granted   | ``ARRAY``   |
|                            | to the user                      |             |
+----------------------------+----------------------------------+-------------+
| ``granted_roles[role]``    | The name of the role granted to  | ``TEXT``    |
|                            | the user                         |             |
+----------------------------+----------------------------------+-------------+
| ``granted_roles[grantor]`` | The name of user who granted the | ``TEXT``    |
|                            | role to the user                 |             |
+----------------------------+----------------------------------+-------------+
| ``jwt``                    | JWT authentication properties    | ``OBJECT``  |
+----------------------------+----------------------------------+-------------+
| ``jwt[aud]``               | Recipient that the JWT is        | ``TEXT``    |
|                            | intended for                     |             |
+----------------------------+----------------------------------+-------------+
| ``jwt[iss]``               | JWK endpoint URL                 | ``TEXT``    |
+----------------------------+----------------------------------+-------------+
| ``jwt[username]``          | User name in a third party app   | ``TEXT``    |
+----------------------------+----------------------------------+-------------+

.. _sys-roles:

Roles
=====

The ``sys.roles`` table contains all existing database roles in the cluster.

+----------------------------+----------------------------------+-------------+
| Column Name                | Description                      | Return Type |
+============================+==================================+=============+
| ``name``                   | The name of the database user.   | ``TEXT``    |
+----------------------------+----------------------------------+-------------+
| ``granted_roles``          | A list of parent roles granted   | ``ARRAY``   |
|                            | to the user                      |             |
+----------------------------+----------------------------------+-------------+
| ``granted_roles[role]``    | The name of the role granted to  | ``TEXT``    |
|                            | the user                         |             |
+----------------------------+----------------------------------+-------------+
| ``granted_roles[grantor]`` | The name of user who granted the | ``TEXT``    |
|                            | role to the user                 |             |
+----------------------------+----------------------------------+-------------+

.. _sys-privileges:

Privileges
==========

The ``sys.privileges`` table contains all privileges for each user and role of
the database.

+--------------+-------------------------------------------------+-------------+
| Column Name  | Description                                     | Return Type |
+==============+=================================================+=============+
| ``class``    | The :ref:`class <privileges-classes>` on which  | ``TEXT``    |
|              | the privilege applies                           |             |
+--------------+-------------------------------------------------+-------------+
| ``grantee``  | The name of the database user or role for which | ``TEXT``    |
|              | the privilege is granted or denied              |             |
+--------------+-------------------------------------------------+-------------+
| ``grantor``  | The name of the database user who granted or    | ``TEXT``    |
|              | denied the privilege                            |             |
+--------------+-------------------------------------------------+-------------+
| ``ident``    | The name of the database object on which the    | ``TEXT``    |
|              | privilege applies                               |             |
+--------------+-------------------------------------------------+-------------+
| ``state``    | Either ``GRANT`` or ``DENY``, which indicates   | ``ARRAY``   |
|              | if the user or role has been granted or denied  |             |
|              | access to the specific database object          |             |
+--------------+-------------------------------------------------+-------------+
| ``type``     | The :ref:`type of access <privilege_types>`     | ``TEXT``    |
|              | for the specific database object                |             |
+--------------+-------------------------------------------------+-------------+

.. _sys-sessions:

Sessions
========

.. list-table::
    :header-rows: 1
    :widths: auto
    :align: left

    * - Column Name
      - Description
      - Return Type
    * - ``id``
      - The unique identifier of the session within a single node. Use together
        with ``handler_node`` to uniquely identify sessions across a cluster
      - ``INTEGER``
    * - ``auth_user``
      - The user which was authenticated for the session
      - ``TEXT``
    * - ``session_user``
      - The user, possibly different than the ``auth_user`` which is currently
        active in the session, see: :ref:`ref-set-session-authorization`
      - ``TEXT``
    * - ``handler_node``
      - The name of the node on which the session is created
      - ``TEXT``
    * - ``client_address``
      - The IPv4 or IPv6 network address of the client which opened the session
      - ``TEXT``
    * - ``time_created``
      - The time on which the session was created
      - ``TIMESTAMPTZ``
    * - ``protocol``
      - ``http`` or ``pg`` to denote weather the session is created through
        :ref:`interface-http` or :ref:`interface-postgresql`
      - ``TEXT``
    * - ``ssl``
      - A flag which denotes if ``SSL`` encryption is used between the client
        which opened the session and the ``handler_node``
      - ``BOOLEAN``
    * - ``settings``
      - The :ref:`session settings <conf-session>`
      - ``OBJECT``
    * - ``last_statement``
      - The last SQL statement which was executed in the session
      - ``TEXT``


.. _sys-allocations:

Allocations
===========

The ``sys.allocations`` table contains information about shards and their
:ref:`allocation <gloss-shard-allocation>` state. The table contains:

* shards that are unassigned and why they are unassigned
* shards that are assigned but cannot be moved or rebalanced and why they
  remain on their current node

It can help to identify problems if shard allocations behave different than
expected, e.g. when a shard stays unassigned or a shard does not move off a
node.

+-------------------------------+-------------------------------+-------------+
| Column Name                   | Description                   | Return Type |
+===============================+===============================+=============+
| ``table_schema``              | Schema name of the table of   | ``TEXT``    |
|                               | the shard.                    |             |
+-------------------------------+-------------------------------+-------------+
| ``table_name``                | Table name of the shard.      | ``TEXT``    |
+-------------------------------+-------------------------------+-------------+
| ``partition_ident``           | Identifier of the partition   | ``TEXT``    |
|                               | of the shard.                 |             |
|                               | ``NULL`` if the table is not  |             |
|                               | partitioned.                  |             |
+-------------------------------+-------------------------------+-------------+
| ``shard_id``                  | ID of the effected shard.     | ``INTEGER`` |
+-------------------------------+-------------------------------+-------------+
| ``node_id``                   | ID of the node on which the   | ``TEXT``    |
|                               | shard resides. ``NULL`` if    |             |
|                               | the shard is unassigned.      |             |
+-------------------------------+-------------------------------+-------------+
| ``primary``                   | Whether the shard is a        | ``BOOLEAN`` |
|                               | primary shard.                |             |
+-------------------------------+-------------------------------+-------------+
| ``current_state``             | Current state of the shard.   | ``TEXT``    |
|                               | Possible states are:          |             |
|                               | ``UNASSIGNED``,               |             |
|                               | ``INITIALIZING``,             |             |
|                               | ``STARTED``,                  |             |
|                               | ``RELOCATING``                |             |
+-------------------------------+-------------------------------+-------------+
| ``explanation``               | Explanation why the shard     | ``TEXT``    |
|                               | cannot be allocated, moved    |             |
|                               | or rebalanced.                |             |
+-------------------------------+-------------------------------+-------------+
| ``decisions``                 | A list of decisions that      | ``ARRAY``   |
|                               | describe in detail why the    |             |
|                               | shard in the current state.   |             |
+-------------------------------+-------------------------------+-------------+
| ``decisions['node_id']``      | ID of the node of the         | ``TEXT``    |
|                               | decision.                     |             |
+-------------------------------+-------------------------------+-------------+
| ``decisions['node_name']``    | Name of the node of the       | ``TEXT``    |
|                               | decision.                     |             |
+-------------------------------+-------------------------------+-------------+
| ``decisions['explanations']`` | Detailed list of human        | ``ARRAY``   |
|                               | readable explanations why the |             |
|                               | node decided whether to       |             |
|                               | allocate or rebalance the     |             |
|                               | shard. Returns ``NULL`` if    |             |
|                               | there is no need to rebalance |             |
|                               | the shard.                    |             |
+-------------------------------+-------------------------------+-------------+

.. NOTE::

   The ``sys.allocations`` table is subject to :ref:`shard_table_permissions`.


.. _shard_table_permissions:

Shard table permissions
=======================

Accessing tables that return shards (``sys.shards``, ``sys.allocations``) is
subjected to the same privileges constraints as the other tables. Namely, in
order to query them, the connected user needs to have the ``DQL`` privilege on
that particular table, either directly or inherited from the ``SCHEMA`` or
``CLUSTER`` (for more information on privileges inheritance see
:ref:`Hierarchical Inheritance of Privileges
<hierarchical_privileges_inheritance>`).

However, being able to query shard returning system tables will not allow the
user to retrieve all the rows in the table, as they may contain information
related to tables, which the connected user does not have any privileges for.
The only rows that will be returned will be the ones the user is allowed to
access.

For example, if the user ``john`` has any privilege on the ``doc.books`` table
but no privilege at all on ``doc.locations``, when ``john`` issues a
``SELECT * FROM sys.shards`` statement, the shards information related to the
``doc.locations`` table will not be returned.

.. _jobs_table_permissions:

``sys`` jobs tables permissions
===============================

Accessing :ref:`sys.jobs <sys-jobs>` and :ref:`sys.jobs_log <sys-logs>` tables
is subjected to the same privileges constraints as other tables. To query
them, the current user needs to have the ``DQL`` privilege on that particular
table, either directly or inherited from the ``SCHEMA`` or ``CLUSTER``.

A user that doesn't have superuser privileges is allowed to retrieve only
their own job logs entries, while a user with superuser privileges has access
to all.


.. _pg_stats:

pg_stats
========

The ``pg_stats`` table in the ``pg_catalog`` system schema contains statistical
data about the contents of the CrateDB cluster.

Entries are periodically created or updated in the interval configured with the
:ref:`stats.service.interval <stats.service.interval>` setting.

Alternatively the statistics can also be updated using the :ref:`ANALYZE
<analyze>` command.


The table contains 1 entry per column for each table in the cluster which has
been analyzed.

.. vale off

.. list-table:: pg_stats schema
    :header-rows: 1


    * - Name
      - Type
      - Description
    * - schemaname
      - text
      - Name of the schema containing the table.
    * - tablename
      - text
      - Name of the table.
    * - attname
      - text
      - Name of the column.
    * - inherited
      - bool
      - Always false in CrateDB; For compatibility with PostgreSQL.
    * - null_frac
      - real
      - Fraction of column entries that are null.
    * - avg_width
      - integer
      - Average size in bytes of column's entries.
    * - n_distinct
      - real
      - An approximation of the number of distinct values in a column.
    * - most_common_vals
      - string[]
      - A list of the most common values in the column. null if no values seem.
        more common than others.
    * - most_common_freqs
      - real[]
      - A list of the frequencies of the most common values. The size of the
        array always matches most_common_vals. If most_common_vals is null this
        is null as well.
    * - histogram_bounds
      - string[]
      - A list of values that divide the column's values into groups of
        approximately equal population. The values in most_common_vals, if
        present, are omitted from this histogram calculation.
    * - correlation
      - real
      - Always 0.0. This column exists for PostgreSQL compatibility.
    * - most_common_elems
      - string[]
      - Always null. Exists for PostgreSQL compatibility.
    * - most_common_elem_freqs
      - real[]
      - Always null. Exists for PostgreSQL compatibility.
    * - elem_count_histogram
      - real[]
      - Always null. Exists for PostgreSQL compatibility.

.. note::

    Not all data types support creating statistics. So some columns may not
    show up in the table.

.. _pg_publication:

pg_publication
==============

The ``pg_publication`` table in the ``pg_catalog`` system schema contains all
publications created in the cluster.

+------------------+------------------------------------------+-------------+
| Column Name      | Description                              | Return Type |
+==================+==========================================+=============+
| ``oid``          | Row identifier.                          | ``INTEGER`` |
+------------------+------------------------------------------+-------------+
| ``pubname``      | Name of the publication.                 | ``TEXT``    |
+------------------+------------------------------------------+-------------+
| ``pubowner``     | ``oid`` of the owner of the publication. | ``INTEGER`` |
+------------------+------------------------------------------+-------------+
| ``puballtables`` | Whether this publication includes all    | ``BOOLEAN`` |
|                  | tables in the cluster, including         |             |
|                  | tables created in the future.            |             |
+------------------+------------------------------------------+-------------+
| ``pubinsert``    | Whether ``INSERT`` operations are        | ``BOOLEAN`` |
|                  | replicated for tables in the publication.|             |
|                  | Always ``true``.                         |             |
+------------------+------------------------------------------+-------------+
| ``pubupdate``    | Whether ``UPDATE`` operations are        | ``BOOLEAN`` |
|                  | replicated for tables in the publication.|             |
|                  | Always ``true``.                         |             |
+------------------+------------------------------------------+-------------+
| ``pubdelete``    | Whether ``DELETE`` operations are        | ``BOOLEAN`` |
|                  | replicated for tables in the publication.|             |
|                  | Always ``true``.                         |             |
+------------------+------------------------------------------+-------------+

.. _pg_publication_tables:

pg_publication_tables
=====================

The ``pg_publication_tables`` table in the ``pg_catalog`` system schema
contains tables replicated by a publication.

+----------------+--------------------------------------+-------------+
| Column Name    | Description                          | Return Type |
+================+======================================+=============+
| ``pubname``    | Name of the publication.             | ``TEXT``    |
+----------------+--------------------------------------+-------------+
| ``schemaname`` | Name of the schema containing table. | ``TEXT``    |
+----------------+--------------------------------------+-------------+
| ``tablename``  | Name of the table.                   | ``TEXT``    |
+----------------+--------------------------------------+-------------+

.. _pg_subscription:

pg_subscription
===============

The ``pg_subscription`` table in the ``pg_catalog`` system schema contains all
subscriptions created in the cluster.

+---------------------+------------------------------------------+-------------+
| Column Name         | Description                              | Return Type |
+=====================+==========================================+=============+
| ``oid``             | Row identifier.                          | ``INTEGER`` |
+---------------------+------------------------------------------+-------------+
| ``subdbid``         | noop value, always ``0``.                | ``INTEGER`` |
+---------------------+------------------------------------------+-------------+
| ``subname``         | Name of the subscription.                | ``TEXT``    |
+---------------------+------------------------------------------+-------------+
| ``subowner``        | ``oid`` of the owner of the subscription.| ``INTEGER`` |
+---------------------+------------------------------------------+-------------+
| ``subenabled``      | Whether the subscription is enabled,     | ``BOOLEAN`` |
|                     | always ``true``.                         |             |
+---------------------+------------------------------------------+-------------+
| ``subbinary``       | Noop value, always ``true``.             | ``BOOLEAN`` |
|                     |                                          |             |
+---------------------+------------------------------------------+-------------+
| ``substream``       | Noop value, always ``true``.             | ``BOOLEAN`` |
|                     |                                          |             |
+---------------------+------------------------------------------+-------------+
| ``subconninfo``     | Connection string to the publishing      | ``TEXT``    |
|                     | cluster.                                 |             |
+---------------------+------------------------------------------+-------------+
| ``subslotname``     | Noop value, always ``NULL``.             | ``TEXT``    |
|                     |                                          |             |
+---------------------+------------------------------------------+-------------+
| ``subsynccommit``   | Noop value, always ``NULL``.             | ``TEXT``    |
+---------------------+------------------------------------------+-------------+
| ``subpublications`` | Array of subscribed publication names.   | ``ARRAY``   |
|                     | These publications are defined in the    |             |
|                     | publishing cluster.                      |             |
+---------------------+------------------------------------------+-------------+

.. _pg_subscription_rel:

pg_subscription_rel
===================

The ``pg_subscription_rel`` table in the ``pg_catalog`` system schema
contains the state for each replicated relation in each subscription.

+-----------------------+--------------------------------------+--------------+
| Column Name           | Description                          | Return Type  |
+=======================+======================================+==============+
| ``srsubid``           | Reference to subscription.           | ``INTEGER``  |
+-----------------------+--------------------------------------+--------------+
| ``srrelid``           | Reference to relation.               | ``REGCLASS`` |
+-----------------------+--------------------------------------+--------------+
| ``srsubstate``        | Replication state of the relation.   | ``TEXT``     |
|                       | State code:                          |              |
|                       | ``i`` - initializing;                |              |
|                       | ``d`` - restoring;                   |              |
|                       | ``r`` - monitoring, i.e. waiting for |              |
|                       | new changes;                         |              |
|                       | ``e`` - error.                       |              |
+-----------------------+--------------------------------------+--------------+
| ``srsubstate_reason`` | Error message if there was a         | ``TEXT``     |
|                       | replication error for the relation   |              |
|                       | or ``NULL``.                         |              |
+-----------------------+--------------------------------------+--------------+
| ``srsublsn``          | Noop value, always ``NULL``.         | ``LONG``     |
+-----------------------+--------------------------------------+--------------+

.. vale on

.. _Farewell to the CrateDB Enterprise License: https://crate.io/blog/farewell-to-the-cratedb-enterprise-license-faq
