使用BYTEA PK的缺点？ [英] Drawbacks of using BYTEA for PK?
问题描述
与使用
原语/原子数据类型（如INT / SERIAL）相比，使用BYTEA for PK有什么缺点吗？ （比如重要的
性能打击，特殊的FK行为等）。
我计划使用BYTEA进行GUID（当然，暂时我希望，直到
PostgreSQL正式支持GUID数据类型），因为与目前其他数据类型相比，它似乎是最方便+紧凑的
可用。我对大多数PK列使用GUID。
-
dave
------- --------------------（广播结束）------------------------- -
提示1：订阅和取消订阅命令转到 ma ******* @ postgresql .org
David Garamond写道：
有没有与使用INT / SERIAL等原始/原子数据类型相比，使用BYTEA for PK的缺点是什么？ （比如重要的性能打击，特殊的FK行为等）。
我计划使用BYTEA进行GUID（当然，暂时我希望，直到
PostgreSQL正式支持GUID数据类型），因为它似乎是目前可用的其他数据类型最方便+紧凑。我对大多数PK列使用GUID。
GUID？难道这不仅仅是序列上的MD5吗？
SELECT（MD5（NEXTVAL（''my_table_seq''）））AS my_guid;
由于7.4内置了md5功能，所以有你的支持;-)
现在只需将它添加到你的表的触发器中就可以了。
我认为在MS产品中，他们用
样式8-4-4-4-12中的破折号格式化guid，但它仍然看起来像32个字符hex
字符串或16字节（128位）值。您可以随意选择存储
值，我不确定什么是最优的，但是
位是位，对吧？
< br $>
Dante
---------------------------（...结束广播）---------------------------
提示2：你可以一次性使用取消注册命令
（发送取消注册YourEmailAddressHere到 ma ******* @ postgresql。组织）
2004年1月11日星期日22：05，D。Dante Lorenso写道：David Garamond写道：与使用INT / SERIAL等原始/原子数据类型相比，使用BYTEA for PK有什么缺点吗？ （比如重要的性能打击，特殊的FK行为等）。
我计划使用BYTEA进行GUID（当然，暂时我希望，直到
PostgreSQL正式支持GUID数据类型），因为它似乎是目前可用的其他数据类型最方便+紧凑。我对大多数PK列使用GUID。
GUID？难道这不仅仅是一个序列上的MD5吗？
SELECT（MD5（NEXTVAL（''my_table_seq''）））AS my_guid;
我认为GUID的重点是它应该在任何数量的
机器上都是唯一的，而不需要那些机器来协调它们对GUID的使用
值。
我认为典型的方法是使用类似的东西：
hash_fn（network_mac_address || other_hopefully_unique_constant ||
sequence_val）
并确保发生碰撞的概率可接受的低。
ISTR长达一年或两年的讨论回到其中一个列表，对于那些
感兴趣的人。
-
Richard Huxton
Archonet Ltd
---------------------------（广播结束）---------- -----------------
提示9：如果您的
joi，计划员将无视您选择索引扫描的愿望ning column'的数据类型不匹配
D. Dante Lorenso写道：GUID？这不仅仅是序列上的MD5吗？
SELECT（MD5（NEXTVAL（''my_table_seq''）））AS my_guid;
我知道有几种算法可以生成GUID，但这是
当然不足:-)你需要确保生成的GUID
将在整个网络空间中独一无二（或者更确切地说，GUID
应该有非常小的机会与其他人碰撞
GUID）。甚至OID也不是一个好种子。
也许我可以通过MD5制作一个GUID（两个随机数||时间戳|| a
唯一像''/ sbin / ifconfig''输出的MD5这样的种子... ...
由于7.4内置了md5函数，所以有你的支持; - ）
好吧，直到有一个GUID或INT128或BIGBIGINT内置类型我怀疑
很多人会认为PostgreSQL完全支持GUID。我相信
那里有GBorg网站上的pguuid项目就是这样的。
-
dave
---------------------------（广播结束）----------- ----------------
提示4：不要杀死-9''邮政局长
Are there any drawbacks of using BYTEA for PK compared to using a
primitive/atomic data types like INT/SERIAL? (like significant
performance hit, peculiar FK behaviour, etc).
I plan to use BYTEA for GUID (of course, temporarily I hope, until
PostgreSQL officially supports GUID data type), since it seems to be the
most convenient+compact compared to other data types currently
available. I use GUIDs for most PK columns.
--
dave
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
David Garamond wrote:
Are there any drawbacks of using BYTEA for PK compared to using a
primitive/atomic data types like INT/SERIAL? (like significant
performance hit, peculiar FK behaviour, etc).
I plan to use BYTEA for GUID (of course, temporarily I hope, until
PostgreSQL officially supports GUID data type), since it seems to be
the most convenient+compact compared to other data types currently
available. I use GUIDs for most PK columns.
GUID? Isn''t that really nothing more than an MD5 on a sequence?
SELECT (MD5(NEXTVAL(''my_table_seq''))) AS my_guid;
Since 7.4 has the md5 function built-in, there''s your support ;-)
Now just add that to your table''s trigger and your good to go.
I think in MS products, they format the guid with dashes in the
style 8-4-4-4-12 but it still looks to me like a 32 character hex
string or a 16 byte (128 bit) value. You can choose to store the
value however you like, I''m not sure what would be optimal, but
bits are bits, right?
Dante
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
On Sunday 11 January 2004 22:05, D. Dante Lorenso wrote:David Garamond wrote:Are there any drawbacks of using BYTEA for PK compared to using a
primitive/atomic data types like INT/SERIAL? (like significant
performance hit, peculiar FK behaviour, etc).
I plan to use BYTEA for GUID (of course, temporarily I hope, until
PostgreSQL officially supports GUID data type), since it seems to be
the most convenient+compact compared to other data types currently
available. I use GUIDs for most PK columns.
GUID? Isn''t that really nothing more than an MD5 on a sequence?
SELECT (MD5(NEXTVAL(''my_table_seq''))) AS my_guid;
I think the point of a GUID is it''s supposed to be unique across any number of
machines without requiring those machines to coordinate their use of GUID
values.
I think the typical approach is to use something like:
hash_fn( network_mac_address || other_hopefully_unique_constant ||
sequence_val )
and make sure that the probability of getting collisions is acceptably low.
ISTR a long discussion a year or two back on one of the lists, for those that
are interested.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
D. Dante Lorenso wrote:GUID? Isn''t that really nothing more than an MD5 on a sequence?
SELECT (MD5(NEXTVAL(''my_table_seq''))) AS my_guid;
I know there are several algorithms to generate GUID, but this is
certainly inadequate :-) You need to make sure that the generated GUID
will be unique throughout cyberspace (or to be more precise, the GUID
should have a very very small chance of colliding with other people''s
GUID). Even OID is not a good seed at all.
Perhaps I can make a GUID by MD5( two random numbers || a timestamp || a
unique seed like MD5 of ''/sbin/ifconfig'' output)...
Since 7.4 has the md5 function built-in, there''s your support ;-)
Well, until there''s a GUID or INT128 or BIGBIGINT builtin type I doubt
many people will regard PostgreSQL as fully supporting GUID. I believe
there''s the pguuid project in GBorg site that does something like this.
--
dave
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster
这篇关于使用BYTEA PK的缺点？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
