Database identity VS. Servoy sequence

[deezzub]

I first used the database identity as a unique identifier, after I found a bug in Servoy, I switched to Servoy sequence. I would like to know, if this can have any sideffects? Is the server generating the Servoy sequences for the connected clients?

Is the Servoy sequence unique, if for example 2 connected clients create new records at the same time?

[Harjo]

1. yes, the sequence is done serverside, and the servoy-repository is keeping track, of the sequencenumbers per table
2. unique? It better be! or else we would run into trouble 10 years ago! (Using servoy sequences ever since, with Firebird, Sybase and now PostgreSQL, without ANY issues!)

Be aware, that using servoy-sequences, the databases can't be altered outside Servoy, or else your sequence's become out of sync!

[david]

UUID IDs is the only way to go! No reseting sequences, can modify tables outside of Servoy, export/import a breeze, can easily split/combine like tables (SaaS task), etc.

[ROCLASI]

UUID IDs is the only way to go!

In fact there is a strong opinion for and against using UUID as PK's.
In the end it all comes down to what your use-case is and can you live with the downside of either option.

[Harjo]

We too are still not convinced if using UUID as PK's, is the road to go....
Still not clear, what the consequences are with a large SaaS solution, containing tables with thousands (millions) of records

[david]

UUID IDs is the only way to go!

In fact there is a strong opinion for and against using UUID as PK's.
In the end it all comes down to what your use-case is and can you live with the downside of either option.

10 years ago there were two valid sides to this decision. Now UUID for PK is a best practice except for a few rare edge cases. If you're in this territory then it is very unlikely you would be developing this app with Servoy.

[ngervasi]

UUIDs are sexy but are very difficult to handle during debugging (integer pks easily show records creation order and are easy to spot when debugging), another concern is about performance if you have very large tables.
David, are you experiencing any performance issue on queries? Especially joins?

[david]

UUIDs are sexy but are very difficult to handle during debugging (integer pks easily show records creation order and are easy to spot when debugging), another concern is about performance if you have very large tables.
David, are you experiencing any performance issue on queries? Especially joins?

All total bunk In what cases is the PK type a query performance consideration? If you know this answer, why are you developing in Servoy? And I had a developer not long ago state that they were using integers instead of UUIDs because they were easier to remember and type when debugging. This is not a valid reason!

SaaS, sharing data, syncing offline data, distributed data, multiple applications on same data, security, no maintaining uniqueness overhead, no scaling issues, multi-threaded ok, mobile friendly, cloud friendly, database vendor agnostic, unique across tables...these are valid reasons.

Auto-increment integers didn't have to deal with all these situations back in the day. And nowadays with compression implemented by many databases, UUID space cost is negligible.

Many of the new db's in the past handful of years don't even have auto-increment integer PKs as an option.

[Harjo]

UUID space cost is negligible.

Space is not the issue, performance is!!
Nobody (sofar) can show real figures what the impact or difference is in performance with large scaled databases!

[david]

UUID space cost is negligible.

Space is not the issue, performance is!!
Nobody (sofar) can show real figures what the impact or difference is in performance with large scaled databases!

Space cost historically is the classic main debate issue. Space can still be an issue in certain types of deployments—but none that a Servoy app would used for.

Performance: exactly. "Large scaled databases"...note what type of PKs all the new databases in this category generate by default out of the box. Apparently they aren't worried about any performance issues with UUIDs.

[ROCLASI]

Hi David,

Now UUID for PK is a best practice except for a few rare edge cases.

Can you enlighten us with some citations of these best practices and use-cases?

[ROCLASI]

Hi David,

Many of the new db's in the past handful of years don't even have auto-increment integer PKs as an option.

Even MySQL (and yes, I see that as a lowest denominator in the RDBMS world) does support these auto-increment integer PK's.
And many of the 'new' (as in Not-Only-SQL) db's also don't support ACID. You make it sound that is always a good thing.
I disagree.
Again, it all depends on your use-case and the cost of the downside.

[sbutler]

Database identity should be your first option. Your database is responsible for data integrity, so let it make the PK's

Servoy Sequences are a good choice if you are making a SaaS product to distribute on various database platforms for which you may not have full say in how the backend is managed. In other words, I you don't control the DB, then let your app do the work.

UUID's are good if you want some kind of offline syncing. This should be your last choice as it comes with high performance costs. Integer PK's can fit into memory easier and the index cluster handles them better.

Back to your original reason for switching from Database sequences. Normally foundset.sort issues a db query, so unsaved records would get dropped. According to your example, you passing a custom function into foundset.sort, so I'd say you did find a bug. Usually Servoy is pretty good about fixing real bugs like that. So, if you can hold off a bit, I'd suggest continuing to use Database Identities, and wait until Servoy fixes the bug.

[david]

Many of the new db's in the past handful of years don't even have auto-increment integer PKs as an option.

Even MySQL (and yes, I see that as a lowest denominator in the RDBMS world) does support these auto-increment integer PK's.
And many of the 'new' (as in Not-Only-SQL) db's also don't support ACID. You make it sound that is always a good thing.
I disagree.
Again, it all depends on your use-case and the cost of the downside.

I give a bunch of reasons for why I think it should be the default choice for business applications. Any reasons for integer PKs? Throw 'em out for debate. Another one for UUIDs: sharing data amongst multi-developer teams.

UUID's are good if you want some kind of offline syncing. This should be your last choice as it comes with high performance costs. Integer PK's can fit into memory easier and the index cluster handles them better.

Now we're into an area that requires some thought. But apparently not black and white: http://inessential.com/vespersyncdiary. He ended up using UUIDs.

[Bernd.N]

A most interesting thread, and it reminds me on discussions between fellow students in a canteen if Atari is the best computer or not. Most believed that the computer they currently used was the best.
But back to topic. I just googled “uuid performance” and found an interesting article written 2007 from MySQL guru Peter Zaitsev, who made some performance testing where integers were much faster for a large table with 268 million rows (200 times performance difference):
http://www.mysqlperformanceblog.com/200 ... t-to-uuid/

Actually, there are tons of articles for "uuid performance", therefore I think we should not fighting tooth and nail about this theme that others discussed so much already.

Without being aware of this thread, I asked by chance today if I should use an integer at least for the most important tenant_id in each table, while using UUID as PK for all tables in general.

UUIDs are sexy but are very difficult to handle during debugging (integer pks easily show records creation order and are easy to spot when debugging)

Patrick Ruhsert told me that the record creation order can easily be done with the standard field creation_date, which most use anyway.

I agree to the advantage of integer PKs for debugging. I had cases where I just asked customers to give me the ID of a record that has a problem on phone. So identifying a record is much simpler. Therefore I am currently planning to use both a UUID as PK and a sequenced integer as user-friendly ID for communication about a specific record. I do not want to horrify my users by showing them a UUID.