はてなブックマーク

Many database systems provide sample databases with the product. A good intro to popular ones that includes discussion of samples available for other databases is Sample Databases for PostgreSQL and More (2006). One trivial sample that PostgreSQL ships with is the Pgbench. This has the advantage of being built-in and supporting a scalable data generator. MySQL has a popular sample database named S

A short list of common mistakes. Kristian Dupont provides schemalint a tool to verify the database schema against those recommendations. Database Encoding Don't use SQL_ASCII Why not? SQL_ASCII means "no conversions" for the purpose of all encoding conversion functions. That is to say, the original bytes are simply treated as being in the new encoding, subject to validity checks, without any regar

This article covers the current status, history, and OS and OS version differences relating to the circa 2018 fsync() reliability issues discussed on the PostgreSQL mailing list and elsewhere. It has sometimes been referred to as "fsyncgate 2018". Current status As of this PostgreSQL 12 commit, PostgreSQL will now PANIC on fsync() failure. It was backpatched to PostgreSQL 11, 10, 9.6, 9.5 and 9.4.

What's New In PostgreSQL 10 General Links: Release Notes PostgreSQL 10 New Features With Examples - HPE.com PostgreSQL10_Roadmap Robert Haas New Features Coming in PostgreSQL 10 Bruce Momjian Major Features: Postgres 10 Magnus Hagander PostgreSQL 10 Big Data Native Partitioning Table_partitioning: Background and Limitations of PostgreSQL 10 Partitioning In 10, partitioning tables is now an attribu

A common problem when copying or recreating a database is that database sequences are not updated just by inserting records in the table that sequence is used in. If you want to make your sequences all start just after whatever values are already there, it's possible to do that for most common configurations like this: SELECT 'SELECT SETVAL(' || quote_literal(quote_ident(sequence_namespace.nspname

現在のロックトランザクションをオンラインでビューにより確認する pg_locksビュー pg_locksを参照すると、ロックが付与されたり、プロセスが獲得されたロックのために待たされていることを確認できます。ロックの問題を探し始めるために良いクエリは以下となります： pg_stat_activityビュー プロセスが掴んでいる又はロックを待っていることを探し出すには、pg_stat_activityの情報を相互参照するのが簡単です。 ブロックされた又はブロック中の活動の組み合わせ 以下のクエリは、プロセスがSQLステートメントをブロックしていることを確認する手助けとなるでしょう（これらは行レベルのロックを発見するのみであり、オブジェクトレベルのロックは発見できません）。 PostgreSQLバージョン9.2未満: select bl.pid as blocked_pid, a.usenam

(This page is currently under development ahead of the release of PostgreSQL 9.5) This page contains an overview of PostgreSQL Version 9.5's features, including descriptions, testing and usage information, and links to blog posts containing further information. See also PostgreSQL 9.5 Open Items. Major new features IMPORT FOREIGN SCHEMA Previously, in order to create a foreign table in PostgreSQL,

This page summarizes the INSERT ... ON CONFLICT UPDATE patch. This feature is popularly known as "UPSERT". The patch has been committed [1], and will appear in PostgreSQL 9.5. This Wiki page was only maintained until a few weeks before commit, where the patch further evolved in some minor aspects (most notably, the syntax became ON CONFLICT DO UPDATE/NOTHING). It is now only of historic interest.

"Loose indexscan" in MySQL and Cockroach, and "Hybrid Scan" in YugabyteDB are names used for an operation that finds distinct values of the leading columns of a btree index efficiently; rather than scanning all equal values of a key, as soon as a new value is found, restart the search by looking for a larger value. This is much faster when the index has many equal keys. Loose Indexscan versus Inde

Why Run PostgreSQL in a VM? There are many ways of installing and running PostgreSQL on OS X and Linux. This document is to make that process simpler by offering a single, consistent solution that works on both platforms and allows for multiple, isolated versions of PostgreSQL. Running PostgreSQL in a virtual machine (VM) standardizes the underlying operating system (no more Linux vs OS X issues),

PostgreSQL builtin & contrib Statistics collector PostgreSQL collects lots of data on its own and offers it via the pg_stat(io)_ system views Official documentation on the Statistics Collector Interpreting pg_stat Views contrib extensions The following extensions offer access to Postgres internals which may be of interest or collect additional information. Most of them are shipped with Postgres (t

The benefit of an HTTP API would be easier access via tools that don't support the PostgreSQL protocol, such as curl, web browsers, new programming languages, etc. It'd also be attractive to the NoSQL crowd, who often want *simple* access. Particularly so when combined with PostgreSQL's new built-in JSON types, which could be returned in a JSON format result unescaped as a directly nested child ob

This page contains an overview of PostgreSQL Version 9.4's features, including descriptions, testing and usage information, and links to blog posts containing further information. See also PostgreSQL 9.4 Open Items. Major new features JSONB Binary JSON storage With the new JSONB data type for PostgreSQL, users no longer have to choose between relational and non-relational data stores: they can hav

PostgreSQL can be installed using RPMs (binary) or SRPMs (source) managed by YUM or DNF . This is available for the following Linux distributions (both 32- and 64-bit platforms; for the current release and prior release or two): Fedora Red Hat Enterprise Linux CentOS Scientific Linux (EOLed) Oracle Enterprise Linux See links from the main repository, http://yum.postgresql.org: A list of available

November 2013 Replication Data Loss Issue What is the November 2013 Replication Data Loss Issue? This is an issue, discovered Nov. 18, 2013., which can cause data corruption on a Hot-Standby replica when it is (re-)started, by marking committed transactions as uncommitted. This issue is fixed in the December 5th 2013 update releases. Hackers mailing list discussion here What are the symptoms of th

Like several of its commercial competitors, one of the more advanced features of PostgreSQL is its ability to perform transactional DDL via its Write-Ahead Log design. This design supports backing out even large changes to DDL, such as table creation. You can't recover from an add/drop on a database or tablespace, but all other catalog operations are reversible. Here is an example showing how robu

PostgreSQL packages for Debian and Ubuntu The PostgreSQL Global Development Group (PGDG) maintains an APT repository of PostgreSQL packages for Debian and Ubuntu located at https://apt.postgresql.org/pub/repos/apt/. We aim at building PostgreSQL server packages as well as extensions and modules packages on several Debian/Ubuntu releases for all PostgreSQL versions supported. Currently, we support

ユーザ、問い合わせ、データベースについてのプライオリティ付け PosgtgreSQLには特定のユーザ、問い合わせ、データベースが消費するリソースを制限する機能はありません。 同様にあるユーザ/問い合わせ/データベースが他より多くのリソースを得られるようなプライオリティを設定する機能もありません。 プライオリティの制限を実現するためにはオペレーティングシステムの機能を使用する必要があります。 PostgreSQLユーザ、問い合わせ、データベースが競い合うリソースには大きく以下の3つがあります。 メモリ CPU ディスクI/O 3つの中で、ディスクI/Oがデータベースアプリケーションのボトルネックとなることがよくあります。 しかし常にそうとは限りません。 スキーマ・問い合わせ設計の中には特にCPUが大きな問題となることがあります。 その他大量のメモリを使用して作業することが、特にソート処理で、

This will report size information for all tables, that are not inherited, in the "pretty" form. Inherited tables are grouped together. WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS (select inhrelid, inhparent FROM pg_inherits UNION SELECT child.inhrelid, parent.inhparent FROM pg_inherit child, pg_inherits parent WHERE child.inhparent = parent.inhrelid), pg_inherit_short AS (SELECT * FROM pg_in

You can often support more concurrent users by reducing the number of database connections and using some form of connection pooling. This page attempts to explain why that is. Summary A database server only has so many resources, and if you don't have enough connections active to use all of them, your throughput will generally improve by using more connections. Once all of the resources are in us

This document showcases many of the latest developments in PostgreSQL 9.2, compared to the last major release – PostgreSQL 9.1. There are many improvements in this release, so this wiki page covers many of the more important changes in detail. The full list of changes is itemised in Release Notes. Major new features Index-only scans In PostgreSQL, indexes have no "visibility" information. It means

Guide to Asking Slow Query Questions In any given week, some 50% of the questions on #postgresql IRC and 75% on pgsql-performance are requests for help with a slow query. However, it is rare for the requester to include complete information about their slow query, frustrating both them and those who try to help. Please post performance related questions to the pgsql-performance mailing list or to

Psycopg2 is a mature driver for interacting with PostgreSQL from the Python scripting language. It is written in C and provides a means to perform the full range of SQL operations against PostgreSQL databases. This page is focused on version 2 of the driver, only. Overview Links Official Project Documentation Psycopg2 Project Repository Psycopg2 Wiki Tutorial PostgreSQL Tutorial and PostgreSQL Pyt

IMPORTANT NOTICE Description of this wikipage is not maintained for more than a year. Please reference the online manual of the PG-Strom project instead. PG-Strom Github: https://github.com/heterodb/pg-strom PG-Strom Documentation: http://heterodb.github.io/pg-strom/ PG-Strom Documentation(Japanese): http://heterodb.github.io/pg-strom/ja/ NOTE: Deprecated documentation was removed to avoid confusi

Index-only scans are a major performance feature added to Postgres 9.2. They allow certain types of queries to be satisfied just by retrieving data from indexes, and not from tables. This can result in a significant reduction in the amount of I/O necessary to satisfy queries. During a regular index scan, indexes are traversed, in a manner similar to any other tree structure, by comparing a constan

Information about the SSI implementation for the SERIALIZABLE transaction isolation level in PostgreSQL, new in release 9.1. Overview With true serializable transactions, if you can show that your transaction will do the right thing if there are no concurrent transactions, it will do the right thing in any mix of serializable transactions or be rolled back with a serialization failure. This docume