Debugging PostgreSQL More Easily
21 comments
·March 18, 2025brodo
TheJCDenton
Extremely useful
ltbarcly3
You absolutely should NOT use postgresql table inheritance for anything. This is extremely bad advice.
Table inheritance goes back to the 90's or something when Object databases were thought to be the 'next big thing'. It's not actively developed and has a lot of sharp edges and you shouldn't use it.
If you really want something like this, then I would use UUID primary keys, then make a view that UNION ALLs. like this:
CREATE VIEW id_to_table AS
SELECT id, 'products' FROM products
UNION ALL
SELECT id, 'users' FROM usersNow you don't need any participation or special magic on any of the tables.
Queries against this like SELECT id, table FROM id_to_table WHERE id = "31ba486f-05ff-11f0-8e4c-a8a15904660a";
Will still use the pk index for each table and will return instantly. You don't even need to keep this view created all the time, just write some SQL to generate the SQL for the view and then execute that, then do your query.
IF you want them to be unique int (ids globally) just make a new sequence and use that sequence whenever you create a new table.
edit: at this point I'm like sure that there is some automated downvoting on this site! why would someone downvoe this? You would think it was political.
kelseydh
Was coming here to find this comment, seeing the table inheritance to create a table looked like a big red flag.
cryptonector
UniSQL/X had a reasonable table inheritance model in the 90s, as did UName*It (though I'm probably one of half a dozen people here max who know what UName*It is, let alone used it or worked on it in any way).
cryptonector
I thought PG's table inheritance was widely thought to be broken, do not use.
mattashii
It isn't broken per se, but the behaviour of inheritance with its many gotchas can be very surprising to a user. Like "IDs are not guaranteed unique across inherited tables" is probably not something you want from your database.
So definitely a case of "do not use", but that's only for your sanity, not because it doesn't work as advertised.
nightpool
TFA says "First of all, there is ONE global sequence for all tables, which means that all IDs across the entirety of the systems are unique", that does sound like "IDs are guaranteed unique across table", no? Maybe there's some gotcha there?
mattashii
That relies on the ID generation to be unique, rather than unique constraints.
E.g. INSERT INTO t_country (id, name) VALUES (1, 'NL') is valid and would not fail in the demonstrated database, but it would result in multiple rows when querying SELECT FROM t_global g WHERE g.id = 1.
A user inserting their own values or resetting the sequence would not necessarily cause immediate unique constraint faulures while duplicate IDs are routed into inherited tables that don't already contain that ID.
cryptonector
If it shouldn't be used because its properties are never desirable, then it's broken enough that it should be removed.
apt-apt-apt-apt
Does using a global PK (id: serial) slow down concurrent inserts in different tables, from having to reference a single, shared auto-incrementer?
acjohnson55
It would have to have some impact. Whether that's significant would depend on the situation.
dvrp
I must be getting old fast because every time I see something that goes so hard against what's common knowledge I can't help it but be skeptical about it. Sure, I am not saying this method does not work, but if you are proposing something that has been around for decades and yet nobody uses it... you need to give arguments for why that is.
null
cachvico
If you don't know which table your ID came from you're probably doing something wrong to begin with. (Why hey there you overly normalized schema..)
itsthecourier
I'm wondering about the contention of this pattern, how it handle updates and how big they have gone as well of how many inserts/updates per second
jayjinjay
[dead]
null
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_t...