Simon Willison’s Weblog

“postgresql” in items

Implementing faceted search with Django and PostgreSQL

I’ve added a faceted search engine to this blog, powered by PostgreSQL. It supports regular text search (proper search, not just SQL“like” queries), filter by tag, filter by date, filter by content type (entries vs blogmarks vs quotation) and any combination of the above. Some example searches:

[... 3048 words]

PostgreSQL 8.5 alpha 2 is out. “P.S. If you’re wondering about Hot Standby and Synchronous Replication, they’re still under heavy development and still (at this point) expected to be in 8.5.”—Hot Standby is PostgreSQL-speak for MySQL-style master/slave replication for scaling your reads. # 28th October 2009, 9:02 am

PostgreSQL: How can I store images in a database? What existing products makes it easy for a user to upload photos into a general database?

As a general rule, it’s a bad idea to store images in a database. This is due to the large amount of space they take up, which can affect database read performance and will greatly increases the size of your backups, making them both take longer and cost more to store.

[... 187 words]

PostgreSQL 8.3 vs. 8.2—a simple benchmark. Stefan Kaltenbrunner reports a 2.2x speed increase for PostgreSQL 8.3 compared to 8.2 for a relatively simple benchmark. # 12th December 2007, 12:42 am

When should one switch from MySQL to Oracle or PostgreSQL?

When your own benchmarks prove that your application’s particular load characteristics will perform better on another database—and the difference is large enough that it’s worth the cost involved in retargeting your code. If that cost is high (and it probably will be) it may be worth paying for some expert consultants to ensure that your implementations against the different databases are properly optimised.

[... 102 words]

PostgreSQL for Mac (via) Looks like a great way of getting PostgreSQL up and running on a Mac. # 10th July 2007, 8:24 am

PostgreSQL 10 Released. Highlights include major improvements to parallelized queries, quorum commit for synchronous replication (sounds reminiscent of Cassandra) and logical replication, which allows modifications to specific tables to be replicated to different clusters. They’re also changing their versioning scheme to Major.Minor, so the next minor release will be 10.1 and the next major release will be 11. # 5th October 2017, 3:31 pm

PostgreSQL Development Priorities. The top two for 8.4 are “Simple built-in replication” and “Upgrade-in-place”, Josh Berkus is seeking feedback on priorities for future work on 8.5. # 28th May 2009, 8:08 pm

Install Django, GeoDjango, PostgreSQL and PostGIS on OSX Leopard. This tutorial worked perfectly for me. # 24th July 2009, 11:47 am

PostgreSQL 8.3 beta 4 release notes. In addition to the huge speed improvements, 8.3 adds support for XML, UUID and ENUM data types and brings full text (tsearch2) in to the core database engine. # 12th December 2007, 12:43 am

PostgreSQL 8.5alpha3 now available. “Hot Standby, allowing read-only connections during recovery, provides a built-in master-slave replication solution.” Woohoo! # 23rd December 2009, 9:57 am

Mandelbrot set in PostgreSQL. Surprisingly short SQL statement that produces an ASCII art Mandelbrot set. # 13th August 2009, 2:23 pm

Historically the project policy has been to avoid putting replication into core PostgreSQL, so as to leave room for development of competing solutions [...] However, it is becoming clear that this policy is hindering acceptance of PostgreSQL to too great an extent, compared to the benefit it offers to the add-on replication projects. Users who might consider PostgreSQL are choosing other database systems because our existing replication options are too complex to install and use for simple cases.

Tom Lane # 7th July 2008, 2:08 pm

Why is postgresql popular with django?

Partly because the first applications developed on Django (before it was even called Django) used PostgreSQL, so it’s had great PostgreSQL support baked in from the start.

[... 74 words]

PostgreSQL 7.3

PostgreSQL Global Development Group Announces Version 7.3. I still haven’t had a play with PostgreSQL yet but everything I’ve heard has been positive. I’m sure PostgreSQL’s popularity would skyrocket if they made a Windows binary available—the ability to test scripts written in PHP and MySQL on a Windows desktop PC is the main reason I have stuck with MySQL rather than exploring PostgreSQL.

[... 66 words]

PostgreSQL 7.4

Last week’s release of PostgreSQL 7.4 made a great open source project even better—it even managed to impress hard-core MySQL advocate Jeremy Zawodny. The detailed release notes show that most of the improvements were with regards to performance, but the thing that really caught my eye was tsearch2, the new full text indexing suite. A bit of digging brought up the CVS tree for the new module, which in turn lead me to this tutorial style overview of its capabilities.

[... 132 words]

SQL Fiddle demonstrating the PostgreSQL to_tsvector() function (via) SQL Fiddle is amazing—it’s an interactive pastebin that lets you execute queries against MySQL, PostgreSQL, Oracle, SQLite & SQL Server, and then share both the input and the results by sending around the resulting URL. Here I’m using it to demonstrate that stripping tags before indexing documents in PostgreSQL is unnecessary because the ts_vector() function already does that for you. # 6th October 2017, 10:11 pm

Performance Tuning PostgreSQL (via) Anything Frank has to say about PostgreSQL is well worth listening to. # 13th December 2005, 8:11 am

PostgreSQL Performance Optimisation

Via the pgsql-performance mailing list, a great guide to Tuning PostgreSQL for performance, accompanied by a huge table of annotated configuration options.

[... 40 words]

Fixing sequence problems in PostgreSQL

This one’s mainly for my own future reference. The following error message in PostgreSQL:

[... 77 words]

PostgreSQL 9.0 Beta 1 Now Available. With asynchronous streaming replication. # 5th May 2010, 2:36 pm

PostgreSQL 8.0.0 Released. Includes a native Win32 port for the first time! # 19th January 2005, 4:06 pm

PostgreSQL 8.0.0 Officially Goes Beta (via) New features include win32 support, point-in-time recovery. # 10th August 2004, 6:17 pm

PostgreSQL: What’s New in 8.0. Win32 server, savepoints, tablespaces, plperl and more. # 19th January 2005, 4:11 pm

Finding open locks on Postgresql. May come in useful in the future. # 26th February 2004, 2:33 am

PostgreSQL startup items for MacOS X (via) Thank you! I’ve been wanting this for months. # 8th September 2004, 6:06 pm

Doing things with Ordnance Survey OpenData. Jo Walsh’s guide to processing Ordnance Survey OpenData using PostgreSQL and PostGIS. # 20th May 2010, 3:22 pm