Currently I'm migrating ASP.net MVC 4 web application Certified Mixtapez to  .NET core 2.2 from Windows Server 2012 & Microsoft Sql Server 2014 to Ubuntu 16.04 and PostgreSQL 11. Since Digital Ocean released Managed Postgres Instances, I decided to try it out.

Getting Started with Managed PostgreSql DB

I created and migrated to the DO Managed Database  in 3 steps!

  • Create 15GB/mo 1GB Postgres Database
  • Import Microsoft SQL Server Existing Data to PostgreSQL with DBeaver
  • Connect DB to Application

Here are the screenshots from Import with DBeaver

Import Data
Select Database to Import From
Select Table to Import From

Getting Started with Postgres Droplet

After creating a managed database, I had several slow running queries after running several webservices. I upgraded the managed database to 8GB ($120) and I was still receiving 18 second responses. I decided to create a postgres droplet.

Installing Postgres & Creating DB - Digital Ocean

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ lsb_release -cs-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

sudo apt-get update
sudo apt-get install postgresql postgresql-contribsudo 
su - postgres
sudo ufw allow 5432						# allow 5432 connections
sudo ufw status
createuser --interactive --pwprompt     # create user for external use
sudo -u postgres createdb cm-web		# create database

Tuning Postgres

I tuned the PostgreSQL instance using PGTune. I added the configuration below to /etc/postgresql/11/main/postgresql.conf

max_connections = 200
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 2
max_parallel_workers_per_gather = 1
max_parallel_workers = 2
listen_addresses = '*' 

External Usage

I opened up the 5432 port by modifying the /etc/postgresql/11/main/pg_hba.conf to match the configuration below:

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer

# IPv4 local connections:
host    all             all             0.0.0.0/0               md5

# IPv6 local connections:
host    all             all             ::0/0                   md5

Restart PostgreSQL

service postgresql restart

Managed Database - Postgres

Pros Cons
Simplicity Performance
No Infrastructure Maintenance No Postgres Extensions
Dashboard for Memory Usage, Queries, and Connections Customization
Upgrade Easily No exporting log file
Expensive $$
  • Simplicity - PostgresSQL is easy to get up and running without installing any dependencies
  • No Infrastructure Maintenance - Database Admin is not needed. Developers don't have to patch servers. This means there shouldnt be any downtime from maintenance unless DO has planned it.
  • Upgrade Easily - Developers can upgrade RAM and storages in seconds
  • Performance - The server has moderate performance for production databases with millions of rows. If you're developing a new product, it will work just fine.
  • Customization - Developers can not install PostgreSQL Extensions to help tune databases. Developer also can not modify postgres.conf file for performance or any special configurations
  • No exporting log file - I didnt see a way to export database logs to run analysis.
  • Expensive $$ - Databases cost alot for RAM
Server Price
1 GB / 1 vCPU/ 10 GB Disk $15/mo
2 GB / 1 vCPU/ 25 GB Disk $30/mo
4 GB / 2 vCPU/ 38 GB Disk $60/mo
8 GB / 4 vCPU/ 115 GB Disk $120/mo
16 GB / 6 vCPU/ 270 GB Disk $240/mo

Droplet - Postgres

Pros Cons
Customization Infrastructure Maintenance
Performance Installation
Cost Efficient Ease of Use
  • Customization - Developers can install any extensions, utilities, and monitoring tools on the Droplet.
  • Performance - DO Spaces performance was better with the same RAM and tuning from PGTune.
  • Cost Efficient - DO Spaces prices are 3x cheaper than Managed Databases.
Server Price
1 GB / 1 CPU/ 25 GB SSD Disk $5/mo
2 GB / 1 CPU/ 50 GB SSD Disk $10/mo
4 GB / 2 CPU/ 80 GB SSD Disk $20/mo
8 GB / 4 CPU/ 160 GB SSD Disk $40/mo
16 GB / 6 CPU/ 320 SSD GB Disk $80/mo
  • Infrastructure Maintenance - Scheduled Patches will need to be install for PostgreSQL and Ubuntu to avail security vulnerabilities.
  • Installion - Developers have to install PostgreSQL to get up and started.
  • Ease of Use - There are not any DB monitoring tools out the box so if you want dashboards, developers will need to install them

Decision Time

Performance is very important to me so I decided to use Spaces. I'm currently running my application on Windows Server 2012 128 GB RAM and 64GB is just for SQL Server. I didn't want to regress in performance plus I'm comfortable with Linux so the installation wasn't a big deal. The price was a major factor since the reason i migrated to Digital Ocean was to get rid of a 1200/mo bare metal server and SQL Server License Fees. I still believe the managed database is a very good option for applications in the early stages of launching or development.