[discussion] best practice to revert apartment and go with one big schema

[krtschmr]

we use PSQL 13 and have a lot of tenants, each with their own schema. While the Client lives in public, everything else lives within schema. All ids are of type uuid.

We want to merge all schemas together into one big shared table but we are unsure what the best practice is.

i came up with a first idea where we could merge the data, table by table.
the idea is to dump the data as CREATE statements, then change the namespace with sed, then import the data into the public schema. after that, we need to add the model to config.excluded_models = %w[Client] and in theory this should run.

this would be quite some work for ~100 schemas with ~100 tables.

in theory we can also remove all constraints, dump all tables of all schemas and then all merge them together into public, then add indexes and constraints again.

class SchemaMerger
  def self.run(table_name)
    schemas = Client.pluck(:name).collect { |s| "-n #{s} " }.join
    tables = Client.pluck(:name).collect { |n| "-t #{n}.#{table_name} " }.join
    cmd = "pg_dump --dbname=$DB --data-only --disable-triggers --inserts #{schemas} #{tables}"
    `#{cmd} | grep "INSERT INTO" > #{table_name}.dmp`
    Client.pluck(:name).each do |client|
      o = "INSERT INTO #{client}.#{table_name} VALUES"
      n = "INSERT INTO public.#{table_name} VALUES"
      `sed -i 's/#{o}/#{n}/g' #{table_name}.dmp`
      o = "INSERT INTO \"#{client}\".#{table_name} VALUES"
      `sed -i 's/#{o}/#{n}/g' #{table_name}.dmp`
    end
    `psql $DB < #{table_name}.dmp`
    `rm #{table_name}.dmp`
  end
end

i wonder if there are any best practices on how to squash schemas into one?

[krtschmr]

I found a more simple alternative. In PSQL you can just INSERT INTO table SELECT from schema.table.
Important is to have constraints and keys under control. might be removed first, then data merged, then keys added again.
unique keys on email must now be unique on client_id, email.

So the Step for us if to have a migration

def up
  if Apartment.current == "public"|
    TableMerger.run("settings")
  end
end

and then add Setting to config.excluded_models = %w[Client Setting]

class TableMerger
  def self.run(table_name)
    ActiveRecord::Base.connection.execute("DELETE FROM public.#{table_name}")

    Client.pluck(:name).each do |client|
      sql = "INSERT INTO public.#{table_name} SELECT * from #\"{client}\".#{table_name}"
      ActiveRecord::Base.connection.execute(sql)
    end
  end
end

This worked and seem to be promising.