Database federation

Querying several databases as one: remote server, user mapping, foreign table, what runs remotely or locally, two-phase commit, federating or copying into a warehouse.

27 cards
7 topics
Free

Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

Concepts
Questions
Sort and link
The numbers
On the diagram
Scenarios
Understand

A few cards from the deck

Federated database: what is it?
Show the answer
A set of independent databases queried through a single interface as though they were one, without moving their data.
The data stays with its owner and is always up to date, but every query depends on the network and on the availability of every database it touches.
In this EXPLAIN VERBOSE plan, where is the pays = 'FR' filter applied?

```text
Foreign Scan on public.clients_distants (cost=100.00..128.45 rows=7 width=68)
Output: id, nom, pays
Remote SQL: SELECT id, nom, pays FROM public.clients WHERE ((pays = 'FR'::text))
`
- Remotely: the Remote SQL line contains the WHERE
- Locally, after the whole table is brought back, like any filter on a foreign table
- On both sides: the remote database filters, then the local database filters every row received again
- Nowhere: the plan shows no Filter node, so the condition is not applied
Show the answer
Remotely: the Remote SQL line contains the WHERE
Remote SQL is the query actually sent to the other database. It contains the WHERE, so only French rows travel. A filter applied locally would show on a Filter: line.
The … part of the SQL standard describes access to external data through foreign tables.
Show the answer
The SQL/MED part of the SQL standard describes access to external data through foreign tables.
Its name stands for management of external data. PostgreSQL takes up its commands (CREATE SERVER, CREATE FOREIGN TABLE) with modules such as postgres_fdw; Oracle meets the same need with its database links, a syntax of its own outside this standard.

Study this deck