Re: DB novice questions

>>> Isabella Ghiurea <isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)ca> wrote:

> 1- when issue create db , is there a option to specify the database

> page size ? what's the default ? ( don't see any comments in docs)

Page size is set at compile time. The default is 8kB. You probably
don't need to fuss with this, as large data is automatically moved out
of the base row into a "TOAST" table on an as-needed basis, row by
row. Feel free to define, for example, varchar columns larger than
the page size.

> 2-any basic tips for cfg to achieve some reliable database I/O
> performance , my first toughts are to create at least 2 additional
> table spaces: one for indexes and one for data.
> when creating the db , how can I specify a separate table space for
:s
> ystem catalog , data and indexes ?( any special syntax , can't find
to
> many examples in PG docs)

You may not need to define additional table spaces, especially if you
have (as you really should) a good battery-backed RAID controller.
Throwing everything into one big RAID and letting things take their
course across all those spindles often out-performs the most careful
hand-tuning.

You will need to adjust some configuration options for best
performance on a reasonable machine -- like most products, the
PostgreSQL defaults are designed to let it at least start up on an old
desktop workstation someone might want to use to "try things out".
The most critical settings to look at, described pretty well in the
documentation, are:

listen_addresses
max_connections
shared_buffers
work_mem
maintenance_work_mem
checkpoint_segments
effective_cache_size

If you get those set right, you're well on your way.

> 3- what is the best practice to use ad owner for a db , if I create

> the db as user: postgress , any implication for not using this
user
> as owner of all db's ?

That depends on your environment. In ours, we routinely do something
like:

create user xxxowner with password 'asdf';
create user xxx with password 'asdf';
create user viewer with password 'asdf';
create database xxx with owner xxxowner;
\c xxx
revoke create on database xxx from public;
revoke create on schema public from public;
grant create on schema public to xxxowner;

Then we have xxxowner create all database objects and grant
appropriate rights to other users. It's best to use the database
superuser only for tasks which require that. In our shop we don't
allow remote access by the superuser, you have to ssh to the box, sudo
su to the superuser OS login, and then connect. See the documentation
for pg_hba.conf to see how to manage access.

I hope this was helpful.

-Kevin