0Pricing
PostgreSQL Performance & Query Optimization · レッスン

適切なデータ型の選択

ストレージ使用量を抑え、クエリ処理を最適化するため、カラムに最も効率的なデータ型を選択します。

「適切なデータ型の選択」はCoddyKit上の無料PostgreSQL Performance & Query Optimizationレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはPostgreSQL Performance & Query Optimization学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

このレッスンの一部はまだ翻訳されておらず、英語で表示されています。

Why Data Types Matter

Choosing the correct data type for your columns in PostgreSQL is a fundamental step in designing an efficient database.

It impacts storage, query performance, and data integrity. Selecting the right type ensures your data is stored efficiently and processed quickly.

Picking Integer Types

PostgreSQL offers several integer types, each with a different storage size and value range:

  • SMALLINT: 2 bytes, range -32,768 to +32,767
  • INTEGER (or INT): 4 bytes, range -2,147,483,648 to +2,147,483,647
  • BIGINT: 8 bytes, range -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807

Always choose the smallest integer type that can safely store your expected data range to minimize disk space and improve performance.

Integer Type Demo

Let's see how different integer types are defined. Notice how we pick the smallest type that fits the value.

CREATE TABLE product_counts (
  small_count SMALLINT,
  medium_count INTEGER,
  large_count BIGINT
);

INSERT INTO product_counts (small_count, medium_count, large_count)
VALUES (100, 50000, 1000000000);

SELECT * FROM product_counts;

Numeric Precision

When dealing with decimal numbers, especially monetary values, precision is key:

  • NUMERIC(p, s): Stores exact numbers. p is the total number of digits (precision), s is the number of digits after the decimal point (scale). Essential for financial data.
  • REAL (4 bytes) and DOUBLE PRECISION (8 bytes): Store approximate floating-point numbers. They are faster but can introduce tiny rounding errors, making them unsuitable for money.

Use NUMERIC when exactness is critical; use REAL or DOUBLE PRECISION for scientific or less critical calculations where approximation is acceptable.

Text Storage Choices

For storing text strings, PostgreSQL offers:

  • VARCHAR(n): Variable-length string with a user-defined maximum length n. If you try to insert a longer string, it will be truncated or an error will occur.
  • TEXT: Variable-length string with no explicit maximum length. It's often the most flexible choice.
  • CHAR(n): Fixed-length string. If the string is shorter than n, it's padded with spaces. Generally discouraged due to potential performance issues and space waste.

For most modern applications, VARCHAR (without a length, acting like TEXT) or simply TEXT are preferred for their flexibility and efficient storage.

VARCHAR vs. TEXT

Here's an example demonstrating VARCHAR with a length constraint and TEXT without one. Both store data efficiently based on actual length.

CREATE TABLE messages (
  short_msg VARCHAR(50),
  long_msg TEXT
);

INSERT INTO messages (short_msg, long_msg)
VALUES ('Hello World', 'This is a much longer message that can span multiple lines and characters.');

SELECT short_msg, LENGTH(short_msg), long_msg, LENGTH(long_msg) FROM messages;

Handling Dates & Times

PostgreSQL provides robust date/time types:

  • DATE: Stores date only (year, month, day).
  • TIME: Stores time of day only (hour, minute, second, fractional seconds).
  • TIMESTAMP: Stores date and time. It does NOT store timezone information.
  • TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE): Stores date and time, and converts it to UTC upon storage. It's the recommended type for most applications to avoid timezone headaches.

Always prefer TIMESTAMPTZ for event timestamps unless you have a very specific reason not to.

Special Types: Boolean & UUID

Two other useful data types:

  • BOOLEAN: Stores true/false values. It's highly efficient, requiring only 1 byte of storage. PostgreSQL accepts 'true', 'false', 't', 'f', '1', '0', 'yes', 'no' as inputs.
  • UUID: Stores Universally Unique Identifiers. These are 128-bit quantities, useful for generating unique primary keys without database sequence contention, especially in distributed systems.

UUIDs can be generated by PostgreSQL using functions like gen_random_uuid() from the pgcrypto extension.

Type Conversion Overhead

While PostgreSQL often handles implicit type conversions (e.g., converting a string '123' to an integer), this comes with a performance cost.

When you compare or join columns of different data types, PostgreSQL might need to perform a conversion on one or both sides, which can prevent indexes from being used and slow down queries.

Always strive to compare and join columns that have identical data types. If conversion is necessary, use explicit casting (e.g., column::INTEGER) to make it clear and sometimes help the query planner.

Data Type Challenge

Imagine you are designing a table to store user registration details. One column needs to store whether a user has verified their email, and another needs to store a unique, globally identifiable user ID that can be generated anywhere.

Data Type Summary

We've covered the importance of choosing appropriate data types for performance, storage, and integrity.

  • Choose the smallest integer type that fits your data.
  • Use NUMERIC for exact decimal values (like money).
  • Prefer TIMESTAMPTZ for storing dates and times with timezone awareness.
  • BOOLEAN is best for true/false flags, and UUID for globally unique identifiers.
  • Avoid unnecessary type conversions to maintain query performance.

Careful data type selection is a cornerstone of efficient database design.

よくある質問

「適切なデータ型の選択」レッスンは無料ですか？

はい。「適切なデータ型の選択」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、PostgreSQL Performance & Query Optimizationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

「適切なデータ型の選択」で何を学びますか？

ストレージ使用量を抑え、クエリ処理を最適化するため、カラムに最も効率的なデータ型を選択します。 ブラウザで直接実行するハンズオンコードでPostgreSQL Performance & Query Optimizationを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

PostgreSQL Performance & Query Optimizationを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのPostgreSQL Performance & Query Optimizationは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。

「適切なデータ型の選択」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このPostgreSQL Performance & Query Optimizationレッスンでコードを書いて実行できますか？

はい。すべてのPostgreSQL Performance & Query Optimizationレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. 正規化と非正規化のトレードオフ
  2. 適切なデータ型の選択
  3. 大規模テーブルのパーティショニング
  4. 主キーと代理キーの設計
← PostgreSQL Performance & Query Optimizationに戻る