0Pricing
PostgreSQL Performance & Query Optimization · Aula

Escolhendo tipos de dados adequados

Selecione os tipos de dados mais eficientes para suas colunas, minimizando o armazenamento e otimizando o processamento das consultas.

Escolhendo tipos de dados adequados é uma aula grátis de PostgreSQL Performance & Query Optimization no CoddyKit. Esta é a aula 2 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de PostgreSQL Performance & Query Optimization, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de PostgreSQL Performance & Query Optimization inclui 4 aulas no total.

Partes desta aula ainda não foram traduzidas e aparecem em inglês.

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.

Perguntas Frequentes

A aula “Escolhendo tipos de dados adequados” é grátis?

Sim — o texto completo de “Escolhendo tipos de dados adequados” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de PostgreSQL Performance & Query Optimization, atualize para CoddyKit PRO. O curso de PostgreSQL Performance & Query Optimization inclui 4 aulas no total.

O que vou aprender em “Escolhendo tipos de dados adequados”?

Selecione os tipos de dados mais eficientes para suas colunas, minimizando o armazenamento e otimizando o processamento das consultas. Você pratica PostgreSQL Performance & Query Optimization com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar PostgreSQL Performance & Query Optimization?

Nenhuma experiência prévia é necessária. PostgreSQL Performance & Query Optimization no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 2 de 4.

Quanto tempo leva a aula “Escolhendo tipos de dados adequados”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de PostgreSQL Performance & Query Optimization?

Sim. Cada aula de PostgreSQL Performance & Query Optimization inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

  1. Concessões entre normalização e desnormalização
  2. Escolhendo tipos de dados adequados
  3. Particionamento de tabelas grandes
  4. Projetando chaves primárias e chaves substitutas
← Voltar para PostgreSQL Performance & Query Optimization