[Закрываются] скрытые функции PostgreSQL

• My by far favorite is generate_series: at last a clean way to generate dummy rowsets.

• Ability to use a correlated value in a LIMIT clause of a subquery:

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

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

COPY

I'll start. Whenever I switch to Postgres from SQLite, I usually have some really big datasets. The key is to load your tables with COPY FROM rather than doing INSERTS. See documentation:

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

The following example copies a table to the client using the vertical bar (|) as the field delimiter:

COPY country TO STDOUT WITH DELIMITER '|';

To copy data from a file into the country table:

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

See also here: Faster bulk inserts in sqlite3?

The easiest trick to let postgresql perform a lot better (apart from setting and using proper indexes of course) is just to give it more RAM to work with (if you have not done so already). On most default installations the value for shared_buffers is way too low (in my opinion). You can set

shared_buffers

in postgresql.conf. Divide this number by 128 to get an approximation of the amount of memory (in MB) postgres can claim. If you up it enough this will make postgresql fly. Don't forget to restart postgresql.

On Linux systems, when postgresql won't start again you will probably have hit the kernel.shmmax limit. Set it higher with

sysctl -w kernel.shmmax=xxxx

To make this persist between boots, add a kernel.shmmax entry to /etc/sysctl.conf.

A whole bunch of Postgresql tricks can be found here:

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

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

select pg_size_pretty(200 * 1024)

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

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)

You может привести много строк к типу INTERVAL.

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

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;

Perl DBI с использованием DBD:: AnyData

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

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 = $1;

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

Примечание. Если функция unnest () создает строки из массива, функция array_agg () агрегирует строки в массив.

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

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

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

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

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

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

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

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

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