Hacker News Reader

Good list.

Also true indexed organized table aka real clustered indexes.

Oh and real cross connection query plan caching, prepared statements are only for the connection and must be explicitly used. No need to use prepared statements in MSSQL since the 90's

Another thing I'd add is a quasi-sorted uuid generator built-in. Real clustered indexes need approximately sorted UUIDs - which could be version 1 UUIDs with an artificial, per-database MAC address, or they could be something more esoteric such as Twitter's snowflake ID generator.

Using UUIDs for PKs is fine and dandy but clustered indexes and type 4 UUIDs do not play well. Many MS SQL users discover this the hard way when their toy database suddenly has order of magnitudes more rows.

That's what NEWSEQUENTIALID() is for in MS SQL.

I'd love to see the MERGE statement from MSSQL in other databases. Wonderful tool for integrations.

Postgres has UPSERT (INSERT INTO ... ON CONFLICT DO ... )

I wish MSSQL had this.

But this doesn't deal with DELETEs of a true MERGE statement

No, it's not a replacement. It's just a very useful feature that I wish all DBs had.

I've had to personally avoid MERGE statements due to implementation problems in MSSQLServer. One system just updated to 2019, so maybe it is better now. I also have to lookup the syntax each time, but that might be just me.

In some projects the MERGE helped me a lot and in my previous job, all of our integration scripts were merge based. You just learn to avoid the pitfalls, but you're right that it does have some: https://www.mssqltips.com/sqlservertip/3074/use-caution-with...

I'm sure if postgres had it, it wouldn't have had most of those problems.

Merge statement always comes with a bunch of caveats from MSSQL experts, has that changed recently?

It does have a bunch, but all of them are implementation problems, AFAICT. The idea is great though.

That's an interesting list. Can you give more context about how you would like to use these features?

Sure. I have two roles: database driver/client maintainer and database based application developer and maintainer.

As a database Driver/Client developer:

* Having two protocols/data types that two the same thing (text / binary) isn't the end of the world, but it just adds confusion. Also it adds complexity for a different server/proxy/pool implementation. Recommendation: better document binary and add an intent to remove text data types.

* Not having an inline cancellation (in same TCP/IP connection) means cancellation isn't supported by many drivers, and even when it is, there are many edge cases were it stops working. Each client implementation has to work around this.

As an application developer, I typically see three patterns emerge:

1. Put SQL on SQL platform: stored functions / procs. Application only calls stored functions / procs. 2. Put SQL on Application platform: simple stored SQL text, query builders. 3. Make Application dump and use simple CRUD queries and put logic fully in application.

I typically find myself in category (2), though I have no beef with (1). Benefits of (2) include: (a) easier to create dynamic search criteria and (b) use a single source of schema truth for application and database.

* Multiple Result Sets:

- (A) The screens I make often have a dynamic column set. To accomplish this I may return three result sets: row list, column list, and field list. This works for my screens and print XLSX sheets that can auto-pivot the data in, while all the data transfer columns can be statically known. and pre-declared. This allows me to edit a pivoted table because each cell knows the origin row.

- (B) Any reasonable amount of work in SQL may be long-ish (300-1000+) lines of SQL. There are often many intermediate steps and temp tables. Without Multiple Result Sets, it is difficult to efficiently return the data when there are often multiple arities and columns sets that are all relevant for analysis or work. So if I take a fully application centric view (more like application mindset (3)), you just call the database alot. But if you are more database server centric (1) or (2), this can pose a real problem as complexity of the business problem increases. (I'm aware of escape hatches, but I'm talking about strait forward development without resorting to alternative coding.)

- (C) Named parameters are extremely useful for building up query parameters in the application (development model (2)). You can specify a query where-clause snippet, the parameter name and the have the system compose it for you that is impossible with ordinal queries. A driver or shim can indeed use text replacement, but that (a) adds implementation cost and (b) server computation / allocation cost, (c) mental overhead when you see trace the query on the server. Further more it composes poorly with stored functions and procs on the server (in my opinion). It is again not insurmountable, but it is another thing that adds friction. Lastly, when you have a query that takes over 30 or 50 parameters, you must use named parameters; ordinal positioning is too error prone at scale.

- (D) Protocol level language selection. PostgreSQL always starts execution in plain SQL context. If you always execute functions as PL/pgSQL it is just extra overhead. In addition, running ad-hoc PL/pgSQL with named parameters isn't the most easy thing. It is possible, just not easy. This feature plus named parameters so by the time I'm writing my query, I know (a) that I have all my sent parameters available to me bound to names and (b) the first character I type is in the language I want.

The combination of these features would make applications developed in model (2) go from rather hard to extremely easy. It would also make other development modes easier I would contend as well.

> Recommendation: better document binary and add an intent to remove text data types.

I would note that it's required in Postgres for custom types to implement in/out (text encoding), but not required to implement send/recv (binary encoding.) In case send/recv isn't implemented, the datum falls back to using in/out even over the binary protocol.

Given the number of third-party extensions, and the ease of creating your own custom types even outside an extension, I'd expect that deprecating the text protocol will never happen. It's, ultimately, the canonical wire-format for "data portability" in Postgres; during major-version pg_upgrades, even values in system-internal types like pg_lsn get text-encoded to be passed across.

Meanwhile, binary wire-encoding (as opposed to internal binary encoding within a datum) is just a performance feature. That's why it's not entirely specified. They want to be able to change the binary wire-encoding of types between major versions to get more performance, if they can. (Imagine e.g. numeric changing from a radix-10000 to a radix-255 binary wire-encoding in PG13. No reason it couldn't.)

> * Having two protocols/data types that two the same thing (text / binary) isn't the end of the world, but it just adds confusion. Also it adds complexity for a different server/proxy/pool implementation. Recommendation: better document binary and add an intent to remove text data types.

I don't see us removing the textual transport, unfortunately. The cost of forcing all clients to deal with marshalling into the binary format seems prohibitive to me.

What's the server/proxy/pool concern? I don't see a problem there.

> Not having an inline cancellation (in same TCP/IP connection) means cancellation isn't supported by many drivers, and even when it is, there are many edge cases were it stops working. Each client implementation has to work around this.

Yea, it really isn't great. But it's far from clear how to do it inline in a robust manner. The client just sending the cancellation inline in the normal connection would basically mean the server-side would always have to eagerly read all the pending data from the client (and presumably spill to disk).

TCP urgent or such can address that to some degree - but not all that well.

> - (C) Named parameters

I'm a bit hesitant on that one, depending on what the precise proposal is.

Having to textually match query parameters for a prepared statement for each execution isn't great. Overhead should be add per-prepare, not per-execute.

If the proposal is that the client specifies, at prepare time, to send named parameters in a certain order at execution time, I'd not have a problem with it (whether useful enough to justify a change in protocol is a different question).

> A driver or shim can indeed use text replacement ... b) server computation / allocation cost

How come?

> - (D) Protocol level language selection. PostgreSQL always starts execution in plain SQL context. If you always execute functions as PL/pgSQL it is just extra overhead. In addition, running ad-hoc PL/pgSQL with named parameters isn't the most easy thing. It is possible, just not easy. This feature plus named parameters so by the time I'm writing my query, I know (a) that I have all my sent parameters available to me bound to names and (b) the first character I type is in the language I want.

I can't see this happening. For one, I have a hard time believing that the language dispatch is any sort of meaningful overhead (unless you mean for the human, while interactively typing?). But also, making connections have state where incoming data will be completely differently interpreted is a no-go imo. Makes error handling a lot more complicated, for not a whole lot of benefit.

> Yea, it really isn't great. But it's far from clear how to do it inline in a robust manner.

How would you feel about Postgres listening over QUIC instead of/in addition to TCP?

It seems to me that having multiple independently-advancing "flows" per socket, would fix both this problem, and enable clients to hold open fewer sockets generally (as they could keep their entire connection pool as connected flows on one QUIC socket.)

You'd need to do something fancy to route messages to backends in such a case, but not too fancy—it'd look like a one-deeper hierarchy of fork(2)s, where the forked socket acceptor becomes a mini-postmaster with backens for each of that socket's flows, not just spawning but also proxying messages to them.

As a bonus benefit, a QUIC connection could also async-push errors/notices spawned "during" a long-running command (e.g. a COPY) as their own new child-flows, tagged with the parent flow ID they originated from. Same for messages from LISTEN.

> How would you feel about Postgres listening over QUIC instead of/in addition to TCP?

At some point? Probably not a bad idea. Right now? Doubtful it's worth it - the stack doesn't seem ready yet.

> and enable clients to hold open fewer sockets generally (as they could keep their entire connection pool as connected flows on one QUIC socket.)

I don't think it'd be a meaningful step towards that.

We basically have to move to a threaded model to significantly improve the situation.

> You'd need to do something fancy to route messages to backends in such a case, but not too fancy—it'd look like a one-deeper hierarchy of fork(2)s, where the forked socket acceptor becomes a mini-postmaster with backens for each of that socket's flows, not just spawning but also proxying messages to them.

Not gonna happen. The added context switches would be a very significant increase in overhead. The added processes themselves are but free either. Postmaster not being the immediate parent would complicate the state handling a good bit.

EDIT: expand #2

"better document binary and add an intent to remove text data types"

For all-binary, the client would have to know about all data types and how to represent them in the host language. But that seems clunky. Consider NUMERIC vs. float vs. int4 vs int8: should the client really know how to parse all of those from binary? It makes more sense to optimize a few common data types to be transferred as binary, and the rest would go through text. That also works better with the extensible type system, where the client driver will never know about all data types the user might want to use. And it works better for things like psql, which need a textual representation.

The main problem with binary is that the "optimize a few columns as binary" can't be done entirely in the driver. The driver knows which types it can parse, but it doesn't know what types a given query will return. The application programmer may know what types the query will return, in which case they can specify to return them in binary if they know which ones are supported by the client driver, but that's ugly (and in libpq, it only supports all-binary or all-text). Postgres could know the data types, but that means that the driver would need to first prepare the query (which is sometimes a good idea anyway, but other times the round trip isn't worth it).

"Not having an inline cancellation (in same TCP/IP connection)"

This is related to another problem, which is that while a query is executing it does not bother to touch the socket at all. That means that the client can disconnect and the query can keep running for a while, which is obviously useless. I tried fixing this at one point but there were a couple problems and I didn't follow through. Detecting client disconnect probably should be done though.

Supporting cancellation would be trickier than just looking for a client disconnect, because there's potentially a lot of data on the socket (pipelined queries), so it would need to read all the messages coming in looking for a cancellation, and would need to save it all somewhere in case there is no cancellation. I think this would screw up query pipelining, because new queries could be coming in faster than they are being executed, and that would lead to continuous memory growth (from all the saved socket data).

So it looks like out-of-band is the only way cancellation will really work, unless I'm missing something.

"Multiple Result Sets... you just call the database alot"

Do pipelined queries help at all here?

"Named parameters are extremely useful for building up query parameters"

+1. No argument there.

"Protocol level language selection"

I'm still trying to wrap my head around this idea. I think I understand what you are saying and it sounds cool. There are some weird implications I'm sure, but it sounds like it's worth exploring.

> The driver knows which types it can parse, but it doesn't know what types a given query will return. [...] Postgres could know the data types, but that means that the driver would need to first prepare the query (which is sometimes a good idea anyway, but other times the round trip isn't worth it).

We return the types of the result set separately even when not preparing. The harder part is doing it without adding roundtrips.

I've argued before that we should allow the client to specify which types it wants as binary, unless explicitly specified. IMO that's the only way to solve this incrementally from where we currently are.

> Do pipelined queries help at all here?

We really need to get the libpq support for pipelining merged :(

"I've argued before that we should allow the client to specify which types it wants as binary, unless explicitly specified. IMO that's the only way to solve this incrementally from where we currently are."

I don't understand the idea, can you clarify?

We should allow the client, on the protocol level, to add (and presumably remove) types to a server side list of types that can automatically be sent in binary if the result format is specified as 'auto'.

That way a driver can add handling for ~10 builtin types (bytea, int[48], float[48], timestamp, ...) that matter from a size/efficiency POV, without needing to handle all the other types. That'd provide a large large fraction of the potential benefit of handling binary for all types.

The reason for the server side list is that otherwise I don't see how you could do it without an added roundtrip.