It is often used to render OSM data visually using Mapnik, as Mapnik can query PostgreSQL for map data (Mapnik can read directly from other data source types including raw osm files directly, but PostgreSQL is the most efficient and most commonly required by stylesheets). For more information on using osm2pgsql to render OpenStreetMap data with Mapnik, see http://wiki.openstreetmap.org/wiki/Mapnik