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