Infrastructure from scratch. Part 6. Database

Database question is probably must have the most reliable answer in all service questions. The more you ensured what to use and how to maintain – the more fast and reliable your system will be.

It’s happened I didn’t make a choice started to work with PostgreSQL. But I never mentioned it, cause Postgres is great for relations building. Won’t describe how Postgres is great, evangelism should not busy engineer’s brain.

DB administration and development don’t include ready solutions. That’s complicated, but interesting for DBA to build the software stack around database system.

Disclaimer: this post matters only SQL databases. I didn’t work yet to work with NoSQL.

Reliable failover (or thank you, RDS engineers)

---

My PostgreSQL instance based on RDS engine by Amazon. So, trying to save the data and get ready to restore, there are 2 one-click solutions:

1. Allocated storage backups. Usually it’s a daily backup window at the time when service load is small. So, you don’t need to care about physical backup issue. I’d suggest to place logical pg_dump in your backup additionally.
2. Multi-AZ option. The point is: have a logical replication to the replica on another availability zone. The replica is not for query processing – even SELECT statements. It’s only failover feature. If your main instance will be crashed – there will be DNS switch to your replica which takes a minute. Nice to recognize your application backbone is secured.

Actually engineers maintaining RDS made a brilliant talk at re.Invent. I suggest you to watch this. It’s not only RDS mastering, but useful recommendations about general performance improvement.

Every year RDS theme called on re.Invent. So, check the video from conferences, this one seems older.

Pgpool II or PgBouncer?

---

Database connections usually intended to perform the same tasks for the same endpoints. For example, application could make 10 connections and send similar kind of queries. Mostly it’s a SELECT.

What about pooling? Use only one connection to unload DB server, prevent overhead on new process fork and so on? That’s the goal of connection poolers! PostgreSQL case has 2 popular third-party solutions: PgPool II and PgBouncer. I tried both and can describe what does the each one stand for.

PgBouncer has much easier customized pools, clarified documentation and simple architecture. Plus clear logging and metrics collection.When you need only pooler – choose the PgBouncer.

The feature of PgPool II that it’s not only pool (strange to tell, but it is). It’s also replication engine and load balancer. Replication is doing by simple INSERT/UPDATE/DELETE queries execution on slave server. Somebody used this for replica maintenance, but I wouldn’t. It’s too flakey engine for such a big issue as replication is. Load balancing seems to be good idea described next.

Query load balancing? In finance?

---

PgPool includes a weight-based load balancing algorithm. You may set coefficients and regulate where should be more or less requests. Non-SELECT queries go only to the master you set in config file. The SELECT queries is distributed by weight. There is some overhead by default. PgPool always checks does the table is temporary or lies in system catalog and so on. The check method is extra SQL query to the master. It could be disabled except pg_catalog check.

The picture looks fine. We started to distribute the queries on few replicas, but…what about finance? The versioning DB usually keeps the newest version on master and older on slaves. It simply copies to replica, but it takes some time: half-second, quarter, but it takes.

So, after transaction user checks the balance info. His query redirected to slave with old tuple version about balance. And some time user looks at the same balance he had before. That’s the mostly logical question, but it exists. I just want to say that load balancing is not always great idea. Before doing this, figure out what kind of data you serve and is it OK to show the older tuples once in awhile. We skipped this feature.

Close everything

---

This task is simple, but requires accuracy. Block all TCP/UDP connections to database server. Leave only DB port for your application instances. If you use connection pooler – leave the port only for pooler. If necessary, leave the SSH access, but move it from 22 port.

It’s going to prevent your DB from network flood, hacking and similar stuff. Also establish only TLS connections with applications for extra authentication. RDS supports this by default.

Privilege separation

---

Don’t use the superuser! Alright, you can, but only in emergency. Make the DBMS account for each user and grant only operations they need. Necessary task to prevent your system from data leak.

The appropriate structure is:

1. Administrator. Almost all than superuser can except sensitive data like pg_shadow table in Postgres;
2. Developers/managers/stakeholders + monitoring user. Only SELECT. Developers can edit database schema by the source code executed by app users. By itself developers cannot change something in production. What’s done is done. Only verified users can revert changes in emergency. Monitoring should have a SELECT grant only on tables you need to monitor.
3. Application users – all kind of CRUD, but restricted in only one DB. They shouldn’t have any access to the databases of neighbor apps. How it works? When one component has been compromised – another ones will keep safe. Because attacker didn’t get an access to them.

PostgreSQL doesn’t have users – it has roles concept. It’s much more flexible. I only set up 3 basic roles mentioned above and pinned the custom roles to them. Later on any privilege change process will be easier. You won’t need anymore to change grants for many users – just change the one role!

Mastering parameters

---

By default every DB system has light settings after installations. Because there is idea the database should work on every kind of hardware with any resources capacity. To revert this setting for your service environment you must think it over where to change and why.

PostgreSQL has a brilliant documentation where configuration parameters explained in 18 chapter. Take some time to run through this source and compare the possible solutions with what you have. Pay close attention on shared_buffers, max_connections, maintenance_work_mem. There is a food for thought customizing WAL parameters, background writer and autovacuum. Additionally logging needs to take care for better DB server workflow understanding and troubleshooting.

There might be thought like “It’s useless, because it works by itself!”. But that’s the culture question mostly. You have to know how your mechanisms is set up, where to roll and what kind of customization exists.

Too small for clusterization

---

After main maintenance work I started to think about sharding, partitioning for scale up. But that’s the culture issue again: don’t do this while unnecessary. While you don’t have a high load. Start the capacity planning at first before trying to scale your database.

At 2016 PostgreSQL and still don’t have a built-in cluster solution. There is PgProxy, Postgres-XL, DTM and so on. But still no one is built-in and reliable. So, be careful when thinking about Postgres clusterization. There could be some easier and reasonable solutions to speed up and scale your database. Partitioning is much better, but has small limits.

Working with versioning lacks

---

Versioning systems is faster than lock-based one by default. But it also lacks obsolete data cleanup. Transaction wraparound story is the most nasty. Append to this bloated indexes, dead tuples and so on.

For this issues autovacuum is your best utility. Autovacuum is a background process waking up when in fixed time interval (autovacuum_naptime) or limits reached (autovacuum_vacuum_threshold). It cleans up old tuples, rebuild indexes and freezes old transactions to get more IDs for the future. And overhead is not big when supporting this background process. Disabling autovacuum seems the bad idea. Your database starting to work slower having too much versions of your tuples.

As a one more thing to ensure yourself, I’ve got a simple script for periodic VACCUUM execution. Figure out where’s the limit, monitor where less requests and make a VACUUM where limit was reached. Manual solution also exists, but as a operation engineers we have to automate the stuff like this.

Version upgrade

---

Minor version upgrade is built-in RDS feature. But I always train the upgrade on test instance at first. In emergency case. Major version upgrade is much more complicated and service-customized action. Before production upgrade ensure yourself that every(!) query is working as should be and performance didn’t got worse.

Monitoring

---

I mentioned monitoring question few posts ago. Here I simply want to call the metrics I set and care.

1. System metrics. In RDS there is only one way to get this – make API calls to CloudWatch. Not bad, but costs something. Here is: CPU and memory utilization, read/write IOPS, waiting operations count (RDS feature) and network traffic (as incoming, as outgoing)
2. PgBouncer metrics. This tool includes powerful admin console as a different database. You can watch some useful statistics there. I set the track on pool connections count and amount of sent traffic.
3. Built-in PostgreSQL extensions. Please, switch on pg_stat_statements. With this extension you can monitor queries count and their execution time. Probably, they are the most important service metrics.

Advices

---

1. Don’t think about scale always. Probably there is no need to do this right now! For this purpose monitoring is going to be your right hand. Set up a monitoring, make some triggers before limit. Start to think only when any trigger will pop up.
2. Make your operations reliable. When database is crashed, everybody’s crying. Mostly will you.
3. Wished a DB system switch? Write down at least 10 reasons why. You haven’t got 10 reasons? Stop to think about it!

• AWS
• database
• infrastructure
• pgbouncer
• pgpool
• PostgreSQL
• RDS