Thread: Which is the setup with lowest resources you know Postgres is used in?

Hi all,

I'm regularly reading that Postgres is often used with containers and
in cloud environments these days, even on some not too powerful NAS.

What are the lowest resource setups you know of or even host Postgres
successfully with yourself? It's especially about RAM and CPU, if you
needed to e.g. configure anything special to make things somewhat work
in your stripped down environment etc.

Is there any point at which one is most likely forced to switch to
more specialized embedded databases like SQLite? E.g. because
Postgres requires a higher amount of resources because of it's
architecture? Or could Postgres in theory be used everywhere where
SQLite is used as well, as long as one is allowed to e.g. start an
additional process?

I would like to know if there's any realistic chance to use Postgres
in a low resources environment with little amount of RAM and somewhat
slow CPU like the following:

http://ww1.microchip.com/downloads/en/DeviceDoc/ATSAMA5D27-WLSOM1-Datasheet-60001590b.pdf
http://ww1.microchip.com/downloads/en/DeviceDoc/SAMA5D2-Series-Data-sheet-ds60001476F.pdf

One point is that I most likely need somewhat concurrent access to the
data, because of having web services exposing that data to clients,
daemons storing data locally only etc. OTOH, the number of concurrent
accessed won't be too high, there won't be too much load most of the
time. Things heavily depend on actual users of the device. Postgres'
architecture seems to better fit that use case than e.g. SQLite.

Thanks for sharing you experiences and suggestions!

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: Thorsten.Schoening@AM-SoFT.de
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

ср, 7 окт. 2020 г. в 10:51, Thorsten Schöning <tschoening@am-soft.de>:
>
> Hi all,
>
> I'm regularly reading that Postgres is often used with containers and
> in cloud environments these days, even on some not too powerful NAS.
>
> What are the lowest resource setups you know of or even host Postgres
> successfully with yourself? It's especially about RAM and CPU, if you
> needed to e.g. configure anything special to make things somewhat work
> in your stripped down environment etc.
AFAIK the default configuration of Postgres is fairly conservative and
may be a good starting point for such cases.

>
> Is there any point at which one is most likely forced to switch to
> more specialized embedded databases like SQLite? E.g. because
> Postgres requires a higher amount of resources because of it's
> architecture? Or could Postgres in theory be used everywhere where
> SQLite is used as well, as long as one is allowed to e.g. start an
> additional process?
For example, when you need to INSERT tens of thousands rows per second
on your low-cost device SQLite is a choice. Postgres is a
client-server with related overheads. Postgres requires deployment and
configuration while SQLite just works with zero-configuration (which
is a big advantage in case of IoT).
Sure, in theory Postgres can be used instead of SQLite (and vice-versa).

>
> I would like to know if there's any realistic chance to use Postgres
> in a low resources environment with little amount of RAM and somewhat
> slow CPU like the following:
>
> http://ww1.microchip.com/downloads/en/DeviceDoc/ATSAMA5D27-WLSOM1-Datasheet-60001590b.pdf
> http://ww1.microchip.com/downloads/en/DeviceDoc/SAMA5D2-Series-Data-sheet-ds60001476F.pdf
>
> One point is that I most likely need somewhat concurrent access to the
> data, because of having web services exposing that data to clients,
> daemons storing data locally only etc. OTOH, the number of concurrent
> accessed won't be too high, there won't be too much load most of the
> time. Things heavily depend on actual users of the device. Postgres'
> architecture seems to better fit that use case than e.g. SQLite.
In many cases concurrency is not a problem and in fact SQLite may
handle concurrent requests faster than Postgres. Since SQLite is
server-less and access overhead is near to zero (compared to Postgres)
each writer does its work quickly and no lock lasts for more than a
few dozen milliseconds.
On the other hand, Postgres is better in cases of really high concurrency.

On Wed, Oct 07, 2020 at 01:53:44PM +0300, Dmitry Igrishin <dmitigr@gmail.com> wrote:

> In many cases concurrency is not a problem and in fact SQLite may
> handle concurrent requests faster than Postgres. Since SQLite is
> server-less and access overhead is near to zero (compared to Postgres)
> each writer does its work quickly and no lock lasts for more than a
> few dozen milliseconds.
> On the other hand, Postgres is better in cases of really high concurrency.

Presumably, this is no longer a problem, but many years
ago (between 14 and 10 years ago) I was using sqlite
for a low traffic website (probably no more than 40
users at a time), and the database became corrupted so
often that I had had to automate rebuilding it from the
latest backup and my own sql logs. I was very silly.
Switching to postgres was the real solution.

cheers,
raf

On Thu, Oct 08, 2020 at 01:14:02AM +0300, Dmitry Igrishin <dmitigr@gmail.com> wrote:

> чт, 8 окт. 2020 г. в 00:14, raf <raf@raf.org>:
> >
> > On Wed, Oct 07, 2020 at 01:53:44PM +0300, Dmitry Igrishin <dmitigr@gmail.com> wrote:
> >
> > > In many cases concurrency is not a problem and in fact SQLite may
> > > handle concurrent requests faster than Postgres. Since SQLite is
> > > server-less and access overhead is near to zero (compared to Postgres)
> > > each writer does its work quickly and no lock lasts for more than a
> > > few dozen milliseconds.
> > > On the other hand, Postgres is better in cases of really high concurrency.
> >
> > Presumably, this is no longer a problem, but many years
> > ago (between 14 and 10 years ago) I was using sqlite
> > for a low traffic website (probably no more than 40
> > users at a time), and the database became corrupted so
> > often that I had had to automate rebuilding it from the
> > latest backup and my own sql logs. I was very silly.
> > Switching to postgres was the real solution.
>
> As for now SQLite is a very robust solution if used properly.

That's great to hear.

чт, 8 окт. 2020 г. в 00:14, raf <raf@raf.org>:
>
> On Wed, Oct 07, 2020 at 01:53:44PM +0300, Dmitry Igrishin <dmitigr@gmail.com> wrote:
>
> > In many cases concurrency is not a problem and in fact SQLite may
> > handle concurrent requests faster than Postgres. Since SQLite is
> > server-less and access overhead is near to zero (compared to Postgres)
> > each writer does its work quickly and no lock lasts for more than a
> > few dozen milliseconds.
> > On the other hand, Postgres is better in cases of really high concurrency.
>
> Presumably, this is no longer a problem, but many years
> ago (between 14 and 10 years ago) I was using sqlite
> for a low traffic website (probably no more than 40
> users at a time), and the database became corrupted so
> often that I had had to automate rebuilding it from the
> latest backup and my own sql logs. I was very silly.
> Switching to postgres was the real solution.
As for now SQLite is a very robust solution if used properly.