# gin Published articles for gin. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## What is index overhead on writes? DevFeed: [What is index overhead on writes?]() Original publisher: [Read original article]() Author: depesz Published: 2026-01-06T11:57:10Z Content type: article Language: en Sources: [select \* from depesz;]() Topics: [data]() Tags: [benchmark](), [btree](), [delete](), [gin](), [index](), [insert](), [operations](), [overhead](), [performance](), [postgresql](), [speed](), [tutorial](), [uncategorized](), [update]() ### AI overview The article measures how indexes affect write performance using PostgreSQL 18 and a one-million-row test table. It reports that loading performance decreases as indexes are added, and that a roughly 3.6-fold increase in storage corresponded to an eightfold slowdown in the tested case. A single wide index performed better than ten separate indexes, though the author notes that the configurations solve different computational problems. ### Source excerpt One of things people learn is that adding indexes isn't free. All write operations (insert, update, delete) will be slower - well, they have to update index. But realistically - how much slower? Full tests should involve lots of operations, on realistic data, but I just wanted to see some basic info. So I figured ... Continue reading "What is index overhead on writes?" ## Do you really need tsvector column? DevFeed: [Do you really need tsvector column?]() Original publisher: [Read original article]() Author: depesz Published: 2025-11-03T12:34:33Z Content type: article Language: en Sources: [select \* from depesz;]() Topics: [data](), [Query (disambiguation)](), [Algorithm]() Tags: [algorithm](), [benchmark](), [comparison](), [data](), [function](), [gin](), [gist](), [index](), [postgresql](), [query](), [tsearch](), [tsvector](), [uncategorized]() ### AI overview This article tests whether a materialized tsvector column is necessary for full-text search. Using a large Wikipedia-derived dataset, it compares a tsvector column with an expression-based function index and reports that the tested query took about 4.5 minutes with the materialized column versus almost an hour with the function-based index. ### Source excerpt When using tsearch one usually, often, creates a tsvector column to put data in, and then create index on it. But, do you really need the index? I wrote once already that you don't have to, but then a person talked with me on IRC, and pointed this section of docs: One advantage of the ... Continue reading "Do you really need tsvector column?" ## Postgres Datatypes - The ones you're not using. DevFeed: [Postgres Datatypes - The ones you're not using.]() Original publisher: [Read original article]() Author: Map Published: 2014-05-07T20:55:56Z Content type: article Language: en Sources: [Craig Kerstiens]() Topics: [Databases](), [timezone](), [DateTime](), [Gin](), [Extension]() Tags: [extension](), [gin](), [postgres](), [postgresql](), [timestamptz](), [timezone](), [uuid]() ### AI overview This article discusses less commonly used Postgres data types, including hstore, range types, timestamp with timezone, and UUIDs. It describes practical uses such as storing key-value data, constraining ranges, handling time zones, and supporting scalable identifiers. ### Source excerpt Postgres has a variety of datatypes, in fact quite a few more than most other databases. Most commonly applications take advantage of the standard ones - integers, text, numeric, etc. Almost every application needs these basic types, the rarer ones may be needed less frequently. And while not needed on every application when you do need them they can be an extremely handy. So without further ado let's look at some of these rarer but awesome types. hstore Yes, I've talked about this one before, yet still not enough people are using it. Of this list of datatypes this is one that could also have benefit for most if not all applications. Hstore is a key-value store directly within Postgres. This means you can easily add new keys and values (optionally), without haveing to run a migration to setup new columns. Further you can still get great performance by using Gin and GiST indexes with them, which automatically index all keys and values for hstore. It's of note that hstore is an extension and not enabled by default. If you want the ins and outs of getting hands on with it, give the article on Postgres Guide a read. Range types If there is ever a time where you have two columns in your database with one being a from, another being a to, you probably want to be using range types. Range types are just that a set of ranges. A super common use of them is when doing anything with calendaring. The place where they really become useful is in their ability to apply constraints on those ranges. This means you can make sure you don't have overlapping time issues, and don't have to rebuild heavy application logic to accomplish it. Timestamp with Timezone Timestamps are annoying, plain and simple. If you've re-invented handling different timezones within your application you've wasted plenty of time and likely done it wrong. If you're using plain timestamps within your application further there's a good chance they dont even mean what you think they mean. Timestamps with timezone o ## Arrays in Postgres DevFeed: [Arrays in Postgres]() Original publisher: [Read original article]() Author: Map Published: 2012-08-20T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [data](), [Data structures](), [App]() Tags: [application](), [arrays](), [development](), [gin](), [gist](), [indexes](), [postgres](), [query]() ### AI overview This tutorial explains how PostgreSQL arrays can store multiple values in a single column or row. It discusses modeling data with arrays, gives purchase and tagging examples, and notes that GIN and GiST indexes can support searches for array contents. ### Source excerpt Postgres out of the box has an abundance of datatypes, from standard numeric datatypes to geometric or even network datatypes. With extensions you can get even more out of it as earlier discussed with hStore. Though with all of the datatypes its easy to miss out on some of them that are there, in fact one of my favorites is often missed entirely. The Array datatype lets you do just as you'd expect, store an array inside Postgres. With this you can often get some of the functionality you'd want in a single table when you might traditionally have expanded to multiple tables. The broader question may be why you'd actually want to use an array. One good reason may be if you're an application developer its how you think of your data, so why not model it the same way. As you'll see below it can be easier than joining and aggregating across a set of rows. Also depending on your case you performance could be improved, though mileage may vary here as it does depend on the data you're storing. First a bit of a hacky example... Lets say you have a basic website that sells stuff, and instead of having a purchase ID and a total you want to include the quantity, id, and price of each item in a single row. With a bit of a messy foreign key (using a decimal) you could store all of this within a single row: CREATE TABLE purchases ( id integer NOT NULL, user_id integer, items decimal(10,2) [100][1], occurred_at timestamp ); With this table I could have an array that holds multiple records of: The item purchased The quantity The price An insert to this table would look something like: INSERT INTO purchases VALUES (1, 37, '\{\{15.0, 1.0, 25.0\}, \{15.0, 1.0, 25.0\}\}', now()); INSERT INTO purchases VALUES (2, 2, '{{11.0, 1.0, 4.99}}', now()); You can see a full example with UDF's to compute total here A more practical example may actually be using an array for tags. If you were to tag your purchases: CREATE TABLE products ( id integer NOT NULL, title character varying(255), description t