Debugging PostgreSQL More Easily | Hacker News Reader

Debugging PostgreSQL More Easily

cybertec-postgresql.com

63 pointsamalinovic18 comments

https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_t...

Extremely useful

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 users

... and so on

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

Was coming here to find this comment, seeing the table inheritance to create a table looked like a big red flag.

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

Hilo > UUID

lol can’t imagine the messy systems people create if they like UUID.

I thought PG's table inheritance was widely thought to be broken, do not use.

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.

If it shouldn't be used because its properties are never desirable, then it's broken enough that it should be removed.

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?

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.

scary! definitely a really dangerous footgun and I'm not surprised nobody recommends it.

Does using a global PK (id: serial) slow down concurrent inserts in different tables, from having to reference a single, shared auto-incrementer?

It would have to have some impact. Whether that's significant would depend on the situation.

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.

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

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

Reply on news.ycombinator.com