Viewing profile — 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. and then issue either a …

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" (compa…

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 unt…

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 b…

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…

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

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 ins…

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 (…

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://…

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 …

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 jus…

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 ther…

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 gratu…

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 …

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_segme…

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 …

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 wi…

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 …

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

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 …

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 muc…