Google Cloud Spanner Database

The first horizontally scalable, strongly consistent, relational database service

Cloud Spanner is the world’s first fully managed relational database service to offer both strong consistency and horizontal scalability for mission-critical online transaction processing (OLTP) applications. With Cloud Spanner you enjoy all the traditional benefits of a relational database; but unlike any other relational database service, Cloud Spanner scales horizontally to hundreds or thousands of servers to handle the biggest transactional workloads.

Customers across industries use Cloud Spanner for mission-critical use cases including:

  • Powering customer authentication and provisioning for multi-national businesses
  • Building consistent systems for transactions and inventory management in the financial services and retail industries
  • Supporting high-volume systems that require low latency and high throughput in the advertising and media industries

Cool Stuff in PostgreSQL 10: Auto-logging

We started off by creating a logging infrastructure, then arranging for a single table to use it.

Rather than repeat that work for each table, let’s use a relatively although not completely new feature: EVENT TRIGGER. The idea here is that we fire a trigger on CREATE TABLE and see to it that the table is logged. We’ll write the trigger first, even though in reality, we’d need to load the function it calls first.

  1. CREATE EVENT TRIGGER add_logger
  2. ON ddl_command_end
  3. WHEN tag IN ('create table')
  4. EXECUTE PROCEDURE add_logger();
  5. COMMENT ON EVENT TRIGGER add_logger IS 'Ensure that each table which is not a log gets logged';

The magic happens inside add_logger(), but it’s magic we’ve already seen. First, we’ll get the table’s name and schema using pg_event_trigger_ddl_commands(), filtering out tables which are already log tables. The test here is crude and string-based, but we could easily go to schema-based ones.


						

pg-libphonenumber

A (partially implemented!) PostgreSQL extension that provides access to Google’s libphonenumber

  • Parsing/formatting/validating phone numbers for all countries/regions of the world.
  • getNumberType – gets the type of the number based on the number itself; able to distinguish Fixed-line, Mobile, Toll-free, Premium Rate, Shared Cost, VoIP and Personal Numbers (whenever feasible).
  • isNumberMatch – gets a confidence level on whether two numbers could be the same.
  • getExampleNumber/getExampleNumberByType – provides valid example numbers for all countries/regions, with the option of specifying which type of example phone number is needed.
  • isPossibleNumber – quickly guessing whether a number is a possible phonenumber by using only the length information, much faster than a full validation.
  • isValidNumber – full validation of a phone number for a region using length and prefix information.
  • AsYouTypeFormatter – formats phone numbers on-the-fly when users enter each digit.
  • findNumbers – finds numbers in text input.
  • PhoneNumberOfflineGeocoder – provides geographical information related to a phone number.
  • PhoneNumberToCarrierMapper – provides carrier information related to a phone number.

Sharding a multi-tenant app with Postgres

Co-locating data within the same physical instance avoids sending data over the network during joins. This can result in much faster operations. With Citus there are a number of ways to move your data around so you can join and query it in a flexible manner, but for this class of multi-tenant SaaS apps it’s simple if you can ensure data ends up on the shard.