Skip to content
Rowsafe
Docs

Recommendations: make your database better

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

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, Redis and Valkey and SQLite.

See them

Open a database, then Pulse → Recommendations, grouped into Schema, Queries, Capacity and Indexes. Your weekly Pulse names each database's top one.

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 hold, including for AI agents.

RecommendationButtonWhat Rowsafe does
A foreign key without an indexCreate the indexCREATE 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 forwardsetval to the column's highest value, if still behind.
An invalid index left by a failed concurrent buildRemove the invalid indexDROP INDEX CONCURRENTLY, if still invalid and no index is being built on the table.
Autovacuum waits too long on a large, busy tableTune autovacuumALTER 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.

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

RecommendationWhen
An id column will run out of numbersA 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 columnThe sequence's next value already exists in the column, usually after rows were copied in with their ids.
A foreign key without an indexThe 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 buildAn index PostgreSQL marked invalid (a failed CREATE INDEX CONCURRENTLY), not being built now.
A table without a primary keyNo primary key and no non-null unique column. Logical replication, online migrations and bringing back rows with Rewind need one.
Duplicate constraintsThe same unique rule, foreign key or check twice, or a unique rule another already implies.
Times without a time zonetimestamp without time zone columns in tables that get writes.
A large table to partition by timeOver 10 GB, insert-only, with a time column.

Queries

Need pg_stat_statements (Slow queries).

RecommendationWhen
N+1 queriesA 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 returnsAt 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 resultsOver 10,000 rows per call, without LIMIT.
Sorts on diskOver 10 MB of temporary files per call, or 1 GB a day.

Capacity

RecommendationWhen
Busiest data no longer fits in memoryOver 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 busyLoad above the CPU count for at least 12 hours and 10% of the week.
Connections near the limitConnections reached 90% of max_connections, or 80% for several hours.
The disk will be fullThe disk forecast says within 90 days.
Autovacuum waits too longA 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.

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)

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.

EngineChecks
MySQL, MariaDBQueries 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).
MongoDBOperations 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.
ClickHouseLarge tables without a sorting key, tables split into too many partitions, and skipping indexes and projections tested on a copy (above), 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.

RecommendationWhen
Big keysSampled 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 goesThe 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 expireA 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 memoryA volatile-* eviction policy with a memory limit, while under half the keys have an expiry: once those are gone, writes fail.
No memory limitmaxmemory isn't set (if memory is already mostly used, Health says so instead).
KEYSKEYS 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 keyHGETALL, 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 databasesThree 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 serverSnapshots 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 decidesappendfsync 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.

RecommendationWhat to do
A foreign key without an index on its columnsRowsafe 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 keyAdd 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 neededAdvice 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.
Many free pages and auto_vacuum offThe Give free space back fix in Pulse.
Rollback-journal modeThe 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.
Edit on GitHub