Скрытые возможности PostgreSQL [закрыто]

на простой трюк, чтобы postgresql работал намного лучше (помимо настройки и использования правильных индексов, конечно), - это просто дать ему больше оперативной памяти для работы (если вы этого еще не сделали). В большинстве установок по умолчанию значение shared_buffers это слишком низко (на мой взгляд). Вы можете установить

shared_buffers

в postgresql.конф. Разделите это число на 128, чтобы получить приблизительный объем памяти (в МБ) postgres может претендовать. Если вы достаточно этого сделаете, это заставит postgresql летать. Не забудьте перезапустить postgresql.

в системах Linux, когда postgresql не запустится снова, вы, вероятно, попали в ядро.лимит shmmax. Установите его выше с

sysctl -w kernel.shmmax=xxxx

чтобы это сохранялось между загрузками, добавьте ядро.запись shmmax в /etc / sysctl.конф.

можно найти целую кучу трюков Postgresql здесь:

• http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks

Postgres имеет очень мощное средство обработки данных datetime благодаря своей поддержке интервала.

например:

select NOW(), NOW() + '1 hour';
              now              |           ?column?            
-------------------------------+-------------------------------
 2009-04-18 01:37:49.116614+00 | 2009-04-18 02:37:49.116614+00
(1 row)



select current_date ,(current_date +  interval '1 year')::date;
    date             |  date            
---------------------+----------------
 2014-10-17          | 2015-10-17
(1 row)

вы можете привести много строк к типу интервала.

скопировать

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

http://www.postgresql.org/docs/8.1/static/sql-copy.html

в следующем примере таблица копируется клиенту с помощью вертикальной полосы ( | ) в качестве разделителя полей:

COPY country TO STDOUT WITH DELIMITER '|';

для копирования данных из файла в таблицу стран:

COPY country FROM '/usr1/proj/bray/sql/country_data';

см. здесь: более быстрые массовые вставки в sqlite3?

• мой безусловно любимый generate_series: наконец-то чистый способ создания манекена множеств.

• возможность использовать коррелированное значение в LIMIT предложение подзапроса:

  SELECT  (
          SELECT  exp_word
          FROM    mytable
          OFFSET id
          LIMIT 1
          )
  FROM    othertable
  

• возможность использовать несколько параметров в пользовательских агрегатах (не охваченных документацией): см. статья в моем блоге для примера.

одна из вещей, которые мне действительно нравятся в Postgres, - это некоторые типы данных, поддерживаемые в Столбцах. Например, существуют типы столбцов, предназначенные для хранения Сетевые Адреса и массивы. Соответствующие функции (Сетевые Адреса/массивы) для этих типов столбцов позволяют выполнять множество сложных операций внутри запросов, которые вам придется выполнять, обрабатывая результаты с помощью кода в MySQL или других ядрах баз данных.

массивы действительно здорово, как только вы узнаете их. Допустим, вы хотите сохранить некоторые гиперссылки между страницами. Вы можете начать с мысли о создании таблицы вроде этого:

CREATE TABLE hyper.links (
     tail INT4,
     head INT4
);

Если вам нужно было индексировать хвост столбец, и у вас было, скажем, 200 000 000 ссылок-строк (например, Википедия даст вам), вы окажетесь с огромной таблицей и огромным индексом.

однако, с PostgreSQL, вы можете использовать этот формат таблицы вместо этого:

CREATE TABLE hyper.links (
     tail INT4,
     head INT4[],
     PRIMARY KEY(tail)
);

чтобы получить все головы для ссылки, вы можете отправить такую команду (unnest() является стандартным с 8.4):

SELECT unnest(head) FROM hyper.links WHERE tail = ;

этот запрос удивительно быстр, когда он сравнивается с первым вариантом (unnest() быстро, а индекс намного меньше). Кроме того, ваша таблица и индекс будут занимать гораздо меньше оперативной памяти и HD-пространства, особенно когда ваши массивы настолько длинны, что они сжимаются в таблицу тостов. Массивы действительно есть мощный.

Примечание: В то время как unnest() будет генерировать строки из массива, array_agg() будет агрегировать строки в массив.

материализованные представления довольно просты в настройке:

CREATE VIEW my_view AS SELECT id, AVG(my_col) FROM my_table GROUP BY id;
CREATE TABLE my_matview AS SELECT * FROM my_view;

это создает новую таблицу, my_matview, со столбцами и значениями my_view. Триггеры или сценарий cron могут быть настроены для поддержания данных в актуальном состоянии, или если вы ленивы:

TRUNCATE my_matview;
INSERT INTO my_matview SELECT * FROM my_view;

• наследование..infact множественное наследование (как в родительско-дочернем "наследовании", а не наследование отношения 1 к 1, которое многие веб-фреймворки реализуют при работе с postgres).

• PostGIS (пространственное расширение), замечательное дополнение, которое предлагает полный набор функций геометрии и хранения координат из коробки. Широко используется во многих гео-библиотеках с открытым исходным кодом (например,OpenLayers,MapServer, Mapnik и т. д.) и определенно лучше, чем пространственный MySQL увеличение.

• написание процедур на разных языках, например,C, Python, Perl и т. д. (делает вашу жизнь проще для кода, если вы разработчик, а не администратор БД).

  кроме того, все процедуры могут храниться внешне (как модули) и могут быть вызваны или импортированы во время выполнения с помощью указанных аргументов. Таким образом, вы можете легко управлять исходным кодом и отлаживать код.

• огромный и всесторонний каталог на всех объектах снабженных в вашем базы данных (т. е. таблицы,ограничения,индексы и т. д.).

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

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

  http://www.alberton.info/postgresql_meta_info.html

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

вам не нужно учиться расшифровывать вывод" объяснить анализ", есть инструмент:http://explain.depesz.com

select pg_size_pretty(200 * 1024)

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

база данных может быть скопирована с помощью:

createdb-T old_db new_db

в документации сказано, что:

Это не (пока) предназначено как средство общего назначения "копировать базу данных"

но это хорошо работает для меня и намного быстрее, чем

createdb new_db

pg_dump old_db / psql new_db

память для хранения выброшенных данных / глобальных переменных

вы можете создать табличное пространство, которое живет в ОЗУ, и таблицы (возможно, незарегистрированные, в 9.1) в этом табличном пространстве для хранения выбрасываемых данных/глобальных переменных, которые вы хотели бы разделить между сеансами.

http://magazine.redhat.com/2007/12/12/tip-from-an-rhce-memory-storage-on-postgresql/

консультативный замки

они описаны в неясная область руководства:

http://www.postgresql.org/docs/9.0/interactive/functions-admin.html

это иногда быстрее, чем получение множества блокировок на уровне строк, и их можно использовать для обхода случаев, когда обновление не реализовано (например, рекурсивные запросы CTE).

это мой любимый список менее известных функций.

Транзакционный DDL

почти каждый оператор SQL является транзакционным в Postgres. Если вы отключите автоматический возможны следующие:

drop table customer_orders;
rollback;
select *
from customer_orders;

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

насколько мне известно, Postgres-это единственная СУБД, которая позволяет создавать ограничение, которое проверяет, перекрываются ли два диапазона. Примером может служить таблица, содержащая цены на продукты с "допустимым от" и "действителен до" даты:

create table product_price
(
   price_id      serial        not null primary key,
   product_id    integer       not null references products,
   price         numeric(16,4) not null,
   valid_during  daterange not null
);

функции NoSQL

The hstore расширение предлагает гибкое и очень быстрое хранилище ключей/значений, которое можно использовать, когда части базы данных должны быть "без схемы". JSON-это еще один вариант хранения данных без схемы и

insert into product_price 
  (product_id, price, valid_during)
values 
  (1, 100.0, '[2013-01-01,2014-01-01)'),
  (1,  90.0, '[2014-01-01,)');


-- querying is simply and can use an index on the valid_during column
select price
from product_price
where product_id = 42
  and valid_during @> date '2014-10-17';

план выполнения для вышеперечисленного на таблице с 700.000 строк:

Index Scan using check_price_range on public.product_price  (cost=0.29..3.29 rows=1 width=6) (actual time=0.605..0.728 rows=1 loops=1)
  Output: price
  Index Cond: ((product_price.valid_during @> '2014-10-17'::date) AND (product_price.product_id = 42))
  Buffers: shared hit=17
Total runtime: 0.772 ms

чтобы избежать вставки строк с перекрывающимися диапазонами действия простой (и эффективный) уникальный ограничение может быть определено:

alter table product_price
  add constraint check_price_range 
  exclude using gist (product_id with =, valid_during with &&)

бесконечность

вместо того, чтобы требовать "реальную" дату далеко в будущем, Postgres может сравнивать даты до бесконечности. Например, если вы не используете диапазон дат, вы можете сделать следующее

insert into product_price 
  (product_id, price, valid_from, valid_until)
values 
  (1,  90.0, date '2014-01-01', date 'infinity');

записи общие табличные выражения

вы можете удалить, вставить и выбрать в одном заявлении:

with old_orders as (
   delete from orders
   where order_date < current_date - interval '10' year
   returning *
), archived_rows as (
   insert into archived_orders 
   select * 
   from old_orders
   returning *
)
select *
from archived_rows;

выше будет удалить все заказы старше 10 лет, переместить их в archived_orders таблица, а затем отображение строк, которые были перемещены.

1.) Когда вам нужно добавить уведомление для запроса, вы можете использовать вложенные комментарии

SELECT /* my comments, that I would to see in PostgreSQL log */
       a, b, c
   FROM mytab;

2.) Удалить конечные пробелы из всех text и varchar поле в базе данных.

do $$
declare
    selectrow record;
begin
for selectrow in
select 
       'UPDATE '||c.table_name||' SET '||c.COLUMN_NAME||'=TRIM('||c.COLUMN_NAME||')  WHERE '||c.COLUMN_NAME||' ILIKE ''% '' ' as script
from (
       select 
          table_name,COLUMN_NAME
       from 
          INFORMATION_SCHEMA.COLUMNS 
       where 
          table_name LIKE 'tbl%'  and (data_type='text' or data_type='character varying' )
     ) c
loop
execute selectrow.script;
end loop;
end;
$$;

3.) Мы можем использовать функцию окна для очень эффективного удаления повторяющихся строк:

DELETE FROM tab 
  WHERE id IN (SELECT id 
                  FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), id 
                           FROM tab) x 
                 WHERE x.row_number > 1);

некоторые оптимизированные версии PostgreSQL (с КРТИ):

DELETE FROM tab 
  WHERE ctid = ANY(ARRAY(SELECT ctid 
                  FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), ctid 
                           FROM tab) x 
                 WHERE x.row_number > 1));

4.) Когда нам нужно определить состояние сервера, то мы можем использовать функцию:

SELECT pg_is_in_recovery();

5.) Сделать команду функции ЯОД.

select pg_get_functiondef((select oid from pg_proc where proname = 'f1'));

6.) Безопасное изменение типа данных столбца в PostgreSQL

create table test(id varchar );
insert into test values('1');
insert into test values('11');
insert into test values('12');

select * from test
--Result--
id
character varying
--------------------------
1
11
12

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

ALTER TABLE test ALTER COLUMN id TYPE integer;

но он возвращает:

ошибка: столбец "id" не может быть автоматически приведен к типу integer SQL состояние: 42804 подсказка: укажите выражение USING для выполнения преобразования

это значит, что мы не можем просто изменить тип данных, потому что данные уже есть в столбец. Поскольку данные имеют тип "изменение характера", postgres не может ожидать его как целое число, хотя мы ввели только целые числа. Итак, теперь, как предложил postgres, мы можем использовать выражение "USING" для приведения наших данных в целые числа.

ALTER TABLE test ALTER COLUMN id  TYPE integer USING (id ::integer);

Это Работает.

7.) Знайте, кто подключен к базе данных
Это более или менее команда мониторинга. Чтобы узнать, какой пользователь подключен к какой базе данных включая их IP и порт следующий SQL:

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;

8.) Перезагрузка файлов конфигурации PostgreSQL без перезагрузки сервера

параметры конфигурации PostgreSQL находятся в специальных файлах, таких как postgresql.conf и pg_hba.конф. Часто может потребоваться изменить эти параметры. Но для того, чтобы некоторые параметры вступили в силу, нам часто нужно перезагрузить файл конфигурации. Конечно, перезапуск сервера сделает это. Но в производственной среде это не так предпочтительнее перезапустить базу данных, которая используется тысячами, просто для установки некоторых параметров. В таких ситуациях мы можем перезагрузить файлы конфигурации без перезагрузки сервера, используя следующую функцию:

select pg_reload_conf();

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

9.) Получение пути к каталогу данных текущего Кластер баз данных

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

SHOW data_directory;

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

SET data_directory to new_directory_path;

10.) Найти символ-это дата или нет

create or replace function is_date(s varchar) returns boolean as $$
begin
  perform s::date;
  return true;
exception when others then
  return false;
end;
$$ language plpgsql;

использование: следующее возвратит правда

select is_date('12-12-2014')
select is_date('12/12/2014')
select is_date('20141212')
select is_date('2014.12.12')
select is_date('2014,12,12')

11.) Изменить владельца в PostgreSQL

REASSIGN OWNED BY sa  TO postgres;

12.) ОТЛАДЧИК PGADMIN PLPGSQL

хорошо объяснил здесь

это удобно переименовать старую базу данных, а не mysql может сделать. Просто используя следующую команду:

ALTER DATABASE name RENAME TO new_name