Instagram five tips for improving PostgreSQL performance _ database Other

With the growing scale of Instagram, Postgres continues to act as a solid foundation for Instagram and stores most of its user data. Less than a year ago, we said on our blog that Instagram "stores a lot of data", adding 90 more data per second, and now this data has grown to 10,000 of its peak. And our underlying storage technology remains the same.

In the past 2.5, we have had some experience and tools about Postgres expansion that we want to share. I wish I had these experiences and tools when I started Instagram. Some of these are unique to postgres and others can be used in other databases. If you want to understand how we are horizontally partitioned, we can read this article.

1. Local Index

Local indexes are very effective in this case, if we often need to filter the data according to a fixed feature, and this feature only exists in a small number of rows.

For example, when Instagram search for tags, we need to find out the labels with many photos. We usually use the technology such as Elasticsearch for advanced search, but the query ability of the database is completely enough. First Look, by the label query, and by the number of photos sorted, Postgres is how to do:

EXPLAIN ANALYZE SELECT id from tags WHERE name LIKE 'snow%' ORDER BY media_count DESC LIMIT 10;   
QUERY PLAN 
---------                                 
 Limit (cost=1780.73..1780.75 rows=10 width=32) (actual time=215.211..215.228 rows=10 loops=1)
  -> Sort (cost=1780.73..1819.36 rows=15455 width=32) (actual time=215.209..215.215 rows=10 loops=1)
     Sort Key: media_count
     Sort Method: top-N heapsort Memory: 25kB
     -> Index Scan using tags_search on tags_tag (cost=0.00..1446.75 rows=15455 width=32) (actual time=0.020..162.708 rows=64572 loops=1)
        Index Cond: (((name)::text ~>=~ 'snow'::text) AND ((name)::text ~<~ 'snox'::text))
        Filter: ((name)::text ~~ 'snow%'::text)
 Total runtime: 215.275 ms
(8 rows)

The

did not see that, in order to get the result, Postgres had to sort 15000 rows of data. Because the distribution of labels satisfies the long tail pattern, the translator notes: According to Baidu Encyclopedia, "our commonly used Chinese characters are actually not many, but because of the frequency of high, so these few Chinese characters occupy the vast red zone, the vast majority of Chinese characters are rare, they belong to the long yellow tail." "), we can query for more than 100 photos of the label, first built local index:
 
CREATE Index concurrently on tags (name text_pattern_ops) WHERE Media_ Count >=
then query to see the new query plan:
 

EXPLAIN ANALYZE SELECT * from tags WHERE name like ' snow% ' and Media_count >=-order by Media_count DESC LIMIT 1/>query plan
 Limit (cost=224.73..224.75 rows=10 width=32) actual (time=3.088..3.105 rows=10 Loops=1)
  Sort (cost=224.73..225.15 rows=169 width=32) (actual time=3.086..3.090 rows=10 Loops=1)
     sort Key:media_count
     Sort method:top-n heapsort memory:25kb
     -> Index Scan using Tags_tag_name_idx on Tags_tag (cost=0.00..221.07 rows= 169 width=32) (actual time=0.021..2.360 rows=924 Loops=1)
        Index Cond: (((name):: Text ~>=~ ' Snow ':: Text) and ((name ):: Text ~<~ ' Snox ':: Text)
        Filter: ((name):: Text ~ ~ ' snow% ':: Text) Total
 runtime:3.137 ms
(8 rows)

As you can see, postgres only needs to access 169 lines, so it's much faster. The Postgres Query Scheduler is also effective at evaluating constraints. If you later want to query for more than 500 photos of the label, because this result set is a subset of the above set, so still use this local index.

2. Function Index

On some tables, we need to index some very long strings, such as the 64-character Base64 notation. If you build the index directly, it will cause a lot of data duplication, in which case, you can use the Postgres function index:

CREATE INDEX concurrently on tokens (substr (token), 0, 8)

Although this will result in many rows matching the same prefix, we can filter on the basis of matching, fast. And the index is very small, only about One-tenth of the original.

3. Use pg_reorg to make the data more compact

As time goes by, postgres tables become more fragmented (caused by the MVCC concurrency model, and so on). Also, the order in which data rows are inserted is often not the order we want to return. For example, if we frequently query photos by user, it is better to put them together on a disk so that we can reduce the time of disk seek.

We use pg_reorg to solve this problem, it takes three steps to "squeeze" a table:

1. To obtain an exclusive lock on a table
2. Create a temporary table that records changes, add a trigger to the original table, and copy the changes to the original table to the temporary table
3. Use Create TABLE ... SELECT from ... Order by build table, the new table has all the data of the original table, and is sorted by index
4. Synchronize changes that occur after the CREATE table execution point from a temporary table
5. Business Switch to new table

Every step has a lot of detail, but it's mostly like this. We first reviewed the tool, ran a number of tests, and then used it in a production environment. Now, we have run dozens of pg_reorg in the environment of hundreds of machines without any problems.

4. Archiving and backup of Wal (pre-write log) with WAL-E

We use WAL-E to file the Wal log, which is a tool written by Heroku, and we have also contributed a bit of code to it. WAL-E greatly simplifies the process of data backup and replication library creation.

WAL-E uses Progres's archive_command to archive every WAL file produced by PG to Amazon's S3. With these Wal files and database base backups, we can restore the database to the state at any point in time after the baseline backup. With this approach, we can also quickly create a read-only replication library or a failed standby library.

We wrote a simple encapsulation script for WAL-E to monitor duplicate failures at the time of filing, see GitHub.

5. autocommit mode and asynchronous mode in PSYCOPG2

We've also started using some of the advanced features in PSYCOPG2 (Psycopg2 is Postgres's Python drive).

One is autocommit mode. In this mode, PSYCOPG2 does not emit begin/commit, each query runs in its own single statement transaction. This is especially useful for read-only queries that do not require transactions. The opening is simple:

Connection.autocommit = True

When automatic submission is turned on, the conversation between our application server and the database is greatly reduced and the CPU usage of the database server is greatly reduced. Moreover, we use pgbouncer as connection pool, turn on automatic submission, the connection is returned faster.

The details of the interaction with Django can be seen here.

PSYCOPG2 also has a very useful feature that can provide collaborative program (COROUTINE) support by registering a waiting callback (wait callback) function. It can support cross join queries, which are useful for queries that hit multiple nodes, and when there is data, the socket is awakened (we use the Python Select module to process the wake). It can also be with Eventlet and gevent and other multi-line threading good collaboration, reference implementation visible Psycogreen.

In general, we are very satisfied with the high performance and reliability of Postgres. Want to work on one of the largest postgres clusters in the world? Do you want to work with a group of infrastructure experts? Please contact infrajobs@instagram.com.