![]() ![]() Using methods such as fetchone(), fetchmany(), Retrieve data from the database by iteration or Send commands to the database using methods such as execute() The class cursor allows interaction with the database: Terminate transactions using the methods commit() or It allows to:Ĭreate new cursor instances using the cursor() method to The class connection encapsulates a database session. The function connect() creates a new database session and commit () # Close communication with the database > cur. ![]() fetchone () (1, 100, "abc'def") # Make the changes to the database persistent > conn. ( 100, "abc'def" )) # Query the database and obtain data as Python objects > cur. execute ( "INSERT INTO test (num, data) VALUES ( %s, %s )". execute ( "CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar) " ) # Pass data to fill a query placeholders and let Psycopg perform # the correct conversion (no more SQL injections!) > cur. cursor () # Execute a command: this creates a new table > cur. connect ( "dbname=test user=postgres" ) # Open a cursor to perform database operations > cur = conn. Though now that the default value of sequence_range is changed to 1000, not many users may see the difference in performance.> import psycopg2 # Connect to an existing database > conn = psycopg2. You may choose any of these techniques to improve the performance. postgres=# ALTER SEQUENCE sensor_log_sensor_log_id_seq CACHE 1000 ALTER SEQUENCE But the sequence_range GUC provides an easy way to override the global default and also ensures that the sequences are cached only when they are getting incremented very rapidly. Following example shows how to do that for a serial column. Similar improvement can also be achieved by increasing the CACHE parameter value of the sequence so that a chunk of sequence values are cached at the backend level. To address this issue, Postgres-XL starts with the specified CACHE parameter value used at sequence creation time and doubles it every time (limited by sequence_range) if sequences are being consumed at a very high rate. Its important to note that while a high value of sequence_range will improve performance for sequences and serials, it can also leave large holes in sequence ranges since the sequence ranges are cached at a backend level. But looking at the performance implications for a very common use case, we decided to increase the default to 1000 and this has now been committed to the XL9_5_STABLE branch of the repository. When this feature was added, the default value of sequence_range GUC was set to 1 because it can leave holes in the sequence values. So by appropriately setting sequence_range to 1000, performance of the INSERT query improved by nearly 30 times. Postgres=# INSERT INTO sensor_log (location, reading, reading_date) SELECT s.id % 1000, s.id % 100, now() - (s.id || 's')::INTERVAL FROM generate_series(1, 40000) s(id) Here is an example, using the same sample schema as used by Shaun in his blog post. Unfortunately, for regular INSERTs, the default is 1 and unless user explicitly sets sequence_range to a reasonably higher value, INSERT performance suffers. Given that COPY is popularly used to bulk load data in Postgres, Postgres-XL automatically overrides this GUC during COPY operation and sets it to 1000, thus dramatically improving COPY performance. Every backend requests a block of sequence values as controlled by this GUC. Postgres-XL provides a user-settable GUC called sequence_range. I thought this is a good opportunity to explain the facility. There is already a way to improve performance for sequences, but it’s clearly not well advertised. Shaun Thomas in a recent blog complained about INSERTs running a magnitude slower on Postgres-XL as compared to vanilla PostgreSQL. This adds significant overhead for a query doing thousands of INSERTs in a table with a serial column, incrementing sequence one at a time and making a network roundtrip to the GTM, for every INSERT. In Postgres-XL, sequences are maintained at the Global Transaction Manager (GTM) to ensure that they are assigned non-conflicting values when they are incremented from multiple nodes. PostgreSQL 9 Cookbook – Chinese Edition.PostgreSQL Server Programming Cookbook – 2nd Edition.PostgreSQL 9 Administration Cookbook – 3rd Edition.PostgreSQL High Availability Cookbook – 2nd Edition. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |