>>> Isabella Ghiurea <isabella.ghiurea@xxxxxxxxxxxxxx> 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
