Re: [GENERAL] Advise on primary key for detail tables (OS: RaspberryPi)

On 04/06/2017 03:27 AM, Ertan Küçükoğlu wrote:
> Sorry for top posting.
>
> I have a serial in master table because I need to know data insertion order.
> DateTime on Raspberry Pi is not accurate due to power loss and lack of
> internet access to fetch correct time from.

Understand, though it does beg the question, why have the uuid column?

>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Wednesday, April 5, 2017 7:17 PM
> To: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>;
> pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Advise on primary key for detail tables (OS:
> Raspberry Pi)
>
> On 04/05/2017 08:04 AM, Ertan Küçükoğlu wrote:
>> Hello,
>>
>> I have a project which will be mainly built on Raspberry Pi and some
>> parts on Windows.
>>
>> I must have a PostgreSQL 9.4.10 running on Raspberry Pi and another
>> PostgreSQL running on Windows. Though, there is still a possibility
>> that Windows database server will be something else that is not known to
> me, yet.
>> Since Raspberry Pi is running on a SD Card, data saved on Raspberry Pi
>> will be copied over to Windows database system for a proper backup &
>> disaster recovery.
>>
>> I need to keep database server overhead as low as possible on
>> Raspberry Pi system. That is because software that will be a running
>> is going to do some time essential sensor communication.
>>
>> I am about to start table designs on Raspberry Pi. There is one
>> master-detail-detail-detail structure I should implement. Master
>> having serial, uuid and some varchar fields. Uuid field being primary
>> key. Details have serial, uuid and some smallint fields.
>
> So what the serial column in the master table for?
>
>>
>> I recall that it is "generally" advised to have a primary key on any
>> table used on a database server.
>>
>
> What is advised is to have some way of determining uniqueness for a row.
> A PK is the simplest way of doing that, also many ORMs will not work without
> one. Now a PK can be a single value such as the serial column in your
> details tables or it can be over multiple columns that determine uniqueness.
> Again you have to be aware of what the application/interface that is using
> the tables is capable of. In the case of ORMs, they often do not understand
> multi--column PKs. This is why PKs on a
> auto-incrementing(serial) integer are often recommended.
>
>> My question is: Is reading performance will be faster, if I remove
>> primary key on serial fields of detail tables and use a regular index
>> put on master table link fields only? In another words, is it
>> advisable *not* to have a primary key on PostgreSQL table?
>>
>> If answer changes according to OS underlying, I appreciate replies
>> indicates so.
>>
>> Thanks & regards,
>> Ertan Küçükoğlu
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com