Performance Insights and events

​

Goal

Find out why a database is slow, and what has happened to it recently, without logging in to the server.

​

Prerequisites

• A Dedicated database that is running
• Read access to the database. Changing the history length needs write access.

​

Workflow

​

Database load

The load chart shows average active sessions: how many sessions were busy at the same moment, per time bucket. Idle moments read as 0. The bucket size grows with the range, from one minute up to a day.

• from and to are RFC 3339 times (--query from=… --query to=… on the CLI). The range is limited to your history length, and to stops at the newest collected minute, so the chart can end a minute or two before now.
• group_by=wait groups by wait type, for example CPU, IO, Lock or LWLock. group_by=sql groups by statement, and group_by=user by database user.
• At most 8 series are returned. The rest are merged into one series named other.
• Add instance_id to look at one server (the primary, standby or a replica). Without it, the load of every server is added up.
• An empty database returns no series.

​

Top statements

• Statements are ranked by total time. Each shows its calls, total and mean time, rows, the database users that ran it and its share of the total time.
• limit defaults to 25, at most 100. from, to and instance_id work as for the load.
• PostgreSQL statements are normalised, so values show as $1.

​

Explain a statement

• Send either a query_id from the top statements or your own sql, never both.
• You get the engine’s plan as JSON. The statement is never run, so there is no ANALYZE and nothing is changed.
• Only a single read-only SELECT is accepted. Inserts, updates, deletes, schema changes, SELECT … INTO, FOR UPDATE, several statements in one and data-changing WITH queries are refused.
• A normalised statement with $1 placeholders is planned as a generic plan, which needs PostgreSQL 16 or newer. On PostgreSQL 15, send your own SQL with real values.
• A statement that was stored cut short (2,000 characters or more), or one that has aged out of your history, can’t be explained.
• The database must be running, or the answer is 409.

​

How long history is kept

• stackshift database insights DATABASE (GET /api/v1/databases/{id}/insights) shows your history length in retention_days and the options you can choose from.
• 7 days is included. 30 and 90 days cost extra: see https://stackshift.cloud/pricing.
• Change it with stackshift database insights-set DATABASE --data '{"retention_days":30}' (PUT /api/v1/databases/{id}/insights). A paid change works like the other paid changes: you get 409 price_confirmation_required with the quote, then resend with confirm_charge_minor. If your wallet is short, the answer is 402 payment_required with a checkout_url.
• Going to a shorter history credits the difference. Older history is deleted at the next daily clean-up.
• Any other number of days is refused with 400.

​

Events feed

stackshift database events DATABASE (GET /api/v1/databases/{id}/events) lists what happened to the database, newest first. It returns events (each with an id, type, severity of info, warning or critical, a title, a message and created_at) and next_before. To read older events, pass next_before as before (--query before=ID on the CLI). limit defaults to 50, at most 200.

• Types: failover, switchover, autogrow, backup_failed, backup_restored, maintenance_scheduled, maintenance_applied, maintenance_failed, storage_80_percent, replica_lag, pending_restart, size_changed, backup_history_gap, backup_history_restarted, and move_started, move_cutover, move_completed, move_failed for a database moved from Starter.
• Conditions (backup_failed, storage_80_percent, replica_lag, pending_restart) raise one event when they start, and again only after they have cleared.
• Each event is also added to your inbox. Warnings, restore notices and maintenance notices are emailed too.

​

Availability

Dedicated databases are rolling out. If Create doesn’t offer Dedicated, it isn’t switched on for your account yet, and the API answers 403.

​

Expected result

​

Common failures

​

Related guides