Rarely Used Postgres Datatypes
craigkerstiens.com
craigkerstiens.com
[1]: http://www.postgresql.org/docs/9.3/static/sql-createtype.htm...
Want to store an order id create a OrderId domain. Want to store weight create a gram domain. Want to store quantity create a quantity domain.
That way it becomes much more easy to spot logical errors and maintain data consistency.
Also one can use it to create something like an auto ORM.
[A domain Is like a user defined child type of the system types that can have some extra restrictions.]
And integer is a hole number. Nothing else can be inferred from knowing that something is a integer.
"If you have to ask, you can't afford it."
With jOOQ's transparent licensing strategy there are no strings attached.
Prosgres == Commercial & Open Source [1] TRUE
http://www.postgresql.org/docs/9.3/static/datatype-geometric...
Currently I'm working with numeric range types to model order quantities and stock quantities, and forecasting which order is going to consume what stock by joining on rows with overlapping ranges, then taking the intersections of those overlaps. Again, Postgres supplies functions and operators for range overlaps, intersections, etc.
In the absence of those datatypes, there'd be a lot more work required to achieve either of these.
(https://github.com/RhodiumToad/ip4r-historical/blob/master/R...)
I really couldn't say how much time and money he saved me, but it was a lot.
[1] http://pgfoundry.org/frs/download.php/3383/ip4r-extension-2....
I suspect that for a lot of developers, the only database features they use are the ones their ORM of choice supports. The popular ORMs out there tend to be database-agnostic, which means they'll only natively support the lowest-common-denominator database features.
Also, I tried the uuid extension once... It is not well supported, had to make a small change to a c header file to get it to compile on ubuntu, for dev on os x I think I gave up.
I've used UUID-as-pkey and had it work well.
UUID has no guaranteed properties you can rely on in high volumes.
The only desirable property it has is that it's an easy way out in tutorials.
The better solution is simply longer to explain (have a unique name for each server, an incremental generation number every time the server is restarted, and an incremental number on top of that for the runtime of a given generation - way Erlang does it).
"only after generating 1 billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be about 50%."
http://en.wikipedia.org/wiki/Universally_unique_identifier#R...
Now, if you want to mark your product SKU with an UUID you have an outstanding chance it'll be unique amongst other UUID SKUs in the scope of every shop selling your SKUs.
But people have been captured by the idea UUID is truly "universally unique", so you can use it for everything, at any volume.
Not at all. Let's say that we're implementing a distributed Actor system (or a similar message passing architecture) at the scale of Google. We'll be using UUID to tag every message to guarantee its identity and various messaging properties (like deliver just once etc.), because UUID is unique. While actor systems can reach millions of messages per server per second, here we'll use a humble 100k messages per second.
- They have over 2 million servers (2,000,000)
- Each of which generates 100k messages per second.
You reach a point of 50% collision rate (for every new UUID) in 3 years:
log2 (2,000,000 * 100,000 * 3600 * 24 * 365 * 3)= ~64
Now you might say, I have a lot of variables pegged to worst case scenario. Sure I have. But this is about 50 damn percent chance of collision on every new UUID generated at that point.
Things will become bad long before 50% collision chance if you use UUID.
That's incorrect. If you go back to the birthday paradox, what you say would mean that, if you have 23 people in a room, and a 24th walks in, there is a 50% chance that that new person shares their birthday with one of those 23. That clearly is incorrect; that probability is at most 23/365 < 0.10.
Also, I don't see how your formula proves it. You will go through 64 of the 128 bits of key space in 3 years, but that means you only got through 2^-64 part of the key space, so each next UUID has a chance of about 2^-64 of a collision.
It's the sheer number of lottery draws with ever increasing probability of a loss that introduces the birthday paradox, not the last lottery.
Yet, even once corrected, 50% chance for collision in a set of 2^64 UUID (which can occur way earlier than 2^64) is way too much for me to go to sleep at night, knowing there are more efficient, smaller (often twice smaller, ex. 64bit PK instead of 128bit PK), guaranteed collision-free ways for producing PKs.
And there's another problem with UUID collision rates. Many versions of the UUID rely on PRNG, and PRNG quality varies wildly from system to system.
A defect in the PRNG (it has happened) can start producing UUID with orders of magnitude higher collisions than the model offers. It's a problem that's better not to have.
This may bring issues in untrusted contexts, but it doesn't involve global (or indeed any) synchronisation outside of each machine, and you may define your 'machine' for UUID purposes to be any size you like -- one per core may work, or one per application, or per application thread even.
That's a pretty big if there, especially considering how unreliable timers are, and the fact they can jump back and forth.
- Wall clock timers will jump forward or back and repeat time after NTP adjustment, or anyone else who adjusts the clock.
- Internal "elapsed time" timers, based on say the CPU clock may jump around or repeat when the source core changes on multicore machines, or you have processes sourcing their CPU clock from the core they're bound on.
- A node may be moved from machine to machine (and their clocks are not necessarily perfectly synched).
This is a hard lesson that distributed system designers learn over and over again: don't rely on random and don't rely on timers for identity. Good old incrementing counters have none of those issues and are absolutely trivial to implement. They never fail, never drift, never repeat, by definition. As I said a few posts above, the typical structure of a "unique id" based on a counter has three counters:
1. nodename (or machine name if you will)
2. namespace (you can have one per thread to avoid contention issues)
3. local (monotonically increases within the namespace).
256.0-4.* (where * is increasing monotonically 0, 1, 2, 3, 4...)
And once you have to reboot the node, you obtain five new namespaces:
256.5-9.*
And the * counters start over from zero on each. As for how big should each segment be, that depends on what you want to do with the nodes I suppose. But the 128-bit UUID size should never be some kind of guide regarding size; maybe you need three shorts, maybe you need three longs, maybe short.medium.long etc., it'll depend on the use case.
I guess most of the messages created in such a system will be emphemeral...
Timestamp with Timezone
There’s seldom a case you shouldn’t be using these.
For recording most event times that's true but:
Birthdates (which can include times) shouldn't use them.
Calendaring should think very carefully too, should the 9am meeting move to 10am due to DST? or should it "stay put" but potentially move for other timezones?
I don't understand why you don't want to use timezone with birthdates(with time). Can you explain this, please?
Just for instance, older Iraqis (if I remember correctly) tend to be part of a culture where their individual birthdates were never recorded, and each person was considered 1 year older on some September date (maybe even talking the Islamic calendar there too). Should you store that date in your database, pretending that it means the same thing as an American who knows they were born at 7:08pm EST on July 17th, 1977?
And that's just one example of many.
I think I learned about this one because intelligence officials were freaking out when they eventually noticed all these people sharing a "birthdate", and it made the news.
Data about human beings is highly un-normalizable, as evidenced by everyone trying to stuff anthroponyms into the American "first name, middle name, last name" paradigm.
The postgresql manpage says it loud: don't fucking use timestamp with timezone. It is bounded to political decision that makes it unusable compared to an UTC TS. Plus good practices says: store UTC present in local
Does any one Read The Fantastics Manual these days?
That's very different than timestamp with time zone, which you should be using unless you have a very good reason to not.
I've used something else from my experiments in PostgreSQL - the TXID - this way I was able to track down changes in the database (by keeping previous TXID and some other various bits, and then by polling again (or making the server call me)) - polling again and only instructing to get the rows that have changed since my last TXID.
http://slashdot.org/story/06/11/09/1534204/slashdot-posting-...
Slashdot got over 16,777,216 comments overflowing a MySQL mediumint they were using for an index.
http://www.postgresql.org/docs/9.3/static/routine-vacuuming....
Fortunately the PostgreSQL has autovacuum so you probably don't need to worry about this.
So if your only dealing with US currency, why not love the money type?
http://www.postgresql.org/message-id/b42b73150805270629h309f...
I use it a lot, and yes, it is missing some casts but you can cast to decimal and go from there:
select '42.00'::money::decimal;
I've also used stripe, and maybe it's a javascript thing, but they represent all their money in cents. Personally, I'd rather use the money type, it is easy for me to reason about.
For a "datatype" as commonly needed as "money", it has always struck me as strange that a more standardized and widely agreed upon approach to dealing with them, have not emerged.
As the currency of money is not known it is pretty obvious that one needs to store that in another column.