P
Prisma•4mo ago
cesar sampaio

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
jonfanz•4mo ago
If I may ask, how many is too many? What numbers are you seeing?
cesar sampaio
cesar sampaioOP•4mo 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

Did you find this page helpful?