Re: 1 machine + master DB with postgres_fdw + multiple DB instances on different ports

Gezeala M. Bacuño II wrote:
> Does anybody have a similar setup:
>
> [a] 1 physical machine with half a terabyte of RAM, Xeon E7- 8837  @ 2.67GHz, huge ZFS pools + ZIL +
> L2ARC
> [b] master DB pg9.3 postgres_fdw with read/write capabilities, with tablespaces and WAL on separate
> zpools, archiving enabled (for zfs snapshots purposes), +17K tables, multi-TB in size and growing
> [c] multiple DB instances listening on different ports or sockets on the same machine with [b]
> (looking at 2 DB instances as of now which may increase later on)
>
>
> On the master DB there are several schemas with foreign tables located on any of the [c] DB instance.
> postgres_fdw foreign server definitions and all table sequence are on the master DB. Basically, I'm
> looking at any benefits in terms of decreasing the master DB scaling, size, separate shared_buffers
> and separate writer processes per instance (to utilize more CPU?). I'm also planning on relocating
> seldom accessed tables on [c] DBs. Am I on the right path on utilizing foreign data wrappers this way?

You are very likely not going to gain anything that way.

Access to foreign tables is slower than access to local tables, and (particularly when joins are
involved) you will end up unnecessarily sending lots of data around between the databases.
So I'd expect performance to suffer.

In addition, all the database clusters will have to share the memory, so I don't see an
improvement over having everything in one database.

Since the size will stay the same, you are not going to save anything on backups either.

Depending on the workload and how you distribute the tables, it might be a win to
distribute a large database across several physical machines.

I would test any such setup for performance.

Yours,
Laurenz Albe