Re: Advices on custom data type and extension development - Mailing list pgsql-hackers

From Corey Huinker
Subject Re: Advices on custom data type and extension development
Date
Msg-id CADkLM=e=nVVkDoWRgq-d6SJGw2Qj5_b-cPthyWRhCG5jOJynbA@mail.gmail.com
Whole thread Raw
In response to Re: Advices on custom data type and extension development  (Kevin Grittner <kgrittn@gmail.com>)
List pgsql-hackers
Seriously, you should consider having a primary key with two
columns, of type date and int.  It would take exactly the same
space as your current plan, and performance should be very close to
what you propose.  As long as you aren't using some ORM that is too
dumb to deal with this, it should be far easier than creating the
custom type.

+1 

Most ORMs cannot handle ENUMs, let alone user defined composite types.

That, or they flood the database with SELECT * FROM pg_type WHERE ... queries. I'm looking at you, Cake.

You're far better off trying a (date,integer) key as Kevin said.

If the ORM doesn't allow that, I'd suggest a custom function that encodes the date bit-shifted to the high 4 bytes, and then adds in the four bytes from a cycling sequence. At least then you've got a shot at partitioning, though the lower/upper bounds of the partitions would not make sense to the casual observer.

pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: PATCH: postpone building buckets to the end of Hash (in HashJoin)
Next
From: Alvaro Herrera
Date:
Subject: Re: Advices on custom data type and extension development