Performance Case Study

From PostgreSQL wiki
Jump to navigationJump to search

This page showcases real-world user examples that demonstrate the performance of PostgreSQL. The page is here to answer vague questions from users considering adoption of PostgreSQL, such as "What performance can PostgreSQL offer?", "What size of database can PostgreSQL handle?", and "Is PostgreSQL used in business critical systems (in a particular industry)?" This page was created by PostgreSQL Enterprise Consortium (PGECons), a Japanese organization founded to promote use of PostgreSQL in enterprises. Feel free to add entries.

Professional photographer arrangement

  • Field of application: Electronic commerce
  • Organization: Photocreate Co.,Ltd. (Japan)
  • Database size: about 2 TB with 400 tables
  • PostgreSQL version: 10.7 (on Amazon Aurora 2.3)
  • Notable characteristics:
    • Electronic commerce system for professional photographer arrangement.
    • About 600 thousand people use the system a month.
    • This project migrated the database on PostgreSQL 8.3 (on Amazon EC2) to PostgreSQL 10.7 (on Amazon Aurora 2.3).
    • The response time was improved from about 150ms to 30ms (5 times faster).
    • To reduce the response time, the system uses partitioned table, BRIN index and parallel query. Also, the read workload is distributed to hot standby servers.
  • Source: Kazuki Uehara, "Migration from PostgreSQL 8.3 to Amazon Aurora in case of Photocreate Co.,Ltd." (in Japanese), PGECons 2020 case seminar, 2020.

Billing system of telecommunications

  • Field of application: Telecommunication
  • Organization: NTT (Japan)
  • Database size: Several TB
  • PostgreSQL version: 9.2
  • Notable characteristics:
    • A customer billing system that handles millions of lines and over 100 services.
    • 24 x 365
    • Strict execution time and performance stabilization of complex and large-scale nighttime batch processing are required.
    • Approximately 5,000 batch jobs take several hours to read and update data in database.
    • Many large-scale analytic queries are running, some of which join dozens of tables.
    • Some queries run concurrently that join and aggregate dozens of GBs of tables.
    • Concurrently run OLTP and OLAP workloads.
    • Transition from a commercial database + Unix + physical environment to PostgreSQL + Linux + virtualized environment.
    • Load balancing using read replicas.
  • Source: Yuuki Asakura, Tatsuro Yamada, "A case study of use of PostgreSQL in the billing system", PGECons 2015 case seminar

Smart meter (IoT)

  • Field of application: Energy
  • Organization: The Chugoku Electric Power Company (Japan)
  • Database size: 11 TB (compressed)
  • PostgreSQL version: 9.4
  • Notable characteristics:
    • Insert 240 millions of records collected from smart meters per day.
  • Source: Ishii, "PostgreSQL underpins power liberalization behind the scenes", PGConf.ASIA 2016

Social network

  • Field of application: Social network
  • Organization: Tantan (China)
  • Database size: 1.3 TB / 11 billion rows (biggest nonsharded table), 22 TB / 350 billion rows (biggest sharded table)
  • PostgreSQL version: 9.3
  • Notable characteristics:
    • 8192 logical shards spread out on 64 physical servers.
    • 1.3 Mtps on the whole system.
    • 15k tuple writes / sec (Contact DB)
    • 270k tuple writes / sec (Aggregated over all databases)
  • Source: Blomqvist, V., "Using PostgreSQL in Tantan - From 0 to 350bn rows in 2 years", PGConf.ASIA 2016

Astronomic catalogues

  • Field of application: Science
  • Organization: University of Cambridge (United Kingdom)
  • Database size: 40 TB
  • PostgreSQL version: 9.4
  • Notable characteristics:
    • About one million queries per day, which is 100 tps
  • Source: Bartunov, O., “PostgreSQL Universal Database”, PGConf ASIA 2017

Mail archive product

Performance comparison of PostgreSQL and MongoDB

  • Field of application: Performance benchmarking
  • Organization: GlobalSign (Belgium)
  • Database size: 3.2 GB, ten millions of records of 32 bytes
  • PostgreSQL version: 9.6
  • Notable characteristics:
    • PostgreSQL is tens of percent to three times faster than MongoDB.
    • MongoDB 3.4.9 was used with its WiredTiger storage engine.
  • Source: Dominic Dwyer, Wei Shan Ang, "High Performance JSON - PostgreSQL vs. MongoDB", FOSDEM PGDay 2018