To guarantee the uniqueness of id, I don't need another column but I've added the region discriminent, planet, as this table could also be used to find the region when only the id is known. This should not be a problem with a UUID and a region name. The only thing you have to take care is that if you partitioned for data governance reasons (to keep sensitive data in specific regions) then the information in this global table should not contain sensitive information. This is not a problem in YugabyteDB because automatic sharding applies. Its primary key is compound of with a generated UUID ( id) and the region identifier ( planet):Įnter fullscreen mode Exit fullscreen mode The customer table is geo-partitioned to earth, moon and mars. In short, I started docker-compose up and created the following tablespaces and table: ![]() I've setup a cluster as in this previous post, a tree-region cluster (across the solar system just for fun) with a Docker Compose. In PostgreSQL this solution is limited because there's no sharding, and a table with a primary key is two structures: a B-Tree index and a Heap Table. Then, in YugabyteDB, this solution is not only logically equivalent to a global index but also physically equivalent. In YugabyteDB, tables and indexes, are the same because a table is stored in its primary key. Here is an example of building a global unique index, as a table maintained by a trigger. Another approach, a global query to check all partitions, must also be a serializable global transaction. However, a global index must be fully ACID and a transaction inserting a new primary key then becomes a global transaction. Sequence is one of this kind, and are optimized in YugabyteDB by limiting their transactional behavior to the minimum necessary: numbers are cached and the incrementing operations are never rolled back. If for any reason you want an additional guarantee of uniqueness for the part that doesn't include the partition key, there's no other choice than having a global source of truth, and it will limit the scalability. Other alternatives are for legacy applications. Applications designed for geo-distribution should do that. This means adding the date (for lifecycle management) or the region (for geo-partitioning) to the local identifier. include the partition key in the primary key.Both are designed to generate unique values, with a high probability in the case of UUID or even a 100% guarantee in the case of a Sequence. ![]() ![]() A primary key should be generated from a UUID or a Sequence, and should be immutable.How to guarantee global uniqueness? There are two easy solutions when the application is designed to scale, and one alternative for legacy applications, which is the goal of this blog post. However, on top of it, you may want to use the PostgreSQL declarative partitioning for two reasons: lifecycle management (with the ability to drop old partitions) or geo-partitioning (to assign partitions to specific regions with tablespaces). ![]() With YugabyteDB you don't need declarative partitioning to scale because tables are split with automatic sharding to small tablets, and, at this level, all indexes are global and can enforce uniqueness. In this latter case, local indexes that enforce the local uniqueness are sufficient to guarantee the global uniqueness of the compound key, but some applications do not like compound primary keys. It means that there's no built-in way to enforce the unicity of a key across all partitions, except when it includes the partition key. This includes the unique index that is necessary to enforce a primary key. One limitation of PostgreSQL declarative partitioning, when compared to some other databases like Oracle, is the impossibility to create global indexes.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |