Re: Advices on custom data type and extension development

On Mon, Jan 18, 2016 at 9:36 PM, Luciano Coutinho Barcellos
<luciano(at)geocontrol(dot)com(dot)br> wrote:

> * a lot of data being generated every day, which are mainly queried
> by an immutable column of type date or timestamp;
> * as a standard, almost every table has a bigserial id column as a
> primary key;
> * data is huge enough to demand table partitioning, which is
> implemented as suggested in Postgres documentation, by using triggers and
> table inheritance. A function called by cron deal with creation of new
> partitions.
>
> What I would like to develop first is a custom type (let's call it
> datedserial) for replacing bigserial as the primary key:
>
> * the type would be 8 bytes long, being 4 dedicated to storing the
> Date, and 4 dedicated to storing a serial within that day;

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.

If you can't overcome the limitations of the "standard" or your
development framework any other way, you plan sounds like the next
best thing.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company