191–200 of 345 posts
> If you have an option to choose, take a look at TSID maintained by Vlad Mihalcea. TSID: > A Java library for generating Time-Sorted Unique Identifiers (TSID). Wouldn't this TSID thing be more useful if it were implemented as a set of PostgreSQL stored procedures or something than a Java library? Not everyone uses Java.
The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator options. Consider other simple options like PNR-style (airline booking) locators first, especially if nontechnical users will quote them. It may even be OK if they’re reused every few years. Do not mix PK types within the schema for a service or application, esp…
IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows. ID data type leaks to every foreign key referencing given table. Many foreign key usually will be indexed, so this further degrades performance. There are multiple data types for a reason.
This is especially true for PostgreSQL which increments sequences for upserts (even if no records are inserted).
That's how I've hit 32-bit limits on tables that had only a couple million rows.
---
I would only use 32-bit for very selective items that is used in a lot of FKs, like a tenant ID.
If I don't want to leak the timestamp, I just use an auto generated integer along with it.
Earlier quoted context omitted.
That’s why the original comment suggested both bigserial and a separate UUID for public exposure. More to the point the person I was replying to said: > IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows The implication I took from that was that they were suggesting using serial over bigserial. My c…
My Dream Web Framework, which for a variety of reasons was never and never will be built, has built-in functionality for obscuring IDs in some session-level map, so you can indicate through some sort of type that something is an ID and it automatically allocates some sort of randomized identifier on the way out and converts it back transparently on the way back in. Thus, not only would DB ids in principle never show…
Earlier quoted context omitted.
As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. You can guess the time the system took between 2 uuid v7 id's. They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a) A big serial starting at a high number can't provide the t…
Can I ask (as a humble application developer, not a backend/database person), if the two requirements are: 1. The UUIDs should be ordered internally, for B-tree performance 2. The UUIDs should not be ordered externally, for security reasons Why not use encryption? The unencrypted ID is a sequential id, but as soon as it leaves the database, it's always encrypted. Like, when getting it out: SELECT encrypt(id) FROM tab…
The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator options. Consider other simple options like PNR-style (airline booking) locators first, especially if nontechnical users will quote them. It may even be OK if they’re reused every few years. Do not mix PK types within the schema for a service or application, esp…
IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows. ID data type leaks to every foreign key referencing given table. Many foreign key usually will be indexed, so this further degrades performance. There are multiple data types for a reason.
Secondly, it's not XXXk rows currently-you have to consider the complete lifetime of the table. When rows are deleted/created and how often.
So what you've said is true, but the set of appropriateness for smallint is a much smaller than expected.
Earlier quoted context omitted.
Can I ask (as a humble application developer, not a backend/database person), if the two requirements are: 1. The UUIDs should be ordered internally, for B-tree performance 2. The UUIDs should not be ordered externally, for security reasons Why not use encryption? The unencrypted ID is a sequential id, but as soon as it leaves the database, it's always encrypted. Like, when getting it out: SELECT encrypt(id) FROM tab…
If the key and encryption mechanism are ever leaked, those opaque external IDs can be converted easily back to sequence numbers, and vice versa, which might pose a risk for you or your users. You won't be able to rotate the encryption key without breaking anything external that tracks those encrypted IDs... third party services, SEO, user bookmarks, etc.
Earlier quoted context omitted.
As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. You can guess the time the system took between 2 uuid v7 id's. They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a) A big serial starting at a high number can't provide the t…
Can I ask (as a humble application developer, not a backend/database person), if the two requirements are: 1. The UUIDs should be ordered internally, for B-tree performance 2. The UUIDs should not be ordered externally, for security reasons Why not use encryption? The unencrypted ID is a sequential id, but as soon as it leaves the database, it's always encrypted. Like, when getting it out: SELECT encrypt(id) FROM tab…
Because then you have a key management problem, which adds complexity.
Earlier quoted context omitted.
I dont understand the recommendation of using bigserial with uuid column when you can use UUIDv7. I get that it made sense years ago when there was no UUIDv7, but why do people keep recommending it over UUIDv7 now beats me.
You're saving storage space but potentially leaking details. Is that ok for your application? No one can answer but your org.
Earlier quoted context omitted.
I dont understand the recommendation of using bigserial with uuid column when you can use UUIDv7. I get that it made sense years ago when there was no UUIDv7, but why do people keep recommending it over UUIDv7 now beats me.
As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. You can guess the time the system took between 2 uuid v7 id's. They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a) A big serial starting at a high number can't provide the t…