Find a row by primary key, when you don't know in which table it is?

Question

I'm the (inofficial) DBA responsible for a proprietary OODBMS. Management wants us to move to Postgres, to reduce license costs. The move should be progressive, therefore we should also keep the same data-structure as in the OODBMS. Luckily, with the support for ARRAYs, and table inheritance, we can literally create an identical schema in Postgres. All tables will use bigint as primary key, and all (indirectly) inherit from the same base table.

The biggest issue is this: Our bigint key are (and must be) unique across all tables, and we must be able to quickly load a set of rows, based on their primary key, without knowing in which table they are. The rows will be spread over any and all tables. The primary concern here is speed, rather than disk or memory usage.

Expressed differently, what we need is a unique index across all tables. AFAIK, this is not possible in Postgres. What is the "next best" option? I'm open to any solution, including using, or maybe even coding, some "Postgres extension".

To give some hints about the actual DB, we are talking about 300 tables, 130M rows, and about 300GB in size (OODBMS size).

Answer 1

All tables will use bigint as primary key, and all (indirectly) inherit from the same base table

I am not sure I like this "all tables inherit from the base table", but given that, it sounds doable with Postgres:

To generate the primary key for all tables, create one sequence:

create sequence one_for_all as bigint;

Create the base table using that sequence to generate the values:

create table base (id integer primary key default nextval('one_for_all'));

Note that the primary key will not be enforced for the child tables!

Then create the child tables:

create table t1 (t1_data integer, primary key (id)) inherits (base);
create table t2 (t2_data integer, primary key (id)) inherits (base);

If you now insert into the child tables, the sequence will be used to generate the ID:

insert into t1 (t1_data) values (100);
insert into t2 (t2_data) values (200);

To find in which table a row is located, select from the base table and include the tableoid column, that identifies the actual table in which the row is located:

select b.*, tableoid::regclass as actual_table
from base b
where id = 42;

You will still need another query to return the full row of the child table.

Online example: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=4305e86b996e7a94c24faed733257232

---

Another approach that does not need inheritance is to generate ID values that encode the table name (shudder).

Something along the lines:

create sequence one_for_all as bigint;
create table lookup (table_name text primary key, code serial unique);
insert into lookup (table_name) values ('base'), ('t1'), ('t2');

create function get_id(p_tablename text)
  returns bigint
as
$$
  select nextval('one_for_all') * 1000 + code
  from lookup
  where table_name = p_tablename;
$$
language sql;

create function get_tablename(p_id bigint)
  returns text
as
$$
  select table_name
  from lookup
  where code = p_id % 1000;  
$$
language sql;

By multiplying the value from the sequence with 1000 we essentially have the lower 3 digits available to encode a unique number for each table. In order to be able to lookup those numbers, we need that additional table.

Note that this will fail miserably if the lookup table does not contain all tables!

Then instead of using nextval() use, the get_id() function:

create table base (id integer primary key default get_id('base'));
create table t1 (id integer primary key default get_id('t1'), some_value integer);
create table t2 (id integer primary key default get_id('t2'), some_data text);

Then you can do:

insert into t1 (some_value) values (42);
insert into t2 (some_data) values ('foo');
insert into t1 (some_value) values (117);
insert into t2 (some_data) values ('bar');

So the rows in t2 now go the IDs 2003 and 4003. The function get_tablename() can be used to retrieve the table name to which an ID belongs:

select get_tablename(2003);

get_tablename
-------------
t2           

This is certainly faster in terms of looking up the table name and does not carry the baggage of a huge inheritance tree with it. So performance wise it's probably faster. It will however become a maintenance nightmare.

Populating of the lookup table could perhaps be done through event triggers whenever a table is created or dropped.

---

If you can change your application to support varchar primary keys, instead of bigint (and you can live with the slightly higher storage requirements), you could also generate the ID as a string that simply contains the tablename:

create function get_id(p_tablename text)
  returns text
as
$$
  select concat(nextval('one_for_all'), '_', p_tablename)
$$
language sql;

Things like sorting and range queries will be complicated though.