脱力系備忘録BloG ホーム »DB

PostgreSQLの格納データサイズによって速度低下する問題  

Gigazinさんに非常に興味深い記事「PostgreSQLには「中途半端なサイズ」のデータを格納しないほうがいい」があったので備忘録。

なんでも、PostgreSQLは「固定長のページ」という概念の中にテーブルを格納して管理しているようです。

そのページに収まらない大きなデータを格納する場合は「TOAST」という機能が自動的に稼働してデータを専用のテーブルに分割格納しているようです。
「TOAST」のことは「PostgreSQL 12.3文書 第68章 データベースの物理的な格納 68.2. TOAST」をご覧ください。

で、興味深いのは、
このTOAST機能を利用するほど大きなデータを扱うとPostgreSQLのパフォーマンスが低下する。

のではなく、
TOAST機能を必要としない「ぎりぎりサイズ」程度のデータを格納するとパフォーマンスが低下する。というのがなるほどと思ったわけです。

データベースは奥が深すぎて私には使いこなせないです。
(´・ω・`)
これからも「便利な格納庫」程度の認識で使わせてもらいます。

category: DB

PostgreSQLで列項目(文字列)と列項目(文字列)のパターンマッチをさせるSQL  

あるテーブルに「A」と「B」という文字型項目があったとして、「A」が「B」に含まれているかの文字列パターンマッチを行うSQLがすぐ忘れそうなので書いておく。

select * from テーブル名 where B like '%' || A || '%';

です。
「%」は0文字以上の並びとの一致を意味します。詳しくはPostgreSQLマニュアルのパターンマッチ「LIKE」をご覧ください。
「||」は文字列の結合です。

category: DB

SQLITE3でUNIXタイムスタンプを日付文字列に変換する  

SQLITE3で日付を扱う際にテキスト型ではなく数値型でUNIXタイムスタンプを格納している場合があります。
それを、日付文字列形式で取得する方法です。

category: DB

SQlite3 でソートするときにアルファベットの大文字と小文字を区別したくない  

そんなとき、COLLATE nocase を付けてあげたら解決。

select 項目 from テーブル名 order by 項目 COLLATE nocase;

category: DB

PostgreSQLで16進数、2進数を扱う場合の表記  

16進数の場合「x」、2進数の場合は「b」を頭に付ける。
x'a' = b'1010' = 10

しかし、これだけだと10進数として計算できないので「::integer」を付ける。

select x'f'::integer + 5;

「20」と表示されます。

category: DB

SQLite3での文字列連結と数値の文字列結合  

SQLite3で文字列結合の関数はなんだろう?と思ったけど関数じゃなくて「||」という演算子で結合できる。
数値はそのまま文字列結合できるのかな?と思ったら、そのままできた。

(例)
select 100 || '*' || 200;
⇒100*200

もちろん、数値のカラムを指定しても連結できる。

category: DB

PostgreSQLで「invalid input syntax for type timestamp with time zone」  

というエラーが出た。
どうやってこのエラーが出たかと言うと、

select CURRENT_TIMESTAMP-'24 hours';

でエラーになりました。
正しくは、

select CURRENT_TIMESTAMP-interval '24 hours';

と書かなくちゃいけない。
これが足し算の場合

select CURRENT_TIMESTAMP+'24 hours';

は、エラーにならないんだなこれが。
まあ、

select CURRENT_TIMESTAMP+interval '24 hours';

と、「interval」を書くクセを付けた方が良いみたい。

ということで、思い込みによるバグは見つけるのが難しい一例でした。

category: DB

64ビット環境でのデータベースの符号付INTEGER型整数の範囲  

64ビット環境ではPHP符号付INTEGER型整数の上限は 9223372036854775807 になります。
PostgreSQLはどうなるんだろう?と思って調べてみたら 2147483647 でした。
PostgreSQLの場合、OSのビット数に関係なく「型宣言」で整数の範囲が決まるようです。

INTEGER → 2147483647
BIGINT → 9223372036854775807

気を付けなくちゃいけないのはPHPでビット操作をする場合。PostgreSQLのINTEGER型をPHPの変数に格納した時点で64ビット整数になります。
INTEGER型で「-1」は「0xffffffff」ですが、PHPの変数では「0xffffffffffffffff」になる。
注意が必要です。

ついでにSQlite3も調べてみました。
64ビット環境でのINTEGER型の上限は 9223372036854775807 です。

category: DB

【PostgreSQL】SERIAL型とROLLBACK  

SERIAL型定義や「CREATE SEQUENCE」でシーケンスをつくり、トランザクション内で自動採番させたものをROLLBACKしても元に戻らない。

これは仕様。

そもそもシーケンスジェネレータは自動採番する仕組みを提供するだけで、連番を保障する仕組みではない。


ふむ(´・ω・`)
LOCK処理などで自分で制御しないとだめな模様。

category: DB

[PHP] DB - PEARとPDOのBOOLEAN型の扱いの違い  

PostgreSQLを使っているサイトの修正で躓いた。

今まではデータベースへのアクセスにPEAR:DBを使用している。
これをPDOに変更したらうまく動かない。

よく調べてみたら、「BOOLEAN」型項目の内容判定が正しく行われていない。

何故だろうと思い、それぞれのアクセス結果を見てみたら、ぬーん。

●PEAR::DBの場合
string("t")
string("f")

●PDOの場合
bool(true)
bool(false)

で返ってくる模様。

「BOOLEAN」型は使わず「INTEGER」型で定義したほうが素直でわかりやすいやね。

category: DB

プロフィール

最新記事

最新コメント

アンケートやゲーム参加で無料でマイルが貯まる! すぐ貯まるポイントサイト すぐたま

▲ Pagetop