Home

Особенности работы в PostgreSQL

PostgreSQL   Добряки  

PostgreSQL

Меня, как аналитика, интересует пользовательские возможности, а не особенности администрирования PostgreSQL. В целом меня устраивает данная СУБД. Если сравнивать с MySQL, то работать в PostgreSQL более удобно. Могу с уверенностью порекомендовать вам ее для построения аналитических систем.

Удобства

  1. Работа с рекурсией. (ссылка на урок)
  2. Поддержка CTE. (ссылка на урок)
  3. Поддержка schema. (При работе с БД можно создать схему test и все на ней проверить, и не надо создавать полную копию БД для работы). Этого понятия очень сильно не хватает в Mysql.
  4. Большое количество утилит для администрирования БД. Рекомендую использовать pgAdmin с возможностью графического построения запросов. Реально экономит время. Один минус - при объединении приходится самостоятельно дописывать объединение по left или rigth.
  5. Кроссплатформенность. Прекрасно работает на Linux серверах.
  6. Наличие встроенных типов, которые не поддерживает SQL стандарт.
  7. Группировка по именным полям. К сожалению, не все БД представляют такую возможность.
  8. Поддержка оконных функций. (ссылка на урок). Еще с помощью оконных функций можно сделать ABC анализ на SQL (ссылка на урок)

Неудобства

  1. Строгое выполнение GROUP BY. Нельзя выполнять запросы типа таких:  
    Select iddoc /*номер документа*/, the_date, min(the_date) as mindate
From table
Group by iddoc
Having the_date = mindate

    Запрос должен найти номера документов с минимальной датой. Такие запросы решаются с помощью оконных функций. 

  2. Не поддерживает использование вычисляемых полей в запросе  
    Select a as val1, b as val2, val1+val2 as sum_a_b
From table;

    Это просто печалит, так как приходится очень долго тестировать вычисляемые поля. При вычислении разного типа величин увеличивает время написания правильного запроса.

Особенности

Создание полей счетчиков

CREATE SEQUENCE public.dim_time_id_dim_time_seq;
CREATE TABLE public.dim_time (
            	id_dim_time BIGINT NOT NULL DEFAULT nextval('public.dim_time_id_dim_time_seq'),
            	CONSTRAINT id_dim_time_pk PRIMARY KEY (id_dim_time)
);
ALTER SEQUENCE public.dim_time_id_dim_time_seq OWNED BY public.dim_time.id_dim_time;

Как вариант, можно использовать тип SERIAL, однако он менее предпочтителен, так как при откате транзакций, выпадут значения из множества ключей.

Работа с первичным ключом (PRIMARY KEY)

При создании первичного ключа создает для него индекс автоматически.

Работа с view

Не очень порадовали view. Работает может быть чуть быстрее чем SQL запрос. Ожидал большего. В 9.3 ввели понятие Materialized Views (https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3). Пока нет времени протестировать производительность.

Блок администрирования

Остановка, перезапуск (linux)

service postgresql stop
service postgresql star
service postgresql restar

Настройка удаленного соединения

Редактирование файла /etc/postgresql/[version postgresql]/main/postgresql.conf

listen_addresses = '*' #разрешить принимать сообщения со всех IP

Добавление строки в файл /etc/postgresql/[version postgresql]/main/pg_hba.conf

host all all  0.0.0.0/0 md5 #разрешить соединение с любым хостом с любой БД любому пользователю

или можно так через ssl:

hostssl  all  all  0.0.0.0/0  md5

Работа с пользователями

Создание пользователя:

CREATE USER test WITH PASSWORD 'test'

 Назначение ему полных прав для БД:

GRANT ALL PRIVILEGES ON DATABASE demo_db to test;

 Дальнейшее, можно прочитать по ссылкам.

Ссылки на материалы:

  1. Записки программиста http://eax.me/postgresql-install/
  2. Заметки под рукой http://knbase.org/8
  3. PostgreSQL 9.2 Начало! http://habrahabr.ru/post/168601/
  4. PostgreSQL создание удаление пользователей http://www.fullautomatic.ru/index.php/postgresql/178-3
  5. Unique Indexes http://www.postgresql.org/docs/current/static/indexes-unique.html
  6. PostgreSQL (ссылка)
  7. Postgres and Indexes on Foreign Keys and Primary Keys http://stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys
  8. PostgreSQL. Добавление нового пользователя, создание базы данных и назначение привилегий. http://h-qub.ru/blogs/?p=116
  9. Практика построения WH (видео)
Подробности
Опубликовано: 20 Январь 2014
Просмотров: 5880
   
   
© BIWED.RU