What are you doing?

When connecting to AWS RDS Postgres greater than v9.6.6 Sequelize pooling causes freeable memory leak because connection stay open forever.

const db = new Sequelize({
  username,
  password,
  host,
  port,
  native: false,
  pool: {
    min: 0,
    max: 40,
  }
})

const Test = db.define('Test', {
  id: {
    field: 'id',
    type: DataTypes.INTEGER,
    autoIncrement: true,
    allowNull: false,
    unique: true,
    primaryKey: true,
    comment: 'The primary identifier for this record',
  },
  value: {
    field: 'value',
    type: DataTypes.STRING,
    allowNull: false,
    comment: 'Random value',
  },
}, {
  tableName: 'test',
  underscored: true,
})

async function createTest (attempt = 0, maxAttempt = 100000) {
  if (attempt > maxAttempt) {
    return
  }
  await Test.create({ value: Math.random().toString() })
  setTimeout(() => createTest(attempt + 1), 1000)
}

To Reproduce
Steps to reproduce the behavior:

1. Define models
2. Run createTest()
3. After 10 min to an hour if you watch Postgres Freeable Memory it will have a steep downward slope in AWS RDS on most database classes with Postgres > v9.6.6. Eventually all freeable memory is used up, swap then is exhausted and the server restarts.

What do you expect to happen?

After a max age a connection would be removed from the pool and replaced with a new one regardless of whether or not it was healthy.

What is actually happening?

The connection pool uses the same connections forever if there is a high request rate.

Environment

Dialect:

• mysql
• postgres
• sqlite
• mssql
• any
  Dialect library version: v7.9.0
  Database version: > v9.6.6
  Sequelize version: v4.43.1
  Node Version: v8.11.3
  OS: Ubuntu 16.04
  Tested with latest release:
• No
• Yes, specify that version: v4.43.1

To Fix
The pool.validate code from issue (#7068 (comment)) fixes the issue so it would be helpful to port the fix in to the library. If direction on where and how this should be implemented in Sequelize is provided I can create a PR. A configuration for the pool such as maxConnectionAge would be helpful as well as setting it to a default of 2 hours for Postgres seems appropriate.

const MAX_CONNECTION_AGE = 10 * 60 * 1000

const db = new Sequelize({
  username,
  password,
  host,
  port,
  pool: {
    min: 0,
    max: 40,
    /**
     * Manually evict connections after MAX_CONNECTION_AGE has passed
     * @param {Object} obj The connection object
     */
    validate (obj = {}) {
      // The connection has not be checked yet so set the current time
      if (!obj.connection.createdAt) {
        obj.connection.createdAt = Date.now()
        return true
      }
      return Math.floor(Date.now() - createdAt) < MAX_CONNECTION_AGE
    },
  },
})

Theory

Postgres keeps all tuples in memory for the life of a connection is the reason this issue occurs. You can find more information about at https://www.postgresql.org/docs/9.6/runtime-config-client.html if you search for idle_in_transaction_session_timeout. Sequelize is too aggressive at keeping high throughput connections alive in a pool is the guess at why it occurs i.e. the connection never enters the idle state.