Понимание оболочек сторонних данных в Postgres и Postgres_fdw

Пониманиеоболочекстороннихданныхвpostgresиpostgresfdw

Идея написания запроса к базе данных, который затем отправляется во внешний источник , может не возникнуть. кому-то, кто не был администратором баз данных на раннем этапе. То есть: вместо того, чтобы выяснять, как захватить, а затем загрузить несколько наборов данных в одно и то же хранилище, или настроить серверную часть приложения для подключения к кучке разнородных источников, почему бы не использовать запросы JOIN, как обычно, для таблиц в одной базе данных?

Если вы не знакомы, модуль dblink в PostgreSQL, наряду с концепцией ссылок на базы данных или связанных серверов в других СУБД, существует уже некоторое время. Оболочки сторонних данных новее, они были введены в PG 9+. Postgres теперь имеет множество доступных оболочек сторонних данных , и они работают с множеством различных типов источников: базами данных NoSQL, платформы, такие как Twitter и Facebook, форматы геопространственных данных и т. д. Мой коллега Крейг Керстиенс поделился своими мыслями на Postgres это база данных с “включенными батареями”, и так легко понять почему.

Тем не менее, почему вам все еще могут понадобиться обёртки сторонних данных между серверами Postgres? Реализация по умолчанию не поддерживает запросы между базами данных даже на одном сервере Postgres. Таким образом, вам по-прежнему нужна оболочка для обработки соединения и получения сторонних данных. postgres_fdw является более или менее эквивалентом dblink для доступа между серверами Postgres, с основным отличием в том, что postgres_fdw соответствует стандартам SQL. Они действительно предоставляют большую часть той же функциональности, но postgres_fdw более рекомендуется и более широко используется на данном этапе.

основы postgres_fdw

Если вы еще не пробовали postgres_fdw, настроить его довольно просто. Скажем, у меня есть таблица контактов в локальной установке Postgres, и я хочу иметь возможность запрашивать удаленный Хрустящий мост база данных, в которой хранится информация о продажах . Я могу сделать следующее:

  1. Загрузите расширение в мою локальную базу данных (postgres_fdw включен в contrib Postgres, и вам нужны привилегии CREATE в локальной базе данных):
      СОЗДАТЬ РАСШИРЕНИЕ postgres_fdw;  
  2. Создайте сторонний сервер:
      СОЗДАТЬ СЕРВЕР salesinfo_bridge ПАРАМЕТРЫ ИНОСТРАННЫХ ДАННЫХ postgres_fdw OPTIONS (host 'p.2gdmzr2pcbadzcstrkuolxvtpq.db.postgresbridge .com ', dbname' sales ');  
  3. Настройте сопоставление пользователей для аутентификации:
      СОЗДАТЬ КАРТУ ПОЛЬЗОВАТЕЛЯ ДЛЯ ПАРАМЕТРОВ СЕРВЕРА postgres salesinfo_bridge (пользователь 'fdw_user', пароль 'пароль');   
  4. Затем я могу настроить сторонние таблицы, соответствующие таблицам, которые я хочу запросить на внешнем сервере. Это делается двумя способами:
    • Запустите CREATE FOREIGN TABLE, который очень похож на CREATE TABLE в том, что вы должны определить имена столбцов, типы данных, ограничения и т. Д.
      1. Запустите IMPORT FOREIGN SCHEMA, который импортирует таблицы и представления из схемы и создает внешние таблицы, соответствующие определения для внешних таблиц. У вас даже есть возможность включать / исключать только определенные таблицы, что делает его еще более удобным:
          test = # IMPORT FOREIGN SCHEMA public LIMIT TO (payment_methods, accounts) FROM SERVER salesinfo_bridge INTO public;  

И я может продолжать запросы, как если бы все эти таблицы находились в одной базе данных!

  test = # ВЫБРАТЬ c.id, pm.type, acct.balance ИЗ контактов c LEFT JOIN account acct ON c.id = acct.contact_id LEFT ПРИСОЕДИНЯЙТЕСЬ к способам оплаты pm ON acct.id = pm.acct_id ГДЕ acct.balance> 0;  id |  тип |  баланс ---- + ------------ + ---------- 1 |  mastercard |  
. 62 2 | mastercard | 2742. 76 3 | mastercard | 464. 67

Хотя я воссоздал внешнюю схему на локальном сервер, эти таблицы фактически не хранят данные локально. Это означает, что локальный сервер ничего не знает о статистике с внешнего сервера (мы немного рассмотрим последствия позже).

Не забывайте о psql команды, которые предоставляют информацию, относящуюся к оболочкам сторонних данных:

  •   des  - список сторонних серверов 
  •   deu  - список использует сопоставления 
  •   det  - список сторонних таблиц 
  • dtE - список как локальных, так и внешних таблиц
  • d - показать столбцы, данные a и другие метаданные таблицы

Некоторые моменты, которые следует учитывать при оптимизации запросов

postgres_fdw попытается оптимизировать запросы самостоятельно. При этом локальный сервер не собирает автоматически статистику с внешнего сервера или таблиц. Это имеет смысл, но также может несколько затруднить планирование и оптимизацию запросов. Как пользователь, вы можете рассмотреть следующие подходы (первые два описаны в официальной документации postgres_fdw ):

  1. Сообщите оболочке сторонних данных что вы хотите, чтобы сторонний сервер выполнил оценку стоимости, установив для параметра use_remote_estimate значение true на уровне сервера или таблицы.

    Итак, каждый раз, когда вы запрашиваете внешний сервер, вы просите его выполнить дополнительные ОБЪЯСНЯТЬ команды. Фактически вы каждый раз запрашиваете больше по сети, что может увеличить общее время для вашего запроса, чтобы вернуть результаты, в зависимости от того, насколько сложен запрос. Если оставить значение по умолчанию false , локальный сервер выполнит оценку стоимости сам.
  2. Запустите ANALYZE для сторонних таблиц, который обновляет эти таблицы статистика на локальном сервере. Но если внешние таблицы обновляются довольно часто, местная статистика также может быстро устареть. Таким образом, вам также необходимо учитывать, как часто вам, возможно, придется планировать АНАЛИЗ.
  3. Используйте материализованное представление внешней таблицы, которое вы также можете обновлять по желанию. Вы будете работать с локальным снимком внешних данных, что должно помочь в увеличении скорости. Это может хорошо работать, когда вам не нужно всегда использовать актуальные, самые свежие данные.
  4. Оболочки сторонних данных как альтернатива ETL?

    С моей точки зрения, не являющейся администратором баз данных, главный вывод состоит в том, что внешние оболочки данных могут упростить запросы и анализ данных, когда вам нужны данные из разрозненных источников. И самый большой недостаток может заключаться в том, что во многих случаях вы не можете «установить и забыть», если не хотите рисковать низкой производительностью запроса. С учетом сказанного, если вы все равно имеете дело с огромными объемами данных, могут быть более подходящие подходы. Но в любом случае приятно, что есть и другие варианты, помимо стандартного шаблона ETL / ELT.

    Для тех из вас, кто использует обёртки сторонних данных, каковы были ваши самые важные соображения? Мне также любопытно услышать о других вариантах использования FDW. Мы все уши в @ crunchydata .