Posts

I’m excited to announce the release of pgAssistant 2.0, a major milestone for the project. This new version marks a strategic shift toward API-first integration, making it easier to embed pgAssistant into existing workflows — from code review pipelines to production compliance checks. The first available API in this release focuses on automated database health reporting. It generates a Markdown report that provides : • A global health overview of your PostgreSQL database • Key performance and metrics • Identified issues and improvement suggestions Since its beginning, pgAssistant’s mission has been to help developers design better, faster, and more maintainable databases — while also providing educational insights, references, and explanations for every metric. ...

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. ...

This release is coming with this a new AI feature : Use AI to verify table compliance with your SQL guide lines (like naming conventions) - Just provide a valid URL to your guidelines. It is strongly recommanded to use the Markdown format for SQL guide lines. This new functionnality is added on the Table definition helper menu. Enjoy ! New docker image is available on dockerhub Take a look at DockerHub image tag ...

This release is coming with this news features : pgTune : docker-compose yaml file is using a healthcheck and the shm_size parameter pgTune : a Kubernetes deployment descriptor has been added New docker image is available on dockerhub Take a look at DockerHub image tag docker pull bertrand73/pgassistant:1.9.8 Enjoy ! Docker image security advices No any security advice from github or docker scouts or Grype.

This release is coming with this news features : Re-factoring LLM API Calls to detect the use of ollama. Tested with the lastest ollama version With Lighthouse, optimize the UI Re-factoring the LLM settings form New docker image is available on dockerhub Take a look at DockerHub image tag docker pull bertrand73/pgassistant:1.9.7 Enjoy ! Docker image security advices No any security advice from github or docker scouts or Grype.

Following the release of OpenAI’s open-source model gpt-oss, I set out to benchmark its performance in conjunction with PostgreSQL, focusing specifically on integration with pgAssistant. All evaluations were conducted locally on a MacBook Pro M4 Pro (24 GB RAM), using Ollama as the model runtime environment. gpt-oss installation To install this new model, first download the latest version of ollama, then run this : ollama pull gpt-oss:20b Ollama should now serve the API at: http://localhost:11434 ...

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: ...

Privacy Data privacy is no longer a luxury — it’s a necessity. Today, we’ll show you how to use pgAssistant with Infomaniak Cloud — Infomaniak’s privacy-focused, sovereign cloud solution based in Switzerland🇨. Why Data Sovereignty Matters When using AI tools in the cloud, where your data is processed and stored has a major impact: Legal compliance (e.g. GDPR) Data residency requirements Protection from mass surveillance Infomaniak’s Swiss Cloud is one of the few solutions that: ...