The seemingly highest used Linux distribution used for hosting PostgreSQL is a Red Hat based system, such as CentOS or Scientific Linux, or even Red Hat itself. For this discussion, we’ll stick to Linux based systems. However performance on Windows is not even comparable to a Unix-like system, so unless it’s for a small throw away project, sticking to an established Unix-like system will be the way to go. PostgreSQL can be compiled on most Unix-like operating systems, as well as Windows. Depending on the applications needs, a sufficient host as well as a well-tuned PostgreSQL configuration will have an amazing impact on the performance of the database system. The main pillars needed to reliably set up PostgreSQL are based on the CPU, Memory, and Disk abilities of the host. When (or if) the application grows to the point that the majority of data cannot be stored in memory, disk access speed is crucial.
Keep an eye out for whether or not the cloud server is shared or dedicated (dedicated allowing full performance from the server for the application), as well as the level of IOPS (Input/output Operations Per Second) provided by a cloud server. Unless the cloud platform chosen is highly optimized (which generally means higher price), it may have trouble with higher load environments.
Hosting a database in the cloud can be wonderful in some aspects, or a nightmare in others. This however comes with the need to have an expert on hand (or contract) to do server maintenance. This also allows for tighter control over CPU, Memory, and Disk setups. With an on-premise database, the best option is for a bare metal host, as Virtual Machines generally perform slower unless we’re talking about high end enterprise level VM’s.
Here, we will go over a basic setup for new projects, and how to set PostgreSQL up to run the most optimally on new projects. When a new server is installed, PostgreSQL’ s settings are very minimum as they are designed to run on the least amount of hardware possible. Regardless of the application, it’s likely that some setup and configuration help will be needed to get the database ready for action. Welcome to PostgreSQL, a powerful open source database system that can host anything from a few megabytes of customer data for a small-town-business, to hundreds of terabytes of ‘big data’ for multinational corporations.