1000 connections in PostgreSQL on OS X

PostgreSQL is not really geared towards more than a few dozen simultaneous connections on desktop operating systems. In this post, I'll show you how to push PostgreSQL 9 on Max OS X 10.6 to handle 1000 connections on a single system running both the webserver and the database.

The first step to getting to 1000 connections is to make PostgreSQL actually try to do it. By default, it only allows 100 simultaneous connections. Change /Library/PostgreSQL/9.0/data/postgresql.conf from max_connections = 100 to max_connections = 1000.

PostgreSQL creates one process per connection, and the web server will need one TCP connection to each one. Thus, to have 1000 connections, we need to allow at least 1000 processes per user (for the PostgreSQL processes) and at least 1000 open file descriptors per process (for the app server). Each PostgreSQL process seems to use 32 fds, and with some margin in case it grows, we should permitt 35000 fds in the system, plus some for other uses (in case you want to do other things on your machine, like logging in to it). The kernel seems to be picky about power-of-two increments, so I'll round the values up to things that it will accept.

Change /etc/sysctl.conf from

kern.maxprocperuid=512
kern.maxproc=2048

to

kern.maxprocperuid=2048
kern.maxproc=4096
kern.maxfiles=40960 
kern.maxfilesperproc=2048

This will make sure the kernel reserves enough space etc. In addition, we need to allow processes to create enough sub-processes (as reported by ulimit -u). This limit is set by OS X's equivivalent of init, called launchd. Change /etc/launchd.conf (or create if you don't have it already) to say limit maxproc 2000 2000.

The changes to /etc/sysctl.conf and /etc/launchd.conf both take effect on bootup, so reboot the system and have fun with your 1000 connections!