Re: Use Postgres as a column store by creating one table per column

On 22/05/19 4:28 PM, Lev Kokotov wrote: Is it efficient to use Postgres as a column store by creating one table per column? I would query it with something like `[...] UNION SELECT value AS FROM WHERE value = UNION [...]` to build a row. I'm thinking since Postgres stores tables in

Re: PostgreSQL upgrade.

On 15/04/19 2:26 PM, Mark Kirkwood wrote: > > On 10/04/19 8:20 PM, Andreas Kretschmer wrote: >> >> >> Am 10.04.19 um 07:40 schrieb Daulat Ram: >>> We have two node postgresql database version 9.6 with streaming >>> replication which is running on docker

Re: pgbench results arent accurate

Hi, I can see two issues making you get variable results: 1/ Number of clients > scale factor Using -c16 and -s 6 means you are largely benchmarking lock contention for a row in the branches table (it has 6 rows in your case). So randomness in *which* rows each client tries to lock will make

Re: Why HDD performance is better than SSD in this case

Hi Neto, In a great piece of timing my new experimental SSD arrived (WD Black 500 G 3D NAND MVME) [1]. Doing some runs of query 9: - default config: 7 minutes - work_mem=1G, max_parallel_workers_per_gather=4: 3-4 minutes Increasing either of these much more got me OOM errors (16 G of ram).

Re: Why HDD performance is better than SSD in this case

of the time taken for this query is in writing and reading files for sorting/hashing, so where pgsql_tmp is located hugely influences the overall time. regards Mark On 20/07/18 12:33, Mark Kirkwood wrote: FWIW: re-running query 9 using the SSD setup as 2x crucial M550 RAID0: 10 minutes. On 20

Re: Why HDD performance is better than SSD in this case

FWIW: re-running query 9 using the SSD setup as 2x crucial M550 RAID0: 10 minutes. On 20/07/18 11:30, Mark Kirkwood wrote: One more thought on this: Query 9 does a lot pf sorting to disk - so there will be writes for that and all the reads for the table scans. Thus the location of your

Re: Why HDD performance is better than SSD in this case

One more thought on this: Query 9 does a lot pf sorting to disk - so there will be writes for that and all the reads for the table scans. Thus the location of your instance's pgsql_tmp directory(s) will significantly influence results. I'm wondering if in your HDD test the pgsql_tmp on the

Re: Why HDD performance is better than SSD in this case

Ok, so you are using 1 instance and tablespaces. Also I see you are restarting the instance between HDD and SSD tests, so all good there. The point I made about having the OS on the SSD's means that if these tests make your system swap, and your swap device is on the SSDs (which is probably

Re: Why HDD performance is better than SSD in this case

Yeah, A +1 to telling us the model. In particular the later EVOs use TLC nand with a small SLC cache... and when you exhaust the SLC cache the performance can be worse than a HDD... On 18/07/18 01:44, Nicolas Charles wrote: Hi Neto, You should list the SSD model also - there are pleinty

Re: Why HDD performance is better than SSD in this case

Ok, so dropping the cache is good. How are you ensuring that you have one test setup on the HDDs and one on the SSDs? i.e do you have 2 postgres instances? or are you using one instance with tablespaces to locate the relevant tables? If the 2nd case then you will get pollution of

Re: Why HDD performance is better than SSD in this case

Can you post make and model of the SSD concerned? In general the cheaper consumer grade ones cannot do sustained read/writes at anything like their quoted max values. regards Mark On 17/07/18 17:00, Neto pr wrote: Dear, Some of you can help me understand this. This query plan is executed

Re: Sv: Sv: Re: Latest advice on SSD?

On 11/05/18 23:23, Andreas Joseph Krogh wrote: På onsdag 09. mai 2018 kl. 22:00:16, skrev Andreas Joseph Krogh >: På tirsdag 10. april 2018 kl. 19:41:59, skrev Craig James >:

Re: HDD vs SSD without explanation

On 16/01/18 23:14, Neto pr wrote: 2018-01-15 20:04 GMT-08:00 Mark Kirkwood <mark.kirkw...@catalyst.net.nz>: On 16/01/18 13:18, Fernando Hevia wrote: The 6 Gb/s interface is capable of a maximum throughput of around 600 Mb/s. None of your drives can achieve that so I don't thi