PostgreSQL LIKE query performance variations

I have been seeing quite a large variation in response times regarding LIKE queries to a particular table in my database. Sometimes I will get results within 200-400 ms (very acceptable) but other...

When should I consider using a in memory database and what are the issue to look out for?

I was just think that now it is common to have enough RAM on your database server to cache your complete database why are the specialist in memory database (e.g TimesTen, see also Wikipedia...

mysql error 1451

I have mysql error with code 1451. Cannot delete or update a parent row: a foreign key constraint fails (online_store_admin.osa_admin_logs, CONSTRAINT fk_admins_logs FOREIGN KEY (aid) REFERENCES...

B trees vs binary trees

If I am implementing in-memory(RAM) search operation with b trees, then would it be better in terms of caching or some other effects when compared with binary trees? What I know is- binary search...

Red Black Tree versus B Tree

I have a project in which I have to achieve fast search, insert and delete operations on data ranging from megabytes to terabytes. I had been studying data structures of late and analyzing them....

Check if a binary tree is a mirror image or symmetric

What is the basics algorithm for testing if a tree is symmetrical. Because it is a binary tree, I would assume that it would be a recursive definition of sorts The formal question is below: A...

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

I've been trying to sort out the relationship between unique and index in Postgres after reading the docs on index uniqueness being an implementation detail: The preferred way to add a unique...

is It faster to insert rows in primary key order?

I have a table like this : create table stream_last ( symbol_id integer, high decimal(8,2), low decimal(8,2), last ...

MySQL when can I use HASH instead of BTREE

Since MySQL uses BTREE by default when creating an index, is there some instance when I can use HASH? For example, if my table only consists of Foreign Keys which are just INT UNSIGNED values. Is...

PHP: Fastest way to handle undefined array key

in a very tight loop I need to access tens of thousands of values in an array containing millions of elements. The key can be undefined: In that case it shall be legal to return NULL without any...

How to create index on JSON field in Postgres?

In PostgreSQL 9.3 Beta 2 (?), how do I create an index on a JSON field? I tried it using the -> operator used for hstore but got the following error: CREATE TABLE publishers(id INT, info JSON); ...

Why is it important to delete files in-order to remove them faster?

Some time ago I learned that rsync deletes files much faster that many other tools. A few days ago I came across this wonderful answer on Serverfault which explains why rsync is so good at...

Using ST_MakePoint for dataset with over 1 billion rows

i have a global dataset in my postgres database (9.2.4 and postgis 2.1.0SVN) with ~ 1.1 billion rows. my aim is to extract relevant rows using a polygon. query is following and running since one...

Postfix transport_maps and virtual_mailbox

I'm trying to pipe one specific address to php script but it seems my transport_maps doesn't trigger, instead of it the virtual_transport is used. Here is what I have: @example.com is listed in...

The more rows a MySQL table has, the longer it takes to find a row

Hy , I have a innoDB table in MySQL with 70785 rows. I need to find rows by its documentId column and it takes moreless 200 miliseconds for each request. This column has a BTREE index The time...

What is special about internal design of LMDB?

What would be the performance difference (reads/writes) between some C++ implementation of in-memory B-Tree (for example google btree) and the LMDB (without taking into consideration all the...

Want to print out a "pretty" btree

As of now,this program traverses in level order but just prints out the numbers.I want to know how to print it so it could look something like the picture below or just a fancy way to show the...

Django PostgreSQL db_index on JSONField to query by exact key

I have a Place model: class Place(models.Model): ... some not interesting fields distances = models.JSONField() In JSONField (distances) stores something like: { '1': 10 '2': 20 '3':...

MySQL hangs on ALTER TABLE

My not-so-big table hangs on an ALTER command. What could it be? Only 150k rows, 42 fields 142 MByte total. InnoDB storage engine and Server version: 5.5.44-MariaDB MariaDB Server. 1 field,...

Postgres hash index with unique constraint

With Postgres 10 properly supporting hash index, I would like to use hash index for id lookup (hash index is smaller in size compared to btree and theoretically faster). I have a table create...

Troubleshooting an insert statement, fails without error

I am trying to do what should be a pretty straightforward insert statement in a postgres database. It is not working, but it's also not erroring out, so I don't know how to troubleshoot. This is...

Possible to use a BRIN Index on a Primary Key in PostgreSQL

I was reading up on the BRIN index within PostgreSQL, and it seems to be beneficial to many of the tables we use. That said, it applies nicely to a column which is already the primary key, in...

The C++ way to relocate object of implicitly non-movable class

I'm writing some custom library like stl, but without allocations in ctors and with disabled copy ctors in resource owning classes (because the environment doesn't support exceptions and all...

Construct a BTree from a sorted array

I need to construct a BTree from a sorted array. Any pointers or how can i write such an algorithm? Is there any algoritm that can take advantage of the array being sorted? I did search it on the...

Binary Tree implemented in C using Python Api converts PyObject into Bytes

My PyObject becomes a Bytes object while getting it's value So lately, I am doing a project in C, where I implement a few type of trees, to be able to use them in python, since a C btree...

Django & Postgres - Index Row Size Exceeds btree version 4 maximum

I have defined the following in Django to store an SSL certificate in plain-text PEM format: class Certificate(models.Model): pem = models.CharField(max_length=4096, unique=True, blank=False,...

How to traverse BTree in-order without recursion in iterative style?

I need B-Tree LNR traversal (in-order). I've found an algorithm for B-Tree traversal here. How I can implement it without recursion in iterative way? I've found this question but there is no...

Postgres uses Hash Join with Seq Scan when Inner Select Index Cond is faster

Postgres is using a much heavier Seq Scan on table tracking when an index is available. The first query was the original attempt, which uses a Seq Scan and therefore has a slow query. I attempted...

Amazon RDS PostgreSQL: Sudden increase in Read IOPS

We are using Amazon RDS to host our PostgreSQL databases. Our production instance (db.t3.xlarge, Single-AZ) was running smoothly until suddenly Read IOPS, Read Latency, Read Throughput and Disk...

Lucene index modeling - Why are skiplists used instead of btree?

I have recently started learning lucene and came to know about how lucene stores and queries indices. Lucene seems to be using skip list as an underlying data structure. However, I did not find...