planet postgresql

Subscribe to planet postgresql のフィード
Planet PostgreSQL
更新: 1時間 35分 前

Don Seiler: Sequence Caching: Oracle vs. PostgreSQL

2018-10-02(火) 14:00:00

Many RDBMSes use sequence caching to pre-generate sequence values in advance and store them in memory, allowing them to return new values quickly. If you are doing a lot of inserts that each call the sequence to get the next value, sequence caching is a good thing. Having to wait for the sequence to generate a value on every call could slow things down greatly.

When I made the move from Oracle to PostgreSQL, I noticed some interesting differences in sequence caching behavior and wanted to share them here for those that may be curious about them.

All examples below were conducted in Oracle (12c Enterprise Edition) and PostgreSQL 9.6.9 (via PGDG Yum Repository).

Cache Value Sharing

In Oracle, when a sequence cache is generated, all sessions access the same cache. However in PostgreSQL, each session gets its own cache. We demonstrate this with a couple of quick-and-easy examples below.


First let’s create our sequence, using a cache size of 25.

SQL> CREATE SEQUENCE dts_seq 2 INCREMENT BY 1 3 CACHE 25; Sequence created.

Now I can open a couple of sessions and call nextval on that sequence to increment it from two different sessions:

-- First session SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 1 SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 2 SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 3 -- Second session SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 4 SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 5 SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 6

It’s even more clear when alternating between the two sessions:

-- Session A SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 151 -- Session B SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 152 -- Session A SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 153 -- Session B SQL> SELECT dts_seq.nextval FROM dual[...]
カテゴリー: postgresql

Bruce Momjian: Switchover/Failover and Session Migration

2018-10-02(火) 07:45:01

I have already covered switchover and failover. In the case of failover, the old primary is offline so there are no options for migrating clients from the old primary to the new primary. If there is a switchover, there are options for client migration. Assuming you are using streaming replication, only one server can accept writes. Therefore, for client migration, you can either:

  • Force all clients on the old primary to exit, then promote the new primary
  • Wait for all clients on the old primary to exit, then promote the new primary

If you choose force, it will disrupt applications; they must be designed to handle disconnections and potentially reconfigure their sessions, e.g., session variables, cursors, open transactions. If you choose wait, how do you handle clients that want to connect while you are waiting for the existing clients to disconnect? Minor upgrades, which require a restart of the database server, have similar issues.

The only clean solution is to use multi-master replication so new clients can connect to the new primary while waiting for old-primary clients to finish and disconnect. However, it is operationally expensive to support multi-master just to minimize switchover disruption.

カテゴリー: postgresql

Keith Fiske: Per-Table Autovacuum Tuning

2018-10-02(火) 03:43:06

A pattern that seems to drive my blog posts definitely seems to be the frequency of client questions. And that is definitely the case here again. Vacuum tuning to manage bloat and transaction id wraparound on production systems has been a hot topic and lately this has even been getting down to tuning autovacuum on the individual table basis. I’ve already discussed bloat pretty extensively in previous posts. While I’d like to get into the details of transaction ID wraparound, that really isn’t the focus of this post, so I’ll defer you to the documentation.

One setting I will discuss first though is autovacuum_freeze_max_age. Basically when any table’s max XID value reaches this, a more aggressive “emergency” autovacuum is kicked off. If many tables hit this at the same time (a common occurrence with data-warehouses that have many large, sparsely written tables), that can kick off some pretty high and long lasting IO caused by these autovacuums. I highly recommend increasing autovacuum_freeze_max_age from the default value of 200 million to 1 billion. However, I don’t recommend doing this unless you have some monitoring in place to watch for tables reaching both autovacuum_freeze_max_age and wraparound, the latter of which can shut your database down for some pretty extensive downtime.

So ideally, we want autovacuum running often enough on its own so you never encounter any of those bad situations above. The next thing I’d recommend tuning on any database before getting down to the table-level methods, no matter its size or traffic, are the default settings that control when autovacuum initially kicks in.

autovacuum_analyze_scale_factor = .10 autovacuum_analyze_threshold = 50 autovacuum_vacuum_scale_factor = .20 autovacuum_vacuum_threshold = 50

In my opinion, the defaults on these are not ideal for any situation. The scales are too high and the thresholds are too low. The scale factor settings are percentage values that say, “When this percentage of the table’s rows have changed (updates/deletes), run vacuum or ana

カテゴリー: postgresql

Andreas 'ads' Scherbaum: PGConf.EU 2018 - Speaker Interviews

2018-10-01(月) 19:00:00

Like in previous years, we conduct interviews with our speakers ahead of this year's PostgreSQL Conference Europe.

The first interviews are already online: 2018 - Speaker Interviews

If you are speaking in Lisbon, and haven't already submitted your answers, please check your mailbox. There should be an email asking you if you want to participate.

カテゴリー: postgresql

Paul Ramsey: PostGIS Code Sprint 2018 #2

2018-10-01(月) 17:00:00

An important topic of conversation this sprint was what kinds of core PostgreSQL features might make PostGIS better in the future?

Parallel GIST Scan

The PostGIS spatial index is built using the PostgreSQL GIST index infrastructure, so anything that makes GIST scans faster is a win for us. This would be a big win for folks with large tables (and thus deep trees) and who run scans that return a lot of indexed tuples.

Faster GIST Index Building

B-Tree index builds are accellerated by pre-sorting the inputs; could the same trick be used in building GIST indexes? Again, for large tables, GIST index building is slower than B-Tree and “faster” is the #1 feature all existing users want.

Multi-Threading in Functions

This isn’t a feature request, so much as a request for clarification and assurance: PostGIS calls out to other libraries, like GEOS, and it’s possible we could make some of our algorithms there faster via parallel processing. If we do our parallel processing within a function call, so the PostgreSQL thread of execution isn’t doing anything until we return, is it OK for us to do threading? We use pthreads, or maybe OpenMP.

Compressed Datum Slicing

“Detoast datum slice” doesn’t actually get around to the slicing step until after the datum is decompressed, which can make some queries quite slow. We already try to read boxes from the headers of our objects, and for large objects that means decompressing the whole thing: it would be nice to only decompress the first few bytes. I have an ugly patch I will be testing to try and get committed.

Forced Inlining

A problem we have with PostgreSQL right now is that we cannot effectively cost our functions due to the current inlining behaviour on our wrapper functions like ST_Intersects(). When raised on the list, the hackers came to a tentative conclusion that improving the value caching behaviour would be a good way to avoid having inlining in incorrect places. It sounded like subtle and difficult work, and nobody jumped to it.

We propose leaving the current inlining

カテゴリー: postgresql