Gatsby source plugin pg Pool max connections during develop

Answer

To resolve intermittent "too many clients" errors when a Gatsby source plugin uses node‑postgres (pg.Pool) during gatsby develop, monitor pool usage, log pool events, create a singleton pool, size it appropriately, and verify the change by restarting the dev server and checking active connections.

Likely explanation

During gatsby develop the sourceNodes function runs repeatedly (on start, data refetches, and schema rebuilds). If the plugin creates a new pg.Pool on each run without calling pool.end(), each pool holds up to its max idle clients. Multiple pools multiply the total connections against PostgreSQL’s max_connections, eventually exhausting the limit and causing the "too many clients already" error.

Confirmed facts

• The default pg.Pool options are max: 10, idleTimeoutMillis: 0, connectionTimeoutMillis: 0 (subject to the installed pg version).
• PostgreSQL reports connection exhaustion via the error message "sorry, too many clients already".
• A pool client must be released with client.release() (or via pool.query callback) to return it to the idle pool.

Steps to monitor active connections

1. Add a temporary listener to the pool that logs its internal counters:

   pool.on('acquire', () => console.log('pg acquire', pool.totalCount, pool.idleCount, pool.waitingCount));
   pool.on('release', () => console.log('pg release', pool.totalCount, pool.idleCount, pool.waitingCount));
   pool.on('remove', () => console.log('pg remove', pool.totalCount, pool.idleCount, pool.waitingCount));
   

2. While running gatsby develop, watch the console for the counts rising and falling. A steady increase in totalCount without matching release events indicates a leak.
3. Optionally, query PostgreSQL directly in another terminal:

   SELECT count(*) FROM pg_stat_activity WHERE application_name ILIKE '%gatsby%';
   

   This shows the actual server‑side connections attributed to your Gatsby process.

Steps to capture and log pg Pool error events

1. Attach an error listener to the pool immediately after creation:

   pool.on('error', (err) => {
         console.error('pg pool error:', err);
         // optionally send to a logging service
       });
       

2. Ensure any pool.connect calls are wrapped in a try/finally that releases the client:

   pool.connect((err, client, done) => {
         if (err) return console.error('connect err', err);
         try {
           // run query
         } finally {
           done(); // releases client back to pool
         }
       });
       

3. With these listeners in place, exhaustion will appear as repeated "error" events containing the message "too many clients, already connected".

Steps to adjust pool size and verify changes

1. Make the pool a module‑level singleton in gatsby-node.js (or the plugin’s entry point) so it is created once per Node.js process:

   const { Pool } = require('pg');
   const pool = new Pool({
     connectionString: process.env.DATABASE_URL,
     max: 4, // adjust based on observed usage
   });
   module.exports = { pool };
       

2. Close the pool when Gatsby finishes bootstrapping to avoid leaving idle connections after the dev server stops:

   exports.onPostBootstrap = async () => {
         await pool.end();
       };
       // also listen for process termination
       process.on('SIGINT', () => pool.end());
       process.on('SIGTERM', () => pool.end());
       

3. Choose a sensible max value. For typical sourceNodes workloads (sequential per plugin) a range of 2‑5 is sufficient. If you run explicit parallel queries with Promise.all, increase proportionally but keep the total below PostgreSQL’s max_connections minus connections used by other services.
4. Restart gatsby develop completely (stop the process, then start again) to ensure the new pool configuration takes effect.
5. After restart, repeat the monitoring steps from the first section. Verify that totalCount stabilizes around the chosen max and that no "error" events appear.
6. Optionally, confirm with the server‑side query:

   SELECT count(*) FROM pg_stat_activity WHERE application_name ILIKE '%gatsby%';
       

   The result should stay consistently below the pool’s max multiplied by the number of plugin instances (usually one).

If you observe that connection counts still exceed the expected max after these steps, please confirm the exact number of concurrent sourceNodes executions (e.g., how many plugins or parallel queries you are running) so we can adjust the pool size recommendation.