Как правильно использовать View в PostgreSQL?

Question

[hbrmdc]

Я знаю, как создавать View и как получать данные оттуда. Вопрос вот в чем: эти viewe -
1. это что-то вроде интерпретатора SQL команд? Когда я делаю запрос типа `SELECT * FROM mycustomview` мой запрос просто является оберткой для того запроса, который я написал при создании этого View? В таком случае, это только удобство, но не performance.
2. или View - это реальные таблицы, сформированные из всех данных, которые получены из того запроса, что я использовал при создании View? В таком случае это сильно влияет не только на удобство но и на performance.

Если View работает как интерпритатор (1), есть ли какие-то общепринятые подходы для того, чтобы делать то, что я описал под цифрой 2?
В последних версиях Postgres появляется все больше фишек для работы с json. Есть ли смысл создавать triggers или rules для того, чтобы данные из определенных таблиц складывались в новую специальную таблицу, чтобы избежать необходимости делать сложные запросы для получения этих данных?

Пример:

CREATE TABLE "public"."user" (
    "email" varchar(36) NOT NULL COLLATE "default",
    "password" varchar(16) NOT NULL COLLATE "default",
    "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
    CONSTRAINT "User_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."user" OWNER TO "postgres";
CREATE UNIQUE INDEX  "users_id_key" ON "public"."user" USING btree("id" ASC NULLS LAST);


CREATE TABLE "public"."friend" (
    "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
    "name" varchar(36) NOT NULL COLLATE "default",
    CONSTRAINT "ability_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE,
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."friend" OWNER TO "postgres";
CREATE UNIQUE INDEX  "ability_id_key" ON "public"."friend" USING btree("id" ASC NULLS LAST);


CREATE TABLE "public"."user_friend" (
    "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
    "owner" uuid NOT NULL,
    "friend" uuid NOT NULL,
    CONSTRAINT "ability_relation_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE,
    CONSTRAINT "owner" FOREIGN KEY ("owner") REFERENCES "public"."user" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
    CONSTRAINT "friend" FOREIGN KEY ("friend") REFERENCES "public"."friend" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."user_friend" OWNER TO "postgres";

Например, мне необходимо постоянно делать запросы вроде такого:

select row_to_json(t)
from (
    select public.user.email, (
        select array_to_json(array_agg(row_to_json(ability_relations)))
      from (
        select * 
        from public.friend
        where public.friend.id in (
            select public.user_friend.friend
            from public.user_friend
            where public.user_friend.owner=public.user.id
          )     
      ) ability_relations
    ) as abilities
    from public.user
) t

Это не самый сложный запрос, дочерних таблиц привязанных с помощью Foreign Key может быть до 4 штук. следовательно, запрос будет в 4 раза тяжелее.
Запросы на получение этих данных в сотни и тысячи раз чаще чем запросы на изменение этих данных. Имеет ли смысл с помощью triggers или rules создавать специальные таблицы, которые будут наполняться собранными в json объекты данными?

Благодарю за уделенное время!

Answer 1

[Melkij]

Есть View. Планировщику придёт что-то похожее на изначальный запрос и дальше он будет переписывать запрос. Т.е. что-то подобное пункту 1.

Есть Materialized Views. Это пункт 2. www.postgresql.org/docs/9.4/static/rules-materiali...

Comments

[hbrmdc]

Materialized Views не обновляются самостоятельно и судя по приведенному там примеру, комманда REFRESH полностью переделывает указанный Materialized Views вместо того, чтобы обновить только измененные данные. Следовательно данные там будут свежими только после очень тяжелого обновления, следовательно это не то, что мне нужно. Я хочу чтобы при обновлении одного единственного объекта в одной единственной таблице происходило обновление одной единственной строки в той самой сформированной таблице.

[Melkij]

Да, рефрешить надо самому когда это нужно, да, это полное перестроение вьюхи.

Описанную вами реакцию можно получить триггерами и рядовой таблицей. Обычный подход аггрегации.