#StackBounty: #ruby-on-rails #postgresql #activerecord Is it OK to specify a schema in `table_name_prefix`?

Bounty: 50

TL;DR: Is it OK to specify a schema in table_name_prefix?

We have a large Rails application that is not quite a traditional multi-tenant app. We have a hundred clients, all supported by one app, and that number will never grow more than 1-2 per year. Currently, every client has their own Postgresql database.

We are addressing some infrastructure concerns of having so many distinct databases…most urgently, a high number of simultaneous database connections when processing many clients’ data at the same time.

The app is not visible, even to clients, so a lot of traditional multi-tenant web site philosophies don’t apply here neatly.

  • Each tenant has a distinct Postgres database, managed in
    database.yml.
  • Each database has a schema, named for the tenant.
  • We have a model specific to each tenant with notably different code.
  • Each model uses establish_connection to select a different database and schema.
  • Each model uses a distinct table_name_prefix with the client’s unique name.

The tables vary extensively for each tenant. There is no hope or desire to normalize the clients together. Clients are not provisioned dynamically — it is always a new code release with migrations.

We intend to move each of the client schemas into one database, so fewer distinct connection pools are required. The unique names we currently have at the database, schema, and table names mean there is no possibility of name collisions.

We’ve looked at the Apartment gem, and decided it is not a good fit for what we’re doing.

We could add all hundred schemas to schema_search_path, so all clients could share the same connection pool and still find their schema. We believe this would reduce our db connection count one-hundred-fold. But we’re a bit uneasy about that. I’ve found no discussions of how many are too many. Perhaps that would work, and perhaps there would not have a performance penalty finding tables.

We’ve found a very simple solution that seems promising, by adding the schema in the table_name_prefix. We’re already setting this like:

def self.table_name_prefix
  'client99_'
end

Through experimenting and looking within Rails 4 (our current version) and Rails 5 source code, this works to specify the schema (‘tenant_99’) as well as the traditional table prefix (‘client99’) :

def self.table_name_prefix
  'tenant_99.client99_'
end

Before that change, queries looked like this:

SELECT COUNT(*) FROM "client99_products"

After, they include the schema, as desired:

SELECT COUNT(*) FROM "tenant_99.client99_products"

This seems to answer our needs, with no downsides. I’ve searched the Interwebs for people encouraging or discouraging this practice, and found no mention of it either way.

So through all this, here are the questions I haven’t found definitive answers for:

  • Is there a concern of having too many schemas listed in schema_search_path?
  • Is putting a schema name in table_name_prefix okay?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.