Skip to main content

Postgres.AI Assistant (beta) tools

Overview​

This reference describes tools (functions) that are available to the Postgres.AI Assistant Beta, including RAG KB semantic search, running benchmarks, executing SQL, and more. It aims to provide a comprehensive overview of the assistant's capabilities and how to interact with it effectively.

Under normal circumstances, users are not expected to mention concrete tools when communicating with the assistant. However, in certain situations, it might be useful to understand what is possible. In such cases, use this reference and discuss concrete functions and parameters with the assistant, as this can help you achieve more predictable results.

Using semantic search (pgvector), find relevant pieces of knowledge in the RAG KB (Retrieval Augmented Generation Knowledge Base).

Categories of data in the RAG KB:

By default, the search is limited to docs and articles.

Input parameters​

ParameterTypeDescriptionDefaultExample
input (required)stringSearch query string used to find relevant content.N/A"Postgres installation guide"
categoriesjsonJSON array listing all categories to be used in search["docs", "articles"]["src"]
match_countintegerMaximum number of records to return. If not specified, the default value is used.1025

Tool make_a_plot​

Visualize some data such as benchmark results. This function uses QuickChart; see QuickChart docs to learn more about its capabilities.

Input parameters​

ParameterTypeDescriptionDefaultExample
type (required)stringThe type of chart to create. (e.g., "bar" or "line").N/A"bar"
dataobjectThe data to be plotted.N/ASee structure below.

Data object structure:

ParameterTypeDescriptionDefaultExample
labelsarrayList containing labels for the x-axis.N/A["January", "February"]
datasetsarrayData to be visualized. Each dataset must have label and data (an array of numbers).N/ASee structure below.

Dataset object structure:

ParameterTypeDescriptionDefaultExample
labelstringThe label for the dataset.N/A"My Dataset"
dataarrayArray of numbers representing the data points.N/A[10, 20, 30, 40]

Tool fetch_whole_web_page​

Fetch the content of a web page. As of July 2024, this feature is limited to these domains:

  • github.com
  • gitlab.com
  • postgresql.org
  • postgres.ai
  • wiki.postgresql.org

Input parameters​

ParameterTypeDescriptionDefaultExample
url (required)stringThe URL of the page to fetch.N/Ahttps://www.postgresql.org/docs/17/release-17.html

Tool run_db_experiment_dedicated_env​

Database experiment in dedicated environment: creates a PostgreSQL cluster in Google Cloud and executes a series of experiment runs on it using a GitLab CI pipeline.

When started, provides pipeline URL. Once experiment is finished, either succesfully or with errors, the user is informed of the results.

JSON configuration example:

{
  "ref":"master",
  "SERVER_TYPE":"n2-standard-2",
  "VOLUME_SIZE":"100",
  "POSTGRES_VERSION":"16",
  "MODE": "dedicated",
  "PGBENCH_INIT_COMMAND": "pgbench -i -s 100 -q",
  "TEST_RUNS": {
    "w/o wal_compression": {
      "pre_configs": [
        "wal_compression=off"
      ],
      "workload_pgbench": "pgbench -h localhost -p 5432 -U postgres -c4 -j4 -nr -P10 -T600 postgres"
    },
    "with wal_compression": {
      "pre_configs": [
        "wal_compression=on"
      ],
      "workload_pgbench": "pgbench -h localhost -p 5432 -U postgres -c4 -j4 -nr -P10 -T600 postgres"
    }
  }
}

Input parameters​

ParameterTypeDescriptionDefaultExample
SERVER_TYPE (required)stringType of server to deploy. Supported values: various configurations with specific vCPUs and RAM."n2-standard-4""n2-standard-4"
SERVER_LOCATIONstringGCP region or region+zone for server deployment. If not specified, an available zone will be selected."""us-central1"
SERVER_SPOTstringDefines whether the GCP compute instance is preemptible (spot)."true""true"
SERVERS_COUNTstringNumber of Postgres VMs in cluster."1""2"
SERVER_IMAGEstringOS and Postgres versions available on the server image."ubuntu-2204-postgres-17beta1-163-157-1412-1315-1219""ubuntu-2204-postgres-162-156-1411-1314-1218"
SERVER_FSTYPEstringFile system type."ext4""xfs"
FLUSH_CACHESbooleanFlush the page cache and restart Postgres before each run of test experiments.falsetrue
VOLUME_SIZE (required)stringDisk size, in GiB."100""200"
VOLUME_TYPEstringVolume type."pd-ssd""pd-extreme"
CLEANUP_AFTER_SECONDSstringHow long (in seconds) the server stays up after the test finishes."1""60"
PGBENCH_INIT_COMMANDstringpgbench initialization command."pgbench -i -q -s 100""pgbench -i -q -s 500"
PGBOUNCERstringUse PgBouncer or not."false""true"
PGBOUNCER_COUNTstringNumber of PgBouncers to be installed (with SO_REUSEPORT)."1""2"
HAPROXYstringUse HAProxy or not."false""true"
POSTGRES_VERSION (required)stringPostgres major version. Options: 10, 11, 12, 13, 14, 15, 16, 17."16""15"
TEST_RUNS (required)objectJSON object containing test series description.N/ASee below.

TEST_RUNS (JSON object)​

ParameterTypeDescriptionDefaultExample
pre_configsarrayOptional array of configuration settings specific to this scenario.N/A["wal_compression=off"]
pre_shellstringOptional bash snippet to execute prior to workload.N/A"echo '[some sql]' > /tmp/part1.sql && echo '[another sql]' > /tmp/part2.sql"
pre_sqlstringOptional SQL commands to be executed prior to workload.N/A"SET work_mem = '64MB';"
workload_sqlstringWorkload SQL queries to be executed in this scenario.N/A"SELECT pg_sleep(1);"
workload_pgbenchstringWorkload command to be executed in this scenario. Unless specified otherwise, always use options -P10 and -r. Values for -c and -j should be the same (e.g., -c 4 -j 4), unless specified otherwise. If no -c/-j values are provided, use a half of vCPU count (e.g., for 32-vCPU machine: -c 16 -j 16).N/Apgbench -c 10 -j 10 -T 600 postgres

Tool get_experiment_results​

Access and analyze experiment's results, or get pipeline's job statuses if the experiment is still running.

Input parameters​

ParameterTypeDescriptionDefaultExample
pipeline_id (required)stringPipeline ID.N/A12345

Tool sql_execute​

Connects to Postgres database and executes SQL query. If database credentials are provided, they are used to establish Postgres connection. Otherwise, a new DBLab clone is created using create_dblab_clone and then clone's credentials are used.

Input parameters​

ParameterTypeDescriptionDefaultExample
SQL (required)stringSQL query.N/ASELECT * FROM users
DB (required)objectDB connection credentials.N/ASee below.

DB (JSON object)​

ParameterTypeDescriptionDefaultExample
db_name (required)stringDB name.N/Amy_database
user (required)stringDB user name.N/Aadmin
password (required)stringDB password.N/Apassword
host (required)stringDB connection hostname or IP address.N/Alocalhost
port (required)stringDB connection port number.N/A5432

Tool create_dblab_clone​

Create a new DBLab clone for a specific Postgres major version (16 by default). This function is called when user wants to execute a SQL query but hasn't provided DB connection information.

Input parameters​

ParameterTypeDescriptionDefaultExample
POSTGRES_VERSION (required)stringPostgres major version. Options: 12, 13, 14, 15, 16, 17. Default: 161613

Tool reset_dblab_clone​

Reset DBLab clone to its original state.

Input parameters​

ParameterTypeDescriptionDefaultExample
CLONE_ID (required)stringID of existing DBLab clone.N/Aclone123

Tool sql_plan_analysis_helper​

Get EXPLAIN plan optimization insights from pgMustard.

Input parameters​

ParameterTypeDescriptionDefaultExample
plan (required)stringPostgres query plan in string format.N/ASeq Scan on users (cost=0.00..34.50 rows=2450 width=12) (actual time=0.012..0.045 rows=2450 loops=1)\nExecution Time: 0.067 ms