Who am I?¶

• Independent Postgres hacker, first used Postgres in 2004
• User -- not contributor

Basic use-case¶

• Large set of data (prices, user_prefs, financial transactions) where volume of inserts is very great

  • Perhaps table is partitioned or is quasi-federated out of heterogenous data stores.

Some current projects using immutable data stores:¶

• Apache Kafka (http://kafka.apache.org/)

  Distributed messenging server

Is it possible to delete a topic? In the current version, 0.8.0, no. (You could clear the entire Kafka and zookeeper states to delete all topics and data.) But upcoming releases are expected to include a delete topic tool.

• Apache Zookeeper (http://zookeeper.apache.org/)

  ZooKeeper is a centralized service for maintaining configuration information, naming, providing distributed synchronization, and providing group services. All of these kinds of services are used in some form or another by distributed applications.

• Datomic (Rich Hickey) -- (http://www.datomic.com)

  • Founded by creator of Clojure.
  • Distributed database with immutable data store.
  • Database with no side effects(!)

Prior Art¶

Postgres pre-6.2 Time Travel¶

http://www.postgresql.org/docs/6.3/static/c0503.htm

But¶

SPI timetravel extension¶

http://www.postgresql.org/docs/current/static/contrib-spi.html

• Table-based
• Uses trigger function before insert, update or delete.
• Assumes two columns of abstime(!) to track freshness of data

But¶

Current Approaches¶

• Oracle has flashback queries:

  SELECT * FROM FOO AS OF TIMESTAMP TO_TIMESTAMP('2014-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

• Depends on setting of UNDO_RETENTION to determine time period available for flashback -- typically <= 1 day

Can we do this without extensions?¶

• Block updates and deletes from table

• Timestamp every record insert with clock_timestamp

• Updates will be have later date_entered for same fact; deletes just not allowed

• Use DISTINCT ON or WINDOW functions to get record as of timestamp

• Have to use custom sql to access current state

Importance of insert-only¶

• Each change to price table is guaranteed to be idempotent
• Clock_timestamp guarantees ordering (even if overlapping connections in long transactions)

• How do we get meaningful data out of append-only table if multiple rows for given fact?

Materialized views¶

• In certain cases, rewriting data using range types and btree-gist indexes may improve discovery queries

Storage needs¶

From recent blog post by Robert Hodges on immutable data (http://scale-out-blog.blogspot.com/2014/02/why-arent-all-data-immutable.html) -- storage needs are not terrible (with certain assumptions, of course)

Prioritize Fresher Data¶

Prioritize fresher data¶

• Most immutable-only data needs to privilege most recent data
• Partition data using time-based partition
• Needs management
  • e.g., using extension pg_partman

Additional improvements¶

• Using tablespaces (SSD for newer data, Hard Drives for older data)
• Heterogenous database engines (NoSQL for very fresh data)

Mixing Postgresql with NoSQL/Hadoop¶

• Foreign Data Wrappers available for Redis, Memcached, MongoDB, Hadoop

  • Mismatch between NoSQL key/value storage and relational database schemas means data must be transformed before the two datasets can be merged
  • All of these FDWs (AFAIK) are currently read-only except Hadoop FDW, so applications will have to manage moving of data from one store to another

• Any time there is more than one master database, conflicts can occur

  • Usually, conflicts here can be resolved by sorting all entries in time order, assuming that roles of data sources are well-understood (e.g., NoSQL data store is responsible for all of today's data; Postgresql for all data < today)

• Many points of possible failure in any distributed system

Conflict resolution with multi-master writes¶

• CAP Theorem (Consistency, Availability, Partition Tolerance) = today's Fast, Good and Cheap

  • http://www.julianbrowne.com/article/viewer/brewers-cap-theorem
  • Since NoSQL sacrifices Consistency in exchange for Partition Tolerance (can lose a partition without stopping), substitutes BASE (Basically Available, Soft state, Eventual consistency) for ACID guarantees

Very difficult problem¶

• https://github.com/aphyr/jepsen is a project that simulates network failures in a distributed cluster using LXC and iptables.

  In every one of the distributed databases, it's possible to lose data unpredictably with network failure.

  From http://aphyr.com/posts/286-call-me-maybe-final-thoughts:

In each case, the system did something… odd. Maybe we hadn't fully thought through the consequences of the system, even if they were documented. Maybe the marketing or documentation were misleading, or flat-out lies. We saw design flaws, like the Redis Sentinel protocol. Some involved bugs, like MongoDB's WriteConcern.MAJORITY treating network errors as successful acknowledgements. Other times we uncovered operational caveats, like Riak's high latencies before setting up fallback vnodes. In each case, the unexpected behavior led to surprising new information about the challenge of building correct distributed systems.

Postgres-only cluster¶

• Possibly simpler solution for distributed immutable table

• Possibility of having foreign tables be child tables in 9.4 (9.5?)

Sample foreign inheritance support syntax -- still being refined:¶

Adapted from recent post by Kyotaro Horiguchi on hackers email list

http://postgresql.1045698.n5.nabble.com/inherit-support-for-foreign-tables-td5778285i60.html

• on child database

    create table pu1 (a int not null, b int not null, c int, d text); 
    create unique index i_pu1_ab on pu1 (a, b); 
    create unique index i_pu1_c  on pu1 (c); 
    create table cu11 (like pu1 including all) inherits (pu1); 
    create table cu12 (like pu1 including all) inherits (pu1); 
    insert into cu11 (select a / 5, 4 - (a % 5), a, 'cu11' from generate_series(000000, 099999) a); 
    insert into cu12 (select a / 5, 4 - (a % 5), a, 'cu12' from generate_series(100000, 199999) a); 

• on parent database

    create extension postgres_fdw; 
    create server pg2 foreign data wrapper postgres_fdw options (host '/tmp', port '5433', dbname 'postgres'); 
    create user mapping for current_user server pg2 options (user 'horiguti');   
    create foreign table _cu11 (a int, b int, c int, d text) server pg2 
       options (table_name 'cu11', use_remote_estimate 'true'); 
    create foreign table _cu12 (a int, b int, c int, d text) server pg2 
       options (table_name 'cu12', use_remote_estimate 'true'); 
    create table rpu1 (a int, b int, c int, d text); 
    alter foreign table _cu11 inherit rpu1; 
    alter foreign table _cu12 inherit rpu1; 
    analyze rpu1; 

Questions?¶

This talk is available at http://nbviewer.ipython.org/gist/nyamada/9946705