Earlier this week, the production database ran at 100% CPU. Turning off searches for visitors who are not logged in brought it down to 22%, and then to 10%. This post covers what was found, and the changes being deployed to fix it.
What was found
- A botnet was getting past the search gate. Requests came from a different residential IP address each time, with the same user agent, and each one sent a
Refererpointing at a FreshPorts category page. The gate trusted that Referer. Most requests were “depends on this port” searches, the most expensive kind. - Searches walked every commit on the branch. The search queries joined
system_branchby name. The planner could not tell which branch that was, so it guessed about 10,000 head rows incommit_log_branches; there are 568,000. It then checked every one of them: 10 seconds for a search that matches a single port. - Port pages scanned the whole dependency table. Each port page looks up its dependents 8 times, once per dependency type. There was no index on
port_dependencies.port_id_dependent_upon, so each lookup scanned all 550,000 rows. Over six months, this was the largest single user of database time. - Category pages computed something nobody saw. For every port on a category page,
pkg_plist()ran a regular expression over every file the port installs: about 1 second per page of 100 ports. Since a 2022 change, the result was thrown away, so the sampleLIB_DEPENDSlines also stopped appearing on port pages.
Results on the development database
| What | Before | After |
|---|---|---|
Search, depends on x11-themes/gnome-icons-slick (1 port) | 10.4 s | 5 ms |
Dependents of devel/gmake, index lookup | 56 ms | 1.4 ms |
pkg_plist() for 1,000 ports | 2,873 ms | 25.5 ms |
The port_dependencies index (step 1) is already on production.
Deployment
Do the database changes first. The new code works without them, only more slowly. Each CREATE INDEX CONCURRENTLY runs without locking the table, and must not be run inside a transaction.
Before you start: pg_stat_statements
The pg_stat_statements extension records how much time each query takes. It is how these problems were found, and step 7 uses it to confirm the fix. Check whether the library is loaded:
SHOW shared_preload_libraries;If pg_stat_statements is not listed, add it and restart PostgreSQL. On RDS it is in the default parameter group, so this is usually already done; if not, add it to the instance’s parameter group and reboot. On a self-managed server, set it in postgresql.conf (if the setting already lists other libraries, keep them and add this one to the list):
shared_preload_libraries = 'pg_stat_statements'service postgresql restartThen create the extension, as a superuser (on RDS, the master user):
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;On production this is already done.
Before you start: shared_buffers
shared_buffers is PostgreSQL’s own cache. Check it:
SHOW shared_buffers;The built-in default is 128MB, which is far too small for FreshPorts. On a development database it made nearly every lookup go to disk: the largest search took 4.3 seconds, and 0.34 seconds after raising shared_buffers to 2GB, with no other change. A good starting point is about 25% of the server’s RAM. Production, on RDS, uses the RDS default of about 25%, which is about 1.9GB there.
On a self-managed server, set it in postgresql.conf. For example, on a server with 8GB of RAM:
shared_buffers = 2GBLike shared_preload_libraries, this takes effect only after a restart, so make both changes and restart once. The cache starts empty after a restart, so the first page loads will be slow.
1. Index for port dependency lookups
Each port page looks up the ports that depend on it, once per dependency type: 8 queries per page. Without this index, each one scans the whole port_dependencies table. Production already has it; IF NOT EXISTS makes this safe to run there anyway. The VACUUM lets the lookup be answered from the index alone.
CREATE INDEX CONCURRENTLY IF NOT EXISTS port_dependencies_upon_type_idx
ON port_dependencies (port_id_dependent_upon, dependency_type, port_id);
VACUUM (ANALYZE) port_dependencies;2. Trigram indexes for searches
These need the pg_trgm extension; the first line installs it if it is not already there. The element index serves searches by port name; a name search that took 19 seconds took under 1 ms with it. element holds every file in the tree, so it takes longer to build than the others. The three depends_* indexes serve the “depends on this port” searches; each took 2 to 3 seconds to build on the development database. The short_description index serves searches by short description, which otherwise read the whole ports table: 2.9 seconds for “editor” on dev.freshports.org.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY IF NOT EXISTS element_name_trgm_idx ON element USING gin (name gin_trgm_ops);
ANALYZE element;
CREATE INDEX CONCURRENTLY ports_depends_build_trgm_idx ON ports USING gin (depends_build gin_trgm_ops);
CREATE INDEX CONCURRENTLY ports_depends_lib_trgm_idx ON ports USING gin (depends_lib gin_trgm_ops);
CREATE INDEX CONCURRENTLY ports_depends_run_trgm_idx ON ports USING gin (depends_run gin_trgm_ops);
CREATE INDEX CONCURRENTLY ports_short_description_trgm_idx ON ports USING gin (short_description gin_trgm_ops);
ANALYZE ports;3. Partial index and new pkg_plist()
Create the index first; it took 34 seconds on the development database. The WHERE clause in the function must match the index condition exactly, or the index is not used.
CREATE INDEX CONCURRENTLY generate_plist_port_id_libs_idx
ON generate_plist (port_id) INCLUDE (installed_file)
WHERE installed_file ~ 'lib/[^/]*?\.so';
CREATE OR REPLACE FUNCTION public.pkg_plist(bigint)
RETURNS json
LANGUAGE sql
STABLE
AS $function$
WITH tmp AS (
select regexp_match(installed_file, 'lib/[^/]*?\.so') as lib
from generate_plist
where generate_plist.port_id = $1
and installed_file ~ 'lib/[^/]*?\.so'
)
select json_agg(distinct lib[1]) from tmp where lib is not null
$function$;3a. Statistics for new quarterly branches
Status: applied on the dev and test databases on 2026-10-16, and being evaluated there. Not yet on production.
When the 2026Q4 branch started, the planner on test still believed it had 1 commit; it had 351. Version lookups on category pages then walked every Q4 commit for each port shown, about 8 times the work: 93 ms instead of 12 ms per 100 ports. Refreshing the statistics fixed it immediately:
ANALYZE commit_log_branches;Autovacuum did not do this on its own. By default it re-analyzes a table after about 10% of its rows change; commit_log_branches has about 600,000 rows, and a new quarterly branch adds a few hundred a week. The same happens to commit_log_ports and commit_log, and it will happen at the start of every quarter. These settings make autovacuum re-analyze them after a fixed number of changes instead:
ALTER TABLE commit_log_branches SET (autovacuum_analyze_scale_factor = 0, autovacuum_analyze_threshold = 500);
ALTER TABLE commit_log_ports SET (autovacuum_analyze_scale_factor = 0, autovacuum_analyze_threshold = 1000);
ALTER TABLE commit_log SET (autovacuum_analyze_scale_factor = 0, autovacuum_analyze_threshold = 500);They take effect without a restart. Before applying them to production, confirm on dev and test that autovacuum now analyzes these tables by itself: last_autoanalyze should have a date and keep moving forward.
SELECT relname, last_autoanalyze, autoanalyze_count
FROM pg_stat_user_tables
WHERE relname IN ('commit_log_branches', 'commit_log_ports', 'commit_log');The one-off ANALYZE commit_log_branches; is safe on production at any time; it only refreshes the statistics.
4. Refresh the statistics
The planner chooses query plans from statistics about each table. On the test database they were out of date well beyond commit_log_branches; refreshing them all brought port pages from about 1 second to under 0.2. ANALYZE reads a sample of each table rather than all of it, takes seconds to a minute, does not block anything, and is safe to run on production at any time:
ANALYZE;5. Turn off JIT for the website
JIT compilation added about 50 ms to a 290 ms search query and gives nothing back on queries this size. This applies to new connections only.
ALTER ROLE <website role> SET jit = off;6. Deploy the code
www/search.phpandclasses/system_branch.php: look up the branch id first, and pass it to both search queries as a literal.classes/ports.php: restore the sampleLIB_DEPENDSlines on port pages, and stop callingpkg_plist()for category pages.
Port pages are cached, so the LIB_DEPENDS lines will appear as each page’s cache is refreshed.
7. Start measuring
SELECT pg_stat_statements_reset();After a day, check the most expensive queries:
SELECT round(total_exec_time) AS ms, calls, round(mean_exec_time) AS mean_ms, left(query, 120)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 15;Verification
- A logged-in search for ports that depend on
devel/gmakereturns the same number of ports as before, in under a second. - A search by short description for “editor” returns the same number of ports as before, and takes well under the 2.9 seconds it took without the index.
- The
graphics/pngport page, once its cache refreshes, listslibpng16.so:graphics/pngunder “For LIB depends”. - A category page, such as
x11-themeswith 100 ports per page, loads normally.
Rollback
Revert the code commits first. The indexes are harmless to leave in place; drop them only if they cause trouble. To restore the old function, recreate it without the and installed_file ~ … line.
ALTER ROLE <website role> RESET jit;
ALTER TABLE commit_log_branches RESET (autovacuum_analyze_scale_factor, autovacuum_analyze_threshold);
ALTER TABLE commit_log_ports RESET (autovacuum_analyze_scale_factor, autovacuum_analyze_threshold);
ALTER TABLE commit_log RESET (autovacuum_analyze_scale_factor, autovacuum_analyze_threshold);
DROP INDEX CONCURRENTLY element_name_trgm_idx;
DROP INDEX CONCURRENTLY ports_depends_build_trgm_idx;
DROP INDEX CONCURRENTLY ports_depends_lib_trgm_idx;
DROP INDEX CONCURRENTLY ports_depends_run_trgm_idx;
DROP INDEX CONCURRENTLY ports_short_description_trgm_idx;
DROP INDEX CONCURRENTLY generate_plist_port_id_libs_idx;
DROP INDEX CONCURRENTLY port_dependencies_upon_type_idx;Not in this deployment
Search stays limited to logged-in users for now. Reopening it needs two more changes: the search gate stops trusting the Referer (include/search-gate.php, www/search-gate.php), and nginx sends searches without a login or gate cookie to the challenge page before PHP or the database is involved. Both will be covered in a separate post.
