Database

Roles, connections, managed PostgreSQL providers, and the one table you must back up.

Roles

Separate roles keep each process to its job. Create them as an administrator before migrate:

CREATE ROLE register_migrate  LOGIN PASSWORD '…';   -- owns the schema, runs migrate
CREATE ROLE register_sync     LOGIN PASSWORD '…';   -- sync
CREATE ROLE register_operator LOGIN PASSWORD '…';   -- suppress (objections)
CREATE ROLE register_api      LOGIN PASSWORD '…';   -- serve

ALTER DATABASE register OWNER TO register_migrate;
GRANT CONNECT ON DATABASE register TO register_sync, register_operator, register_api;
GRANT TEMPORARY ON DATABASE register TO register_sync, register_operator;
ALTER ROLE register_api SET default_transaction_read_only = on;
ALTER ROLE register_api SET statement_timeout = '10s';

Then run migrate with the role names. It grants each role exactly the table rights it needs, after every migration:

REGISTER_DB_ROLE_API=register_api REGISTER_DB_ROLE_SYNC=register_sync \
REGISTER_DB_ROLE_OPERATOR=register_operator accquix-register migrate

The docker compose deployment does all of this for you, so no register process connects as the superuser. PostgreSQL 16, 17 and 18 are tested.

Then point each mode at its role:

Never give serve a role that can write. Use sslmode=verify-full for managed databases.

The API connection pool

serve opens at most 10 connections, unless the connection string sets pool_max_conns. When the database is not next to the API, this pool, not the database, can set the limit: one search runs several queries in a row, so at 20 ms per round trip each connection carries about 7 searches a second. Set the size in the connection string, within the database’s connection limit:

REGISTER_DATABASE_URL='postgresql://…@host:6432/register?sslmode=verify-full&pool_max_conns=12'

The instance runs at most as many data requests at once as it has connections. When it is full, a new request waits a quarter of a second and then gets 503 with reason: overloaded and Retry-After, so the admitted requests keep their speed instead of everyone timing out together.

Through a transaction pooler such as PgBouncer, the client connections do not each hold a server connection: in the test on PlanetScale Postgres, 21 client connections used at most 16 on the server.

Managed providers

Size and growth

About 2.3 GB of data with indexes, growing with the registers (a few percent a year). The word dictionary used by fuzzy search adds about 10 MB.

Back up the objections

The register data needs no backup: it can always be rebuilt from the official sources. Section versions are content hashes, so after a rebuild your consumers see the same versions and no false changes.

The objections are different: they are your own data, and no source can restore them. If they are lost, every person who objected silently returns to name search. Dropping the database, docker compose down -v and migrate down-to 0 all delete them.

accquix-register suppress --export --output objections.tsv --overwrite   # after every change, and on a schedule
accquix-register suppress --restore objections.tsv                       # on a rebuilt database

• The export is written atomically, needs --overwrite to replace a file, and never replaces one with an empty export. Do not redirect suppress --list into a file: the shell empties the file before the command runs.
• Keep the file encrypted and away from the database: it lists the people who objected. Its last line holds a row count and a checksum, so a restore refuses a damaged file. That is integrity, not authenticity: whoever can change the file can recompute the checksum.
• After a rebuild the API answers 503 with reason: setup until you run suppress --restore, or suppress --init-empty on a new installation with no objections. An empty database cannot tell the two apart, so the register does not guess.
• If you lost both the database and every copy of the backup, the register cannot recover the objections.