4

In MySQL/InnoDB, the clustered index is synonymous with the primary key, so picking up a poor primary key affect your db performance, i.e. use of UUID as PK is a performance killer for database write.

Now, in PostgreSQL, there is no such thing as clustered limitation as in MySQL. What are the impact if I pick UUID as the PK? Does the database write performance killer also exists in PostgreSQL like in MySQL?

2

MySQL

While the MySQL Documentation literally says Typically, the clustered index is synonymous with the primary key, they are not one and the same. Please keep in mind that the clustered index (called gen_clust_index) was created in such a way that the index pages for the PRIMARY KEY and the table's row data coexist in the same pages. Having wide PRIMARY KEY values, such as a UUID, would make BTREE pages much wider. It could even cause data pages to split. Since the default innodb_page_size is 16KB in MySQL (this was a fixed compiled-in value in MySQL 5.5 and back), you must expect data pages to have fewer rows and less room for PRIMARY KEY navigation per 16KB page.

I have discussed PRIMARY KEY implications before : See my post InnoDB primary key efficiency

PostgreSQL

A StackOverflow post from Peter Eisentraut says The maximum length for a value in a B-tree index, which includes primary keys, is one third of the size of a buffer page, by default floor(8192/3) = 2730 bytes.

According to the PostgreSQL Wiki

The maximum table size, row size, and maximum number of columns can be quadrupled by increasing the default block size to 32k. The maximum table size can also be increased using table partitioning.

From this, let's say you use 32K blocks instead of the default 8K. You could fit 4 times more info, but there is still a limit of some kind.

Fortunately, UUID is just 16 bytes. I would not expect earth-shattering drawbacks from it.

Analysis

InnoDB's use of the Clustered Index, order inflexible as it may be, would benefit from smaller keys and would lend itself to fast writes because of not having to manage as much space for distributing keys within the Clustered Index.

While PostgreSQL's storage engine is not bounded or tethered like that of MySQL's InnoDB, smaller keys definitely have to process faster and consume less space. This would increase read and write performance for PostgreSQL, MySQL or any other RDBMS.

To demonstrate how structural changes can make a difference, let's use MySQL other storage engine MyISAM (which is non-transactional and does not have Clustered Index). I once took a MyISAM table and changed its row format from Dynamic to Fixed-Length and got a 20% increase in performance without touching anything else. I made the data bigger to get better read performance. The write performance increased for the better as well because there were fewer mechanisms to trigger any space management (See my post What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?).

Just reading through MySQL Documentation on Optimizing Data Size you get phrases like

Smaller tables normally require less main memory while their contents are being actively processed during query execution.

Any space reduction for table data also results in smaller indexes that can be processed faster.

Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space.

To further my point on smaller datatypes, I mention MySQL's SELECT ... PROCEDURE ANALYSE();. When you run SELECT * FROM tablename PROCEDURE ANALYSE();, the output is an analysis of your data, min values, max values, avg values, STD of values, and (here is the main point) the recommended data type for each column.

If you apply the ALTER TABLE commands to apply the recommended data types, the table has to end up smaller.

Even PostgreSQL would have to benefit from smaller data types. How ?

Please recall the PostgreSQL has this mechanism called TOAST (The Outside Attribute Storage Technique) that I have discussed before (See my post Proposal: MySQL blob handling revision has to juggle row data if there are large columns. Obviously, this mechanism would never be triggered is all row was small and many rows would fit comfortably in the 8K blocks PostgreSQL has.

Conclusion

Since your question seems to be more focused on PostgreSQL, let me answer your question like this:

What is the impact if I pick UUID as the PK? Does the database write performance killer also exists in PostgreSQL like in MySQL?

PostgreSQL would process write faster with smaller column values. UUID is 16 bytes. Using an 8-byte integer as a PRIMARY KEY would be faster to write and process than a UUID. A 4-byte integer is even faster than that. Lesson from all this ? Don't slow yourself down with wider PRIMARY KEY values if you don't have to.

1

If the UUIDs are not the sequential variant, then inserting them in the order they are created will cause some random leaf node in the index to be dirtied for each row inserted. Once the index is large enough, this will kill write performance on spinning hard drives, as it won't be able to consolidate the writes for efficient writing to disk.

  • 2
    I'm not convinced it will be a "kill". – ypercubeᵀᴹ Jan 14 '15 at 0:45
  • Yes, you are right, but does this impact PostgreSQL as well since the PK is not the clustered index. – Yoga Jan 14 '15 at 6:24
  • @yoga, the index is always "clustered" with itself. The table is not kept in the same order as the index, so the table is not clustered. But as the size increases, eventually the random write effect in the index itself is enough to cause problems. – jjanes Jan 14 '15 at 17:34
0

If you are running away from MySQL and hoping that PostgreSQL is better, then first consider what type of UUIDs you have. If they are the 'sequential variant' (Type 1), and if you have some clustering by time, then MySQL (or any database) can take advantage of it. Here is a discussion of such: http://mysql.rjweb.org/doc.php/uuid

Although MySQL does not have a datatype for UUIDs, it is pretty easy to convert the CHAR(36) to/from BINARY(16). The link above show that also.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.