TPS too high in PostgreSQL

I created a table:

CREATE TABLE foo (
    id integer primary key,
    bar integer NOT NULL
);

and inserted one row:

INSERT INTO foo (id, bar) VALUES (1, 1);

and ran a pgbench of the following simple update statement.

script.txt:

update foo set bar=bar+1 where id=1

pgbench result:

C:\my-temp>"c:\Program Files\PostgreSQL\9.3\bin\pgbench.exe" -U postgres -c 1 -t 10000 -n -f script.txt testdb
Password:
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 10000/10000
tps = 2052.384567 (including connections establishing)
tps = 2058.699883 (excluding connections establishing)

Given that it's serially executing transactions, this tps result seems to be too high for my system:

OS: Windows 8
CPU: Intel Core i3-2100 3.10GHz
RAM: 4GB
Disk: SATA II 7200 rpm
PostgreSQL: 9.3

I haven't changed the default settings, so fsync and synchronous_commit options should be both on.

Here is my postgresql.conf:

```
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter c

To answer your first question.

No, the fsync parameter in postgresql.conf only tells the database that it should write to disk, not how it should do it. According to the documentation:


If this parameter is on, the PostgreSQL server will try to make sure that updates are physically written to disk, by issuing fsync() system calls or various equivalent methods

Which in this case, since you have not changed the wal_sync_method it should choose open_datasync since that is available and the default for Windows platforms.

Also, here I can only speculate:

But seeing that the table is small, and the updates as well, I would guess that you are actually hitting the write-back cache in the hard-drive, which means that you are not writing directly to the disk and therefore the operations are not slowed down due to the writing.

You do not mention what kind of hard drive it is, so it is not possible to know if the cache is write-back, but it sounds like a possible scenario.

StackExchange Database Administrators Q#50947, answer score: 3