This chapter consists of several recipes to c. We shall start this chapter with recipes that show some of the utilities available for creating and dropping databases. We will see how a database and a table can be located on a file system and then see how a schema can be created. We shall also understand the advantages of using schemas, along with the steps involved in assigning the ownership of a schema to a user. We'll then move on to discuss the methods involved in looking at the size of a table and an index in PostgreSQL.
Over a period of time, database activity may cause objects to grow huge in size. This is the time when we wish to move tables and indexes across different tablespaces to distribute the IOPS across multiple disks. Additionally, you may wish to create archive tables to store old data that is not heavily accessed but only kept for satisfying compliances. For this purpose, we shall discuss the steps involved in creating a tablespace and how to move a table to a tablespace.
We will then move on to user management in PostgreSQL, where we will see how to create and drop a user, how to assign and revoke a privilege from a user, and how to properly manage the segregation of privileges using roles.
Finally, we will end the chapter by discussing how the MVCC implementation is different in PostgreSQL, along with an introduction to VACUUM.
The following are the recipes that will be discussed in this chapter:
- Creating and dropping databases
- Locating a database and a table
- Creating a schema
- Checking table and index sizes
- Creating tablespaces
- Moving tables to a different tablespace
- Creating a user
- Dropping a user
- Assigning and revoking privileges
- Creating a group role for role-based segregation
- MVCC implementation and VACUUM