![]() ![]() It's also a discussion about the size of the data type. The other thing that Christophe talks about is that this is not necessarily a discussion of “bigint versus UUID”. ![]() You will see index bloat if you’re using random keys. This is a strong argument for sequential keys, if you can use them.Ī random value interacts really badly with how the B-tree index gets structured. You will see index bloat if you're using random keys. If you have a B-tree index, and if you're using Postgres probably most of your indexes are going to be B-tree indexes, you will see that a random value interacts really badly with how the B-tree index gets structured. That's pretty fast on most machines.įurther, sequential keys interact much better with B-tree indexes. A sequential key doesn't require access to a random number generator, you just need to increment a single field. Sequential keys are faster to generate, for example. There are a couple of reasons why you would want to have a sequential key. ![]() If you're using distributed systems, there's another argument to have something that's at least semi random. One thing that's not mentioned in Christophe’s article is that if you're using any kind of distributed database, for example if you're using the Citus extension for Postgres (even though Citus has ways to do sequential sequences across multiple distributed nodes), it is much easier to handle this with a random number. It's very easy to make sure that you are just hitting that specific record, so randomness can be helpful to identify things uniquely. Second, it's much easier to handle random keys if you're looking at all your records in your whole database, and you're just trying to find something. It also has the downside that if there is a potential attack to get other people's data, it makes it easier to discover and try that out because the value of the next record is known. That's maybe bad for business because people think you're too small. If you have "/order/1", then people will know that they're the first order, the first person who bought something from your store, for example. Let's say you have an orders table in your database and you're listing your orders in your URL scheme. For example, a random value is harder to do an enumeration type attack on. Now, let's start by looking at his question: random or sequential? Reasons for using random keys UUIDs are 128 bits, whereas a bigint would be 64 bits. Only after that you should consider if your keys should be 64 bits or larger. What he's suggesting is that instead you should look at this problem by asking: “should our keys be random or sequential?” That's the first question to answer. In it, Christophe mentions that if you're asking yourself: “should a UUID or a bigint be the primary key?” what you’re really doing is conflating two different concepts. I want to start with this blog post by Christophe Pettus from the PG Experts team. What we have discussed in this episode of 5mins of Postgres ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |