281–290 of 345 posts
Earlier quoted context omitted.
When running a batched migration it is important to batch using a strictly monotonic field so that new rows wont get inserted in already processed range
Strictly monotonic fields are quite expensive and the bigserial PK alone won't give you that.
Earlier quoted context omitted.
When running a batched migration it is important to batch using a strictly monotonic field so that new rows wont get inserted in already processed range
Okay, but in a live DB, typically you won't have only inserts while migrating, won't you?
Earlier quoted context omitted.
When running a batched migration it is important to batch using a strictly monotonic field so that new rows wont get inserted in already processed range
would creation/lastmod timestamps cover this requirement?
Earlier quoted context omitted.
Why “bigint generated always as identity” instead of bigserial, instead of Postgres' uuid data type? Postgres' UUID datatype: https://www.postgresql.org/docs/current/datatype-uuid.html#D... django.db.models.fields.UUIDField: https://docs.djangoproject.com/en/5.0/ref/models/fields/#uui... : > class UUIDField: A field for storing universally unique identifiers. Uses Python’s UUID class. When used on PostgreSQL and Mari…
Just noting, the commenter you replied to said: > use “bigint generated always as identity” instead of bigserial. The commenter you are replying to was not saying anything about whether to use UUIDs or not; they just said "if you are going to use bigserial, you should use bigint generated always as identity instead".
Why does OT compare text and UUID instead of char(32) and UUID?
What advantage would there be for database abstraction libraries like SQLalchemy and Django to implement the UUID type with bigint or bigserial instead of the native pg UUID type?
Earlier quoted context omitted.
When running a batched migration it is important to batch using a strictly monotonic field so that new rows wont get inserted in already processed range
It's not even necessarily it being strictly monotonic. That part does help though as you don't need to skip rows. For me the bigger thing is the randomness. A uid being random for a given row means the opposite is true; any given index entry points to a completely random heap entry. When backfilling this leads to massive write amplification. Consider a table with rows taking up 40 bytes, so roughly 200 entries per pa…
Earlier quoted context omitted.
Simplest way is to keep the identifiers from DB A and increment all the identifiers from DB B by an offset. Third parties complicates things of course but internally it can be pretty simple, so maybe they just didn't have too many third parties using the IDs.
That was it if I recall. They wrote a small script with the logic involved in the merging. PKs and FKs of only one database had to be incremented by an offset of max(table.pk) + safe margin. They did this for each table. Once this script was tested multiple times with subsets of each database, they stopped production and ran the script against it (with backup fallbacks). A small downtime window in a Sunday. And that…
Oh I see, we're talking about two entirely different worlds here, lol.
Earlier quoted context omitted.
You don't need to worry about a collision in a UUIDv4 that you created on your server. But I have seen a surprising number of applications that took a UUID generated client side and basically upserted it. Allowing taking over resources who's ID was known via the insert API (even if the update API has proper access control).
> UUID generated client side and basically upserted it Read and take notes. This is crazy in untrusted environments.
Earlier quoted context omitted.
> https://github.com/arp242/goatcounter/blob/master/db/schema .... - number of IDs is actually 7, not 6. the point is that some/many of those ids are not timezone and country, and potentially can grow high in cardinality.
No, my point is you accused me of lying at the drop of a hat for no good reason, and that this is demonstrably not true. Don't try and spinelessly slime your way out of that. And no, they I won't run out of IDs for them. "Potentially" anything can happen, but it won't happen here. After five year the furthest sequence is 0.86% on its way of being full.
I said you described fictional scenario, and it appears to be true, your table ID fields are very different than timezone, country and user_id.
Earlier quoted context omitted.
> I'm saving 48 bytes per row you saving 24 bytes per row: downsizing 6 columns from 8 bytes to 4, which is fraction of your table size. If your system is sensitive to such change, you likely should optimize something else. > Using bigint here would add absolutely nothing. I'm never going to have billions of users. I'm never going to have billions of different operating systems. I think you cherry picked some fiction…
> If your system is sensitive to such change, you likely should optimize something else. This isn’t even optimization, it’s just understanding your tools and needs. It’s akin to profiling your app under load, seeing that at worst it needs 1 GiB of RAM allocated, and then giving it 8 EiB just in case. By all means, if you can reasonably predict that a given table will near or pass 2^31 rows in the near future, just se…
all computers will be fine with 640kb of ram
Earlier quoted context omitted.
They are not theoretically guaranteed they are in practice though. 2^128 and 122 are big numbers. Even if you are producing a billion per second you have a 50% chance of not getting a collision for 100 years.
I've used 128 secure-random bits for ages, not caring for any of the UUID version nonsense. Per the birthday paradox, I need to have 2*64 entries in my tables to reach 50% collision probability, and it will be a while before I can afford that much storage anyhow.