Reputation
1,739
Top tag
Next privilege 2,000 Rep.
Edit questions and answers
Badges
5 17
Newest
 Yearling
Impact
~90k people reached

Apr
11
awarded  Yearling
Sep
30
awarded  Explainer
Apr
11
awarded  Yearling
Sep
17
comment How to optimization database for heavy I/O from updates (software and hardware)
In addition to what @dezso said, hard drives are OK for storage dedicated to WAL because access is almost entirely sequential, so seek time is minimal..
Sep
16
answered How to optimization database for heavy I/O from updates (software and hardware)
Apr
11
awarded  Yearling
Feb
18
comment Postgresql - Segmentation fault (core dumped)
To get a core dump in Linux (which is one of your tags), you should run ulimit -c unlimited in the session or script which is starting the executable which is getting the segmentation fault. It looks like this setting is in effect, since it says "Segmentation fault (core dumped)". You can examine the back-trace and values of variables in gdb if you specify the core file when you start it. For that to be useful, you must have built with debug info, and it sometimes helps to reduce the optimization level of code.
Feb
18
comment Is PostgreSQL replication production ready?
@CraigRinger: What I have seen people ask for is not quite what you stated, they sometimes request "Use sync rep but automatically fall back to async rep if the sync is taking too long." So they do not want to pause the master if it falls too far behind the replica -- that's exactly the case where they want it to confirm commits quickly, without any write to another site. To me that sounds like a case of promising more than is being delivered. They want up-front "Yeah, you have sync rep; you're safe." and after a crash "That committed data is gone; it wasn't really written anywhere else."
Feb
10
revised Postgresql - Segmentation fault (core dumped)
Add a paragraph to address the setfault aspect of the question.
Feb
10
answered Postgresql - Segmentation fault (core dumped)
Feb
10
comment Is PostgreSQL replication production ready?
There is one thing about sync rep which surprises some users, but really makes sense if you think about it: the commit of a transaction which is subject to synchronous replication will not return until the transaction has been persisted on at least one cluster besides the master. People coming from some other systems feel that should happen unless the replication attempt takes too long, which would mean "it's synchronous unless it's not," which is not an acceptable guarantee to the PostgreSQL community. Use multiple sync targets to avoid a stall if the replica fails, or use async.
Feb
8
awarded  Custodian
Feb
8
reviewed Satisfactory Decomposition of a relation to 2NF then to 3NF
Jan
18
revised how to query for rows by specifying the range of row numbers in postgresql
Clean up formatting to better represent quotes.
Dec
3
awarded  Enlightened
Dec
3
awarded  Nice Answer
Nov
2
awarded  Nice Answer
Oct
29
comment Locking issue with concurrent DELETE / INSERT in PostgreSQL
As long as you are using READ COMMITTED transactions, you have a race condition: what would happen if another transaction inserted a new row after the first DELETE started and before the second DELETE started? With transactions less strict than SERIALIZABLE the two main ways to close race conditions are through promotion of a conflict (but that doesn't help when the row is being deleted) and materialization of a conflict. You could materialize the conflict by having an "id" table that was updated for every row deleted, or by explicitly locking the table. Or use retries on error.
Oct
28
comment Locking issue with concurrent DELETE / INSERT in PostgreSQL
I'm sure there's a way to get whatever it is that you need working to work consistently with both databases; if nothing else setting the transaction isolation level to SERIALIZABLE and handling serialization failures (SQLSTATE 40001) by retrying the failed transaction from the start would almost certainly do it. For this particular example you want each transaction to leave the database in exactly the same state it started in, which is very easily done in any case, by not doing DML. Perhaps an example closer to what you need would draw useful suggestions for less strict isolation levels.
Oct
27
revised Locking issue with concurrent DELETE / INSERT in PostgreSQL
Add some additional elaborations.