Extensions
- pg_achievements 1.0.0
- Game achievements for PostgreSQL users
Documentation
- ACHIEVEMENTS
- Achievements
- CHANGELOG
- Changelog
README
Contents
pg_achievements
Game achievements for PostgreSQL users. The extension watches what roles do in the database and hands out achievements in four tiers (bronze, silver, gold, platinum). The notice arrives right in the client, in the same session:
postgres=> UPDATE accounts SET balance = 0;
NOTICE: π₯ [GOLD] Achievement unlocked: Forgot WHERE (+50)
DETAIL: UPDATE without a WHERE clause. Total score: 135.
UPDATE 1042
Some achievements nudge you toward PostgreSQL features (EXPLAIN ANALYZE,
recursive CTEs, window functions, partitioning, CREATE INDEX CONCURRENTLY).
Others gently mock classic mistakes, like the UPDATE above or a Friday
evening deploy. All 46 builtin achievements are listed in
docs/ACHIEVEMENTS.md.
Requirements
- PostgreSQL 16, 17 or 18 on Linux; the main development version is 18;
- the server must preload the library (
shared_preload_libraries), so managed services such as RDS or Cloud SQL are not supported; - server headers and PGXS:
postgresql-server-dev-NNon Debian/Ubuntu, pluslibkrb5-dev(the server headers include the GSSAPI header when PostgreSQL is built with GSSAPI, as the PGDG packages are).
There are no other dependencies.
Installation
make # uses the first pg_config in PATH
sudo make install # or: make PG_CONFIG=/path/to/pg_config install
Add the library to postgresql.conf and restart the server:
shared_preload_libraries = 'pg_achievements' # keep other libraries in the list
#pg_achievements.database = 'postgres' # database that stores the tables
Then create the extension in that database:
CREATE EXTENSION pg_achievements;
The extension can only be created in pg_achievements.database, but events
are collected from every database of the cluster. If that database does not
exist yet, the background worker waits until it is created.
Usage
-- my achievements
SELECT tier, title, points, awarded_at FROM achievements.my;
-- everything with my progress; secret descriptions stay hidden until unlocked
SELECT code, title, current, threshold, unlocked FROM achievements.progress;
-- leaderboard of all roles that did not opt out
SELECT * FROM achievements.leaderboard;
-- the catalog without hidden fields
SELECT * FROM achievements.catalog_public;
-- one counter of the current role, including events not flushed yet
SELECT achievements.counter('stmt_select');
-- wait until the worker has written everything to the tables
SELECT achievements.sync();
Opt out of tracking for a role, or only for a session:
ALTER ROLE robot SET pg_achievements.opt_out = on;
SET pg_achievements.opt_out = on;
Quiet mode, or medals without emoji:
SET pg_achievements.notify = off; -- still awarded, just no notice
SET pg_achievements.notice_style = ascii; -- [GOLD] instead of π₯ [GOLD]
Functions
| Function | Who | What it does |
|---|---|---|
achievements.counter(key) |
any role | Current value of a counter of the current role |
achievements.sync(timeout_ms DEFAULT 5000) |
any role | Flushes local events and waits for the worker to write them; returns false on timeout |
achievements.version() |
any role | Extension, library and catalog versions |
achievements.reset(role DEFAULT current_user) |
the role itself or a superuser | Removes counters and achievements of the role |
achievements.forget_role(role) |
superuser | Removes the data of a role that no longer exists (DROP ROLE keeps it) |
achievements.define(code, title, description, tier, counter_key, threshold) |
superuser | Adds a custom achievement on an existing counter; points follow the tier (10/25/50/100) |
achievements.grant(role, code) |
superuser | Awards an achievement by hand |
A custom achievement becomes active within flush_interval_ms after the
transaction that defined it commits. Counter keys are listed by
SELECT * FROM achievements._counter_keys().
Example:
SELECT achievements.define('vacuum_fan', 'Vacuum Fan', 'Run VACUUM FULL 10 times.',
'gold', 'vacuum_full', 10);
Settings
| Parameter | Context | Default | Meaning |
|---|---|---|---|
pg_achievements.enabled |
reload | on |
Turns all tracking on or off |
pg_achievements.database |
restart | postgres |
Database that stores the tables |
pg_achievements.opt_out |
user | off |
The role or session is not tracked at all |
pg_achievements.notify |
user | on |
Show notices in the session |
pg_achievements.notice_style |
user | emoji |
emoji (needs UTF8 on both sides) or ascii |
pg_achievements.track_nested |
superuser | off |
Also count statements run inside functions |
pg_achievements.flush_interval_ms |
reload | 1000 |
How often counters go to the tables (min 100) |
pg_achievements.big_table_rows |
reload | 1000000 |
Row estimate (reltuples) of a βbigβ table for seqscan_big |
pg_achievements.max_roles |
restart | 1024 |
Roles kept in shared memory |
How it works
client backends shared memory worker
βββββββββββββββ βββββββββββββ ββββββ
hooks bump local deltas ββcommitβββΆ per-role counters (atomics) βββΆ achievements.counters
threshold reached? award bits + award queue βββΆ achievements.awarded
ββ claim the bit, NOTICE now catalog snapshot βββ achievements.catalog
- Hooks (
post_parse_analyze,ProcessUtility,ExecutorRun/Finish/End,emit_log_hook, transaction callbacks andClientAuthentication_hook) only increment counters in backend memory. No I/O, no SPI, no exclusive locks. - At commit (and abort, backend exit, or at most every
flush_interval_msinside long transactions) the deltas are added to per-role atomics in shared memory. - The backend compares the counters with the thresholds itself. When a threshold is reached it claims the award bit atomically, so 50 sessions of one role racing for the same achievement still produce exactly one award, and sends the notice right away.
- A background worker writes counters and awards to the tables once per
flush_interval_ms, in its own transactions. Tracking therefore does not depend on the userβs transaction: rolling back does not take an achievement back. After a crash at mostflush_interval_msof progress is lost. - The worker is started by a small launcher, which restarts it 10 seconds after a failure. On a hot standby the worker never writes.
Counters
Every achievement checks one counter. sum counters add up events, max
counters keep the largest value seen.
| Key | Type | Counts |
|---|---|---|
connect |
sum | successful connections |
stmt_select |
sum | top-level SELECT statements that succeeded |
select_star |
sum | SELECT * / SELECT t.* |
err_syntax |
sum | syntax errors (42601) |
explain_any / explain_analyze |
sum | EXPLAIN / EXPLAIN ANALYZE |
cte_any / cte_recursive |
sum | queries with WITH / WITH RECURSIVE |
window_func |
sum | queries with window functions |
lateral |
sum | queries with LATERAL |
jsonb_ops |
sum | uses of the JSONB operators ->, ->>, @>, ? |
join_count_max |
max | most explicit JOINs in one query |
null_eq |
sum | = NULL instead of IS NULL |
update_no_where / delete_no_where |
sum | UPDATE / DELETE without WHERE |
seqscan_big |
sum | queries with a sequential scan of a big table |
query_over_60s / query_over_1h |
sum | queries longer than a minute / an hour |
deadlock / serialization_failure |
sum | errors 40P01 / 40001 |
rollback_long_xact |
sum | rollbacks of transactions older than an hour |
ddl_friday_evening |
sum | DDL on a Friday after 18:00 |
night_query |
sum | statements between 03:00 and 05:00 |
create_table, create_index, create_index_concurrently, create_function, create_trigger, create_extension, partition_create |
sum | the corresponding CREATE commands |
drop_table, drop_database, truncate, vacuum_full |
sum | the corresponding commands; DROP TABLE counts every table dropped |
terminate_backend |
sum | calls of pg_terminate_backend in queries that succeeded |
Counting rules:
- Only top-level statements count unless
track_nestedis on. update_no_where,delete_no_where,null_eqandselect_starcount at parse analysis, because the attempt is what matters. Everything else counts only when the statement finishes successfully.- Statements that change nothing (
DROP TABLE IF EXISTSon a missing table,CREATE TABLE IF NOT EXISTSorCREATE EXTENSION IF NOT EXISTSon an existing object) do not count. - The role is
current_user(GetUserId()), so afterSET ROLEevents go to the new role. - Times of day use the session
timezone. - Background processes (autovacuum, replication, the worker itself) are never tracked.
Privacy and security
- Query texts, parameters and object names are never stored or logged. The
extension keeps only counters and award facts, and its notices and warnings
are sent with
errhidestmt. - The tables are not readable by
PUBLIC. Ordinary roles use the views, which only show their own progress (the leaderboard shows scores of all roles that did not opt out). - Administrative functions are revoked from
PUBLIC. Functions that act on the caller (counter,sync,reset) run as the caller and check permissions in C, becauseSECURITY DEFINERwould hide the callerβs identity.version()isSECURITY DEFINERwith a fixedsearch_path. - An internal error in a hook prints one
WARNING, disables tracking for that session and never breaks the userβs query.
Known limitations
- Errors are seen through
emit_log_hook, which runs only for messages that go to the server log: keeplog_min_messagesaterroror lower (the defaultwarningis fine). - Achievements earned by an error (deadlock, syntax error) are reported while
the transaction aborts, so psql may print the notice before or after the
ERRORline. - A prepared statement is analyzed once, so its parse-time features
(
SELECT *, CTEs, joins, β¦) count at its first execution only. The same goes for statements cached by PL/pgSQL withtrack_nested. - Client-side meta-commands such as
\dare invisible; the server only sees the SQL they send. pg_dumpsaves progress and custom achievements. After restoring into a running server, restart it so the worker reloads the tables.
Testing
The test build adds GUCs that the tests rely on: pg_achievements.test_now
(fake clock), pg_achievements.test_time_shift (added to measured durations)
and pg_achievements.test_inject_error (simulates an internal failure).
They do not exist in a normal build:
make PGA_TESTING=1 && make PGA_TESTING=1 install
make PGA_TESTING=1 installcheck # pg_regress + TAP (needs --enable-tap-tests)
installcheck starts its own temporary server, so no running instance is
needed. The TAP tests cover restarts and crashes, kill -9 of the worker,
50 sessions racing for one award, pg_dump/restore, pg_stat_statements in
both preload orders, hot standby and real deadlocks.
Performance
bench/run.sh [pg_config] measures the throughput loss with pgbench,
comparing pg_achievements.enabled = off and on (median of several runs).
PostgreSQL 18.7 built with -O2, scale 10, 5 Γ 15 s per case, 12 vCPU under
WSL2:
| Script | Clients | Overhead |
|---|---|---|
select-only (-S) |
1 | 1.4% |
select-only (-S) |
16 | 1.3% |
| TPC-B-like | 1 | within noise (β5.1%) |
| TPC-B-like | 16 | 2.4%, within noise |
The TPC-B-like runs are I/O bound and vary by about Β±5% between runs on this machine (the extension βwonβ the single-client case), so they cannot confirm the 2% target either way; the CPU-bound select-only case does. Rerun the script on your hardware for numbers that matter to you.
License
PostgreSQL License, see LICENSE.