# Code Style and Conventions Standards for PostgreSQL
This document outlines the code style and conventions standards for PostgreSQL development. Following these guidelines ensures code consistency, readability, maintainability, and performance. It's intended to serve as a definitive guide for developers and an information source for AI coding assistants. These standards are based on the latest PostgreSQL features and best practices.
## I. General Principles
### 1. Consistency
* **Do This:** Adhere to the established conventions within a project or the PostgreSQL community. Consistency reduces cognitive load and allows developers to understand and modify code more easily.
* **Don't Do This:** Introduce arbitrary stylistic variations without a clear justification.
### 2. Readability
* **Do This:** Write code that is easy to understand. Use meaningful names, comments where necessary, and a clear, logical structure.
* **Don't Do This:** Write overly complex or obfuscated code that is difficult to decipher.
### 3. Maintainability
* **Do This:** Design code that is easy to modify and extend. Consider future changes and potential refactoring. Use modular designs.
* **Don't Do This:** Write monolithic code blocks that are difficult to isolate and modify.
### 4. Performance
* **Do This:** Write code with performance in mind. Select appropriate data types, use indexing effectively, and optimize queries.
* **Don't Do This:** Ignore performance considerations during development, leading to slow queries and inefficient resource utilization. Premature optimization is bad, but blatant inefficiency is worse.
### 5. Security
* **Do This:** Follow security best practices to protect against SQL injection, data breaches, and other vulnerabilities. Use parameterized queries. Understand and apply least privilege.
* **Don't Do This:** Write code that exposes the database to security risks. Neglect input validation.
## II. Formatting
### 1. Indentation
* **Do This:** Use 4 spaces for indentation. Avoid tabs. Configure your editor to automatically convert tabs to spaces.
* **Why:** Consistent indentation improves readability.
* **Example:**
"""sql
CREATE FUNCTION calculate_total(price NUMERIC, quantity INTEGER)
RETURNS NUMERIC AS $$
DECLARE
total NUMERIC;
BEGIN
total := price * quantity;
RETURN total;
END;
$$ LANGUAGE plpgsql;
"""
### 2. Line Length
* **Do This:** Limit lines to a maximum of 80-120 characters. Break long lines into multiple shorter lines.
* **Why:** Long lines are difficult to read, especially on smaller screens.
* **Example:**
"""sql
CREATE TABLE very_long_table_name (
id SERIAL PRIMARY KEY,
very_long_column_name VARCHAR(255) NOT NULL,
another_very_long_column_name TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
"""
### 3. Whitespace
* **Do This:** Use whitespace to separate logical code blocks and operators. Add a newline character at the end of each file.
* **Why:** Whitespace improves readability and visual structure.
* **Example:**
"""sql
SELECT
customer_id,
SUM(order_total) AS total_spent
FROM
orders
WHERE
order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
customer_id
ORDER BY
total_spent DESC;
"""
### 4. Case Sensitivity
* **Do This:** Use lowercase for SQL keywords, function names, and table/column names (unless identifiers are quoted). Be consistent.
* **Why:** PostgreSQL is case-insensitive for unquoted identifiers, but using lowercase is a common convention.
* **Don't Do This:** Mix uppercase and lowercase without a clear reason.
### 5. Comments
* **Do This:** Use comments to explain complex logic, non-obvious code sections, and design decisions.
* **Why:** Comments help other developers understand the code's intent.
* **Types of Comments:**
* **Single-line:** "-- This is a single-line comment"
* **Multi-line:** "/* This is a multi-line comment */"
* **Example:**
"""sql
-- Calculate the average order value for new customers
SELECT
AVG(order_total)
FROM
orders
WHERE
customer_id IN (SELECT customer_id FROM customers WHERE signup_date > CURRENT_DATE - INTERVAL '3 months');
"""
## III. Naming Conventions
### 1. Tables
* Standard: "table_name" (lowercase, plural nouns, separated by underscores)
* Example: "customers", "order_items", "product_categories"
### 2. Columns
* Standard: "column_name" (lowercase, singular nouns or adjectives, separated by underscores)
* Example: "first_name", "last_name", "order_date", "product_id", "is_active"
### 3. Indexes
* Standard: "table_name_column_name_idx" or "table_name_column_name1_column_name2_idx" (or "table_name_pkey" for primary key indexes, "table_name_fkey" for foreign key constraint indexes)
* Example: "customers_last_name_idx", "orders_customer_id_order_date_idx", "products_pkey", "orders_customer_id_fkey"
### 4. Constraints
* Standard: "table_name_column_name_constraint_type"
* Example: "customers_email_unique", "orders_order_date_not_null"
### 5. Functions and Procedures
* Standard: "function_name" (lowercase, verb or verb phrase, separated by underscores)
* Example: "calculate_discount", "get_customer_orders", "update_product_inventory"
### 6. Sequences
* Standard: "table_name_column_name_seq"
* Example: "customers_customer_id_seq", "products_product_id_seq"
### 7. Views
* Standard: "view_name" (lowercase, noun or noun phrase, separated by underscores)
* Prefix with "v_" if the naming convention requires it to differentiate from tables.
* Example: "customer_order_summary", "active_products", "v_product_sales_report"
### 8. Data Types
* Use descriptive and appropriate data types. Prefer "NUMERIC" over "FLOAT" for financial data. Use "TEXT" over "VARCHAR" if there's no maximum length. Use "TIMESTAMPTZ" (timestamp with time zone) over "TIMESTAMP" for datetime values unless there's a very specific reason not to.
* Example:
"""sql
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name TEXT NOT NULL,
price NUMERIC(10, 2) NOT NULL, -- Precise financial values
created_at TIMESTAMPTZ DEFAULT NOW()
);
"""
### 9. Avoid Reserved Words
* **Do This:** Avoid using PostgreSQL reserved words as identifiers (table names, column names, etc.).
* **Don't Do This:** Use reserved words without quoting them, which can lead to syntax errors or unexpected behavior.
* **Check:** Refer to the PostgreSQL documentation for a list of reserved words. Use quoting if you *absolutely* must use a reserved word.
## IV. SQL Constructs
### 1. JOINs
* **Do This:** Use explicit "JOIN" syntax ("INNER JOIN", "LEFT JOIN", "RIGHT JOIN", "FULL OUTER JOIN"). Specify the "ON" condition clearly.
* **Why:** Explicit "JOIN" syntax is more readable and less prone to errors than implicit joins (listing tables in the "FROM" clause and specifying the join condition in the "WHERE" clause).
"""sql
-- Good: Explicit JOIN
SELECT
o.order_id,
c.first_name,
c.last_name
FROM
orders o
INNER JOIN
customers c ON o.customer_id = c.customer_id;
-- Bad: Implicit JOIN (avoid)
SELECT
o.order_id,
c.first_name,
c.last_name
FROM
orders o, customers c
WHERE
o.customer_id = c.customer_id;
"""
### 2. WHERE Clause
* **Do This:** Use parentheses to clarify complex "WHERE" clause conditions.
* **Why:** Parentheses improve readability and prevent ambiguity.
* Example:
"""sql
SELECT
product_name,
price
FROM
products
WHERE
(category_id = 1 OR category_id = 2)
AND price > 100;
"""
### 3. Subqueries
* **Do This:** Use subqueries judiciously. Consider using "WITH" clauses (Common Table Expressions - CTEs) for complex subqueries to improve readability.
* **Why:** CTEs make complex queries easier to understand.
* Example:
"""sql
-- Using a CTE
WITH high_value_customers AS (
SELECT
customer_id
FROM
orders
GROUP BY
customer_id
HAVING
SUM(order_total) > 1000
)
SELECT
c.first_name,
c.last_name
FROM
customers c
JOIN
high_value_customers hvc ON c.customer_id = hvc.customer_id;
-- Equivalent subquery (less readable)
SELECT
c.first_name,
c.last_name
FROM
customers c
WHERE
c.customer_id IN (
SELECT
customer_id
FROM
orders
GROUP BY
customer_id
HAVING
SUM(order_total) > 1000
);
"""
### 4. Transactions
* **Do This:** Use explicit transactions ("BEGIN", "COMMIT", "ROLLBACK") to ensure data consistency. Handle potential errors and roll back transactions when necessary.
* **Why:** Transactions guarantee atomicity, consistency, isolation, and durability (ACID properties).
* Example:
"""sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;
COMMIT; -- Or ROLLBACK; if an error occurs
"""
### 5. NULL Handling
* **Do This:** Use "IS NULL" and "IS NOT NULL" to check for null values. Use "COALESCE()" to provide default values when a value might be null.
* **Why:** Direct comparisons with "NULL" (e.g., "column = NULL") will always evaluate to false.
* Example:
"""sql
SELECT
product_name,
COALESCE(description, 'No description available') AS description
FROM
products
WHERE
description IS NULL;
"""
### 6. Aggregate Functions
* **Do This:** Use "GROUP BY" clauses appropriately when using aggregate functions ("SUM", "AVG", "COUNT", "MIN", "MAX"). Be aware of "FILTER (WHERE ...)" for conditional aggregation.
* **Why:** "GROUP BY" allows you to aggregate data based on specific columns. The filter clause allows you to add conditions to the aggregate function.
* Example:
"""sql
SELECT
category_id,
AVG(price) FILTER (WHERE is_discounted = TRUE) AS average_discounted_price,
COUNT(*)
FROM
products
GROUP BY
category_id;
"""
### 7. "EXPLAIN" and Query Optimization
* **Do This:** Use "EXPLAIN" to analyze query execution plans and identify potential performance bottlenecks. Pay attention to sequential scans, missing indexes, and inefficient join strategies. Use "EXPLAIN ANALYZE" in a development environment to get more detailed performance information.
* **Why:** "EXPLAIN" helps you understand how PostgreSQL is executing your queries, so you can optimize them.
* Example:
"""sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2024-01-01';
"""
### 8. Prepared Statements
* **Do This:** Use prepared statements for queries that are executed repeatedly with different parameters. This improves performance by reducing parsing and planning overhead.
* Prefer server-side prepared statements over client-side emulation of prepared statements.
* Example:
"""sql
PREPARE get_product(INT) AS
SELECT product_name, price FROM products WHERE product_id = $1;
EXECUTE get_product(10);
EXECUTE get_product(25);
DEALLOCATE get_product;
"""
### 9. Window Functions
* **Do This:** Leverage window functions for calculations that involve a set of rows related to the current row (e.g., running totals, rankings). Be aware of the "PARTITION BY" and "ORDER BY" clauses within the window function.
* **Why:** Window functions can perform complex calculations with better performance than traditional subqueries or procedural code.
* Example:
"""sql
SELECT
order_id,
customer_id,
order_date,
order_total,
RANK() OVER (PARTITION BY customer_id ORDER BY order_total DESC) AS customer_rank
FROM
orders;
"""
### 10. Common Table Expressions (CTEs) - WITH Clause
* **Do this:** Utilize CTEs to break down complex queries into smaller, more manageable, and readable units.
* **Why:** CTEs enhance code clarity, promote modularity, and can sometimes improve performance by allowing the query optimizer to better understand the query's structure. Recursive CTEs are very powerful but can be complex and need careful planning. "MATERIALIZED" option can be beneficial to improve the performance of CTEs, but it also adds overhead and should be used carefully.
* Example:
"""sql
WITH MonthlySales AS (
SELECT
EXTRACT(MONTH FROM order_date) AS month,
SUM(order_total) AS total_sales
FROM
orders
WHERE EXTRACT(YEAR FROM order_date) = 2024
GROUP BY 1
)
SELECT
month,
total_sales
FROM
MonthlySales
ORDER BY
month;
"""
## V. PL/pgSQL Specific Standards
### 1. Error Handling
* **Do This:** Use "BEGIN ... EXCEPTION ... END" blocks to handle exceptions gracefully. Use "RAISE NOTICE", "RAISE EXCEPTION", and "RAISE WARNING" as appropriate.
* **Why:** Proper error handling prevents unexpected program termination and provides useful debugging information.
"""sql
CREATE OR REPLACE FUNCTION update_product_price(product_id INT, new_price NUMERIC)
RETURNS VOID AS $$
BEGIN
UPDATE products SET price = new_price WHERE product_id = update_product_price.product_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Product with ID % not found', product_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE WARNING 'An error occurred: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;
"""
### 2. Parameterized Queries
* **Do This:** Always use parameterized queries to prevent SQL injection vulnerabilities. Use "$1", "$2", etc. to refer to parameters.
* **Why:** Parameterized queries prevent attackers from injecting malicious SQL code.
* Example:
"""sql
CREATE OR REPLACE FUNCTION get_customer_by_id(customer_id INT)
RETURNS SETOF customers AS $$
BEGIN
RETURN QUERY SELECT * FROM customers WHERE customers.customer_id = get_customer_by_id.customer_id;
END;
$$ LANGUAGE plpgsql;
"""
### 3. Transaction Management within Functions
* **Do This:** If a PL/pgSQL function modifies data, consider managing transactions explicitly within the function using "BEGIN", "COMMIT", and "ROLLBACK", especially if the function performs multiple operations that should be treated as a single atomic unit. Ensure that nested transactions are handled carefully.
* **Why:** Explicit transaction management ensures data consistency.
* Be aware of the isolation level and how it impacts concurrency.
"""sql
CREATE OR REPLACE FUNCTION transfer_funds(from_account INT, to_account INT, amount NUMERIC)
RETURNS BOOLEAN AS $$
DECLARE
success BOOLEAN := FALSE;
BEGIN
BEGIN
UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
COMMIT;
success := TRUE;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE WARNING 'Transaction failed: %', SQLERRM;
END;
RETURN success;
END;
$$ LANGUAGE plpgsql;
"""
### 4. Security Definer
* **Do This:** Be cautious when using the "SECURITY DEFINER" option for functions. This allows the function to execute with the privileges of the user who created it, which can be a security risk if not handled properly. Carefully consider the function's behavior and the potential for privilege escalation. If possible, use "SECURITY INVOKER" instead and grant specific privileges to the users who need to execute the function.
* **Why:** Improper use of "SECURITY DEFINER" can create security vulnerabilities.
### 5. Avoid Cursors When Possible
* **Do This:** Consider set-based operations, window functions, or array manipulation for better performance before resorting to explicit cursors.
* **Why:** Cursors can be significantly slower than set-based operations, especially for large datasets. Cursors are appropriate when operations need to be done one row at a time.
* Example showing a non-cursor-based operation:
"""sql
-- Process multiple orders at once
CREATE OR REPLACE FUNCTION process_orders(p_order_ids INT[])
RETURNS VOID AS $$
BEGIN
-- Update the status of all orders in the array
UPDATE orders
SET status = 'Processed'
WHERE order_id = ANY(p_order_ids);
END;
$$ LANGUAGE plpgsql;
"""
### 6. Data Type Considerations
* **Do This:** Be mindful of data type conversions, especially when comparing values or passing parameters to functions. Explicitly cast values when necessary to avoid unexpected behavior.
* **Why:** Implicit data type conversions can lead to unexpected results and performance problems.
### 7. Using "RETURNS TABLE"
* **Do This:** When a PL/pgSQL function is designed to return multiple rows with a predefined structure, use the "RETURNS TABLE" syntax. This declares the function's output structure, enhancing readability and maintainability.
* **Why:** "RETURNS TABLE" provides a clear contract for the function's output and allows you to refer to the output columns directly within the function's logic.
"""sql
CREATE OR REPLACE FUNCTION get_products_by_category(category_id INT)
RETURNS TABLE (product_id INT, product_name TEXT, price NUMERIC) AS $$
BEGIN
RETURN QUERY SELECT p.product_id, p.product_name, p.price
FROM products p
WHERE p.category_id = get_products_by_category.category_id;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM get_products_by_category(1);
"""
## VI. PostgreSQL-Specific Best Practices
### 1. Use "pg_stat_statements"
* **Do This:** Install and configure the "pg_stat_statements" extension to track query performance statistics. Analyze the output to identify slow queries and optimize them.
* **Why:** "pg_stat_statements" provides valuable insights into query performance.
### 2. Regularly Vacuum and Analyze
* **Do This:** Schedule regular "VACUUM" and "ANALYZE" operations to maintain database performance. Autovacuum is usually sufficient, but monitor and adjust autovacuum settings as needed.
* **Why:** "VACUUM" reclaims space occupied by deleted rows, and "ANALYZE" updates table statistics used by the query planner.
### 3. Connection Pooling
* **Do This:** Use connection pooling to reduce connection overhead. Tools like "pgBouncer" or "pgpool-II" can be used for connection pooling.
* **Why:** Creating new connections to the database is an expensive operation.
### 4. Partitioning
* **Do This:** Consider table partitioning for large tables to improve query performance and manageability. Choose a partitioning strategy that aligns with your query patterns. Declarative partitioning is preferred.
* **Why:** Partitioning divides a large table into smaller, more manageable pieces.
### 5. JSONB Data Type
* **Do This:** Use the "JSONB" data type for storing JSON data in a binary format. Use indexing on "JSONB" columns for efficient querying. Use the "@>" (contains) operator to search JSON documents.
* **Why:** "JSONB" is more efficient than "JSON" for storage and querying.
### 6. Full-Text Search
* **Do This:** Use PostgreSQL's built-in full-text search capabilities for text-based searches. Create indexes on the "tsvector" column.
* **Why:** Full-text search is much faster than "LIKE" queries for searching through large amounts of text.
### 7. Extensions
* **Do This:** Explore and use PostgreSQL extensions to extend the database's functionality. Examples include "PostGIS" for geospatial data, "uuid-ossp" for generating UUIDs, and "pg_trgm" for trigram indexing.
* **Why:** Extensions provide additional features and capabilities that are not built into the core database.
### 8. Asynchronous Operations via "pg_amqp" or similar
*Consider leveraging extensions such as "pg_amqp" or similar mechanisms to offload time-consuming or non-critical tasks to asynchronous processing queues.
*Why: Asynchronous operations prevent blocking the main database processes, improving responsiveness and overall system performance.
### 9. Leveraging Unlogged Tables
* For temporary datasets or data that can be recreated, consider using "UNLOGGED" tables for potentially faster write operations.
* Why: "UNLOGGED" tables skip writing operations to the write-ahead log (WAL), thus providing a performance gain. However, they are not crash-safe and will be truncated after a database crash or unclean shutdown. Use with caution and only when data loss is tolerable.
This coding standards document provides a comprehensive guide to writing high-quality PostgreSQL code. By following these guidelines, developers can ensure code consistency, readability, maintainability, performance, and security.
danielsogl
Created Mar 6, 2025
This guide explains how to effectively use .clinerules with Cline, the AI-powered coding assistant.
The .clinerules file is a powerful configuration file that helps Cline understand your project's requirements, coding standards, and constraints. When placed in your project's root directory, it automatically guides Cline's behavior and ensures consistency across your codebase.
Place the .clinerules file in your project's root directory. Cline automatically detects and follows these rules for all files within the project.
# Project Overview project: name: 'Your Project Name' description: 'Brief project description' stack: - technology: 'Framework/Language' version: 'X.Y.Z' - technology: 'Database' version: 'X.Y.Z'
# Code Standards standards: style: - 'Use consistent indentation (2 spaces)' - 'Follow language-specific naming conventions' documentation: - 'Include JSDoc comments for all functions' - 'Maintain up-to-date README files' testing: - 'Write unit tests for all new features' - 'Maintain minimum 80% code coverage'
# Security Guidelines security: authentication: - 'Implement proper token validation' - 'Use environment variables for secrets' dataProtection: - 'Sanitize all user inputs' - 'Implement proper error handling'
Be Specific
Maintain Organization
Regular Updates
# Common Patterns Example patterns: components: - pattern: 'Use functional components by default' - pattern: 'Implement error boundaries for component trees' stateManagement: - pattern: 'Use React Query for server state' - pattern: 'Implement proper loading states'
Commit the Rules
.clinerules in version controlTeam Collaboration
Rules Not Being Applied
Conflicting Rules
Performance Considerations
# Basic .clinerules Example project: name: 'Web Application' type: 'Next.js Frontend' standards: - 'Use TypeScript for all new code' - 'Follow React best practices' - 'Implement proper error handling' testing: unit: - 'Jest for unit tests' - 'React Testing Library for components' e2e: - 'Cypress for end-to-end testing' documentation: required: - 'README.md in each major directory' - 'JSDoc comments for public APIs' - 'Changelog updates for all changes'
# Advanced .clinerules Example project: name: 'Enterprise Application' compliance: - 'GDPR requirements' - 'WCAG 2.1 AA accessibility' architecture: patterns: - 'Clean Architecture principles' - 'Domain-Driven Design concepts' security: requirements: - 'OAuth 2.0 authentication' - 'Rate limiting on all APIs' - 'Input validation with Zod'
# Database: Create RLS policies You're a Supabase Postgres expert in writing row level security policies. Your purpose is to generate a policy with the constraints given by the user. You should first retrieve schema information to write policies for, usually the 'public' schema. The output should use the following instructions: - The generated SQL must be valid SQL. - You can use only CREATE POLICY or ALTER POLICY queries, no other queries are allowed. - Always use double apostrophe in SQL strings (eg. 'Night''s watch') - You can add short explanations to your messages. - The result should be a valid markdown. The SQL code should be wrapped in ``` (including sql language tag). - Always use "auth.uid()" instead of "current_user". - SELECT policies should always have USING but not WITH CHECK - INSERT policies should always have WITH CHECK but not USING - UPDATE policies should always have WITH CHECK and most often have USING - DELETE policies should always have USING but not WITH CHECK - Don't use `FOR ALL`. Instead separate into 4 separate policies for select, insert, update, and delete. - The policy name should be short but detailed text explaining the policy, enclosed in double quotes. - Always put explanations as separate text. Never use inline SQL comments. - If the user asks for something that's not related to SQL policies, explain to the user that you can only help with policies. - Discourage `RESTRICTIVE` policies and encourage `PERMISSIVE` policies, and explain why. The output should look like this: ```sql CREATE POLICY "My descriptive policy." ON books FOR INSERT to authenticated USING ( (select auth.uid()) = author_id ) WITH ( true ); ``` Since you are running in a Supabase environment, take note of these Supabase-specific additions below. ## Authenticated and unauthenticated roles Supabase maps every request to one of the roles: - `anon`: an unauthenticated request (the user is not logged in) - `authenticated`: an authenticated request (the user is logged in) These are actually [Postgres Roles](/docs/guides/database/postgres/roles). You can use these roles within your Policies using the `TO` clause: ```sql create policy "Profiles are viewable by everyone" on profiles for select to authenticated, anon using ( true ); -- OR create policy "Public profiles are viewable only by authenticated users" on profiles for select to authenticated using ( true ); ``` Note that `for ...` must be added after the table but before the roles. `to ...` must be added after `for ...`: ### Incorrect ```sql create policy "Public profiles are viewable only by authenticated users" on profiles to authenticated for select using ( true ); ``` ### Correct ```sql create policy "Public profiles are viewable only by authenticated users" on profiles for select to authenticated using ( true ); ``` ## Multiple operations PostgreSQL policies do not support specifying multiple operations in a single FOR clause. You need to create separate policies for each operation. ### Incorrect ```sql create policy "Profiles can be created and deleted by any user" on profiles for insert, delete -- cannot create a policy on multiple operators to authenticated with check ( true ) using ( true ); ``` ### Correct ```sql create policy "Profiles can be created by any user" on profiles for insert to authenticated with check ( true ); create policy "Profiles can be deleted by any user" on profiles for delete to authenticated using ( true ); ``` ## Helper functions Supabase provides some helper functions that make it easier to write Policies. ### `auth.uid()` Returns the ID of the user making the request. ### `auth.jwt()` Returns the JWT of the user making the request. Anything that you store in the user's `raw_app_meta_data` column or the `raw_user_meta_data` column will be accessible using this function. It's important to know the distinction between these two: - `raw_user_meta_data` - can be updated by the authenticated user using the `supabase.auth.update()` function. It is not a good place to store authorization data. - `raw_app_meta_data` - cannot be updated by the user, so it's a good place to store authorization data. The `auth.jwt()` function is extremely versatile. For example, if you store some team data inside `app_metadata`, you can use it to determine whether a particular user belongs to a team. For example, if this was an array of IDs: ```sql create policy "User is in team" on my_table to authenticated using ( team_id in (select auth.jwt() -> 'app_metadata' -> 'teams')); ``` ### MFA The `auth.jwt()` function can be used to check for [Multi-Factor Authentication](/docs/guides/auth/auth-mfa#enforce-rules-for-mfa-logins). For example, you could restrict a user from updating their profile unless they have at least 2 levels of authentication (Assurance Level 2): ```sql create policy "Restrict updates." on profiles as restrictive for update to authenticated using ( (select auth.jwt()->>'aal') = 'aal2' ); ``` ## RLS performance recommendations Every authorization system has an impact on performance. While row level security is powerful, the performance impact is important to keep in mind. This is especially true for queries that scan every row in a table - like many `select` operations, including those using limit, offset, and ordering. Based on a series of [tests](https://github.com/GaryAustin1/RLS-Performance), we have a few recommendations for RLS: ### Add indexes Make sure you've added [indexes](/docs/guides/database/postgres/indexes) on any columns used within the Policies which are not already indexed (or primary keys). For a Policy like this: ```sql create policy "Users can access their own records" on test_table to authenticated using ( (select auth.uid()) = user_id ); ``` You can add an index like: ```sql create index userid on test_table using btree (user_id); ``` ### Call functions with `select` You can use `select` statement to improve policies that use functions. For example, instead of this: ```sql create policy "Users can access their own records" on test_table to authenticated using ( auth.uid() = user_id ); ``` You can do: ```sql create policy "Users can access their own records" on test_table to authenticated using ( (select auth.uid()) = user_id ); ``` This method works well for JWT functions like `auth.uid()` and `auth.jwt()` as well as `security definer` Functions. Wrapping the function causes an `initPlan` to be run by the Postgres optimizer, which allows it to "cache" the results per-statement, rather than calling the function on each row. Caution: You can only use this technique if the results of the query or function do not change based on the row data. ### Minimize joins You can often rewrite your Policies to avoid joins between the source and the target table. Instead, try to organize your policy to fetch all the relevant data from the target table into an array or set, then you can use an `IN` or `ANY` operation in your filter. For example, this is an example of a slow policy which joins the source `test_table` to the target `team_user`: ```sql create policy "Users can access records belonging to their teams" on test_table to authenticated using ( (select auth.uid()) in ( select user_id from team_user where team_user.team_id = team_id -- joins to the source "test_table.team_id" ) ); ``` We can rewrite this to avoid this join, and instead select the filter criteria into a set: ```sql create policy "Users can access records belonging to their teams" on test_table to authenticated using ( team_id in ( select team_id from team_user where user_id = (select auth.uid()) -- no join ) ); ``` ### Specify roles in your policies Always use the Role of inside your policies, specified by the `TO` operator. For example, instead of this query: ```sql create policy "Users can access their own records" on rls_test using ( auth.uid() = user_id ); ``` Use: ```sql create policy "Users can access their own records" on rls_test to authenticated using ( (select auth.uid()) = user_id ); ``` This prevents the policy `( (select auth.uid()) = user_id )` from running for any `anon` users, since the execution stops at the `to authenticated` step.
# Database: Create migration You are a Postgres Expert who loves creating secure database schemas. This project uses the migrations provided by the Supabase CLI. ## Creating a migration file Given the context of the user's message, create a database migration file inside the folder `supabase/migrations/`. The file MUST following this naming convention: The file MUST be named in the format `YYYYMMDDHHmmss_short_description.sql` with proper casing for months, minutes, and seconds in UTC time: 1. `YYYY` - Four digits for the year (e.g., `2024`). 2. `MM` - Two digits for the month (01 to 12). 3. `DD` - Two digits for the day of the month (01 to 31). 4. `HH` - Two digits for the hour in 24-hour format (00 to 23). 5. `mm` - Two digits for the minute (00 to 59). 6. `ss` - Two digits for the second (00 to 59). 7. Add an appropriate description for the migration. For example: ``` 20240906123045_create_profiles.sql ``` ## SQL Guidelines Write Postgres-compatible SQL code for Supabase migration files that: - Includes a header comment with metadata about the migration, such as the purpose, affected tables/columns, and any special considerations. - Includes thorough comments explaining the purpose and expected behavior of each migration step. - Write all SQL in lowercase. - Add copious comments for any destructive SQL commands, including truncating, dropping, or column alterations. - When creating a new table, you MUST enable Row Level Security (RLS) even if the table is intended for public access. - When creating RLS Policies - Ensure the policies cover all relevant access scenarios (e.g. select, insert, update, delete) based on the table's purpose and data sensitivity. - If the table is intended for public access the policy can simply return `true`. - RLS Policies should be granular: one policy for `select`, one for `insert` etc) and for each supabase role (`anon` and `authenticated`). DO NOT combine Policies even if the functionality is the same for both roles. - Include comments explaining the rationale and intended behavior of each security policy The generated SQL code should be production-ready, well-documented, and aligned with Supabase's best practices.
# Postgres SQL Style Guide ## General - Use lowercase for SQL reserved words to maintain consistency and readability. - Employ consistent, descriptive identifiers for tables, columns, and other database objects. - Use white space and indentation to enhance the readability of your code. - Store dates in ISO 8601 format (`yyyy-mm-ddThh:mm:ss.sssss`). - Include comments for complex logic, using '/_ ... _/' for block comments and '--' for line comments. ## Naming Conventions - Avoid SQL reserved words and ensure names are unique and under 63 characters. - Use snake_case for tables and columns. - Prefer plurals for table names - Prefer singular names for columns. ## Tables - Avoid prefixes like 'tbl\_' and ensure no table name matches any of its column names. - Always add an `id` column of type `identity generated always` unless otherwise specified. - Create all tables in the `public` schema unless otherwise specified. - Always add the schema to SQL queries for clarity. - Always add a comment to describe what the table does. The comment can be up to 1024 characters. ## Columns - Use singular names and avoid generic names like 'id'. - For references to foreign tables, use the singular of the table name with the `_id` suffix. For example `user_id` to reference the `users` table - Always use lowercase except in cases involving acronyms or when readability would be enhanced by an exception. #### Examples: ```sql create table books ( id bigint generated always as identity primary key, title text not null, author_id bigint references authors (id) ); comment on table books is 'A list of all the books in the library.'; ``` ## Queries - When the query is shorter keep it on just a few lines. As it gets larger start adding newlines for readability - Add spaces for readability. Smaller queries: ```sql select * from employees where end_date is null; update employees set end_date = '2023-12-31' where employee_id = 1001; ``` Larger queries: ```sql select first_name, last_name from employees where start_date between '2021-01-01' and '2021-12-31' and status = 'employed'; ``` ### Joins and Subqueries - Format joins and subqueries for clarity, aligning them with related SQL clauses. - Prefer full table names when referencing tables. This helps for readability. ```sql select employees.employee_name, departments.department_name from employees join departments on employees.department_id = departments.department_id where employees.start_date > '2022-01-01'; ``` ## Aliases - Use meaningful aliases that reflect the data or transformation applied, and always include the 'as' keyword for clarity. ```sql select count(*) as total_employees from employees where end_date is null; ``` ## Complex queries and CTEs - If a query is extremely complex, prefer a CTE. - Make sure the CTE is clear and linear. Prefer readability over performance. - Add comments to each block. ```sql with department_employees as ( -- Get all employees and their departments select employees.department_id, employees.first_name, employees.last_name, departments.department_name from employees join departments on employees.department_id = departments.department_id ), employee_counts as ( -- Count how many employees in each department select department_name, count(*) as num_employees from department_employees group by department_name ) select department_name, num_employees from employee_counts order by department_name; ```
# API Integration Standards for PostgreSQL This document outlines the coding standards for integrating PostgreSQL with external APIs and backend services. These standards promote maintainability, performance, and security when building applications that rely on data and functionality outside of the database itself. It focuses on modern approaches compatible with the latest PostgreSQL version. ## 1. Architectural Considerations for API Integration ### 1.1. Standard: Define Clear API Boundaries **Do This:** * Clearly define the responsibilities of PostgreSQL and external APIs. Use PostgreSQL for data persistence, relational logic, and indexing. Offload complex computations, specialized data processing, and external data access to APIs. * Use clear and consistent naming conventions for database functions/procedures interacting with APIs. Prefix them (e.g., "api_", "ext_") to easily identify external API integration code. * Document the contract (input/output) with each API thoroughly. **Don't Do This:** * Overload PostgreSQL with tasks that APIs are better suited for (e.g., image processing, complex machine learning tasks that are not data-intensive). * Embed undocumented or magic API calls directly within SQL queries. **Why:** Defining clear boundaries ensures modularity, easier maintenance, and optimized performance. It avoids turning the database into a monolithic application component. **Example:** """sql -- Good: Function for fetching user profiles from an external API. CREATE OR REPLACE FUNCTION api_get_user_profile(user_id INT) RETURNS JSONB AS $$ BEGIN -- Call external API to get user profile details. -- Using a hypothetical extension for API calls RETURN http_get('https://api.example.com/users/' || user_id)::jsonb; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Error fetching user profile from API: %', SQLERRM; END; $$ LANGUAGE plpgsql; -- Bad: Embedding API logic directly within a complex query. -- SELECT * FROM users WHERE ... AND api_call(...) ... ; -- Avoid! """ ### 1.2. Standard: Asynchronous vs. Synchronous API Interactions **Do This:** * Use asynchronous API calls (e.g., message queues, background workers) where possible to prevent long-running database transactions from blocking other operations. Implement retries and error handling for asynchronous tasks. * For synchronous calls, keep the execution time as short as possible to avoid holding database connections for extended periods. **Don't Do This:** * Make blocking API calls directly within critical transaction paths. This will significantly impact database performance and availability. * Assume API calls will always succeed. Implement robust error handling and retries. **Why:** Asynchronous operations improve scalability and responsiveness. Synchronous operations can lead to deadlocks and performance degradation if not managed carefully. **Example (using pg_amqp or similar queue extensions):** """sql -- Asynchronous API call using a message queue. (Hypothetical Example) CREATE OR REPLACE FUNCTION api_process_user_data(user_id INT) RETURNS VOID AS $$ BEGIN -- Send a message to a queue for processing user data via an external API. PERFORM amqp.publish('process_user_data_queue', json_build_object('user_id', user_id)); -- Hypothetical RETURN; END; $$ LANGUAGE plpgsql; -- Example of a background worker (using pg_background) that consumes from the queue to call the external API -- Code for the background worker would be in a separate file and process the queue. """ ### 1.3. Standard: Data Transformation and Mapping **Do This:** * Define clear data mapping between PostgreSQL data types and API request/response formats (e.g., JSON, XML). Use PostgreSQL's JSONB and XML support effectively. * Validate data received from APIs before inserting it into the database using "CHECK" constraints or other validation mechanisms. * Log API requests and responses for debugging and auditing purposes. **Don't Do This:** * Directly insert untrusted data received from APIs into the database without validation. This can lead to SQL injection and other security vulnerabilities. * Rely on implicit type conversions between PostgreSQL and API data formats. Be explicit. **Why:** Proper data transformation and validation prevent data corruption and security breaches. Logging helps troubleshoot issues and track API usage. **Example:** """sql -- Validating and inserting JSON data from an API. CREATE TABLE api_user_profiles ( user_id INT PRIMARY KEY, profile_data JSONB -- CHECK constraint is appropriate here to require the JSON object ALWAYS conform to a schema ); CREATE OR REPLACE FUNCTION api_import_user_profile(user_id INT, profile_json JSONB) RETURNS VOID AS $$ DECLARE -- Validate JSON data against a schema (hypothetical function). is_valid BOOLEAN; BEGIN -- Validate that the JSON is valid against a schema is_valid := jsonb_matches_schema('{"type": "object", "properties": {"name": {"type": "string"},"email": {"type": "string", "format": "email"} }}', profile_json); IF NOT is_valid THEN RAISE EXCEPTION 'Invalid profile data format.'; END IF; INSERT INTO api_user_profiles (user_id, profile_data) VALUES (user_id, profile_json); RETURN; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Error importing user profile: %', SQLERRM; END; $$ LANGUAGE plpgsql; """ ## 2. Implementation Details ### 2.1. Standard: Choosing the Right API Interaction Method **Do This:** * Evaluate these methods: * **HTTP Requests (using extensions like "http" or "curl"):** Suitable for RESTful APIs. * **Message Queues (using extensions like "pg_amqp" or "pg_kafka"):** Ideal for asynchronous communication. * **Foreign Data Wrappers (FDWs):** For integrating with other databases or data stores directly. * Choose the method that best fits the API's protocol, data format, and communication pattern. **Don't Do This:** * Force a specific integration method because it's familiar. Consider alternatives based on the API's characteristics. * Build custom, ad-hoc solutions when standard extensions and FDWs provide the necessary functionality. **Why:** Selecting the right method simplifies integration, improves performance, and reduces development effort. **Example (using "http" extension for a REST API):** """sql -- Example using the http extension to call a REST API CREATE EXTENSION IF NOT EXISTS http; CREATE OR REPLACE FUNCTION api_get_weather(city TEXT) RETURNS JSONB AS $$ DECLARE api_url TEXT := 'https://api.weatherapi.com/v1/current.json?key=YOUR_API_KEY&q=' || city; response HTTPResponse; BEGIN response := http_get(api_url); IF response.status_code = 200 THEN RETURN response.content::jsonb; ELSE RAISE EXCEPTION 'Weather API error: %', response.content; END IF; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Error fetching weather data: %', SQLERRM; END; $$ LANGUAGE plpgsql; -- SELECT api_get_weather('London'); """ ### 2.2. Standard: Error Handling and Retries **Do This:** * Implement robust error handling for API calls. Catch exceptions, log errors, and implement retry mechanisms with exponential backoff. * Distinguish between transient and permanent errors. Retry transient errors (e.g., network timeouts), and log permanent errors (e.g., invalid API key) for investigation. * Set appropriate timeouts for API calls to prevent indefinite blocking. * Consider using "TRY...CATCH" blocks for error handling within PL/pgSQL functions. **Don't Do This:** * Ignore errors from API calls. At a minimum, log the error so it can be investigated later. * Retry indefinitely without a limit or backoff strategy. This can overload the API or the database. **Why:** Robust error handling ensures resilience and prevents cascading failures. It provides valuable insights into API issues. **Example:** """sql CREATE OR REPLACE FUNCTION api_get_data_with_retry(url TEXT, max_retries INT DEFAULT 3) RETURNS JSONB AS $$ DECLARE response HTTPResponse; retries INT := 0; delay INTERVAL := '1 second'; BEGIN LOOP BEGIN response := http_get(url); IF response.status_code = 200 THEN RETURN response.content::jsonb; ELSE RAISE WARNING 'API call failed with status code: %', response.status_code; -- Check for non-retryable errors here! -- IF response.status_code = 400 THEN RETURN NULL; -- Bad Request (do not retry) END IF; EXCEPTION WHEN OTHERS THEN RAISE WARNING 'API call error: %', SQLERRM; END; retries := retries + 1; IF retries >= max_retries THEN RAISE EXCEPTION 'Max retries exceeded for API call.'; END IF; RAISE NOTICE 'Retrying in %', delay; PERFORM pg_sleep(extract(epoch from delay)); delay := delay * 2; -- Exponential backoff END LOOP; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Failed to get data after multiple retries: %', SQLERRM; END; $$ LANGUAGE plpgsql; """ ### 2.3. Standard: Security Considerations **Do This:** * Store API keys and secrets securely using PostgreSQL's configuration parameters or a dedicated secrets management solution. NEVER hardcode API keys in SQL code. * Use HTTPS for all API calls to encrypt data in transit. * Validate API responses to prevent data injection (e.g., JSON injection). * Implement rate limiting to prevent abuse. * Use least privilege principle when granting permissions to API interaction functions. **Don't Do This:** * Hardcode API keys or secrets in SQL code or store them in plain text in the database. * Trust API responses implicitly. Always validate the data. * Expose your PostgreSQL database directly to the internet without proper firewall and security measures. **Why:** Security is paramount. Protecting API keys, encrypting data, and rate limiting prevent unauthorized access and malicious attacks. **Example:** """sql -- Storing API key securely using postgresql.conf -- In postgresql.conf: -- api.weather_api_key = 'YOUR_API_KEY' -- SQL to retrieve the API key CREATE OR REPLACE FUNCTION api_get_weather_secure(city TEXT) RETURNS JSONB AS $$ DECLARE api_url TEXT := 'https://api.weatherapi.com/v1/current.json?key=' || current_setting('api.weather_api_key') || '&q=' || city; response HTTPResponse; BEGIN response := http_get(api_url); IF response.status_code = 200 THEN RETURN response.content::jsonb; ELSE RAISE EXCEPTION 'Weather API error: %', response.content; END IF; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Error fetching weather data: %', SQLERRM; END; $$ LANGUAGE plpgsql SECURITY DEFINER; -- SECURITY DEFINER crucial for accessing external configurations -- Revoke execute permission from public REVOKE EXECUTE ON FUNCTION api_get_weather_secure(TEXT) FROM PUBLIC; -- Grant access to specific roles GRANT EXECUTE ON FUNCTION api_get_weather_secure(TEXT) TO your_application_role; """ ### 2.4. Standard: Performance Optimization **Do This:** * Cache API responses to reduce the number of API calls, especially for frequently accessed data. Use "MATERIALIZED VIEW" or a custom cache table. * Use connection pooling to minimize the overhead of establishing new connections to APIs. Some HTTP extensions do this internally. * Optimize data transfer by requesting only the necessary fields from the API. Use appropriate query parameters. **Don't Do This:** * Make redundant API calls. Identify opportunities for caching or batching. * Retrieve large amounts of data from APIs when only a small subset is needed. **Why:** Performance optimization improves application responsiveness and reduces API usage costs. **Example (using a materialized view for caching):** """sql CREATE MATERIALIZED VIEW weather_cache AS SELECT city, api_get_weather(city) AS weather_data, NOW() AS last_updated FROM (VALUES ('London'), ('New York'), ('Tokyo')) AS cities(city); CREATE UNIQUE INDEX idx_weather_cache_city ON weather_cache (city); -- Refresh the cache periodically CREATE OR REPLACE FUNCTION refresh_weather_cache() RETURNS VOID AS $$ BEGIN REFRESH MATERIALIZED VIEW CONCURRENTLY weather_cache; RETURN; END; $$ LANGUAGE plpgsql; -- Schedule daily refreshes with pg_cron or a similar scheduler: -- SELECT cron.schedule('0 0 * * *', 'SELECT refresh_weather_cache()'); -- Usage: CREATE OR REPLACE FUNCTION get_weather_from_cache(city TEXT) RETURNS JSONB AS $$ BEGIN RETURN (SELECT weather_data FROM weather_cache WHERE city = get_weather_from_cache.city); EXCEPTION WHEN no_data_found THEN RETURN api_get_weather(city); -- if not in cache, fetch it from the API END; $$ LANGUAGE plpgsql; """ ## 3. Coding Style and Conventions ### 3.1. Standard: Code Formatting and Comments **Do This:** * Use consistent indentation (typically 4 spaces) and line breaks to improve readability. * Add comments to explain complex logic, API calls, and data transformations. * Use meaningful names for variables, functions, and parameters. **Don't Do This:** * Write long, monolithic functions without comments or clear structure. * Use cryptic or ambiguous names. **Why:** Consistent formatting and clear comments make the code easier to understand and maintain. ### 3.2. Standard: Transaction Management **Do This:** * Wrap API calls within explicit transactions when necessary to ensure data consistency. Use "BEGIN", "COMMIT", and "ROLLBACK". * Handle potential errors during API calls gracefully and roll back the transaction if necessary. **Don't Do This:** * Leave transactions open for extended periods of time while waiting for API responses. * Commit transactions before ensuring the success of all related API calls. **Why:** Proper transaction management ensures data integrity and prevents inconsistencies. ### 3.3. Standard: Testing **Do This:** * Write unit tests for API interaction functions to verify that they handle different scenarios correctly (e.g., success, error, timeout). * Use mock APIs or stubs to isolate the database from external dependencies during testing. * Write integration tests to ensure that the database and APIs work together seamlessly. **Don't Do This:** * Skip testing API interaction code. This can lead to unexpected errors and integration issues in production. * Rely solely on manual testing. **Why:** Automated testing improves code quality, reduces the risk of regressions, and facilitates continuous integration and delivery. These API integration standards will help create reliable, secure, and maintainable PostgreSQL applications that integrate effectively with external services. Remember to stay updated with the latest PostgreSQL features and best practices as the ecosystem evolves.
# Core Architecture Standards for PostgreSQL This document outlines the coding standards for the core architecture of PostgreSQL. It aims to provide clear guidance for developers contributing to the core codebase, ensuring maintainability, performance, security, and consistency. The standards reflect modern approaches, patterns, and the latest features of PostgreSQL. ## 1. Fundamental Architectural Patterns PostgreSQL's core architecture is based on a process-based model, where each client connection is handled by a separate server process. This concurrency model heavily relies on shared memory for inter-process communication and data sharing. **Do This:** * Understand the process-based architecture deeply. Familiarize yourself with the following processes: "postgres" (the postmaster), "backend" (server processes), "walwriter", "autovacuum launcher", "stats collector", and "bgwriter". * Design extensions with process isolation in mind. Avoid global state modification to prevent unintended side effects across different backend processes. * Favor shared memory mechanisms for data sharing across backends over file-based communication where performance is critical. **Don't Do This:** * Create singletons or static variables that hold global state without proper consideration for concurrency. This will lead to unexpected behavior and difficult to debug race conditions. * Introduce shared resources without adequate locking mechanisms. * Rely on inter-process communication (IPC) without understanding the potential for deadlocks or race conditions. **Why:** Maintaining a well-defined process model ensures stability and scalability. Properly isolated processes minimize the risk of crashes affecting other connections. ### 1.1 Process Lifecycle Each PostgreSQL backend process follows a well-defined lifecycle: 1. **Startup:** Initialization of process-specific resources and connection to the shared memory. 2. **Authentication:** Verification of the client's identity. 3. **Query Processing:** Parsing, planning, and execution of SQL queries. 4. **Transaction Management:** Ensuring ACID properties of database operations. 5. **Shutdown:** Clean-up of resources and disconnection from shared memory. **Do This:** * Ensure proper resource cleanup in all stages of the lifecycle, especially during error handling. * Use "elog()" with appropriate severity levels for logging events during the lifecycle. * Catch and handle exceptions appropriately throughout the lifecycle. **Don't Do This:** * Leak resources (memory, file descriptors, etc.) during any phase of the process lifecycle. * Ignore errors during startup or shutdown. * Introduce long-running operations inside the authentication phase. **Why:** Strict adherence to the process lifecycle prevents resource exhaustion and ensures a clean state upon process termination. ### 1.2 Shared Memory Management Shared memory provides a crucial mechanism for communication and data sharing between PostgreSQL backend processes. **Do This:** * Use PostgreSQL's shared memory APIs (e.g., "ShmemAlloc()", "ShmemInitStruct()") for allocating and managing shared memory. These functions handle the platform-specific details of shared memory allocation and ensure proper alignment and size constraints. * Protect access to shared memory regions using appropriate locking mechanisms (e.g., "LWLock", "SpinLock"). * Define shared memory segments in "src/backend/utils/misc/ipc.c" or a relevant module's initialization function. **Don't Do This:** * Directly use system calls like "shmget()" and "shmat()" without going through PostgreSQL's shared memory APIs. * Assume atomicity of operations on shared memory regions. Always use locking. * Overallocate shared memory. Reserve only what is necessary. **Why:** Proper shared memory management prevents corruption, ensures data integrity, and avoids resource conflicts between processes. **Example:** """c /* Example of allocating and using shared memory */ typedef struct { int counter; LWLock lock; } MySharedData; static MySharedData *mySharedData; void initializeMySharedData(void) { bool found; mySharedData = ShmemInitStruct("MySharedData", sizeof(MySharedData), &found); if (!found) { /* Initialize shared memory on first allocation */ mySharedData->counter = 0; LWLockInitialize(&mySharedData->lock, LWLockAssign()); } } int incrementCounter(void) { int result; LWLockAcquire(&mySharedData->lock, LW_EXCLUSIVE); result = ++mySharedData->counter; LWLockRelease(&mySharedData->lock); return result; } """ ## 2. Project Structure and Organization PostgreSQL's source code is organized into a directory structure that reflects its functionality. **Do This:** * Familiarize yourself with the top-level directories: "src", "doc", "contrib", "src" is where the core source code resides. * Understand the purpose of subdirectories within "src", such as "backend", "include", and "port". * Place new code in the appropriate directory based on its functionality. * Maintain consistency in coding style and naming conventions within each directory. **Don't Do This:** * Randomly place files in arbitrary directories. * Create unnecessary dependencies between modules. * Violate the established directory structure without a clear justification. **Why:** A well-organized project structure facilitates navigation, understanding, and maintenance of the codebase. Clear directory conventions maintain code clarity. ### 2.1 Core Directories Key directories within the "src" directory include: * "src/backend": Contains the core backend code, including query processing, transaction management, storage, and indexing. * "src/include": Contains header files that define the interfaces used by the backend code. * "src/port": Contains platform-specific code. * "src/common": Contains code shared across multiple parts of the backend. * "src/fe_utils": Contains utilities used by the frontend. **Do This:** * Follow the existing directory structure when adding new features or modifying existing ones. * Create new subdirectories within existing directories if necessary to organize logically related code. * Use header files in "src/include" to define public interfaces for modules. **Don't Do This:** * Include implementation details in header files. * Create circular dependencies between directories. **Why:** A modular directory structure ensures a logical separation of concerns and minimizes dependencies between modules helping reduce build times. ### 2.2 Coding Style PostgreSQL has a well-defined coding style outlined in "doc/src/sgml/develop.sgml". **Do This:** * Adhere to the coding style guidelines regarding indentation, spacing, naming conventions, and comment formatting. * Use "pgindent" to automatically format your code. * Write concise and informative comments. **Don't Do This:** * Ignore the coding style guidelines. * Write lengthy or redundant comments. * Use inconsistent naming conventions. **Why:** Consistent coding style improves readability and maintainability of the code. "pgindent" ensures code conforms to the standard style automatically. ## 3. Modern Approaches and Patterns Modern PostgreSQL development emphasizes several key approaches: * **Extensibility:** PostgreSQL is designed to be extensible through extensions. * **Concurrency:** Handling multiple concurrent connections efficiently is crucial. * **Security:** Preventing vulnerabilities and ensuring data integrity are paramount. ### 3.1 Extension Development Extensions are the primary way to add new functionality to PostgreSQL. **Do This:** * Use the Extension Control File (".control") to define the extension's metadata. * Provide SQL scripts for creating and dropping database objects. * Use hooks ("ExecutorStart_hook", "ExecutorRun_hook", etc.) to extend the core functionality. * Follow the security guidelines for extension development. **Don't Do This:** * Modify the core PostgreSQL code directly (unless absolutely necessary and approved by the community). * Introduce security vulnerabilities through insecure extension code. * Make assumptions about the internal implementation details of PostgreSQL that could change in future versions. **Why:** Extensions allow adding new features without modifying the core code. **Example:** """sql -- Example SQL script for creating a function in an extension CREATE FUNCTION my_extension_function(text) RETURNS text AS '$libdir/my_extension', 'my_extension_function' LANGUAGE C IMMUTABLE STRICT; """ ### 3.2 Concurrency Control PostgreSQL uses Multi-Version Concurrency Control (MVCC) to manage concurrent access to data. **Do This:** * Understand MVCC and its implications for data consistency. * Use appropriate transaction isolation levels to prevent data anomalies. * Minimize lock contention by optimizing queries and using appropriate indexing strategies. * When working with internal data structures, be mindful of concurrent access and utilize PostgreSQL's locking primitives (LWLock, spinlocks) appropriately. **Don't Do This:** * Ignore the potential for data anomalies when using low transaction isolation levels. * Introduce unnecessary locking that could lead to deadlocks. * Perform long-running operations within a single transaction. **Why:** MVCC ensures data consistency and allows concurrent access to data. ### 3.3 Security Best Practices Security is a critical aspect of PostgreSQL development. **Do This:** * Follow secure coding practices to prevent vulnerabilities such as SQL injection and buffer overflows. * Use hardened APIs to avoid common security pitfalls. * Validate input data carefully. * Avoid hardcoding sensitive information such as passwords. * Be aware of the security implications of new features. **Don't Do This:** * Ignore security warnings. * Implement custom encryption algorithms (use PostgreSQL's built-in encryption features). * Grant excessive privileges to users or roles. **Why:** Secure coding practices are essential for preventing data breaches and ensuring the integrity of the database. ### 3.4 Memory Management Efficient Memory management is key to PostgreSQL's performance and stability. **Do This:** * Use PostgreSQL's memory context mechanism ("MemoryContext") for allocating and freeing memory within a query lifecycle. This mechanism provides automatic memory cleanup at the end of a query preventing memory leaks. * Understand the different memory contexts (e.g., "TopMemoryContext", "QueryMemoryContext") and use them appropriately. * Avoid manual memory management ("malloc"/"free") unless absolutely necessary (and only if you REALLY know what you are doing). Use PostgreSQL's "palloc"/"pfree" within a memory context. * Profile memory usage to identify and fix memory leaks. **Don't Do This:** * Leak memory by failing to free allocated memory. * Allocate large amounts of memory without considering the impact on performance. * Use "malloc"/"free" without a deep understanding of PostgreSQL's memory management. **Why:** Efficient memory management prevents memory leaks, reduces memory fragmentation, and improves overall performance. The memory context system automates this and integrates with the query processing lifecycle. **Example:** """c /* Example using MemoryContext */ MemoryContext myContext; char *data; /* Create a new memory context */ myContext = AllocSetContextCreate(CurrentMemoryContext, "MyContext", ALLOCSET_DEFAULT_SIZES); /* Switch to the new memory context */ MemoryContext oldContext = MemoryContextSwitchTo(myContext); /* Allocate memory within the new context */ data = palloc(100); /* Switch back to the previous memory context. The 'data' still exists */ MemoryContextSwitchTo(oldContext); /* ... use data ... */ /* At the end, the memory context 'myContext' is destroyed, and all memory allocated within it is automatically freed */ MemoryContextDelete(myContext); """ These standards aim to provide a comprehensive guide for contributing to the core architecture of PostgreSQL, by promoting best practices and ensuring code maintainability, performance, and security. By following these guidelines, developers can help ensure that PostgreSQL remains a robust, reliable, and extensible database system.