Should I add a new schema for my application?

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Good morning,

I am seeking for a general advice if it would be a good idea for a PostgreSQL/PostGIS using application to add a third schema.

I am using postgis/postgis Docker image which comes with "public" schema.

Then I have loaded OpenStreetMap data into the database and have created "osm_schema" for that:

# psql -p 5432 -U postgres

    CREATE DATABASE osm_database TEMPLATE=template_postgis;

    CREATE USER osm_username WITH ENCRYPTED PASSWORD 'osm_password';

    GRANT ALL PRIVILEGES ON DATABASE osm_database TO osm_username; -- TODO read only

# psql -p 5432 -U osm_username osm_database

    CREATE SCHEMA osm_schema AUTHORIZATION osm_username;

    ALTER ROLE osm_username SET search_path TO osm_schema, public;

After loading OSM data with the osm2pgsql tool I have 3 tables in there:

# psql -p 5432 -U osm_username osm_database

psql (15.2)

osm_database=> \dt

                   List of relations

   Schema   |        Name        | Type  |    Owner

------------+--------------------+-------+--------------

 osm_schema | planet_osm_line    | table | osm_username

 osm_schema | planet_osm_point   | table | osm_username

 osm_schema | planet_osm_polygon | table | osm_username

 osm_schema | planet_osm_roads   | table | osm_username

 public     | spatial_ref_sys    | table | postgres

(5 rows)

And now I need one more table, for the new mapping feature in my application, which would reference the osm_id column in the planet_osm_roads.

I have not used database schemas at all until yet, I am looking for an advice if I should add a third schema here, just for my application?

Would it be a good long term idea and what advantages/disadvantages would that bring with it?

Thank you for any feedback and also I have to note, that I am really amazed by all these software products -

PostgreSQL, PostGIS and also the OpenStreetMap - how smooth the installation has worked for me - just an hour spent and I have the whole planet at my fingertips - this is not coming for free, there are probably decades of effort by many people in the products.

Best regards

Alex