6,040 reputation
31331
bio website
location Berlin, Germany
age 38
visits member for 2 years, 2 months
seen 19 hours ago

Mar
12
comment Best Approach to storing chat history?
Adding to @CraigRinger's comment, I am wondering if constantly updating the MongoDB document for a given chat is faster than storing rows in PostgreSQL (or fast enough for being a viable solution).
Mar
11
comment Order by distance
I was thinking that a point being inside a geometry or nor farther than a given distance may be more or less equivalent in some cases.
Mar
11
comment SELECT DISTINCT with another table
You can always try to fix it youorself. In this case you could simply pick a table alias (either r or g)...
Mar
11
comment Order by distance
Can you use the distance in your WHERE clause? Like comparing it with a threshold?
Mar
10
comment How can streaming replication be kept lagged by a certain 'delay' time?
On one hand, I believe you cannot tell the standby where to stream the recieved WAL records. On the other hand, what you propose can possibly work. If you cannot archive the WAL on the master to a place where it is also available to the standby, this can be a solution. What you won't be able to influence is that, between two pauses, how much of the records will be applied. But then this is not that far from my suggestion where the delay is also like delay plusminus epsilon :)
Mar
10
comment How can streaming replication be kept lagged by a certain 'delay' time?
Exactly. I don't see how this could be solved with streaming.
Mar
10
comment Postgres Point-In-Time Recovery with a limited point in time
I would say you get base2.tgz the same way you got base1.tgz :)
Mar
10
comment How can streaming replication be kept lagged by a certain 'delay' time?
Streaming replication cannot do this, but if you make the standby getting the WAL files and apply them, you can apply an arbitrary delay. We push the WALs to a NFS volume and the restore command gets them from there to the local WAL dir, checking if the files are old enough.
Mar
10
comment Why does this limit make the postgres planner use a much slower index scan instead of a much faster bitmap heap/index scan?
@Mohan Not really. But the good news is that it does not really make sense - sometimes this is faster, sometimes that, all depending on the given scenario. You can be pretty confident that the planner chooses the best possible plan for a given query if you keep the statistics in good shape.
Mar
10
comment Why would you index text_pattern_ops on a text column?
@ErwinBrandstetter I have to confirm, you are right.
Mar
7
comment Converting a Ruby on Rails site from MySQL to PostgreSQL?
This question appears to be off-topic because it is a strong candidate of LMGTFY (see Craig's comment).
Mar
5
comment Some SQLs sometimes take all available RAM
That 15-30 minutes is under 1 second, as seen from the plan you linked. Returning 20 rows, so it cannot be that the client munches on your data so long. So it looks like some important piece of information is missing here...
Mar
4
comment Extract all tags from hstore field
@MMacD you can still mark as accepted one of these (or add your own answer, basically copying your comment, and accept that). I'm glad to hear that we could help.
Mar
4
comment Some SQLs sometimes take all available RAM
Please provide the table and index definitions and the output of EXPLAIN (ANALYZE, BUFFERS). Also, the settings shared_buffers and work_mem can be of interest, probably a few others, too.
Feb
28
comment Order of columns in a compound index in PostgreSQL (and query order)
How about creating the mentioned indexes and check which one is used?
Feb
26
comment Nearest value for each foreign key
Probably I did not understand you correctly. Do you want to follow the FK to the referenced table? Or what is the problem?
Feb
26
comment Most performant way to fetch last record
This is not 'non formal' but a form of denormalization. Sometimes this is the only viable solution, but when one pushes it (I mean denormalizing by triggers) too far, it can be a maintenance and bugtracking nightmare.
Feb
26
comment PostgreSQL Streaming Replication - Slave remains in recovery mode
postgresql.org/docs/9.0/static/… may give you some hints how to circumvent this.
Feb
26
comment Select * on inner join with common columns
One approach can be to add prefixes to the column, derived from the table name, like a first name column on a table calles order, gets the name o_first_name, for example. If you manage to keep the prefixes unique, it can solve such a problem.
Feb
25
comment Select * on inner join with common columns
This is why I left it in a comment and not an answer. However, I would like to know why listing the columns is not an option...