This was performed on Ubuntu 14.04 with a fresh install of PostgreSQL 9.3.14 directly from the official Postgres apt repo. It only works if the query planner chooses the attached plan (with HashAggregate).
Create and populate a new database:
psql -U postgres -c "create database test;"
psql -U postgres test < populate.sql
Open a connection to the database and execute query.sql
. The Postgres worker memory usage will rise approximately 50MB and won't be released until the connection is closed.
The second link contains the answer I settled on. I was doing great many
array_agg()
operations, which used a lot of memory in Postgres 9.3. The implementation was improved in 9.5, so after upgrading to 9.6 the issue went away.