On Tue, Oct 29, 2013 at 10:49 AM, Leonardo Francalanci <m_li...@yahoo.it> wrote: >> Another point to add: I don't really see btree as a barrier to >> performance for most of the problems I face. The real barriers to >> database performance are storage, contention, and query planning. > > Ehm that's true for regular OLTP stuff, which I understand is what most > (95%?) of people use/need. But if you try to insert rows into a 50M table > with a couple of indexes, btrees just can't keep up. > Of course, you can't have it all: fast at big table insertion, good > contention, good query times... > >> Postgres btreee indexes are pretty fast and for stuff like bulk >> insertions there are some optimization techniques available (such as >> sharding or create index concurrently). > > > At the moment I'm relying on partitioning + creating indexes in bulk on > "latest" table (the partitioning is based on time). But that means K*log(N) > search times (where K is the number of partitions). > That's why I gave a look at these different indexing mechanisms.
I bet you've mis-diagnosed the problem. Btrees don't have a problem keeping up with 50m records; you're problem is that after a certain point your page cache can't keep up with the pseudo-random i/o patterns and you start seeing faults to storage. Disk storage is several order of magnitude slower than memory and thus performance collapses. This has nothing to do the btree algorithm except to the extent it affects i/o patterns. With the advances in storage over the last several years such that commodity priced SSD is available I think that all lot of assumptions under these trade-offs will change. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
