pglinter is a PostgreSQL extension that analyzes your database for potential issues, performance problems, and best practice violations. Written in Rust using pgrx, it provides deep integration with PostgreSQL for efficient database analysis.
Seems nice. It would be better if it could be run as a script or agent, instead of a plugin, so it could work against hosted installations on AWS or Google Cloud (both of which limit extensions).
Why does it have to be an extension? At a cursory glance I did not see any checks that cannot be performed by a client/application that connects to the database. Being an extension gives it privileges that wouldn't be available to a client application.
Interesting project. Has anyone tried adopting something like this in their database cluster? I would like to know how it performs in practice. Is it useful?
mirroring all the comments about this _needing_ to be an extension..
in theory, one should be able to extract the "rule" definitions [1] and have it run with a conn str; instead of this _needing_ to be an extension.
in practice though, query plan analysis and missing indexes is a bigger use-case; since it's bad queries that take down the db.. and i see no rules here to help with that.
I expect that the thing that makes it an extension is "T005: Tables with potential missing indexes (high sequential scan usage)." Can you get that data on the outside?
We are also working on a database linter.
Currently focusing on Oracle but we will support Postgres soon too.
Rules can either run queries against the DB (e. g. foreign key without index) or use our parser to check code SQL, PL/SQL, and pgSQL soon (naming standards, security and performance issues, etc.). We currently have over 280 rules [1]. The tool runs as a lange server during development or as a CLI so you can use it in your automations. Its more enterprise focused, an admin can create configurations that get applied to all developmers.
It’s a good idea, but this kind of thing is my problem with linters: “B006: Tables with uppercase names/columns”
They usually end up expanding in scope into places they shouldn’t be. Consider also react linters, full of rules that shouldn’t always be blanket applied or create tons of pointless busywork.
My ORM will decide the naming of my database tables, thank you very much. It’s much more qualified than a linter, which should stay in its lane.
The reason you don’t this in psql is that for some versions of Postgres case is significant and you need to use quotation marks. I ran into this at one point and it drove me bonkers.
Older versions of pg let you create cases identifiers without quotes. I don’t care enough to look which ones.
14 comments
[ 3.4 ms ] story [ 41.1 ms ] threadin theory, one should be able to extract the "rule" definitions [1] and have it run with a conn str; instead of this _needing_ to be an extension.
in practice though, query plan analysis and missing indexes is a bigger use-case; since it's bad queries that take down the db.. and i see no rules here to help with that.
[1] https://github.com/pmpetit/pglinter/blob/9a0c427fac14840a7d6...
Rules can either run queries against the DB (e. g. foreign key without index) or use our parser to check code SQL, PL/SQL, and pgSQL soon (naming standards, security and performance issues, etc.). We currently have over 280 rules [1]. The tool runs as a lange server during development or as a CLI so you can use it in your automations. Its more enterprise focused, an admin can create configurations that get applied to all developmers.
[1]: https://dblinter-rules.united-codes.com/all-rules/
They usually end up expanding in scope into places they shouldn’t be. Consider also react linters, full of rules that shouldn’t always be blanket applied or create tons of pointless busywork.
My ORM will decide the naming of my database tables, thank you very much. It’s much more qualified than a linter, which should stay in its lane.
Older versions of pg let you create cases identifiers without quotes. I don’t care enough to look which ones.
https://sqlpey.com/sql/postgresql-identifier-case-sensitivit...
- https://github.com/kaaveland/eugene/
- https://github.com/supabase-community/postgres-language-serv...