Re: Advise on primary key for detail tables (OS: Raspberry Pi)

On Wed, 5 Apr 2017 18:04:39 +0300, Ertan Küçüko?lu
<ertan.kucukoglu@1nar.com.tr> wrote:

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

Umm ... in my opinion, Postgresql is not a good choice for an embedded
database, and particularly for a low power platform like Pi.

Postgresql is a heavyweight DBMS: it uses process based parallelism
[not threads], its memory requirements are higher than some competing
platforms, and it requires (at least periodic) administration to keep
it running well.

You should examine whether you *really* need a DBMS at all or only
need some kind of structured storage.  Then consider whether you
really need a *server* based solution, or if you really only need
shared files.

If you want to stay with SQL for a common access language, then see if
SQLlite, MySQL Embedded, or Firebird Embedded will do the job.

If you only need structured storage and are willing to give up SQL,
then there are many other choices available.


>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.
>
>I recall that it is "generally" advised to have a primary key on any table
>used on a database server.

You don't need a "primary" key column per se ... but for almost all
purposes you *DO* need a unique key - which may be a combination of
columns - that can distinguish individual rows in the table.


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

In Postgresql, there is nothing special about a "primary" key index.
And having one won't necessarily improve speed - Postgresql decides
whether or not to even use an index based on many factors such as the
size of the table, whether the index is applicable to the query, how
many rows are expected to be returned [or changed], how much memory is
available, etc.


>If answer changes according to OS underlying, I appreciate replies indicates
>so.

Windows is somewhat less efficient than Unix/Linux at sharing library
code (DLLs) between/among processes.  Postgresql starts several admin
processes to begin with, and then starts a new process for each client
connection.


>Thanks & regards,
>Ertan Küçüko?lu

George