How to increase the max connections in postgres?

by

Just increasing max_connections is bad idea. You need to increase shared_buffers and kernel.shmmax as well.

Considerations

max_connections determines the maximum number of concurrent connections to the database server. The default is typically 100 connections.

Before increasing your connection count you might need to scale up your deployment. But before that, you should consider whether you really need an increased connection limit.

Each PostgreSQL connection consumes RAM for managing the connection or the client using it. The more connections you have, the more RAM you will be using that could instead be used to run the database.

A well-written app typically doesn’t need a large number of connections. If you have an app that does need a large number of connections then consider using a tool such as pg_bouncer which can pool connections for you. As each connection consumes RAM, you should be looking to minimize their use.

How to increase max connections

1. Increase max_connection and shared_buffers

in /var/lib/pgsql/{version_number}/data/postgresql.conf

change

max_connections = 100
shared_buffers = 24MB

to

max_connections = 300
shared_buffers = 80MB

The shared_buffers configuration parameter determines how much memory is dedicated to PostgreSQL to use for caching data.

  • If you have a system with 1GB or more of RAM, a reasonable starting
    value for shared_buffers is 1/4 of the memory in your system.
  • it’s unlikely you’ll find using more than 40% of RAM to work better
    than a smaller amount (like 25%)
  • Be aware that if your system or PostgreSQL build is 32-bit, it might
    not be practical to set shared_buffers above 2 ~ 2.5GB.
  • Note that on Windows, large values for shared_buffers aren’t as
    effective, and you may find better results keeping it relatively low
    and using the OS cache more instead. On Windows the useful range is
    64MB to 512MB    .

2. Change kernel.shmmax

You would need to increase kernel max segment size to be slightly larger
than the shared_buffers.

In file /etc/sysctl.conf set the parameter as shown below. It will take effect when postgresql reboots (The following line makes the kernel max to 96Mb)

kernel.shmmax=100663296

References

Postgres Max Connections And Shared Buffers

Tuning Your PostgreSQL Server

More Related Contents:

  1. Why the “Group BY” by month didn’t work? [closed]
  2. Truncating all tables in a Postgres database
  3. How can I drop all the tables in a PostgreSQL database?
  4. How to change PostgreSQL user password?
  5. How to get the count of current month Sunday’s in psql?
  6. quick random row selection in Postgres
  7. Restore a postgres backup file using the command line?
  8. Time zone names with identical properties yield different result when applied to timestamp
  9. CONSTRAINT to check values from a remotely related table (via join etc.)
  10. Can I automatically create a table in PostgreSQL from a csv file with headers?
  11. User defined variables in PostgreSQL
  12. PostgreSQL query very slow with limit 1
  13. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  14. Postgres query optimization (forcing an index scan)
  15. psql: FATAL: database “” does not exist
  16. Difference between timestamps with/without time zone in PostgreSQL
  17. Omitting the double quote to do query on PostgreSQL
  18. Storing Images in PostgreSQL
  19. Psql could not connect to server: No such file or directory, 5432 error?
  20. Strange behaviour in Postgresql
  21. Postgres: Distinct but only for one column
  22. Cursor based records in PostgreSQL
  23. How to add column if not exists on PostgreSQL?
  24. Maximum characters in labels (table names, columns etc)
  25. Docker – Postgres and pgAdmin 4 : Connection refused
  26. Configuration parameter work_mem in PostgreSQL on Linux
  27. How to delete an enum type value in postgres?
  28. Why unsigned integer is not available in PostgreSQL?
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment