Prisma•4w ago

How to kill idle connections properly in postgres

Prisma has been creating too many connections on my app. I have this:

export const prismaOptions: Prisma.Subset<
  Prisma.PrismaClientOptions,
  Prisma.PrismaClientOptions
> = {
  errorFormat: 'pretty',
  // log: ['info', 'query'],
  datasources: {
    db: {
      url: `${process.env.DATABASE_URL}?connection_limit=500&pool_timeout=0`,
    },
  },
}

export const prismaOptions: Prisma.Subset<
  Prisma.PrismaClientOptions,
  Prisma.PrismaClientOptions
> = {
  errorFormat: 'pretty',
  // log: ['info', 'query'],
  datasources: {
    db: {
      url: `${process.env.DATABASE_URL}?connection_limit=500&pool_timeout=0`,
    },
  },
}

2 Replies

jonfanz•4w ago

If I may ask, how many is too many? What numbers are you seeing?

cesar sampaio•4w ago

I have pods with 500 connections limit and reaching the limit. max connection in db level is 5000 but I see some pods with very old and idle connection reaching 500 in rds i see 1600 connections

import { Prisma } from '@prisma/client'
import * as Sentry from '@sentry/node'
import { ApolloError } from 'apollo-server'
import { ErrorCode } from '../models'

export const prismaOptions: Prisma.Subset<
  Prisma.PrismaClientOptions,
  Prisma.PrismaClientOptions
> = {
  errorFormat: 'pretty',
  // log: ['info', 'query'],
  datasources: {
    db: {
      url: `${process.env.DATABASE_URL}?connection_limit=500&pool_timeout=0`,
    },
  },
}

export const prismaOptionsReadOnly: Prisma.Subset<
  Prisma.PrismaClientOptions,
  Prisma.PrismaClientOptions
> = {
  ...prismaOptions,
  // log: ['info', 'query'],
  datasources: {
    db: {
      url: process.env.RO_DATABASE_URL
        ? `${process.env.RO_DATABASE_URL}?connection_limit=500&pool_timeout=0&connect_timeout=30`
        : `${process.env.DATABASE_URL}?connection_limit=500&pool_timeout=0`,
    },
  },
}

import { Prisma } from '@prisma/client'
import * as Sentry from '@sentry/node'
import { ApolloError } from 'apollo-server'
import { ErrorCode } from '../models'

export const prismaOptions: Prisma.Subset<
  Prisma.PrismaClientOptions,
  Prisma.PrismaClientOptions
> = {
  errorFormat: 'pretty',
  // log: ['info', 'query'],
  datasources: {
    db: {
      url: `${process.env.DATABASE_URL}?connection_limit=500&pool_timeout=0`,
    },
  },
}

export const prismaOptionsReadOnly: Prisma.Subset<
  Prisma.PrismaClientOptions,
  Prisma.PrismaClientOptions
> = {
  ...prismaOptions,
  // log: ['info', 'query'],
  datasources: {
    db: {
      url: process.env.RO_DATABASE_URL
        ? `${process.env.RO_DATABASE_URL}?connection_limit=500&pool_timeout=0&connect_timeout=30`
        : `${process.env.DATABASE_URL}?connection_limit=500&pool_timeout=0`,
    },
  },
}

thats how I instatiate them

Gaming

Programming

How to kill idle connections properly in postgres