What is PostGIS?
[1] It is an extension to PostgreSQL. One of the strengths of postgres is that was designed from the ground up to be highly extensible, allowing such third party developments as this to be created.
[2] PostGIS adds a number of geometric data types to postgres together with functions and operators that allow SQL queries to be performed on these data types in exactly the same way that queries are performed against the regular numeric and text data types.
PostGIS is primarily about vector based representations of spatial data. However, rudimentary support is included for raster objects but additional third party extensions are required to make use of this support.
[3] Significantly, PostGIS follows key OGC standards. These include the data representations that it supports – including the OGC ‘well known text’ and ‘well known binary’ formats, and the range of simple features for SQL –points, lines and polygon types etc.
The OGC specifications also describe a catalogue schema that lists tables within a database that contain geometric fields. PostGIS comes with scripts to build those tables and with functions to update them.
What PostGIS is not –
It is not a desktop GIS system – it simply adds various functions and data object types to the database. It is a data source, and requires additional software to make use of that data. There are some projects to facilitate using postgis data sources in ArcGIS, although I have not tried this myself.