What are the disadvantages in using IDs suffixed by a table identifying number?

Question

I'm playing with:

DROP TABLE users CASCADE;
CREATE SEQUENCE users_id_seq MINVALUE 100;
CREATE TABLE users (
    id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('users_id_seq') * 100 + 1,
    ...
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;

DROP TABLE txns CASCADE;
CREATE SEQUENCE txns_seq MINVALUE 100;
CREATE TABLE txns (
    id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('txns_seq') * 100 + 2,
    ...
);
ALTER SEQUENCE txns_seq OWNED BY txns.id;
...

To my mind, this has the following benefits:

• Each id is unique across the whole database, removing wrong-table errors.
• No id has a value of less than 10,000, removing confusion with most iteration counters.
• The containing table is easily learned from the last two digits of any id.

Disadvantages:

• We're wasting 99% of ids.

What other disadvantages haven't I considered?

Answer 1

As has been commented, there are good reasons not to go there. Canonically, a surrogate PK is a unique identifier for rows within its table without carrying additional information.

I have not had cases of "wrong-table errors" or "confusion with iteration counters", yet. Reducing the key space of an integer (int4) PK by 99% may come around to bite you later, as well as limiting yourself to a maximum of 100 tables. If you start relying on the meaning of the number (which I would advise against), you better make sure that new tables stick to the rules. And nobody inserts manual ID values or alters ID values ...

That said, if you go there, consider:

CREATE SEQUENCE users_id_seq MINVALUE 1 INCREMENT 100;  -- ①
CREATE TABLE users (
    users_id bigint PRIMARY KEY DEFAULT nextval('users_id_seq'), -- ②
    ...
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;

CREATE SEQUENCE txns_id_seq MINVALUE 2 INCREMENT 100;
CREATE TABLE txns (
    txns_id bigint PRIMARY KEY DEFAULT nextval('txns_id_seq'),
    ...
);
ALTER SEQUENCE txns_id_seq OWNED BY txns.id;

① Use MINVALUE 1 INCREMENT 100, MINVALUE 2 INCREMENT 100 etc. instead of manual calculation.

② Use bigint to more than make up for the reduced key space.
Also, it's odd that you'd put so much effort into unique IDs across the DB, and then use non-descriptive, duplicative column names like id. txns_id is a better naming convention.

Downside: more susceptible to setval() or ALTER SEQUENCE breaking the regime. Revoke the UPDATE privilege on sequences from all plain users.