31–40 of 81 posts
The initial design was quite flawed, in addition to not using sequences they should not use one DB object per organization, but rather a single object with an "organization" field.
Isn't creating a sequence a bad idea in general, anyway? Aren't there a zillion ways to compromise things if you know that some field is a sequence?
> Isn't creating a sequence a bad idea in general, anyway? No: - sequences are very common. I recommend using them on every table for mgmt. and internal efficiency reasons. For example, with Innodb, if you don't have a numeric id as a PK, it will assign an invisible one for internal use anyway. Most third-party tools won't allow you to manage tables without numeric PK's. - in most large applications, most sequence ID…
> with Innodb, if you don't have a numeric id as a PK, it will assign an invisible one for internal use anyway.
There's no requirement that your PK be numeric with InnoDB. A monotonically increasing numeric ID will have the best performance, yes. But even a small-ish varchar PK may perform better than relying on InnoDB's internal invisible one, depending on the workload.
InnoDB will only use an invisible numeric PK if you have no explicit PK defined, and you either have no UNIQUE KEYs at all either, or all of your UNIQUE KEYs have nullable columns. The column type of your PK is irrelevant though.
The invisible numeric PK is terrible because it uses a system-wide lock (or at least it did prior to 8.0, not sure if this has been fixed). So if you're inserting at any real volume to multiple tables like this, performance suffers badly. Worse still, the lock it uses is the dict_sys mutex, which other code paths (e.g. DROP TABLE) also hit.
Customer #C1010 may have 2 locations #L1899 and #L8443 and many invoices #IN1940 and #IN2399 for example.
When we first built the system, I considered using native Postgres sequences to track these, but decided against them because of how they are affected during a rollback. In our system, each account has a record in a table that controls the next value of the sequence.
We have an event in our ORM to automatically generate the next sequence value as part of the transaction so if the transaction is rolled back, the next sequence value is as well. Sure, it requires locking the sequence record but it's a very small table and generating a sequence is quick. We wrapped everything up in a stored procedure named generate_sequence() which returns the next value of the sequence and increments it. It's scaled to millions of records quite well without issue.
Earlier quoted context omitted.
Do you mean enumeration, whereby an attacker starts at some ID and tries several in sequence? It has never been a problem for me and my applications. Just because you know a record exists, doesn't mean you can see it. For example, if you are authorized to view https://www.example.com/records/100 , and you decide to try https://www.example.com/records/101 , then the code will check to see if you're authorized to see r…
> I suppose there are situations where it's a problem if someone finds out that record 101 even exists, but not in any of my apps. Well, it's things like say, an invoice number. I can buy something from you. And then 7 days later I buy something else from you. If the invoice numbers are in sequence, I just gained quite a bit of information about how fast you are selling things. That's the kind of information leak tha…
Perhaps it's a bad idea in this one specific case but not in general.
Be careful about treating sequences as monotonic. For transactions in progress at the same time, the order of sequence values might not be consistent with the order of transaction commits and the logical order of serializable transactions.
One example where that could cause problems is if you filter a change stream using the last seen id. For such an approach an out-of-order id would lead to missed events.
> we don’t just want a monotonically increasing sequence Be careful about treating sequences as monotonic. For transactions in progress at the same time, the order of sequence values might not be consistent with the order of transaction commits and the logical order of serializable transactions. One example where that could cause problems is if you filter a change stream using the last seen id. For such an approach a…
Yep, already walked that path with invoice numbers. The only thing you can say for sure about a sequence is the next number will be greater, which is not good enough for many kinds of identifiers.
Thank you for uncovering this edge case. To summarize, Postgres reserves a batch of 32 serial numbers from its sequence objects. Then, in the case of a crash, or the promotion of a "follower", that batch is lost. I consider ID numbers somewhat opaque, like GUIDs but maybe not that opaque. Fretting about gaps in ID numbers can cause hair loss. This is just one of many ways gaps can happen. It is just an artifact of "s…