On Wed, 5 Apr 2017 18:04:39 +0300, Ertan Küçüko?lu <ertan.kucukoglu@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
