# Recommendations: make your database better

> What to change in your schema, queries and server, why, and what it costs.

Source: https://rowsafe.sh/docs/guides/recommendations

Health says what's wrong now. **Recommendations** say what will bite later, or what would make the database faster or cheaper: an id column running out of numbers, a foreign key without an index, an N+1 query.

Each says **what** to change, **why** (with numbers) and **what it costs**. Safe ones have an **Apply fix** button; the rest come with a plan. They don't affect the health score, and they're on every plan.

Examples are PostgreSQL's. See also [other engines](#mysql-mariadb-mongodb-and-clickhouse), [Redis and Valkey](#redis-and-valkey) and [SQLite](#sqlite).

## See them

Open a database, then **Pulse → Recommendations**, grouped into **Schema**, **Queries**, **Capacity** and **Indexes**. [Your weekly Pulse](https://rowsafe.sh/docs/guides/monitoring#your-weekly-pulse) names each database's top one.

```sh
rowsafe recommendations            # every database's top recommendation
rowsafe recommendations app        # all of them, with why and what it costs
rowsafe recommendations app --group queries
```

## Apply a fix

Click the button (like **Create the index**), read what will happen, and confirm. You see each step and the result ("Created the index orders_customer_id_idx on public.orders (1.3 GB) in 41s"). The [Apply fix safety rules](https://rowsafe.sh/docs/guides/monitoring#what-keeps-it-safe) hold, including for [AI agents](https://rowsafe.sh/docs/guides/agent-autonomy).

| Recommendation                                                   | Button                        | What Rowsafe does                                                                                                                                                                                                          |
| ---------------------------------------------------------------- | ----------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| A foreign key without an index                                   | **Create the index**          | `CREATE INDEX CONCURRENTLY` on the referencing columns. Skipped if such an index appeared meanwhile; a failed build is cleaned up. Reads and writes keep working.                                                          |
| A sequence behind its column (inserts fail with "duplicate key") | **Move the sequence forward** | `setval` to the column's highest value, if still behind.                                                                                                                                                                   |
| An invalid index left by a failed concurrent build               | **Remove the invalid index**  | `DROP INDEX CONCURRENTLY`, if still invalid and no index is being built on the table.                                                                                                                                      |
| Autovacuum waits too long on a large, busy table                 | **Tune autovacuum**           | `ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = N, ...)`: cleanup after a fixed number of changed rows. Only autovacuum settings, only numbers. The task log shows how to undo it. |

CLI: `rowsafe fix app` lists these with the health fixes; `rowsafe fix app fk_without_index:app.public.orders.orders_customer_id_fkey` applies one.

## Not relevant?

Click **Not relevant**, pick a reason (not relevant here, on purpose, later, wrong) and add a note if you like. It moves to **Dismissed** for your whole organization, with who and why. **Bring back** undoes it.

```sh
rowsafe recommendations app --dismiss no_primary_key:app.public.event_log --reason intended --note "append-only log"
rowsafe recommendations app --restore no_primary_key:app.public.event_log
```

## What Rowsafe looks at

About every 30 minutes the agent reads catalog facts (read-only, 5-second timeout, never your rows), plus a day of `pg_stat_statements` and a week of metrics.

### Schema

| Recommendation                           | When                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| ---------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **An id column will run out of numbers** | A `smallint`/`integer` column filled from a sequence (serial or identity), or an integer column pointing at one, is 50% used (warning) or 80% (critical), or will run out within 90 days at the current pace (with the date). The plan: widen it to `bigint` without downtime (a copy column, batches, a quick swap), or with one `ALTER` in a maintenance window, with an estimate of how long that blocks the table. If only the sequence stops at the integer maximum and the column is already `bigint`, `ALTER SEQUENCE ... AS bigint` is enough. |
| **A sequence is behind its column**      | The sequence's next value already exists in the column, usually after rows were copied in with their ids.                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| **A foreign key without an index**       | The referencing columns don't lead any index, on a table of at least 10,000 rows. Deletes on the referenced table then read the whole referencing table.                                                                                                                                                                                                                                                                                                                                                                                               |
| **A broken index from a failed build**   | An index PostgreSQL marked invalid (a failed `CREATE INDEX CONCURRENTLY`), not being built now.                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| **A table without a primary key**        | No primary key and no non-null unique column. Logical replication, online migrations and bringing back rows with Rewind need one.                                                                                                                                                                                                                                                                                                                                                                                                                      |
| **Duplicate constraints**                | The same unique rule, foreign key or check twice, or a unique rule another already implies.                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| **Times without a time zone**            | `timestamp without time zone` columns in tables that get writes.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| **A large table to partition by time**   | Over 10 GB, insert-only, with a time column.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |

### Queries

Need `pg_stat_statements` ([Slow queries](https://rowsafe.sh/docs/guides/monitoring#slow-queries)).

| Recommendation                     | When                                                                                                                                   |
| ---------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------- |
| **N+1 queries**                    | A cheap one-row query runs at least once a second, and its calls rise and fall with another query's (a list, then one query per item). |
| **Reads far more than it returns** | At least 100 pages read per row returned and 8 MB per call, and at least 1% of all query time. Links to the index suggestions.         |
| **Returns huge results**           | Over 10,000 rows per call, without `LIMIT`.                                                                                            |
| **Sorts on disk**                  | Over 10 MB of temporary files per call, or 1 GB a day.                                                                                 |

### Capacity

| Recommendation                            | When                                                                                                                                              |
| ----------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Busiest data no longer fits in memory** | Over the last week, under 95% of reads came from memory for a large part of the time. Shows your database and memory sizes, and `shared_buffers`. |
| **CPUs often fully busy**                 | Load above the CPU count for at least 12 hours and 10% of the week.                                                                               |
| **Connections near the limit**            | Connections reached 90% of `max_connections`, or 80% for several hours.                                                                           |
| **The disk will be full**                 | The [disk forecast](https://rowsafe.sh/docs/guides/monitoring#disk-forecast) says within 90 days.                                                                   |
| **Autovacuum waits too long**             | A table of over a million rows had updates and deletes of at least 10% of its rows, with the default scale factor (20%).                          |

### Indexes

Health's unused, duplicate and missing index findings, plus **new indexes proven on a copy of your database**.

Every night at your database's quietest hour (03:00 UTC until there's a day of statistics), Rowsafe takes the costliest queries, works out indexes that could help, and **tests each on a copy** restored from your backups next to production. You only see indexes PostgreSQL actually used on the copy, with:

- **how much faster** each query got (SELECTs timed for real; data-changing statements compared by cost estimate, never run);
- its **disk** use and **build time**;
- its **cost to writes**: one more index to update.

The **index check** box shows the last run ("Looked at 24 busy queries and tested 3 index ideas on a copy: 1 is worth adding"), live progress, and **Check now**. **More options** sets the schedule: nightly (default), your own UTC cron, or off. Ideas tested in the last 7 days aren't retested. **Tested but not recommended** lists the rest, with why.

**With hypopg.** Where the `hypopg` extension is on, Rowsafe first tries each idea as a hypothetical index on production: PostgreSQL only plans the queries, in a read-only session. Nothing is built and no data is read. Only ideas it would use go on to the copy. When no copy can be made (no backup yet, not enough disk), they show as **Checked without a copy (estimate)**, and the next check tests them on a copy when there's room. Installed but off? Pulse offers **Turn on hypopg**.

**Create index** runs `CREATE INDEX CONCURRENTLY` (reads and writes go on), named `rs_<table>_<columns>_idx`. Rowsafe first rechecks the table, columns and existing indexes, refuses on a replica or with less than about twice the index size free, waits at most 5 seconds for a lock, and removes a half-built index on failure.

A week later it reports the real effect ("used 1.2 million times in its first week, and the queries it was made for got about 31× faster"). A new index isn't called unused for two weeks.

```sh
rowsafe recommendations app --group indexes   # the proven indexes, and the last index check
rowsafe recommendations find app              # test index ideas now
rowsafe recommendations schedule app off      # auto (every night), off, or a cron (UTC)
```

**From queries to index ideas**

The agent parses your busiest statements with PostgreSQL's own parser (libpg_query, built in). Per table it notes `=`/`IN` columns, ranges, joins, `ORDER BY` with `LIMIT`, and `IS NULL`. Ideas put the most selective equality columns first, then a range or sort column; a partial index (`WHERE processed_at IS NULL`) when that skips most of the table; `INCLUDE` columns when clearly better. It skips small tables, low-variety columns, queries that read most of the table, and what existing indexes cover. At most 3 ideas per table, 12 per database.

**Testing on the copy**

The copy is restored like a [Proof run](https://rowsafe.sh/docs/guides/restore): own directory, private socket, no network port, no archiving, no background workers, low CPU and IO priority. It needs about 1.3 times the database size plus 1 GB free, or the check is skipped with a reason.

Per idea: `EXPLAIN` the table's queries (`EXPLAIN (GENERIC_PLAN)` on PostgreSQL 16+), `CREATE INDEX`, `EXPLAIN` again, time SELECTs with `EXPLAIN ANALYZE` in a read-only transaction (10 seconds max each), `DROP INDEX`. Recommended only if used and at least one query got at least twice as fast. Among ideas for the same queries, the most time saved per megabyte wins.

The copy is deleted afterwards. Only query IDs, names, costs, timings and sizes reach Rowsafe.

#### ClickHouse

ClickHouse finds rows by each table's sorting key (`ORDER BY`), so ideas add to it. Rowsafe reads last week's slowest SELECTs from `system.query_log` on your server (texts never leave it), keeps those reading far more rows than they return, and tests on a copy of those tables in a temporary ClickHouse on the same server. Ideas:

- a **skipping index** on a column the sorting key doesn't start with: `bloom_filter` for `=` and `IN`, `set` for few values, `minmax` for ranges, `tokenbf_v1` for `hasToken`, `ngrambf_v1` for `LIKE`;
- a **projection** that pre-aggregates a busy `GROUP BY`, or sorts by another column (only if a skipping index didn't help);
- a **sorting key** for a table without one. Advice only: it means rebuilding the table (steps under **Do it yourself**).

Recommended only when queries on the copy read at least 2 times fewer rows and get faster.

**Add skipping index** / **Add projection** saves a Mark, runs `ALTER TABLE ... ADD INDEX` (or `ADD PROJECTION`) and `MATERIALIZE`. ClickHouse builds it in the background while queries and inserts go on; Pulse shows progress and **Cancel** removes it. Ones no query used in a week (at least 10 MB) show as unused with **Drop**. Projections only on `MergeTree` and `ReplicatedMergeTree`.

## MySQL, MariaDB, MongoDB and ClickHouse

Query and capacity checks work for every engine, from `performance_schema` (MySQL, MariaDB), the profiler (MongoDB) or the query log (ClickHouse). Values are removed on your server.

| Engine         | Checks                                                                                                                                                                                                                                                                                                                     |
| -------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| MySQL, MariaDB | Queries that read far more rows than they return, queries that sort or group on disk, tables without a primary key, indexes nothing uses and indexes another index covers (with **Drop**, online, a Mark first). Index suggestions tested on a copy (samples from `performance_schema`, or the slow query log on MariaDB). |
| MongoDB        | Operations that read far more documents than they return, the profiler being off (with **Turn on query statistics**), indexes nothing used for a week and redundant indexes (with **Drop**), and index suggestions tested on a copy.                                                                                       |
| ClickHouse     | Large tables without a sorting key, tables split into too many partitions, and skipping indexes and projections tested on a copy ([above](#clickhouse)), with **Add** and **Drop**.                                                                                                                                        |

## Redis and Valkey

Recommendations here are about keys, memory and commands. **Key names and values never leave your server**: only key-name patterns, counts and sizes.

About every 30 minutes the agent samples gently, on its own connection:

- up to 10,000 keys per logical database, 100 at a time (`SCAN`, `TYPE`, `PTTL`, `MEMORY USAGE`), changing nothing, not even eviction order;
- pausing at least 4 times as long as each batch took, stopping after 90 seconds per logical database and 5 minutes in all (it says when it sampled only part);
- command statistics and the slow log (command names only), and a few settings.

Key names become patterns on your server: ids become `*`, so `user:4821:cart` becomes `user:*:cart`. A pattern is reported only if it covers at least 10 sampled keys and 0.5% of them.

**How key names become patterns**

Every part that looks like an id becomes `*`: anything with a digit, long hex strings, emails, long or random-looking words. Words with many values at the same place (user or customer names) become `*` too. Words naming a kind of key, like `session` or `cart`, stay. Keys no pattern covers are only counted. The control plane checks every pattern again and drops anything that doesn't look like one.

| Recommendation                                         | When                                                                                                                                                                                                                               |
| ------------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Big keys**                                           | Sampled keys of 10 MB or more, with their patterns. Reading one whole, deleting it or letting it expire blocks every other client meanwhile.                                                                                       |
| **Where the memory goes**                              | The biggest pattern holds at least 20% of the data (on servers with at least 64 MB): the top five patterns, with estimated size, key count and how many have no expiry.                                                            |
| **Keys that never expire**                             | A pattern whose name says it's temporary (`cache`, `session`, `lock`, `tmp`, `otp`, rate limits...) where most keys have no expiry.                                                                                                |
| **Eviction that can't free memory**                    | A `volatile-*` eviction policy with a memory limit, while under half the keys have an expiry: once those are gone, writes fail.                                                                                                    |
| **No memory limit**                                    | `maxmemory` isn't set (if memory is already mostly used, Health says so instead).                                                                                                                                                  |
| **KEYS**                                               | `KEYS` called at least 10 times since start (or once for 10 ms or more): it goes through every key at once. Use `SCAN`.                                                                                                            |
| **Commands that go through a whole key**               | `HGETALL`, `SMEMBERS`, `LRANGE`, `ZRANGE`, `SORT`, `SUNION` and the like, called at least 100 times and over 1 ms per call on average; a `FLUSHALL` or `FLUSHDB` that blocked for over 100 ms.                                     |
| **Many logical databases**                             | Three or more hold keys: they share one server and can't be scaled apart, and Redis Cluster (and Valkey Cluster before 9.0) only has database 0.                                                                                   |
| **A restart empties the server**                       | Snapshots and the append-only file are both off on a primary. Fine for a cache the app can refill (dismiss it as on purpose). Otherwise Rowsafe's backups still have the data, but the app sees an empty server until you restore. |
| **Writes reach the disk only when the system decides** | `appendfsync no`: a power loss can lose about 30 seconds of writes.                                                                                                                                                                |

These need an app or setting change, so they come with a plan, not a button. **Queries** and **Insights** don't show for Redis and Valkey.

## SQLite

SQLite keeps no query statistics, so recommendations come from the file's schema, read about every 30 minutes and soon after it changes. Only table, column and index names reach Rowsafe.

| Recommendation                                | What to do                                                                                                                                                                                                                                                                                                       |
| --------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| A foreign key without an index on its columns | Rowsafe tests the index on a copy restored from your backups (child tables of 1,000 rows or more): build time, size, and that SQLite uses it. **Create index** saves a Mark, then runs `CREATE INDEX IF NOT EXISTS`. Writes wait while it builds (about the time measured on the copy); reads go on in WAL mode. |
| A table without a primary key                 | Add one in your next migration: Rewind compares and brings rows back by primary key, and a `VACUUM` can renumber the rows of a table without one.                                                                                                                                                                |
| `AUTOINCREMENT` where it isn't needed         | Advice only. `INTEGER PRIMARY KEY` already gives new ids; `AUTOINCREMENT` adds a write to `sqlite_sequence` on every insert. Changing it means rebuilding the table.                                                                                                                                             |
| No query planner statistics (`sqlite_stat1`)  | The **Refresh statistics** fix in [Pulse](https://rowsafe.sh/docs/guides/sqlite#pulse-and-one-click-fixes).                                                                                                                                                                                                                        |
| Many free pages and `auto_vacuum` off         | The **Give free space back** fix in Pulse.                                                                                                                                                                                                                                                                       |
| Rollback-journal mode                         | The **Turn on continuous backups** fix in Pulse.                                                                                                                                                                                                                                                                 |

Rowsafe can't measure how much a created SQLite index is used, only that it's still there.

## Limits

- Estimates (index sizes, rewrite times, run-out dates) come from sizes and rates, not measurements of your disks.
- Query recommendations see only what `pg_stat_statements` keeps.
- On a replica, write-based recommendations are left out; fixes run on the primary.
- Older agents don't send schema facts: update the agent.
- Proven indexes need PostgreSQL 12+, `pg_stat_statements` and backups on. They're b-tree indexes on plain columns (no expression, GIN, GiST or partitioned tables), from the 40 busiest statements of the week. Copies have your data, not your load. Builds estimated over 2 hours are left to you (under **Do it yourself**).
- Redis and Valkey figures are scaled up from the sample. Small databases may show no patterns.
