A connection and a session are two different things. A connection is essentially a relationship between a user, an app, and some API...that connection's primary data point is the OAuth access token that was granted during connection time.
In OAuth 1.0/1.0a access tokens don't expire (at least not per the spec) and are valid until the user explicitly revokes the authorization (conceptually, forever). Therefore, a Spring Social connection for an OAuth 1.0/1.0a provider is also long-lived. In OAuth 2, authorizations are long-lived, but tokens are allowed to expire and thus are considered short-lived. Even so, a so-called "short-lived" token may last for a very long time. Facebook tokens, for example, have a 60-day expiration.
If you were to use an in-memory implementation, that'd be fine until you restart your app. Then suddenly all of those tokens are forgotten and then your users are forced to reauthorize. Depending on the provider, the user may not even notice that reauthorization is taking place, but in many cases they'll have to authorize your app again and it may confuse them or frustrate them. Persisting connections in a database ensures that those connections survive a server restart.
Also, if your app has a *LOT* of users, then an in-memory connection repository will have higher demands on memory-usage (obviously).
That said, I like the idea of an in-memory repo for development-time purposes. It's not on my todo list currently, but feel free to open a JIRA issue requesting it or (better yet) implement one yourself and submit a pull request.
Spring Social Project Lead