Good question. If you rarely change your db, it'll probably not be as useful.
But some platforms (such as Supabase) rely on many parts of Postgres.
You use INSERT hooks to trigger queue insertions, Row Level Security (RLS) to secure data, SQL functions for aggregate queries. Plus the schema changes you do to support business use-cases.
Normally, you'll write these things into SQL migration files. Without an LSP, you'd have to look up the current state/implementation of schemas/functions, and you'd have to run the migrations to see whether there are errors. With the LSP, that's easier.
steinroe and I both use a lot of migrations in our day jobs (a whatsapp newsletter and a fintech startup).
Thanks for the feedback. Good points, we'll improve the README.
Regarding your questions:
1. It's currently only for SQL statements, but we'll work on function bodies and PL/pgSQL soon.
2. Exactly, we follow the the PostgreSQL dialect (we use Postgres's parser)
3. You can give it a database connection and it will query some pg_catalog tables. If you don't provide connection details, we'll bypass features requiring that.
co-author here: The most interesting part is probably the parsing of SQL files.
The first issue is that the Postgres parser is complex and always changing, so you can't really roll your own parser to parse SQL.
The second is that the parser only works with valid and complete SQL statements, but an LSP should help you with those that are invalid or incomplete.
The simple solution is to actually use two parsers – the original libpg_query that's used by Postgres itself, and tree-sitter for incomplete statements, and merge the parsed results.
With that, you can both get a workable AST for diagnostics and tree-sitters CST for autocompletion, for example.