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 "rollback;" or a "commit;"... I've been…
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…
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…
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…
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…
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…
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…
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:…
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.…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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"…
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 ;)…
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 "rollback;" or a "commit;"... I've been…
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…
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…
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…
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…
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…
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…
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:…
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.…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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"…
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 ;)…