Does Rails use a single "database session" indefinitely?

Question

I have a use case for custom PostgreSQL prepared statements in a Rails app (apart from what ActiveRecord does automatically).

My use case is to bulk insert a bunch of rows, such as:

PREPARE insert_2_events (text, text, timestamp, int, json, timestamp, timestamp)
AS
INSERT INTO events (uuid, kind, generated_at, team_id, data, created_at, updated_at)
VALUES
($1, $2, $3, $4, $5, $6, $7), ($8, $9, $10, $11, $12, $13, $14)
ON CONFLICT(uuid) DO NOTHING;

EXECUTE insert_2_events(
  'ed8bd52a-4ea9-4548-ab02-87adfca90789', 'call', '2017-04-06T16:14:20-04:00', 1, '{"some":"data"}', '2017-04-06T20:14:20+00:00', '2017-04-06T20:14:20+00:00',
  '27c3251e-aff0-4005-918a-d7011f984515', 'text', '2017-04-06T16:14:20-04:00', 1, '{awesome:true}', '2017-04-06T20:14:20+00:00', '2017-04-06T20:14:20+00:00'
);

I can't know whether, for any given execution, there will be 2 rows as shown, 10 rows, or whatever. So what I'd like to be able to do is this:

• If my method is called with 3 rows, see if I've already PREPAREed a 3-row version of this statement.
• If not, PREPARE it
• EXECUTE it

I could track "have I already prepared this?" in application code, but I'm not sure that's reliable.

The PostgreSQL docs say:

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use. Prepared statements can be manually cleaned up using the DEALLOCATE command.

I'm not sure how the "database session" concept maps to a Rails application.

• Will the Rails app be using the same database session the whole time it's running?
• Will different instances (Unicorn worker processes, Puma threads, or whatever) be considered different database clients (I think so), in which case I'd need to track which statements have been prepared in a thread-local variable?

I have a prototype working where I just always prepare the statement, and always DEALLOCATE it after execution, but that seems pretty inefficient.

This all may be too hacky to be practical, but it's at least interesting.

Answer 1

PostgreSQL and Rails use different words for the same thing: A session in PostgresSQL corresponds to a connection in Rails.

Will the Rails app be using the same database session the whole time it's running?

Short answer: It tries to.

Long answer: Rails maintains a thread-safe pool of connections/sessions. With each request a connection is borrowed from the pool and returned afterwards.

If there are no existing connection, e.g. at the very first request or if all active connections are checked out, Rails opens a new connection (Up until the maximum number of connections is reached)

So unless the connections get closed (manually or by PgBouncer for example) you'll end up with the same connection over the whole application lifetime.

Will different instances (Unicorn worker processes, Puma threads, or whatever) be considered different database clients

Processes: Yes.

Threads: It depends. Concurrent threads definitely get their own connections, but if there's no more than one thread active at any time, then it's most likely they all get the same connection from the pool.