Обсуждение: Distributed systems and primary keys
Hello list,
Can anyone point me to some reading material on how auto-generated sequence primary keys are handled on distributed systems? I think the advice used to be to use GUIDs, but I thought I read somewhere that PostgreSQL now assigns a pool of numbers to each node when a sequence is implemented. I have searched the PostgreSQL 9.1.5 Documentation, but apparently my search terms are not quite what it takes, or dreamt that up.
Thanks,
Melvin
Can anyone point me to some reading material on how auto-generated sequence primary keys are handled on distributed systems? I think the advice used to be to use GUIDs, but I thought I read somewhere that PostgreSQL now assigns a pool of numbers to each node when a sequence is implemented. I have searched the PostgreSQL 9.1.5 Documentation, but apparently my search terms are not quite what it takes, or dreamt that up.
Thanks,
Melvin
On 07/12/2013 07:23 AM, Melvin Call wrote: > Hello list, > > Can anyone point me to some reading material on how auto-generated > sequence primary keys are handled on distributed systems? I think the > advice used to be to use GUIDs, but I thought I read somewhere that > PostgreSQL now assigns a pool of numbers to each node when a sequence is > implemented. I have searched the PostgreSQL 9.1.5 Documentation, but > apparently my search terms are not quite what it takes, or dreamt that up. PostgreSQL itself does not support a distributed architecture. You may be thinking of Postgres-XC? Sequences are local to each instances and it is not a pool, it is a 64bit allocation for each sequence within the local node, generally constrained only when called from the serial (big serial being 64 bits) type to 32 bits. Sincerely, Joshua D. Drake > > Thanks, > Melvin -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats
In Postgres-XC, GTM assigns sequence value to all the transactions in its cluster. XC is a kind of tightly-coupled distributed system. In a loosely-coupled distributed system, where each database is autonomous, we may need another mechanism.
I've learned that logical replication (used to be bi-directional replication) people are doing this kind of work.
Regards;
----------
Koichi Suzuki
Koichi Suzuki
2013/7/12 Joshua D. Drake <jd@commandprompt.com>
On 07/12/2013 07:23 AM, Melvin Call wrote:Hello list,
Can anyone point me to some reading material on how auto-generated
sequence primary keys are handled on distributed systems? I think the
advice used to be to use GUIDs, but I thought I read somewhere that
PostgreSQL now assigns a pool of numbers to each node when a sequence is
implemented. I have searched the PostgreSQL 9.1.5 Documentation, but
apparently my search terms are not quite what it takes, or dreamt that up.
PostgreSQL itself does not support a distributed architecture. You may be thinking of Postgres-XC?
Sequences are local to each instances and it is not a pool, it is a 64bit allocation for each sequence within the local node, generally constrained only when called from the serial (big serial being 64 bits) type to 32 bits.
Sincerely,
Joshua D. Drake
Thanks,
Melvin
--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Jul 12, 2013 at 9:46 AM, Joshua D. Drake <jd@commandprompt.com> wrote:
Thank you Joshua.
Yep, if you are correct, and I believe you are, then obviously I was thinking of something else (this happens when you work on multiple systems I'm afraid). That would explain why I was having so much trouble finding something that wasn't there.
This will make our planning a bit easier because I now know what the limits currently are. I will be checking out Postgres-XC as well.
PostgreSQL itself does not support a distributed architecture. You may be thinking of Postgres-XC?
On 07/12/2013 07:23 AM, Melvin Call wrote:Hello list,
Can anyone point me to some reading material on how auto-generated
sequence primary keys are handled on distributed systems? I think the
advice used to be to use GUIDs, but I thought I read somewhere that
PostgreSQL now assigns a pool of numbers to each node when a sequence is
implemented. I have searched the PostgreSQL 9.1.5 Documentation, but
apparently my search terms are not quite what it takes, or dreamt that up.
Sequences are local to each instances and it is not a pool, it is a 64bit allocation for each sequence within the local node, generally constrained only when called from the serial (big serial being 64 bits) type to 32 bits.
Sincerely,
Joshua D. Drake
Thank you Joshua.
Yep, if you are correct, and I believe you are, then obviously I was thinking of something else (this happens when you work on multiple systems I'm afraid). That would explain why I was having so much trouble finding something that wasn't there.
This will make our planning a bit easier because I now know what the limits currently are. I will be checking out Postgres-XC as well.
Regards,
Melvin
Melvin
On Fri, Jul 12, 2013 at 10:04 AM, Koichi Suzuki <koichi.szk@gmail.com> wrote:
In Postgres-XC, GTM assigns sequence value to all the transactions in its cluster. XC is a kind of tightly-coupled distributed system. In a loosely-coupled distributed system, where each database is autonomous, we may need another mechanism.I've learned that logical replication (used to be bi-directional replication) people are doing this kind of work.Regards;----------
Koichi Suzuki
Thanks Koichi. I will be looking into it shortly.
Melvin
2013/7/12 Joshua D. Drake <jd@commandprompt.com>
On 07/12/2013 07:23 AM, Melvin Call wrote:Hello list,
Can anyone point me to some reading material on how auto-generated
sequence primary keys are handled on distributed systems? I think the
advice used to be to use GUIDs, but I thought I read somewhere that
PostgreSQL now assigns a pool of numbers to each node when a sequence is
implemented. I have searched the PostgreSQL 9.1.5 Documentation, but
apparently my search terms are not quite what it takes, or dreamt that up.
PostgreSQL itself does not support a distributed architecture. You may be thinking of Postgres-XC?
Sequences are local to each instances and it is not a pool, it is a 64bit allocation for each sequence within the local node, generally constrained only when called from the serial (big serial being 64 bits) type to 32 bits.
Sincerely,
Joshua D. Drake
Thanks,
Melvin--
--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general