PGCon 2013 Keynote - Postgres at Disqus

Overview of how Disqus uses Postgres at scale, some of the pain points we've encountered, and where we see the future of Postgres going in our use case.

Transcript

1. Mike Clarke @mikeclarke Postgres at Disqus
2. what is disqus
3. what is disqus
4. the present
5. where we use Postgres ๏ All comment data ๏ Comments ๏ Threads ๏ Forums ๏ Imports ๏ All user data ๏ Users ๏ Profiles
6. where we DON’T we use it ๏ Stats and counters (Cassandra) ๏ Eventual consistency ๏ Availability ๏ Queue (RabbitMQ) ๏ Compatibility with existing libraries ๏ Availability
7. our main cluster ๏ 3+ TB of data ๏ Hardware ๏ 6 node cluster (1 master, 1 failover, 4 read-only slaves) ๏ 384G RAM ๏ 16xRAID10 800G SSDs ๏ Previously 32xRAID10 15K RPM drives ๏ Dual 2.9GHz OctoCore CPUs
8. our workload ๏ 50,000 transactions / second ๏ Even more queries / second ๏ millions of new posts + threads daily
9. the power of SSDs (before)
10. the power of SSDs (before)
11. the power of SSDs (after)
12. the power of SSDs (after)
13. pain points
14. logical replication ๏ Love + Hate for Slony ๏ Killer feature: flexible replication sets ๏ Still running a patched 2.0.3 release ๏ Hate the lock-in & using 3rd party tool ๏ Tremendous risk associated with upgrade ๏ Unbelievably excited for BDR
15. connecting to postgres ๏ Lots of possibilities ๏ Connect directly to Postgres ๏ local pgBouncer - Postgres ๏ pgBouncer - pgBouncer - Postgres ๏ pgBouncer - HAProxy - Postgres ๏ HAProxy - pgBouncer - Postgres ๏ Zen of Python ๏ “There should be one-- and preferably only one --obvious way to do it.”
16. the future
17. a parallel to nginx ๏ right tool for the job... ๏ ...isn’t always obvious ๏ nginx can be used for: ๏ streaming HTTP connections ๏ resizing images on the fly ๏ caching & transforming HTTP responses ๏ and more!
18. extensions
19. building your app on your database vs. building your app inside your database
20. real extensions ๏ already a bunch of awesome extensions today ๏ PostGIS (huge di erentiator for PG) ๏ pg_repack ๏ pl/v8 ๏ hstore ๏ ... and others, but we can do better
21. “magical pony” extensions ๏ PL/Proxy-style query routing ๏ move partitioning logic into postgres ๏ query progress indicator ๏ helps set biz analyst expectations ๏ pg_autoscale ๏ tools a la Heroku for everyone ๏ INDEXED BY support in SQL
22. tl;dr 1. SSDs are fantastic 2. connections & replication 3. extensions are a honking great idea -- let’s do more!
23. thanks! @mikeclarke