SQL Keys in Depth • Hacker News Board

Re: SQL Keys in Depth

#171

Post by manigandham » Fri, Jan 05, 2018, 4:30 PM UTC

needusername wrote: post #155

manigandham wrote: post #103

After a decade of large systems relying on RDBMs, we now use 64-bit integers for all primary keys with a global Hi/Lo id generation system (app reserves a range of numbers on startup to assign to records automatically). This means plenty of ID space, maintains rough numeric ordering, allows ID creation without a roundtrip for every insert, is easily portable across different databases, and produces unique IDs for eve…

> global Hi/Lo id generation system Can you elaborate a bit? Are you using an ORM, eg. Hibernate? If so have to considered other strategies like pooled-lo, IDENTITY or recursive CTEs that offer the same benefits but make the sequence values match the database values and reduce "id loss"?

Anything with atomic increments can do this, we use a simple table called idsequence with a single column and row. When an application starts up, it upserts 100000 and gets the new value. This gives both the min and max values that the app can then use as ids for any entities with no overlap with anything else.

It's actually not Hi/Lo but similar. You can also add another column to that table if you want multiple "sequences" but we like to keep it simple and with bigints, you'll never realistically run out.

If you're using an ORM, you might need some extra work but most of the time they are smart enough to use an existing ID if the object already has one rather than try to get one from the database.

Re: SQL Keys in Depth

#172

Post by manigandham » Fri, Jan 05, 2018, 4:40 PM UTC

jmts wrote: post #170

needusername wrote: post #155

Earlier quoted context omitted.

> global Hi/Lo id generation system Can you elaborate a bit? Are you using an ORM, eg. Hibernate? If so have to considered other strategies like pooled-lo, IDENTITY or recursive CTEs that offer the same benefits but make the sequence values match the database values and reduce "id loss"?

I'm also interested in some elaboration of this scheme. While I'm less interested in ORMs etc, the advantages/disadvantages would be useful to compare to some recent thoughts I've had regarding the use of databases. Professionally I have little use for RDBMSs, however recently I've been working on some side projects with a focus on flexibility and modularity that have changed my perspective on how databases should be…

Answered in the sibling comment: https://news.ycombinator.com/item?id=16079576

Anything with atomic increments works so we've also used a completely separate system for just IDs before like dynamodb or google's cloud datastore.

Re: SQL Keys in Depth

#173

Post by needusername » Tue, Jan 09, 2018, 7:47 AM UTC

manigandham wrote: post #171

needusername wrote: post #155

Earlier quoted context omitted.

> global Hi/Lo id generation system Can you elaborate a bit? Are you using an ORM, eg. Hibernate? If so have to considered other strategies like pooled-lo, IDENTITY or recursive CTEs that offer the same benefits but make the sequence values match the database values and reduce "id loss"?

Anything with atomic increments can do this, we use a simple table called idsequence with a single column and row. When an application starts up, it upserts 100000 and gets the new value. This gives both the min and max values that the app can then use as ids for any entities with no overlap with anything else. It's actually not Hi/Lo but similar. You can also add another column to that table if you want multiple "se…

What was your reason for not using database sequences?

Re: SQL Keys in Depth

#174

Post by manigandham » Mon, Jan 22, 2018, 7:16 PM UTC

needusername wrote: post #173

manigandham wrote: post #171

Earlier quoted context omitted.

Anything with atomic increments can do this, we use a simple table called idsequence with a single column and row. When an application starts up, it upserts 100000 and gets the new value. This gives both the min and max values that the app can then use as ids for any entities with no overlap with anything else. It's actually not Hi/Lo but similar. You can also add another column to that table if you want multiple "se…

What was your reason for not using database sequences?

It was in my original post:

>> This means plenty of ID space, maintains rough numeric ordering, allows ID creation without a roundtrip for every insert, is easily portable across different databases, and produces unique IDs for every row in the database which greatly simplifies everything from caching to replication.