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@xxxxxxxxxxx]
Sent: Wednesday, April 5, 2017 7:17 PM
To: Ertan Küçükoğlu <ertan.kucukoglu@xxxxxxxxxxx>;
pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
