anarazel

FWIW, the set of procedural languages in postgres is runtime extensible: https://www.postgresql.org/docs/current/sql-createlanguage.h...

The absurd thing about it is that IO bound code IME often is way easier to optimize than CPU bound code. Adding batching or pipelinining is often almost mechanical work and can give huge speedups.

> That's still an architectural thing. If CPU isn't an issue, why would you be running your application server on a different machine from your database?

Because that'll be too much of a scalability limitation. Rails etc are rather CPU heavy. In cloud environments it's also typically much more feasible to scale the stateless parts up and down than the database.

> Even if you have it on another machine, it's on the same switch, right (also an architectural choice)?

Yep, was on the same switch in my example.

> So latency should be sub millisecond, and irrelevant for a single request.

In my testcase it was well below a millisecond (2.8k QPS on a single non-pipelined connection would not be possible, it implies a RTT <= 0.35ms), but I don't at all agree that that makes it irrelevant for a single request.

For typical rails like applications that are "bottlenecked on the database", latency is a much bigger factor than actual query processing performance. Even if careful about placement of the database and "application servers".

Just as an example, here's the results of pgbench -S of a single client, with pgbench running on different servers (a single pkey lookup):

pgbench on database server: 36233 QPS

pgbench on a different system (local 10gbit network): 2860 QPS

It's rare to actually have that low latency to the database in the real world IME.

If the same pkey lookup is executed utilizing pipelining, I get ~145k QPS from both locally and remotely.

> I don't think it can be used to construct logical changes.

It can: https://www.postgresql.org/docs/current/logicaldecoding.html

It's not entirely from the WAL though, some catalog accesses are necessary for metadata (shape and name of tables etc).

I got Elon tweets in my notifications, despite having him blocked. That was the last straw for me.

There's a patch for that, but JITLink doesn't work for all the versions of LLVM we support. At least not on enough architectures.

Fwiw, a good portion of IO via io_uring is not executed via the threaded work queue anymore. Even with buffered file reads it's avoided for some common filesystems.

You're right of course that there are lots of cases (missing metadata, synchronous operation like extending files, ...) where it's all offloaded to the wq.

> Especially since launching postgres is equally easy and fast as sqlite

It's definitely not as fast to start postgres as it is to start sqlite. Pretty much inherently - postgres has to fork a bunch of processes, establishes network connectivity etc. And running trivial queries will always be faster with sqlite, because executing queries via postgres will require intra-process context switches.

That's not to say postgres is bad (I've worked on it for most of my career), but there just are inherent advantages and disadvantages of in-process databases vs out-of-process databases. And lower startup time and lower "dispatch" overhead are advantages of in-process databases.

Postgres does have infrastructure to avoid this in cases where the result is reused, and that's used in other places, e.g. array constructors / accessors. But not for jsonb at the moment.

> If you have a user table with a non-nullable email column and a nullable username column, and a uniqueness constraint on something like (email, username), you'll be able to insert multiple identical emails with a null username into that table - because a null isn't equivalent to another null.

FWIW, since 15 postgres you can influence that behaviour with NULLS [NOT] DISTINCT for constraints and unique indexes.

https://www.postgresql.org/docs/devel/sql-createtable.html#S...

EDIT: Added link

FWIW, PG's default has been flipped to checksums being enabled by default in the in-development version.

Personally I'm not sure it's the right call, as it comes with some increased write overhead.

CRC32C is not that cheap, even with hardware acceleration. Postgres uses CRC32C for the WAL an it definitely shows up in profiles, despite HW acceleration. And that's at a far lower throughput than you can have with e.g. sequential scans. Which is why postgres chose a different checksum (FNV) for data checksums...

It's been only four days since a phone alarm would have let me sleep through giving a talk. Android rebooted, somehow got stuck in a reboot loop (I think), discharging faster than the crappy charger charged. Luckily jetlag worked in my favor for once...

You can implement a different mvcc model today, without patching code.

> Even the extension systems, which have the opportunity to hook directly into DB internals (and has no standardization to bother meeting) end up with SQL as the API to do actual db operations.

FWIW, nothing forces an extension to do so. I'm pretty sure there are several that do DML using lower level primitives.

> I'd be curious how much effort and specific shared-memory-IPC-expertise is required on the part of the Postgres maintainers to keep the shared memory layer capable and secure.

I don't think security plays a huge role for shared memory in postgres - if an attacker gains arbitrary code execution in one postgres backend, the installation is hosed. No need to go through SHM to escalate to other backends, there's easier ways.

WRT capable: There's definitely substantial costs due to using inter-process shared memory. But it's more an architectural cost, rather than something that everyone has to bear while just doing mostly unrelated hacking. Some features get harder, less flexible and require more code.

FWIW, there's some work towards moving towards a threaded connection model. Still some way to go, but I expect it to happen eventually.

FWIW, the biggest problem I've seen with efficiently using io_uring for networking is that none of the popular TLS libraries have a buffer ownership model that really is suitable for asynchronous network IO.

What you'd want is the ability to control the buffer for the "raw network side", so that asynchronous network IO can be performed without having to copy between a raw network buffer and buffers owned by the TLS library.

It also would really help if TLS libraries supported processing multiple TLS records in a batched fashion. Doing roundtrips between app <-> tls library <-> userspace network buffer <-> kernel <-> HW for every 16kB isn't exactly efficient.

You can, you just need to use savepoints.

The bottleneck isn't at all the checksum computation itself. It's that to keep checksums valid we need to protect against the potential of torn pages even in cases where it doesn't matter without checksums (i.e. were just individual bits are flipped). That in turn means we need to WAL log changes we don't need to without checksums - which can be painful.

Historically there was no atomicity at 4k boundaries, just at 512 byte boundaries (sectors). That'd have been too limiting. Lowering the limit now would prove problematic due to the smaller row sizes/ lower number of columns.

It's indeed used:

Definition of macro: https://sourceware.org/git/?p=glibc.git;a=blob;f=include/lib...

Use: https://sourceware.org/git/?p=glibc.git;a=blob;f=string/memm...

For a bunch of other places -fno-builtin-* seems to be used.

Nitpick: If you're careful fdatasync() can suffice.

The linked code is actually just for the variables in psql (the commandline tool). For the code parsing input to the SQL-level bool datatype, it's

https://github.com/postgres/postgres/blob/master/src/backend...

which uses

https://github.com/postgres/postgres/blob/master/src/backend...

In my experience, the conflict in many bigger orgs isn't even on the cost vs profit axis, it's on the tangible vs non-tangible axis. It's a lot easier for middle managers to show they did well if they deliver customer impacting features than a nebulous "improved security". This is item true even when higher up management actually wants to invest in security.

> Meh. Well. Good to hear on the cleanup. Didn't know it used to be different :/

If you want to be scared: Until not too long ago postgres' supervisor process would start some types of subprocesses from within a signal handler... Not entirely surprisingly, that found bugs in various debugging tools (IIRC at least valgrind, rr, one of the sanitizer libs).

> Re. SIGFPE, to be fair, it feels a bit like the "asynchronous vs. synchronous abort¹" thing on CPUs; synchronous aborts are reasonably doable while on asynchronous aborts you're pretty much left with torching things down far and wide.

Agreed, I think it's quite reasonable to use signals + longjmp() for the FP error case. In fact, I think we should do so more widely - we loose a fair bit of performance due to all kinds of floating point error checking that we could set up to instead signal.

It unfortunately is used from within signal handlers, albeit only in specific cases (SIGFPE). There used to several more, but we luckily largely cleaned that up over the last few years.

> I just looked at the locking around running queries on a replica, and that's clearly never going to be correct

Uh, huh. Details please?

Just because I spent a few hours in the postgres parser guts lately:

Postgres doesn't quite have the span of the error, just a single location :). We can of course measure the length of the token at the error point, but that isn't quite the same, as the cause of the error does not have to be a single token.

  postgres[122581][1]=# SELECT * FROM kdjfkdj;
  ERROR:  42P01: relation "kdjfkdj" does not exist
  LINE 1: SELECT * FROM kdjfkdj;
                        ^

Where I think ML would be much better than what we (postgres) do, is iteratively improving selectivity estimation. In today's postgres there's zero feedback from noticing at runtime that the collected statistics lead to bad estimates. In a better world we'd use that knowledge to improve future selectivity estimates.

> In order to do that, they purposefully don't explore the true planning space...they might explore 3-10 alternative ways of executing, whereas there might be hundreds or thousands of ways to do the same thing.

FWIW, often postgres' planner explores many more plan shapes than that (although not as complete plans, different subproblems are compared on a cost basis).

> While Postgres has explicitly chosen to not implement planning pragmas to override planner behavior, it would be really cool if you could have multiple planners optimized for different types of workloads,

FWIW, it's fully customizable by extensions. There's a hook to take over planning, and that can still invoke postgres' normal planner if the query isn't applicable. Obviously that's not the same as actually providing pragmas.