Postgis is an extension to Postgres, rather than a stand alone application, that provides a spatial data type to Postgres, and provides numerous spatial functions that operate on geometry(ies). Spatial indexing, which you will surely need to find n closest points efficiently, is implemented as an extended R-tree, but the indexing mechanism comes from Postgres's Gist indexing. So, as such, by using Postgis you get the best of both worlds -- all of the ACID features of an RDBMS, plus very efficient storing and indexing of spatial types. So really, you face no choice at all, choosing Postgis, is implicitly choosing Postgres and its many features.
As far as finding nearest features are concerned, you can either use ST_DWithin or if one of the points is a constant, you can use the new operators in Postgres, <-> and <#>, for very fast index lookup of nearest points. Here is a good post on the latter. Any of these will require you to have a spatial index.
You could do the above directly without any use of spatial types, but you are likely to find that it won't scale well without an index that works in two dimensions. Also, but using Postgis, you are future proofing in that if you suddenly have a need to buffer points, or convert to another coordinate system (or countless other things) you will immediately have the capability.
As an aside, I have worked a lot on MySQL spatial, before moving to Postgres/Postgis, and I can tell you without any prejudice that in terms of functionality, speed and community, it comes a very poor second. If you are already using MySQL, I am sure that finding n-nearest neighbours will not be a problem, but if you are looking into choosing a spatially enabled RDBMS in general, I would whole heartedly recommend the Postgres/Postgis route. There is a Stack Overflow question about different spatial DBs -- this is also a shameless plug for an answer of mine :-)
