Re: Some indexing advice for a Postgres newbie, please?

On 25/02/15 15:42, Merlin Moncure wrote:
> On Sat, Feb 21, 2015 at 5:33 AM, Arjen Nienhuis <a(dot)g(dot)nienhuis(at)gmail(dot)com> wrote:
>> Hi,
>>
>> On 19 Feb 2015 17:12, "brian" <brian(at)meadows(dot)pair(dot)com> wrote:
>>>
>>>
>>> Hi folks,
>>>
>>> I have a single-user application which is growing beyond the
>>> fixed-format data files in which it currently holds its data, I need a
>>> proper database as the backend. The front end is written using Lazarus
>>> and FreePascal under Linux, should anyone feel that makes a
>>> difference. The database will need to grow to around 250,000 records.
>>>
>>> My problem is with the data field which is the (unique) key. It's
>>> really a single 192-bit integer (it holds various bits of bitmapped
>>> data) which I currently hold as six 32-bit integers, but can convert
>>> if needed when transferring the data.
>>>
>>> How would you advise that I hold this field in a Postgres database,
>>> given the requirement for the whole thing to be a unique key? The
>>> first 64 bits change relatively infrequently, the last 128 bits will
>>> change with virtually every record. The last 128 bits will ALMOST be
>>> unique in themselves, but not quite. :(
>>>
>>> Thanks,
>>>
>>> Brian.
>>>
>>
>> Postgres can use almost anything as a key so it probably depends on the
>> library you use to access the database.
>>
>> If it supports "composite primary keys" you can use the 6 ints as a key:
>>
>> PRIMARY KEY (n1, n2, n3, n4, n5, n6)
>>
>> The numeric type can hold 192-bit numbers. I think Lazarus supports this as
>> well.
>>
>> You could also use a surrogate key and define a UNIQUE constraint on the 6
>> ints or the 192-bit number.
>
> You could also use 3 64 bit bigints if that's easier. The other way
> to do it is bytea.

The other way to do it is to have semantically-meaningful columns
rather than glomming them into this 192-bit integer, and a composite
key on the lot - if the set truly is unique.
--
Jeremy