
Scaling the GitLab database - koolba
https://about.gitlab.com/2017/10/02/scaling-the-gitlab-database/
======
koolba
> Sticky connections are supported by storing a pointer to the current
> PostgreSQL WAL position the moment a write is performed. This pointer is
> then stored in Redis for a short duration at the end of a request. Each user
> is given their own key so that the actions of one user won't lead to all
> other users being affected. In the next request we get the pointer and
> compare this with all the secondaries. If all secondaries have a WAL pointer
> that exceeds our pointer we know they are in sync and we can safely use a
> secondary for our read-only queries. If one or more secondaries are not yet
> in sync we will continue using the primary until they are in sync. If no
> write is performed for 30 seconds and all the secondaries are still not in
> sync we'll revert to using the secondaries in order to prevent somebody from
> ending up running queries on the primary forever.

Interesting approach. Kind of halfway between async replication and a per
client synchronous commit.

Why wait for all of the slave databases to be in sync? If at least one is in
sync, can you get all database interactions in the application to the valid
subset?

Snippet of code from the article:

    
    
         query = "SELECT NOT pg_is_in_recovery() OR " \
        "pg_xlog_location_diff(pg_last_xlog_replay_location(), #{string}) >= 0 AS result"
    

> Here WAL-POINTER is the WAL pointer as returned by the PostgreSQL function
> pg_current_xlog_insert_location(), which is executed on the primary. In the
> above code snippet the pointer is passed as an argument, which is then
> quoted/escaped and passed to the query.

Why escape the input and use string interpolation instead of a parameter?

~~~
YorickPeterse
Sorry for the late reply!

    
    
        > Why wait for all of the slave databases to be in sync? If at least one is in sync, 
        > can you get all database interactions in the application to the valid subset?
    

If queries fail on the replica we'll cycle through the available replicas
until we have one that works. If we didn't wait for all to be in sync it could
in theory be possible we'd retry our query on a host that does not yet have
the data, though this would be very unlikely. So basically we're being a bit
paranoid here.

    
    
        > Why escape the input and use string interpolation instead of a parameter?
    

Rails doesn't really provide an easy way of doing this unfortunately. That is,
to run raw queries you'll use something like this:

    
    
        ActiveRecord::Base.connection.execute 'your query here'
    

This particular API doesn't support passing any parameters, so you have to
resort to manually escaping data and using string interpolation.

