Precomputing “People watching this port, also watch”

This is not yet deployed and is still in test. When a date appears here, it has been deployed to production.

Every port page on FreshPorts has a line reading People watching this port, also watch: followed by up to five links. Until now, that list was calculated every time the page was built. For a popular port such as devel/gmake, that meant counting about 186,000 watch list entries to keep five, taking roughly 150 ms of database time on each uncached view. The same calculation also runs once for every port shown on search, category and index pages.

The list now comes from a table that is rebuilt every night. Looking it up takes about 0.2 ms. On the development server, uncached page times dropped from 373 ms to 238 ms for devel/gmake, and from 228 ms to 125 ms for converters/libiconv.

This post describes how the change will be deployed.

What changes for users

Deleted ports no longer use up places in the list. Previously, the top five were chosen first and deleted ports were dropped afterwards, so many pages showed fewer than five links and some showed none. sysutils/php5-posix, with 76 watchers, showed nothing; it now shows five.
The list can be up to a day old. It changes only when people edit their watch lists, so this is not noticeable in practice.
Port descriptions in the links are read when the page is built, so changes to them show up immediately.

Phase 1: ship the code, disabled

This goes out with the next website update. Nothing runs, because the new periodic script must be enabled by hand.

periodics 1.2.0 adds daily/230.fp_watchers_also_watched. It does nothing until fp_watchers_also_watched_enable="YES" is set.
scripts adds watchers_also_watched_rebuild.pl, which the periodic script runs.
The website itself needs no change. It still calls WatchersAlsoWatched(), and the function keeps its name, arguments and output.

Phase 2: the database, one environment at a time

Order: dev (already done), test, stage, then production. For each environment:

1. Apply the schema update

From the database-schema repository:

psql -d freshports.stage -f updates-2026-10-17-watchers-also-watched.ddl

This creates three tables (watchers_also_watched, watchers_also_watched_excluded_lists, watchers_also_watched_queue) and the rebuild functions, grants access to www, reading and commits, and points WatchersAlsoWatched() at the new table. It runs as a single transaction.

The table starts empty, so uncached port pages show no “also watch” line until step 2 finishes. Do step 2 straight away.

2. Run the first rebuild by hand

On that environment’s ingress host, as the freshports user:

cd /usr/local/libexec/freshports
perl watchers_also_watched_rebuild.pl

The script prints nothing. It logs one line to /var/log/freshports/freshports.log:

watchers_also_watched rebuilt: 24297 of 24297 elements in 42.9 s, 0 lists excluded, 0 left in queue

The two element counts should be equal, and nothing should be left in the queue. If something is left, another rebuild was running at the same time; run it again. Expect about a minute.

On stage, expect 1 lists excluded. Stage has one list of 32,664 ports, almost every port there is. A list like that says nothing about what people watch together, so lists over 5,000 ports are left out. Without that limit, the stage rebuild would take about 13 minutes instead of one. Production has no list over 2,207 ports.

3. Check a port

SELECT P.element_id, W.*
  FROM ports_active P
 CROSS JOIN LATERAL WatchersAlsoWatched(P.element_id) W
 WHERE P.category = 'devel' AND P.name = 'gmake';

Expect five rows: libiconv, gettext, expat, m4 and png on dev. The ports may differ slightly on other environments.

4. Enable the nightly rebuild

On the ingress host, add this to /etc/periodic.conf.local:

fp_watchers_also_watched_enable="YES"

or, as root:

sysrc -f /etc/periodic.conf.local fp_watchers_also_watched_enable=YES

To test it the way periodic runs it, as root:

sh /usr/local/etc/periodic/daily/230.fp_watchers_also_watched

5. Check the next morning

After the daily periodic run, /var/log/freshports/freshports.log should have a new watchers_also_watched rebuilt: line.

Cached pages

Port pages that are already cached keep their old “also watch” line until their cache entry is cleared, which happens whenever the port is next updated. The new lists therefore appear gradually rather than all at once. Nothing needs to be done about this.

Rolling back

To go back to calculating the list on every view, point the function at the original query, which is kept as WatchersAlsoWatched_live():

CREATE OR REPLACE FUNCTION WatchersAlsoWatched(int) RETURNS SETOF also_watched_record AS $$
SELECT * FROM WatchersAlsoWatched_live($1);
$$ LANGUAGE SQL STABLE;

Then remove fp_watchers_also_watched_enable from /etc/periodic.conf.local (as root: sysrc -f /etc/periodic.conf.local -x fp_watchers_also_watched_enable). The tables can stay; nothing reads them.