Я удивлен, что это еще не опубликовано. Какие интересные трюки вы знаете в Postgres? Особенно приветствуются незаметные параметры конфигурации и масштабирование/перфекционные трюки.
Я уверен, что мы можем победить 9 комментариев в соответствующем потоке MySQL:)
Так как postgres намного более здравомыслящий, чем MySQL, не так много "трюков" для отчета; -)
В руководстве есть несколько приятных performance советов.
Несколько других вещей, связанных с производительностью, чтобы иметь в виду:
Вот несколько вещей, которые я нашел полезными, которые не связаны с конфигурацией или производительностью.
Чтобы узнать, что происходит в данный момент:
select * from pg_stat_activity;
Поиск различных функций:
select * from pg_proc WHERE proname ~* '^pg_.*'
Найти размер базы данных:
select pg_database_size('postgres');
select pg_size_pretty(pg_database_size('postgres'));
Найти размер всех баз данных:
select datname, pg_size_pretty(pg_database_size(datname)) as size
from pg_database;
Найти размер таблиц и индексов:
select pg_size_pretty(pg_relation_size('public.customer'));
Или, чтобы перечислить все таблицы и индексы (возможно, проще сделать это):
select schemaname, relname,
pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) as size
from (select schemaname, relname, 'table' as type
from pg_stat_user_tables
union all
select schemaname, relname, 'index' as type
from pg_stat_user_indexes) x;
О, и вы можете вложить транзакции, откатить частичные транзакции ++
test=# begin;
BEGIN
test=# select count(*) from customer where name='test';
count
-------
0
(1 row)
test=# insert into customer (name) values ('test');
INSERT 0 1
test=# savepoint foo;
SAVEPOINT
test=# update customer set name='john';
UPDATE 3
test=# rollback to savepoint foo;
ROLLBACK
test=# commit;
COMMIT
test=# select count(*) from customer where name='test';
count
-------
1
(1 row)
Самый простой трюк, позволяющий postgresql работать намного лучше (помимо настройки и использования правильных индексов, конечно), просто дать ему больше оперативной памяти для работы (если вы этого еще не сделали). В большинстве установок по умолчанию значение shared_buffers слишком низкое (на мой взгляд). Вы можете установить
shared_buffers
в postgresql.conf. Разделите это число на 128, чтобы получить приблизительное количество памяти (в МБ), которое может требовать postgres. Если вы его достаточно, это заставит postgresql летать. Не забудьте перезапустить postgresql.
В системах Linux, когда postgresql не запускается снова, вы, вероятно, достигли предела kernel.shmmax. Установите его выше с помощью
sysctl -w kernel.shmmax=xxxx
Чтобы сделать это упорство между загрузками, добавьте запись kernel.shmmax в /etc/sysctl.conf.
Здесь можно найти целую кучу трюков Postgresql.:
Postgres имеет очень мощное средство обработки данных в режиме реального времени благодаря поддержке INTERVAL.
Например:
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)
Вы можете использовать много строк для типа INTERVAL.
COPY
Я начну. Всякий раз, когда я переключаюсь на Postgres из SQLite, у меня обычно есть несколько действительно больших наборов данных. Ключ должен загружать ваши таблицы с COPY FROM, а не делать INSERTS. См. Документацию:
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, - это некоторые типы данных, поддерживаемые в столбцах. Например, существуют типы столбцов, предназначенные для хранения Сетевые адреса и Arrays. Соответствующие функции (Сетевые адреса/Массивы) для эти типы столбцов позволяют выполнять множество сложных операций внутри запросов, которые вам нужно будет сделать, обрабатывая результаты с помощью кода в MySQL или других механизмах баз данных.
Массивы действительно круты, когда вы узнаете их. Допустим, вы хотели бы сохранить некоторые гиперссылки между страницами. Вы можете начать думать о создании таблицы вроде этого:
CREATE TABLE hyper.links (
tail INT4,
head INT4
);
Если вам нужно индексировать столбец хвоста, и у вас было, скажем, 200 000 000 строк ссылок (например, википедия), вы оказались бы с огромной таблицей и огромным индексом.
Однако, используя PostgreSQL, вы можете использовать этот формат таблицы вместо:
CREATE TABLE hyper.links (
tail INT4,
head INT4[],
PRIMARY KEY(tail)
);
Чтобы получить все заголовки для ссылки, вы можете отправить такую команду (unsest() является стандартной с 8.4):
SELECT unnest(head) FROM hyper.links WHERE tail = $1;
Этот запрос является неожиданно быстрым, когда он сравнивается с первым параметром (unsest() выполняется быстро, а индекс намного меньше). Кроме того, ваша таблица и индекс будут занимать гораздо меньше RAM-памяти и HD-пространства, особенно когда ваши массивы настолько длинны, что их сжимают до таблицы Toast. Массивы действительно мощные.
Примечание: while unsest() будет генерировать строки из массива, 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 script, чтобы поддерживать данные в актуальном состоянии, или если вы ленивы:
TRUNCATE my_matview;
INSERT INTO my_matview SELECT * FROM my_view;
Inheritance..infact Multiple Inheritance (как в родительском-дочернем "наследовании", а не наследования отношения 1 к 1, которое реализуется многими веб-фреймами при работе с postgres).
PostGIS (пространственное расширение), замечательное дополнение, которое предлагает полный набор функций геометрии и хранения координат из коробки. Широко используется во многих открытых источниках с открытым исходным кодом (например, OpenLayers, MapServer, Mapnik и т.д.) И определенно лучше, чем пространственные расширения MySQL.
Процедуры написания на разных языках, например. C, Python, Perl и т.д. (Делает вашу жизнь проще, если вы разработчик, а не db-admin).
Также все процедуры могут храниться извне (как модули) и могут быть вызваны или импортированы во время выполнения указанными аргументами. Таким образом, вы можете легко управлять кодом и легко отлаживать код.
Огромный и всеобъемлющий каталог по всем объектам, реализованным в вашей базе данных (т.е. таблицы, ограничения, индексы и т.д.).
Мне всегда очень полезно запускать несколько запросов и получать всю метаинформацию, например., имена ограничений и поля, на которых они были реализованы, имена индексов и т.д.
Для меня все это очень удобно, когда мне приходится загружать новые данные или делать массовые обновления в больших таблицах (я бы автоматически отключил триггеры и индексы drop), а затем снова заново создаст их после завершения обработки. Кто-то отлично справился с написанием нескольких этих запросов.
Несколько схем в одной базе данных, вы можете использовать ее, если в вашей базе данных большое количество таблиц, вы можете думать о схемах как категории. Все таблицы (независимо от их схемы) имеют доступ ко всем другим таблицам и функциям, присутствующим в родительском db.
Вам не нужно научиться расшифровывать вывод "объяснять анализ", есть инструмент: 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
Это иногда быстрее, чем приобретение множества блокировок на уровне строк, и их можно использовать для работы в случаях, когда FOR UPDATE не реализуется (например, рекурсивные запросы CTE).
Это список избранных менее известных функций.
Почти каждый оператор 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
);
Расширение 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 (с ctid):
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.) Получить команду DDL.
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 state: 42804 Подсказка: укажите выражение USING, чтобы выполнить Преобразование
Это означает, что мы не можем просто изменить тип данных, потому что данные уже есть в столбце. Так как данные имеют тип "характер, отличающийся от postgres can not, ожидайте его как целого, хотя мы ввели только целые числа. Итак, теперь, как предложили 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.conf. Часто вам может потребоваться изменить эти параметры. Но для того, чтобы некоторые параметры вступили в силу, нам часто нужно перезагрузить файл конфигурации. Конечно, перезапуск сервера это сделает. Но в производственной среде не рекомендуется перезапускать базу данных, которая используется тысячами, только для установки некоторых параметров. В таких ситуациях мы можем перезагрузить конфигурационные файлы без перезагрузки сервера, используя следующую функцию:
select pg_reload_conf();
Помните, что это не будет работать для всех параметров, некоторый параметр изменения требуют полного перезапуска сервера, который будет действовать.
9.) Получение пути каталога данных к текущему кластеру базы данных
Возможно, что в системе несколько экземпляров (кластер) PostgreSQL настроены, как правило, в разных портах или около того. В таких случаях поиск того, какой каталог (каталог физического хранилища) используется, каким экземпляром представляет собой суматошную задачу. В таких случаях мы можем использовать следующую команду в любой базе данных в интересующем нас кластере, чтобы получить путь к каталогу:
SHOW data_directory;
Та же самая функция может быть использована для изменения каталога данных кластера, но для перезагрузки сервера требуется:
SET data_directory to new_directory_path;
10.) Найти CHAR - DATE или не
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;
Использование: следующее возвращает True
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 DEBUGGER
Хорошо объяснено здесь
Удобно переименовать старую базу данных, а не mysql. Просто используя следующую команду:
ALTER DATABASE name RENAME TO new_name