Simon Willison’s Weblog

“postgresql” in items


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: 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.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 for Mac (via) Looks like a great way of getting PostgreSQL up and running on a Mac. # 10th July 2007, 8:24 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 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

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

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

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

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

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]

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

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]

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]

Showdown: MySQL 8 vs PostgreSQL 10 (via) MySQL 8 makes comparisons between PostgreSQL and MySQL far more interesting, as it closes some of the key feature gaps. Meanwhile the PostgreSQL replication story (long one of MySQL’s key advantages) has improved dramatically in recent versions. This article offers a useful overview of the current differences, including diving into some of the less obvious implementation details that differ between the two. # 23rd May 2018, 5:02 pm

Cloud SQL for PostgreSQL adds high availability and replication. Google Cloud Platform now offers PostgreSQL with automatic asynchronous disk-level replication to a separate instance in a different availability zone, via their new “Regional Disks“ feature. Between this, Heroku, Citus and Amazon RDS the appeal of a self-maintained PostgreSQL instance continues to fall. # 7th November 2017, 1:49 pm

How a single PostgreSQL config change improved slow query performance by 50x. “If you are using SSDs and running PostgreSQL with default configuration, I encourage you to try tuning random_page_cost & seq_page_cost. You might be surprised by some huge performance improvements.” # 23rd November 2017, 8:11 pm

Benefit of TEXT with CHECK over VARCHAR(X) in PostgreSQL. Brandur suggests using “email TEXT CHECK (char_length(email) <= 255)” to define a column with a length limit in PostgreSQL over “VARCHAR(255)” because TEXT and VARCHAR are equally performant but a CHECK length can be changed later on without locking the table, whereas a VARCHAR requires an ALTER TABLE with an exclusive lock. # 28th October 2017, 12:59 am

PostgreSQL Exercises. Excellent set of PostgreSQL exercises by Alisdair Owens, each with an interactive editor that lets you run your queries against a real database. Starts with the basics, but also covers advanced topics like recursive queries and window aggregate functions. # 6th December 2017, 4:20 pm

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 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