Postgresql — что это за программа? свободная объектно-реляционная система управления базами данных (субд) postgresql

Выполнение запросов с помощью psql

Для запуска SQL-запросов используют SQL Server Management Studio, также называемую SSMS или SqlWb. Если пользователь предпочитает интерфейс GUI для выполнения запросов, нужно попробовать pgAdmin III, расположенный в меню «Пуск-> Программы» в разделе PostgreSQL 8.3 на машинах Windows. После запуска PostgreSQL можно запустить psql, открыв оболочку PostgreSQL и набрав > psql my_database, где my_database имя базы данных.

Если опускается имя базы данных, psql по умолчанию, то получают доступ к базе данных с тем же именем, что и имя пользователя CSE. Когда psql откроется, появится сообщение следующего вида.

Строка michaelr=# представляет собой приглашение для операторов SQL, которые отправляются на сервер базы данных, или команды, отличные от SQL, интерпретируемые psql. Здесь michaelr — это имя базы данных, оно может отличаться в системе

Как следует из сообщения, пользователь выходит из psql, набрав \q и нажав Enter, при этом нужно обратить внимание на отсутствие точки с запятой, это необходимо, потому что \q — это не оператор SQL

Microsoft Access

for TT in $(mdb-tables file.mdb); do
     mdb-export -Q -d '\t' -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.tsv"
done

for TT in $(mdb-tables file.mdb); do
     mdb-export -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.csv"
done

If the tablenames have embedded spaces…

mdb-tables -1 file.mdb| while read TT
do
     mdb-export -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.csv"
done

A shell script that may be useful for converting entire databases:

#!/bin/sh -e

mdbfn=$1
schemafn=$2
fkfn=$3
datafn=$4
schema=$5

tf=$(tempfile)

pre=""
 && pre="\"${schema}\"."

mdb-schema "${mdbfn}" postgres > "${tf}"

# Schema file
echo "BEGIN;\n" > "${schemafn}"

sp=""
 && echo "CREATE SCHEMA \"${schema}\";\n" >> "${schemafn}"
 && sp="SET search_path = \"${schema}\", pg_catalog;\n" 

echo ${sp} >> "${schemafn}"

awk '($0 !~ /^ALTER TABLE.*FOREIGN KEY.*REFERENCES/) {print;}' "${tf}" >> "${schemafn}"

echo "\nEND;" >> "${schemafn}"

# Foreign keys file
echo "BEGIN;\n" > "${fkfn}"
echo ${sp} >> "${fkfn}"

awk '($0 ~ /^ALTER TABLE.*FOREIGN KEY.*REFERENCES/) {print;}' "${tf}" >> "${fkfn}"

echo "\nEND;" >> "${fkfn}"

# Data file
echo "BEGIN;\n" > "${datafn}"
echo "SET CONSTRAINTS ALL DEFERRED;\n" >> "${datafn}"

mdb-tables -1 "${mdbfn}" | while read TT
do
    mdb-export -Q -d '\t' -D '%Y-%m-%d %H:%M:%S' "${mdbfn}" "$TT" > "${tf}"

    awk -v pre="${pre}" -v TT="${TT}" \
	'(NR==1) {gsub(/\t/,"\",\""); print "COPY " pre "\"" TT "\"(\"" $0 "\") FROM stdin;";}' "${tf}" >> "${datafn}"
    awk '(NR>1) {gsub(/\t\t/,"\t\\N\t"); gsub(/\t$/,"\t\\N"); gsub(/\t\t/,"\t\\N\t"); print;}' "${tf}" >> "${datafn}"

    echo "\\.\n" >> "${datafn}"
done

echo "END;" >> "${datafn}"

rm -f "${tf}"

If this script is saved to the file access2psql.sh and made executable, then it would be used as follows:

access2psql.sh file.mdb schema.sql foreignkeys.sql data.sql pg_schema_name
psql -f schema.sql pg_db_name
psql -f data.sql pg_db_name
psql -f foreignkeys.sql pg_db_name

This script won’t work properly if there are tab characters in text columns, though the call to mdb-export could be modified to export INSERT statements to fix this. Also, mdb-schema has trouble representing multi-column foreign keys, so foreignkeys.sql may need some manual editing.

Microsoft Access to PostgreSQL Conversion by Jon Hutchings (2001-07-20)

Compatibility

This command conforms to the SQL standard, except that the FROM and RETURNING
clauses are PostgreSQL
extensions, as is the ability to use WITH with UPDATE.

According to the standard, the column-list syntax should allow
a list of columns to be assigned from a single row-valued
expression, such as a sub-select:

UPDATE accounts SET (contact_last_name, contact_first_name) =
    (SELECT last_name, first_name FROM salesmen
     WHERE salesmen.id = accounts.sales_id);

This is not currently implemented — the source must be a list
of independent expressions.

Some other database systems offer a FROM option in which the target table is supposed
to be listed again within FROM. That is
not how PostgreSQL interprets
FROM. Be careful when porting
applications that use this extension.

Производительность

Базы данных должны обязательно быть оптимизированы для окружения, в котором вы будете работать. Исторически так сложилось что MySQL ориентировалась на максимальную производительность, а Postgresql разрабатывалась как база данных с большим количеством настроек и максимально соответствующую стандарту. Но со временем Postgresql получил много улучшений и оптимизаций.

MySQL

В большинстве случаев для организации работы с базой данных в MySQL используется таблица InnoDB, эта таблица представляет из себя B-дерево с индексами. Индексы позволяют очень быстро получить данные из диска, и для этого будет нужно меньше дисковых операций. Но сканирование дерева требует нахождения двух индексов, а это уже медленно. Все это значит что MySQL будет быстрее Postgresql только при использовании первичного ключа.

Postgresql

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

В целом PostgreSQL работает быстрее, за исключениям использования первичных ключей. Давайте рассмотрим несколько тестов с различными операциями:

What is PostgreSQL?

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform.

PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions. PostgreSQL runs on all major operating systems, has been ACID-compliant since 2001, and has powerful add-ons such as the popular PostGIS geospatial database extender. It is no surprise that PostgreSQL has become the open source relational database of choice for many people and organisations.

Getting started with using PostgreSQL has never been easier — pick a project you want to build, and let PostgreSQL safely and robustly store your data.

Notes

See Chapter 11 for information
about when indexes can be used, when they are not used, and in
which particular situations they can be useful.

Caution

Hash index operations are not presently WAL-logged, so
hash indexes might need to be rebuilt with REINDEX after a database crash if there
were unwritten changes. Also, changes to hash indexes are
not replicated over streaming or file-based replication
after the initial base backup, so they give wrong answers
to queries that subsequently use them. For these reasons,
hash index use is presently discouraged.

Currently, only the B-tree, GiST and GIN index methods support
multicolumn indexes. Up to 32 fields can be specified by default.
(This limit can be altered when building PostgreSQL.) Only B-tree currently supports
unique indexes.

An operator class can be specified
for each column of an index. The operator class identifies the
operators to be used by the index for that column. For example, a
B-tree index on four-byte integers would use the int4_ops class; this operator class includes
comparison functions for four-byte integers. In practice the
default operator class for the column’s data type is usually
sufficient. The main point of having operator classes is that for
some data types, there could be more than one meaningful
ordering. For example, we might want to sort a complex-number
data type either by absolute value or by real part. We could do
this by defining two operator classes for the data type and then
selecting the proper class when making an index. More information
about operator classes is in Section 11.9 and in Section 35.14.

For index methods that support ordered scans (currently, only
B-tree), the optional clauses ASC,
DESC, NULLS FIRST, and/or NULLS LAST can be
specified to modify the sort ordering of the index. Since an
ordered index can be scanned either forward or backward, it is
not normally useful to create a single-column DESC index — that sort ordering is already
available with a regular index. The value of these options is
that multicolumn indexes can be created that match the sort
ordering requested by a mixed-ordering query, such as SELECT ... ORDER BY x ASC, y DESC. The NULLS options are useful if you need to support
«nulls sort low» behavior, rather than
the default «nulls sort high», in
queries that depend on indexes to avoid sorting steps.

For most index methods, the speed of creating an index is
dependent on the setting of .
Larger values will reduce the time needed for index creation, so
long as you don’t make it larger than the amount of memory really
available, which would drive the machine into swapping.

Use DROP INDEX to remove an
index.

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

Как гласит документация, для полнотекстового поиска требуется использовать типы и . Первый хранит текст документа в оптимизированном для поиска виде, второй — хранит полнотекстовый запрос.

Для поиска в PostgreSQL есть функции , , . Для ранжирования результатов есть . Их использование интуитивно понятно и они хорошо описаны в документации, поэтому на подробностях их использования останавливаться не будем.

Традиционный поисковый запрос с помощью них будет выглядеть так:

Мы вывели id-ы документов, в тексте которых есть слово «запрос», и отсортировали их по убыванию релевантности. Кажется, всё хорошо? Нет.

У подхода выше есть много недостатков:

Мы не используем индекс для поиска.
Функция ts_vector вызывается для каждой строки таблицы.
Функция ts_rank вызывается для каждой строки таблицы.

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

420 секунд! На один запрос!

Ещё база генерит множество ворнингов вида . В этом ничего страшного нет. Причина в том, что в моей базе лежат документы, созданные в WYSIWYG-редакторе. Он вставляет множество всюду, где можно, и их бывает по 54 тысячи штук подряд. Postgres слова такой длины игнорирует и пишет ворнинг, который нельзя отключить.

Попробуем исправить все замеченные проблемы и ускорить поиск.

Стандарт SQL

Независимо от используемой системы управления базами данных, SQL — это стандартизированный язык выполнения запросов. И он поддерживается всеми решениями, даже MySQL или Postgresql. Стандарт SQL был разработан в 1986 году и за это время уже вышло нескольких версий.

MySQL

MySQL поддерживает далеко не все новые возможности стандарта SQL. Разработчики выбрали именно этот путь развития, чтобы сохранить MySQL простым для использования. Компания пытается соответствовать стандартам, но не в ущерб простоте. Если какая-то возможность может улучшить удобство, то разработчики могут реализовать ее в виде своего расширения не обращая внимания на стандарт.

Postgresql

Postgresql — это проект с открытым исходным кодом, он разрабатывается командой энтузиастов, и разработчики пытаются максимально соответствовать стандарту SQL и реализуют все самые новые стандарты. Но все это приводит к ущербу простоты. Postgresql очень сложный и из-за этого он не настолько популярен как MySQL.

datagrip

IDE для баз. Несмотря на то, что продукт относительно свежий, он уже используется повсеместно. В основном за счет того, что сразу встроен в мегапопулярные продукты от компании JetBrains: IntelliJ IDEA, PyCharm, PhpStorm и т.д.

Собственно, эта его встроенность одновременно является и главной киллер-фичей продукта: вы редактируете, например, php-код, в котором есть строка с sql-запросом, и внезапно понимаете, что IDE вам подсказывает (прямо в вашем коде) синтаксис SQL, названия таблиц и их полей, подчеркивает красненьким, если что-то написано не так, форматирует SQL и многое-многое другое. Конечно, в этом же IDE можно делать и то, что умеют другие GUI для баз: просматривать списки таблиц и других сущностей, отдельно делать запросы, экспорт таблиц в разные форматы и многое другое.

Из особенностей я бы отметил следующие вещи:

можно выделить несколько insert’ов и нажать «Edit as table» (см. картинку). После чего отредактировать это в удобном табличном виде вместо sql-синтаксиса, причем там же можно добавлять строки, колонки, экспортировать в csv и т.д.
Можно сравнивать результаты двух запросов. Это полезно, когда пытаешься упростить сложный запрос, и при этом ничего не сломать.
встроенность в код проработана не до конца. К примеру, при переименовывании в каком-либо интерфейсе колонки таблицы, IDE не находит нужные строки с SQL в коде (при этом автокомплит в этих строках работал), и наоборот, находит какую-то чушь.
Визуальной разработки не очень много. Т.е. вы можете сделать таблицу, но view уже не можете. Если таблица содержит какие-то id с foreign key (допустим, ссылка на некий словарь), хотелось бы при в вводе данных в таблицу выбирать значения из словаря, а не вбивать айдишки.
Если посмотреть таблицу в какой-нибудь из схем, то Datagrip посылает запрос set search_path = имясхемы, что приводит к плохим последствиям, если используется pgbouncer (а он используется почти всегда в случае с php или когда много серверов), так что для dev-разработки лучше использовать разные подключения: для работы кода — через pgbouncer, для ide — напрямую к базе.

Datagrip активно развивается, в частности, исправлены некоторые раздражающие баги с подсветкой синтаксиса.

В целом хороший современный инструмент, рекомендую.

Особенности новейшей разработки

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

Невосприимчивость к чрезмерному развертыванию.
Перераспределение — это то, что некоторые патентованные поставщики баз данных считают своей проблемой № 1 из-за соответствия лицензии. С PostgreSQL никто не может подать в суд на пользователя за нарушение лицензии. Так как нет никаких связанных лицензионных затрат на ПО.
Лучшая поддержка при создании базы данных PostgreSQL, чем у патентообладателей.
Яркое сообщество профессиональных энтузиастов PostgreSQL.
Значительная экономия на штатных расходах.
Низкие требования к обслуживанию и настройке, чем ведущие запатентованные базы данных, но при этом сохраняют все функции, стабильность и производительность.
Учебные программы продукта обычно рассматриваются как гораздо более дорогостоящие, эффективные, управляемые и практичные в реальном мире, чем у ведущих поставщиков собственных баз данных.
Легендарная надежность и стабильность.
Исходный код доступен бесплатно.
Если пользователю необходимо каким-либо образом настроить или расширить PostgreSQL, тогда он сможет сделать это с минимальными усилиями и без каких-либо затрат.

Важно, что это PostgreSQL объединяет сообщество энтузиастов по всему миру, которые ежедневно активно расширяют ПО. Многие пользователи используют стратегию хранения данных с несколькими строками, называемую MVCC, чтобы сделать PostgreSQL чрезвычайно отзывчивым в средах с большими объемами

Description

CREATE INDEX constructs an index on
the specified column(s) of the specified table. Indexes are
primarily used to enhance database performance (though
inappropriate use can result in slower performance).

The key field(s) for the index are specified as column names,
or alternatively as expressions written in parentheses. Multiple
fields can be specified if the index method supports multicolumn
indexes.

An index field can be an expression computed from the values
of one or more columns of the table row. This feature can be used
to obtain fast access to data based on some transformation of the
basic data. For example, an index computed on upper(col) would allow the clause WHERE upper(col) = 'JIM' to use an index.

PostgreSQL provides the index
methods B-tree, hash, GiST, and GIN. Users can also define their
own index methods, but that is fairly complicated.

When the WHERE clause is present, a
partial index is created. A partial
index is an index that contains entries for only a portion of a
table, usually a portion that is more useful for indexing than
the rest of the table. For example, if you have a table that
contains both billed and unbilled orders where the unbilled
orders take up a small fraction of the total table and yet that
is an often used section, you can improve performance by creating
an index on just that portion. Another possible application is to
use WHERE with UNIQUE to enforce uniqueness over a subset of a
table. See Section 11.8 for
more discussion.

The expression used in the WHERE
clause can refer only to columns of the underlying table, but it
can use all columns, not just the ones being indexed. Presently,
subqueries and aggregate expressions are also forbidden in
WHERE. The same restrictions apply to
index fields that are expressions.

2017

Документация версии 10 локализована для России

11 октября 2017 года компания Postgres Professional сообщила о переводе документации по PostgreSQL 10.0 на русский язык. Материалы доступны в форматах Html, epub и pdf. Общий объем текста составляет почти 2 600 страниц.

Помимо документации на русском языке, российским пользователям PostgreSQL также доступна техническая поддержка в режиме 24/7, помощь в миграции с других СУБД на PostgreSQL, обучающие курсы и технические конференции.

В ближайшее время Postgres Professional планирует выпуск обновленной версии российской СУБД Postgres Pro Standard на основе PostgreSQL 10.0.

PostgreSQL 10

5 октября 2017 года состоялся релиз PostgreSQL версии 10. В целом с каждой версией, выходящей раз в год, PostrgeSQL получает возможности, расширяющие область эффективного применения СУБД.

Основные нововведения:

Логическая репликация: отдельные части этого механизма были добавлены в PostgreSQL уже довольно давно, а в этой версии логическая репликация стала полностью доступна для пользователей. С ее помощью можно выборочно реплицировать отдельные таблицы на другой сервер, который при этом может выполнять как читающие, так и пишущие запросы. Серверы, участвующие в репликации, могут работать под управлением разных версий PostgreSQL, что позволяет проводить обновление кластера с минимальным временем простоя.
Декларативное секционирование избавляет администратора от необходимости вручную определять иерархию таблиц, создавать триггеры и ограничения целостности.
Параллельное выполнение запросов стало возможным для сканирования битовых карт и индексов, для соединения слиянием и подзапросов в дополнение к тем возможностям, которые появились в предыдущей версии.
Синхронная репликация с учетом кворума позволяет фиксировать изменения, если их подтвердило необходимое число произвольных реплик.
SCRAM-аутентификация является более криптостойким вариантом используемой ранее MD5-аутентификации.

Всего, по словам разработчиков, в версию 10 вошло более 100 изменений и улучшений, часть из которых выполнена в компании Postgres Professional.

Интеграция с Ethereum

14 сентября 2017 года российская компания Postgres Professional объявила о создании прототипа расширения Posthereum для интеграции полнофункциональной СУБД PostgreSQL с блокчейн-платформой Ethereum, предназначенной для регистрации сделок с любыми видами активов на основе системы «умных контрактов». По замыслу компании, крупные российские банки, корпорации и госструктуры, работающие с СУБД PostgreSQL, с помощью данной разработки смогут объединить базы данных с блокчейн-приложениями на основе Ethereum. Подробнее здесь.

Среда программирования на Mac

Прежде чем приступать к установке на компьютере Mac, пользователь должен убедиться, что вошел в систему как администратор, и далее выполнить:

Выбрать номер версии PostgreSQL для Mac OS, доступный в EnterpriseDB, например postgresql-9.2.4-1-osx.dmg для Mac OS, работающей с OS X версии 10.8.3.
Открыть изображение dmg в finder, просто дважды щелкнуть его, что запустит установщик PostgreSQL в следующем окне.
Затем нажать значок postgres-9.2.4-1-osx, который выдает предупреждающее сообщение.
Принять предупреждение и приступить к дальнейшей установке. Он попросит пароль администратора.
Ввести пароль, приступить к установке и после этого перезапустить компьютер Mac. Если не запустится начальный экран программы, запустить установку еще раз.
После запуска установщика он задает несколько основных вопросов, таких как местоположение установки, пароль пользователя, который будет использовать базу данных, номер порта.
Сохранить все их значения по умолчанию, кроме пароля.

Теперь можно запустить любую из программ.

Why use PostgreSQL?

PostgreSQL comes with many features aimed to help developers build applications, administrators to protect data integrity and build fault-tolerant environments, and help you manage your data no matter how big or small the dataset. In addition to being free and open source, PostgreSQL is highly extensible. For example, you can define your own data types, build out custom functions, even write code from different programming languages without recompiling your database!

PostgreSQL tries to conform with the SQL standard where such conformance does not contradict traditional features or could lead to poor architectural decisions. Many of the features required by the SQL standard are supported, though sometimes with slightly differing syntax or function. Further moves towards conformance can be expected over time. As of the version 13 release in September 2020, PostgreSQL conforms to at least 170 of the 179 mandatory features for SQL:2016 Core conformance. As of this writing, no relational database meets full conformance with this standard.

Below is an inexhaustive list of various features found in PostgreSQL, with more being added in every major release:

Data Types
- Primitives: Integer, Numeric, String, Boolean
- Structured: Date/Time, Array, Range, UUID
- Document: JSON/JSONB, XML, Key-value (Hstore)
- Geometry: Point, Line, Circle, Polygon
- Customizations: Composite, Custom Types
Data Integrity
- UNIQUE, NOT NULL
- Primary Keys
- Foreign Keys
- Exclusion Constraints
- Explicit Locks, Advisory Locks
Concurrency, Performance
- Indexing: B-tree, Multicolumn, Expressions, Partial
- Advanced Indexing: GiST, SP-Gist, KNN Gist, GIN, BRIN, Covering indexes, Bloom filters
- Sophisticated query planner / optimizer, index-only scans, multicolumn statistics
- Transactions, Nested Transactions (via savepoints)
- Multi-Version concurrency Control (MVCC)
- Parallelization of read queries and building B-tree indexes
- Table partitioning
- All transaction isolation levels defined in the SQL standard, including Serializable
- Just-in-time (JIT) compilation of expressions
Reliability, Disaster Recovery
- Write-ahead Logging (WAL)
- Replication: Asynchronous, Synchronous, Logical
- Point-in-time-recovery (PITR), active standbys
- Tablespaces
Security
- Authentication: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, Certificate, and more
- Robust access-control system
- Column and row-level security
- Multi-factor authentication with certificates and an additional method
Extensibility
- Stored functions and procedures
- Procedural Languages: PL/PGSQL, Perl, Python (and many more)
- SQL/JSON path expressions
- Foreign data wrappers: connect to other databases or streams with a standard SQL interface
- Customizable storage interface for tables
- Many extensions that provide additional functionality, including PostGIS
Internationalisation, Text Search
- Support for international character sets, e.g. through ICU collations
- Case-insensitive and accent-insensitive collations
- Full-text search

There are many more features that you can discover in the PostgreSQL documentation. Additionally, PostgreSQL is highly extensible: many features, such as indexes, have defined APIs so that you can build out with PostgreSQL to solve your challenges.

PostgreSQL has been proven to be highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL clusters in production environments that manage many terabytes of data, and specialized systems that manage petabytes.