The problems arise when it doesn't automatically do what you want it to do and you don't have a good way of fixing it yourself. I'm not in a position to understand init systems so I will be in this camp if there's ever any problem with any init system. What matters to me, as the end user, is how likely it is to work automatically.
If systemd 'just works', then what's the problem? If systemd is broken... well, that's a problem, isn't it. But from what I've seen a lot (though not all, of course) of the arguments against it are highly political/philosophical and aren't based on whether it's actually working or not.
So if your init system is handling hotplug events, then that's some bad design. systemd doesn't stoop that low, though it still has plenty of mistakes - like putting the INI parser in PID1. As an example, even Apple put launchd's XML parser as a separate process!
This after having udev exist as a independent project for a decade.
These days the only way to download and install udev manually is to download the whole of systemd and then extract udev from that. Something that caused Gentoo to fork udev into eudev.
Honestly, I'll take a dozen 5-line unit files over a single "portable" init script that implements its own utility library, or yet another "simple" init script that's 90% standard boilerplate and still doesn't work because the 10% doing the real work doesn't check PID files correctly ("I can't just kill all processes called foo?") ...
Anyway, the point is that systemd models system states declaratively, solves the dependencies and executes the necessary steps required to reach that state, all without explicitly telling it what to do. If your dependencies are declared correctly, there is little danger of misbehaviour. The advantage to this is that parallelization is free and you never need explicit waits. In practice of course there will be misbehaving units and incomplete dependency declarations, but I think as admins gain experience with systemd those kinds of issues will drastically decrease. You'll still be able to run plain old scripts from unit files. I do that on my home server to start containers. since the contained process doesn't background itself and just prints to stdout and stderr, when started via systemd all its output will go into the journal and the tail shows up when I do "service container@foo status".
Analogous to SQL, I suppose something like "systemctl explain" might be useful. There's list-dependencies, which gets halfway there.
See the 'On Upstart' section here [1].
A recent failure of systemd i have seen was related to NFS. For some reason systemd would yank the network down before dismounting NFS.
If you don't unmount NFS before taking down the network, it will sit there forever waiting for that final server response.
Another example was where while mount would happily ignore a flawed fstab entry but mount the rest, systemd would bail on the whole mount task and so halt the boot process.
This isn't specific to systemd – I had to deploy workarounds to the same bug in RHEL, Debian, Ubuntu, FreeBSD, etc.
I've never messed with systemd configuration too much, but is it much more complicated than a make-style "how do I get there from here? OK lets do that"?
There have been hundreds of thousands of words written on how simple utilities can form the basis of supportable, complex systems and still retain the ability to be comprehensible. He has dismissed all that as beneath consideration, and he has the One True Vision™ on how Unix should be architected.
Don't get me wrong, hubris and self-righteousness have given us great things: GNU, OpenBSD, qmail, etc. But I don't think anyone would accuse rms or theo or djb of not being callous and myopic.
I don't see him passing a value judgement on how it should be done, just dismissing a particular criticism as poorly founded. If you look at the difference between the Unix and Linux development models, there is a point. There are a lot of properties to Unix so it's a bad argument to say "not Unixy enough" when you really mean something more along the lines of "components take on too many responsibilities at once and are too tightly coupled."
the whole of systemd sits in a single source tree in freedesktop.org. You don't find system-networkd in one tree, udev in another, systemd-init in a third.
In contrast, each part of XFCE, the window manager, the panel, etc, sit as their own source trees within the XFCE repository.
"...to make it clear, I think Upstart actually has its benefits. The source code is very, very nice, and it’s very simple, but I think it’s too simple. It doesn’t have this engine that can figure out what the computer is supposed to be doing."
is callous and myopic?