MEPO Forum - PostgreSQL

Create a Table with Incremental Primary Key (no replies)

mepoadm — Wed, 25 Jun 2014 00:02:11 +0800

1. Add primary key when creating a table

CREATE TABLE example
(
id bigserial primary key,
name VARCHAR(255) not null,
value bigint
);

2. Add primary key by updating a table

ALTER TABLE example add primary key (id);

參數設定與效能校調(Configuration)：VACUUM / Automatic VACUUM (no replies)

HP — Tue, 04 Sep 2012 22:32:27 +0800

1. 什麼是VACUUM
2. Vacuum 設定
3. Vacuum 自動執行設定

1. 什麼是VACUUM

VACUUM 基本的功能就是 Garbage collection。主要原因是 PostgreSQL 資料庫在資料刪除的時候不會直接將資料刪除。要等執行 Vacuum 時才會將資料從硬碟等其他儲存設備上實體移除。如果要讓系統自動執行 Vacuum，有 autovacuum 等參數可以設定。系統預設 autovacuum 是啟用的。

詳細說明可見 Routine Vacuuming。

2. Vacuum 設定

This feature is disabled by default for manually issued VACUUM commands. To enable it, set the vacuum_cost_delay variable to a nonzero value.

vacuum_cost_delay (integer)
The length of time, in milliseconds, that the process will sleep when the cost limit has been exceeded. The default value is zero, which disables the cost-based vacuum delay feature. Positive values enable cost-based vacuuming. Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; setting vacuum_cost_delay to a value that is not a multiple of 10 might have the same results as setting it to the next higher multiple of 10.

When using cost-based vacuuming, appropriate values for vacuum_cost_delay are usually quite small, perhaps 10 or 20 milliseconds. Adjusting vacuum's resource consumption is best done by changing the other vacuum cost parameters.
vacuum_cost_page_hit (integer)
The estimated cost for vacuuming a buffer found in the shared buffer cache. It represents the cost to lock the buffer pool, lookup the shared hash table and scan the content of the page. The default value is one.
vacuum_cost_page_miss (integer)
The estimated cost for vacuuming a buffer that has to be read from disk. This represents the effort to lock the buffer pool, lookup the shared hash table, read the desired block in from the disk and scan its content. The default value is 10.
vacuum_cost_page_dirty (integer)
The estimated cost charged when vacuum modifies a block that was previously clean. It represents the extra I/O required to flush the dirty block out to disk again. The default value is 20.

vacuum_cost_limit (integer)
The accumulated cost that will cause the vacuuming process to sleep. The default value is 200.

3. Vacuum 自動執行設定

Postgresql 和 MySQL 語法相異處整理：Create Table, KEY / INDEX, BIGINT / INT UNSIGNED (no replies)

HP — Sat, 03 Sep 2011 01:56:08 +0800

1. PostgreSQL 沒有 CREATE TABLE IF NOT EXISTS 的語法

乍看之下好像 PostgreSQL 的語法比較弱，但事實上是 PostgreSQL 會自動判斷 table 存在與否，所以直接寫 CREATE TABLE 就可以了。

2. PostgreSQL 在 CREATE TABLE 時不能建 non-unique key

在 MySQL 中常會有這樣的語法：

CREATE TABLE table (
        request_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        user_id INT UNSIGNED NOT NULL ,
        KEY (user_id)
);

最後的 KEY (user_id) 是建立一個非唯一限制的 index，但在 PostgreSQL 中 CREATE TABLE 時只能建立 UNIQUE INDEX，所在基本上要建 non-unique key 只能獨立地以 CREATE INDEX 建立。

3. PostgreSQL 沒有 BIGINIT UNSIGNED / INT UNSIGNED 的 data type

PostgreSQL 和 MySQL 的 Data type 對照表：

http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL#Data_Types

參考資料：
1. Postgresql: how to create table only if it does not already exist?
http://stackoverflow.com/questions/435424/postgresql-how-to-create-table-only-if-it-does-not-already-exist
2. Converting MySQL to PostgreSQL
http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL

避免註冊會員「使用者名稱重複」(case-insensitive)：Functional indexes & lower() search (no replies)

mepoadm — Sun, 26 Sep 2010 03:23:21 +0800

第一步：對有關「會員」的 database table 中的 username 建立 UNIQUE KEY。SQL 如下：

CREATE UNIQUE INDEX users_unique_username ON the_user_table USING btree (lower(username));

其中的 (lower(username)) 的用法稱作 Functional Indexes。USING btree 是指定建立 KEY 的方法，此處為以 btree 建立。不一定要寫上。

第二步：在 Script 中確保使用者名稱不重複：Case-insensitive 搜尋

SELECT * FROM the_user_table WHERE lower(username) = '新註冊的使用者名稱';

若 SELECT 結果有找到相同的使用者名稱，就表示該帳號已經不能申請。

Reference:
Functional Indexes

Performance Issue in PostgreSQL: Counting rows in a table (no replies)

HP — Fri, 12 Feb 2010 00:16:01 +0800

In summary, PostgreSQL sacrifices some performance to the overall reliability and data integrity. COUNT is slow for the whole table scan but with normal speed when COUNT with INDEXed tables.

--
The article below is from Why PostgreSQL Instead of MySQL 2009.
--

Counting rows in a table

One operation that PostgreSQL is known to be slow performing is doing a full count of rows in a table, typically using this SQL:

SELECT COUNT(*) FROM table

The reason why this is slow is related to the MVCC implementation in PostgreSQL. The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table; PostgreSQL must walk through all rows, in some sense. This normally results in a sequential scan reading information about every row in the table.
Some DBMSes provide the ability for "COUNT(*)" queries to work via consulting an index. Unfortunately, in PostgreSQL, this strategy does not work, as MVCC visibility information is not stored at the index level. It is necessary to actually examine the rows themselves to determine if they are visible to the transaction or not.

In MySQL, MyISAM tables cache the row count information, making this type of count operation almost instant. That is the reason why there exists so much MySQL code that uses this construct assuming it's a trivial operation. But if you're using InnoDB instead, this is no longer the case. See COUNT(*) for Innodb Tables and COUNT(*) vs COUNT(col) for notes on the limitations of MySQL in this area. MySQL designs that may be deployed on InnoDB can't assume that a full row count will be fast, and therefore are hampered by similar limitations to those present in PostgreSQL. However, InnoDB's MVCC information is present in its indexes, so an index can be used to satisfy COUNT(*) queries, even when there is no WHERE clause; a full table scan is not necessary.

It is worth observing that it is only this precise form of aggregate that must be so pessimistic; if augmented with a "WHERE" clause like

SELECT COUNT(*) FROM table WHERE status = 'something'

PostgreSQL, MySQL, and most other database implementations will take advantage of available indexes against the restricted field(s) to limit how many records must be counted, which can greatly accelerate such queries. PostgreSQL will still need to read the resulting rows to verify that they exist; MySQL may or may not, depending on the storage engine and the transaction isolation level. InnoDB generally does not need to read the rows, and can satisfy the operation from the index alone.

One popular approach for applications that need a row count but can tolerate it not including transactions that are in the middle of being committed is to use a trigger-based mechanism to count the rows in the table. In PostgreSQL, another alternative when only an approximate count is needed is to use the reltuples field from the pg_class catalog table.

PostgreSQL的數值資料型態(Numeric Data Types)速查表 (no replies)

HP — Fri, 09 Oct 2009 16:56:21 +0800

在設定 PostgreSQL 資料庫時經常會用到，節錄此表方便大家查詢：

Name	Storage Size	Description	Range
`smallint`	2 bytes	small-range integer	-32768 to +32767
`integer`	4 bytes	usual choice for integer	-2,147,483,648 to +2,147,483,647
`bigint`	8 bytes	large-range integer	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
`decimal`	variable	user-specified precision, exact	no limit
`numeric`	variable	user-specified precision, exact	no limit
`real`	4 bytes	variable-precision, inexact	6 decimal digits precision
`double precision`	8 bytes	variable-precision, inexact	15 decimal digits precision
`serial`	4 bytes	autoincrementing integer	1 to 2147483647
`bigserial`	8 bytes	large autoincrementing integer	1 to 9223372036854775807

基本上常用的 integer 的大小是十億等級(2.1 billion)，bigint (big integer) 是 92 萬兆(京)。

Reference:
http://www.postgresql.org/docs/8.3/static/datatype-numeric.html