nierman

  begin;
  delete from product where id = 123;
  --verify things are correct with counts, by issuing "selects", etc.

You are right: durability and surviving extended stays in "cold storage" was a big factor.

definitely monitor your replication lag--or at least disk usage on the master--with this approach (in case wal starts piling up there).

pg_upgade has a --link option which uses hard links in the new cluster to reference files from the old cluster. This can be a very fast way to do upgrades even for large databases (most of the data between major versions will look the same; perhaps only some mucking with system catalogs is required in the new cluster). Furthermore, you can use rsync with --hard-links to very quickly upgrade your standby instances (creating hard links on the remote server rather than transferring the full data).

that is all referenced in the current documentation: https://www.postgresql.org/docs/current/static/pgupgrade.htm...

I wouldn't consider it the same as "eating a donut" (the cereal has 4g sugar and 5g fiber per 3/4 cup serving).

note, the following jdbc driver can do async listen/notify: http://impossibl.github.io/pgjdbc-ng/

http://www.postgresql.org/docs/9.5/static/sql-notify.html

"... if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed. This is appropriate, since if the transaction is aborted, all the commands within it have had no effect, including NOTIFY.... Secondly, if a listening session receives a notification signal while it is within a transaction, the notification event will not be delivered to its connected client until just after the transaction is completed (either committed or aborted). Again, the reasoning is that if a notification were delivered within a transaction that was later aborted, one would want the notification to be undone somehow — but the server cannot "take back" a notification once it has sent it to the client. So notification events are only delivered between transactions."

https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9....

and his python script for doing this: https://github.com/pgexperts/flexible-freeze

Also, note that long running transactions can prevent cleanup of tuples. Look for old xact_start values of non-idle queries in pg_stat_activity (particularly "idle in transaction" connections) and old entries in pg_prepared_xacts.

The founders both have strong academic and commercial experience and the advisor is Prof. Joseph Hellerstein (he has both an excellent academic reputation and good experience in industry/tech transfer).

Note that TPCH is a "decision support benchmark". There are other technologies for helping postgres with these workloads as well: a column-store approach like https://github.com/citusdata/cstore_fdw, or the recent work on parallel sequential scans in Postgres core (http://rhaas.blogspot.com/2015/03/parallel-sequential-scan-f...), etc.

effective_cache_size should be set to a reasonable value of course, but it does not affect the allocated cache size, it's just used by the query optimizer.

You can avoid setting arbitrarily low--but not low enough--TTL, etc.

pg_rewind will be great for remastering under other scenarios (unexpected failovers, etc.)

or were you referring to some other issue with failed replication? something that mysql handles better/differently?

Note that the AWS IOPS numbers are for 16KB reads; with a max 4k IOPS disk you'll be getting a max throughput of ~62.5MB. You could toss a few together with software RAID 0 to scale up, but you're limited to a 4x improvement based on the largest instance types listed below:

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSOptimi...

spatial indices of the R-tree variety work quite well as long as the dimensionality isn't too high (i.e., your 2D data would be good).

note: the original 1984 paper on r-trees is a good one (although the performance section is a bit weak): http://postgis.refractions.net/support/rtree.pdf

further, you might decide to cluster on the index or partition.

The Postgres docs show a simple example that enforces the constraint "no two rows in the table contain overlapping circles" (&& is the overlaps operator for geometric types):

  CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
  );

the postgres query optimizer is an example I know off the top of my head (it uses GAs for the join order; at least for queries with enough joins): http://www.postgresql.org/docs/9.1/static/geqo.html

  $title =~ s/Google/Open Street/;

    import java.util.Scanner;
    
    public class Addup {
        public static void main(String args[]) {
            Scanner sc = new Scanner(System.in);
            int i1 = sc.nextInt();
            int i2 = sc.nextInt();
            System.out.println(i1 + i2);
        }
    }