Josh Berkus (pgExperts) - 5- Leo Hsu and Regina Obe - 5
- Michael Paquier (VMware) - 4
- Ernst-Georg Schmid - 3
- gabrielle roth - 3
- Glyn Astill - 3
- Greg Sabino Mullane (EndPoint) - 3
- Hubert 'depesz' Lubaczewski - 3
- Binod Nirvan - 2
- damien clochard (Dalibo) - 2
- Daniel Pocock - 2
- Denish Patel (OmniTI) - 2
- Dimitri Fontaine (2ndQuadrant) - 2
- Gabriele Bartolini (2ndQuadrant) - 2
- Josh Berkus - 2
- Pavel Stehule - 2
- US PostgreSQL Association - 2
- Amit Kapila - 1
- Andreas Scherbaum - 1
- David Christensen (EndPoint) - 1
- EndPoint - 5
- pgExperts - 5
- VMware - 4
- 2ndQuadrant - 4
- Dalibo - 3
- OmniTI - 2
- Cybertec - 1
- EnterpriseDB - 1
- Follow @planetpostgres on Twitter for the latest updates!
- Policy for being listed on Planet PostgreSQL.
- Add your blog to Planet PostgreSQL.
- List of all subscribed blogs.
- Manage your registration.
- Get in touch with the Planet PostgreSQL administrators at planet at postgresql.org.
In a application following an
intelligent database design, calls to stored procedures happen
very often and thus must be done with as little boilerplate as
possible.
Usually frameworks abstracting calls to the database are just ORMs,
ignoring completely stored procedures, making the database stupid
and moving all the logic in the application.
A year ago, I read on planet PostgreSQL (http://tech.zalando.com/posts/zalando-stored-procedure-wrapper-part-i.html)
about a simple system built using Java and the Java annotation and
reflection system.
A stored procedure can be called using a few lines of interface
:
@SProcService
interface BasicExample {
@SProcCall
long computeProduct(@SProcParam int a, @SProcParam int b);
}
Recently, I started planning the development on my spare time on a C++/Qt5 application using a PostgreSQL database and I realized I had just no way to easily call stored procedures. Doing a proper database for the application would thus be a huge pain from a C++ point of view, messing database calls in the middle of the application… Since my C++ skills needed an update (C++11 and C++14 are out in the wild since a few years and I never had an opportunity to use the new features they bring), I figured this would be the best time to do it.
C++ does not have (yet… C++17, I have faith in you) the
attributes and introspection used in the stored procedure wrapper
of Zalando. Instead, C++ has a great compilation-time processing
system through the templates. Templates are not just meant for
implementing generics, they are a turing-complete meta-programming
language. You can really do a lot of things using them. A lot. For
instance, a tuple type working just like a Python tuple, to store a
few values of different types side by side. Or implementing a
compile-time mathematical function. C++11 and C++14 brought
variadic templates, auto and a few other tools
that seemed very powerful and could yield great solutions for my
problem.
After a few hours of hacking, I had my first interesting results :
SqlBin
A couple of days ago the following commit has popped up in PostgreSQL tree for the upcoming 9.5, introducing a feature particularly interesting for developers of backend extensions and plugins:
commit: bd4e2fd97d3db84bd970d6051f775b7ff2af0e9d
author: Robert Haas <rhaas@postgresql.org>
date: Fri, 30 Jan 2015 12:56:48 -0500
Provide a way to supress the "out of memory" error when allocating.
Using the new interface MemoryContextAllocExtended, callers can
specify MCXT_ALLOC_NO_OOM if they are prepared to handle a NULL
return value.
Michael Paquier, reviewed and somewhat revised by me.
The memory allocation routines are located in the code of PostgreSQL in mcxt.c while being declared in palloc.h, the most famous routines of this set being palloc(), palloc0(), or repalloc() which work on CurrentMemoryContext. There are as well some higher-level routines called MemoryContextAlloc* able to perform allocations in a memory context specified by caller. Using those routines it is possible to allocate memory in any context other than the current one. Each existing allocation routine share a common property: when allocation request cannot be completed because of an out-of-memory error, process simply errors out, contrary to what a malloc() would do by returning a NULL pointer to the caller with ENONEM set as errno.
The commit above introduces in backend code a routine allowing to bypass this out-of-memory error and get back a NULL pointer if system runs out of memory, something particularly useful for features having a plan B if plan A that needed a certain amount of allocated buffer could not get the memory wanted. Let's imagine for example the case of a backend process performing some compression of data using a custom data type. If compression buffer cannot be allocated, process can store the data as-is instead of failing, making this case more robust.
So, the new routine is called MemoryContextAllocExtended, and comes with three control flags:
- MCXT_ALLOC_HUGE, to perform allocations higher than 1GB. This is equivale
Unlogged tables
One of our customers needed a new database, created as a copy of an existing one but with some data obscured for privacy reasons. We could have done this with a view or probably any of several other techniques, but in this case, given the surrounding infrastructure, a new database, refreshed regularly from the original, was the simplest method. Except that with this new database, the regular binary backups became too large for the backup volume in the system. Since it seemed silly to re-provision the backup system (and talk the client into paying for it) to accommodate data we could throw away and recalculate at any time, we chose unlogged tables as an alternative.
"Unlogged," in this case, means changes to this table aren't written in WAL logs. This makes for better performance, but also means if the database crashes, these tables can't be recovered in the usual way. As a side effect, it also means these tables aren't copied via WAL-based replication, so the table won't show up in a hot standby system, for instance, nor will the table appear in a system restored from a WAL-based backup (pg_dump will still find them). Unlogged tables wouldn't give our application much of a performance boost in this case — the improved performance applies mostly to queries that modify the data, and ours were meant to be read-only. But before this change, the regular refresh process generated all kinds of WAL logs, and now they've all disappeared. The backups are therefore far smaller, and once again fit within the available space. Should the server crash, we'll have a little more work to do, regenerating these tables from their sources, but that's a scripted process and simple to execute.
Stubborn vacuum
Another fairly new customer has a database under a heavy and very consistent write load. We've had to make autovacuum very aggressive to keep up with bloat in s
The next test is two tags combined. This is where the alternative approaches really pull ahead of the traditional tagging approaches.
For example, here's the text tag query:
select doc_id
from doc_tags_text dt1
join doc_tags_text dt2
using (doc_id)
where dt1.tag = 'math'
and dt2.tag = 'physics'
order by doc_id limit 25;
The query for tag IDs is even worse:
select di1.doc_id
from doc_tags_id di1
join doc_tags_id di2
on di1.doc_id = di2.doc_id
join tags tags1
on di1.tag_id = tags1.tag_id
join tags tags2
on di2.tag_id = tags2.tag_id
where tags1.tag = 'thrift shop'
and tags2.tag = 'blogging'
order by di1.doc_id limit 25;
Imagine how either of these would look for three tags, or four. Now compare that with the JSONB and array queries:
select doc_id
from doc_tags_array
where tags @> array['math','physics']
order by doc_id limit 25;
with find_docs as (
select doc_id
from doc_tags_json
where tags @> '[ "thrift shop", "blogging" ]'
)
select * from find_docs
order by doc_id limit 25;
(the dodge with the WITH clause is to force use of the JSONB index, per Part 2)
Big difference, eh? It can probably be taken as a given that if you need to do searches which involve combining two or more tags, you really want to use a GIN indexed approach just for code maintainability. Just in case, though, let's look at performance, both for combining two common tags ("math" and "physics", for 957 hits), and two rare tags ("thrift shop" and "blogging", which only have 5 hits). The differences in performance in the approaches were so extreme, I have to use a logarithmic scale for this graph, and am providing the raw numbers:
That's a huge difference. The "JSONB fixed" numbers are for a query where I force the planner to use the JSONB index instead of letting it choose its own path (using the doc_id index). As you can see, two-tag search
So the real test of the different tagging data structures and types is how they perform. The tests below were run on a 10 million document database, on an AWS m3.large virtual server. Importantly, the total database size is around 7GB on a machine with 3.75GB RAM, so I've deliberately sized things so that none of the tables will fit into shared_buffers.
So our first check was for size, including indexes. All other things being equal, a smaller table and indexes is faster to query, and takes up less RAM you'd like to use to cache other data. So smaller is better.
tagtest=# select relname,
pg_size_pretty(pg_total_relation_size(relid))
from pg_stat_user_tables;
relname | pg_size_pretty
----------------+----------------
tags | 96 kB
doc_tags_id | 1551 MB
doc_tags_text | 2106 MB
doc_tags_json | 1285 MB
doc_tags_array | 1036 MB
How about a graph of that?
The text array is a clear winner here, half the size of the default text tag option. Both of the advanced data types are smaller than any of the "normalized" versions. This is largely because of per-row overhead, which is 14 bytes per row, and really adds up.
So for our first exercise, we'll want to compare the amount of time required to retrieve all of the tags for a single document, which is probably the most common single operation. In this case I tested both documents with 1 tag (75% of all documents) and documents with 9 tags (< 1% of documents).
The next test is to simulate looking up paginat
The good news: submissions from people we don't normally see behind the podium at pgCon (Asians, South Americans, women, etc.) are up, so hopefully this will help make it a more diverse pgCon this year.
Now, the bad news: I am seeing the same issue I saw last year, which is long-time PostgreSQL community members submitting talks with a one or two line description. Yes, we know you're a great person and know lots about Postgres. But if we don't know what you're really going to talk about, we can't take it anyway, especially over other submitters who have carefully prepared a detailed description and thus shown that they plan to put time and effort into their talks. If this is you, you can still log in and improve the description of your talk.
There is more than one solution to this problem. One is the Tanimoto coefficient or Jaccard index of the peaks.
The Tanimoto coefficient T can be calculated as T = c / (a+b-c) with c the number of peaks both spectra have in common and a and b the number of peaks in each spectrum.
Similarity is then expressed as a number between 0 and 1, with 0 meaning no and 1 maximum similarity. Likewise the Tanimoto distance 1-T expresses the distance between two spectra.
On a table like
CREATE TABLE <spectra_table>
(
id integer NOT NULL,
"m/z" numeric NOT NULL,
intensity numeric NOT NULL
)
this can easily be done in SQL with a common table expression:
WITH SQ AS (select "m/z" from <spectra_table> where id=1),
ST AS (select "m/z" from <spectra_table> where id=2),
SC AS (select count(1)::float as common from SQ, ST where sq."m/z" = st."m/z")
select (select common FROM SC) / ((select count(1) from sq) + (select count(1) from st) - (SELECT common FROM SC))
as spectral_tanimoto;
If desired, further properties like the peak intensity can be used to narrow the match:
WITH SQ AS (select "m/z", intensity from <spectra_table> where id=1),
ST AS (select "m/z", intensity from <spectra_table> where id=2),
SC AS (select count(1)::float as common from SQ, ST where sq."m/z" = st."m/z" and sq.intensity = st.intensity)
select (select common FROM SC) / ((select count(1) from sq) + (select count(1) from st) - (SELECT common FROM SC))
as spectral_tanimoto;
The problem here is, that the join between the peaks relies on exact matching properties. 'Fuzzy' joins can relax this to some extent. The simple variant is to cast the m/z values to integers:
WITH SQ AS (select "m/z"::int from <spectra_table> where id=1),
ST AS (select "m/z"::int from <spectra_table> where id=2),
SC AS (select count(1)::float as common from SQ, ST where sq."m/z" = st."m/z")
select (select common FROM SC) / ((select count(1) from sq) + (select count(1) from st) - (SELECT comm
To test various ways of tagging, I created a test server on AWS (an m3.large) and put PostgreSQL on local storage to eliminate latency as a consideration. I then took a population of tags from two real databases: a document management application and a recipe database. This gave me a population of 165 unique tags to work with.
The simplest way to store tags is just the basic text tags table:
table doc_tags_text (
doc_id int not null references documents(doc_id),
tag text not null
)
unique index doc_tags_text_doc_id_tag on (doc_id, tag)
index doc_tags_text_tag on (tag)
This has the advantage of being simple, direct, and fast for some queries. I also created the "normalized" version of the above, which is equally common:
table tags (
tag_id serial not null primary key,
tag text not null unique
)
table doc_tags_id (
doc_id int not null references documents(doc_id),
tag_id int not null references tags(tag_id)
)
unique index doc_tags_id_doc_id_tag_id on (doc_id, tag_id)
index doc_tags_id_tag_id on (tag_id)
I put "normalized" in quotes because using a surrogate integer key doesn't actually make our schema more normal in any real sense. Nor does it make it smaller or faster, as we will soon see.
Now for the advanced Postgres magic. Of course, one of the first things I looked at was 9.4's JSONB. Particularly, JSON arrays stored in JSONB seemed to have every thing I was looking for: they can s
One of the lesser known Postgres parameters is also one of the most powerful: session_replication_role. In a nutshell, it allows you to completely bypass all triggers and rules for a specified amount of time. This was invented to allow replication systems to bypass all foreign keys and user triggers, but also can be used to greatly speed up bulk loading and updating.
The problem with disabling triggers
Once upon a time, there were two replication systems, Slony and Bucardo, that both shared the same problem: triggers (and rules) on a "target" table could really mess things up. In general, when you are replicating table information, you only want to replicate the data itself, and avoid any side effects. In other words, you need to prevent any "post-processing" of the data, which is what rules and triggers may do. The disabling of those was done in a fairly standard, but very ugly method: updating the system catalogs for the tables in question to trick Postgres into thinking that there were no rules or triggers. Here's what such SQL looks like in the Bucardo source code:
$SQL = q{
UPDATE pg_class
SET reltriggers = 0, relhasrules = false
WHERE (
};
$SQL .= join "OR\n"
=> map { "(oid = '$_->{safeschema}.$_->{safetable}'::regclass)" }
grep { $_->{reltype} eq 'table' }
@$goatlist;
$SQL .= ')';
This had a number of bad side effects. First and foremost, updating the system catalogs is never a recommended step. While it is *possible*, it is certainly discouraged. Because access to the system catalogs do not follow strict MVCC rules, odd things can sometimes happen. Another problem is that editing the system catalogs causes locking issues, as well as bloat on the system tables themselves. Yet another problem is that it was tricky do get this right; even the format of the system catalogs change over time, so that your code would need to have alternate paths for disabling and enabling triggers depending on the version of Postgres in use. Finally, the
Andreas 'ads' Scherbaum
As Josh Berkus blogged before, Pivotal is hosting the inaugural South Bay PostgreSQL Meetup in the Palo Alto office.
We were able to secure two very good speaker:
- Mason Sharp, from TransLattice: Scale-out PostgreSQL with PostgreSQL-XL
- Peter van Hardenberg, from Heroku: Postgres 9.4, new opportunities for users, admins and hackers
Please sign up at the Meetup website, if you plan to attend.
Date: Monday, February 16th, 2015
Time: 6:30pm
Location: 3495 Deer Creek Road Palo Alto, CA 94304 United States
PostgreSQL 9.4 and below doesn't support importing whole set of
tables from a FOREIGN server, but PostgreSQL 9.5 does with the
upcoming Import Foreign Schema. To use will require FDW
wrapper designers to be aware of this feature and use the plumbing
in their wrappers. IMPORT FOREIGN SCHEMA for ogr_fdw come PostgreSQL 9.5
release is on the features ticket list. The ogr_fdw comes with this to die for commandline
utility called ogr_fdw_info that does generate the
table structures for you and will also list all the tables in the
Foreign data source if you don't give it a specific table name. So
with this utility I wrote a little hack involving using PostgreSQL
COPY PROGRAM feature to call out to the
ogr_fdw_info commandline tool to figure out the table
names and some DO magic to create the tables.
Though ogr_fdw is designed to be a spatial foreign data wrapper, it's turning out to be a pretty nice non-spatial FDW as well especially for reading spreadsheets which we seem to get a lot of. This hack I am about to demonstrate I am demonstrating with LibreOffice/OpenOffice workbook, but works equally well with Excel workbooks and most any data source that OGR supports.
Continue reading "Import Foreign Schema hack with OGR_FDW and reading LibreOffice calc workbooks"
Actually this means: "DO NOT EVEN TOUCH!"
I found this out yesterday when tracking down frequent sigsevs in one of our PostgreSQL servers.
The Oracle instant client was set up with NAMES.DIRECTORY_PATH=(TNSNAMES, LDAP) in sqlnet.ora.
Even when the connect string used in the FDW is in the //<server>:<port>/<service> form, i.e. no Oracle name resolution is required, just allowing LDAP in sqlnet.ora will kill your server.
A couple of days back a new mode has been added in vacuumdb for the support of parallel jobs:
commit: a17923204736d8842eade3517d6a8ee81290fca4
author: Alvaro Herrera <alvherre@alvh.no-ip.org>
date: Fri, 23 Jan 2015 15:02:45 -0300
vacuumdb: enable parallel mode
This mode allows vacuumdb to open several server connections to vacuum
or analyze several tables simultaneously.
Author: Dilip Kumar. Some reworking by Álvaro Herrera
Reviewed by: Jeff Janes, Amit Kapila, Magnus Hagander, Andres Freund
When specifying a number of jobs with -j, the number of maximum connections defined by max_connections should be higher than the number of jobs specified as process creates a number of connections to the remote database equal to the number of jobs, and then reuses those connections to process the tables specified.
This of course supports all the modes already present in vacuumdb, like --analyze, --analyze-in-stages, etc. The list of tables processed in parallel can as well be customized when passing several values via --tables.
An important thing to note is that when using this feature with -f (VACUUM FULL), there are risks of deadlocks when processing catalog tables. For example in this case what happens was a conflict between pg_index and pg_depend:
$ vacuumdb -j 32 -f -d postgres
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming of database "postgres" failed: ERROR: deadlock detected
DETAIL: Process 2656 waits for RowExclusiveLock on relation 2608 of database 12974; blocked by process 2642.
Process 2642 waits for AccessShareLock on relation 2610 of database 12974; blocked by process 2656.
HINT: See server log for query details.
$ psql -At -c "SELECT relname FROM pg_class WHERE oid IN (2608,2610);"
pg_index
pg_depend
Note that this has higher chances to happen if:
- the number of relations defined on the database processed is low.
- the quantity of data to be processed is low
- the number of jobs is high
So be careful when using parallel jobs with FULL on a complete database.
The other day I demonstrated how to get your Github issues/bugs as an iCalendar feed.
I'm planning to take this concept further and I just whipped up another Python script, exposing Nagios issues as an iCalendar feed.
The script is nagios-icalendar. Usage is explained concisely in the README file, it takes just minutes to get up and running.
One interesting feature is that you can append a contact name to the URL and just get the issues for that contact, e.g.:
http://nagios-server.example.org:5001?contact=daniel
Screenshots
Here I demonstrate using Mozilla Lightning / Iceowl-extension to aggregate issues from Nagios, the Fedora instance of Bugzilla and Lumicall's Github issues into a single to-do list.
Today version 1.4.0 of Barman has been officially released. The
most important feature is incremental backup
support, which relies on rsync and hard links and helps you reduce
both backup time and disk space by 50-70%.
Barman adds one configuration option, called
reuse_backup. By setting this option to
link, Barman will transparently reuse
the latest available backup in the catalogue of a given server when
issuing a barman backup command.
“Mhh … hang on … Did you just say ‘Reuse’? In what terms?”
Essentially, Barman will reuse the previous backup in two phases:
- when reading the backup files;
- when permanently saving the backup files.
Barman simply exploits rsync’s robust and proven technology in order:
- to skip transferring those database files that have not changed from the latest base backup;
- use hard links on the backup side in order to save disk space (data deduplication).
If you happen to follow me on Twitter, you might have already seen this message about data deduplication on one of our customer’s database:
This particular case involves a very large
database of 13.2 Terabyte. Consider doing a weekly backup of a 13.2
TB database. You face two major problems:
- backup time
- backup size (with a large impact on retention policies)
As you can see, over 8.2 TB of data had not changed between the two backups, with a subsequent reduction of both backup time (17 hours instead of more than 50!) and disk space (5TB instead of 13TB!), as well as network bandwidth (by the way, Barman allows you to enable network compression too).
I must confess that a 13TB database is quite an exceptional case. However, what we have witnessed so far, even on smaller (and much smaller) databases, is an almost constant deduplication ratio in all the backups that we are managing with 2ndQuadrant. On average, deduplication ratio is between 50% and 70%.
The experience gained on this topic by Barman’s development team won’t stop here. A similar approach is being followed by Marco Nenciarini in its attempt to introduce file level incrementa
The simplest tools are usually the best.
One of the tools I usually need when I go see customers is vmstat. Nothing beats vmstat to give me a real overview of what the server is really doing. This overview gives system metrics, such as CPU usage, and disk usage. That's quite useful to check where the bottleneck comes from.
I wish I had a PostgreSQL tool like that. I wished enough to eventually build it. I call it pgstat because I couldn't find a better name for it.
It's an online command tool that connects to a database and grabs its activity statistics. As PostgreSQL has many statistics, you have a command switch to choose the one you want (-s):
- archiver for pg_stat_archiver
- bgwriter for pg_stat_bgwriter
- connection for connections by type
- database for pg_stat_database
- table for pg_stat_all_tables
- tableio for pg_statio_all_tables
- index for pg_stat_all_indexes
- function for pg_stat_user_function
- statement for pg_stat_statements
- pbpools for pgBouncer pools statistics
- pbstats for pgBouncer general statistics
It looks a lot like vmstat. You ask it the statistics you want, and the frequency to gather these statistics. Just like this:
$ pgstat -s connection
- total - active - lockwaiting - idle in transaction - idle -
1546 15 0 0 1531
1544 17 0 0 1527
1544 14 0 0 1530
1546 26 0 0 1520
1543 21 0 0 1522
Yeah, way too many idle connections. Actually, way too many connections. Definitely needs a pooler there.
This is what happens on a 10-secondes 10-clients pgbench test:
$ pgstat -s database 1
- backends - ------ xacts ------ -------------- blocks -------------- -------------- tuples -------------- ------ temp ------ ------- misc --------
commit rollback read hit read_time write_time ret fet ins upd del files bytes conflicts deadlocks
1I've just whipped up a Python script that renders Github issue lists from your favourite projects as an iCalendar feed.
The project is called github-icalendar. It uses Python Flask to expose the iCalendar feed over HTTP.
It is really easy to get up and running. All the dependencies are available on a modern Linux distribution, for example:
$ sudo apt-get install python-yaml python-icalendar python-flask python-pygithub
Just create an API token in Github and put it into a configuration file with a list of your repositories like this:
api_token: 6b36b3d7579d06c9f8e88bc6fb33864e4765e5fac4a3c2fd1bc33aad bind_address: ::0 bind_port: 5000 repositories: - repository: your-user-name/your-project - repository: your-user-name/another-project
Run it from the shell:
$ ./github_icalendar/main.py github-ics.cfg
and connect to it with your favourite iCalendar client.
Consolidating issue lists from Bugzilla, Github, Debian BTS and other sources
A single iCalendar client can usually support multiple sources and thereby consolidate lists of issues from multiple bug trackers.
This can be much more powerful than combining RSS bug feeds because iCalendar has built-in support for concepts such as priority and deadline. The client can use these to help you identify the most critical issues across all your projects, no matter which bug tracker they use.
Bugzilla bugtrackers already expose iCalendar feeds directly, just look for the iCalendar link at the bottom of any search results page. Here is an example URL from the Mozilla instance of Bugzilla.
The Ultimate Debian Database consolidates information from the Debian and Ubuntu universe and can already export it as an RSS feed, there is discussion about extrapolating that to an iCalendar feed too.
Further possibilities
- Prioritizing the issues in Github and mapping these priorities to iCalendar priorities
- Creating tags in Github that allow issues to be ignored/excluded from the feed (e.g. excluding wishlist items)
- Creating summary entries instead of listing all the issues, e.g.
One of the foreign data wrappers I included in the PostgreSQL 9.3 Windows FDW bag and PostgreSQL 9.4 Windows FDW bag is the www_fdw extension used for querying web services. Someone asked that since I didn't build curl with SSL support, they are unable to use it with https connections. The main reason I didn't is that the EDB installs come with ssleay32.dll and libeay32.dll (even the 64-bit) which are dependencies of curl when built with SSL support. I wanted to minimize the issue of distributing dlls that are packaged with Windows PostgreSQL installers already.
Though this article is specific to using www_fdw on Windows systems, many of the issues are equally applicable to other platforms, so may be worth a read if you are running into similar issues with using specialty SSL certificates on Linux/Unix/Mac.
Continue reading "Using SSL https connections with www_fdw on windows"
when the data can fit in RAM. I have ran a pgbench read-only load to
compare the performance difference between 9.4 and HEAD (62f5e447)
on IBM POWER-8 having 24 cores, 192 hardware threads, 492GB RAM
and here is the performance data
commit - ab5194e6 (Improve LWLock scalability). The previous implementation
has a bottleneck around spin locks that were acquired for LWLock
Acquisition and Release and the implantation for 9.5 has changed the
LWLock implementation to use atomic operations to manipulate the state.
Thanks to Andres Freund (and according to me the credit goes to reviewers
(Robert Haas and myself) as well who have reviewed multiple versions
of this patch) author of this patch due to whom many PostgreSQL users will
be happy.
In realistic cases, CREATE INDEX operations on text are over 3 times faster than in PostgreSQL 9.4. Not every such utility operation, or data warehousing query involving a big sort is sped up by that much, but many will be.
This was a piece of work that I spent a considerable amount of time on over the past few months. It's easy to justify that effort, though: sorting text is a very fundamental capability of any database system. Sorting is likely the dominant cost when creating B-Tree indexes, performing CLUSTER operations, and, most obviously, for sort nodes that are required by many plans that are executed in the service of queries with ORDER BY or DISTINCT clauses, or aggregates using the GroupAggregate strategy. Most of the utility statements that need to perform sorts must perform them with a very disruptive lock on the target relation (CREATE INDEX CONCURRENTLY is a notable exception), so quite apart from the expense of the sort, the duration of sorts often strongly influences how long a production system is seriously disrupted.
My interest in sorting is not new: I first worked on it in 2011. Early research on it back then prompted Robert Haas and Tom Lane to write the SortSupport infrastructure, which I've now extended here. Originally, the SortSupport infrastructure was all about providing alternative versions of comparators for use in sort routines, versions that avoided certain overhead otherwise inherent to calling functions that are generally accessible from SQL. As a highly extensible system, PostgreSQL requires that sort behavior be defined in terms of a default B-Tree operator class, which is itself defined in terms of SQL operators with underlying SQL-callable functions. These functions are written in C for built-in type
Regular Expression Stop Words for Postgresql
While working on a NLP project with OCR-derived text, I discovered a need that wasn’t immediately met by the built in parsers and dictionaries which ship with Postgresql. Fortunately text search in Postgresql is fairly extensible.
This post looks at the process of implementing a custom dictionary which can be used to create stop words which are really stop regular expressions.
Background on Parsing, Dictionaries, and Stop Words
The first step in using most text search features is converting unstructured text into text search vectors. A text search vector is a set of (lexeme, position) pairs.
=# select to_tsvector('Time is an illusion. Lunchtime doubly so.');
to_tsvector
--------------------------------------------
'doubli':6 'illus':4 'lunchtim':5 'time':1
(1 row)
As you might expect, the internal representation of a ts_vector is simply information about where words are located inside a big string.
typedef struct
{
int32 vl_len_; /* varlena header (do not touch directly!) */
int32 size;
WordEntry entries[1]; /* variable length */
/* lexemes follow the entries[] array */
} TSVectorData;
The transformation from text to ts_vector involves parsing text into tokens, then filtering the tokens through a dictionary which may change or eliminate words.
A text search configuration can be used to map token categories to dictionaries. The official documentation contains additional details.
The Problem Statement
The text corpus is derived from an OCR process which results in some unhelpful tokens.
a-433
-43 44 40
Lunchtime is 46 #@$dfsdf an illusion.Q!~ Lunchtime ksdfkjsadjfksdjf so.
431,-
So how to get pleasant ts_vectors?
One solution would be adding a token type ocr gibberish to the parser and removing mappings from ocr_gibberish to any dictionary. While you can write your own parser, the default is pretty good, and it does not appear to be easily extended.
Another approach would be to use a dictionary as a white
MixERP PgDoc is a console-based application that creates beautiful PostgreSQL database documentation. The first release supported Windows and with the second release today, we now support OSX and Linux (tested on OSX Yosemite and Ubuntu 14.4). Please make sure you have Mono installed first before you download PgDoc here:
http://mixerp.org/erp/mixerp-pgdoc-mono.zip
Running on OSX and Linux
Extract the downloaded archive mixerp-pgdoc-mono.zip. Open terminal and type
mono /path/to/mixerp-pgdoc.exe <arguments>
Or simply
mono /users/nirvan/desktop/pg-doc/mixerp-pgdoc.exe
-s=localhost -d=mixerp -u=postgres -p=secret
-o=/users/nirvan/desktop/db-doc
I have created a category in MixERP Forums for further discussion.
PostgreSQL 9.4 introduces a new statistic in the catalogue,
called pg_stat_archiver.
Thanks to the SQL language it is now possible, in an instant, to
check the state of the archiving process of transactional logs
(WALs), crucial component of a PostgreSQL disaster
recovery system.
Introduction and reasons
The need for the pg_stat_archiver view comes from the last few years of experience with Barman as a solution for disaster recovery of PostgreSQL databases in business continuity environments.
In particular, some of the needs and recurring questions that DBAs, system administrators, CTOs and CIOs repeatedly – and legimitately – share with us, are:
- how much disk space will I need?
- how can I keep the whole backup process, including continuous archiving, under control?
The starting point can only be a business requirement
and it is defined through the concept of retention
policy.
Usually, a company defines a disaster recovery
plan within a business continuity plan,
where it is clearly defined the period of retention of backup data.
In the same documents we find both the recovery point
objective (RPO) and the recovery time objective (RTO)
definitions, the two key metrics that respectively measure the
amount of data that a business can afford to lose and the maximum
allowed time to recover from a disaster.
Monitoring and studying the past behaviour of a database
are important elements for correctly sizing the storage
requirements of a PostgreSQL backup solution.
For example, a company may decide to retain data of a PostgreSQL database for a month, in order to reconstruct the state of the database in a consistent manner at any point in time from the first available backup to the last available WAL file (through the rock solid Point-in-Time-Recovery technology of PostgreSQL, introduced 10 years ago).
The required size is given not only by the number of periodic full backups (for example, one a week), but also the number of WAL files stored in the Barman archive, each containing all the transactions that have been pro
In the spirit of open-source, we would like to share how we handled the talk selection process for PGConf US 2015. This post will discuss the entire process of how a talk ends up in one of our United States PostgreSQL Association conferences; our goal is to help you understand what our conference is looking for in talk proposals and help you decide what you submit for PGConf US 2016!
One of the changes coming to you in PostGIS 2.2 are additional extensions. Two ones close to my heart are the address_standardizer (which was a separate project before, but folded into PostGIS in upcoming 2.2) and the SFCGAL extension for doing very advanced 3D stuff. We had a need to have address standardizer running on our Ubuntu box, but since PostGIS 2.2 isn't released yet, you can't get it without some compiling. Luckily the steps are fairly trivial if you are already running PostGIS 2.1. In this article, I'll walk thru just building an installing the address_standardizer extension from the PostGIS 2.2 code base. Though I'm doing this on Ubuntu, the instructions are pretty much the same on any Linux, just replacing with your Linux package manager.
Continue reading "Installing PostGIS packaged address_standardizer on Ubuntu"
When: Thu Jan 29, 6pm-8pm
Where: Renewable Funding, 400 SW 6th Ave
We’ll go over Foreign Data Wrappers, focusing on the Postgres FDW.
Our labs are casual, attendee-led affairs with no agenda set in advance. The goal is to share what we know & learn together.
Show up with a couple of Pg databases – they can be on the same cluster or different ones, doesn’t really matter. One of them should have at least one table with data in it.
We do need to know who’s coming so we can notify building security, so please sign up in advance here.
Grab dinner at the carts & come on over!
We’ve just released a new version of PoWA, the PostgreSQL Workload Analyzer. Check out complete release note here.
This new version adds an highly requested feature : you can now collect stats from multiple PostgreSQL servers using a single PoWA instance. This should be usefull if you have many servers to monitor. We’ve also improved the UI in many ways : the graph should be easier to read on full screen and the install process is more convienient
This version is probably the major release of the 1.x branch. We’re currently working on the branch that will change almost everything : PoWA 2.x will be only compatible with PostgreSQL 9.4 because we want to use the new stats and features of latest version. This should allow PoWA 2.x to some nice things like finding missing indexes or displaying advanced filesysteme stats. The 2.x version will also have a brand new user interface and we hope this will be similar to what we did when we rewrote completely the pgBadger interface 2 years ago…
Here’s a some preview:
Of course we will continue to maintain PoWA 1.x for the users who installed it on PostgreSQL 9.3 servers
If you go to FOSDEM 2015 next week, come see us ! We will be presenting these new features and more !
If you can’t go to FOSDEM 2015, subscribe to the powa-users mailing list to receive news and updates.