4 ms·

Database Linting and Analysis for PostgreSQL

117 points by fljdin 1y ago

fljdin 1y ago
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.
clintonb 1y ago
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).
- bigiain 1y ago
  While that'd be nice, I ended up deciding I probably didn't want something like this installed on my prod RDS Postgres, but instead I can easily run it on local dev/staging Postgres instances, and ensure I'm testing the prod config without having to run pg extensions on the prod instances. It looks like running this on a database dump from prod will be able to run all the tests except the Cluster Rules - which feels like a good tradeoff for me.
ddxv 1y ago
Will this support Postgres18 soon? I see the docs say it currently supports Postgres18 beta 2, so possibly just the docs need to be bumped?
gurjeet 1y ago
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.
- gazpacho 1y ago
  Came here to say just this. I want this so bad! But I can’t run it on a cloud hosted Postgres…
- traceroute66 1y ago
  > Why does it have to be an extension? Same sentiment here. Its 2025, the necessity of the principle of least privilege is greater than ever. I'm not installing random third-party postgres extensions. Even in dev environments. Sorry.
  - oefrha 1y ago
    I run plpgsql_check extension (packaged by Debian) in a test-only container, which only live for the duration of automated tests. It’s alright.
- plateboxbag 1y ago
  Fair point, but can't it just be run in a container that has the schema applied? Can just run locally/in ci?
neves 1y ago
Looks nice. Do you know any similar tools that work for other databases?
- evanelias 1y ago
  Schema management tools often include built-in linters. I added a linter engine to my MySQL/MariaDB schema management tool Skeema back in 2019 and it proved to be a popular feature [1]. A couple more recent entrants in this space include ByteBase [2] and Atlas [3]. When selecting a linter, I'd just recommend ensuring the author(s) are deeply experienced in your particular DBMS. Otherwise they tend to cargo-cult bad advice that is either out-of-date, or only makes sense for some other DBMS. And nowadays, AI hallucinations are another source of nonsensical linters. [1] https://www.skeema.io/docs/features/safety/ https://www.skeema.io/docs/features/safety/ [2] https://docs.bytebase.com/sql-review/review-rules https://docs.bytebase.com/sql-review/review-rules [3] https://atlasgo.io/versioned/lint https://atlasgo.io/versioned/lint
  - rotemtam 1y ago
    Thanks for the mention evan!
  - tianzhou 1y ago
    Appreciate the Bytebase shoutout! Skeema has definitely been an inspiration.
SteveLauC 1y ago
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?
thewisenerd 1y ago
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. [1] https://github.com/pmpetit/pglinter/blob/9a0c427fac14840a7d67657a82d8ce862e42daf0/sql/rules.sql#L269 https://github.com/pmpetit/pglinter/blob/9a0c427fac14840a7d6...
mannyv 1y ago
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?
- wiredfool 1y ago
  The pg_stat_io tables have a bunch of data that will tell you about index and table usage.
phartenfeller 1y ago
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. [1]: https://dblinter-rules.united-codes.com/all-rules/ https://dblinter-rules.united-codes.com/all-rules/
davedx 1y ago
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.
- miniwark 1y ago
  It look like easy to disable a rule : `SELECT pglinter.disable_rule('B006');`. That said, i agree with you than some of the default rules may be bad. For example : B001 & T001 recommend primary keys, but it will effectively kill a TimescaleDB hypertable (primary keys are not recommended).
- evanelias 1y ago
  Generally speaking, table name capitalization linters are actually very useful for portability reasons. The exact rules for identifier case sensitivity vary across different DBMS, for example in Postgres it depends on whether quotes are used: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#:~:text=Quoting%20an%20identifier,never%20quote%20it.%29 https://www.postgresql.org/docs/current/sql-syntax-lexical.h... Meanwhile for MySQL/MariaDB it depends on whether the underlying OS/filesystem is case-sensitive or not: https://www.skeema.io/blog/2022/06/07/lower-case-table-names/ https://www.skeema.io/blog/2022/06/07/lower-case-table-names... And plenty of similarly weird behavior on other DBMS. ORMs tend to be generic / multi-DBMS, and you shouldn't always assume your ORM's behavior is more qualified than a DBMS-specific linter.
  - davedx 1y ago
    I don't understand -- useful how exactly? For most of my recent projects I use Prisma with Postgres; Prisma tables by default are named like TableName, and yes - for actual Postgres SQL that means you need to wrap everything in double quotes if you do anything manually `SELECT * FROM "TableName"` because otherwise it won't work. But that's never actually been an issue for me in some way? Compared to the immense benefits of having a well designed ORM (like Prisma), this linter doesn't seem useful to me at all. But maybe I'm missing something.
lervag 1y ago
Related tools: - https://github.com/kaaveland/eugene/ https://github.com/kaaveland/eugene/ - https://github.com/supabase-community/postgres-language-server https://github.com/supabase-community/postgres-language-serv...
landsman 1y ago
Checks for DB migrations in GitHub Pull Request would be really nice!
sherinjosephroy 1y ago
[flagged]

117 points by fljdin 1y ago

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.

clintonb 1y ago

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

bigiain 1y ago
While that'd be nice, I ended up deciding I probably didn't want something like this installed on my prod RDS Postgres, but instead I can easily run it on local dev/staging Postgres instances, and ensure I'm testing the prod config without having to run pg extensions on the prod instances. It looks like running this on a database dump from prod will be able to run all the tests except the Cluster Rules - which feels like a good tradeoff for me.

ddxv 1y ago

Will this support Postgres18 soon? I see the docs say it currently supports Postgres18 beta 2, so possibly just the docs need to be bumped?

gurjeet 1y ago

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.

gazpacho 1y ago
Came here to say just this. I want this so bad! But I can’t run it on a cloud hosted Postgres…
traceroute66 1y ago
> Why does it have to be an extension? Same sentiment here. Its 2025, the necessity of the principle of least privilege is greater than ever. I'm not installing random third-party postgres extensions. Even in dev environments. Sorry.
- oefrha 1y ago
  I run plpgsql_check extension (packaged by Debian) in a test-only container, which only live for the duration of automated tests. It’s alright.
plateboxbag 1y ago
Fair point, but can't it just be run in a container that has the schema applied? Can just run locally/in ci?

neves 1y ago

Looks nice. Do you know any similar tools that work for other databases?

evanelias 1y ago
Schema management tools often include built-in linters. I added a linter engine to my MySQL/MariaDB schema management tool Skeema back in 2019 and it proved to be a popular feature [1]. A couple more recent entrants in this space include ByteBase [2] and Atlas [3]. When selecting a linter, I'd just recommend ensuring the author(s) are deeply experienced in your particular DBMS. Otherwise they tend to cargo-cult bad advice that is either out-of-date, or only makes sense for some other DBMS. And nowadays, AI hallucinations are another source of nonsensical linters. [1] https://www.skeema.io/docs/features/safety/ https://www.skeema.io/docs/features/safety/ [2] https://docs.bytebase.com/sql-review/review-rules https://docs.bytebase.com/sql-review/review-rules [3] https://atlasgo.io/versioned/lint https://atlasgo.io/versioned/lint
- rotemtam 1y ago
  Thanks for the mention evan!
- tianzhou 1y ago
  Appreciate the Bytebase shoutout! Skeema has definitely been an inspiration.

SteveLauC 1y ago

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?

thewisenerd 1y ago

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. [1] https://github.com/pmpetit/pglinter/blob/9a0c427fac14840a7d67657a82d8ce862e42daf0/sql/rules.sql#L269 https://github.com/pmpetit/pglinter/blob/9a0c427fac14840a7d6...

mannyv 1y ago

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?

wiredfool 1y ago
The pg_stat_io tables have a bunch of data that will tell you about index and table usage.

phartenfeller 1y ago

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. [1]: https://dblinter-rules.united-codes.com/all-rules/ https://dblinter-rules.united-codes.com/all-rules/

davedx 1y ago

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.

miniwark 1y ago
It look like easy to disable a rule : `SELECT pglinter.disable_rule('B006');`. That said, i agree with you than some of the default rules may be bad. For example : B001 & T001 recommend primary keys, but it will effectively kill a TimescaleDB hypertable (primary keys are not recommended).
evanelias 1y ago
Generally speaking, table name capitalization linters are actually very useful for portability reasons. The exact rules for identifier case sensitivity vary across different DBMS, for example in Postgres it depends on whether quotes are used: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#:~:text=Quoting%20an%20identifier,never%20quote%20it.%29 https://www.postgresql.org/docs/current/sql-syntax-lexical.h... Meanwhile for MySQL/MariaDB it depends on whether the underlying OS/filesystem is case-sensitive or not: https://www.skeema.io/blog/2022/06/07/lower-case-table-names/ https://www.skeema.io/blog/2022/06/07/lower-case-table-names... And plenty of similarly weird behavior on other DBMS. ORMs tend to be generic / multi-DBMS, and you shouldn't always assume your ORM's behavior is more qualified than a DBMS-specific linter.
- davedx 1y ago
  I don't understand -- useful how exactly? For most of my recent projects I use Prisma with Postgres; Prisma tables by default are named like TableName, and yes - for actual Postgres SQL that means you need to wrap everything in double quotes if you do anything manually `SELECT * FROM "TableName"` because otherwise it won't work. But that's never actually been an issue for me in some way? Compared to the immense benefits of having a well designed ORM (like Prisma), this linter doesn't seem useful to me at all. But maybe I'm missing something.

lervag 1y ago

Related tools: - https://github.com/kaaveland/eugene/ https://github.com/kaaveland/eugene/ - https://github.com/supabase-community/postgres-language-server https://github.com/supabase-community/postgres-language-serv...

landsman 1y ago

Checks for DB migrations in GitHub Pull Request would be really nice!

sherinjosephroy 1y ago

[flagged]