2

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).

  • 3
    IMHO a sequence plus a prefix to rule them all will suffice. – McNets Oct 8 '19 at 10:06
  • @McNets So, you mean we replace the bigint with a "composite" primary key, which contains the "table id" inside it? I don't directly see a problem on the DB side of things, but then the issue becomes that our "code" has to support 2 different types of "keys"; bigint and, well, something else. This would potentially cause of lot of code changes, but still a useful proposal. – Sebastien Diot Oct 8 '19 at 11:05
  • One "obvious" (and very ugly) solution, is just one giant table, with all columns of all tables. But in our case, I believe we would hit the "maximum number of columns" limit. – Sebastien Diot Oct 8 '19 at 12:08
  • This question seems somewhat related: dba.stackexchange.com/questions/225299/… – Sebastien Diot Oct 8 '19 at 12:12
  • 1
    One thing to keep in mind it would be a good idea to make changes to the app to better support the features of postgres as you migrate to it as an rdms as it can not only make the migration easier but help you take better advantage of postgres features and make long term maintenance easier. While it may not be your call I would suggest pushing for it if you can. – Joe W Oct 8 '19 at 12:38
5

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.

  • +1 1) "all tables inherit from the base table" is not strictly required; it just seemed "logical", if we want to keep the same structure. 2) "the primary key will not be enforced for the child tables" That I was aware of, but it seems like a minor issue; just create the primary keys explicitly. 3) I thought the issue with the last select, was that it was a "full table scan", because Postgres does not know which of the child table has the row with id==42? And the pimary-key "index" on base does not extend to t1 and t2? – Sebastien Diot Oct 8 '19 at 10:59
  • 1
    The final query will do an index scan on the PK index for all children, yes. – a_horse_with_no_name Oct 8 '19 at 11:02
  • 1
    @SebastienDiot: with a hundred child tables each with 500.000 rows, I get the following plan: explain.depesz.com/s/TBtT - the number of child tables makes planning slow (35ms) the query itself then only takes 4ms – a_horse_with_no_name Oct 8 '19 at 11:13
  • 1
    @SebastienDiot: unless you use prepared statements where the plan is cached in the current session, then yes you will have to pay that every time when you select from the base table (this is not the case when selecting from a child table). – a_horse_with_no_name Oct 8 '19 at 11:34
  • 1
    @SebastienDiot: I have added another possible option. – a_horse_with_no_name Oct 8 '19 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.