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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Apr 6, 2017 at 4:27 AM, Ertan Küçükoğlu
<ertan.kucukoglu@1nar.com.tr> 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.

Note that serial / sequences do NOT guarantee exact order of
insertion. It's quite possible to have a few switched here and there.
It gets you an approximate order of insertion but a few values could
get swapped around depending on timing of your application and how you
do inserts. Just an FYI.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general