One important decision for delivering a platform independent solution with low memory footprint and minimal dependencies was the choice of the programming language.

Simple queries and testing replication lag are just a special case of running arbitrary SQL queries as a health check, so we just focused on this one and implemented the others as a form of syntactic sugar.

Low memory footprint - avoid вЂњstealingвЂќ memory from PostgreSQL ДЄ web service, exposing a simple HTTP endpoint, would work in any environment and easily be able to test TCP connectivity.

Support custom health checks in the form of SQL queries - extensible and future-proof.

Check replication lag (time elapsed since the last transaction was replayed ).

We need to distinguish between these two scenarios, and potentially take different actions

Check server availability by running a test query with a time limit - if a server is under load, it may be responding to TCP but not able to process a simple query (SELECT 1).

Check basic TCP connectivity, on an arbitrary port, with a configurable timeout.

Work equally well on both environments - RHEL 5/ HAproxy on Softlayer and Debian 7/ ELBs on AWS.

Not having found an open source tool that implements powerful enough health-checks for PostgreSQL, we decided to write our own. Performance and replication lag were (and still are!) very important factors to us - if a given replica is lagging behind by more than N seconds, we prefer not to use it until it recovers as it would otherwise lead to stale reads. One critical detail, however, was far from being a solved problem: health checks.ДЄ basic ping on port 5432 was not enough. We knew what we wanted the infrastructure to look like from a high-level perspective and had the tools available to implement almost all of it on both providers (Fig. Replication, high-availability, and load-balancing

to support SoftLayer and AWS environments during the transition period.

a way of performing health checks on each server, executed from the load balancer, so that failed nodes would be taken in and out of rotation automatically.

the load balancer itself could not be a single point of failure.

an arbitrary number of read-only replicas behind a load balancer.

In order to properly implement this we would need: This was good news as it meant we could scale out by sending SELECT statements to a cluster of read-only replicas and leaving the master alone to process DML commands.

ThumbtackвЂ™s SQL traffic was (and still is) quite read-intensive, with less than 3% of all queries being executed on the master node. The future, already being planned for, would be powered by EC2 instances on AWS, running Debian 7 behind SoftLayer running RHEL 5 and we were using HAproxy with Keepalived Elastic Load Balancers.ДЄs traffic grew, we knew we would need to scale out PostgreSQL further. Our infrastructure was comprised of a few dozen physical machines on for load balancing.

In 2014, Thumbtack was running PostgreSQL 9.1 on two servers: a basic master - slave setup leveraging PostgreSQLвЂ™s built-in streaming replication.

Replicas are also valuable to implement a disaster recovery plan as itвЂ™s possible to promote one to master in the event of a failure. There can be many replicas, so this strategy scales better with a higher read/write ratio. When the read/write ratio is high enough, there is fairly straightforward scaling strategy: setup secondary PostgreSQL nodes (replicas) that stream data from the primary node (master) and split SQL traffic by sending all writes ( INSERT, DELETE, UPDATE, UPSERT ) to the single master node and all reads ( SELECT ) to the replicas. No single approach solves all possible scaling challenges, but there are quite a few options for scaling PostgreSQL depending on the requirements. As traffic increases, so does the number of concurrent reads and writes, the read/write ratio may become too high, a fast and reliable recovery plan needs to exist, the list goes onвЂ¦ However, such a setup usually does not last forever. There is a single source of truth, one instance to handle all reads and writes, one target for all clients to connect to, and only a single configuration file to maintain. Running PostgreSQL on a single primary master node is simple and convenient.

Aws postgresql load file powrershell