Modernizing from PostgreSQL to Serverless with Fauna Part 1

Introduction

Comparing Postgres and Fauna

Notable Differences

1. Relational-model vs multi-model: Relational-model vs multi-model: Postgres is often described as an object-relational database with additional data types that support documents like JSON and JSONB, but it is primarily built and used as a traditional relational database. Fauna is often described as a document-relational database with JSON documents at its core but also includes a wide variety of relational features. In contrast to other common document databases, Fauna offers relational options and flexible indexing to maintain consistency. Further, it adds temporality by using MVCC for maintaining versions of documents as well as has an advanced ABAC system for access control.
2. Schemas: Both Postgres and Fauna have a concept of logical databases that include schema records describing the collections, indexes, and security properties of the included records. However, Postgres enforces schema through an inflexible tabular model while Fauna gives users a choice. Fauna allows for a simple but sophisticated set of features that offers enforcement (for things like field names, required fields, data types, business logic, computed fields, etc) as well as the hybrid ability to allow additional dynamic components to documents at the same time. Further, Fauna offers migration blocks to allow for seamless schema changes over time.
3. Database connections: Postgres expects persistent connections, like many similar relational databases. Most implementations must configure connection pools and have to be concerned with connection overhead and limits. Fauna uses stateless, secure HTTP connections and requires no connection management.
4. Delivery Model: Postgres is built to self-host or to be hosted or managed by cloud providers, requiring managing server instances, read replicas, partitioning, and the like. There are vendors that claim that they provide serverless Postgres, but in many cases, there are still provisioning decisions to consider. On the other hand, Fauna was built from the ground-up with a fully serverless delivery model. Consumed as an API with a single global endpoint, there are no clusters, partitions, and replication to configure. Furthermore, the single global endpoint can intelligently route a request to the closest multi-active copy of the data, removing the need to design for HA.
5. Distribution: When it comes to distribution in a traditional database, asynchronous replication is the most popular form of distribution which introduces eventual consistency and potential data loss. If consistency is required, synchronous replication is provided as an option, but it typically comes at a high price in terms of performance, especially if distribution across regions is desired. Although traditional databases like Postgres were not built with distribution in mind, many recent improvements and additions provide multiple options that supplement the non-distributed nature of its core engine. In contrast, Fauna is built from the ground up as a scalable, multi-region distributed database. It is inspired by the Calvin algorithm that speeds up data consensus by relying on deterministic calculations. Further, this distribution of data is transparent to the application developers and can accommodate data locality restrictions.
6. Multi-tenancy: Although Postgres provides various options to create a SaaS application where you can have multiple tenants (whether across servers or within a single database), Fauna offers robust, native multi-tenancy configurations with a unique concept of child databases with arbitrarily deep nesting. Child databases are completely isolated from each other, with the ability to create separate permissions for child databases, and the child databases cannot determine whether there’s a parent database.
7. Query Languages: The primary language used for Postgres is SQL or, more accurately, a SQL dialect. Although SQL has been around for many decades and is well known, documented, and standardized to the extent that it can be, every SQL database exhibits significant variations in the language. For example, to support JSON manipulation and event streaming, you will encounter significant differences between the SQL commands used in Postgres and other SQL databases like MySQL. Application developers will likely use an object-relational mapper (ORM) that adds some application complexity without removing the need to understand the Postgres-specific SQL conventions. GraphQL can be used with Postgres by adding extensions, of which there are many options to consider. Fauna provides its own native query language, FQL, which is designed to align with modern coding paradigms. Because of the nature of FQL and other design considerations, Fauna requires no ORM and is not susceptible to injection. The choice for a custom language is rooted in Fauna's scalable and distributed design. It’s designed to prevent long-running transactions, maximize performance in distributed scenarios, be highly composable to reduce round-trips and overall latency, and have a transparent and predictable query plan. Many of these characteristics will become evident in this article and its subsequent parts as we cover FQL extensively in the coming sections.

Postgres and Fauna: Terminology Mapping

From Postgres to Fauna: Building the Basics

Creating a new database

Sign up

Create the database

One-to-many relations

Creating the language collection

{
  id: "406214110775083073",
  coll: <collection>,
  ts: Time("2022-01-14T17:00:14.430Z"),
  fields...
}

 Collection.create({name: "language"})

Creating language documents

language.create({name: 'english'})
language.create({name: 'french'})

• id: a unique reference to the document. The presence of references doesn’t mean that we can no longer use other IDs in Fauna. We’ll see the difference between native Fauna references and IDs shortly when we start querying.
• coll: is the name of the collection that this document belongs to.
• ts: the timestamp of the document, which is automatically updated when the document is adapted. Fauna’s timestamp is part of temporality features that provide time travel and support the streaming capabilities.

Creating the film collection and documents

Collection.create({name: "film"})

film.create({
        title: "Academy Dinosaur",
        language: {
            spoken: language("288878259769180673"),
            subtitles: language("288878259769180673")
        }
      }
)

film.create({
        title: "Back to the Future",
        language: {
            spoken: language("288878259769180673"),
            subtitles: language("288878259769180673")
        }
      }
)

Querying basics

SELECT title FROM film where id = '406214477539704900'

film.byId("406214477539704900") {
  title
}

Pagination

film.all().paginate(1)

{
  data: [
    {
      id: "406214477539704900",
      coll: film,
      ts: Time("2024-08-14T17:06:04.210Z"),
      title: "Academy Dinosaur",
      language: {
        spoken: language("406214110775083073"),
        subtitles: language("406214110775083073")
      }
    }
  ],
  after: "hdWCxmRmaWxtgcqEY2FsbID09oCBzUoFoyw/jlAAQQQGwYIaZr0HPBogyFWAAQ=="
}

Set.paginate("hdWCxmR...")

Querying one-to-many relationships

Querying normalized data with native references

{
  id: "406214477539704900",
  coll: film,
  ts: Time("2024-08-14T17:06:04.210Z"),
  title: "Academy Dinosaur",
  language: {
    spoken: language("406214110775083073"),
    subtitles: language("406214110775083073")
  }
}

SELECT * FROM film 
JOIN "language" as spol ON spol.language_id = film.spoken_language_id 
JOIN "language" as subl ON subl.language_id = film.subtitles_language_id 
LIMIT 16

film.byId("406214477539704900") {
  title,
  language {
    spoken
  }
}

{
  title: "Academy Dinosaur"
  ,language: {
    spoken: {
      id: "406214110775083073",
      coll: language,
      ts: Time("2024-08-14T17:00:14.430Z"),
      name: "english"
    }
  }
}

film.byId("406214477539704900") {
  title
  ,language {
    spoken {
      name
    }
  }
}

{
  title: "Academy Dinosaur",
  language: {
    spoken: {
      name: "english"
    }
  }
}

film.create({
        title: "Batman",
        language: [  // note the array of references
            language("406214110775083073"),
            language("406251657205121089")
        ]
      }
)

film.byId("406251968497975364") {
  language
}

{
  language: [
    {
      id: "406214110775083073",
      coll: language,
      ts: Time("2024-08-14T17:00:14.430Z"),
      name: "english"
    },
    {
      id: "406251657205121089",
      coll: language,
      ts: Time("2024-08-15T02:57:01.500Z"),
      name: "french"
    }
  ]
}

Additional common query functionality

Modeling relationships

film.create({
        title: "Academy Dinosaur",
        language: {
            spoken: { name: "English" },
            subtitles: { name: "English" }
        }
      }
)

Common optimizations

1. Normalizing versus embedding. In order to optimize for read execution, when applications scale it can be better to store all relevant data inside 1 document instead of spreading it out over multiple documents. An example would be embedding a shipping address for an order. Since this is unlikely to change, is a small amount of data and is likely to be included in a query of the order, it may be optimal to store it with the order and avoid the additional read IO in a query.
2. Updates versus Inserts. As systems scale, it is generally beneficial to avoid changing any one item too often. Inserts can be done with more concurrency than updates, on a single logical value. Thus it is an optimization to change the data model and access patterns from updating a single document to inserting new documents with the new value, along with a timestamp. This, plus an index on the timestamp, allows for both the latest state to be gathered quickly as well as a version history to be included. With Fauna’s TTL (time to live) feature for documents it is simple to auto-clean the data as well.
3. Incrementing integers. It is common for every system to need unique IDs and for relational systems to use incrementing integers for that purpose. In distributed systems it is far easier to generate unique IDs when they are random. This avoids locking contention. So for this purpose it is best practice to either let each document’s ID become that “rows” ID or to use the newId() function to generate a globally unique ID. There is also a sequence function available for generating a set of sequential numbers.
4. Covered indexes. These are the same between Postgres and Fauna, but it is a common optimization to make sure that most all reads are either needing all/most of the document or have their few return fields covered by the index. This removes the need for an extra IO if the index can fully satisfy the query. Fauna has no restrictions on the number of indexes per collection. It is a best practice to ensure that the indexes planned for the Fauna model cover as many of the queries as possible.
5. Sorted indexes. In applications meant for scale, design work can be done to avoid having to process things at run time (which could either be over a large number of records or consume a lot of server compute). An example of that is to use a sorted index (an option in Fauna) to maintain an order to documents. This enables fast selection for a TOP N type query or quickly returns the most recent entry when the sorted field is a timestamp.
6. Computed fields. Fauna allows for sophisticated logic to be added as computed fields. This can allow for some data to be logical instead of physical as well as these fields can be indexed. This can be helpful in enforcing business rules, limiting the size of data, simplifying indexes and models. An example would be a “state” or “status” field which can then be logically computed and indexed, allowing for processes looking for certain values to gather them easily and without having to scan a lot of data looking for qualifying items.

Summary