I cannot find documentation or discussions on what is and how to use Drop-in plugins.
The question was raised in this Q&A, Populate content on install.

Is a wp-content/install.php file that redefines the pluggable function wp_install_defaults a Drop-in plugin or not?

My research results

The following list is from hakre’s article (which seems to be the origin of all other copies in the interwebs).

File                Type of Plugin                          Loaded                          Context
advanced-cache.php  Advanced caching plugin.                on WP_CACHE value               Single
db.php              Custom database class                   always                          Single
db-error.php        Custom database error message           on error                        Single
install.php         Custom install script                   on install                      Single
maintenance.php     Custom maintenance message              on maintenance                  Single
object-cache.php    External object cache                   always                          Single
sunrise.php         Executed before Multisite is loaded     on SUNRISE value                Multi
blog-deleted.php    Custom blog deleted message             on deleted blog                 Multi
blog-inactive.php   Custom blog inactive message            on inactive blog                Multi
blog-suspended.php  Custom blog suspended message           on archived or spammed blog     Multi

Source: Data taken from global function _get_dropins() in wp-admin/includes/plugin.php.

In the Codex a Drop-in is defined as:

The concrete PHP-file

At first, there’s nothing on [wp-hackers] list. And searching for “drop-in + wordcamp” I’ve only found this of relevance ^{my emphasis}:

One of the great strengths of WordPress is its plugin system. In the upcoming 2.1 release, there is very little you can’t do as a drop-in plugin. This makes it easy to use WordPress as a CMS and keep your customizations separate from the main codeline; which in turn simplifies upgrades and maintenance.

To answer your question, Yes

In fact, there are many other functions you can override in that file, it’s included as the first line of “wp-admin/includes/upgrade.php” & there are many functions inside to be overridden

Unfortunately, there is not too much information available regarding drop-in plugins but i’ll try to put a few points

1. They are distinct to normal plugins in the sense that they can’t be packaged & installed through wordpress admin.
2. Using them means the owner manually uploaded the plugin at the correct location. So it’s assumed that it’s written & added by the owner themselves.
3. These plugins are only meant to have code specific to the particular wordpress site.
4. Since they are added by the owner, they can’t be controlled (activated/deactivated) from admin panel. The include statement is hardcoded in the core.
5. They don’t need to have any file headers.

To summarize, they are php files hardcoded to be included(if exist), whenever the corresponding functionality is being loaded by wordpress.

To use them, just create a php file with that name inside the “wp-content” directory & it will be automatically included. That file can of course include any number of other files as needed.

EDIT

As toscho pointed out,
1) Drop-ins(if available) are also listed in the wordpress admin panel with all the other plugins. Though unlike other plugins, you don’t have the control to de-activate them from there.
2) WordPress, when upgrading itself(core upgrade), does not alter the “wp-content” directory. For this reason, these plugins never change between upgrades.
3) You can define a constant in the wp-config.php to change the “wp-content” directory to some other. The constant you need to define is WP_CONTENT_DIR. This also changes the defaults for other constants such as plugins directory, so you’ll need to take care of that as well, if needed