Workload capture records a PostgreSQL query workload over a few days and writes it as a bundle of files. The PlanetScale migration team uses that bundle to plan a sharding scheme for a migration to PlanetScale Neki. Sharding decisions need the query workload, not only the schema.
Capture is opt-in. Nothing here runs during a normal discovery run, and nothing here writes into the discovery output.
It runs from cron over hours or days rather than once, because a sharding scheme needs to know how often each query runs, not only that it exists. See How a capture works over time for the shape of a capture and how long to run one.
The tool measures your workload and hands the measurements to the migration team, whose planning tools work out the sharding scheme from them.
A capture is not one reading. It is a series of readings, and what you hand over is the difference between them.
pg_stat_statements counts forward from the moment it started collecting. A
single reading tells you a statement has run 4 million times, but not whether
that took a week or a year. Two readings, an hour apart, tell you it ran 12,000
times in that hour. The second answer is the one a sharding scheme needs, so the
tool takes many readings and differences each consecutive pair.
hour 0 hour 1 hour 2 hour 3
────┬─────────────┬─────────────┬─────────────┬────────► time
│ │ │ │
init collect collect collect
│ │ │ │
snap #1 snap #2 snap #3 snap #4
│ │ │ │
└── delta ────┴── delta ────┴── delta ────┘
1h 1h 1h
│
▼
finalize sums every interval
into one measured window
Three properties follow from this shape, and they are what make the capture safe to leave running:
- Every snapshot is a separate file, written once and never modified. A tick that fails, or two ticks that overlap, cost you nothing but that interval.
- The capture holds no state on the server. Nothing is reset, nothing is written, and stopping is deleting a cron line.
- You can stop whenever you like.
finalizedifferences whatever snapshots exist. More snapshots mean a longer window, not a different format.
The window you measure is the whole workload the sharding scheme is designed against. Anything that did not run during the capture does not exist as far as the sharding scheme is concerned, so the goal is to cover a representative slice of real life rather than to run for as long as possible.
Start from what you know about your own traffic:
| If your workload has | Capture for | Because |
|---|---|---|
| A daily peak, and nothing unusual otherwise | 24 hours, hourly | One full daily cycle, peak and trough |
| Nightly batch jobs, reports, or ETL | 2 to 3 days, hourly | The batch window plus the days on either side |
| Weekly jobs: billing runs, weekend reports, Monday backfills | A full 7 days, hourly | A weekly job that runs once is invisible in a 3-day capture |
| Month-end or quarter-end processing | Cover the boundary itself | Those jobs often touch tables nothing else touches |
Two or three days is the usual answer. A week is better when you know something important happens weekly, and the cost of the longer capture is small: hourly snapshots are roughly 80 KiB each, so a week is around 13 MiB on disk.
Name the jobs you know about before you start. A nightly reconciliation that rewrites a large table, a weekly export that reads across every tenant, a month-end billing run — each of these can be the query that decides a shard key, and each is easy to miss. Write the list down, check the capture covered them, and tell your migration engineer which ones fell inside the window and which did not. A capture that missed the weekly job is still useful, as long as everyone knows it was missed.
Mon Tue Wed Thu Fri Sat Sun
──────────────────────────────────────────────────────────────
███ ███ ███ ███ ███ ██ ██ daily peak
▒ ▒ ▒ ▒ ▒ ▒ ▒ nightly batch
████ weekly billing
──────────────────────────────────────────────────────────────
[───── 3-day capture ─────]
sees the daily peak and the nightly batch, never sees the billing run
[────────────────── 7-day capture ───────────────────────────]
sees every recurring job above
A short capture is still worth having. Two snapshots an hour apart give a real window, just a narrow one. Say so when you hand the bundle over, because a one-hour window measured at 14:00 on a Tuesday describes exactly that and nothing else.
What you should not do is finalize a session with a single snapshot and treat
the result as traffic. finalize refuses that by default, and --allow-partial
exists for the case where lifetime totals are genuinely all you can get. The
bundle then says so in its caveats, and no rate can be derived from it.
Work through this list once. workload init --check checks every item and
tells you what is missing. It is read-only and creates no session, so run it
early, before you commit to a capture.
Capture is off by default, so the flow cannot start by accident. Set
enabled: true under database.workload in your config file:
database:
workload:
enabled: trueWithout it, init, collect and finalize stop with exit code 1 and name the
setting. Only status runs, because it reads the session and never touches the
server.
setup.sh asks whether you will run a workload capture. Answer yes, and it
installs the optional SQL parser pglast. If you answered no, or you set up the
tool another way, install the parser now:
pip install 'pglast>=7,<9'The parser only checks that the rendered schema.sql parses. Without it,
finalize still writes the complete bundle and records the skipped check as a
caveat.
Workload capture needs less access than a discovery run. It reads no user table.
It needs SELECT only on the columns you declare for
step 6:
CREATE USER planetscale_workload WITH PASSWORD 'secure_password_here';
GRANT CONNECT ON DATABASE your_database TO planetscale_workload;
GRANT pg_monitor TO planetscale_workload;pg_monitor lets the capture role read the statistics views. Without
it, PostgreSQL hides the statement text of every other role behind
<insufficient privilege>, and the capture would describe only the statements
your capture role ran itself. That result looks complete and is badly wrong, so
the tool refuses it rather than reporting it.
Turning on capture_log needs more, both in the database and in your cloud
account, and the extra access belongs to whoever sets the capture up rather
than to the capture role. See
Permissions for log capture.
The capture role also needs a column-level SELECT grant on each column you
declare in step 6. See
Grant the capture role each column.
You can reuse the discovery role from the
Required PostgreSQL Privileges
section instead. Add pg_monitor to it if it does not have it.
This step is required. pg_stat_statements is the only place PostgreSQL
records the query workload, and a sharding scheme is planned from that workload.
Without it there is nothing to capture, so init stops rather than producing a
bundle that cannot be planned. Table and index counters say a table is read
often; they never say which column a query filtered on.
The extension needs two things, and the order matters:
- The library must be listed in the
shared_preload_librariesserver setting. Changing that setting needs a restart, because PostgreSQL loads the library at startup. - The extension must then be created in the database:
CREATE EXTENSION pg_stat_statements;
Step 2 alone does nothing. The extension is created, the view appears, and it
stays empty forever. workload init reports that state specifically, because it
is the failure that wastes a capture window.
Most managed providers preload the library for you, so step 1 is usually already
done and only CREATE EXTENSION is left. Find your provider below.
pg_stat_statements is not a trusted extension, so CREATE EXTENSION
normally requires a superuser or the provider's equivalent admin role. On a
managed service you may not have one. If that blocks you, raise it with your
PlanetScale migration engineer early: capture cannot proceed without it, and the
right people can usually get the extension enabled once they know it is a
prerequisite rather than a preference.
| Where the database runs | Library preloaded? | What you do |
|---|---|---|
| Self-managed | No | Edit postgresql.conf, restart, then CREATE EXTENSION |
| Amazon RDS and Aurora | Yes, by the default parameter group | CREATE EXTENSION as rds_superuser |
| Cloud SQL | Normally yes | CREATE EXTENSION. Add the database flag only if init reports it missing |
| AlloyDB | No | Set the shared_preload_libraries flag, restart, then CREATE EXTENSION |
| Supabase | Yes, enabled on every project | Nothing. It is already on |
| Heroku Postgres | Yes | CREATE EXTENSION |
| Neon | Yes | CREATE EXTENSION, and read the warning below |
Add the library to postgresql.conf, keeping any entries already there:
shared_preload_libraries = 'pg_stat_statements'
Restart PostgreSQL, then, as a superuser:
CREATE EXTENSION pg_stat_statements;The default parameter group already loads pg_stat_statements, so usually you
only need to create the extension. Connect as a member of rds_superuser:
CREATE EXTENSION pg_stat_statements;If init reports the library is not loaded, you are on a custom parameter group
that dropped it. Add pg_stat_statements to shared_preload_libraries in that
group, then reboot the instance, because it is a static parameter. For
Aurora, set it on the cluster parameter group.
Check rds.allowed_extensions if CREATE EXTENSION is refused. That parameter
can restrict which extensions may be installed at all.
pg_stat_statements is normally preloaded, so create the extension:
CREATE EXTENSION pg_stat_statements;If init reports the library is not loaded, add it to the
shared_preload_libraries database flag and restart the instance. Set the flag
as a comma-separated list, keeping anything already there:
gcloud sql instances patch INSTANCE_NAME \
--database-flags shared_preload_libraries=pg_stat_statements
Setting --database-flags replaces the whole list, so include every flag
the instance already has, or you will silently drop one.
AlloyDB does not preload the library by default. Set the
shared_preload_libraries flag on the primary instance to include
pg_stat_statements, restart the instance, then create the extension:
CREATE EXTENSION pg_stat_statements;Nothing to do. Every Supabase project has pg_stat_statements enabled by
default; the query performance page in the dashboard is built on it. If it has
been turned off, re-enable it under Database > Extensions.
The library is preloaded and you cannot change shared_preload_libraries.
Create the extension:
CREATE EXTENSION pg_stat_statements;heroku pg:outliers reads the same view, so if that command returns rows, the
extension is working.
Create the extension:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;Neon needs one extra decision. When a Neon compute suspends or scales to zero, the collected statistics are lost. A capture spanning several days on a compute that idles will keep restarting from nothing, and the result will describe only the periods between suspensions.
Before capturing on Neon, disable scale-to-zero on the branch you are measuring,
or accept that the window covers only active periods. Capture handles the resets
correctly rather than reporting negative numbers, and finalize counts them in
the caveats, so check that count before you quote any figure.
These affect what the capture can see. All are optional. init reports each one
that will limit the result, so you do not have to check them by hand.
| Setting | Why it matters |
|---|---|
pg_stat_statements.max |
The number of statements tracked, 5000 by default. When the table is full, PostgreSQL evicts the least-used statements. An evicted statement is one the capture never sees, and nothing records that it existed. Raise it on a busy database |
pg_stat_statements.track |
top, the default, records the statement the application sent. If your application does its work inside functions or procedures, top records only the call, so the statements that touch tables stay invisible. all records those too |
track_io_timing |
Off by default. While it is off, every block read and write time reads 0, which means "not measured" rather than "fast" |
Raising pg_stat_statements.max needs a restart. The other two are runtime
settings, and both add measurement overhead, so change them with the same care
as any other production setting.
Run it against the primary. A standby counts only the statements that
executed on it, and the write workload is what decides a shard key. init
refuses a standby unless you pass --allow-replica.
The host that runs the capture needs cron, the tool installed, and a writable directory for the session. It does not need to be the database host.
This step is required. Your PlanetScale migration engineer gives you a list of columns, normally the candidate shard keys. The capture records the most common values of each of those columns, and how often each value occurs. A sharding scheme cannot be planned without them.
- Add the columns under
database.workload.distributionsin your config file. See Declare the columns. - Grant the capture role
SELECTon each column. See Grant the capture role each column.
init checks each declared column. It stops when a column does not exist, or
when the capture role cannot read it, and it prints the GRANT to run. When
every column passes, init lists the columns whose values the capture records.
Start with init --check. It reports every problem the server has before you
create a session, and it changes nothing. Fix what it reports, then run it
again until it reports no problem.
# Report what this server can supply, and change nothing
ps-discovery workload init --check
# Check the server, store the schema, take the baseline snapshot
ps-discovery workload init --session ./workload-session
# Append a snapshot. Safe to run from cron
ps-discovery workload collect --session ./workload-session
# Difference the snapshots and write the bundle
ps-discovery workload finalize --session ./workload-session
# Check on a session, with no database connection
ps-discovery workload status --session ./workload-session
The tool finds ./config.yaml on its own, as it does for a discovery run. Use
--config only to point at a different file.
There is no other command. Reading the query log, described in Capturing transaction shapes and values, is a config setting on this same flow, not a separate step.
--session names a directory that this tool creates and owns. Give all four
commands the same directory. It holds:
workload-session/
schema.json the schema, captured once by init
snapshots/
snapshot-20260826T211338.json.gz one file per collect
snapshot-20260826T211522.json.gz
bursts/
burst-20260826T211400.json.gz one per collect, with capture_log on
workload-<capture id>/ the bundle, written by finalize
You can name the directory anything and put it anywhere the cron user can write.
The directory is mode 0700. Delete it when you have the bundle. To stop
collection, remove the cron entry and delete the directory.
Nothing persists on the database server with capture_log off, and nothing
persists when capture_log_source is file, which reads a log you exported.
Only capture_log_source: log_fdw writes to the server: each collect
creates a work schema, a foreign server and the log_fdw extension to read the
log, and drops all three when it finishes. If a collect is killed between the
two, the next one clears what was left behind, and
ps-discovery workload init --cleanup clears it on demand. See
Leave nothing behind.
Each collect takes one snapshot and exits. It does no scheduling of its own,
so something must call it repeatedly. Cron is the intended driver. init prints
a crontab line you can paste:
17 * * * * cd /path/to/ps-discovery && ./ps-discovery workload collect --session ./workload-session
The line uses minute 17 rather than the top of the hour, where scheduled jobs
tend to collect. Keep the cd. Cron starts in the home directory, so without it
the relative --session path misses and ./config.yaml is not found.
Two snapshots are the minimum, because a window needs two readings to difference. Hourly snapshots for two or three days is the usual shape, which is 48 to 72 snapshots at approximately 80 KiB each. See Choosing the capture period for how long to run in your case, and note that a weekly job needs a capture that spans a week.
Hourly suits most databases. Change the cadence only for a reason:
| Cadence | Crontab | When |
|---|---|---|
| Hourly | 17 * * * * |
The default. Good resolution, small files |
| Every 15 minutes | */15 * * * * |
A short capture, or a sharp peak you want resolved |
| Every 6 hours | 17 */6 * * * |
A capture running for weeks, where volume matters |
A finer cadence does not measure more traffic, only the same traffic in smaller
pieces. finalize sums the intervals either way.
Schedule the capture across a peak. A workload measured only at 03:00 is not the workload you must shard.
init takes the first snapshot, because deltas need a baseline. collect
refuses to run against a session directory that does not exist. Such a session
would have no baseline and no schema, and you would not find out for days.
Each collect writes its own timestamped file and modifies no existing file.
Overlapping cron ticks are therefore harmless, and there is nothing to lock.
status needs no database connection, so it is safe to run at any time and it
is the right thing to paste into a support thread:
ps-discovery workload status --session ./workload-session
Check two things. The snapshot count must rise by one per scheduled tick, and
the newest snapshot must be recent. If the count stops rising, the cron entry is
the first place to look: run the collect command by hand and read the error.
A tick that fails leaves the earlier snapshots untouched, and capture resumes on the next tick. A gap costs you that interval and nothing else.
Run finalize when you have enough snapshots. It writes the bundle and prints a
summary, the caveats, and where to send the result.
finalize reads only the session directory, so you can run it as often as you
like, and you can run it again later after more snapshots arrive.
Compress the whole bundle directory into one archive and send that archive to your PlanetScale migration engineer:
tar -czf workload-bundle.tar.gz -C ./workload-session bundle
Or, if you prefer a zip:
cd ./workload-session && zip -r ../workload-bundle.zip bundle
Send one archive, not individual files. The files reference each other, and a partial set cannot be used.
A bundle is small: it holds statement text and counters, not data, so the
archive is normally well under the attachment limit of any mail or ticket
system. Every file in it is mode 0600, and the directory carries a
.gitignore so it cannot be committed by accident.
It holds table, column and index names, statement shapes, row counts, a
distinct-value count and null fraction per indexed column, and a
manifest.json recording when the capture ran and over which intervals. It
also holds values from your data in distributions.json: the most common
values of the columns you declared, as they are, unless you set
hash_values: true.
With capture_log on, burst.csv holds the literal values from your queries.
See
Privacy below, which
includes how to check the bundle yourself before you send it.
When the migration is planned, delete the bundle and the session directory, and remove the cron entry. Nothing persists on the database server.
The tool reads catalogs and statistics views only. It reads no user table. It
does not sample, count or TABLESAMPLE a user table.
It reads pg_stat_statements, pg_stat_user_tables, pg_stat_user_indexes,
pg_class, pg_index, pg_attribute, pg_namespace, pg_settings,
pg_extension, information_schema.columns, and the catalogs that the schema
analyzer already reads.
It also reads pg_stats. For indexed and constrained columns it reads only
the counts and frequencies. For the columns you declare in
step 6, it also reads
most_common_vals, the most common values themselves.
It also calls pg_total_relation_size, pg_relation_size and
pg_indexes_size on each table and index in scope. These report how many bytes
an object occupies on disk. They read no row, take no lock, and need no
privilege beyond the ones above.
The tool never runs ANALYZE, never calls pg_stat_statements_reset() and
never terminates a backend. It holds no table locks, because the schema comes
from catalog reads and not from pg_dump.
Session settings apply to this tool's own connection only. The tool sets
default_transaction_read_only = on, a statement timeout, a lock timeout, an
idle-in-transaction timeout, and jit = off. It never runs ALTER SYSTEM,
ALTER DATABASE or ALTER ROLE. Some connection poolers reject these settings
as startup parameters. The tool then applies them with SET and verifies them.
Capture needs three things, and init checks each one before it writes
anything:
| Requirement | Why |
|---|---|
pg_monitor on the capture role |
to read the statistics views at all, and to see statement text belonging to other roles |
pg_stat_statements, installed and readable |
it is the only record of the query workload, which is what a sharding scheme is planned from |
SELECT on each declared column |
PostgreSQL shows a column's statistics only to a role that may read the column |
init stops with exit code 5 when one is missing, and names which one. It
reports the case where the extension is installed but not preloaded separately,
because CREATE EXTENSION then succeeds and collects nothing forever. When the
connected role is not a superuser, init names where the setting lives instead
of telling you to run a statement that will fail: a parameter group on RDS and
Aurora, or a database flag on Cloud SQL and AlloyDB.
You are about to send a file to another company, so you should know exactly what is in it. The short version: the bundle describes the shape of your queries and the size of your tables. It also holds one set of values from your data: the most common values of the columns you declare in step 6. A sharding scheme is planned from where those values fall, so the capture cannot leave them out.
| In the bundle | Never in the bundle |
|---|---|
| Table, column and index names | Any row from any table |
| Query shapes, with values removed | The values your queries search for, unless you turn on capture_log |
| How often each query ran, and how long it took | Passwords, connection strings or credentials |
| Row counts and table sizes | Values of any column you did not declare |
| How evenly each column's values are spread | Sampled values from database statistics, other than the declared columns |
| How many rows were written to each table | |
| Your PostgreSQL settings and version | |
| The most common values of each declared column, as they are, and how often each occurs |
The declared columns' values come from pg_stats, the statistics PostgreSQL
keeps for its own query planner. The tool scans no table to get them. By
default the values ship as they are, because the migration team must know
which shard each value goes to. init lists each declared column before the
capture starts, and the bundle's README lists them again. See
Recording the common values of chosen columns.
One more setting adds values: capture_log, which
is off by default, writes the literal values from your queries to burst.csv.
Your application runs this:
SELECT * FROM orders WHERE customer_email = 'ada@example.com' AND total > 500
The bundle records this:
SELECT * FROM orders WHERE customer_email = $1 AND total > $2
The structure is what a sharding scheme is planned from: which tables a query touches, which columns it filters on, and how often it runs. The values are not needed for that, so they are removed before anything is written to disk.
This matters more than it might appear, because PostgreSQL does not always
return pre-normalized text. Statements such as CREATE ROLE app PASSWORD 'secret'
are stored by the database verbatim, so every statement passes through the same
removal step regardless of where it came from. A password is a string literal
like any other, and is removed like any other.
It reads the PostgreSQL catalogs and statistics views — the same information
\d in psql shows you, plus counters. It runs no SELECT against a table of
yours, takes no sample, and counts no rows. PostgreSQL keeps sampled column
values for its own planner in pg_stats, which is the one place in the
catalogs where real values from your tables appear. The tool reads those values
for the columns you declare, and for no other column.
The bundle is plain text. Before you send it, read it:
# The query log, in full
less workload-session/workload-*/workload.sql
# Search it for anything that worries you
grep -i "@" workload-session/workload-*/workload.sql
Every value should appear as $1, $2 and so on. If you find anything that
looks like real data, stop and tell your PlanetScale migration engineer before
you send the file — that is a bug in the tool, and PlanetScale wants to hear
about it.
Your security team can also read the removal logic directly. It is one function,
in planetscale_discovery/common/sanitize.py, and every statement in the tool
passes through it.
The tool sets every file it writes to mode 0600, owner-only, and puts a
.gitignore in the bundle directory so it cannot be committed to a repository
by accident. Nothing is written to your database server, and nothing is sent
anywhere by the tool itself: you decide when and how the bundle travels.
Once the sharding scheme is planned, delete the bundle and the session directory. Ask your migration engineer if you would like PlanetScale to confirm deletion of its copy in writing.
workload.sql holds the queries a sharding scheme has to be designed around:
SELECT, INSERT, UPDATE, DELETE and MERGE. Two kinds of statement are
left out, and finalize reports how many of each.
- Statements that read and write no data, such as
COMMIT,SET,GRANTand schema changes. There is no data for them to reach, so there is nothing to decide about where they run. - Statements that are not your application's. Monitoring agents,
psqlsessions, and this tool's own reads of the system catalogs. They name no table or view from your schema. On one 90-table capture that was 100 statements out of 572, and leaving them in produced every routing error reported against that capture, which buried the real result.
A statement found in existing PostgreSQL statistics but that did not run during
the capture window is excluded. pg_stat_statements keeps a row after the
statement stops running. The baseline subtracts that history, so a row whose
counters do not move has a count of zero for the window and is excluded.
Every query that ran during the window and touches one of your tables is kept, however rarely it ran, because how much a query matters depends on a cost model that the planning tools apply later.
The capture above is built on pg_stat_statements, and that view cannot answer
two questions no matter how long you run it:
- Which tables you write together in one transaction.
pg_stat_statementsrecords statements, not transactions, so it cannot show that anINSERTintoordersand anINSERTintoorder_itemshappened together. Two tables written in one transaction usually belong on the same shard. Get this wrong and those writes become slower, more failure-prone distributed transactions. - How unevenly the values behind a candidate shard key are accessed.
pg_stat_statementsnormalizes every literal to$1, so a query that filters ontenant_idgives no way to tell that one tenant is 40% of the traffic.
Both answers are in one place: the server's own query log. Unlike
pg_stat_statements, it records each statement as it actually ran, values
included, in the order and the transaction it ran in.
So the capture can read the query log as well. It is off by default. A session that never reads the log still produces a complete bundle, with both gaps named in its caveats.
This is a setting, not a command. Nothing about the flow changes: the same
init, the same collect from the same cron entry, the same finalize.
database:
workload:
capture_log: true
capture_log_type: statement
capture_log_file: exported.logcapture_log_type is required. statement reads the server's statement log
and needs no extension. pgaudit reads pgAudit. capture_log_source defaults
to file: you export the window and name it in capture_log_file. Each
collect takes its snapshot as before and then reads the file. The tool reads
the file's encoding itself: plain text, csvlog, or a Cloud SQL or AlloyDB
pgAudit JSON export.
With capture_log_source: log_fdw, the tool reads the server's log over its own
connection instead, and each collect watches the log for
capture_log_seconds after its snapshot, so the log window always falls inside
the snapshot window around it. Keep the schedule interval longer than
capture_log_seconds, or the next collect starts before this one returns.
With the hourly crontab line init prints, anything up to about 45 minutes is
safe.
capture_log_seconds accepts 10 to 3600 and applies to log_fdw only. Keep it
short and off-peak: the overhead is log I/O, roughly 150-300 bytes per
statement, so 10k statements a second for ten minutes is 100-200 MB of log.
If the log cannot be read, the collect still stores its snapshot, logs the
reason as a warning and exits 0. A capture never fails because of this.
capture_log_type selects what was logged. capture_log_source selects where
the log is read. capture_log_type is required when capture_log is on.
statement needs no extension. pgaudit logs one role's traffic, it needs no
write of any kind to your database, and every setting it uses can be changed
without a restart after the extension is enabled.
capture_log_source defaults to file. log_fdw reads the csv log the server
is already writing, over this connection, on RDS and Aurora. That read is
always csvlog. pgAudit over it is capture_log_type: pgaudit plus
capture_log_source: log_fdw.
capture_log_type |
Supported capture_log_source values |
|---|---|
statement |
file (plain text or csvlog). log_fdw on RDS and Aurora, which reads csvlog over this connection |
pgaudit |
file (plain text, csvlog, or a Cloud SQL or AlloyDB JSON export). log_fdw on RDS and Aurora, which reads csvlog over this connection |
ps-discovery workload init --check reports what this server can supply. It
opens a connection, creates no session and changes nothing. Add --json to
sweep an estate.
log_fdw reads the log the server is already writing, over this tool's own
connection, so it needs no export and no restart. It needs the log_fdw
extension, csvlog output and a role holding rds_superuser, and it is the only
source that creates objects in your database.
The AWS guide compares file and log_fdw. To get statements into the log in the first place, set
log_min_duration_statement = 0 for the window; see
Set the logging up yourself.
For a file source, export the log through the provider's own tooling and name the file:
database:
workload:
capture_log: true
capture_log_type: pgaudit
capture_log_file: exported.logA collect with a file source takes its snapshot as usual and then reads the
file, so one schedule still produces both the measured window and the log. Point
capture_log_file at the window you exported before each run: re-reading a file
this session already read changes nothing, because that would double every
count.
If the machine running collect cannot reach the database, the file is still
read and the run says that it took no snapshot. A session of nothing but
imports has no window to measure the log against, so run at least two collects
that can connect.
A file is classified before it is parsed. A leading { or [ is the Cloud
SQL and AlloyDB PgAuditEntry export, either one payload per line or a
gcloud logging read --format=json array, and it is read only when
capture_log_type is pgaudit. A csvlog row is csv. Anything else with a
severity marker (LOG:, ERROR:, and the rest) is plain text.
capture_log_type: statement pointed at a JSON export records no log window.
Per-provider export steps: RDS and Aurora, Cloud SQL and AlloyDB, Supabase. Neon and Heroku cannot produce a log this capture can read; their guides say why.
Log capture asks for access the aggregate capture does not. It splits in two, and the two usually belong to different people.
In the database. The capture role still needs only pg_monitor, except
with log_fdw:
| Task | Needs |
|---|---|
ALTER ROLE app SET pgaudit.log, and the reset afterwards |
A superuser, rds_superuser on RDS and Aurora, or cloudsqlsuperuser on Cloud SQL. A role with ADMIN OPTION on app can also do it |
CREATE EXTENSION pgaudit |
The same, once per instance |
capture_log_source: log_fdw |
rds_superuser on the capture role itself, because it creates the foreign server and reads the log files. This is the one path that raises what the capture role needs |
pg_ls_logdir(), to watch the log volume |
A superuser or pg_monitor, and RDS restricts it |
A GRANT does not reach a session that is already open, so reconnect after
granting.
In your cloud account. The tool never touches your cloud API for this. You enable the logging and export the file yourself, so these are your permissions, not the ones in the discovery policy:
| Platform | To enable pgAudit | To export the log |
|---|---|---|
| RDS, Aurora | Modify the parameter group and reboot | rds:DescribeDBLogFiles and rds:DownloadDBLogFilePortion, or console access to Logs & events |
| Cloud SQL, AlloyDB | Set the instance flag and restart | Read access to Cloud Logging, and the Data Access audit logs must be on for the project |
| Supabase | Enable the extension in the dashboard | Dashboard access to Postgres Logs, or a Logflare API token |
| Self-managed | Edit postgresql.conf, or ALTER SYSTEM and reload |
Read access to the log directory on the host |
log_fdw needs neither column: it reads the log over the database connection,
so no export and no cloud permission. The parameter-group change to add csvlog
and set log_min_duration_statement still applies.
The provider guides carry the exact policy statements and the commands they go with: RDS and Aurora, Cloud SQL and AlloyDB, Supabase.
This tool reads the log. It never turns logging on for you, because statement logging on a production role is your decision to make in your own console, and a setting this tool changed could outlive the process that changed it.
With pgAudit, every setting is dynamic and settable per role. Turn it on, watch what it produces, and turn it off again:
-- 1. ON
ALTER ROLE app SET pgaudit.log = 'read,write,misc';
ALTER ROLE app SET pgaudit.log_parameter = 'on';
-- 2. WATCH, at any point in the window
SELECT count(*) AS files, pg_size_pretty(sum(size)) AS log_size
FROM pg_ls_logdir();
-- files | log_size
-- 4 | 181 MB <- run it twice and you have a growth rate
-- 3. OFF
ALTER ROLE app RESET pgaudit.log;
ALTER ROLE app RESET pgaudit.log_parameter;Run step 3 as soon as the last collect finishes. finalize prints the same
two statements for that reason.
Step 2 is for self-managed PostgreSQL. pg_ls_logdir() needs a superuser or
pg_monitor and RDS restricts it, and Cloud SQL does not expose the log
directory at all. Watch the platform's own metric instead: FreeStorageSpace
in CloudWatch on RDS and Aurora, the log bucket or sink size on Cloud SQL and
AlloyDB, the disk usage graph in the Supabase dashboard.
Replace app with the role your application connects as. Role settings bind at
connection time, so an already-open pooled connection keeps the old settings
until it recycles. Plan the window around the pool's connection lifetime.
Keep pgaudit.log_relation off: object logging emits several entries per
statement and corrupts the record pairing. Keep log_statement at 'none'
during the window, or every statement is logged twice.
Without pgAudit, set log_min_duration_statement = 0 for the window instead,
leave log_line_prefix as it is (the tool reads it from the file), and set
capture_log_type: statement. No extension is needed. The trade-off: the plain
log carries durations and pgAudit does not, but a busy server logs a great deal
at log_min_duration_statement = 0.
Every provider needs one restart to enable pgAudit. After that, no capture needs another.
| Provider | Enablement |
|---|---|
| RDS for PostgreSQL | shared_preload_libraries in the parameter group, reboot, CREATE EXTENSION pgaudit; |
| Aurora PostgreSQL | same, in the cluster parameter group |
| Cloud SQL for PostgreSQL | the cloudsql.enable_pgaudit database flag, restart, CREATE EXTENSION pgaudit; |
| AlloyDB | the alloydb.enable_pgaudit flag, restart, CREATE EXTENSION pgaudit; |
| Supabase | enable pgAudit in the dashboard, or CREATE EXTENSION pgaudit;; no preload step |
Do this on RDS and Aurora too. A pgAudit capture logs one role. With
capture_log_source: file the tool writes nothing to the database. Without
the reboot, set capture_log_type: statement. See
Log type.
Some limits carry into every pgAudit capture. None of them stops a capture being useful, but each one qualifies what a transaction shape proves.
pgAudit does not reliably audit superusers, which is one more reason application traffic should not run as one. Its delivery is best-effort, so entries can be lost on a crash or a full log volume.
An aborted transaction reads as open rather than rolled back. pgAudit logs no
statement issued while a transaction is already in the aborted state, so the
ROLLBACK that ends an errored transaction never appears, and the shape looks
identical to one cut off at the edge of the capture.
Two things outlive the last collect unless you end them, and both cost you
money or disk:
- The statement logging you turned on. Reset it on the role, as step 3 of
the recipe above. Without pgAudit, return
log_min_duration_statementto the value it had.finalizeprints this reminder when the bundle holds a log window. - The exported log files. Delete them once the bundle is built and delivered. They hold literal values from your queries.
Confirm the log volume stops growing afterwards. On RDS, FreeStorageSpace
levelling off is the signal; on a self-managed host, run the pg_ls_logdir()
query once more.
capture_log_source: log_fdw is the only source that creates anything in your
database. Each collect creates a work schema, a foreign server and, if it was
not there already, the log_fdw extension, and drops all three before it
returns. A collect that is killed can leave them behind.
To drop them at any point:
ps-discovery workload init --cleanup
It reports what it dropped and needs no --session. It leaves the log_fdw
extension installed, since it may have been in use before the capture; remove
it with DROP EXTENSION log_fdw.
ps-discovery workload init --check reports the same objects without dropping
anything.
Delete the session directory on the machine running collect once you have the
bundle. Remove the cron entry first.
Privacy above
describes the capture with capture_log off. The log is different, which is why
capture_log is opt-in: burst.csv keeps statement text with its
literal values, exactly as the server logged them. Transaction shapes and
per-value access skew are unanswerable without them.
Treat a bundle that includes burst.csv the way you would treat a database
log: read the file before the archive leaves your organization, and decide
about it the way you would decide about log access. The bundle's own README
says the same thing in its privacy section.
A log window is matched against the snapshot window it was taken alongside. A
window read by collect always falls inside it. An exported file might not;
see W215.
column_stats.json says how unevenly a column's values are spread, never which
values those are. A sharding scheme needs to know which values those are. When
one tenant holds 40% of the rows, the migration team must know which tenant it
is, so they can place it and match it to that tenant's traffic.
The capture records this for the columns you declare, and for no other. It
reads most_common_vals and most_common_freqs from pg_stats: the most
common values PostgreSQL's last ANALYZE sampled, and how often each occurs. It
scans no table. Your PlanetScale migration engineer tells you which columns to
declare.
Declare the columns in your config file before you run init:
database:
workload:
enabled: true
distributions:
by_column:
- organization_id
explicit:
- issues.status
- {schema: billing, table: invoices, column: account_id}by_columnnames a column. Every table in the capture that has a column of that name is read.explicitnames one column of one table, written asschema.table.column, astable.column, or as a mapping oftable,columnand, optionally,schema. Without a schema the entry must match exactly one table. When two schemas hold a table of that name, the entry is skipped and named rather than guessed, so add the schema.hash_valuesdefaults tofalse, which ships the values as they are. Set it totrueto ship a hash of each value instead. See What leaves with the bundle before you do.
Names match regardless of case, the way PostgreSQL folds unquoted names, and an exact match wins over a folded one. A list with one entry can be written as the entry alone.
init resolves each entry and checks that the capture role can read the
column. It stops with exit code 1 when an entry matches no column, matches more
than one, or names a column of a type the capture cannot record. It stops with
exit code 5 when the role cannot read a column, and prints the GRANT to run.
When every column passes, init prints each one and says whether its values
ship as hashes or verbatim.
Each collect reads the declared columns again, and finalize writes the
latest reading into distributions.json. A column you declare after init is
read by the next collect, but init did not check it. A declaration no
collect ever read is named in the bundle's caveats.
PostgreSQL shows a column in pg_stats only to a role that may SELECT that
column. The capture role from
step 2 holds pg_monitor and nothing else,
so it sees no rows in pg_stats at all, and every declared column would be left
out. Grant it each declared column:
GRANT SELECT (organization_id) ON public.issues TO planetscale_workload;A column-level grant is enough, and it is the narrowest grant PostgreSQL offers.
It does let the role read that column's values directly, although this tool
never does: it reads only pg_stats. Revoke the grant when the capture ends.
A table with row-level security enabled shows nothing in pg_stats to a role
its policies apply to, so a declared column on such a table is left out.
By default distributions.json holds the values as they are. A sharding scheme
places each value on a shard, and the migration team can place a value only
when they can read it. Both the file ("values_hashed": false) and the bundle
README say that the values are verbatim. Treat the bundle the way you would
treat a database log.
With hash_values: true, the file holds a SHA-256 hash of each value instead.
The migration team can then match a value to its traffic, but cannot place it
on a shard, so the plan for that column is an estimate. The hash has no salt.
A hash hides a value nobody can guess, like a UUID. It does not hide a status
name or a small integer id, because whoever holds the file can hash every
candidate and compare.
If a column on the list holds values you cannot send, tell your PlanetScale migration engineer before the capture starts.
A declared column that cannot be read is left out of the file and named in the README's caveats. The file's format, the hashing rules and every reason a column is left out are in Workload Bundle Format.
A cron wrapper must be able to tell a real fault from a finished capture.
| Code | Meaning |
|---|---|
| 0 | success |
| 1 | usage or config error |
| 2 | no session at that path |
| 3 | a volume cap was reached; run finalize |
| 5 | the server cannot support collection; read the printed remediation |
Every condition the tool reports carries a code, and each has a section below saying what it means and what to do about it.
E1xx stops the capture. init exits 5 and writes no session.
W2xx does not. The capture continues and the result carries a qualification.
There is no middle case. A sharding scheme is planned from your query workload,
and pg_stat_statements is the only place that workload exists. A run that
cannot read it has nothing to plan against, so it fails rather than producing a
bundle nobody can use.
pg_stat_statements is not available on this server at all.
Install the PostgreSQL contrib package, add pg_stat_statements to
shared_preload_libraries, restart the server, then run
CREATE EXTENSION pg_stat_statements;. On a managed service the library setting
lives in the control plane: a parameter group on RDS and Aurora, a database flag
on Cloud SQL and AlloyDB.
The library is loaded and only the extension is missing.
Run CREATE EXTENSION pg_stat_statements;. The extension is not trusted, so
this needs a superuser. If your role is not one, ask whoever administers the
database. No restart is needed.
Neither the library nor the extension is in place. Two steps, in this order.
Add pg_stat_statements to shared_preload_libraries and restart the server,
then run CREATE EXTENSION pg_stat_statements;. Creating the extension alone
leaves the view permanently empty, which is the trap this code exists to name.
The extension exists but the library is not loaded, so the view is there and stays empty forever.
Add pg_stat_statements to shared_preload_libraries and restart. Until the
restart nothing is recorded, so a capture started now would measure nothing.
The extension is installed and this role cannot read it.
Grant pg_monitor to the capturing role, or capture with a role that already
has it. If the role does hold pg_monitor and you still see this, check where
the extension is installed: the tool reads the schema from pg_extension, so a
mismatch usually means the view was dropped and recreated by hand.
pg_stat_statements is installed and readable, and recording nothing:
pg_stat_statements.track is set to none.
The view will stay empty however long a capture runs, so this stops init
rather than producing a bundle with no queries in it. Set the parameter to
top to record statements, or to all to also record statements run inside
functions and procedures. It takes effect without a restart.
The view starts empty afterwards, so let traffic accumulate before capturing.
An hour is usually enough to tell whether statements are being recorded: check
with SELECT count(*) FROM pg_stat_statements.
The role cannot read the statistics views at all, so nothing can be collected.
Grant pg_monitor. This is a stronger failure than W213,
which is the case where the statistics are readable and only other roles'
statement text is hidden.
You are connected to a standby.
pg_stat_statements and the relation counters are per instance, so a replica
shows only the queries routed to it, and the write workload is what decides a
shard key. Capture the primary instead. If you mean to measure this replica's
traffic, pass --allow-replica to init; the same code is then reported as a
warning and the capture continues.
pg_stat_statements.track is set to top, so a statement executed inside a
function or procedure is attributed to the call rather than recorded on its own.
If your application's work happens inside stored procedures, the query log will
describe the calls rather than the queries. Set pg_stat_statements.track = all
to record both. It costs more shared memory and more entries, so raise
pg_stat_statements.max with it.
pg_stat_statements.max is at or below its default, and PostgreSQL evicts the
least-used statements when the table fills.
An evicted statement is one the capture never sees, so a busy database loses its
long tail, which is often the queries that shard worst. Raise
pg_stat_statements.max (4 to 5 times the default is a reasonable start) and
restart. Compare pg_stat_statements_info.dealloc before and after: if it keeps
climbing, the value is still too low.
track_io_timing is off, so block read and write times are reported as zero
rather than measured.
Every other counter is real. What you lose is the ability to tell a query that
is slow from disk from one that is slow from CPU. Set track_io_timing = on to
measure it. It needs no restart, and the overhead is small on modern hardware.
Always reported, because it is a property of the data source rather than a setting.
pg_stat_statements records statements, not transactions, so nothing in a
capture shows which tables your application writes together in one transaction.
That matters, because two tables written in the same transaction usually want to
be on the same shard. There is nothing to change. Bring that knowledge to the
review instead: list the transactions that matter most and the tables each one
touches.
A snapshot could not read pg_stat_user_tables or pg_stat_user_indexes.
The snapshot is kept, so the window can still be measured from the ticks around it. A single occurrence is usually transient, such as a failover or a statement timeout. If every tick reports it, treat it as E106 and check the role's privileges.
The statistics views returned no table at all.
Either the database is empty, or workload.schemas in the config names schemas
that do not exist. Check the spelling, and note that the setting is case
sensitive.
Table counters came back but every idx_scan was null, which means the role can
list the relations and not read their statistics.
Grant pg_monitor. This is the misleading case the code exists to name: the
table list looks correct while the counters behind it are blank.
A snapshot collected no statements, although init confirmed the extension was
readable.
Something changed mid-capture: the extension was dropped, or the role lost its
grant. Re-run init in a new session directory once the cause is fixed. The
snapshots already taken remain valid for the window they cover.
This server lacks some counters the tool collects, usually because it is an older major version.
Nothing to fix. The missing columns are recorded by name so a consumer cannot read "the server does not have this" as "the value is zero". The tool supports PostgreSQL 12 through 18, and the older the server, the more counters are absent.
Reading pg_stat_statements failed for this tick, and the error text follows
the code.
The snapshot is kept. If the message is relation "pg_stat_statements" does not exist while the extension is installed, the view is in a schema the tool could
not resolve; check
SELECT n.nspname FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace WHERE e.extname = 'pg_stat_statements'. If it is a permission error, see
E105.
The snapshot hit workload.statement_row_limit, so the tail of the workload is
missing from this tick.
Raise workload.statement_row_limit in the config. The default is 20,000
statements, and a database with more distinct query shapes than that is worth a
look on its own: an application that inlines literals instead of using
parameters produces a new shape per value.
PostgreSQL returned <insufficient privilege> instead of the statement text for
some entries. Those statements belong to another role.
Grant pg_read_all_stats, or capture with a role that has it. Masked statements
keep their counters and lose their text, so they cannot be planned.
A snapshot could not read pg_stats, so it recorded no column distributions.
The capture continues, and the query log, schema and row counts are unaffected.
What is lost is the evidence of whether a candidate shard key spreads evenly,
which the migration team would otherwise read from column_stats.json. A single
occurrence is usually transient. If every tick reports it, the role is missing
pg_monitor, so see E106.
Reported by init, before any statement is read: the role holds neither
pg_monitor nor pg_read_all_stats, so text belonging to other roles will be
hidden.
Grant pg_monitor before starting a long capture. Left unfixed, the workload is
biased toward the statements this role happens to run, and the bias is invisible
in the result. W212 is the same problem observed
after the fact, with a count.
The session holds a query log window whose own timestamps, earliest to latest, fall outside the snapshot window this bundle covers.
The window was left out of burst.csv rather than mixed into a window it does
not describe: a log read last Tuesday says nothing about a window measured this
week. A window read by collect cannot land here, because it is taken between
two snapshots. An exported file can, when it covers a different day.
Export a file that covers the time the snapshots span, or finalize a session whose snapshot window already covers when the log was written.
The database connection closed while a snapshot was being read. The detail names the read that was in progress.
The snapshot is kept with everything read before the connection closed. The reads after it were skipped. A skipped read that has its own code, such as W210, also reports that code.
Check the PostgreSQL server log for the time of the snapshot. Look for a line
that says why the server ended the session, such as a restart, a failover, or
terminating connection due to administrator command. A connection pooler or a
proxy between the tool and the server can also close the connection. If every
snapshot reports this code, check the pooler's query and transaction timeouts.
No measurement tool sees everything, and it is better to know the edges before you plan a migration around the result. None of the following blocks a sharding plan. Each one is a place where your knowledge of the application is worth more than the measurement, so bring what you know to the review.
Which tables you write together in one transaction. PostgreSQL records
statements, not transactions, so the capture sees an INSERT into orders and
an INSERT into order_items without knowing they happened together. That
matters, because two tables written in the same transaction usually want to live
on the same shard. If they end up apart, those writes become distributed
transactions, which are slower and can fail in ways a single-shard write cannot.
What to do: list the transactions that matter most to you — checkout, signup,
whatever your critical write path is — and name the tables each one touches.
Your migration engineer will use that list directly. Or set capture_log,
which reads the transaction boundaries straight from the server's log. See
Capturing transaction shapes and values.
The real values your queries search for. The capture knows a query filters
on tenant_id. It does not know that one tenant is 40% of your traffic. A shard
key that looks evenly distributed on paper can still put half your load on one
shard.
What to do: tell your migration engineer about any tenant, customer or region
that is much larger than the rest. If you can run a GROUP BY on the candidate
key yourself and share the top few counts, that answers the question
completely. Or set capture_log, which keeps the literal values a snapshot
never can.
Anything that did not run during the capture window. A weekly billing job that falls outside the window is invisible to the plan, and those jobs are often the ones that read across every tenant at once. See Choosing the capture period.
What to do: check your list of scheduled jobs against the capture window, and tell your migration engineer which ones ran and which did not.
Queries PostgreSQL forgot before you started. pg_stat_statements holds a
fixed number of statements and discards the least-used ones when it fills. Rare
queries can be dropped before the first snapshot is ever taken.
What to do: raise pg_stat_statements.max before the capture if you can. See
W202.
How much time your queries spend waiting on disk, when track_io_timing is
off. Those figures come back as zero, meaning "not measured" rather than "fast".
Everything else — call counts, row counts, execution time — is measured
regardless.
What to do: nothing, unless you want that detail. Turning track_io_timing on
needs no restart. See W203.