Matthias van de Meent (@[email protected])

PostgreSQL hacker @ Neon (@neondatabase)
Opinions are my own - ∃RT ¬(RT∈{Endorsements})
Fediverse: @[email protected]

[Matthias van de Meent (@[email protected]) @mmeent_pg]

Looks like today is going to be the last day: I'm halting my activity here.

You can find me in the fediverse at @[email protected]; this account is shutting down.

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@fujii_masao Yeah, I think it'd be a quagmire: Would there be a separate statement start time for invocations of (potentially inlined) SQL functions? What about subselects? Etc.

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@samokhvalov Mastodon has a default of 500 characters (IIUC, it's configurable per instance).
There are also various other implementations of ActivityPub, e.g. Sharkey with 3k chars (Calckey, Misskey) for microblogging, Pixelfed focused on image sharing... And those are just the popular ones.

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@samokhvalov @PostgreSQL Not to forget, if you think you need the second, then the first is easier:
- "TABLE mytable;" not "SELECT * FROM mytable;"

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@MarkCallaghanDB Then I think it's mostly down to a PG planner/executor/index AM limitation.

For those interested, here are some related threads about this:

pgvector - https://t.co/z6FKCk9Zn1
psql-hackers - https://t.co/ncE4DJBQly

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@MarkCallaghanDB Personally I suspect it has to do with distance getting projected by the executor even after the index provided this exact data, but you may know whether there is more going on than just that.

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@MarkCallaghanDB > * pgvector gets less QPS because it uses more CPU to compute the distance metric

Do you know of a good reason why, or is this just empirical data?

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@fujii_masao Could you explain what you mean by "the SQL firewall feature"?

That's the first time I've heard of it, and AFAIK not part of ISO 9075 (at least, not clearly from its parts' descriptions).

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@PierreZ @MarkCallaghanDB A good start would be the PostgreSQL Wiki page on MT: https://t.co/kLWzH6qLnC

It contains most of the current state of the effort.

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@FranckPachot @MarkCallaghanDB I'm not familiar enough with MySQL's pluggable storage APIs to make comments to that - though it looks like several features in PostgreSQL seem to be implemented above the TableAM abstraction, whereas in MySQL they seem to be pushed down into Pluggable Storage (e.g. FKs)

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@FranckPachot @MarkCallaghanDB True, though the HEAP part is optional, as is (technically) the blocks part. The only significant restrictions are
- TID correllation implies tuple locality
- Each indexed TID indicates a constant set of attribute values.

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@MarkCallaghanDB But indeed, compressed storage isn't exactly great inside the core PostgreSQL feature set.
Extensions usually cover important gaps; that's considered part of the design of the DBMS, not an oversight/missing feature per se. E.g. PostGIS, pgvector, etc.

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@MarkCallaghanDB There also is (was?) OreoleDB, which did bring its own buffer pool and data storage, but it was a fairly involved fork, not an extension. It broke with most of Postgres' storage model, including its indexing.

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@MarkCallaghanDB > why not LSM
Complexities integrating a new table AM into PG, presumably.
Adding a new table AM would require a lot of code, and the current page-based buffers don't work nice with the (presumably) variable but larger than page-sized chunks that are implied by the usage of LSMs.

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@MarkCallaghanDB > why not MultiThreaded
Historical reasons. There is work actively ongoing to get multithreading ready, but it'll take some time yet. Hopefully by PG19 we have the capability, though I think it'll take more time to turn it on by default.

[Matthias van de Meent (@[email protected]) @mmeent_pg]

So in line with previous years, I plan to travel to #pgconfeu 2025 by train, not by plane.
Considering I'm already halfway up the Baltics, I might just go back through Estonia and Finland, taking the long way around the Baltic Sea.

[Matthias van de Meent (@[email protected]) @mmeent_pg]

🇳🇱>Riga is only 3 travel days by rail from NL (~48h door-to-door for me), with most of that time on the Warsaw-Vilnius-Riga section waiting for daily trains: with the Warsaw-Riga section by bus it's ~23h (but 10h on a bus🙁)

@RailBaltica could reduce that to just 18h by 2030

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@fujii_masao if the xact is still running it may still have open portals (i.e. queries, through cursors) that were started (opened) before the statement that created/updated/deleted the tuple, and may thus need to see the old tuples, even if it updated/deleted them in later statements.

[Matthias van de Meent (@[email protected]) @mmeent_pg]

@fujii_masao Also note that the same is still true when a tuple's xmin equals its xmax.
One might think that's a trivial case of "nobody can see this", but that's only true if the xact has committed or rolled back, because (see next)