Skip to main content

PostgreSQL databases

You’ll create a PostgreSQL database with durable disk / storage and connection details in the dashboard.

Unsure which path to use? Start at Choose a database path. This page is the Databases path.

Before you start​

  • Connected cluster and an environment
  • Permission to create a database on your role (owners and Full access members have it)
  • Free disk for the size you pick

Create the database​

  1. Open Databases → create PostgreSQL.
  2. Select the environment.
  3. Set name, storage size, and resources.
  4. Review credentials the form shows — store them safely.
  5. Create and wait until status is healthy.

Connect an app​

  1. Open the database detail page; copy host, port, database name, and user.
  2. Put them in the app’s variables or a shared variables group.
  3. Restart the app if needed, then confirm in logs.

You’re done when​

  • Database status is healthy.
  • An app in the same cluster connects with the shown endpoint.
  • Data survives an instance restart.

Turn on backups before production data.

Read query statistics​

PostgreSQL records which statements use the most time. Use that record to find slow queries. It turns on the next time the database is updated. Until that update finishes, pg_stat_statements is not available yet.

That update restarts the database, because PostgreSQL loads the record only at startup. With one copy, the database is unavailable during the restart. With more than one copy, Xooshe updates the copies and moves the primary over; apps should reconnect. Wait until the database is healthy before you measure.

Connect with the user shown on the database page:

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

You see statements executed by that user. Literal values in the text are replaced with placeholders ($1). Counters start empty the first time the record is turned on and are kept across later restarts.

Recording statements uses a small amount of extra memory and CPU.

Change how much is recorded​

Open the database → Custom Config. Your role needs change on Custom config (owners and Full access have it).

Add the keys below beside whatever is already in the editor, then choose Save Changes. Saving updates the database. Loading or unloading the record, or changing max, restarts it the same way as the first enable. Wait until the database is healthy.

To keep more statements, and to include statements that run inside functions:

{
  "postgres_parameters": {
    "pg_stat_statements.max": "10000",
    "pg_stat_statements.track": "all"
  }
}

max is how many distinct statements to keep. The default is "5000". track defaults to "top", which records the statement you sent. "all" also records statements inside functions and costs more CPU on a very busy database. Write the values as strings, in quotes.

To turn the record off, set a boolean false (this removes the pg_stat_statements view after the restart):

{
  "pg_stat_statements": false
}

Xooshe enables the library from these settings. A shared_preload_libraries entry in Custom Config is ignored.