class passlib. This function is safe against timing attacks. We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. That is still ongoing, but let's focus on some more fun things. This setting specifies how much memory PostgreSQL can use to cache data, which improves performance. It puts its changes into a new file at the very bottom. You shouldn't build SQL by putting your variables directly via string concatenation. Work memory and maintenance work memory. 6, only hash joins and nested loops can be performed in the parallel portion of a plan. Add hash partitioning. This prevents the scenario of someone altering data and also changing the hash to match. Once the parent table has been created, it is already time to create the partitions. If you're not sure which to choose, learn more about installing packages. When inserting into the index, let's compute the hash function for the key. Postgres 10 came with RANGE and LIST type partitions. For these reasons, hash index use is presently discouraged. If no maxchars is specified ST_GeoHash returns a GeoHash based on full precision of the input geometry type. That is the warning in the current version of Postgres about hash indexes. Creating Roles. Seeing the impact of the change using Datadog allowed us to instantly validate that altering that part of the query was the right thing to do. The previous examples show how a B-Tree index works 9. By default, Postgres-XL always try to create a table distributed by HASH. sql from an instance to create hash index on a very big partitioned table on Postgres 11. The string argument is the string of which the MD5 hash is calculated. With this discovery, the next step was to figure out why the performance of these queries differed by so much. One great method with psexec in metasploit is it allows you to enter the password itself, or you can simply just specify the hash values, no need to crack to gain access to the system. It is a powerful, open source object-relational database system. h" PG_MODULE_MAGIC; /* Hash table tester. 6 and 10 can use all three join strategies in parallel query plans, but they can only use a partial plan on the outer side of the join. PostgreSQL only has support for MD5 hashing and we all know how reliable that can be. 7 Quorum-base synchronous replication). query_store. (CVE-2019-10208) Andreas Seltenreich discovered that PostgreSQL did not properly handle user-defined hash equality operators. The number of buckets initially equals two and dynamically increases to adjust to the data size. … The G-I-S-T or GIST indexes … is a generalized search tree. 5 I consistently see HashJoin plans that hash the large table, and scan the small table. When Postgres receives a query, the first thing it does is try to optimize how the query will be executed based on its knowledge of the table structure, size, and indices. We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. Stable hash functions for Postgres. PostgreSQL 11 also added hash partitioning. There are many databases that support the use of SQL to access their data, among them MySQL and PostgreSQL. It has strong support from the community and is being actively developed with a new release every year. conf) manages the configuration of the database server. Usually hash partitioning is used when you do not have a natural way of partitioning your data or you want to evenly distribute the data based on hash. If present, it will use the primary key, or a unique constraint otherwise. PostgreSQL and MySQL roles store their text passwords as hashes. By Josh Tolley March 26, 2009 A few days ago a patch was committed to improve PostgreSQL's performance when hash joining tables too large to fit into memory. Special Character Symbols. Storing Passwords Securely With PostgreSQL and Pgcrypto. PostgreSQL supports basic table partitioning. An index creates an entry for each value that appears in the indexed columns. Sort operations are defined by ORDER BY , DISTINCT , and merge joins. h" PG_MODULE_MAGIC; /* Hash table tester. This allows the verify function to verify the hash without needing separate storage for the salt or algorithm information. The immediate question is how do they perform as compared to Btree indexes. B-trees can handle equality and range. MERGE JOIN , in our case, is quite efficient, since the indexes are read first and the actual records are only looked up for the matched records (which are not too numerous). The sequential scan on a large table contributed to most of the query time. How to do it…. Continue reading Using PostgreSQL in R: A quick how-to The combination of R plus SQL offers an attractive way to work with what we call medium-scale data: data that's perhaps too large to gracefully work with in its entirety within your favorite desktop analysis tool (whether that be R or Excel), but too small to justify the overhead of big. Throughout this post, I will present most of the queries on Postgres 10 on Ubuntu 16. The dissector_postgresql function in dissectors/ec_postgresql. I've been absent for quite a while. The interface for this class is based on libpq, the C application programmer's interface to PostgreSQL. In PostgreSQL 10, the hash index was WAL logged, that means, it's CRASH safe and can be replicated. It uses default values of the parameters, but we can change these values to better reflect workload and operating environment. PostgreSQL is an advanced object-relational database management system that supports an extended subset of the SQL standard, including transactions, foreign keys, subqueries, triggers, user-defined types and functions. For many SQL programmers, occasional bouts with long-running queries and poor performance are simply par for the course. We tend to keep things small and simple. For many SQL programmers, occasional bouts with long-running queries and poor performance are simply par for the course. What happens here is that with 11, your SQL becomes: set last=11 Which is valid SQL (using 11 as a integer literal), while with xx it becomes: set last=xx There are no quotes, so the SQL means you're. PostgreSQL supports the most advanced features included in SQL standards. In 11, we have HASH type partitions also. ALL CAPS SQL. No more shoehorning writeable common table expressions. The PostgreSQL relational database system is a powerful, scalable, and standards-compliant open-source database platform. Technically, these generated ids cannot be called hashes since a cryptographic hash has one-way mapping (cannot be decrypted). In particular, setting up indexes of the hash, GIN and BRIN type might just be the solution to your performance issues:. This will serve as a starting point and building block for the remainder of our Become a PostgreSQL DBA blog series. Each of these indexes is useful, but which to use depends on the data type, underlying data, and types of lookups performed. Storing Passwords Securely With PostgreSQL and Pgcrypto. 2 in the following examples. If you want to go purely open source, your options basically are: * Shard it yourself at the. MariaDB is an open source database that supports continuity and open collaboration. Since you haven't specified your PostgreSQL version I'll assume you're using the current 9. Filesystem choice matters. The home lab automation has taken a back seat to unpacking and setting up IKEA flat-pack furniture. We're the creators of MongoDB, the most popular database for modern apps, and MongoDB Atlas, the global cloud database on AWS, Azure, and GCP. By default, Postgres-XL always try to create a table distributed by HASH. When inserting into the index, let's compute the hash function for the key. Of this new concatenated VARBINARY value SQL Server calculates the SHA_512 hash. What I am looking for a good hashing algorithm that has very low Collision and can generate a hash in Int or. PostgreSQL 9. Seeing the impact of the change using Datadog allowed us to instantly validate that altering that part of the query was the right thing to do. This article will explain you to salt hash passwords using Node. hash module contains all the password hash algorithms built into Passlib. - table_hash. There's a download link available for any file or directory, if you only want to download a portion of the phpMyFAQ code. 6, only hash joins and nested loops can be performed in the parallel portion of a plan. Here i provide a sample to demonstrate how to partition table in PostgreSQL. No more shoehorning writeable common table expressions. reads -- ~250,000 rows CREATE TABLE re. DBD::Pg, like PostgreSQL, allows the same number to be used more than once in the query. Fourth, list one or more columns that to be stored in the index. What is the optimal data type for an MD5 field? Each row has a text field representation and a unique key that is an MD5 hash of that PostgreSQL also accepts. PostgreSQL supports basic table partitioning. 6, only hash joins and nested loops can be performed in the parallel portion of a plan. This article will explain you to salt hash passwords using Node. This will serve as a starting point and building block for the remainder of our Become a PostgreSQL DBA blog series. org is the Ruby community’s gem hosting service. So how can I hash passwords (with some salts) in postgr. These tables store a mapping between the hash of a password, and the correct password for that hash. Functions allow database reuse as other applications can interact directly with your stored procedures instead of a middle-tier or duplicating code. Recent Releases PostgreSQL_Anonymizer 0. • Provided HASH partition as a partitioning method • Create PRIMARY KEY / UNIQUE KEY for the partitioned table. Parameter type is same as in encode. Use a trigger to set the hash column on insert and update. All source code is kept under Git revision control, which you can browse online. Many people doesn't know about passwords stored in database What's the right and safe way to do? OK! In this short presentation teaches about this. In addition to basic SQL operations, the API also supports prepared statements. The hstore data type from PostgreSQL allows for the storing of key/value pairs or simply a hash within a single column. 6, only hash joins and nested loops can be performed in the parallel portion of a plan. Before proceed, please understand some basic concept like,er… better i provide a concept of partition “time. Each index type uses a different storage structure and algorithm to cope with different kinds of queries. A Tour of Postgres Index Types Knowing the various index types available to you in Postgres is key when deciding which one to use. Hashing a String to a Numeric Value in PostgreSQL. A buffer table can be logically divided into three parts: a hash function, hash bucket slots, and data entries (Fig. I know that there is an existing MD5 function, but it is not an aggregate. I am using psql -f file. Now, with the next version of PostgreSQL, they will be durable. Django uses PostgreSQL’s SERIAL data type to store auto-incrementing primary keys. 4 has a slew of built-in features that allow you to improve both performance and systems administration. My tweet announcing this post resulted in some interesting discussion about how Postgres handles hash joins (including the painful discovery that Oracle and Postgres present the build and hash tables the opposite way up in execution plans). This makes the index entries smaller (perhaps very substantially so) and speeds up various operations. This version of Aurora PostgreSQL is compatible with PostgreSQL 10. 2 in the following examples. Parameter type is same as in encode. If two queries are identical except for literal values, they will have the same hash. "Index only scans" were launched with Postgres 9. Postgres has a number of index types. The ASC and DESC specify the sort order. 3 An extension adding hypothetical indexes in PostgreSQL. MERGE JOIN , in our case, is quite efficient, since the indexes are read first and the actual records are only looked up for the matched records (which are not too numerous). For these reasons, hash index use is presently discouraged. So, I fired up Google and set out to figure out the best way to do it. Hash aggregate is a node type that requires an aggregate operator, and a group key column. Citus is an extension to Postgres that makes easy for you to shard your data and allow you to continue to scale out memory or processing power. How do I decrypt the password of a user? What is the command used for decrypting the encrypted password to normal one? After entering into postgreSQL database and typing:. When we do this, PostgreSQL scans the entire table, computes the hash and stores it in the index. If you want to go purely open source, your options basically are: * Shard it yourself at the. ASC is the default. YAML generators use this technique to correctly serialize repeated references to the same object, and even cyclic references. I have 3 tables which I wish to join using inner joins in Postgres 9. x that are currently supported by PostgreSQL on Amazon RDS. The home lab automation has taken a back seat to unpacking and setting up IKEA flat-pack furniture. With PyCharm, you can access the command line, connect to a database, create a virtual environment, and manage your version control system all in one place, saving time by avoiding constantly switching between windows. Safety in Numbers - feeds. For SHA-256, use the pgcrypto extension module's digest function. The takeaway. We expect everyone who participates to conduct themselves in a professional manner, acting with common courtesy and in the common interest, with respect for all of our community. Many people doesn't know about passwords stored in database What's the right and safe way to do? OK! In this short presentation teaches about this. In PostgreSQL hash partitioning might seem a bit strange in the beginning because you define a modulus and a remainder and that defines where the data is going to. In general, we see this node type being utilized during the GROUP BY , DISTINCT , or set operations. What I have effectively created is a (database) table of hash tables. A Tour of Postgres Index Types Knowing the various index types available to you in Postgres is key when deciding which one to use. In this post we're going to take a closer look at two of those extensions, Postgres's earthdistance and the third party PostGIS. This PostgreSQL tutorial explains how to create, drop, and rename indexes in PostgreSQL with syntax and examples. Below is an abbreviated schema for each table. Hash indexes have been available in PostgreSQL for a long time, but they suffer from a number of data integrity issues in older versions. LEARNING WITH lynda. The main author of this feature wrote a great article: Parallel Hash for PostgreSQL. Hash functions in PostgreSQL always return the «integer» type, which is in range of 2 32  ≈ 4 billion values. There is a lot of work done in the coming version to make them faster. … GIST, SP-GIST, GIN, and BRIN indexes. If this is the case, configure this trigger to fire on replica too:. 5 and lower, each property was represented with a separate field of the «pg_am» table. The clog is small and never has any reason to become bloated, so you should never have any reason to touch it. Now, with the next version of PostgreSQL, they will be durable. PostgreSQL Architecture. PostgreSQL and MySQL roles store their text passwords as hashes. PostgreSQL + PowerShell 14 January 2016. To do that, the PARTITION OF clause has been added. DESCRIPTION. Similar to a hash join, a merge join only works on joins where the join condition is an equality filter. The MD5 hash can not be decrypted if the text you entered is complicated enough. In PostgreSQL 10, the hash index was WAL logged, that means, it's CRASH safe and can be replicated. Using PostgreSQL and jsonb with Ruby on Rails. This Postgres module introduces a new data type hll which is a HyperLogLog data structure. Instantly publish your gems and then install them. "select * from table" would return result set in unexpected order, so that Data Transfer and Export functions would return incorrect result. PostgreSQL 12 changes things so this meta-data loading is performed after partition pruning. Even though both tables have Indexes, PostgreSQL decided to do a Hash Join with a sequential scan on the large table. Hash indexes are unrelated. You can also create sub-partitions on child tables too! Let's take an example to partition the table using RANGE and LIST partition types. The PostgreSQL MD5() function calculates the MD5 hash of a string and returns the result in hexadecimal. In the documentation we can read The table is partitioned by specifying a modulus and a remainder for each partition. In addition to basic SQL operations, the API also supports prepared statements. PostgreSQL 9. 6) Still with version 9. You can have a partitioned table on either side of a foreign key constraint, and everything will work correctly. (1 reply) I am trying to get Postgres to use SHA as the password hash algorithm instead of MD5. 5 ships with a variety of index types that use distinct algorithms to speed up different queries. No ads, nonsense or garbage, just a Postgres pass generator. In this section, we are going to look at the various additions made to the PostgreSQL database adapter. That is still ongoing, but let's focus on some more fun things. It seems like it might be a waste to use 64 bits for a 32-bit value, but I couldn't think of anything better than a BIGINT. Even though both tables have Indexes, PostgreSQL decided to do a Hash Join with a sequential scan on the large table. In Redshift Redshift supports the handy strtol function, making our hash-string-to-integer conversion a bit easier. Note: Testing has shown PostgreSQL's hash indexes to be similar or slower than B-tree indexes, and the index size and build time for hash indexes is much worse. The sequential scan on a large table contributed to most of the query time. So if you're just giving everything a unique primary key, then thats the hash. A Tour of Postgres Index Types Knowing the various index types available to you in Postgres is key when deciding which one to use. How To Change Password Hash Algorithm From MD5 to SHA-256. PostgreSQL functions, also known as Stored Procedures, allow you to carry out operations that would normally take several queries and round trips in a single function within the database. The takeaway. To illustrate how the different geolocation modules and methods work, I'm. Throughout this post, I will present most of the queries on Postgres 10 on Ubuntu 16. Therefore, all information that's needed to verify the hash is included in it. There just isn't a good way to perform a hash join on disk. DynamoDB to Postgres. PostgreSQL is developed by the PostgreSQL Global Development Group. pgDash shows you information and metrics about every aspect of your PostgreSQL database server, collected using the open-source tool pgmetrics. hash keys are required and range keys are not. A Tour of Postgres Index Types Knowing the various index types available to you in Postgres is key when deciding which one to use. When Postgres receives a query, the first thing it does is try to optimize how the query will be executed based on its knowledge of the table structure, size, and indices. Press question mark to learn the rest of the keyboard shortcuts. ALL CAPS SQL. js and Bcrypt The bcrypt library on NPM makes it really easy to hash and compare passwords in Node. Tree hashes are more flexible than conventional hash functions, and Bao can verify small pieces of a file independently rather than just the whole thing at once. h" #include "utils/hsearch. What I am looking for a good hashing algorithm that has very low Collision and can generate a hash in Int or. Use the value that is output by this command in the next step as the value of POSTGRESQL_PASSWORD_HASH. This allowed it to save the entire data set into a single, in-memory hash table and avoid using temporary buffer files. DBD::Pg is a Perl module that works with the DBI module to provide access to PostgreSQL databases. About Recipes¶ [edit on GitHub] A recipe is the most fundamental configuration element within the organization. Each row in the "people" table will have its own hash table, with any keys and values. The Citus extension to Postgres distributes data & queries across nodes so your database can scale and queries are fast. pg_pathman uses pathman_ddl_trigger event trigger to remove the record about dropped table from pathman_config, and this trigger by default won't fire on replica, leading to inconsistent state when pg_pathman thinks that the table still exists, but in fact it doesn't. Cracking Postgres Password Hashes with MDCrack As far as I'm aware there are aren't many good password crackers around for PostgreSQL database password hashes. Encryption methods such as AES are two-way —they can be decrypted— while hash-based encryption methods such as MD5 are one-way. If present, it will use the primary key, or a unique constraint otherwise. Explaining the Postgres Query Optimizer BRUCE MOMJIAN The optimizer is the "brain" of the database, interpreting SQL queries and determining the fastest method of execution. In PostgreSQL hash partitioning might seem a bit strange in the beginning because you define a modulus and a remainder and that defines where the data is going to. There are many databases that support the use of SQL to access their data, among them MySQL and PostgreSQL. There's no automatic rehash (there can't be, since PostgreSQL doesn't store the cleartext of the old password). The PostgreSQL connection class. Oracle has offered a similar type of functionality where its function-based indexes can be used for pl/sql based functions instead of just the standard pre-defined system. PostgreSQL 12 changes things so this meta-data loading is performed after partition pruning. Also if you are looking for a way to reduce collisions and still keep the hash result small (smaller than say md5) you could get a nice database friendly 64 bit value by using hash/crc32 and hash/crc32b, which is slower than a single md5 but the result may be more suitable for certain tasks. The B-Tree type is great for general purpose indexes but there are special cases when other types provide better results. The other major case when a hash join won't be the preferred join algorithm is when Postgres thinks the hash table needed for the hash join won't fit in memory 1. 4 has a slew of built-in features that allow you to improve both performance and systems administration. This module implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. Note: Testing has shown PostgreSQL's hash indexes to be similar or slower than B-tree indexes, and the index size and build time for hash indexes is much worse. What I am looking for a good hashing algorithm that has very low Collision and can generate a hash in Int or. A point to remember is - "The fewer the hash functions, the more the chances of collisions. PostgreSQL: PostgreSQL includes built-in support for regular B-tree and hash indexes. 2/37 One fine day early in the morning You are woken up by SMS Bz-z-z! Something is wrong with your live system. The second type of placeholder is "dollar sign numbers". Some familiarity with libpq is recommended, but not necessary. 867 ms It's the same with hash index test after that. According to PostgreSQL documentation it's only range and list partitions are supported. If no maxchars is specified ST_GeoHash returns a GeoHash based on full precision of the input geometry type. This setting specifies the amount of RAM that can be used by internal sort operations and hash tables before writing to temporary disk files. We finally have the upsert feature we've been waiting for. To our surprise, there were no interesting results (at the time we tried) for PostgreSQL in combination to pass-the-hash. patch) at 2017-11-01 10:16:27 from amul sul (Patch: Yes) Attachment (0001-provariadic-sanity-check. An index is a performance-tuning method of allowing faster retrieval of records. Manually assigning a value to an auto-incrementing field doesn’t update the field’s sequence, which might later cause a conflict. … GIST, SP-GIST, GIN, and BRIN indexes. I decided to seize the moment and launched myself into an exploration of what the template databases are meant to do, and why the heck there are two of. In addition to basic SQL operations, the API also supports prepared statements. Attempts to fingerprint a remote PostgreSQL instance, inferring version number from the failed authentication messages or simply returning the result of “select version()” if authentication was successful. For many SQL programmers, occasional bouts with long-running queries and poor performance are simply par for the course. You can also decode those ids back. The takeaway. An attacker could use this to execute arbitrary SQL with the permissions of the function owner. com CONTENT Business Creative Technology Hash index example plan. In this post, I will look at some examples of features introduced in the newest version of Postgres, which was released on 5 October. 31, The Mac Is Not a Typewriter. Even though the number of hash bucket slots is greater than the number of the buffer pool slots, collisions may occur. SF PGDAY 20151 / 50. PostgreSQL starts by scanning the tiny (1000 rows) taxonomy_term_data table and constructing an in-memory. It seems like it might be a waste to use 64 bits for a 32-bit value, but I couldn't think of anything better than a BIGINT. I am trying to get Postgres to use SHA as the password hash algorithm instead of MD5. For more information about the improvements in release 10. So PostgresQL, for instance, stores the actual plaintext password (or an encrypted, but not hashed) password? And compares that with the hash that it receives from libpq. Usually hash partitioning is used when you do not have a natural way of partitioning your data or you want to evenly distribute the data based on hash. A Postgres INT is the correct size, but it is signed, which means we have to use the next larger type, BIGINT. Postgres-XC allows a table of no columns to be created (for example, CREATE TABLE foo();). Cracking Postgres Password Hashes with MDCrack As far as I’m aware there are aren’t many good password crackers around for PostgreSQL database password hashes. Here are just a few of the organizations that choose Vagrant to automate their development environments, in lightweight and reproducible ways. Postgres has support for universally unique identifiers (UUIDs) as a column data type via uuid. I have 3 tables which I wish to join using inner joins in Postgres 9. Useful, free online tool that generates a PostgreSQL from a string. 6) Still with version 9. 6 and 10 can use all three join strategies in parallel query plans, but they can only use a partial plan on the outer side of the join. Another type of index is the Hash index. class passlib. It is written in C and provides to efficiently perform the full range of SQL operations against Postgres databases. Hash index that did not output WAL before PostgreSQL 9. There's a download link available for any file or directory, if you only want to download a portion of the phpMyFAQ code. DynamoDB to Postgres. PostgreSQL provides several index types: B-Tree, Hash, No extra support is provided for the Range Functions listed in Table 9-45 of the postgres documentation. 2 and 4 have the same salt, and different hashes. PHP's PostgreSQL extension provides a comprehensive API for developers to interact with a PostgreSQL RDBMS. After that we tried to write a proof of concept (POC) to make sure our finding is real, using the latest available PostgreSQL database version from their GIT repository. The ASC and DESC specify the sort order. This allows the verify function to verify the hash without needing separate storage for the salt or algorithm information. Then iterate through all of the pets and use the check if the owner of the pet is in the hash table. Press button, get result. In particular, setting up indexes of the hash, GIN and BRIN type might just be the solution to your performance issues:. All source code is kept under Git revision control, which you can browse online. In some cases EXPLAIN ANALYZE provides additional execution statistics beyond the execution times and row counts, such as Sort and Hash above. Here we’ll not go into details comparing the pros and cons of different ways of storing passwords, rather we’ll see how we can implement salt hashing mechanism for storing passwords in NodeJS. If Postgres were to use a hash join to execute this query, one way execution would proceed is as follows. … We'll briefly discuss them here, … but we won't go into a lot of detail about them. I need to hash some passwords with salt on postgresql, and I haven't been able to find any relevant documentation on how to get that done. PostgreSQL has now the leadership position for the over clause. 1, reads, devices, and device_patients. sql from an instance to create hash index on a very big partitioned table on Postgres 11. Also if you are looking for a way to reduce collisions and still keep the hash result small (smaller than say md5) you could get a nice database friendly 64 bit value by using hash/crc32 and hash/crc32b, which is slower than a single md5 but the result may be more suitable for certain tasks. Parameter type is same as in encode. Inside PostgreSQL — Multi-Batch Hash Join Improvements. So if you’re just giving everything a unique primary key, then thats the hash. Programs that want the database software to handle the low-level work of managing data simply use that language to send it instructions. The number of buckets initially equals two and dynamically increases to adjust to the data size. By Nando Vieira. Postgres 10 came with RANGE and LIST type partitions. Postgres has support for universally unique identifiers (UUIDs) as a column data type via uuid. Add hash partitioning. Hash aggregate is a node type that requires an aggregate operator, and a group key column. Instead, that Hash Join (property_id = id) returns 1,338 rows. 2 and basically check if all the data that you need is in the index, if so, it doesn't access the actual table row. If you want the digest as a hexadecimal string, use encode() on the result. 2 in the following examples. Postgres has a number of index types. PostgreSQL 9. Tree hashes are more flexible than conventional hash functions, and Bao can verify small pieces of a file independently rather than just the whole thing at once. Creating Roles. What I am looking for a good hashing algorithm that has very low Collision and can generate a hash in Int or. PostgreSQL + PowerShell 14 January 2016. " Their approach is to use a traditional stored procedure interface for accessing data and on top of that layer proxy servers which hash SQL requests to a set of database servers that. PostgreSQL 11 taps LLVM and parallelization for speed boost and the ability to partition tables using the hash function of a given key, as well as ranges for a column value or a list of. The number of buckets initially equals two and dynamically increases to adjust to the data size. "N/A" indicates that the extension or module is not available for that PostgreSQL version. This gives us robustness and scalability - your Catalyst-based app also inherits those. The B-Tree type is great for general purpose indexes but there are special cases when other types provide better results. With the release of PostgreSQL 9. The other major case when a hash join won't be the preferred join algorithm is when Postgres thinks the hash table needed for the hash join won't fit in memory 1. Django uses PostgreSQL’s SERIAL data type to store auto-incrementing primary keys.