# pg\_stat\_statements

{% hint style="info" %}
This extension is a prerequisite when using Datasentinel.
{% endhint %}

Once the settings are configured, there is no further action required. \
Resetting **pg\_stat\_statements** is unnecessary.

Datasentinel collects activity metrics for all queries at regular intervals to allow detailed analyzes on the graphical interface.

## **Installation**

This extension is part of the official PostgreSQL contrib package.\
Refer to the [documentation](https://www.postgresql.org/docs/current/pgstatstatements.html) for more information

{% hint style="danger" %}
To be installed in the internal database named **postgres**
{% endhint %}

### Modify `postgresql.conf`

{% hint style="info" %}
Add **`pg_stat_statements`** to **`shared_preload_libraries`** parameter
{% endhint %}

* Add the following lines

<pre class="language-apache"><code class="lang-apache"><strong>#
</strong><strong># Track all statements
</strong>pg_stat_statements.track = all
#
# Optional, not necessary for Datasentinel to save pg_stat_statements.
pg_stat_statements.save = false
#
# The extension's maximum number of tracked statements should be reduced from its default 5000 value 
# (e.g. 2000) as Datasentinel collects metrics at regular intervals.
pg_stat_statements.max = 2000
#
# Since postgresql 14
compute_query_id = on
#
# log IO timing
track_io_timing=on
</code></pre>

{% hint style="info" %}
A new parameter **`compute_query_id`** has been added since PostgreSQL 14.\
This is a configuration parameter enabling computation of a query identifier, which can be displayed via `pg_stat_activity` , in `EXPLAIN` output or in log output if configured in log\_line\_prefix .&#x20;

For optimal utilization of Datasentinel, we advise configuring this parameter accordingly.

```sql
compute_query_id = on
```

{% endhint %}

{% hint style="info" %}
The **Agent version 3.2, which was released in February 2023**, introduces the [Collection level](about:blank/installation/collection_level.html#collection-level) feature allowing you to filter metrics collected from the extension
{% endhint %}

{% hint style="danger" %}
You see lots of utility commands (other than SELECT, INSERT, UPDATE, DELETE) in `pg_stat_statements,`

Especially  if you are using XA type distributed transactions, PREPARE TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION queries are not normalized by pg\_stat\_statements

You can disable their collect by setting this parameter:

```sql
pg_stat_statements.track_utility = off
```

{% endhint %}

### Reload Conf

* connect as a superuser

```sql
SELECT pg_reload_conf();
```

### Create Extension

* connect to the internal database named **postgres** with superuser privileges

```sql
CREATE EXTENSION pg_stat_statements;
```

### Check Extension

```sql
SELECT current_database(), extname from pg_extension where extname ='pg_stat_statements';
current_database |      extname
------------------+--------------------
postgres         | pg_stat_statements
(1 row)
```

```sql
// Ensure the returned count is greater than zero
select count(*) from pg_stat_statements
```