lfittl

Author here - what we call "pg_plan" internally, is essentially very similar in spirit to what we do with pg_query [0], but the difference is that we pull in a lot more code from Postgres, and have more overrides in places that are not needed for the use case (e.g. MVCC handling, etc).

My gut feeling tells me that the chances of having an upstream library that contains the parser/parse analysis/planner are slim. Mainly from there being a lot of entanglement with reading files on disk, memory management, etc - I suspect one of the pushbacks would be it would complicate development work for Postgres itself, to the point its not worth the benefits.

For the pg_query library on the other hand I have hopes that we can upstream this eventually - there are enough third-party users out there to clearly show the need, and its much more contained (i.e. raw parser + AST structs). Hopefully something we can spend a bit of time on next year.

[0]: https://github.com/pganalyze/libpg_query

OP here (happy to see this on HN!) - if you're interested in our work in this area, I hosted a webinar earlier this week where we walked through a new constraint programming based system (utilizing CP-SAT) for Postgres index selection that we're starting to roll out in pganalyze:

https://www.youtube.com/watch?v=SlNQTtfjlnI

Compared to the initial version, this updated version is both more configurable, as well as has better handling of competing objectives in index selection (write overhead vs query performance).

If you want to give this a try, and have pganalyze set up / want to try it out, feel free to send me a message (email in profile).

pganalyze | Head of Sales | REMOTE (US/CA time zones) | Full-time | base $120-150k + commission/bonus + equity | https://pganalyze.com

At pganalyze, we build observability and automation tools for optimizing the performance of Postgres databases. Our product helps customers such as Atlassian, Robinhood and DoorDash to understand complex Postgres problems and solve performance issues. We are bootstrapped, profitable, with over 500 customers, small and large.

As we're growing and moving from founder-led sales to a repeatable sales motion, we're looking to make the first hire for a dedicated sales team. For this role we are looking for someone who wants to sell the product themselves at the start, establish playbooks, and then grow the sales team over time.

If you work in sales and are reading Hacker News that means you might be a good fit!

Interested? Email me directly (the Founder & CEO) at lukas AT pganalyze.com - or fill out the form on the website.

All details: https://pganalyze.com/careers/head-of-sales

Here is the email they sent for this, with some extra context:

"Earlier this year, we shared our view on AI at Figma [0] and the importance of being open with our community about our AI journey moving forward. Since then, we've been building practical, useful features for FigJam [1] and Figma, with more to come soon!

To power our new AI features, we're adding OpenAI, L.L.C. (“OpenAI”) as a subprocessor. Figma uses subprocessors, which are companies that process data on our behalf, to help provide our core Figma services to you.

What this means for you: We will only send the information you provide when using AI features. We also have an agreement that prohibits OpenAI from using any Figma customer data for training purposes.

If you would like to opt out of our AI-powered features, learn more here [2]."

[0]: https://www.figma.com/blog/ai-the-next-chapter-in-design/ [1]: https://www.figma.com/community/widget/1274481464484630971/j... [2]: https://help.figma.com/hc/en-us/articles/17725942479127

https://www.openwall.com/lists/oss-security/2023/10/03/2 is probably a better link (without ads and unnecessary images), which is essentially what the article is citing 1:1.

You can see a break-down of the core team and major contributors here, as well as their current company affiliation: https://www.postgresql.org/community/contributors/

(and as noted in the other comment, whilst EDB certainly makes important contributions, they are one of many)

In the above described scenario (US-based retiree moving to Germany), its likely the social security totalization treaty applies, giving them access to the health system: https://www.ssa.gov/international/Agreement_Pamphlets/german...

Generally I agree that this would be great to have, and Postgres does have a set of libraries it already maintains as part of the main source tree (i.e. libpq, etc), and there is a shared set of code between the backend and the "frontend" (https://github.com/postgres/postgres/tree/master/src/common). So theoretically you could imagine the parser moving into that shared code portion, sharing code but not necessarily requiring linking to a library from the backend.

However, the challenge from what I've understood from past conversations with some folks working on Postgres core is that the parser is currently heavily tied into the backend - note the parser isn't just the scan.l/gram.y file, but also the raw parse node structs that it outputs. You can see how many files we pull in from the main tree that are prefixed with "src_backend": https://github.com/pganalyze/libpg_query/tree/15-latest/src/...

Further, there isn't a canonical way to output node trees into a text format today in core, besides the rather hard to work with output of debug_print_parse - there have been discussions on -hackers to potentially utilize JSON here, which may make this a bit easier. Note that in libpg_query we currently use Protobuf (but used to use JSON), which does have the benefit of getting auto-generated structs in the language bindings - but Protobuf is not used in core Postgres at all today.

All in all, I think there is some upstream interest, but its not clear that this is a good idea from a maintainability perspective.

Excited to see this - and excellent use case for libpg_query (I'm the original author and still help maintain it together with the rest of the team) and appreciate the shout out to pganalyze!

If anyone else has a use case for using the Postgres parser outside the server, we have a healthy ecosystem of libraries that build on the core C library (we maintain bindings for Ruby, Go and Rust ourselves), as well as various projects using it (e.g. sqlc uses it for a type-safe way for using hand-written SQL in Go): https://github.com/pganalyze/libpg_query#resources

We at pganalyze would definitely welcome help here - and if anyone would like to give this a try - feel free to email me, happy to talk this through, email is in my profile.

Postgres itself supports Windows, the main challenge is identifying the relevant files to pull in (since we use a subset of the full source code), and testing the changes. It should be fairly quick for someone who has C development experience on Windows, with a working development environment.

Thanks Yuri - and appreciate your contributions to libpg_query!

We at pganalyze have plans to add Postgres 16 support to libpg_query sometime between the RC1 and the final release - mostly to watch out for the rare case where a feature gets reverted during the beta releases.

If you'd like to have a go at it before then definitely welcome (here is some context on the extraction process using libclang: https://pganalyze.com/blog/pg-query-2-0-postgres-query-parse...), and we'll also need people to test the PR once available.

I think Supabase generally does good work, but I don't think they can be given credit for pgvector, if that's what you're indicating (I might have misread).

As I understand, Andrew Kane is the principal author of pgvector, and has worked on it for almost two years before Supabase added support for it.

See also https://github.com/pgvector/pgvector/issues/54 and https://github.com/supabase/postgres/pull/472.

Neat project!

Just in case its helpful to you, I maintain pg_query together with my colleagues at pganalyze, which packages the Postgres parser as a library: https://github.com/pganalyze/libpg_query

Might be useful to include in your product as a way to run a quick syntax check on the query output by the LLM, without actually connecting to the database and causing an error in the logs.

It does not, except if you're COPYing into an unlogged table - for regular tables data that gets COPYed in gets WAL logged and is crash safe.

In my understanding, as compared to a multi-row insert, a COPY statement is (1) a lot more efficient to parse/process, (2) uses a special bulk write strategy that avoids churn and large amount of dirty pages in shared buffers.

As compared to multiple single row inserts another benefit is that you're writing a single transaction ID, instead of a lot of individual transaction IDs (causing less freezing work having to be done by autovacuum, etc.)

Good catch. Thats actually intentional at the moment - we don't put the weekly "5mins of Postgres" episodes in our RSS feed.

Primarily since we syndicate the feed to Planet Postgres, and video would go against the rules. I'll see if we can split up our feeds somehow.

OP here - thanks for noting!

We've recently invested effort to make the homepage better (added the animation, revised the messaging, etc), so that makes me happy to hear & will forward to the team as well.

Fair point, I read this more under the light of paying for an ongoing therapist relationship to deal with a challenging situation (which as noted in another comment, $300 as a one time payment doesn't do much if you have weekly sessions with an out of network provider).

I agree it might help someone make a decision to try out therapy and see whether its a good idea for them.

To clarify why it bothers me: It tries to tell a story of "we as a company care about you", but at least in my experience, health care plans cover less than $100 per session, and in the SF Bay Area you easily pay $200+ per session for mental health professionals (as they are often out of network).

Maybe Apollo's health care plan has much better out of network coverage than what I'm familiar with, in which case I stand corrected (and agreed that its good to help cover copays, if this is what this actually does).

The "$300 mental health" seems odd - for any kind of counseling/therapy/etc sessions this is a drop in the bucket in terms of cost.

It will easily cost 10x to work with a mental health professional (and insurance companies sadly pay a very small amount of it). So why add this to an otherwise reasonable package?

Agreed - I think MERGE is great, but this post is missing the "Why not" part of the analysis.

In the Postgres community, MERGE has been talked about for a long time, but in my understanding, part of the reason why the Postgres team initially shipped INSERT ... ON CONFLICT (instead of straight up MERGE) is that it lets you have guarantees about the outcome of the statement (i.e. either INSERT or UPDATE, by use of speculative insertion handling), vs MERGE can cause unique constraint violations and other issues.

AFAIK, the generic syntax of MERGE does not allow for stricter guarantees, and therefore there will always be cases where one is better than the other.

Autovacuum essentially runs a regular VACUUM, which helps you prevent new table bloat. However, to avoid locking issues, it does not fully rewrite the table, so it does not remove existing table bloat.

If you want to remove existing table bloat you either need a VACUUM FULL (takes an exclusive lock, impacting your regular workload thus autovacuum doesn't do it), or use an extension that does an online version of that, i.e. pg_repack or pg_squeeze.

Edit: Also, to clarify, with table bloat in this context I mean pages in the table that are not actually needed, but were added to the table previously to store data. Existing pages couldn't be used because rows were still marked as dead, i.e. autovacuum hadn't yet gotten around to clean them up before new data came in.

Agreed. It's clear that PostgresPro is profiting from this, and they have not taken a stance on Russia's war in Ukraine (I imagine they couldn't, given how much money they are to make in the now isolated Russian market).

And if I've observed correctly, they've also quietly moved their supposed company headquarters to Gibraltar, and removed any clear mentions of the above cited Russian government contracts from their website.

pganalyze | Backend Software Engineer | REMOTE (US/CA time zones) | Full-time | Salary: $150-190k | https://pganalyze.com

At pganalyze, we build observability and automation tools for optimizing the performance of Postgres databases. Our product helps customers such as Atlassian, Robinhood and DoorDash to understand complex Postgres problems and solve performance issues. We are bootstrapped, profitable, and are currently hiring employee #6 and #7 for our team.

We recently launched the pganalyze Indexing Engine, an automated way to find the right indexes based on a Postgres query workload, and continue expanding from observability into automated analysis and recommendations for Postgres performance tuning.

For this role we are looking for someone with database expertise, and who has worked on low-level code with C or Rust. Having worked with Ruby or Go before is a plus.

All details: https://pganalyze.com/careers/backend-software-engineer

Interested? Email me (the Founder & CEO) at lukas AT pganalyze.com - or fill out the form on the website.

Doing this well for a production system is a hard problem. The sibling comment pointed out HypoPG and Dexter, but I'm not familiar with folks using a simple approach like the one Dexter implements on a production system. The human element is valuable when indexing, to correctly model the workload understanding and assess the trade-offs (index write overhead, etc).

For context, I've personally been working on an automatic indexing system for Postgres for more than a year now [1], and whilst I think what we have today is pretty useful, there is still work to be done. We've intentionally not yet enabled full automation (i.e. actual automatic creation of the indexes on the production database), because most people I've talked with prefer to review a recommendation and then apply it through their regular migration tooling, after making an assessment.

If you ever want to talk more about this, feel free to send me an email - I've spent a lot of time thinking about this topic :)

[1] https://pganalyze.com/blog/automatic-indexing-system-postgre...

As one of the authors of the tool that was critiqued in depesz' blog post from last year, I fully agree that looking at this on a per-query basis is too simple.

Since we wrote our initial index suggestion tool for Postgres, we actually went back to the drawing board, examined the concerns brought up, and developed a new per-table Index Advisor for Postgres that we recently released [1].

The gist of it: Instead of looking at the "perfect" index for each query, its important to test out different "good enough" indexes that cover multiple queries. Additionally, as you note, the write overhead of indexes needs to be considered (both from a table writes / second approach, as well as disk space used at a given moment in time).

I think this is a fascinating field and there is lots more work to be done. I've also found the 2020 paper "Experimental Evaluation of Index Selection Algorithms" [2] pretty useful, that compares a few different approaches.

[1] https://pganalyze.com/blog/automatic-indexing-system-postgre...

[2] https://www.vldb.org/pvldb/vol13/p2382-kossmann.pdf

pganalyze | Backend Software Engineer | REMOTE (US/CA time zones) | Full-time | Salary: $150-190k | https://pganalyze.com

At pganalyze, we redefine the user experience for optimizing the performance of Postgres databases. Our product helps customers such as Atlassian, Robinhood and DoorDash to understand complex Postgres problems and performance issues. We are bootstrapped, profitable, and are currently hiring employee #6 and #7 for our team.

We recently launched the pganalyze Indexing Engine, an automated way to find the right indexes based on a Postgres query workload, and continue expanding from observability into automated analysis and recommendations for Postgres performance tuning.

For this role we are looking for someone who has worked with Ruby, Go or Rust before, and ideally has also worked on low-level code with C or Rust. Having a solid understanding of statistics is a plus.

All details: https://pganalyze.com/careers/backend-software-engineer

Interested? Email me (the Founder & CEO) at lukas AT pganalyze.com - or fill out the form on the website.

> There's another, similar, story around creating draft invoices to give people quotes before checking out. The dashboard API has a function to modify the line items of a draft invoice, but it's not a public-facing API and there's no short-term intention to make it public-facing, so one has to build an ugly workaround.

There is a similar "not a public-facing API" limitation with configuring multiple invoice email addresses for Stripe customers (works in the dashboard, but blocked in the public API, where you can only set a single email per customer).

I'm actually quite surprised Stripe does this, since their initial reason for being was developer-friendly payments. But oh well, maybe I should take a look at an alternative (like Lago) sometime.

And for fun, I ended up doing the same in parallel for the 2021 data:

https://gist.github.com/lfittl/2f112698f908d38f43f4f2aeaaa94...

Note that some of the folks on the list have changed jobs in the last 2 years, so it wouldn't be accurate to attribute all contributions to the current employer (plus some folks contribute on their own time).

Also, the EDB / 2ndquadrant acquisition has led to a lot of EDB-attributed contributions, which hopefully will last :)

Edit: FWIW, I think your 2020 list has Tom Lane working for Google - thats not accurate (I think you got the wrong one from LinkedIn), as he is still employed by Crunchy Data (since leaving Salesforce in 2015), to my knowledge.

(author here)

Thanks for trying out the pganalyze app! Sorry to hear about the challenges with log monitoring setup - happy to restart the trial and do a screenshare to debug - you can email me directly at lukas AT pganalyze.com

On the index advisor errors: Sounds like you are running into errors with cases where the planner calls functions (e.g. for type conversion, expression simplification). We've recently improved some cases there, but numeric specifically I know there is a pending bugfix. Happy to take a closer look.

(author here)

Just realized that could use a more direct link (will add), here is the relevant section from our post on pg_query:

https://pganalyze.com/blog/pg-query-2-0-postgres-query-parse...

The quick summary: Call libclang using the Ruby FFI bindings to parse C code into AST and locate function/symbol definitions, build a tree of dependent code, remove all code that is not referenced + add a few stubs where there is direct disk access.