Skip to main content

Indexing PostgreSQL with Apache Solr

Image
By
Struggling with delivery, architecture alignment, or platform stability?

I help teams fix systemic engineering issues: processes, architecture, and clarity.
→ See how I work with teams.


This article explains how to offload heavy IP search and filtering workloads from PostgreSQL to Apache Solr to gain speed, scalability and richer query capabilities. PostgreSQL remains the system of record for IP ranges and geolocation data, while Solr—backed by Lucene’s inverted indexes—handles fast range lookups and flexible text queries over fields like country, state and city. The piece walks through creating a Solr core, defining an IP-focused schema, wiring PostgreSQL to Solr via the DataImportHandler, and running full imports so Solr can be queried for IP ranges and geo filters with simple syntax. The takeaway: combining Postgres for storage and Solr for search yields a robust, scalable IP management stack that outperforms pure SQL approaches for complex, high-volume search use cases.

Searching and filtering large IP address datasets within PostgreSQL can be challenging. Why? Databases excel at data storage and structured queries, but often struggle with full-text search and complex analysis. Apache Solr, a high-performance search engine built on top of Lucene, is designed to handle these tasks with remarkable speed and flexibility.

What do we need?

  • A running PostgreSQL database with a table containing IP address information (named "ip_loc" in our example).
  • A basic installation of Apache Solr.

Setting up Apache Solr

1. Create a Solr Core:

solr create -c ip_data -d /path/to/solr/configsets/

2. Define the Schema (schema.xml)

<field name="start_ip" type="ip" indexed="true" stored="true"/>
<field name="end_ip" type="ip" indexed="true" stored="true"/>
<field name="iso2" type="string" indexed="true" stored="true"/>
<field name="state" type="text_general" indexed="true" stored="true"/>
<field name="city" type="text_general" indexed="true" stored="true"/>

Integrating PostgreSQL and Solr

Solr's DataImportHandler (DIH): Add the following DIH configuration to your solrconfig.xml:

<dataConfig>
    <dataSource type="JdbcDataSource" 
                driver="org.postgresql.Driver"
                url="jdbc:postgresql://localhost/your_database"
                user="your_username" 
                password="your_password"/>
    <document>
        <entity name="ip_data" query="SELECT * FROM ip_loc">
            <field column="start_ip" name="start_ip" /> 
            </entity>
    </document>
</dataConfig>

Import Data: Initiate the data import using the Solr admin interface or the command line:

http://localhost:8983/solr/ip_data/dataimport?command=full-import

Querying Solr

  • IP Range Search: start_ip:[192.168.0.1 TO 192.168.255.255]
  • Geolocation Filtering: iso2:US AND state:California
  • Combined Search: city:NewYork AND start_ip:[10.0.0.0 TO 10.255.255.255]

Benefits vs. Pure PostgreSQL

  1. Performance: Solr's inverted indexes provide superior search speed.
  2. Scalability: Solr easily distributes across multiple machines.
  3. Flexibility: Solr's query syntax offers rich search capabilities.

My take

By combining PostgreSQL and Apache Solr, you create a robust IP address management system that scales efficiently while providing lightning-fast search functionality.

Related guides:

If you need help with distributed systems, backend engineering, or data platforms, check my Services.

Labels:

Most read articles

Building a Model-Agnostic Multi-Agent System with OpenClaw

By
Over one week we rebuilt our AI stack around OpenClaw’s multi-agent architecture to avoid provider lock-in and stop wasting premium tokens. By aligning models to tasks, diversifying fallbacks across providers, enforcing minimal tool access, and switching to memory-first workflows with ephemeral sessions, we reduced token usage per task by about 70% and cut our monthly bill by 77% while improving operational resilience. How We Achieved 77% Cost Reduction and Provider Independence Over the past week, we rebuilt our AI infrastructure around OpenClaw’s multi-agent architecture. The result was a 77% cost reduction , provider independence , and a delegation system that routes work to the most cost-effective model for each job. Below is the technical journey of optimizing a 7-agent squad with OpenClaw. The Challenge: Model Provider Lock-In We started with a simple problem: our entire squad defaulted to a single model provider. This created three issues: Cost inefficiency beca...
Read more

BacNet => MQTT in Production: The Real Cost of Bridging BACnet to MQTT at Scale

By
bacnet2mqtt looks simple in a README and expensive in production. Once BACnet polling, reconnection behavior, stale state, and MQTT publishing collide, teams discover they are not deploying a lightweight adapter but operating infrastructure. This article breaks down where bacnet2mqtt works, where it becomes a bottleneck, and which production patterns reduce the operational damage before incidents, backlogs, and silent data loss turn a building integration into a long-running engineering problem. I inherited a building controls integration problem 18 months ago. Three office floors. 217 BACnet sensors covering temperature, occupancy, and HVAC actuators. The data was trapped inside the building automation network while the business wanted analytics, reporting, and compliance visibility in the data platform. The obvious answer looked easy enough: deploy bacnet2mqtt, bridge BACnet into MQTT, and push the stream into the lakehouse stack. The repository made it sound like a w...
Read more

Get Apache Flume 1.3.x running on Windows

By Anonymous
Since we found an increasing interest in the flume community to get Apache Flume running on Windows systems again, I spent some time to figure out how we can reach that. Finally, the good news - Apache Flume runs on Windows. You need some tweaks to get them running. Prerequisites Build system: maven 3x, git, jdk1.6.x, WinRAR (or similar program) Apache Flume agent: jdk1.6.x, WinRAR (or similar program), Ultraedit++ or similar texteditor Tweak the Windows build box 1. Download and install JDK 1.6x from Oracle 2. Set the environment variables    => Start - type " env " into the search box, select " E dit system environment variables ", click Environment Variables, Select " New " from the " Systems variables " box, type " JAVA_HOME " into " variable name " and the path to your JDK installation into "Variable value" (Example:  C:\Program Files (x86)\Java\jdk1.6.0_33 ) 3. Download maven from Apache 4. Set...
Read more