116 items tagged “postgresql”
2017
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
Implementing Stripe-like Idempotency Keys in Postgres (via) Having clients send “idempotency keys” with API requests in order to be able to safely retry them if something’s goes wrong is a really neat trick for making transactional APIs more robust. Here Brandur Leach talks implementation strategies. # 27th October 2017, 5:51 pm
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 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
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:
[... 3103 words]2013
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]NoSQL: What is the “best” solution for storing high volumes of structured data?
On the right setup, PostgreSQL can handle petabytes. There are also commercial vendors such as Greenplum that offer data warehouse solutions built on a modified version of PostgreSQL.
[... 80 words]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]2012
What tools and techniques are used for relational database version control (structure and data)?
The term you are looking for is database migrations (sometimes called database change scripts).
[... 308 words]2010
PostgreSQL: 5 Minutes to Binary Replication. The missing manual. # 14th October 2010, 8:23 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]MapOSMatic. Clever service built on top of OpenStreetMap, which renders double sided city maps with a map and grid on one size and an A-Z street name index on the other. Runs on top of Mapnik, PostGIS and Cairo, with a few thousand additional lines of Python and Django. # 11th July 2010, 12:15 pm
Appending the request URL to SQL statements in Django. A clever frame-walking monkey-patch which pulls the most recent HttpRequest object out of the Python stack and adds the current request.path to each SQL query as an SQL comment, so you can see it in debugging tools such as slow query logs and the PostgreSQL “select * from pg_stat_activity” query. # 2nd June 2010, 9:09 am
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
Installing GeoDjango Dependencies with Homebrew. brew update && brew install postgis && brew install gdal # 7th May 2010, 2:40 pm
PostgreSQL 9.0 Beta 1 Now Available. With asynchronous streaming replication. # 5th May 2010, 2:36 pm
grant XXX on * ? (via) PostgreSQL doesn’t have a way to say “this user is allowed to select/update/etc on all tables in database X”. That kind of sucks. UPDATE: This is fixed in PostgreSQL 9, see the comments. # 16th March 2010, 6:26 pm
jacobian’s django-deployment-workshop. Notes and resources from Jacob’s 3 hour Django deployment workshop at PyCon, including example configuration files for Apache2 + mod_wsgi, nginx, PostgreSQL and pgpool. # 19th February 2010, 2:28 pm
2009
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
Django-Jython 1.0.0 released! Now with database backends for PostgreSQL, Oracle and MySQL. The next release (planned for next month) should provide full compatibility with Django 1.1—the current release has 1.1 support for PostgreSQL but only 1.0 support for the other two databases. # 9th November 2009, 1:53 pm
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
Londiste Tutorial. Master/slave replication for PostgreSQL, developed and used by Skype. # 9th September 2009, 11:06 pm
Mandelbrot set in PostgreSQL. Surprisingly short SQL statement that produces an ASCII art Mandelbrot set. # 13th August 2009, 2:23 pm
Install Django, GeoDjango, PostgreSQL and PostGIS on OSX Leopard. This tutorial worked perfectly for me. # 24th July 2009, 11:47 am
EveryBlock source code released. EveryBlock’s Knight Foundation grant required them to release the source code after two years, under the GPL. Lots of neat Django / PostgreSQL / GIS tricks to be found within. # 1st July 2009, 8:01 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
What happened to Hot Standby? Hot Standby (the ability to have read-only replication slaves) has been dropped from PostgreSQL 8.4 and is now scheduled for 8.5. “Making hard decisions to postpone features which aren’t quite ready is how PostgreSQL makes sure that our DBMS is ”bulletproof“ and that we release close to on-time every year”. # 8th March 2009, 9:28 am
The Django and Ubuntu Intrepid Almanac. Will Larson’s impressively comprehensive guide to configuring and securing an Ubuntu VPS from scratch to run Django, using PostgreSQL and Apache/mod_wsgi behind nginx. # 14th February 2009, 3:42 pm
2008
Represent. Andrei Scheinkman and Derek Willis describe how they built the NYTimes Represent feature using GeoDjango and PostGIS. # 29th December 2008, 10:10 pm
bpgsql. Barry Pederson’s pure Python PostgreSQL client library now ships with a Django backend. # 23rd September 2008, 11:42 am