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.
-----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