One of the coolest things about Postgres is how easy it is to make your <i>own</i> datatypes[1]. At previous jobs we've had custom types and aggregates that allowed super fact set operations with comparatively small data storage requirements.<p>[1]: <a href="http://www.postgresql.org/docs/9.3/static/sql-createtype.html" rel="nofollow">http://www.postgresql.org/docs/9.3/static/sql-createtype.htm...</a>
If your ORM doesn't let you take advantage of these goodies, maybe it's time you went past ORM? Java's JOOq library is stellar at this kind of raw SQL access stuff, while still being expressive and well-typed:<p><a href="http://www.jooq.org/" rel="nofollow">http://www.jooq.org/</a>
Hstore, and the supplied functions for converting to/from records has been very useful for making a "one size fits all" history table.<p>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.<p>In the absence of those datatypes, there'd be a lot more work required to achieve either of these.
The ltree module is really awesome. If you have to do any hierarchical stuff I feel it's much better than nested set implementations. Great performance with gist indexes and high level operators for querying.
The ip4/ip4r data type is fantastic as well if you just need to store an ip address.<p>(<a href="https://github.com/RhodiumToad/ip4r-historical/blob/master/README.ip4r" rel="nofollow">https://github.com/RhodiumToad/ip4r-historical/blob/master/R...</a>)
Why aren't they getting a lot of use? Is it because they're new or ORMs don't have them yet? (Note: This might sound like a false dichotomy but these are the only 2 reasons I know so feel free to add more reasons.)
I keep seeing the uuid idea for pkeys, but have yet to see them used by any big site. Last I looked, twitter, facebook are using bigints all over the place. Also, instagram went bigint and blogged about it here <a href="http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram" rel="nofollow">http://instagram-engineering.tumblr.com/post/10853187575/sha...</a><p>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.
the only bit I'd prefer clarification on is:<p><i>Timestamp with Timezone</i><p><i>There’s seldom a case you shouldn’t be using these.</i><p>For recording most event times that's true but:<p>Birthdates (which can include times) shouldn't use them.<p>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?
Kind of confused here: I was under the impression that timestamp with timezone only did the converting to-and-from your system timezone, but still always stored in UTC. Is that incorrect?
I wish arrays and hstore-like types were available in sqlite (not through plugins, but standard-wise).<p>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.
The array type is actually one of the most interesting, I'd love to see the performance implications of those specifically covered in more detail.
I think the money type is awesome! Under the hood it uses int (I believe int32 even on 64bit) so it takes 50% less space than decimal.<p>So if your only dealing with US currency, why not love the money type?
Yes, amazing. About to work on stats caching and that Ranges datatype will be suuuper useful for using ranges of timestamps when caching a stat every week, month, etc.