{"id":5012,"date":"2018-07-18T12:28:30","date_gmt":"2018-07-18T11:28:30","guid":{"rendered":"https:\/\/www.discoverpassenger.com\/?p=5012"},"modified":"2018-07-18T12:28:30","modified_gmt":"2018-07-18T11:28:30","slug":"all-the-databases","status":"publish","type":"post","link":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/","title":{"rendered":"All the databases"},"content":{"rendered":"
\n

Originally posted on our ‘Base’ Medium account.
In September 2019, Base became Passenger<\/a>.<\/p><\/blockquote>\n<\/div><\/div><\/div>\n\n

\n
\n<\/div>\n\n
\n

The database landscape was largely unchanging years ago. There was still innovation but it stayed tethered to SQL. SQL was king. DBAs wrote stored procedures and DevOps hadn\u2019t been born. At this time there was a 90% chance you had a single database architecture that was either Microsoft SQL, Oracle or MySQL.<\/p>\n<\/div>\n\n

\n

Times changed and people started talking about CAP instead of ACID. The idea of NoSQL came along and new database engines popped up almost as often as new JavaScript frameworks. Today, software teams have a vast choice of database systems to choose from.<\/p>\n<\/div>\n\n

\n

Passenger<\/a> is made up of many services (not quite microservices) and various database systems. When chatting about our tech stack to other PHP developers it takes a while to get through the list and explain the reasons behind them. We often forget about some of them too, as in many cases they\u2019re sitting there in the background, doing reads and writes all day long without complaining.<\/p>\n<\/div>\n\n

\n

If you\u2019re interested in the \u201cwhy\u201d behind some of the tech at Passenger then read on\u2026<\/p>\n<\/div>\n\n

\n
\n<\/div>\n\n
\n

MongoDB<\/h3>\n<\/div>\n\n
\n
\"\"\/<\/figure>\n<\/div>\n\n
\n

Almost all our transport data is stored in MongoDB. Mongo falls under the \u201cNoSQL\u201d class of databases and is document-orientated. Mongo lends itself well here as public transport journeys are discrete objects with lots of data. To fetch a timetable, we look for all the journeys in that timetable. This is much simpler than storing individual times as rows in a SQL database as it means things like journeys crossing midnight aren\u2019t a special case.<\/p>\n<\/div>\n\n

\n

A benefit working with fixed timetables is that there is a whole sub-domain of data that rarely changes. This means we can focus on having simple and performant read structures. We do this by performing the more intensive writes as a series of background import jobs. Any time we want a new view on the data that existing documents don\u2019t lend themselves to, we can add a new job to import data in a different way.<\/p>\n<\/div>\n\n

\n

MariaDB<\/h3>\n<\/div>\n\n
\n
\"\"\/<\/figure>\n<\/div>\n\n
\n

Ticket products and payments are stored in MariaDB. The relational nature of it allows for various different views and reports to be created and its atomicity is suitable for payment transactions.<\/p>\n<\/div>\n\n

\n

Elasticsearch<\/h3>\n<\/div>\n\n
\n
\"\"\/<\/figure>\n<\/div>\n\n
\n

Elasticsearch is a search engine. We, of course, use it for searching. This powers things like the auto-complete results in Passenger apps and websites. Google is very good at search too but Elastic allows us to fine tune what data goes in and what data is returned in search results, prioritising locations that can be reached via each transport network.<\/p>\n<\/div>\n\n

\n

Redis<\/h3>\n<\/div>\n\n
\n
\"\"\/<\/figure><\/div>\n<\/div>\n\n
\n

We use Redis for real time transport data, such as stop departures. Redis\u2019 built in timed expiry of keys is perfect for automatically cleaning out data. Being in-memory, Redis is very fast, which allows us to serve departure boards for many thousands of stops in just a few milliseconds.<\/p>\n<\/div>\n\n

\n

DynamoDB<\/h3>\n<\/div>\n\n
\n
\"\"\/<\/figure>\n<\/div>\n\n
\n

Amazon\u2019s DynamoDB is used as Passenger\u2019s configuration. Distributed services can access parts of the same config over the internet. We have a package used in each service that fetches the config for the service in a uniform way.<\/p>\n<\/div>\n\n

\n

Graphs<\/h3>\n<\/div>\n\n
\n
\"\"\/<\/figure><\/div>\n<\/div>\n\n
\n

Getting more esoteric, we also have an in-memory directed graph of journey data. This allows us to plan journeys across nodes a transport network, querying based on geospatial and temporal constraints.<\/p>\n<\/div>\n\n

\n

We\u2019ve built 2 of our own journey planners from scratch in the past but Passenger now uses OpenTripPlanner<\/a> behind the scenes, with our own data augmentation and processes going on too.<\/p>\n<\/div>\n\n

\n
\n<\/div>\n\n
\n

At first glance having so many different databases may look like overcomplexity. In reality, the storage engines have clear responsibilities and contexts in which they use the data. The isolation means we haven\u2019t ended up with one monolithic SQL server that gets out of control.<\/p>\n<\/div>\n\n

\n

Having 6* different databases can be a bit of a learning curve for new developers. Fortunately, PHP packages like Doctrine<\/a> abstract away many of the differences. Ansible<\/a> and Terraform<\/a> (for provisioning servers and development environments) helps here too, as new instances can be created without having to configure anything.<\/p>\n<\/div>\n\n

\n

There\u2019s various bits we\u2019re expecting to change in the future. There\u2019s a good chance we\u2019ll outgrow Redis for some of the real-time things we want to do and have to move that elsewhere. We\u2019ve only scratched the surface of Elastic, so want to exploit more of its capabilities to provide a better UX. More of our storage is heading towards AWS too, to make scaling easier and so we can focus on the code.<\/p>\n<\/div>\n\n

\n

*At the current count, not including storage used by websites and apps\u200a\u2014\u200athat will have to be a separate post.<\/em><\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"

The database landscape was largely unchanging years ago. There was still innovation but it stayed tethered to SQL. SQL was king. DBAs wrote stored procedures and DevOps hadn\u2019t been born. At this time there was a 90% chance you had a single database architecture that was either Microsoft SQL, Oracle or MySQL. Times changed and […]<\/p>\n","protected":false},"author":7,"featured_media":12760,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[21],"tags":[],"class_list":["post-5012","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-making-passenger","making-passenger"],"acf":[],"yoast_head":"\nAll the databases - Masabi<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/\" \/>\n<meta property=\"og:locale\" content=\"it_IT\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"All the databases - Masabi\" \/>\n<meta property=\"og:description\" content=\"The database landscape was largely unchanging years ago. There was still innovation but it stayed tethered to SQL. SQL was king. DBAs wrote stored procedures and DevOps hadn\u2019t been born. At this time there was a 90% chance you had a single database architecture that was either Microsoft SQL, Oracle or MySQL. Times changed and […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/\" \/>\n<meta property=\"og:site_name\" content=\"Masabi\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-18T11:28:30+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.masabi.com\/wp-content\/uploads\/2019\/09\/1_QIsr08hkvXbF2EeBls3XdA.png\" \/>\n\t<meta property=\"og:image:width\" content=\"740\" \/>\n\t<meta property=\"og:image:height\" content=\"304\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"TomQuay\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Scritto da\" \/>\n\t<meta name=\"twitter:data1\" content=\"TomQuay\" \/>\n\t<meta name=\"twitter:label2\" content=\"Tempo di lettura stimato\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minuti\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/news\\\/all-the-databases\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/news\\\/all-the-databases\\\/\"},\"author\":{\"name\":\"TomQuay\",\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/#\\\/schema\\\/person\\\/7f9321d16421f5dd31179aec75bd022d\"},\"headline\":\"All the databases\",\"datePublished\":\"2018-07-18T11:28:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/news\\\/all-the-databases\\\/\"},\"wordCount\":776,\"image\":{\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/news\\\/all-the-databases\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.masabi.com\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/1_QIsr08hkvXbF2EeBls3XdA.png\",\"articleSection\":[\"Making Passenger\"],\"inLanguage\":\"it-IT\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/news\\\/all-the-databases\\\/\",\"url\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/news\\\/all-the-databases\\\/\",\"name\":\"All the databases - Masabi\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/news\\\/all-the-databases\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/news\\\/all-the-databases\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.masabi.com\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/1_QIsr08hkvXbF2EeBls3XdA.png\",\"datePublished\":\"2018-07-18T11:28:30+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/#\\\/schema\\\/person\\\/7f9321d16421f5dd31179aec75bd022d\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/news\\\/all-the-databases\\\/#breadcrumb\"},\"inLanguage\":\"it-IT\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.masabi.com\\\/it\\\/news\\\/all-the-databases\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"it-IT\",\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/news\\\/all-the-databases\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.masabi.com\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/1_QIsr08hkvXbF2EeBls3XdA.png\",\"contentUrl\":\"https:\\\/\\\/www.masabi.com\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/1_QIsr08hkvXbF2EeBls3XdA.png\",\"width\":740,\"height\":304},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/news\\\/all-the-databases\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"All the databases\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/#website\",\"url\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/\",\"name\":\"Masabi\",\"description\":\"Making shared transport the first choice\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"it-IT\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.masabi.com\\\/it\\\/#\\\/schema\\\/person\\\/7f9321d16421f5dd31179aec75bd022d\",\"name\":\"TomQuay\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"it-IT\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/670e69485b65f2c5a6ee3fb9d0e3ed3163fb4107b7ab9ade42394576b7d42571?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/670e69485b65f2c5a6ee3fb9d0e3ed3163fb4107b7ab9ade42394576b7d42571?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/670e69485b65f2c5a6ee3fb9d0e3ed3163fb4107b7ab9ade42394576b7d42571?s=96&d=mm&r=g\",\"caption\":\"TomQuay\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"All the databases - Masabi","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/","og_locale":"it_IT","og_type":"article","og_title":"All the databases - Masabi","og_description":"The database landscape was largely unchanging years ago. There was still innovation but it stayed tethered to SQL. SQL was king. DBAs wrote stored procedures and DevOps hadn\u2019t been born. At this time there was a 90% chance you had a single database architecture that was either Microsoft SQL, Oracle or MySQL. Times changed and […]","og_url":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/","og_site_name":"Masabi","article_published_time":"2018-07-18T11:28:30+00:00","og_image":[{"width":740,"height":304,"url":"https:\/\/www.masabi.com\/wp-content\/uploads\/2019\/09\/1_QIsr08hkvXbF2EeBls3XdA.png","type":"image\/png"}],"author":"TomQuay","twitter_card":"summary_large_image","twitter_misc":{"Scritto da":"TomQuay","Tempo di lettura stimato":"4 minuti"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/#article","isPartOf":{"@id":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/"},"author":{"name":"TomQuay","@id":"https:\/\/www.masabi.com\/it\/#\/schema\/person\/7f9321d16421f5dd31179aec75bd022d"},"headline":"All the databases","datePublished":"2018-07-18T11:28:30+00:00","mainEntityOfPage":{"@id":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/"},"wordCount":776,"image":{"@id":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/#primaryimage"},"thumbnailUrl":"https:\/\/www.masabi.com\/wp-content\/uploads\/2019\/09\/1_QIsr08hkvXbF2EeBls3XdA.png","articleSection":["Making Passenger"],"inLanguage":"it-IT"},{"@type":"WebPage","@id":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/","url":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/","name":"All the databases - Masabi","isPartOf":{"@id":"https:\/\/www.masabi.com\/it\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/#primaryimage"},"image":{"@id":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/#primaryimage"},"thumbnailUrl":"https:\/\/www.masabi.com\/wp-content\/uploads\/2019\/09\/1_QIsr08hkvXbF2EeBls3XdA.png","datePublished":"2018-07-18T11:28:30+00:00","author":{"@id":"https:\/\/www.masabi.com\/it\/#\/schema\/person\/7f9321d16421f5dd31179aec75bd022d"},"breadcrumb":{"@id":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/#breadcrumb"},"inLanguage":"it-IT","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.masabi.com\/it\/news\/all-the-databases\/"]}]},{"@type":"ImageObject","inLanguage":"it-IT","@id":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/#primaryimage","url":"https:\/\/www.masabi.com\/wp-content\/uploads\/2019\/09\/1_QIsr08hkvXbF2EeBls3XdA.png","contentUrl":"https:\/\/www.masabi.com\/wp-content\/uploads\/2019\/09\/1_QIsr08hkvXbF2EeBls3XdA.png","width":740,"height":304},{"@type":"BreadcrumbList","@id":"https:\/\/www.masabi.com\/it\/news\/all-the-databases\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.masabi.com\/it\/"},{"@type":"ListItem","position":2,"name":"All the databases"}]},{"@type":"WebSite","@id":"https:\/\/www.masabi.com\/it\/#website","url":"https:\/\/www.masabi.com\/it\/","name":"Masabi","description":"Making shared transport the first choice","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.masabi.com\/it\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"it-IT"},{"@type":"Person","@id":"https:\/\/www.masabi.com\/it\/#\/schema\/person\/7f9321d16421f5dd31179aec75bd022d","name":"TomQuay","image":{"@type":"ImageObject","inLanguage":"it-IT","@id":"https:\/\/secure.gravatar.com\/avatar\/670e69485b65f2c5a6ee3fb9d0e3ed3163fb4107b7ab9ade42394576b7d42571?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/670e69485b65f2c5a6ee3fb9d0e3ed3163fb4107b7ab9ade42394576b7d42571?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/670e69485b65f2c5a6ee3fb9d0e3ed3163fb4107b7ab9ade42394576b7d42571?s=96&d=mm&r=g","caption":"TomQuay"}}]}},"_links":{"self":[{"href":"https:\/\/www.masabi.com\/it\/wp-json\/wp\/v2\/posts\/5012","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.masabi.com\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.masabi.com\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.masabi.com\/it\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.masabi.com\/it\/wp-json\/wp\/v2\/comments?post=5012"}],"version-history":[{"count":0,"href":"https:\/\/www.masabi.com\/it\/wp-json\/wp\/v2\/posts\/5012\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.masabi.com\/it\/wp-json\/wp\/v2\/media\/12760"}],"wp:attachment":[{"href":"https:\/\/www.masabi.com\/it\/wp-json\/wp\/v2\/media?parent=5012"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.masabi.com\/it\/wp-json\/wp\/v2\/categories?post=5012"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.masabi.com\/it\/wp-json\/wp\/v2\/tags?post=5012"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}