Should I add a new schema for my application?

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Should I add a new schema for my application?
Date: 2023-04-15 10:06:21
Message-ID: CAADeyWg7SB37GshGHgZS1xZLKtYuZ7K67RW3dNEP=PBC369f-g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter J. Holzer 2023-04-15 10:46:59 Re: JSON / ASP.NET AJAX Dates support in PostgreSQL
Previous Message Federico 2023-04-15 06:54:36 Re: Guidance on INSERT RETURNING order