nierman

you can also take advantage of transactions:

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

I grew up in Washington State and remember eating a lot of Gala, Braeburn, and Fuji apples in that time period; I think those are all pretty good apples to eat "out of hand" (compared to what you list for sure; a granny smith is great in a pie though!). Yeah, some of the tasty varieties like Cameo, Honeycrisp, etc, etc, weren't really widely available until quite a bit later, even if they were discovered in the 80s/90s.

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

yes, wal archiving would have helped (archive_command = rsync standby ...), but it's also very easy in postgres 9.4+ to add a replication slot on the master so that wal is kept until it is no longer needed by the standby. simply reference the slot in the standby's recovery.conf file.

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

with respect to "Difficulty upgrading to newer releases":

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 don't usually eat breakfast, but one of the cereals I really like is Heritage Flakes from Nature's Path: http://us.naturespath.com/product/heritager-flakes

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

yeah, the current/official JDBC driver does not support async listen/notify so you have to do polling.

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

the way in which notify/listen interacts with transactions is interesting; from the docs:

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."

I'd like to make more use of it but it can't "repack" tables that have gist indices (e.g., postgis spatial indices, etc).

note: there are also "skip locked" and "nowait" clauses in Postgres 9.5 for more flexibility when dealing with row level locking. This gives you the option of failing immediately (nowait) or just getting back the set of rows that aren't locked (skip locked).

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

Running more aggressive vacuums that will clean up "old" tuples/tables is good for off-peak times. See Josh Berkus' short three part blog series on postgres' vacuum/freeze: http://www.databasesoup.com/2012/09/freezing-your-tuples-off...

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.

MB/s is probably incorrect for the EBS throughput instance limits; those values make more sense if they use Mb/s.

Check out the Tech and Company pages as well as the slides from the recent South Bay PostgreSQL Meeetup: http://goo.gl/Mtg2W6

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.

shared_buffers is the setting you're thinking of here.

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.

Even if the long-running transaction hasn't accessed the job queue table yet it might do so before it completes. Postgres needs to keep the "dead" tuples accessible as long as there are active transactions that started before the deletion.

Don't muck with the DNS record. Use an "A record" associated with a secondary/floating IP; then simply move the IP associated with the A record to the new machine and issue a gratuitous ARP.

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

If you are performing a planned failover then the old master can be turned into a slave without extra tools or steps. Simply shut down the master first. As part of this process it waits until the slave has the necessary wal.

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

what sort of "replication issues"? if you use archive_command and ship/rsync wal files somewhere you will safeguard against falling too far behind (past an arbitrary wal_keep_segments limit). Also, in 9.4 you have replication slots so you can get rid of wal_keep_segments altogether: the master knows the replication progress/status of each slave and keeps required wal files around until they are no longer needed.

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

For the best performance you'd want dedicated hardware with a battery-backed RAID array. SSDs can be used for tablespaces with the most frequently accessed data.

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...

But note that the baseline performance for the new General Purpose SSD is listed as the size (in GB) times 3 IOPS (so 3K/3072 IOPS for your 1000GB/1TB example). So large volumes will have a baseline that basically matches the listed "burst" IOPS speed, and at less than 1/2 the cost of the PIOPS version.

If you're taking an instantaneous snapshot of the system then yes. A standard copy/rsync/etc. isn't going to give you that. If the copy takes a long time at what point do you grab the pg_xlog directory? and are all the files there that you need/ed?

you could try PostGIS's spatial indices for a less manual/static approach.

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

and you probably want to include these indices in any case to enforce that a given (user, movie) pair in the join table is unique (unless you want to track if a rating was changed and when, etc.)

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

or you could add an index to the join table and see a similar speedup without the accuracy tradeoff. I'm not saying that the approach doesn't have applications, but it would be much more useful if the post discussed a case that couldn't be addressed easily by standard means.

Using gist indices for exclusion constraints (added in pg 9.0) can be pretty useful also.

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 &&)
  );

it's basically a search technique for problems where the search space is too large. so you visit "promising" portions of the search space. this happens a lot in computer science, and it happens even more in real life ;)

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/;

yeah, the use of java.util.Scanner would make it much more succinct:

    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);
        }
    }