Обсуждение: 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

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Thursday, April 6, 2017 4:18 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/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?
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

Programmatically it is easier as I can start saving from very bottom detail
table instead of having to save first the master table record. Users will
directly work on detail tables. Master table will be saved & updated by
software automatically as last piece of information.

On 04/06/2017 06:40 AM, Ertan Küçükoğlu wrote:
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Thursday, April 6, 2017 4:18 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/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?
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>
> Programmatically it is easier as I can start saving from very bottom detail
> table instead of having to save first the master table record. Users will
> directly work on detail tables. Master table will be saved & updated by
> software automatically as last piece of information.

Hmm. Well back to your original question, PK's on the detail serial
columns. Given you have a uuid column to mark uniqueness the PK on the
serial column is redundant. The issue is that database and software
above it will not 'know' that the uuid column is your PK stand in.
Putting a UNIQUE index or PK on the uuid column would help flag the
column as unique. Whether the index is used in a given query is
dependent on the scope of the query. At some point it is faster for
Postgres to just scan the table rather then incur the cost of a look up
on the index.



--
Adrian Klaver
adrian.klaver@aklaver.com