Postgres

Lightweight SQL style guide Overview SQL style guide by Simon Holywell is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at https://www.sqlstyle.guide/ General Do Use consistent and descriptive identifiers and names. Make judicious use of white space and indentation to make code easier to read. Store [ISO 8601][iso-8601] compliant time and date information (YYYY-MM-DDTHH:MM:SS.SSSSS). Try to only use standard SQL functions instead of vendor-specific functions for reasons of portability. Keep code succinct and devoid of redundant SQL—such as unnecessary quoting or parentheses or WHERE clauses that can otherwise be derived. Include comments in SQL code where necessary. Use the C style opening /* and closing */ where possible otherwise precede comments with -- and finish them with a new line. Avoid camelCase—it is difficult to scan quickly. Descriptive prefixes or Hungarian notation such as sp_ or tbl. Plurals—use the more natural collective term where possible instead. For example staff instead of employees or people instead of individuals. Quoted identifiers—if you must use them then stick to SQL-92 double quotes for portability (you may need to configure your SQL server to support this depending on vendor). Object-oriented design principles should not be applied to SQL or database structures. Naming conventions General Ensure the name is unique and does not exist as a Postgresql keyword. Keep the length to a maximum of 30 bytes—in practice this is 30 characters unless you are using a multi-byte character set. Names must begin with a letter and may not end with an underscore. Only use letters, numbers and underscores in names. Avoid the use of multiple consecutive underscores—these can be hard to read. Use underscores where you would naturally include a space in the name (first name becomes first_name). Avoid abbreviations and if you have to use them make sure they are commonly understood. Tables Use a collective name or, less ideally, a plural form. For example (in order of preference) staff and employees. Do not prefix with tbl or any other such descriptive prefix or Hungarian notation. Never give a table the same name as one of its columns and vice versa. Avoid, where possible, concatenating two table names together to create the name of a relationship table. Rather than cars_mechanics prefer services. Columns Always use the singular name. Where possible avoid simply using id as the primary identifier for the table. Do not add a column with the same name as its table and vice versa. Always use lowercase except where it may make sense not to such as proper nouns. Aliasing or correlations Should relate in some way to the object or expression they are aliasing. As a rule of thumb the correlation name should be the first letter of each word in the object’s name. If there is already a correlation with the same name then append a number. Always include the AS keyword—makes it easier to read as it is explicit. For computed data (SUM() or AVG()) use the name you would give it were it a column defined in the schema. Uniform suffixes The following suffixes have a universal meaning ensuring the columns can be read and understood easily from SQL code. Use the correct suffix where appropriate. ...

Object This post provides a SQL query that analyzes PostgreSQL tables and suggests optimized autovacuum settings on a per-table basis. It aims to improve database health and performance by adapting vacuum/analyze thresholds according to table size and usage patterns. Goal PostgreSQL uses autovacuum to automatically clean up dead tuples and refresh planner statistics. However, the default settings may be too aggressive for small tables or too lax for large ones, leading to: ...

How to do it pg_stat_statements is required by pgAssistant. If you are not familiar with this module, you can find here the official Postgres documentation. To enable this module, add this option on the command that runs Posgres : shared_preload_libraries='pg_stat_statements' Then, connect to the database and run this command : CREATE EXTENSION IF NOT EXISTS pg_stat_statements; (When you run pgAssistant, he will execute this SQL Statements) If you run Postgresql in a docker environment Here is a sample docker-compose file that enables the module : ...

What is all about When you create a foreign key, you should always create an index on it (and NO, postgres do not create automaticly an index for you). Should You Always Index Foreign Keys in PostgreSQL? Foreign keys are an essential part of relational database design. They ensure referential integrity by linking rows between tables. But when it comes to performance, many developers wonder: “Should I always add an index to a foreign key column?” ...