2

PostgreSQL 9.4

In the article about hardware optimization for PostgreSQL server was shown that too much moving disk head may cause performance bottleneck. As far as I got, for the sequential scan, it'd be much faster if the cylinders with blocks of the same table are located as close as possible. So, if we're inserting data into a table, the data will be placed in "sequential order" on a disk-storage, therefore reducing the disk head moving overhead.

But I presume that such location may be break in time. Is there a way for postgreSQL to maintain such "clustering" (if I can say so)?

Patrick
  • 29,357
  • 6
  • 62
  • 90
St.Antario
  • 26,175
  • 41
  • 130
  • 318
  • You can use the [cluster](http://www.postgresql.org/docs/current/static/sql-cluster.html) command. But that won' t "maintain" the ordering. But any real world DB server will have an several hard disks (RAID) (or even SSDs) and in that case you can't control the ordering of the rows anyway. –  Oct 05 '15 at 07:27
  • And I guess that if you're using SSD then the seek time is largely irrelevant. Maybe that's the answer. – David Aldridge Oct 05 '15 at 07:55
  • @a_horse_with_no_name I still don't see why such operation doesn't make sense. If a DB has more than 1 hard disks, why isn't it useful to "maintain" physical ordering of the table's data on each drive? – St.Antario Oct 05 '15 at 08:09
  • @a_horse_with_no_name Is there a real-world case where we should take the physical ordering into account. Or we can permanently forget about it at all, as I was told [there](http://stackoverflow.com/a/32497086/2786156) – St.Antario Oct 05 '15 at 08:11
  • Because you have no control over where the RAID controller will put the blocks, so they might not be sequential after all. And what exactly does "sequential" mean if you are reading data from say 10 hard disks at the same time? Unless you need to tune some low-end single hard disk server for a _very_ special use case I would simply forget about this. –  Oct 05 '15 at 08:11
  • @a_horse_with_no_name _And what exactly does "sequential" mean_ But each of the hard disks has its own head to read the data. So I'd say, sequentially means requireing the least possible time for the disk's head to move from one cylinders to another containing the table's data on each disk. – St.Antario Oct 05 '15 at 08:14
  • @a_horse_with_no_name __Unless you need to tune some low-end single hard disk server for a very special use case I would simply forget about this.__ Got it, thank you. – St.Antario Oct 05 '15 at 08:15

0 Answers0