An important aspect of building and managing a database is organizing the objects within your database. You may have tables that support different functions, for example, a schema for warehousing operations and another for sales. Some logins may need access to some tables, but not others. You might want to isolate one set of objects … Read more
Configuration parameters are the most mythical creatures in the world of PostgreSQL. Ordinary PostgreSQL users often do not know that PostgreSQL configuration parameters exist, let alone what they are and what they mean. There is a good reason for such ignorance since, in real life, ordinary users don’t have any say in how these parameters … Read more
In the first security article, PostgreSQL Basics: Roles and Privileges, I discussed how roles (users and groups) are created and managed in PostgreSQL Depending on your background with permissions, particularly in other database products, some of those nuances in how permissions work may have been surprising. Understanding how roles and privileges work in Postgres is … Read more
One of the most important aspects of data management is the ability to ensure that the data in your database is well defined and consistent. Some aspects of that are ensured through the relational data structures you design. Another piece of control is using the correct data type. Then, we get to constraints. A constraint is a way to validate data prior to adding it to your database. This is one more tool in the toolbox that helps you maintain good data.… Read more
Information security, whether in databases, applications, or larger IT systems, is based on the principles of Authentication and Authorization, often referred to as AuthN and AuthZ respectively.
Likewise, PostgreSQL security involves creating roles that allow access to server resources (AuthN) and then granting privileges to database objects appropriately (AuthZ). Understanding how each of these components work together is essential to effectively manage security in a PostgreSQL cluster.… Read more
As with any other relational data management system (RDBMS), PostgreSQL uses indexes as a mechanism to improve data access. PostgreSQL has a number of different index types, supporting different behaviors and different types of data. In addition, again, similar to other RDBMS, there are properties and behaviors associated with these indexes.
In this article I’m going to go over the different types of indexes and some index behaviors. We’ll get into what the indexes are, how they work, and how best you can apply them within your databases. I’m hoping you’ll develop an understanding of which indexes are likely to work better in each situation.… Read more
PostgreSQL database performance is critical for application performance. EzzEddin Abdullah shows how to get information about a query’s performance from the execution plan.… Read more
PostgreSQL has an unusually large number of data types. Grant Fritchey reviews each data type and discusses the good and bad points of each.… Read more
You can perform PostgreSQL backups with the pg_dump command. In this article, Grant Fritchey explains how to back up and restore with the pg_dump and pg_restore utilities.… Read more
The syntax for creating databases and tables in PostgreSQL is similar to other RDBMS. In this article, Grant Fritchey shows how to create databases and tables.… Read more
Database professionals often work with more than one database platform. Grant Fritchey explains setting up and connecting to PostgreSQL database.… Read more
