Debugging PostgreSQL More Easily

https://www.cybertec-postgresql.com/en/debugging-postgresql-more-easily/

by amalinovic • 1 year ago

63 18 1 year ago

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

ltbarcly3• 1 year ago

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.

kelseydh• 1 year ago

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

cryptonector3• 1 year ago

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

wordofx2• 1 year ago

Hilo > UUID

wordofx2• 1 year ago

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

apt-apt-apt-apt• 1 year ago

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

acjohnson55• 1 year ago

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

cryptonector3• 1 year ago

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

mattashii2• 1 year ago

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.

cryptonector3• 1 year ago

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

nightpool2• 1 year ago

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?

dvrp• 1 year ago

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.

brodo• 1 year ago

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

TheJCDenton• 1 year ago

Extremely useful

cachvico• 1 year ago

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

Debugging PostgreSQL More Easily

Related Stories