Key idea
Your app finds its database through one connection string, and because it contains the password, the whole string is a secret. Each open connection costs the database memory, so every database has a connection limit, and all your servers share it.
Reading one
Anatomy of a connection string
| id | name | city |
|---|---|---|
| 1 | Ada | Leeds |
| 2 | Lin | Lagos |
{ "id": 42,
"name": "Ada",
"tags": ["pro"] }Nested JSON; fields can varyYour database provider gives you this string. Apps usually read it from an environment variable called DATABASE_URL, and most database libraries accept it as it is.
If the password contains characters such as @, : or /, they must be percent-encoded in the string: p@ss becomes p%40ss. Left as they are, many parsers split the string in the wrong place and read part of the password as the host.
Keep it out of your code
Anyone with the string can read, change or delete your data. So:
- never commit it to git, even in a private repository; history keeps it after you delete the line;
- store it as a secret in the place that runs your app, and read it from the environment;
- give each environment its own database and string, so staging can never touch production data.
If a string does leak, change the database user's password straight away. Deleting the commit doesn't take it back.
Connection limits
Opening a connection takes a network round trip, the TLS handshake and a login, so apps don't open one per request. They keep a connection pool: a few open connections, borrowed for each query and handed back.
Pools multiply. Each copy of your app keeps its own pool, so 4 servers with a pool of 10 each hold 40 connections before anyone else connects. PostgreSQL allows 100 by default, and small hosted plans often allow fewer.
Go over the limit and new connections are refused. PostgreSQL says sorry, too many clients already, and requests fail even though the database itself is fine. This often happens the moment you add servers to handle more traffic.
Size pools from the limit: divide what the database allows, minus a few spare, by the number of app processes that can run at once.
When there are too many app processes
If you run many app processes, a pooler such as PgBouncer sits between the apps and the database: hundreds of app connections share a few real ones. Some hosted providers include one; its address goes in the connection string instead of the database's.
Check yourself