PgConf.Russia 2020

Locking is critical for providing high concurrency for any database — you cannot fully utilize your hardware if locking is throttling its use. This talk explores all aspects of locking in Postgres by showing queries and their locks; covered lock types include row, table, shared, exclusive, and advisory lock types. The high concurrency provided by Multiversion Concurrency Control (MVCC) is also covered.

Slides are at https://momjian.us/main/writings/pgsql/locking.pdf

Я расскажу о том, как приспосабливал индекс GiST для решения одной частной задачи на примере поиска текстов, "похожих" на данный в большом наборе текстов. Сама по себе задача довольно узкая и в общем случае ценности не представляет. Но она простая, понятная и отлично подходит в качестве иллюстрации. Интересен подход к построениею стратегии индексирования и поиска при решении нестандартной поисковой задачи почти без программирования с использованием сильных сторон индекса GiST. Возможно, это поможет вам найти решение для каких-то других поисковых задач.

Many businesses which use Database management systems like Oracle, DB2 & MS SQL are unreliable these days. Moreover, the costs incurred in maintaining these systems and its product licenses keeps on increasing. As the competitors are migrating over to the new technologies and tools available in the market, it is necessary for these businesses to migrate to new environment which is efficient, consistent and reliable to stay in the market and the technologies used in the current environment have become obsolete or no longer serve the business purpose. PostgreSQL has emerged as a top open-source RDBMS software. Since there is no licensing cost associated with it most of the companies are planning to migrate the databases which are currently running on other RDBMS like Oracle, DB2, MS SQL server to PostgreSQL. This report summarizes the various methodologies, procedures and techniques involved in successfully migrating the data from Oracle to PostgreSQL & DB2 to PostgreSQL. Migration is not a simple effort there should be proper planning and testing involved in this right from database connectivity to performance analysis. In this paper we are going to cover most of the steps which we need to consider before the migration and after the migration like choosing the correct tools for implementing the migration, time taken to migrate ,data compatibility, code conversion, application connectivity to database, database configuration parameters, performance analysis, replication setups, database monitoring, patching and backup strategies.

pg_repack является популярным инструментом для устранения bloat-a таблиц и индексов в постгресе. В большинстве случаев он отлично справляется с проблемой. Но как оказалось, в случае использования такой фичи постгреса как deferred ограничения, использование pg_repack либо сильно усложняется, либо становится невозможным. Я расскажу о том, как мы обнаружили эту проблему и опишу возможные способы ее решения - от встроенных средств постгреса до небольшого патча pg_repack.