DOCSEARCHbyAlgolia
Submit Docs

Postgres.ai Guidelines

website·Indexed 34 days ago

https://postgres.ai/

A collection of best practices and guides for PostgreSQL, including DB schema design, SQL style guidelines, and instructions for Postgres hacking and environment setup.

Tokens
1.8K
Snippets
10
Records
13
Agent score
72%

What's inside Postgres.ai

  1. Install build dependencies for PostgreSQL on Ubuntu/Debian

    Install the necessary build tools and libraries required to compile PostgreSQL from source on Ubuntu or Debian-based systems.
    sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison \
  libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache docbook \
  docbook-dsssl docbook-xsl docbook-xml docbook-defguide openjade \
  libxml2-utils sgml-data

  2. Document database objects and SQL code

    Use a combination of database comments and inline code comments for maintainability:

    • Object Comments: Use the comment command to add descriptions to columns, tables, and other objects. Keep these short, precise, and under 1024 characters. Focus on the purpose of the object rather than the implementation. For enums or constrained fields, list the valid values in the comment.
    • Inline Comments: Use C-style comments /* ... */ for detailed explanations within SQL statements.
    • Formatting: Use lowercase SQL keywords (instead of uppercase) and utilize spaces and line breaks to improve the readability of complex statements.
    -- Example of object documentation
COMMENT ON COLUMN users.status IS 'User account status. Valid values: active, suspended, deleted';

/* 
   Complex query to calculate monthly churn 
   based on subscription end dates 
*/
select count(*) 
from subscriptions 
where end_date < now();

  3. Follow PostgreSQL commit message and patch standards

    PostgreSQL requires comprehensive, detailed commit messages without emojis. When creating patches or diffs, explicitly exclude AI tooling files (like .cursor/, CLAUDE.md, and .cursorrules) and IDE-specific files.
    # Exclude AI tooling files from patches
git diff -- . ':(exclude).cursor' ':(exclude)CLAUDE.md' ':(exclude).cursorrules'

  4. Write a compliant SQL query

    Compare the following 'Good' vs 'Bad' examples to ensure compliance with the style guide. The 'Good' example demonstrates lowercase keywords, explicit aliases, explicit joins, and proper indentation.
    -- GOOD
select
  t.client_id as client_id,
  date(t.created_at) as day
from telemetry as t
inner join users as u
  on t.user_id = u.id
where
  t.submission_date > '2019-07-01'
  and t.sample_id = '10'
group by t.client_id, day;

-- BAD
SELECT t.client_id, DATE(t.created_at) day
FROM telemetry t, users u
WHERE t.user_id = u.id AND t.submission_date > '2019-07-01'
GROUP BY 1, 2;

  5. Format SQL queries for readability

    Adhere to the following formatting standards for SQL queries:

    • Alignment: Root keywords should be left-aligned. Arguments must be indented relative to the root keyword.
    • Line Breaks: Place root keywords on their own line (unless they have a single argument). For multi-argument clauses, place one argument per line.
    • Boolean Operators: Place AND and OR operators at the beginning of the line.
    • Parentheses: The opening parenthesis should end the line, the closing parenthesis should align with the starting line, and the contents should be indented.
    • Comments: Use /* ... */ for multi-line block descriptions and -- for single-line notes.
    -- Root keywords left-aligned, arguments indented
select
  client_id,
  submission_date
from main_summary
where
  sample_id = '42'
  and submission_date > '20180101'
limit 10;

-- Parentheses formatting
with sample as (
  select
    client_id,
    submission_date
  from main_summary
  where sample_id = '42'
)

  6. Apply core SQL naming and syntax rules

    Follow these core rules for SQL development to ensure consistency and readability:

    • Casing: Use lowercase for SQL keywords and snake_case for all identifiers (avoid CamelCase).
    • Identifiers: Names must start with a letter, may not end with an underscore, and should only contain letters, numbers, and underscores.
    • Explicitness: Always use the AS keyword for aliases and explicitly specify JOIN types.
    • Dates: Use the ISO 8601 date format: yyyy-mm-ddThh:mm:ss.sssss.
    • Foreign Keys: Name foreign keys using the singular table name plus the _id suffix (e.g., user_id to reference the users table).
    • Aliases: Use meaningful aliases that reflect the data rather than single letters.

  7. Apply PostgreSQL naming conventions and identifier rules

    Maintain consistency and avoid system conflicts by following these naming rules:

    • Casing: Use snake_case for all identifiers.
    • Tables: Use plural names (e.g., users, blog_posts).
    • Columns: Use singular names (e.g., email, status).
    • Constraints: Ensure names are unique and do not exceed 63 characters.
    • Reserved Words: Avoid using SQL reserved words in identifiers.

  8. Configure and build PostgreSQL for development

    When building PostgreSQL for hacking/development, use specific flags to enable assertions and debugging. It is critical to use the --prefix flag with a descriptive path in /tmp (e.g., /tmp/pg18_featureXXX) to prevent conflicts with system-wide PostgreSQL installations.
    # Full build sequence
./configure \
  --enable-depend \
  --enable-cassert \
  --enable-debug \
  --prefix=/tmp/pg18_featureXXX \
  --with-openssl

make -j8
make install

  9. Implement SQL best practices for queries

    Optimize query structure using these best practices:

    • CTEs: Use Common Table Expressions (CTEs) instead of nested subqueries.
    • GROUP BY: Use explicit column names in GROUP BY clauses. The only exception is when using complex expressions, where using positional numbers (e.g., GROUP BY 1) is acceptable to avoid repetition.
    • Functions: Treat functions as identifiers by using lowercase (e.g., date_trunc() instead of DATE_TRUNC()).
    -- Example of the GROUP BY exception for complex expressions
select
  date_trunc('minute', xact_start) as xact_start_minute,
  count(*)
from pg_stat_activity
group by 1
order by 1;

  10. Test and debug PostgreSQL source changes

    Use the following commands to verify changes and debug the PostgreSQL engine. Ensure the build is configured with --enable-debug for GDB usage.
    # Run all regression tests
make check

# Run specific tests
make check TESTS="test_name"

# Clean and rebuild
make clean && make -j8

# Debug with gdb
gdb --args postgres -D /path/to/data

  11. Select optimal PostgreSQL data types for schema design

    Follow these data type preferences to ensure scalability and precision in PostgreSQL schemas:

    • Primary Keys: Use int8 ... generated always as identity instead of int4 or serial.
    • Timestamps: Prefer timestamptz (timestamp with time zone) over timestamp.
    • Strings: Prefer text over varchar.
    • Unique Identifiers: Use UUIDv7 (via the uuidv7() function) for PostgreSQL 18+.
    • Currency: Never use the money data type; instead, store monetary values as cents or the smallest currency unit (e.g., using int8).
    -- Recommended Primary Key definition
CREATE TABLE users (
    id int8 GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at timestamptz DEFAULT now(),
    bio text,
    user_uuid uuid DEFAULT uuidv7(),
    balance_cents int8
);

  12. Design PostgreSQL tables according to style guidelines

    When designing tables, follow these structural rules:

    • Primary Keys: Always include an id column of type identity generated always.
    • Documentation: Always add table comments using the comment on table... command.
    • Schema: Default to the public schema, but include the schema name in queries for clarity.
    • Naming: Use singular table names.