StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First, I think you need to be a lot more specific. Performance tuning is very fact-centric and without a lot of details (explain plans, etc), information on your hardware, etc. we can't tell you what to do. Additionally something like Postgres-XC adds a lot of complexity although it does help with write performance. I think it would help in your case but you really want to optimize what you have (and maybe hire someone to optimize it for you) first.</p> <p>There are however a bunch of warning signs in your post (which is another reason I think hiring a professional might be a good idea). Without knowing more I can't tell you whether Postgres-XC is the right solution or not. What I can tell you is you will have a steep learning curve implementing it.</p> <p>So I want to go through the warning signs because they represent possible tuning points.</p> <ol> <li><p><code>i see that the postgres server is cpu bound and the postgres processes are UPDATEing all the time.</code> This is most likely caused by too much contention for semaphores and shared memory. You are likely to find that if you reduce your max connections you will process more per second. A connection pool might help.</p></li> <li><p>All your interesting data is on extended storage. This means extra random disk I/O on storage and retrieval. Unless you are doing a lot of sequential scans on the tables, you should let PostgreSQL decide what to TOAST.</p></li> <li><p>I call baloney on your claim that most statements are like <code>UPDATE <table> updated_at=<date> WHERE id=<id></code> since there is probably very little reason to record a row as updated when you aren't updating the data. Something else is likely going on here. My guess is that you have a lot of queries updating stuff in extended storage too. This may not be a big deal performance wise since you are not I/O bound but it does incur overhead with both CPU and disk I/O.</p></li> </ol> <p>On the whole Postgres-XC is a great project and I would recommend it. However it adds a lot of complexity to the database and if you can make your single instance work, you are likely to find that is a lot cheaper to run in the long run (simplicity is golden).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload