From: Rory Campbell-Lange <rory@campbell-lange.net>
To: pgsql-performance@postgresql.org
Subject: Strategy for materialisation and centralisation of data
Date: Fri, 22 May 2020 15:48:20 +0100
Message-ID: <20200522144820.GA21655@campbell-lange.net> (raw)
Apologies for the cross-post to the general list.
I'm keen to know if there are any good reasons apart from disk space and
possible replication connection overhead to avoid the strategy proposed
below.
We have quite a few databases of type a and many of type b in a cluster.
Both a and b types are fairly complex and are different solutions to a
similar problem domain. All the databases are very read-centric, and all
database interaction is currently through plpgsql with no materialised
data.
Some organisations have several type a and many type b databases, and
need to query these in a homogeneous manner. We presently do this with
many middleware requests or pl/proxy. An a or b type database belongs to
0 or 1 organisations.
Making a and b generally the same would be a very big project.
Consequently I'm discussing materialising a subset of data in a common
format between the two database types and shipping that data to
organisation databases. This would have the benefit of providing a
common data interface and speeding up queries for all database types.
Users would have faster queries, and it would be a big time saver for
our development team, who presently have to deal with three quite
different data APIs.
Presently I've been thinking of using triggers or materialized views in
each database to materialise data into a "matview" schema which is then
shipped via logical replication to an organisation database when
required. New columns in the matview schema tables would ensure replica
identity uniqueness and allow the data to be safely stored in common
tables in the organisation database.
A few issues I foresee with this approach include:
* requiring two to three times current storage for materialisation
(the cluster is currently ~250GB)
* having to have many logical replication slots
(we sometimes suffer from pl/proxy connection storms)
Commentary gratefully received,
Rory
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rory@campbell-lange.net
Subject: Re: Strategy for materialisation and centralisation of data
In-Reply-To: <20200522144820.GA21655@campbell-lange.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox