Saturday, August 18

Tag: GIN

On the usefulness of expression indexes

2ndQuadrant, PostgreSQL, Tomas' PlanetPostgreSQL
When teaching PostgreSQL trainings, both on basics and advanced topics, I often find out the attendees have very little idea how powerful the expression indexes may be (if they are aware of them at all). So let me give you a brief overview. So, let's say we have a table, with a range of timestamps (yes, we have generate_series function that can generate dates): CREATE TABLE t AS SELECT d, repeat(md5(d::text), 10) AS padding FROM generate_series(timestamp '1900-01-01', timestamp '2100-01-01', interval '1 day') s(d); VACUUM ANALYZE t; The table also includes a padding column, to make it a bit larger. Now, let's do a simple range query, selecting just one month from the ~200 years included in the table. If you do explain on the (more…)
NoSQL with PostgreSQL 9.4 and JSONB

NoSQL with PostgreSQL 9.4 and JSONB

Giuseppe's PlanetPostgreSQL
The introduction of the JSONB data type in PostgreSQL, definitely makes the “NoSQL” side of this relational DBMS come out: this introduction meets the requirements of all those who prefer a data structure in a “key-value” array, dictionary style (widely used in the field of development) and, at the same time, ensures all the advantages of a relational database. (more…)