That works fairly naturally with sequential identifiers (the records will have good locality both in the table and in the index), but the random nature of UUIDs contradicts that, resulting in poor cache hit ratio. For example, an e-commerce site mostly cares about orders from the last couple of days, rarely accessing data beyond this time window. ![]() Applications typically access a fairly limited subset of recent data. Of course, UUIDs influence read patterns too. (There is a feedback loop, as FPIs increase the amount of WAL, triggering checkpoints more often – which then results in more FPIs generated, …) And for small indexes, you probably don’t care that much.įurthermore, this random write access pattern inflates the amount of generated WAL, due to having to perform full-page writes every time a page is modified for the first time after a checkpoint. If the index is larger than memory, the cache hit ratio (both for page cache and shared buffers) is doomed to be poor. This is unfortunate, as it works against adaptive cache management algorithms – there is no set of “frequently” accessed pages that we could keep in memory. As the UUID values are generated as random, the location will be random, with uniform distribution for all index pages. But what about the index? For indexes ordering matters, so the database has little choice about where to insert the new item – it has to go into a particular place in the index. In the table the rows may be simply appended at the end, which is very cheap. Let’s assume we’re inserting rows into a table with an UUID primary key (so there’s a unique index), and the UUIDs are generated as random values. So let’s look at an extension generating “sequential” UUIDs, and how it can reduce the negative consequences of using UUIDs. Traditional primary keys based on sequences won’t give you any of that, which makes them unsuitable for public identifiers, and UUIDs solve that pretty naturally.īut there are disadvantages too – they may make the access patterns much more random compared to traditional sequential identifiers, cause WAL write amplification etc. UUIDs are a popular identifier data type – they are unpredictable, and/or globally unique (or at least very unlikely to collide) and quite easy to generate. PostgreSQL 9 Cookbook – Chinese Edition.PostgreSQL Server Programming Cookbook – 2nd Edition. ![]() ![]() PostgreSQL 9 Administration Cookbook – 3rd Edition.PostgreSQL High Availability Cookbook – 2nd Edition.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |