One of the most useful constructs in SQL Server is the stored procedure. It gives you a way to do several things. First up, you can store code within the database. Next, you can parameterize queries so that you’re not hard coding or generating ad hoc queries every time you want to call them. You … Read more
While there are many features within PostgreSQL that are really similar to those within SQL Server, there are some that are unique. One of these unique features is called VACUUM. In my head, I compare this with the tempdb in SQL Server. Not because they act in any way the same or serve similar purposes. … Read more
Writing queries to retrieve the data from a database is probably the single most common task when it comes to working with data. Working with data in PostgreSQL is no exception. Further, PostgreSQL has an incredibly rich, wide, and varied set of mechanisms for retrieving data. From standard SELECT… FROM… WHERE to windowing functions and … Read more
So far in the series I’ve shown how to create databases, tables, constraints, indexes and schema. Now, it’s time to put some of that information to work and begin the process of manipulating data within the database. After all, a database is only useful if there’s information stored within. PostgreSQL makes use of standard SQL … Read more
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
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
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
