nierman

7 ms·

1.
by nierman 4y ago
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
2.
by nierman 5y ago
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 g
3.
by nierman 10y ago
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 refere
4.
by nierman 10y ago
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 da
5.
by nierman 10y ago
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 "eati
6.
by nierman 10y ago
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/
7.
by nierman 10y ago
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,
8.
by nierman 11y ago
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).
9.
by nierman 11y ago
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
10.
by nierman 11y ago
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.co
11.
by nierman 11y ago
MB/s is probably incorrect for the EBS throughput instance limits; those values make more sense if they use Mb/s.
12.
by nierman 11y ago
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
13.
by nierman 11y ago
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.
14.
by nierman 11y ago
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
15.
by nierman 11y ago
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 arbitra
16.
by nierman 11y ago
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
17.
by nierman 12y ago
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
18.
by nierman 12y ago
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 dis
19.
by nierman 12y ago
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 list
20.
by nierman 13y ago
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 f
21.
by nierman 14y ago
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
22.
by nierman 14y ago
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
23.
by nierman 14y ago
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
24.
by nierman 14y ago
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 overlap
25.
by nierman 14y ago
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 o
26.
by nierman 14y ago
$title =~ s/Google/Open Street/;
27.
by nierman 15y ago
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);